大数据入门第十一天——hive详解（二）基本操作与分区分桶

一、基本操作

　　1.DDL

　　　　官网的DDL语法教程：点击查看

　　　　建表语句

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]

data_type
  : primitive_type
  | array_type
  | map_type
  | struct_type
  | union_type  -- (Note: Available in Hive 0.7.0 and later)
 
primitive_type
  : TINYINT
  | SMALLINT
  | INT
  | BIGINT
  | BOOLEAN
  | FLOAT
  | DOUBLE
  | DOUBLE PRECISION -- (Note: Available in Hive 2.2.0 and later)
  | STRING
  | BINARY      -- (Note: Available in Hive 0.8.0 and later)
  | TIMESTAMP   -- (Note: Available in Hive 0.8.0 and later)
  | DECIMAL     -- (Note: Available in Hive 0.11.0 and later)
  | DECIMAL(precision, scale)  -- (Note: Available in Hive 0.13.0 and later)
  | DATE        -- (Note: Available in Hive 0.12.0 and later)
  | VARCHAR     -- (Note: Available in Hive 0.12.0 and later)
  | CHAR        -- (Note: Available in Hive 0.13.0 and later)
 
array_type
  : ARRAY < data_type >
 
map_type
  : MAP < primitive_type, data_type >
 
struct_type
  : STRUCT < col_name : data_type [COMMENT col_comment], ...>
 
union_type
   : UNIONTYPE < data_type, data_type, ... >  -- (Note: Available in Hive 0.7.0 and later)
 

　说明：

　　　　1、CREATE TABLE 创建一个指定名字的表。如果相同名字的表已经存在，则抛出异常；用户可以用 IF NOT EXISTS 选项来忽略这个异常。
　　　　2、EXTERNAL关键字可以让用户创建一个外部表，在建表的同时指定一个指向实际数据的路径（LOCATION），Hive 创建内部表时，会将数据移动到数据仓库指向的路径；若创建外部表，仅记录数据所在的路径，不对数据的位置做任何改变。在删除表的时候，内部表的元数据和数据会被一起删除，而外部表只删除元数据，不删除数据。
　　　　3、LIKE 允许用户复制现有的表结构，但是不复制数据。
　　　　4、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 确定表的具体的列的数据。
　　　　5、STORED AS
　　　　　　SEQUENCEFILE|TEXTFILE|RCFILE
　　　　　　如果文件数据是纯文本，可以使用 STORED AS TEXTFILE。如果数据需要压缩，使用 STORED AS SEQUENCEFILE。

　　　　6、CLUSTERED BY
　　　　　　对于每一个表（table）或者分区， Hive可以进一步组织成桶，也就是说桶是更为细粒度的数据范围划分。Hive也是 针对某一列进行桶的组织。Hive采用对列值哈希，然后除以桶的个数求余的方式决定该条记录存放在哪个桶当中。
把表（或者分区）组织成桶（Bucket）有两个理由：
　　　　（1）获得更高的查询处理效率。桶为表加上了额外的结构，Hive 在处理有些查询时能利用这个结构。具体而言，连接两个在（包含连接列的）相同列上划分了桶的表，可以使用 Map 端连接 （Map-side join）高效的实现。比如JOIN操作。对于JOIN操作两个表有一个相同的列，如果对这两个表都进行了桶操作。那么将保存相同列值的桶进行JOIN操作就可以，可以大大较少JOIN的数据量。
　　　　（2）使取样（sampling）更高效。在处理大规模数据集时，在开发和修改查询的阶段，如果能在数据集的一小部分数据上试运行查询，会带来很多方便。

　　　　示例：

CREATE TABLE page_view(viewTime INT, userid BIGINT,
     page_url STRING, referrer_url STRING,
     ip STRING COMMENT 'IP Address of the User')
 COMMENT 'This is the page view table'
 PARTITIONED BY(dt STRING, country STRING)
 ROW FORMAT DELIMITED
   FIELDS TERMINATED BY '\001'
STORED AS SEQUENCEFILE;

 　　　　重命名表

ALTER TABLE table_name RENAME TO new_table_name

　　　　增加/更新列

ALTER TABLE table_name ADD|REPLACE COLUMNS (col_name data_type [COMMENT col_comment], ...) 

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

ALTER TABLE table_name CHANGE [COLUMN] col_old_name col_new_name column_type [COMMENT col_comment] [FIRST|AFTER column_name]

 

　　2.DML

　　　　Load

LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO 
TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)]

　　　　说明：

　　　　　　1、 Load 操作只是单纯的复制/移动操作，将数据文件移动到 Hive 表对应的位置。

　　　　　　2、 filepath：

　　　　　　　　相对路径，例如：project/data1

　　　　　　　　绝对路径，例如：/user/hive/project/data1

　　　　　　包含模式的完整 URI，列如：

　　　　　　　　　　hdfs://namenode:9000/user/hive/project/data1

　　　　　　3、 LOCAL关键字

　　　　　　　　如果指定了 LOCAL， load 命令会去查找本地文件系统中的 filepath。

　　　　　　　　如果没有指定 LOCAL 关键字，则根据inpath中的uri查找文件

　　　　　　4、 OVERWRITE 关键字

　　　　　　　如果使用了 OVERWRITE 关键字，则目标表（或者分区）中的内容会被删除，然后再将 filepath 指向的文件/目录中的内容添加到表/分区中。

　　　　　　　如果目标表（分区）已经有一个文件，并且文件名和 filepath 中的文件名冲突，那么现有的文件会被新文件所替代。

　　　　insert

INSERT OVERWRITE TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...)] select_statement1 FROM from_statement

Multiple inserts:
FROM from_statement 
INSERT OVERWRITE TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...)] select_statement1 
[INSERT OVERWRITE TABLE tablename2 [PARTITION ...] select_statement2] ...

Dynamic partition inserts:
INSERT OVERWRITE TABLE tablename PARTITION (partcol1[=val1], partcol2[=val2] ...) select_statement FROM from_statement

 　　　　select

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]

　　　　保存select 结果集的方式：

保存select查询结果的几种方式：
1、将查询结果保存到一张新的hive表中
create table t_tmp
as
select * from t_p;

2、将查询结果保存到一张已经存在的hive表中
insert into  table t_tmp
select * from t_p;

3、将查询结果保存到指定的文件目录（可以是本地，也可以是hdfs）
insert overwrite local directory '/home/hadoop/test'
select * from t_p;


insert overwrite directory '/aaa/test'
select * from t_p;

　　3.hive join

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

　　　Hive中除了支持和传统数据库中一样的内关联、左关联、右关联、全关联，还支持LEFT SEMI JOIN和CROSS JOIN，但这两种JOIN类型也可以用前面的代替。

　　　左半连接这里给出一个示例，理论不赘述：参考：这里

 1     SELECT a.id,
 2     a.name
 3     FROM lxw1234_a a
 4     LEFT SEMI JOIN lxw1234_b b
 5     ON (a.id = b.id);
 6      
 7     --执行结果：
 8     1 zhangsan
 9     2 lisi
10      
11     --等价于：
12     SELECT a.id,
13     a.name
14     FROM lxw1234_a a
15     WHERE a.id IN (SELECT id FROM lxw1234_b);

 

　示例：

select * from a inner join b on a.id=b.id;

二、分区表与分桶表

　　1.分区表

　　　　在Hive Select查询中一般会扫描整个表内容，会消耗很多时间做没必要的工作。有时候只需要扫描表中关心的一部分数据，因此建表时引入了partition概念。分区表指的是在创建表时指定的partition的分区空间。 

Hive可以对数据按照某列或者某些列进行分区管理，所谓分区我们可以拿下面的例子进行解释。 
当前互联网应用每天都要存储大量的日志文件，几G、几十G甚至更大都是有可能。存储日志，其中必然有个属性是日志产生的日期。在产生分区时，就可以按照日志产生的日期列进行划分。把每一天的日志当作一个分区。 
将数据组织成分区，主要可以提高数据的查询速度。至于用户存储的每一条记录到底放到哪个分区，由用户决定。即用户在加载数据的时候必须显示的指定该部分数据放到哪个分区。

　　　　建立分区表

create table t_user_part (id int, name string) 
partitioned by (country string) 
row format delimited fields terminated by '\t';

　　　　向分区表中导入数据

[hadoop@mini1 ~]$ cat user.dat 
1    Join
2    Mary
3    Bob
4    Tom

LOAD DATA LOCAL INPATH '/home/hadoop/user.dat' INTO TABLE t_user_part PARTITION(country='USA');

0: jdbc:hive2://localhost:10000> SELECT * FROM t_user_part;
+-----------------+-------------------+----------------------+--+
| t_user_part.id  | t_user_part.name  | t_user_part.country  |
+-----------------+-------------------+----------------------+--+
| 1               | Join              | USA                  |
| 2               | Mary              | USA                  |
| 3               | Bob               | USA                  |
| 4               | Tom               | USA                  |
+-----------------+-------------------+----------------------+--+
4 rows selected (0.801 seconds)
0: jdbc:hive2://localhost:10000> 

 　　// 分区字段是一个伪字段

0: jdbc:hive2://localhost:10000> SELECT * FROM t_user_part WHERE country='USA';

　　　　增加分区

ALTER TABLE table_name ADD partition_spec [ LOCATION 'location1' ] partition_spec [ LOCATION 'location2' ] ... partition_spec: : PARTITION (partition_col = partition_col_value, partition_col = partiton_col_value, ...)

用户可以用 ALTER TABLE ADD PARTITION 来向一个表中增加分区。当分区名是字符串时加引号。例：

ALTER TABLE test_table ADD PARTITION (dt='2016-08-08', hour='10') location '/path/uv1.txt' PARTITION (dt='2017-08-08', hour='12') location '/path/uv2.txt';

　　　　删除分区

ALTER TABLE table_name DROP partition_spec, partition_spec,...

用户可以用 ALTER TABLE DROP PARTITION 来删除分区。分区的元数据和数据将被一并删除。例：

ALTER TABLE test_table DROP PARTITION (dt='2016-08-08', hour='10');

　　　　查看分区

show partitions tablename

show partitions t_user_part;

 　　分区的更多介绍以及动态分区的介绍与设置：https://www.cnblogs.com/liqiu/p/4095654.html

　　2.分桶表

• 对于每一个表（table）或者分区， Hive可以进一步组织成桶，也就是说桶是更为细粒度的数据范围划分。（桶有点类似mr中的分区）Hive也是针对某一列进行桶的组织。Hive采用对列值哈希，然后除以桶的个数求余的方式决定该条记录存放在哪个桶当中。基本可以这么说分区表是粗粒度的划分，桶在细粒度的划分。当数据量比较大，我们需要更快的完成任务，多个map和reduce进程是唯一的选择。

• 但是如果输入文件是一个的话，map任务只能启动一个。此时bucket table是个很好的选择，通过指定CLUSTERED的字段，将文件通过hash打散成多个小文件。

• 把表（或者分区）组织成桶（Bucket）有两个理由：

• 获得更高的查询处理效率。桶为表加上了额外的结构，Hive 在处理有些查询时能利用这个结构。具体而言，连接两个在（包含连接列的）相同列上划分了桶的表，可以使用 Map 端连接 （Map-side join）高效的实现。比如JOIN操作。对于JOIN操作两个表有一个相同的列，如果对这两个表都进行了桶操作。那么将保存相同列值的桶进行JOIN操作就可以，可以大大较少JOIN的数据量。

• 使取样（sampling）更高效。在处理大规模数据集时，在开发和修改查询的阶段，如果能在数据集的一小部分数据上试运行查询，会带来很多方便

　　　　注意：分桶之前要设置分桶开关以及reduce数量使其与分桶数量一致！

set hive.enforce.bucketing = true;
set mapreduce.job.reduces=4;

　　分桶表的相关操作，参考：http://www.mamicode.com/info-detail-1781619.html

　　　　建立分桶表：

create table t_buck(id int, name string)
clustered by(id) 
sorted by(id)
into 4 buckets
row format delimited
fields terminated by ',';

DESC EXTENDED t_buck;

　　　　装载数据：此种方式不会自动分桶，需要自己提前分桶，不建议使用！

LOAD DATA LOCAL INPATH '/home/hadoop/user2.dat' INTO TABLE t_user_buck;

　　　　向这种带桶的表里面导入数据有两种方式，一种是外部生成的数据导入到桶表，一种是利用hive来帮助你生成桶表数据。　　　　　

　　　　开始往创建的分通表插入数据(插入数据需要是已分桶, 且排序的)

　　　　可以使用distribute by(id) sort by(id asc)

　　　　排序和分桶的字段相同的时候也可以使用Cluster by(字段)

　　　　注意使用cluster by 就等同于分桶+排序(sort)

insert overwrite table t_buck
select id,name from t_buck_from cluster by(id);

　　　　

注：1、order by 会对输入做全局排序，因此只有一个reducer，会导致当输入规模较大时，需要较长的计算时间。
2、sort by不是全局排序，其在数据进入reducer前完成排序。因此，如果用sort by进行排序，
　　并且设置mapred.reduce.tasks>1，则sort by只保证每个reducer的输出有序，不保证全局有序。
3、distribute by(字段)根据指定的字段将数据分到不同的reducer，且分发算法是hash散列。
4、Cluster by(字段) 除了具有Distribute by的功能外，还会对该字段进行排序。
5、创建分桶表并不意味着load进数据也是分桶的，你必须先分好桶，然后再放到表中。

 

0: jdbc:hive2://localhost:10000> dfs -cat /user/hive/warehouse/t_buck/000001_0;
+-------------+--+
| DFS Output  |
+-------------+--+
| 1,aa        |
| 5,ee        |
| 9,ii        |
+-------------+--+
3 rows selected (0.032 seconds)
0: jdbc:hive2://localhost:10000> 

　　分桶表的最大作用是提高了表之间的join效率，因为两个分桶表之间join的字段只需对应桶即可！

　　具体分析与操作，参考：https://www.cnblogs.com/kouryoushine/p/7809299.html