Hive中sql的使用

在hive的使用中写了不少常用的hql，但在这里更系统的写完sql的基本操作。

1、创建表
建表语法

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]

创建测试使用的数据库myhive3,使用该数据库。
1）、创建普通表

0: jdbc:hive2://localhost:10000> create database myhive3;No rows affected (0.204 seconds)0: jdbc:hive2://localhost:10000> use myhive3;No rows affected (0.13 seconds)0: jdbc:hive2://localhost:10000> create table t1(id int,name string)0: jdbc:hive2://localhost:10000> row format delimited fields terminated by ',';//指定，分割，具体的参考前面说的那篇No rows affected (0.117 seconds)0: jdbc:hive2://localhost:10000> show tables ;+-----------+--+| tab_name  |+-----------+--+| t1        |+-----------+--+0: jdbc:hive2://localhost:10000> desc t1;+-----------+------------+----------+--+| col_name  | data_type  | comment  |+-----------+------------+----------+--+| id        | int        |          || name      | string     |          |+-----------+------------+----------+--+

2）、创建外部表
EXTERNAL关键字可以让用户创建一个外部表，在建表的同时指定一个指向实际数据的路径（LOCATION），Hive 创建内部表时，会将数据移动到数据仓库指向的路径；若创建外部表，仅记录数据所在的路径，不对数据的位置做任何改变。在删除表的时候，内部表的元数据和数据会被一起删除，而外部表只删除元数据，不删除数据。
STORED AS
SEQUENCEFILE|TEXTFILE|RCFILE
如果文件数据是纯文本，可以使用 STORED AS TEXTFILE。如果数据需要压缩，使用 STORED AS SEQUENCEFILE。
location当然是指定表（hdfs上）位置

0: jdbc:hive2://localhost:10000> create external table t2(id int,name string)0: jdbc:hive2://localhost:10000> row format delimited fields terminated by ','0: jdbc:hive2://localhost:10000> stored as textfile0: jdbc:hive2://localhost:10000> location '/mytable2';No rows affected (0.133 seconds)

页面查看是否创建了该表

直接创建在根目录下的，区别于普通表创建在/user/hive/warehouse目录下。
3）、创建分区
创建分区，分区字段fields string，查看表信息的时候会显示该表下所有分区信息的。

0: jdbc:hive2://localhost:10000> create table t3(id int,name string)0: jdbc:hive2://localhost:10000> partitioned by(fields string)0: jdbc:hive2://localhost:10000> row format delimited fields terminated by ',';No rows affected (0.164 seconds)0: jdbc:hive2://localhost:10000> load data local inpath '/root/sz.data' into table t3 partition (fields ='Chengdu');INFO  : Loading data to table myhive3.t3 partition (fields=Chengdu) from file:/root/sz.dataINFO  : Partition myhive3.t3{fields=Chengdu} stats: [numFiles=1, numRows=0, totalSize=91, rawDataSize=0]No rows affected (0.738 seconds)0: jdbc:hive2://localhost:10000> load data local inpath '/root/sz.data' into table t3 partition (fields ='Wuhan');INFO  : Loading data to table myhive3.t3 partition (fields=Wuhan) from file:/root/sz.dataINFO  : Partition myhive3.t3{fields=Wuhan} stats: [numFiles=1, numRows=0, totalSize=91, rawDataSize=0]No rows affected (0.608 seconds)0: jdbc:hive2://localhost:10000> select * from t3;+--------+-----------+------------+--+| t3.id  |  t3.name  | t3.fields  |+--------+-----------+------------+--+| 1      | zhangsan  | Chengdu    || 2      | lisi      | Chengdu    || 3      | wangwu    | Chengdu    || 4      | furong    | Chengdu    || 5      | fengjie   | Chengdu    || 6      | aaa       | Chengdu    || 7      | bbb       | Chengdu    || 8      | ccc       | Chengdu    || 9      | ddd       | Chengdu    || 10     | eee       | Chengdu    || 11     | fff       | Chengdu    || 12     | ggg       | Chengdu    || 1      | zhangsan  | Wuhan      || 2      | lisi      | Wuhan      || 3      | wangwu    | Wuhan      || 4      | furong    | Wuhan      || 5      | fengjie   | Wuhan      || 6      | aaa       | Wuhan      || 7      | bbb       | Wuhan      || 8      | ccc       | Wuhan      || 9      | ddd       | Wuhan      || 10     | eee       | Wuhan      || 11     | fff       | Wuhan      || 12     | ggg       | Wuhan      |+--------+-----------+------------+--+

页面查看

这两个分区目录下都存放了文件sz.data。

4）、创建分桶表
这个比较麻烦一些，在hive分桶这篇博文有较详细的讲解。

2、修改表
1）、增加、删除表分区
语法

增加ALTER TABLE table_name ADD [IF NOT EXISTS] partition_spec [ LOCATION 'location1' ] partition_spec [ LOCATION 'location2' ] ...删除ALTER TABLE table_name DROP partition_spec, partition_spec,...

还是对上面的分区表t3
增加分区fields=’Hefei’位置还是跟其他分区一致（可以省略不写）
由于hive客户端命令行可以使用hadoop命令查看文件系统（dfs），后面就不去页面查看了

0: jdbc:hive2://localhost:10000> alter table t3 add partition (fields='Hefei');No rows affected (0.198 seconds)0: jdbc:hive2://localhost:10000> dfs -ls /user/hive/warehouse/myhive3.db/t3;+---------------------------------------------------------------------------------------------------------------+--+|                                                  DFS Output                                                   |+---------------------------------------------------------------------------------------------------------------+--+| Found 3 items                                                                                                 || drwxr-xr-x   - root supergroup          0 2017-10-19 05:17 /user/hive/warehouse/myhive3.db/t3/fields=Chengdu  || drwxr-xr-x   - root supergroup          0 2017-10-19 05:28 /user/hive/warehouse/myhive3.db/t3/fields=Hefei    || drwxr-xr-x   - root supergroup          0 2017-10-19 05:18 /user/hive/warehouse/myhive3.db/t3/fields=Wuhan    |+---------------------------------------------------------------------------------------------------------------+--+0: jdbc:hive2://localhost:10000> alter table t3 drop partition (fields='Hefei');INFO  : Dropped the partition fields=HefeiNo rows affected (0.536 seconds)0: jdbc:hive2://localhost:10000> dfs -ls /user/hive/warehouse/myhive3.db/t3;+---------------------------------------------------------------------------------------------------------------+--+|                                                  DFS Output                                                   |+---------------------------------------------------------------------------------------------------------------+--+| Found 2 items                                                                                                 || drwxr-xr-x   - root supergroup          0 2017-10-19 05:17 /user/hive/warehouse/myhive3.db/t3/fields=Chengdu  || drwxr-xr-x   - root supergroup          0 2017-10-19 05:18 /user/hive/warehouse/myhive3.db/t3/fields=Wuhan    |+---------------------------------------------------------------------------------------------------------------+--+

2）、重命名表
语法

alter table old_name rename to new_name

将t1改名为t4

0: jdbc:hive2://localhost:10000> alter table t1 rename to t4;No rows affected (0.183 seconds)0: jdbc:hive2://localhost:10000> show tables;+-----------+--+| tab_name  |+-----------+--+| t2        || t3        || t4        |+-----------+--+3 rows selected (0.127 seconds)

3）、添加、更新列
语法

alter table table_name add|replace columns(col_name data_type  ...) 

注：ADD是代表新增一字段，字段位置在所有列后面，REPLACE则是表示替换表中所有字段。

0: jdbc:hive2://localhost:10000> desc t4;+-----------+------------+----------+--+| col_name  | data_type  | comment  |+-----------+------------+----------+--+| id        | int        |          || name      | string     |          |+-----------+------------+----------+--+2 rows selected (0.315 seconds)0: jdbc:hive2://localhost:10000> alter table t4 add columns (age int);No rows affected (0.271 seconds)0: jdbc:hive2://localhost:10000> desc t4;+-----------+------------+----------+--+| col_name  | data_type  | comment  |+-----------+------------+----------+--+| id        | int        |          || name      | string     |          || age       | int        |          |+-----------+------------+----------+--+3 rows selected (0.199 seconds)0: jdbc:hive2://localhost:10000> alter table t4 replace columns (no string,name string,scores int);No rows affected (0.406 seconds)0: jdbc:hive2://localhost:10000> desc t4;+-----------+------------+----------+--+| col_name  | data_type  | comment  |+-----------+------------+----------+--+| no        | string     |          || name      | string     |          || scores    | int        |          |+-----------+------------+----------+--+

常用显示命令

show tablesshow databasesshow partitionsshow functionsdesc formatted table_name;//跟desc table_name一样，但是显示的内容更多

3、数据操作
1）、load导入数据
上面已经演示了将本地的文件sz.data导入到t3表中。
load也就是说将文件复制到指定的表（目录）下，指定了local的话那么会去查找本地文件系统中的文件路径。如果没指定会根据inpath指定的路径去查找。如果是hdfs的话，如下格式
hdfs://namenode:9000/user/hive/project/data1。
另外如果使用了 OVERWRITE 关键字，则目标表（或者分区）中的内容会被删除，然后再将 filepath 指向的文件/目录中的内容添加到表/分区中。
如果目标表（分区）已经有一个文件，并且文件名和 filepath 中的文件名冲突，那么现有的文件会被新文件所替代。

0: jdbc:hive2://localhost:10000> load data local inpath '/root/sz.data' overwrite into table t4 ;INFO  : Loading data to table myhive3.t4 from file:/root/sz.dataINFO  : Table myhive3.t4 stats: [numFiles=1, numRows=0, totalSize=91, rawDataSize=0]No rows affected (0.7 seconds)0: jdbc:hive2://localhost:10000> select * from t4;+--------+-----------+------------+--+| t4.no  |  t4.name  | t4.scores  |+--------+-----------+------------+--+| 1      | zhangsan  | NULL       || 2      | lisi      | NULL       || 3      | wangwu    | NULL       || 4      | furong    | NULL       || 5      | fengjie   | NULL       || 6      | aaa       | NULL       || 7      | bbb       | NULL       || 8      | ccc       | NULL       || 9      | ddd       | NULL       || 10     | eee       | NULL       || 11     | fff       | NULL       || 12     | ggg       | NULL       |+--------+-----------+------------+--+

2）、插入语句
向表中插入语句的话
普通插入，查询其他表的表信息插入（自动数量要一致），将查询结果保存到一个目录中（目录会自动创建，由OutputFormat实现）。

 insert into table t4 values('13','zhangsan',99);

0: jdbc:hive2://localhost:10000> truncate table t4;//清空表信息0: jdbc:hive2://localhost:10000> insert into t4 0: jdbc:hive2://localhost:10000> select id,name from t3;0: jdbc:hive2://localhost:10000> select * from t4;+--------+-----------+--+| t4.no  |  t4.name  |+--------+-----------+--+| 1      | zhangsan  || 2      | lisi      || 3      | wangwu    || 4      | furong    || 5      | fengjie   || 6      | aaa       || 7      | bbb       || 8      | ccc       || 9      | ddd       || 10     | eee       || 11     | fff       || 12     | ggg       || 1      | zhangsan  || 2      | lisi      || 3      | wangwu    || 4      | furong    || 5      | fengjie   || 6      | aaa       || 7      | bbb       || 8      | ccc       || 9      | ddd       || 10     | eee       || 11     | fff       || 12     | ggg       |+--------+-----------+--+

重新创建表t5，将表信息保存到本地目录/root/insertDir/test中

0: jdbc:hive2://localhost:10000> insert overwrite local directory '/root/insertDir/test'0: jdbc:hive2://localhost:10000> select * from t5;查看本地[root@mini1 ~]# cd insertDir/test/[root@mini1 test]# ll总用量 4-rw-r--r--. 1 root root 91 10月 19 06:15 000000_0[root@mini1 test]# cat 000000_0 1zhangsan2lisi3wangwu4furong5fengjie6aaa7bbb8ccc9ddd10eee11fff12ggg

4、数据查询SELECT
语法基本跟mysql一样，留意下分桶即可

SELECT [ALL | DISTINCT] select_expr, select_expr, ... FROM table_reference[WHERE where_condition] [GROUP BY col_list [HAVING condition]] [CLUSTER BY col_list   | [DISTRIBUTE BY col_list] [SORT BY| ORDER BY col_list] ] [LIMIT number]

在前面做了很多测试，就不想再重复了，会mysql的查询这个肯定也会。
需要注意的是order by和sort by的区别，在hive分桶这篇中也说过了。
1、order by 会对输入做全局排序，因此只有一个reducer，会导致当输入规模较大时，需要较长的计算时间。
2、sort by不是全局排序，其在数据进入reducer前完成排序。因此，如果用sort by进行排序，并且设置mapred.reduce.tasks>1，则sort by只保证每个reducer的输出有序，不保证全局有序。

主要介绍下join
5、Join查询
join查询其实跟mysql还是一样的
准备数据
a.txt中
1,a
2,b
3,c
4,d
7,y
8,u
b.txt中
2,bb
3,cc
7,yy
9,pp
创建表a和b，将a.txt导入到a表中，b.txt导入到b表中
1）、内连接

0: jdbc:hive2://localhost:10000> create table a(id int,name string)0: jdbc:hive2://localhost:10000> row format delimited fields terminated by ',';No rows affected (0.19 seconds)0: jdbc:hive2://localhost:10000> create table b(id int,name string)0: jdbc:hive2://localhost:10000> row format delimited fields terminated by ',';No rows affected (0.071 seconds)0: jdbc:hive2://localhost:10000> load data local inpath '/root/a.txt' into table a;0: jdbc:hive2://localhost:10000> load data local inpath '/root/b.txt' into table b;0: jdbc:hive2://localhost:10000> select * from a;+-------+---------+--+| a.id  | a.name  |+-------+---------+--+| 1     | a       || 2     | b       || 3     | c       || 4     | d       || 7     | y       || 8     | u       |+-------+---------+--+6 rows selected (0.218 seconds)0: jdbc:hive2://localhost:10000> select * from b;+-------+---------+--+| b.id  | b.name  |+-------+---------+--+| 2     | bb      || 3     | cc      || 7     | yy      || 9     | pp      |+-------+---------+--+4 rows selected (0.221 seconds)0: jdbc:hive2://localhost:10000> select * from a inner join b on a.id = b.id;...+-------+---------+-------+---------+--+| a.id  | a.name  | b.id  | b.name  |+-------+---------+-------+---------+--+| 2     | b       | 2     | bb      || 3     | c       | 3     | cc      || 7     | y       | 7     | yy      |+-------+---------+-------+---------+--+

根据id进行连接，能连接到的则串起来。
2）、左外连接（outer可省）

0: jdbc:hive2://localhost:10000> select * from a left outer join b on a.id = b.id;...+-------+---------+-------+---------+--+| a.id  | a.name  | b.id  | b.name  |+-------+---------+-------+---------+--+| 1     | a       | NULL  | NULL    || 2     | b       | 2     | bb      || 3     | c       | 3     | cc      || 4     | d       | NULL  | NULL    || 7     | y       | 7     | yy      || 8     | u       | NULL  | NULL    |+-------+---------+-------+---------+--+6 rows selected (16.453 seconds)

左边的表内容全列出来，右边的能连上的就显示，不能的则显示null。
右外连接则相反。
3）、全连接full outer

0: jdbc:hive2://localhost:10000> select * from a full outer join b on a.id = b.id;...+-------+---------+-------+---------+--+| a.id  | a.name  | b.id  | b.name  |+-------+---------+-------+---------+--+| 1     | a       | NULL  | NULL    || 2     | b       | 2     | bb      || 3     | c       | 3     | cc      || 4     | d       | NULL  | NULL    || 7     | y       | 7     | yy      || 8     | u       | NULL  | NULL    || NULL  | NULL    | 9     | pp      |+-------+---------+-------+---------+--+

相当于左连接+右连接
4）、semi join

0: jdbc:hive2://localhost:10000> select * from a left semi  join b on a.id = b.id;+-------+---------+--+| a.id  | a.name  |+-------+---------+--+| 2     | b       || 3     | c       || 7     | y       |+-------+---------+--+3 rows selected (17.511 seconds)

相当于左外连接得到的信息的左半部分。
注：可以理解为exist in(…)，但是hive中没有该语法，所以使用LEFT SEMI JOIN代替IN/EXISTS的，前者为后者高效实现。
比如下面的例子

重写以下子查询为LEFT SEMI JOIN  SELECT a.key, a.value  FROM a  WHERE a.key exist in   (SELECT b.key    FROM B);可以被重写为：   SELECT a.key, a.val   FROM a LEFT SEMI JOIN b on (a.key = b.key)