| Hive专栏 | ||
|---|---|---|
| 上一篇 | 主目录 | 下一篇 |
目录
【前言】
hive的常用命令
【注】文中dbname表示数据库的名称， []中的表示可选项
默认的Hive仓库路径：hdfs://hadoop01:50070/user/hive/warehouse/
- 使用，insert…select 往表中导入数据时，查询的字段个数必须和目标的字段个数相同，不能多，也不能少,否则会报错。但是如果字段的类型不一致的话，则会使用null值填充，不会报错。而使用load data形式往hive表中装载数据时，则不会检查。如果字段多了则会丢弃，少了则会null值填充。同样如果字段类型不一致，也是使用null值填充。
1 Load加载数据
LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename [PARTITION
(partcol1=val1, partcol2=val2 ...)]
说明：
- LOAD
操作只是单纯的复制或者移动操作，将数据文件移动到 Hive 表对应的位置。 - LOCAL
如果指定了 LOCAL， LOAD 命令会去查找本地文件系统中的 filepath。
如果没有指定 LOCAL 关键字，则根据 inpath 中的 uri 查找文件
注意：uri 是指 hdfs 上的路径，分简单模式和完整模式两种，例如：
简单模式：/user/hive/project/data1
完整模式：hdfs://namenode_host:9000/user/hive/project/data1
导入本地数据和导入HDFS上的数据的区别：
1、导入HDFS上的数据到hive表，表示截切，移动
2、导入本地数据，相当于复制或者上传 - filepath：
相对路径，例如：project/data1
绝对路径，例如：/user/home/project/data1
包含模式的完整 URI，列如：hdfs://namenode_host:9000/user/home/project/data1
注意：inpath 子句中的文件路径下，不能再有文件夹。 - overwrite
如果使用了 OVERWRITE 关键字，则目标表（或者分区）中的内容会被删除，然后再将filepath 指向的文件/目录中的内容添加到表/分区中。
没有OVERWRITE关键字，仅仅会把新增的文件增加到目标文件夹而不会删除之前的数据。如果使用OVERWRITE关键字，那么目标文件夹中之前的数据将会被先删除掉。
如果目标表（分区）已经有一个文件，并且文件名和 filepath 中的文件名冲突，那么现有的文件会被新文件所替代。
2 插入数据
2.1 插入一条数据
INSERT INTO TABLE table_name VALUES(XX,YY,ZZ);
2.2 利用查询语句将结果导入新表
INSERT OVERWRITE [INTO] TABLE table_name [PARTITION (partcol1=val1, partcol2=val2 ...)]
select_statement1 FROM from_statement
例如：执行 INSERT INTO TABLE test4 SELECT * FROM test1; 语句将test1表中查询到的数据以追加的方式（没有overwrite不会覆盖原数据）插入到test4表中
2.3 多重插入
from student
insert into table ptn_student partition(city='MA') select id,name,sex,age,department where department='MA'
insert into table ptn_student partition(city='IS') select id,name,sex,age,department where department='IS';
insert into table ptn_student partition(city='CS') select id,name,sex,age,department where department='CS';
2.4 分区插入
分区插入有两种，一种是静态分区，另一种是动态分区。如果混合使用静态分区和动态分区，则静态分区必须出现在动态分区之前。现分别介绍这两种分区插入。
- 静态分区：
A)、创建静态分区表
B)、从查询结果中导入数据
C)、查看插入结果 - 动态分区：
静态分区需要创建非常多的分区，那么用户就需要写非常多的 SQL！Hive 提供了一个动态分区功能，其可以基于查询参数推断出需要创建的分区名称。
A)、创建分区表，和创建静态分区表是一样的
B)、参数设置hive> set hive.exec.dynamic.partition=true; hive> set hive.exec.dynamic.partition.mode=nonstrict;
【注意】：动态分区默认情况下是开启的。但是却以默认是”strict”模式执行的，在这种模式下要求至少有一列分区字段是静态的。这有助于阻止因设计错误导致查询产生大量的分区。但是此处我们不需要静态分区字段，估将其设为 nonstrict。
对应还有一些参数可设置：
set hive.exec.max.dynamic.partitions.pernode=100;//每个节点生成动态分区最大个数
set hive.exec.max.dynamic.partitions=1000;//生成动态分区最大个数，如果自动分区数大于这个参数，将会报错
set hive.exec.max.created.files=100000;//一个任务最多可以创建的文件数目
set dfs.datanode.max.xcievers=4096;//限定一次最多打开的文件数
set hive.error.on.empty.partition=false;//表示当有空分区产生时，是否抛出异常
小技能补充：如果以上参数被更改过，想还原，请使用 reset 命令执行一次即可
2.4.1 静态分区插入
①创建分区表
hive> CREATE TABLE test2(name STRING,address STRING,school STRING)
PARTITIONED BY(age float)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
STORED AS TEXTFILE ;
此处创建了一个test2的分区表，以年龄分区
②从查询结果中导入数据
hive> INSERT INTO TABLE test2 PARTITION (age='24') SELECT name,address,school FROM test1;
注：不能这样写hive> INSERT INTO TABLE test2 PARTITION (age='24') SELECT * FROM test1;此处会报一个错误，是因为test2中是以age分区的，有三个字段，SELECT * 语句中包含有四个字段，所以出错。
静态分区的分区字段在插入时需要指定字段的值：age=‘24’。这个操作会使导入到test2的数据的age的值全部变成24
③ 查看插入结果
2.4.2 动态分区插入
2.4.2.1 单字段动态分区
① 创建分区表
hive> CREATE TABLE test2(name STRING,address STRING,school STRING)
PARTITIONED BY(age float)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
STORED AS TEXTFILE ;
此处创建了一个test2的分区表，以年龄分区
② 参数设置
使用动态分区表必须配置的参数
hive> set hive.exec.dynamic.partition=true;
hive> set hive.exec.dynamic.partition.mode=nonstrict;
③ 数据动态插入
insert into table test2 partition (age) select name,address,school,age from test1;
注意：查询语句select查询出来的age字段必须放在最后，和分区字段对应，不然结果会出错
动态分区的分区字段在插入时不需要指定字段的值：age=’？’，会自动的将该字段的值相同的放在一个分区中
④ 查看结果
以上是一个分区字段的情况，多个分区字段情况如下：
2.4.2.2 多字段半动态分区
① 创建分区表
hive> CREATE TABLE sd_partition(id INT,name STRING)
PARTITIONED BY(state STRING,city STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
STORED AS TEXTFILE ;
② 参数设置
使用动态分区表必须配置的参数
hive> set hive.exec.dynamic.partition=true;
hive> set hive.exec.dynamic.partition.mode=nonstrict;
③ 数据动态插入
insert overwrite table sd_partition
partition(state='China',city)
select id ,name,adress from test1;
半自动动态分区：state分区为静态，city为动态分区，以查询到的city字段为动态分区的分区名。这里将select id ,name,adress from test1; 中的最后一个字段adress对应city动态分区字段
④ 查看结果
2.4.2.2 多字段全动态分区
① 创建分区表
hive> CREATE TABLE dd_partition(id INT,name STRING)
PARTITIONED BY(state STRING,city STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
STORED AS TEXTFILE ;
② 参数设置
使用动态分区表必须配置的参数
hive> set hive.exec.dynamic.partition=true;
hive> set hive.exec.dynamic.partition.mode=nonstrict;
③ 数据动态插入
insert overwrite table dd_partition
partition(state,city)
select id ,name,state,city from test;
半=全自动动态分区：state和city均为动态分区，先以查询到的state字段的值为动态分区的分区名，然后在同一个state分的区里面，再以查询到的city字段的值为动态分区的分区名。这里将select id ,name,state,city from test; 中的最后两个字段state，city分别对应state，city动态分区字段
④ 查看结果
2.5 CTAS(create table…as select…)
在实际情况中，表的输出结果可能太多，不适于显示在控制台上，这时候，将 Hive 的查询输出结果直接存在一个新的表中是非常方便的，我们称这种情况为 CTAS展示：
CREATE TABLE mytest AS SELECT name, age FROM test;
注意：CTAS 操作是原子的，因此如果 select 查询由于某种原因而失败，新表是不会创建的！
3 导出数据
3.1 导出数据到本地
insert overwrite local directory '/home/hadoop/test_from_hive' select * from test;
数据写入到文件系统时进行文本序列化，且每列用^A 来区分，\n 为换行符。用more 命令查看时不容易看出分割符，可以使用: sed -e ‘s/\x01/\t/g’ filename 来查看。
3.2 导出数据到HDFS
insert overwrite directory /test_from_hive' select * from test;
insert overwrite directory 'hdfs://hadoop01:50070/test_from_hive' select * from test;
4 select查询数据
Hive 中的 SELECT 基础语法和标准 SQL 语法基本一致，支持 WHERE、DISTINCT、GROUP BY、ORDER BY、HAVING、LIMIT、子查询等；
语法结构
SELECT [ALL | DISTINCT] select_ condition, select_ condition, ...
FROM table_name a
[JOIN table_other b ON a.id = b.id]
[WHERE where_condition]
[GROUP BY col_list [HAVING condition]]
[CLUSTER BY col_list | [DISTRIBUTE BY col_list] [SORT BY col_list | ORDER BY col_list] ]
[LIMIT number]
说明：
- select_ condition 查询字段
- table_name 表名
- order by(字段) 全局排序，因此只有一个 reducer，只有一个 reduce task 的结果，比如文件名是 000000_0，会导致当输入规模较大时，需要较长的计算时间。
- sort by(字段) 局部排序，不是全局排序，其在数据进入 reducer 前完成排序。因此，如果用 sort by 进行排序，并且设置 mapred.reduce.tasks>1，则 sort by 只保证每个 reducer的输出有序，不保证全局有序。那万一，我要对我的所有处理结果进行一个综合排序，而且数据量又非常大，那么怎么解决？我们不适用 order by 进行全数据排序，我们适用 sort by 对数据进行局部排序，完了之后，再对所有的局部排序结果做一个归并排序
- distribute by(字段) 根据指定的字段将数据分到不同的 reducer，且分发算法是 hash 散列。
- cluster by(字段) 除了具有 Distribute by 的功能外，还会对该字段进行排序。因此，如果分桶和 sort 字段是同一个时，此时，cluster by = distribute by + sort by如果我们要分桶的字段和要排序的字段不一样，那么我们就不能使用 clustered by
分桶表的作用：最大的作用是用来提高 join 操作的效率；（思考这个问题：select a.id,a.name,b.addr from a join b on a.id = b.id;如果 a 表和 b 表已经是分桶表，而且分桶的字段是 id 字段做这个 join 操作时，还需要全表做笛卡尔积吗？）
5 Hive Jion查询
语法结构：
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 支持等值连接（equality join）、外连接（outer join）和（left/right join）。Hive 不支持非等值的连接，因为非等值连接非常难转化到 map/reduce 任务。另外，Hive 支持多于 2 个表的连接。
写查询时要注意以下几点：
- 只支持等值链接，支持 and，不支持 or
例如：
SELECT a.* FROM a JOIN b ON (a.id = b.id)
SELECT a.* FROM a JOIN b ON (a.id = b.id AND a.department = b.department)是正确的；
然而：SELECT a.* FROM a JOIN b ON (a.id>b.id)是错误的。 - 可以 join 多于 2 个表
例如：
SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key2)
如果 join 中多个表的 join key 是同一个，则 join 会被转化为单个 map/reduce 任务，例如：
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 中只使用了 b.key1 作为 join key。
例如：SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key2)
而这一 join 被转化为 2 个 map/reduce 任务。因为 b.key1 用于第一次 join 条件，而b.key2 用于第二次 join。 - Join 时，每次 map/reduce 任务的逻辑reducer 会缓存 join 序列中除了最后一个表的所有表的记录，再通过最后一个表将结果序
列化到文件系统。这一实现有助于在 reduce 端减少内存的使用量。实践中，应该把最大的那个表写在最后（否则会因为缓存浪费大量内存）。例如：
SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key1)
所有表都使用同一个 join key（使用 1 次 map/reduce 任务计算）。Reduce 端会缓存 a 表 和 b 表的记录，然后每次取得一个 c 表的记录就计算一次 join 结果，类似的还有：
SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key2)
这里用了 2 次 map/reduce 任务：
第一次缓存 a 表，用 b 表序列化；
第二次缓存第一次 map/reduce 任务的结果，然后用 c 表序列化。 - HiveJoin 分三种：inner join, outer join, semi join
其中：outer join 包括 left join，right join 和 full outer join,主要用来处理 join 中空记录的情况
495
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
