Hive 基本语法

Hive 操作

（一）表操作

        Hive 和 Mysql 的表操作语句类似，如果熟悉 Mysql，学习Hive 的表操作就非常容易了，下面对Hive的表操作进行深入讲解。

(1)先来创建一个表名为student1的内部：

hive> CREATE TABLE IF NOT EXISTS student1;

建表规则如下：

•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 。

（2）创建外部表

（3）删除表

首先创建一个表名为test1的表

hive> CREATE TABLE IF NOT EXISTS test1;

然后查看一下是否有test1表

hive> SHOW TABLES;

用命令删test1表

hive> DROP TABLE test1;

查看test1表是否删除

hive> SHOW TABLES;

（4）修改表的结构，比如为表增加字段

首先看一下student1表的结构：

为表student1增加两个字段：

hive> ALTER TABLE student1 ADD COLUMNS  

    > (address STRING,grade STRING);

OK

Time taken: 0.241 seconds

再查看一下表的结构，看是否增加：

（5）修改表名student1为student3

hive> ALTER TABLE student1 RENAME TO student3;

查看一下：

下面我们再改回来：

hive> ALTER TABLE student3 RENAME TO student1;

查看一下：

（6）创建和已知表相同结构的表

hive> CREATE TABLE copy_student1 LIKE student1;

查看一下：

2、加入导入数据的方法，（数据里可以包含重复记录），只有导入了数据，才能供后边的查询使用

（1）加载本地数据load

首先看一下表的结构：

创建/home/hadoop/data目录，并在该目录下创建student1.txt文件，添加如下内容：

加载数据到student1表中：

查看是否加载成功：

hive> SELECT * FROM student1;

（2）加载hdfs中的文件

首先将文件student1.txt上传到hdfs文件系统对应目录上：

[hadoop@dwq01 hadoop]$ hadoop fs -put /home/hadoop/data/student1.txt /user/hive

加载hdfs中的文件数据到copy_student1表中：

查看是否加载成功：

hive> SELECT * FROM copy_student1;

（3）表插入数据（单表插入、多表插入）

1）单表插入

首先创建一个表copy_student2,表结构和student1相同：

hive> CREATE TABLE copy_student2 LIKE student1;

查看一下是否创建成功：

hive> SHOW TABLES;

看一下copy_student2表的表结构：

hive> DESC copy_student2;

把表student1中的数据插入到copy_student2表中：

hive> INSERT OVERWRITE TABLE copy_student2 SELECT * FROM student1;

查看数据是否插入：

hive> SELECT * FROM copy_student2;

2）多表插入

先创建两个表：

向多表插入数据：

hive> FROM student1                                       

    > INSERT OVERWRITE TABLE copy_student3

    > SELECT *                            

    > INSERT OVERWRITE TABLE copy_student4

    > SELECT *;

查看结果：

hive> SELECT * FROM copy_student3;

hive> SELECT * FROM copy_student4;

3、有关表的内容的查询

（1）查表的所有内容

hive> SELECT * FROM student1;

（2）查表的某个字段的属性

hive> SELECT sname FROM student1;

（3）where条件查询

hive> SELECT * FROM student1 WHERE sno>201501004 AND address="北京";

（4）all和distinct的区别（这就要求表中要有重复的记录，或者某个字段要有重复的数据）：

（5）limit限制查询

hive> SELECT * FROM student1 LIMIT 4;

(6) GROUP BY 分组查询

group by 分组查询在数据统计时比较常用，接下来讲解 group by 的使用。

1) 创建一个表 group_test，表的内容如下：

hive> create table group_test(uid STRING,gender STRING,ip STRING) row format delimited fields terminated by '\t'  STORED AS TEXTFILE;

向 group_test 表中导入数据。

hive> LOAD DATA LOCAL INPATH '/home/hadoop/xxx/user.txt'  INTO TABLE group_test;

2) 计算表的行数命令如下。

hive> select count(*) from group_test;

3) 根据性别计算去重用户数。

首先创建一个表 group_gender_sum：

hive> create table group_gender_sum(gender STRING,sum INT);

将表 group_test 去重后的数据导入表 group_gender_sum：

hive> insert overwrite table group_gender_sum select group_test.gender,count(distinct group_test.uid) from group_test group by group_test.gender;

同时可以做多个聚合操作，但是不能有两个聚合操作有不同的 distinct 列。下面正确合法的聚合操作语句。

首先创建一个表 group_gender_agg：

hive> create table group_gender_agg(gender STRING,sum1 INT,sum2 INT,sum3 INT);

将表 group_test 聚合后的数据插入表 group_gender_agg：

hive> insert overwrite table group_gender_agg select group_test.gender,count(distinct group_test.uid),count(*),sum(distinct group_test.uid) 

from group_test group by group_test.gender;

但是，不允许在同一个查询内有多个 distinct 表达式。下面的查询是不允许的：

hive> insert overwrite table group_gender_agg select group_test.gender,count(distinct group_test.uid),count(distinct group_test.ip) from group_test group by group_test.gender;

这条查询语句是不合法的，因为 distinct group_test.uid 和 distinct group_test.ip 操作了uid 和 ip 两个不同的列。

(7) ORDER BY 排序查询

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;

(8) SORT BY 查询

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 ;

(9) DISTRIBUTE BY 排序查询

按照指定的字段对数据划分到不同的输出 Reduce 文件中，操作如下：

hive> insert overwrite local directory '/home/hadoop/xxx/test' select * from group_test distribute by length(gender);

此方法根据 gender 的长度划分到不同的 Reduce 中，最终输出到不同的文件中。length 是内建函数，也可以指定其它的函数或者使用自定义函数：

hive> insert overwrite local directory '/home/hadoop/xxx/test' select * from group_test order by gender  distribute by length(gender);

order by gender 与 distribute by length(gender) 不能共用。

(10) CLUSTER BY 查询

cluster by 除了具有 distribute by 的功能外还兼具 sort by 的功能。

(二)视图操作

1) 创建一个测试表：

hive> create table test(id int,name string);

2) 基于表 test 创建一个 test_view 视图。

hive> create view test_view(id,name_length) as select id,length(name) from test;

3) 查看 test_view 视图属性。

hive> desc test_view;

4) 查看视图结果。

hive> select * from test_view;

（三）索引操作

1) Hive 创建索引。

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;

2) 更新数据。

hive> alter index user_index on user rebuild;

3) 删除索引

hive> drop index user_index on user;

4) 查看索引

hive> show index on user;

5) 创建表和索引案例

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

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

6) 创建一个临时索引表 index_tmp。

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

7) 加载本地数据到 index_tmp 表中。

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

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

hive> set hive.exec.dynamic.partition.mode=nonstrict;----设置所有列为 dynamic partition

hive> set hive.exec.dynamic.partition=true;----使用动态分区

8) 查询index_tmp 表中的数据，插入 table_test 表中。

hive> insert overwrite table index_test partition(dt) select id,name,dt from index_tmp;

9) 使用 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 DEFERRED REBUILD;

10) 填充索引数据。

hive> alter index index1_index_test on index_test rebuild;

11) 查看创建的索引。

hive> show index on index_test

12) 查看分区信息。

hive> show partitions index_test;

13) 查看索引数据。

$ hadoop fs -ls /usr/hive/warehouse/default_index_test_index1_index_test_

14) 删除索引。

hive> drop index index1_index_test on index_test;

show index on index_test;

15) 索引数据也被删除。

$ hadoop fs -ls /usr/hive/warehouse/default_index_test_index1_index_test_

no such file or directory

&16) 修改配置文件信息：

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 是 副层次。

2、 插入数据

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

1) 查看 partition_test_input 表的结构，命令如下：

hive> desc partition_test_input;

2) 查看 partition_test_input 的数据，命令如下：

hive> select * from partition_test_input;

3) 向 partition_test 的分区中插入数据，命令如下：

向多个分区插入数据，命令如下：

3、 动态分区

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

动态分区可以通过下面的设置来打开：

set hive.exec.dynamic.partition=true;  

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

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

hive> insert overwrite table partition_test partition(stat_date='2019-01-18',province)

select member_id,name province from partition_test_input where stat_date='2019-01-18';

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

尽量让分区列的值相同的数据在同一个 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），桶操作是通过 Partition 的 CLUSTERED BY 实现的，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)

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) 建表

 2) 导入数据

hive> LOAD DATA LOCAL INPATH '/home/hadoop/xxx/test5.txt' INTO TABLE student_test;

 3) 查询数据：

hive> select info.age from student_test;  

2、Array使用

1) 建表

 2) 导入数据

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) 建表

 2) 导入数据

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='2008-07-07' AND b.ds='2008-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='2008-07-07' AND a.ds='2008-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 内置操作符与函数

字符串函数：

用户自定义函数 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

注意事项：

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

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

 自定义UDAF找到最大值

注意事项：

 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中设置：

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本身有权限管理功能，需要通过配置开启：

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

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

编写权限控制类，代码如下：

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

至此，只有admin用户可以进行Grant/Revoke操作;

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/下的xxx.txt文件内容(每行数据之间用tab键隔开)如下所示:

2) 程序代码：

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只能在单机范围内排序

用户的原始数据如下所示:

转化之后的数据如下所示，每个字段我们使用"@"分割符号：

1370695139@03111108020232488@2012-09-21@BTV影视@再回首@13:50:10@13:55:11@301

上面的字段分别代表：机顶盒号、用户编号、收看日期、频道、栏目、起始时间、结束时间、收视时长。

步骤2：创建hive表

我们根据对应字段，使用hive创建表：

create table tvdata(cardnum string,stbnum string,date string,sn string,p string ,s string,e string,duration int) row format delimited fields terminated by '@' stored as textfile; 

步骤3：将hdfs中的数据导入表中

我们使用以下命令，将hdfs中的数据导入表中：

load data inpath '/media/tvdata/part-r-00000' into table tvdata;

步骤4：编写HQL，分析数据

使用HQL语句，统计每个的频道的人均收视时长：

select sn,sum(duration)/count(*) from tvdata group by sn;

这里我们使用HQL只是从一个角度分析数据，大家可以尝试从多个角度来分析数据。