hive学习笔记——表的基本的操作

时间:2024-07-08 17:35:20

1、hive的数据加载方式

1.1、load data

这中方式一般用于初始化的时候

load data [local] inpath '...' [overwrite] into table t1 [partition(...)]
-- 如果有local是从linux磁盘加载,如果没带local是从hdfs的某个目录移动到hive中
-- 带上overwrite会覆盖掉表中原来的数据
-- partition 加载到指定分区

1.2、使用查询语句向表中插入数据

insert overwrite table t1 select * from t2;
--也可以不覆盖掉,直接导入,把关键词overwrite替换为into -- 对于分区表稍微复杂
insert overwrite table t2 partition(day=2) select * from t1;
--有一张大表,可以通过这种方式构建小表。但是这种方式在多个分区的情况下效率很低,会对原始表执行多次扫描。 --将一张大表拆开写入分区表的时候应该使用下面的语法
from t1
insert overwrite table t2 partition(day=2) select * where day=2
insert overwrite table t2 partition(day=3) select * where day=3
insert overwrite table t4 select * where day=4

1.3、动态分区的插入

如果分区很多的情况下,单独下插入语句是非常恐怖的事。

所谓的动态分区,指的是插入到目标表时,不指定分区值,仅指定分区字段,分区值是从原始表中取得的。

默认,hive是不支持动态分区插入的,如果不支持的话,可以设置hive.exec.dynamic.partition=true打开。

set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.mode=nonstrict;
set hive.exec.max.dynamic.partitions.pernode=1000;
insert overwrite table t2 partition(province,city) select ...,province,city from a;
--动态分区的字段一定位于其他各个字段的最后

也可以动态静态结合的使用

insert overwrite table t2 partition(province='beijing',city) select ...,province,city from a;
--前面指定了province的值的时候,在select中查不查province已经无所谓了(前面指定的值优先级高,但是不会对后面的查询结果进行过滤,就是说所有的province的值都会改为北京)

1.4、CTAS

使用单个查询语句创建表并加载数据(不能创建分区表,外部表,桶表)

create table ... as select

查询数据的时候允许创建一张表,语法如下

create table t3 as select id from t1 where id =1

1.5、导出数据

有两种方式:

(1)直接使用HDFS命令导出文件夹

(2)使用如下语法的hive

insert overwrite local directory 'localpath' select id from t1;

2、表查询

2.1、数据查询

表可以使用别名

select * from t1 e;

在select 和 where 子句中可以使用hive的内置函数和自定义函数。函数分为普通函数、聚合函数、表函数。其中表函数必须使用别名,不能在where语句中不能引用列别名。

2.2、表连接

hive只支持等值连接,即on子句中使用等号连接。

如果连接语句中有where子句,会先执行join子句,在执行where子句。

2.2.1、内连接

内连接指把符合条件的数据查询出来。

语句如下:

select * from user join job on user.id=job.user_id;
-- 等价于
select * from user , job on user.id=job.user_id;

2.2.2、左外连接

语句如下:

select * from user left outer join job on user.id=job.user_id;

2.2.3、右外连接

和左外连接类似,语句如下

select * from user right outer join job on user.id=job.user_id;

2.2.4、全外连接

语法如下

select * from user outer join job on user.id=job.user_id;

2.2.5、左半连接

老版本特有的功能,用来代替in 和 exist操作

语法如下:

select * from user left semi join job on user.id=job.user_id;
--语句相当于:
select * from user where user.id in (select user_id from job);

但是hive不支持in子句(新版本中已经支持in)

2.2.6、笛卡尔积

语句如下:

select * from user join job;  

2.2.7、join 与where的顺序

where 子句的执行顺序是位于join子句之后的。对比下列两个sql语句的输出

select * from user left join job on user.id=job.user_id where job.id=3;
select * from user left join (select * from job where id=3) job on user.id = job.user_id;

第一条语句无任何输出,第二条语句产生三条输出。

2.3、排序

hive支持 order by 和 sort by子句

当可能有多个reduce任务时,order by 是在一个 reduce 任务中进行排序;sort by 是在多个reduce 任务内部进行排序,每个reduce 任务自己排序,不管全局是否有排序。

distribute by 会与 sort by一起使用,目的是在sort 不用排序时 把相同的分类放到一个reduce中进行排序

*cluster by 是distribute by 和 sort by的缩写形式。

2.4、数据倾斜

数据不是均匀分布,在shuffle过程中,map向reduce分配数据的时候,分配的数据量不一样,导致执行时间不一样。

2.5、视图

*作用在于简化复杂查询

创建视图

create view user_view as select * from user where id=1;

3、hive参数

hive-default.xml中的参数

4、hive的文件格式

4.1、自定义hive的文件格式

create table t1(...) stored as textfile;
--缺点:占用空间比较大

sequencefile 是包含键值对的二进制的文件存储格式,支持压缩可以节约存储空间。是hadoop领域的标准文件格式,但是在hadoop之外无法使用。

rcfile 是列式文件存储格式,适合压缩处理。对于有成百上千的字段而言,RCFile 更加适合。

5、调优

5.1、本地模式

对于小文件处理,如果分发到各个节点处理,网络传输等因素效率很低。

对于小数据集,运行时设置SET mapred.job.tracker=local;可以使用本地方式运行,即在hive客户端执行,而不是提交到hadoop集群执行。因此速度更快。

5.2、map side agg

row format, storage format,serde

row foramt 用户指定row foramt(行分隔)

delimited 子句用户处理有分隔符的文件;

escaped by 子句用户普通处理分隔符;

null defined as 子句用户指定空如何显示,默认是'\N';

serde 子句用户指定serde;

stored as textfile 用于指定处处文件必须是普通文件;

stored as sequencefile 用户指定存储文件可以被压缩;

inputformat和outputformat用户指定inputformat和outputformat的名称;

stored as parquet 指定列格式 parquet存储格式;

stored by 用于创建非本地表,如hbase表等;

7、DML

7.1、Alter Table

--表重命名
Alter TABLE table_name RENAME To new_table_name;
--修改表属性
ALTER TABLE table_name SET TBLPROPERTIES table_properties;
--修改注释
ALTER TABLE table_name SET SERDEPROPERTIES serde_paoperties;
--添加SerDe
ALTER TABLE table_name SET SERDE serde_class_name [WITH SERDEPROPERTIES serde_properties];
ALTER TABLE table_name SET SERDEPROPERTIES serde_properties;
--修改表存储
ALTER TABLE table_name CLUSTERED BY(col_name,col_name...) [STORED BY(col_name...)] INTO num_buckets BUCKETS;

7.2、Alter Partition

7.2.1、Add Partitions

ALTER TABLE table_name ADD [IF NOT EXISTS] PARTITION partition_spec [LOCATION 'location1'] partition_spec [LOCATION 'location2'];

7.2.2、Dynamic Partition

7.2.3、Rename Partition

ALTER TABLE table_name Partition partition_spec RENAME TO PARTITION partition_spec;

7.2.4、Recover Partitions

MSCK REPAIR TABLE table_name;
--会把已经位于HDFS的分区目录中,但是在metastore中没有的分区信息,自动添加到metastore中。

7.2.5、Drop Partitions

ALTER TABLE table_name DROP [IF EXISTS] PARTITION partition_spec, PARTITION partition_spec;

如果分区有些保护,可以使用下面语句;

ALTER TABLE table_name DROP [IF EXISTS] PARTITION partition_spec IGNORE PROTECTION;

7.3、Alter Column

7.3.1、Change Column Name/Type/Position/Comment

ALTER TABLE table_name CHANGE [COLUMN] col_old_name col_new_name col_type [COMMENT col_comment] [FIRST|ALTER column_name];

修改语句只会影响metastore,不会影响原始数据。

7.3.2、Add/Replace Columns

7.4、Create/Drop/Alter View

创建视图

CREATE VIEW [IF NOT EXISTS] view_name [(column_name [COMMENT column_comment],...)] [COMMENT view_comment] [TBLPROPERTIES (property_name=property_value,...)] AS SELECT ...

视图仅是逻辑对象,在HDFS中没有存储。当基表改变时,视图定义不会改变,再次使用视图会报错。

--删除视图
DROP VIEW [IF EXISTS] view_name;
--修改视图定义
ALTER VIEW view_name AS select_statement;

7.5、Create/Drop Functions

7.6、Create/Drop/Grant/Revoke Roles and Privileges

7.7、Show

--显示数据库
SHOW DATABASES;
--显示表
SHOW TABLES [IN database_name];
--显示分区
SHOW PARTITIONS table_name;
--显示一部分分区
SHOW PARTITIONS [db_name].table_name [PARTITION(partition_desc)];
--显示表/分区扩展信息
SHOW TABLE EXTENDED [IN|FROM database_name] LIKE identifier_with_wildcards [PARTITION(partition_desc)];

7.8、Describe

7.9、Select

语法

[WITH CommonTableExpression (,CommonTableExpression)*]

SELECT [ALL|DISTINCT] select_expr,select_expr,...

FROM table_reference

[WHERE where_condition]

[GROUP BY col_list]

[[CLUSTER BY col_list] | [DISTRIBUTE BY col_list] [SORT BY col_list]]

[LIMIT number]

7.9.1、where 子句

where 子句必须是布尔值,可以支持子查询

7.9.2、all and distinct

select 子句中默认是all,可以不写。Distinct 是去重。

7.9.3、基于partition的查询

7.9.4、having 子句

having子句相当于子查询。

7.9.5、limit子句

返回指定数量的数据,数据是任意的。

7.10、使用正则

7.11、group by 子句

7.12、join子句

table_reference JOIN table_factor [join_condition]
| table_reference {LEFT | RIGHT | FULL} [OUTER] JOIN table_reference join_condition
|table_reference LEFT SEMI JOIN table_reference join_condition
|table_reference CROSS JOIN table_reference [join_condition]

不使用join,使用逗号分隔表,也会认为是join操作。

默认,每个join操作会产生一个 M/R job

驱动表

多表联合的时候,非驱动表会被缓存,也可以通过STREAMTABL指定驱动表

10、Types

10.1、符合类型

hive array、map、struct使用

struct:struct 内部的数据可以通过Dot(.)来访问

map(k-v对):访问指定域可以通过["指定域名称"]进行。map["key"]

array:array中的数据为相同类型,通过下标访问内部数据(起始位置0)

10.1.1、struct使用

建表:

create table student_test(id INT,info struct<name:STRING,age:INT>)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
COLLECTION ITEMS TERMINATED BY ':';
--FIELDS TERMINATED BY :字段之间的分隔符
--COLLECTION ITEMS TERMINATED BY :一个字段各item的分隔符

数据示例:

1,小李:12

加载数据

load data local  inpath 'data' into table student_test;

查询:

select info.age from student_test;

10.1.2、array的使用

创建表

create table array_table(name STRING,student_id_list array<INT>)
row format delimited
fields terminated by ','
collection items terminated by ':';

导入数据

load data local inpath 'data6' into table array_table;

查询

select student_id_list[] from array_table;

10.1.3、map使用

创建表

create table map_table(id STRING,pref map<string,int>)
row format delimited
fields terminated by '\t'
collection items terminated by ','
map keys terminated by ':';

导入数据

load data local inpath 'map_data' into table map_table;

查询

select perf['person'] from map_table;

11、ORC

ORC(optimized row columnar) 提供了更高效的方式存储hive数据。使用ORC,会提高读、写、处理数据的能力。

(1)每个task输出会产生一个文件,可以煎炒namenode的压力;

(2)支持datetime、decimal和复合类型;

(3)文件中可以存储轻量级的索引;

(4)支持数据类型的块压缩;

(5)使用多个RecordReader并发的读取相同的文件;

(6)ability to split files without scanning for markers;

(7)bound the amount of memory needed for reading or writing;

(8)metadata stored using Protocol Buffers,which allows addition and removal of fields;

12、CompressedStorage

压缩存储有时候可以获得更好的性能。

使用TextFile 存储时,可以使用GZIP或BZIP2进行压缩。操作如下:

CREATE TABLE raw(line STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n';
LOAD DATA LOCAL INPATH 'file.gz' INTO TABLE raw;

以上操作的缺点是hive在查下时不能 分割压缩文件,不能并行执行map。

更好的方式如下操作:

CREATE TABLE raw(line STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n';
CREATE TABLE raw_sequence(line STRING) STORED AS SEQUENCEFILE;
LOAD DATA LOCAL INPATH 'file.gz' INTO TABLE raw;
SET hive.exec.compress.output=true;
SET io.seqfile.compression.type=BLOCK; --NONE/RECORD/BLOCK
INSERT OVERWRITE TABLE raw_sequence SELECT * FROM raw;

把数据插入到另一张表,另一张表使用SequenceFile存储。

13、函数

13.1、常见函数

hive函数分为四大类:单行函数、聚合函数、表函数、分析函数。

在hive客户端输入show functions 中可以看到所有的函数。

查看函数的用法 show function function_name;

表函数:

explode()函数是把一个数组作为数据,输出时数组的每个元素作为单独一行。

示例:

select explode(array(1,23,4)) from dual;
select explode(map(1,11,2,22,4,44)) from dual;

*dual 是一个伪表,满足sql的语法select ... from ...;

*array是一个函数

posexplode()函数和explode()函数非常相似,增加的功能是返回结果带有位置信息,表示第几行。

parse_url_tuple()函数用户解析url信息。

示例:

select parse_url_tuple('http://www.test.com/a.html?key=k1&key2=k2#tag ','HOST','PATH','QUERY','QUERY:k1','QUERY:k2') from dual; 

12.2、自定义函数

12.2.1、自定义单行函数

(1)继承org.apache.hadoop.hive.ql.exec.UDF,覆盖其中的evaluate()方法,该方法的形参数量、类型,返回值类型,都不做限制。返回值只要是hive可以序列化的类型即可。

(2)打包成jar

(3)在hvie命令行下,执行命令ADD JAR ...jar;

(4)在hive命令行下,执行命令CREATE TEMPORARY FUNCTION xxx AS '函数全名称';

如果要删除函数,在hive命令行下,执行DROP TEMPORARY FUNCTION IF EXISTS xxx;