Hive 基本语法

版权声明: https://blog.csdn.net/asd54090/article/details/80920515

Hive 语法和SQL的类似,但不完全一样,这里给出一个官方文档地址和转载一个优秀的教程(要问我为什么不写?要写完实在是太多了。。。)

官方DDL地址:https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL

下面是转载的,转载至:https://www.cnblogs.com/qiaoyihang/p/6181630.html

建表规则如下:

复制代码
CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name 
  [(col_name data_type [COMMENT col_comment], ...)] 
  [COMMENT table_comment] 
  [PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)] 
  [CLUSTERED BY (col_name, col_name, ...) 
  [SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS] 
  [ROW FORMAT row_format] 
  [STORED AS file_format] 
  [LOCATION hdfs_path]
复制代码

•CREATE TABLE 创建一个指定名字的表。如果相同名字的表已经存在,则抛出异常;用户可以用 IF NOT EXIST 选项来忽略这个异常

•EXTERNAL 关键字可以让用户创建一个外部表,在建表的同时指定一个指向实际数据的路径(LOCATION)

•LIKE 允许用户复制现有的表结构,但是不复制数据

•COMMENT可以为表与字段增加描述

•ROW FORMAT DELIMITED [FIELDS TERMINATED BY char] [COLLECTION ITEMS TERMINATED BY char]

[MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char]

| SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, ...)]

用户在建表的时候可以自定义 SerDe 或者使用自带的 SerDe。如果没有指定 ROW FORMAT 或者 ROW FORMAT DELIMITED,将会使用自带的 SerDe。在建表的时候,用户还需要为表指定列,用户在指定表的列的同时也会指定自定义的 SerDe,Hive 通过 SerDe 确定表的具体的列的数据。

•STORED AS

SEQUENCEFILE

| TEXTFILE

| RCFILE

| INPUTFORMAT input_format_classname OUTPUTFORMAT output_format_classname

如果文件数据是纯文本,可以使用 STORED AS TEXTFILE。如果数据需要压缩,使用 STORED AS SEQUENCE 。

例:创建外部表

hive> CREATE EXTERNAL TABLE IF NOT EXISTS student2
    > (sno INT,sname STRING,age INT,sex STRING)   
    > ROW FORMAT DELIMITED                        
    > FIELDS TERMINATED BY '\t'                   
    > STORED AS TEXTFILE                          
    > LOCATION '/user/external';      

 

一些基本操作:

复制代码
删除:
 hive> DROP TABLE test1;

修改表结构:
 DESC student1;
hive> ALTER TABLE student1 ADD COLUMNS  
    > (address STRING,grade STRING);

修改表名:
hive> ALTER TABLE student1 RENAME TO student3;

创建和已知表相同结构的表:
hive> CREATE TABLE copy_student1 LIKE student1;

导入外部文件数据:
加载数据到student1表中
LOAD DATA LOCAL INPATH '/home/hadoop/data/student1.txt' INTO TABLE student1;

加载hdfs中的文件:
LOAD DATA INPATH '/user/hive/student1.txt' INTO TABLE copy_student1;

复制表数据:
 INSERT OVERWRITE TABLE copy_student2 SELECT * FROM student1;

多表同时复制:
hive> FROM student1                                       
    > INSERT OVERWRITE TABLE copy_student3
    > SELECT *                            
    > INSERT OVERWRITE TABLE copy_student4
    > SELECT *;
复制代码

 

ORDER BY 会对输入做全局排序,因此只有一个 Reduce(多个 Reduce 无法保证全局有序)会导致当输入规模较大时,需要较长的计算时间。使用 ORDER BY 查询的时候,为了优化查询的速度,使用 hive.mapred.mode 属性。

hive.mapred.mode = nonstrict;(default value/默认值)
hive.mapred.mode=strict;

与数据库中 ORDER BY 的区别在于,在 hive.mapred.mode=strict 模式下必须指定limit ,否则执行会报错。

hive> set hive.mapred.mode=strict;
hive> select * from group_test order by uid limit 5;

 

sort by 不受 hive.mapred.mode 的值是否为 strict 和 nostrict 的影响。sort by 的数据只能保证在同一个 Reduce 中的数据可以按指定字段排序。

使用 sort by 可以指定执行的 Reduce 个数(set mapred.reduce.tasks=< number>)这样可以输出更多的数据。对输出的数据再执行归并排序,即可以得到全部结果。

hive> set hive.mapred.mode=strict;
hive> select * from group_test sort by uid ;

 

DISTRIBUTE BY 排序查询

-- 按照指定的字段对数据划分到不同的输出 Reduce 文件中,操作如下。
hive> insert overwrite local directory '/home/hadoop/djt/test' select * from group_test distribute by length(gender);

--此方法根据 gender 的长度划分到不同的 Reduce 中,最终输出到不同的文件中。length 是内建函数,也可以指定其它的函数或者使用自定义函数。
hive> insert overwrite local directory '/home/hadoop/djt/test' select * from group_test order by gender  distribute by length(gender);
order by gender 与 distribute by length(gender) 不能共用。

 

 

索引操作

创建一个索引

复制代码
hive> create index user_index on table user(id) 
    > as 'org.apache.hadoop.hive.ql.index.compact.CompactIndexHandler' 
    > with deferred rebuild
    > IN TABLE user_index_table;
hive> alter index user_index on user rebuild;
hive> select * from user_index_table limit 5; 
0       hdfs://mycluster/user/hive/warehouse/table02/000000_0   [0]
1       hdfs://mycluster/user/hive/warehouse/table02/000000_0   [352]
2       hdfs://mycluster/user/hive/warehouse/table02/000000_0   [704]
3       hdfs://mycluster/user/hive/warehouse/table02/000000_0   [1056]
4       hdfs://mycluster/user/hive/warehouse/table02/000000_0   [1408]
Time taken: 0.244 seconds, Fetched: 5 row(s)
复制代码

 

 

索引案例

创建一个索引测试表 index_test,dt作为分区属性,“ROW FORMAT DELIMITED FILEDS TERMINATED BY ','” 表示用逗号分割字符串,默认为‘\001’。

 create table index_test(id INT,name STRING) PARTITIONED BY (dt STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';

创建一个临时索引表 index_tmp。

hive> create table index_tmp(id INT,name STRING,dt STRING) ROW FORMAT DELIMITED FILEDS TERMINATED BY ',';

加载本地数据到 index_tmp 表中。

hive> load data local inpath '/home/hadoop/djt/test.txt' into table index_tmp

设置 Hive 的索引属性来优化索引查询,命令如下。

hive> set hive.exec.dynamic.partition.mode=nonstrict;----设置所有列为 dynamic partition
hive> set hive.exec.dynamic.partition=true;----使用动态分区

查询index_tmp 表中的数据,插入 table_test 表中。

hive> insert overwrite table index_test partition(dt) select id,name,dt from index_tmp;
复制代码
--使用 index_test 表,在属性 id 上创建一个索引 index1_index_test 。
hive> create index index1_index_test on table index_test(id) as 'org.apache.hadoop.hive.ql.index.compact.CompactIndexHandler' WITH DEFERERD REBUILD;

--填充索引数据。
hive> alter index index1_index_test on index_test rebuild;

--查看创建的索引。
hive> show index on index_test

-- 查看分区信息。
hive> show partitions index_test;
复制代码

 

修改配置文件信息:

复制代码
< property>
    < name>hive.optimize.index.filter< /name>
    < value>true< /value>
< /property>
< property>
    < name>hive.optimize.index.groupby< /name>
    < value>true< /value>
< /property>
< property>
    < name>hive.optimize.index.filter.compact.minsize< /name>
    < value>5120< /value>
< /property>
复制代码

hive.optimize.index.filter 和 hive.optimize.index.groupby 参数默认是 false。使用索引的时候必须把这两个参数开启,才能起到作用。

hive.optimize.index.filter.compact.minsize 参数为输入一个紧凑的索引将被自动采用最小尺寸、默认5368709120(以字节为单位)。

 

分区操作

Hive 的分区通过在创建表时启动 PARTITION BY 实现,用来分区的维度并不是实际数据的某一列,具体分区的标志是由插入内容时给定的。当要查询某一分区的内容时可以采用 WHERE 语句, 例如使用 “WHERE tablename.partition_key>a” 创建含分区的表。创建分区语法如下。

CREATE TABLE table_name(
...
)
PARTITION BY (dt STRING,country STRING)

1、 创建分区

Hive 中创建分区表没有什么复杂的分区类型(范围分区、列表分区、hash 分区,混合分区等)。分区列也不是表中的一个实际的字段,而是一个或者多个伪列。意思是说,在表的数据文件中实际并不保存分区列的信息与数据。

创建一个简单的分区表。

hive> create table partition_test(member_id string,name string) partitioned by (stat_date string,province string) row format delimited fields terminated by ',';

 

复制代码
--这个例子中创建了 stat_date 和 province 两个字段作为分区列。通常情况下需要预先创建好分区,然后才能使用该分区。例如:
hive> alter table partition_test add partition (stat_date='2015-01-18',province='beijing');

--这样就创建了一个分区。这时会看到 Hive 在HDFS 存储中创建了一个相应的文件夹。
$ hadoop fs -ls /user/hive/warehouse/partition_test/stat_date=2015-01-18
/user/hive/warehouse/partition_test/stat_date=2015-01-18/province=beijing----显示刚刚创建的分区

每一个分区都会有一个独立的文件夹,在上面例子中,stat_date 是主层次,province 是 副层次。
复制代码

 

复制代码
--向分区表中插入数据
--使用一个辅助的非分区表 partition_test_input 准备向 partition_test 中插入数据,实现步骤如下。

insert overwrite table partition_test partition(stat_date='2015-01-18',province='jiangsu') select member_id,name from partition_test_input where stat_date='2015-01-18' and province='jiangsu';

向多个分区插入数据,命令如下。
hive> from partition_test_input
insert overwrite table partition_test partition(stat_date='2015-01-18',province='jiangsu') select member_id,name from partition_test_input where stat_date='2015-01-18' and province='jiangsu'
insert overwrite table partition_test partition(stat_date='2015-01-28',province='sichuan') select member_id,name from partition_test_input where stat_date='2015-01-28' and province='sichuan'
insert overwrite table partition_test partition(stat_date='2015-01-28',province='beijing') select member_id,name from partition_test_input where stat_date='2015-01-28' and province='beijing';
复制代码

 

 动态分区的产生

按照上面的方法向分区表中插入数据,如果数据源很大,针对一个分区就要写一个 insert ,非常麻烦。使用动态分区可以很好地解决上述问题。动态分区可以根据查询得到的数据自动匹配到相应的分区中去。动态分区可以通过下面的设置来打开:

set hive.exec.dynamic.partition=true;  
set hive.exec.dynamic.partition.mode=nonstrict; 

动态分区的使用方法很简单,假设向 stat_date='2015-01-18' 这个分区下插入数据,至于 province 插到哪个子分区下让数据库自己来判断。stat_date 叫做静态分区列,province 叫做动态分区列。

hive> insert overwrite table partition_test partition(stat_date='2015-01-18',province)
select member_id,name province from partition_test_input where stat_date='2015-01-18';

注意,动态分区不允许主分区采用动态列而副分区采用静态列,这样将导致所有的主分区都要创建副分区静态列所定义的分区。

 

几个常用参数

hive.exec.max.dynamic.partitions.pernode:每一个 MapReduce Job 允许创建的分区的最大数量,如果超过这个数量就会报错(默认值100)。

hive.exec.max.dynamic.partitions:一个 dml 语句允许创建的所有分区的最大数量(默认值100)。

hive.exec.max.created.files:所有 MapReduce Job 允许创建的文件的最大数量(默认值10000)。

尽量让分区列的值相同的数据在同一个 MapReduce 中,这样每一个 MapReduce 可以尽量少地产生新的文件夹,可以通过 DISTRIBUTE BY 将分区列值相同的数据放到一起,命令如下。

hive> insert overwrite table partition_test partition(stat_date,province)
select memeber_id,name,stat_date,province from partition_test_input distribute by stat_date,province;

 

桶操作

Hive 中 table 可以拆分成 Partition table 和 桶(BUCKET),table和partition可以通过‘CLUSTERED BY ’进一步分bucket,BUCKET 中的数据可以通过 SORT BY 排序。

BUCKET 主要作用如下。

 

1)数据 sampling;

 

2)提升某些查询操作效率,例如 Map-Side Join。

 

需要特别主要的是,CLUSTERED BY 和 SORT BY 不会影响数据的导入,这意味着,用户必须自己负责数据的导入,包括数据额分桶和排序。 'set hive.enforce.bucketing=true' 可以自动控制上一轮 Reduce 的数量从而适配 BUCKET 的个数,当然,用户也可以自主设置 mapred.reduce.tasks 去适配 BUCKET 个数,推荐使用:

hive> set hive.enforce.bucketing=true;
复制代码
1) 创建临时表 student_tmp,并导入数据。
hive> desc student_tmp;
hive> select * from student_tmp;

2) 创建 student 表。
hive> create table student(id int,age int,name string)
partitioned by (stat_date string)
clustered by (id) sorted by(age) into 2 bucket
row format delimited fields terminated by ',';

3) 设置环境变量。
hive> set hive.enforce.bucketing=true;

4) 插入数据。
hive> from student_tmp
insert overwrite table student partition(stat_date='2015-01-19')
select id,age,name where stat_date='2015-01-18' sort by age;

5) 查看文件目录。
$ hadoop fs -ls /usr/hive/warehouse/student/stat_date=2015-01-19/

6) 查看 sampling 数据。
hive> select * from student tablesample(bucket 1 out of 2 on id);
tablesample 是抽样语句,语法如下。

tablesample(bucket x out of y)

y 必须是 table 中 BUCKET 总数的倍数或者因子。
复制代码

 

Hive 复合类型

 

hive提供了复合数据类型:

 

 1)Structs: structs内部的数据可以通过DOT(.)来存取。例如,表中一列c的类型为STRUCT{a INT; b INT},我们可以通过c.a来访问域a。

 

 2)Map(K-V对):访问指定域可以通过["指定域名称"]进行。例如,一个Map M包含了一个group-》gid的kv对,gid的值可以通过M['group']来获取。

 

 3)Array:array中的数据为相同类型。例如,假如array A中元素['a','b','c'],则A[1]的值为'b'

复制代码
1、Struct使用

 1) 建表
hive> 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的分隔符。

 2) 导入数据
$ cat test5.txt   
1,zhou:30  
2,yan:30  
3,chen:20  
4,li:80  
hive> LOAD DATA LOCAL INPATH '/home/hadoop/djt/test5.txt' INTO TABLE student_test;

 3) 查询数据
hive> select info.age from student_test;  

2、Array使用
 1) 建表
hive> create table class_test(name string, student_id_list array< INT>)  
> ROW FORMAT DELIMITED                                              
> FIELDS TERMINATED BY ','                                          
> COLLECTION ITEMS TERMINATED BY ':';

 2) 导入数据
$ cat test6.txt   
034,1:2:3:4  
035,5:6  
036,7:8:9:10  
hive>  LOAD DATA LOCAL INPATH '/home/work/data/test6.txt' INTO TABLE class_test ; 

 3) 查询
hive> select student_id_list[3] from class_test; 

3、Map使用
 1) 建表
hive> create table employee(id string, perf map< string, int>)       
> ROW FORMAT DELIMITED                                          
> FIELDS TERMINATED BY '\t'                                
> COLLECTION ITEMS TERMINATED BY ','                       
> MAP KEYS TERMINATED BY ':';  
 ‘MAP KEYS TERMINATED BY’ :key value分隔符

 2) 导入数据
$ cat test7.txt   
1       job:80,team:60,person:70  
2       job:60,team:80  
3       job:90,team:70,person:100  
hive>  LOAD DATA LOCAL INPATH '/home/work/data/test7.txt' INTO TABLE employee;  

 3) 查询
hive> select perf['person'] from employee;
复制代码

 

 

Hive 的 JOIN 用法

 hive只支持等连接,外连接,左半连接。hive不支持非相等的join条件(通过其他方式实现,如left outer join),因为它很难在map/reduce job实现这样的条件。而且,hive可以join两个以上的表。

复制代码
1、等连接

只有等连接才允许
hive> SELECT a.* FROM a JOIN b ON (a.id = b.id)  
hive> SELECT a.* FROM a JOIN b ON (a.id = b.id AND a.department = b.department) 

2、多表连接
 同个查询,可以join两个以上的表
hive> SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key2) 

3、join的缓存和任务转换
 hive转换多表join时,如果每个表在join字句中,使用的都是同一个列,只会转换为一个单独的map/reduce。
hive> SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key1)
被转换为两个map/reduce任务,因为b的key1列在第一个join条件使用,而b表的key2列在第二个join条件使用。第一个map/reduce任务join a和b。第二个任务是第一个任务的结果join c。
hive> SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key2) 在join的每个map/reduce阶段,序列中的最后一个表,当其他被缓存时,它会流到reducers。所以,reducers需要缓存join关键字的特定值组成的行,通过组织最大的表出现在序列的最后,有助于减少reducers的内存。
hive> SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key1)
三个表,在同一个独立的map/reduce任务做join。a和b的key对应的特定值组成的行,会缓存在reducers的内存。然后reducers接受c的每一行,和缓存的每一行做join计算。
hive> SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key2)
这里有两个map/reduce任务在join计算被调用。第一个是a和b做join,然后reducers缓存a的值,另一边,从流接收b的值。第二个阶段,reducers缓存第一个join的结果,另一边从流接收c的值。 在join的每个map/reduce阶段,通过关键字,可以指定哪个表从流接收。
hive> SELECT /*+ STREAMTABLE(a) */ a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key1)
三个表的连接,会转换为一个map/reduce任务,reducer会把b和c的key的特定值缓存在内存里,然后从流接收a的每一行,和缓存的行做join。 4、join的结果 LEFT,RIGHT,FULL OUTER连接存在是为了提供ON语句在没有匹配时的更多控制。例如,这个查询: hive> SELECT a.val, b.val FROM a LEFT OUTER JOIN b ON (a.key=b.key) 将会返回a的每一行。如果b.key等于a.key,输出将是a.val,b.val,如果a没有和b.key匹配,输出的行将是 a.val,NULL。如果b的行没有和a.key匹配上,将被抛弃。语法"FROM a LEFT OUTER JOIN b"必须写在一行,为了理解它如何工作——这个查询,a是b的左边,a的所有行会被保持;RIGHT OUTER JOIN将保持b的所有行, FULL OUTER JOIN将会保存a和b的所有行。OUTER JOIN语义应该符合标准的SQL规范。 5、join的过滤 Joins发生在where字句前,所以,如果要限制join的输出,需要写在where字句,否则写在JOIN字句。现在讨论的一个混乱的大点,就是分区表 hive> SELECT a.val, b.val FROM a LEFT OUTER JOIN b ON (a.key=b.key) WHERE a.ds='2009-07-07' AND b.ds='2009-07-07' 将会连接a和b,产生a.val和b.val的列表。WHERE字句,也可以引用join的输出列,然后过滤他们。 但是,无论何时JOIN的行找到a的key,但是找不到b的key时,b的所有列会置成NULL,包括ds列。这就是说,将过滤join输出的所有行,包括没有合法的b.key的行。然后你会在LEFT OUTER的要求扑空。 也就是说,如果你在WHERE字句引用b的任何列,LEFT OUTER的部分join结果是不相关的。所以,当外连接时,使用这个语句 hive> SELECT a.val, b.val FROM a LEFT OUTER JOIN b ON (a.key=b.key AND b.ds='2009-07-07' AND a.ds='2009-07-07'; join的输出会预先过滤,然后你不用对有a.key而没有b.key的行做过滤。RIGHT和FULL join也是一样的逻辑。 6、join的顺序 join是不可替换的,连接是从左到右,不管是LEFT或RIGHT join。 hive> SELECT a.val1, a.val2, b.val, c.val FROM a JOIN b ON (a.key = b.key) LEFT OUTER JOIN c ON (a.key = c.key) 首先,连接a和b,扔掉a和b中没有匹配的key的行。结果表再连接c。这提供了直观的结果,如果有一个键都存在于A和C,但不是B:完整行(包括 a.val1,a.val2,a.key)会在"a jOIN b"步骤,被丢弃,因为它不在b中。结果没有a.key,所以当它和c做LEFT OUTER JOIN,c.val也无法做到,因为没有c.key匹配a.key(因为a的行都被移除了)。类似的,RIGHT OUTER JOIN(替换为LEFT),我们最终会更怪的效果,NULL, NULL, NULL, c.val。因为尽管指定了join key是a.key=c.key,我们已经在第一个JOIN丢弃了不匹配的a的所有行。 为了达到更直观的效果,相反,我们应该从 hive> FROM c LEFT OUTER JOIN a ON (c.key = a.key) LEFT OUTER JOIN b ON (c.key = b.key). LEFT SEMI JOIN实现了相关的IN / EXISTS的子查询语义的有效途径。由于Hive目前不支持IN / EXISTS的子查询,所以你可以用 LEFT SEMI JOIN 重写你的子查询语句。LEFT SEMI JOIN 的限制是, JOIN 子句中右边的表只能在 ON 子句中设置过滤条件,在 WHERE 子句、SELECT 子句或其他地方过滤都不行。 hive> SELECT a.key, a.value FROM a WHERE a.key in (SELECT b.key FROM B); 可以重写为 hive> SELECT a.key, a.val FROM a LEFT SEMI JOIN b on (a.key = b.key) 7、map 端 join 但如果所有被连接的表是小表,join可以被转换为只有一个map任务。查询是 hive> SELECT /*+ MAPJOIN(b) */ a.key, a.value FROM a join b on a.key = b.key 不需要reducer。对于每一个mapper,A和B已经被完全读出。限制是a FULL/RIGHT OUTER JOIN b不能使用。 如果表在join的列已经分桶了,其中一张表的桶的数量,是另一个表的桶的数量的整倍,那么两者可以做桶的连接。如果A有4个桶,表B有4个桶,下面的连接: hive> SELECT /*+ MAPJOIN(b) */ a.key, a.value FROM a join b on a.key = b.key 只能在mapper工作。为了为A的每个mapper完整抽取B。对于上面的查询,mapper处理A的桶1,只会抽取B的桶1,这不是默认行为,要使用以下参数: hive> set hive.optimize.bucketmapjoin = true; 如果表在join的列经过排序,分桶,而且他们有相同数量的桶,可以使用排序-合并 join。每个mapper,相关的桶会做连接。如果A和B有4个桶 hive> SELECT /*+ MAPJOIN(b) */ a.key, a.value FROM A a join B b on a.key = b.key 只能在mapper使用。使用A的桶的mapper,也会遍历B相关的桶。这个不是默认行为,需要配置以下参数: hive> set hive.input.format=org.apache.hadoop.hive.ql.io.BucketizedHiveInputFormat; hive> set hive.optimize.bucketmapjoin = true; hive> set hive.optimize.bucketmapjoin.sortedmerge = true;
复制代码

 

Hive 内置操作符与函数

 1)字符串长度函数:length

2)字符串反转函数:reverse

 3)字符串连接函数:concat

 4)带分隔符字符串连接函数:concat_ws

 5)字符串截取函数:substr,substring

  7)字符串转大写函数:upper,ucase

 8)字符串转小写函数:lower,lcase

9)去空格函数:trim

 10)左边去空格函数:ltrim

11)右边去空格函数:rtrim

集合统计函数

 1) 个数统计函数 count。

 2) 总和统计函数 sum。

 3) 平均值统计函数avg。

4) 最小值统计函数 min。统计结果集中 col 字段的最小值。

 5) 最大值统计函数 max。统计结果集中 col 字段的最大值。

 

复合类型操作

 1) Map 类型构建。根据输入的 Key-Value 对构建 Map 类型。

语法:map(key1, value1, key2, value2,...)
举例:
hive>create table map_test as select map('100','jay','200','liu') from student;
hive>describe map_test;
hive>select map_test from student;

 2) Struct 类型构建。根据输入的参数构建结构体 Struct 类型。

语法:struct(val1, val2, val3, ...)
举例:
hive>create table struct_test as select struct('jay','liu','gang') from student;
hive>describe struct_test;
hive>select struct_test from student;

3) Array 类型构建。根据输入的参数构建数组 Array 类型。

语法:array(val1,val2, ...)
举例:
hive> create table array_test as select array('jay','liu','gang') from student;
hive> describe array_test;
hive> select array_test from array_test;

用户自定义函数 UDF

 UDF(User Defined Function,用户自定义函数) 对数据进行处理。UDF 函数可以直接应用于 select 语句,对查询结构做格式化处理后,再输出内容。

 Hive可以允许用户编写自己定义的函数UDF,来在查询中使用。Hive中有3种UDF:

 1)UDF:操作单个数据行,产生单个数据行。

 2)UDAF:操作多个数据行,产生一个数据行。

 3)UDTF:操作一个数据行,产生多个数据行一个表作为输出。

 用户构建的UDF使用过程如下:

 第一步:继承UDF或者UDAF或者UDTF,实现特定的方法。

 第二步:将写好的类打包为jar。如hivefirst.jar。

 第三步:进入到Hive外壳环境中,利用add jar /home/hadoop/hivefirst.jar 注册该jar文件。

 第四步:为该类起一个别名,create temporary function mylength as 'com.whut.StringLength';这里注意UDF只是为这个Hive会话临时定义的。

 第五步:在select中使用mylength()。

自定义UDF

复制代码
package whut;
import org.apache.commons.lang.StringUtils;
import org.apache.hadoop.hive.ql.exec.UDF;
import org.apache.hadoop.io.Text;
//UDF是作用于单个数据行,产生一个数据行
//用户必须要继承UDF,且必须至少实现一个evalute方法,该方法并不在UDF中
//但是Hive会检查用户的UDF是否拥有一个evalute方法
public class Strip extends UDF{
    private Text result=new Text();
    //自定义方法
    public Text evaluate(Text str)
    {
        if(str==null)
        return null;
        result.set(StringUtils.strip(str.toString()));
        return result;
    }
    public Text evaluate(Text str,String stripChars)
    {
        if(str==null)
        return null;
        result.set(StringUtils.strip(str.toString(),stripChars));
        return result;
    }
}
复制代码

注意事项:

 1、一个用户UDF必须继承org.apache.hadoop.hive.ql.exec.UDF;

 2、一个UDF必须要包含有evaluate()方法,但是该方法并不存在于UDF中。evaluate的参数个数以及类型都是用户自己定义的。在使用的时候,Hive会调用UDF的evaluate()方法。

 

自定义UDAF找到最大值

复制代码
package whut;
import org.apache.hadoop.hive.ql.exec.UDAF;
import org.apache.hadoop.hive.ql.exec.UDAFEvaluator;
import org.apache.hadoop.io.IntWritable;
//UDAF是输入多个数据行,产生一个数据行
//用户自定义的UDAF必须是继承了UDAF,且内部包含多个实现了exec的静态类
public class MaxiNumber extends UDAF{
    public static class MaxiNumberIntUDAFEvaluator implements UDAFEvaluator{
    //最终结果
    private IntWritable result;
    //负责初始化计算函数并设置它的内部状态,result是存放最终结果的
    @Override
    public void init() {
        result=null;
    }
    //每次对一个新值进行聚集计算都会调用iterate方法
    public boolean iterate(IntWritable value)
    {
        if(value==null)
            return false;
        if(result==null)
            result=new IntWritable(value.get());
        else
            result.set(Math.max(result.get(), value.get()));
        return true;
    }
     
    //Hive需要部分聚集结果的时候会调用该方法
    //会返回一个封装了聚集计算当前状态的对象
    public IntWritable terminatePartial()
    {
        return result;
    }
    //合并两个部分聚集值会调用这个方法
    public boolean merge(IntWritable other)
    {
        return iterate(other);
    }
    //Hive需要最终聚集结果时候会调用该方法
    public IntWritable terminate()
    {
        return result;
    }
    }
}
复制代码

 注意事项:

 1、用户的UDAF必须继承了org.apache.hadoop.hive.ql.exec.UDAF。 

 2、用户的UDAF必须包含至少一个实现了org.apache.hadoop.hive.ql.exec的静态类,诸如常见的实现了 UDAFEvaluator。 

 3、一个计算函数必须实现的5个方法的具体含义如下: 

  init():主要是负责初始化计算函数并且重设其内部状态,一般就是重设其内部字段。一般在静态类中定义一个内部字段来存放最终的结果。 

  iterate():每一次对一个新值进行聚集计算时候都会调用该方法,计算函数会根据聚集计算结果更新内部状态。当输入值合法或者正确计算了,则就返回true。 

  terminatePartial():Hive需要部分聚集结果的时候会调用该方法,必须要返回一个封装了聚集计算当前状态的对象。 

  merge():Hive进行合并一个部分聚集和另一个部分聚集的时候会调用该方法。 

  terminate():Hive最终聚集结果的时候就会调用该方法。计算函数需要把状态作为一个值返回给用户。 

 4、部分聚集结果的数据类型和最终结果的数据类型可以不同。

 

Hive 的权限控制

 Hive从0.10可以通过元数据控制权限。但是Hive的权限控制并不是完全安全的。基本的授权方案的目的是防止用户不小心做了不合适的事情。

 为了使用Hive的授权机制,有两个参数必须在hive-site.xml中设置:

复制代码
< property> 
    < name>hive.security.authorization.enabled< /name> 
    < value>true< /value> 
     < description>enable or disable the hive client authorization< /description> 
 < /property> 
 
< property> 
     < name>hive.security.authorization.createtable.owner.grants< /name> 
     < value>ALL< /value> 
     < description>the privileges automatically granted to the owner whenever a table gets created. An example like "select,drop" will grant select and drop privilege to the owner of the table< /description>
< /property>
复制代码

 hive.security.authorization.enabled //参数是开启权限验证,默认为 false。

 hive.security.authorization.createtable.owner.grants //参数是指表的创建者对表拥有所有权限。

角色的创建和删除

 Hive 中的角色定义与关系型数据库中角色的定义类似,它是一种机制,给予那些没有适当权限的用户分配一定的权限。

复制代码
 1) 创建角色。
语法:hive> create role role_name;
示例:hive> create role role_tes1;

 2) 删除角色。
语法:drop role role_name
示例:drop role role_test1;

角色的授权和撤销
 1) 把 role_test1 角色授权给 xiaojiang 用户,命令如下。
hive> grant role role_test1 to user xiaojiang;

 2) 查看 xiaojiang 用户被授权的角色,命令如下。
show role grant user xiaojiang;

 3) 取消 xiaojiang 用户的 role_test1 角色,命令如下。
hive> revoke role role_test1 from user xiaojiang;

Hive 支持的权限控制。
 1) 把 select 权限授权给 xiaojiang 用户,命令如下。
hive> grant select on database default to user xiaojiang;

 2) 查看 xiaojiang 被授予那些操作权限,命令如下。
hive> show grant user xiaojiang on database default;

 3) 收回 xiaojiang 的 select 权限,操作如下。
hive> revoke select on database default from user xiaojiang;

 4) 查看 xiaojiang 用户拥有哪些权限,命令如下。
hive> show grant user xiaojiang on database default;
复制代码

超级管理权限

 HIVE本身有权限管理功能,需要通过配置开启。

复制代码
< property> 
    < name>hive.metastore.authorization.storage.checks< /name>
    < value>true< /value>
< /property>

< property>
    < name>hive.metastore.execute.setugi< /name>
    < value>false< /value>
< /property>

< property>
    < name>hive.security.authorization.enabled< /name>
    < value>true< /value>
< /property>

< property>
    < name>hive.security.authorization.createtable.owner.grants< /name>
    < value>ALL< /value>
< /property>
复制代码

 

 其中hive.security.authorization.createtable.owner.grants设置成ALL表示用户对自己创建的表是有所有权限的(这样是比较合理地)。

 开启权限控制有Hive的权限功能还有一个需要完善的地方,那就是“超级管理员”。 Hive中没有超级管理员,任何用户都可以进行Grant/Revoke操作,为了完善“超级管理员”,必须添加hive.semantic.analyzer.hook配置,并实现自己的权限控制类。

 编写权限控制类,代码如下所示。

复制代码
package com.xxx.hive;
import org.apache.hadoop.hive.ql.parse.ASTNode;
import org.apache.hadoop.hive.ql.parse.AbstractSemanticAnalyzerHook;
import org.apache.hadoop.hive.ql.parse.HiveParser;
import org.apache.hadoop.hive.ql.parse.HiveSemanticAnalyzerHookContext;
import org.apache.hadoop.hive.ql.parse.SemanticException;
import org.apache.hadoop.hive.ql.session.SessionState;
/**   
    * 设置Hive超级管理员   *   
    * @author   
    * @version $Id: AuthHook.java,v 0.1 2013-6-13 下午3:32:12 yinxiu Exp $  
    */  
public class AuthHook extends AbstractSemanticAnalyzerHook { 
    private static String admin = "admin";
    @Override 27 public ASTNode preAnalyze(HiveSemanticAnalyzerHookContext context, 28 ASTNode ast) throws SemanticException {
    switch (ast.getToken().getType()) { 
        case HiveParser.TOK_CREATEDATABASE: 
        case HiveParser.TOK_DROPDATABASE: 
        case HiveParser.TOK_CREATEROLE: 
        case HiveParser.TOK_DROPROLE:
        case HiveParser.TOK_GRANT: 
        case HiveParser.TOK_REVOKE: 
        case HiveParser.TOK_GRANT_ROLE: 
        case HiveParser.TOK_REVOKE_ROLE: 
        String userName = null;
        if (SessionState.get() != null  && SessionState.get().getAuthenticator() != null) {
            userName = SessionState.get().getAuthenticator().getUserName();
        } 
        if (!admin.equalsIgnoreCase(userName)) { 
            throw new SemanticException(userName + " can't use ADMIN options, except " + admin + ".");
         } 
        break;
        default: 
        break;
        }
        return ast;
    } 
}
复制代码

 添加了控制类之后还必须添加下面的配置:

< property> 
    < name>hive.semantic.analyzer.hook< /name> 
    < value>com.xxx.AuthHook< /value>  
< /property>

若有使用hiveserver,hiveserver必须重启。

 至此,只有admin用户可以进行Grant/Revoke操作。
 权限操作示例:

grant select on database default to user xiaojiang;
revoke all on database default from user xiaojiang;
show grant user xiaojiang on database default;

Hive与JDBC示例

 在使用 JDBC 开发 Hive 程序时, 必须首先开启 Hive 的远程服务接口。使用下面命令进行开启:

hive -service hiveserver &  //Hive低版本提供的服务是:hiveserver
hive --service hiveserver2 &    //Hive0.11.0以上版本提供了的服务是:hiveserver2

本课程我们使用的hive1.0版本,故我们使用hiveserver2服务,下面我使用 Java 代码通过JDBC连接Hiveserver。

1) 测试数据

 本地目录/home/hadoop/下的djt.txt文件内容(每行数据之间用tab键隔开)如下所示:

1    dajiangtai
2    hadoop
3    hive
4    hbase
5    spark

 2) 程序代码

复制代码
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class Hive {
    private static String driverName = "org.apache.hive.jdbc.HiveDriver";//hive驱动名称
    private static String url = "jdbc:hive2://djt11:10000/default";//连接hive2服务的连接地址,Hive0.11.0以上版本提供了一个全新的服务:HiveServer2
    private static String user = "hadoop";//对HDFS有操作权限的用户
    private static String password = "";//在非安全模式下,指定一个用户运行查询,忽略密码
    private static String sql = "";
    private static ResultSet res;
    public static void main(String[] args) {
        try {
            Class.forName(driverName);//加载HiveServer2驱动程序
            Connection conn = DriverManager.getConnection(url, user, password);//根据URL连接指定的数据库
            Statement stmt = conn.createStatement();
            
            //创建的表名
            String tableName = "testHiveDriverTable";
            
            /** 第一步:表存在就先删除 **/
            sql = "drop table " + tableName;
            stmt.execute(sql);
            
            /** 第二步:表不存在就创建 **/
            sql = "create table " + tableName + " (key int, value string)  row format delimited fields terminated by '\t' STORED AS TEXTFILE";
            stmt.execute(sql);
            
            // 执行“show tables”操作
            sql = "show tables '" + tableName + "'";
            res = stmt.executeQuery(sql);
            if (res.next()) {
                System.out.println(res.getString(1));
            }
            
            // 执行“describe table”操作
            sql = "describe " + tableName;
            res = stmt.executeQuery(sql);
            while (res.next()) {  
                System.out.println(res.getString(1) + "\t" + res.getString(2));
            }
            
            // 执行“load data into table”操作
            String filepath = "/home/hadoop/djt.txt";//hive服务所在节点的本地文件路径
            sql = "load data local inpath '" + filepath + "' into table " + tableName;
            stmt.execute(sql);
            
            // 执行“select * query”操作
            sql = "select * from " + tableName;
            res = stmt.executeQuery(sql);
            while (res.next()) {
                System.out.println(res.getInt(1) + "\t" + res.getString(2));
            }
            
            // 执行“regular hive query”操作,此查询会转换为MapReduce程序来处理
            sql = "select count(*) from " + tableName;
            res = stmt.executeQuery(sql);
            while (res.next()) {
                System.out.println(res.getString(1));
            }        
            conn.close();
            conn = null;
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            System.exit(1);
        } catch (SQLException e) {
            e.printStackTrace();
            System.exit(1);
        }
    }
}
复制代码
复制代码
3) 运行结果(右击-->Run as-->Run on Hadoop)

 执行“show tables”运行结果:

testhivedrivertable
 执行“describe table”运行结果:

key    int
value    string
 执行“select * query”运行结果:

1    dajiangtai
2    hadoop
3    hive
4    hbase
5    spark
 执行“regular hive query”运行结果:

5
复制代码

hive性能调优

hive性能调优

(一)Hadoop 计算框架的特性

什么是数据倾斜

由于数据的不均衡原因,导致数据分布不均匀,造成数据大量的集中到一点,造成数据热点

Hadoop框架的特性

不怕数据大,怕数据倾斜

jobs数比较多的作业运行效率相对比较低,比如即使有几百行的表,如果多次关联多次汇总,产生十几个jobs,耗时很长。原因是map reduce作业初始化的时间是比较长的

sum,count,max,min等UDAF,不怕数据倾斜问题,hadoop在map端的汇总合并优化,使数据倾斜不成问题

count(distinct ),在数据量大的情况下,效率较低,因为count(distinct)是按group by 字段分组,按distinct字段排序,一般这种分布方式是很倾斜的

(二)优化的常用手段

解决数据倾斜问题

减少job数

设置合理的map reduce的task数,能有效提升性能。

了解数据分布,自己动手解决数据倾斜问题是个不错的选择

数据量较大的情况下,慎用count(distinct)。

对小文件进行合并,是行至有效的提高调度效率的方法。

优化时把握整体,单个作业最优不如整体最优。

 

(三)Hive的数据类型方面的优化

优化原则

按照一定规则分区(例如根据日期)。通过分区,查询的时候指定分区,会大大减少在无用数据上的扫描, 同时也非常方便数据清理。

合理的设置Buckets。在一些大数据join的情况下,map join有时候会内存不够。如果使用Bucket Map Join的话,可以只把其中的一个bucket放到内存中,内存中原来放不下的内存表就变得可以放下。这需要使用buckets的键进行join的条件连结,并且需要如下设置

set hive.optimize.bucketmapjoin = true

(四)Hive的操作方面的优化

(1)全排序

Hive的排序关键字是SORT BY,它有意区别于传统数据库的ORDER BY也是为了强调两者的区别–SORT BY只能在单机范围内排序

(2)怎样做笛卡尔积

当Hive设定为严格模式(hive.mapred.mode=strict)时,不允许在HQL语句中出现笛卡尔积

MapJoin是的解决办法

MapJoin,顾名思义,会在Map端完成Join操作。这需要将Join操作的一个或多个表完全读入内存

MapJoin的用法是在查询/子查询的SELECT关键字后面添加/*+ MAPJOIN(tablelist) */提示优化器转化为MapJoin(目前Hive的优化器不能自动优化MapJoin)

其中tablelist可以是一个表,或以逗号连接的表的列表。tablelist中的表将会读入内存,应该将小表写在这里

在大表和小表做笛卡尔积时,规避笛卡尔积的方法是,给Join添加一个Join key,原理很简单:将小表扩充一列join key,并将小表的条目复制数倍,join key各不相同;将大表扩充一列join key为随机数

(3)控制Hive的Map数

通常情况下,作业会通过input的目录产生一个或者多个map任务

主要的决定因素有: input的文件总个数,input的文件大小,集群设置的文件块大小(目前为128M, 可在hive中通过set dfs.block.size;命令查看到,该参数不能自定义修改)

是不是map数越多越好

答案是否定的。如果一个任务有很多小文件(远远小于块大小128m),则每个小文件也会被当做一个块,用一个map任务来完成,而一个map任务启动和初始化的时间远远大于逻辑处理的时间,就会造成很大的资源浪费。而且,同时可执行的map数是受限的。

是不是保证每个map处理接近128m的文件块,就高枕无忧了?

答案也是不一定。比如有一个127m的文件,正常会用一个map去完成,但这个文件只有一个或者两个小字段,却有几千万的记录,如果map处理的逻辑比较复杂,用一个map任务去做,肯定也比较耗时。

针对上面的问题3和4,我们需要采取两种方式来解决:即减少map数和增加map数;

举例

a) 假设input目录下有1个文件a,大小为780M,那么hadoop会将该文件a分隔成7个块(6个128m的块和1个12m的块),从而产生7个map数

b)假设input目录下有3个文件a,b,c,大小分别为10m,20m,130m,那么hadoop会分隔成4个块(10m,20m,128m,2m),从而产生4个map数

即如果文件大于块大小(128m),那么会拆分,如果小于块大小,则把该文件当成一个块

(4)怎样决定reducer个数

Hadoop MapReduce程序中,reducer个数的设定极大影响执行效率

不指定reducer个数的情况下,Hive会猜测确定一个reducer个数,基于以下两个设定:

参数1:hive.exec.reducers.bytes.per.reducer(默认为1G)

参数2 :hive.exec.reducers.max(默认为999)

计算reducer数的公式

N=min(参数2,总输入数据量/参数1)

依据Hadoop的经验,可以将参数2设定为0.95*(集群中TaskTracker个数)

reduce个数并不是越多越好

同map一样,启动和初始化reduce也会消耗时间和资源;

另外,有多少个reduce,就会有多少个输出文件,如果生成了很多个小文件,那么如果这些小文件作为下一个任务的输入,则也会出现小文件过多的问题

什么情况下只有一个reduce

很多时候你会发现任务中不管数据量多大,不管你有没有设置调整reduce个数的参数,任务中一直都只有一个reduce任务;

其实只有一个reduce任务的情况,除了数据量小于

hive.exec.reducers.bytes.per.reducer参数值的情况外,还有以下原因:

a)没有group by的汇总

b)用了Order by

(5)合并 MapReduce 操作

Multi-group by

Multi-group by是Hive的一个非常好的特性,它使得Hive中利用中间结果变得非常方便

FROM log

insert overwrite table test1 select log.id group by log.id

insert overwrite table test2 select log.name group by log.name

上述查询语句使用了Multi-group by特性连续group by了2次数据,使用不同的group by key。这一特性可以减少一次MapReduce操作。

Bucket 与 Sampling

Bucket是指将数据以指定列的值为key进行hash,hash到指定数目的桶中。这样就可以支持高效采样了

Sampling可以在全体数据上进行采样,这样效率自然就低,它还是要去访问所有数据。而如果一个表已经对某一列制作了bucket,就可以采样所有桶中指定序号的某个桶,这就减少了访问量。

如下例所示就是采样了test中32个桶中的第三个桶。

SELECT * FROM test 、、、TABLESAMPLE(BUCKET 3 OUT OF 32);

(6)JOIN 原则

在使用写有 Join 操作的查询语句时有一条原则:应该将条目少的表/子查询放在 Join 操作符的左边

原因是在 Join 操作的 Reduce 阶段,位于 Join 操作符左边的表的内容会被加载进内存,将条目少的表放在左边,可以有效减少发生 OOM 错误的几率

Map Join

Join 操作在 Map 阶段完成,不再需要Reduce,前提条件是需要的数据在 Map 的过程中可以访问到

例如:

INSERT OVERWRITE TABLE phone_traffic

SELECT /*+ MAPJOIN(phone_location) */ l.phone,p.location,l.traffic from phone_location p join log l on (p.phone=l.phone)

相关的参数为:

hive.join.emit.interval = 1000 How many rows in the right-most join operand Hive should buffer before emitting the join result.

hive.mapjoin.size.key = 10000

hive.mapjoin.cache.numrows = 10000

(7)Group By

Map 端部分聚合

并不是所有的聚合操作都需要在 Reduce 端完成,很多聚合操作都可以先在 Map 端进行部分聚合,最后在 Reduce 端得出最终结果

基于 Hash

参数包括:

hive.map.aggr = true 是否在 Map 端进行聚合,默认为 True

hive.groupby.mapaggr.checkinterval = 100000 在 Map 端进行聚合操作的条目数目

有数据倾斜的时候进行负载均衡

hive.groupby.skewindata = false

当选项设定为 true,生成的查询计划会有两个 MR Job。第一个 MR Job 中,Map 的输出结果集合会随机分布到 Reduce 中,每个 Reduce 做部分聚合操作,并输出结果,这样处理的结果是相同的 Group By Key 有可能被分发到不同的 Reduce 中,从而达到负载均衡的目的;第二个 MR Job 再根据预处理的数据结果按照 Group By Key 分布到 Reduce 中(这个过程可以保证相同的 Group By Key 被分布到同一个 Reduce 中),最后完成最终的聚合操作。

(8)合并小文件

文件数目过多,会给 HDFS 带来压力,并且会影响处理效率,可以通过合并 Map 和 Reduce 的结果文件来消除这样的影响:

hive.merge.mapfiles = true 是否和并 Map 输出文件,默认为 True

hive.merge.mapredfiles = false 是否合并 Reduce 输出文件,默认为 False

hive.merge.size.per.task = 256*1000*1000 合并文件的大小

展开阅读全文

没有更多推荐了,返回首页