Hive基本语法操练
建表规则如下：
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可以进一步组织成桶，也就是说桶是更为细粒度的数据范围划分。Hive是针对某一列进行分桶。Hive采用对列值哈希，然后除以桶的个数求余的方式决定该条记录存放在哪个桶中。分桶的好处是可以获得更高的查询处理效率。使取样更高效。
分桶其实就是把大表化成了“小表”，然后 Map-Side Join 解决之，这是用来解决大表与小表之间的连接问题。将桶中的数据按某列进行排序会提高查询效率。
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 合并文件的大小
