本文详细介绍了HiveQL中的数据操作，包括如何向管理表和内部表装载数据，通过查询语句插入数据，以及单个查询语句中创建表并加载数据的方法。同时，还讲解了导出数据的两种方式，直接拷贝文件或使用INSERT...DIRECTORY...语句。
HiveQL：数据操作
向表中装载数据和从表中抽取数据到文件系统的数据操作语言部分
一、向管理表/内部表中装载数据
-
装载语句
LOAD DATA LOCAL INPATH '${env:HOME}/california-employees' OVERWRITE INTO TABLE employees PARTITION (country='US',state='CA');
数据将会存放在如下文件夹中：
hdfs://master_server/user/hive/warehouse/mydb.db/employees/country=US/state=CA
-
关键字解析
LOCAL:
如果包含LOCAL关键字，文件路径为本地文件系统路径，文件将会从本地文件系统中被拷贝到目标位置，即分布式文件系统上的目标位置；
本地文件系统 -----> 分布式文件系统
如果省略掉LOCAL关键字，则该路径为分布式文件系统中的路径；此时数据是从此文件系统的一个路径下转移到另一个路径中
分布式文件系统 ------> 分布式文件系统
注意：hive要求源文件和目标文件以及目录应该再同一个文件系统中，即用户不能使用LOAD DATA语句将数据从一个集群的HDFS中转载（转移）到另一个集群的HDFS中；
OVERWRITE:
如果使用OVERWRITE关键字，目标文件夹中之前存在的数据将会被先删除掉；
如果不适用OVERWRITE关键字，则仅会把新增的文件增加到目标文件夹中，而不会删除掉之前的数据；
如果不使用OVERWRITE关键字，且目标文件夹中已经存在和装载的文件同名的文件，则旧的同名文件将被覆盖重写；事实上如果没有使用OVERWRITE，且目标文件夹下已经存在同名的文件，会保留之前的文件，并重命名新文件为“之前的文件名_序列号”
（Hive v0.9.0版本之前的版本中存在bug，即如果没有使用OVERWRITE关键字，目标文件夹下已经存在和转载文件同名的文件，之前的文件会被覆盖重写，会产生数据丢失，此bug已经修复）
PARTITION
如果目标表是分区表，则需要使用PARTITION字句，且需要为每个分区的键指定一个值 -
注意事项
~分区目录问题：
如果分区目录不存在，则会先创建分区目录，再将数据拷贝到该目录下，且文件名会保持不变；
如果分区目录不存在，则会先创建分区目录，再将数据拷贝到该目录下，且文件名会保持不变；
如果目标表（TABLE employees）是非分区表，则语句中应该省略PARTITION字句；
~相对路径问题：
指定全路径会具有更好的鲁棒性，也同样支持相对路径：
本地模式：
相对路径相对的是HiveCLI启动时用户的工作目录
分布式或伪分布式模式：
相对于分布式文件系统中用户的根目录，在HDFS中和MapRFS中默认为/user/$USER
二、通过查询语句向表中插入数据
- 插入语句
静态分区—扫描多次输入数据：
INSERT OVERWRITE TABLE employees
PARTITION (country='US',state='OR')
SELECT * FROM staged_employees se
WHERE se.cnty='US' AND se.st=‘OR’;
假设staged_employees表中已经有相关数据，使用不同的名字表示国家和州，分别称作cnty和st；
如果表staged_employees非常大，则用户需要对65个州都执行这些语句，即需要扫描staged_employees表65次；
静态分区—只扫描一次输入数据：
只扫描一次输入数据，然后按多种方式进行划分
为3个州创建表employees分区
FROM staged_employees se
INSERT OVERWRITE TABLE employees
PARTITION (country='US',state='OR')
SELECT * WHERE se.cnty='US' AND se.st='OR'
INSERT OVERWRITE TABLE employees
PARTITION(country='US',state='CA')
SELECT * WHERE se.cnty='US' AND se.st='CA'
INSERT OVERWRITE TABLE employees
PARTITION (country='US',state='IL')
SELECT * WHERE se.cnty='US' AND se.st='IL';
从staged_employees表中读取的每条记录都会经过SELECT …WHERE…子句进行判断，这些子句都是独立判断的；
应用场景：源表中的某些数据可以被写入目标表的多个分区中，或者不被写入任一个分区中
动态分区：
INSERT OVERWRITE TABLE employees
PARTITION (country,state)
SELECT ...,se.cnty,se.st
FROM staged_employee se;
Hive根据SELECT语句中最后两列(se.cnty,se.st)来确定分区字段country,state，且源表字段值和输出分区值之间的关系是根据位置而不是根据命名来匹配的
动态分区与静态分区混用：
INSERT OVERWRITE TABLE employees
PARTITION (country='US',state)
SELECT ...,se.cnty,se,st
FROM staged_employees se
WHERE se.cnty='US';
静态分区键必须出现在动态分区键之前
-
关键字解析
INSERT:
INSERT允许用户通过查询语句向目标表中插入数据；
每个INSERT子句，都可以插入到不同的表中，且目标表可以是分区表也可以使非分区表；
OVERWRITE:
如果使用OVERWRITE关键字，之前分区中的内容，将会被覆盖掉
INTO:
如果没有使用OVERWRITE关键字或者使用INTO关键字替换掉OVERWRITE，则HIVE会以追加的方式写入数据，而不覆盖掉之前已经存在的内容（Hive v0.8.0版本以及之后的版本中才有）
SELECT…WHERE：
如果某条记录满足某个SELECT…WHERE…语句，则这条记录就会被写入到指定的表和分区中
PARTITION:
PARTITION (country=‘US’,state=‘OR’)----静态分区
PARTITION (country,state)—动态分区
INSERT OVERWRITE句式和INSERT INTO可以混用
动态分区和静态分区可以混用：
静态分区键必须在动态分区键之前；
动态分区功能默认情况下没有开启，开启后，默认以“严格模式”执行，要求至少有一列分区字段是静态的，有助于组织因涉及错误导致查询产生大量分区； -
动态分区属性
hive.exec.dynamic.partition ：
缺省值：false
描述：设置成true，表示开启动态分区功能
hive.exec.dynamic.partition.mode：
缺省值：strict
描述：设置成nonstrict，表示允许所有分区都是动态的
hive.exec.max.dynamic.partitions.pernode：
缺省值：100
描述：每个mapper或reducer可以创建的最大动态分区个数，如果某个mapper或reducer尝试创建大于这个值得分区，则会抛出一个致命错误信息
hive.exec.max.dynamic.partitions：
缺省值：+1000
描述：一个动态分区创建语句可以创建的最大动态分区个，如果超过这个值会抛出一个致命错误信息
hive.exec.max.created.files：
缺省值：100000
描述：全局可以创建的最大文件个数，有一个hadoop计数器会跟踪记录创建了多少个文件，如果超过这个值会抛出一个致命错误信息 -
应用场景
数据已经存在于某个目录下，对于Hive来说是一个外部表，现在需要将其倒入到最终的分区表中。如果用户需要将数据导入到一个具有不同记录格式（具有不同字段分隔符）的目标表中，则可以使用以上方式
三、单个查询语句中创建表并加载数据
- 建表语句
在一条语句中完成创建表，并将查询结果载入这个表
CREATE TABLE ca_employees
AS SELECT name,salary,address
FROM employees
WHERE se.state='CA'
-
应用场景
从一个大的宽表中选取部分需要的数据集 -
注意事项
此功能不能用于外部表，使用ALTER TABLE语句可以为外部表“引用”到一个分区，这里本身没有进行数据“装载”，而是将元数据中指定一个纸箱数据的路径
四、导出数据
- 导出数据
如果数据文件恰好是用户需要的格式：
直接拷贝文件夹或者文件即可
hadoop fs -cp source_path target_path
如果需要转换格式
使用INSERT…DIRECTORY…
INSERT OVERWRITE LOCAL DIRECTORY '/tmp/ca_employees'
SELECT name,salary,address
FROM employees
WHERE se.state='CA';
- 注意事项
一个或者多个文件将会被写入到/tmp/ca_employees，具体个数取决于调用的reducer个数;
路径可以写成全URL路径(如hdfs://master-server/tmp/ca_employees);
不论在源表中数据实际是如何存储的，Hive会将所有字段序列化成字符串写入到文件中。Hive使用和Hive内容存储的表相同的彪马方式来生成输出文件。
- 查看导出结果文件内容
hive>! ls /tmp/ca_employee;
000000_0
文件名，如果有两个或多个reducer来写输出，可以看到其他相似命名文件，如000001_1
hive>! cat /tmp/payroll/000000_0
John Doe1000000.0201 San Antonio CircleMountain ViewCA94040
........
从输出内容看自断剑没有分隔符，是因为此处没有把^ A和^B显示出来，和向表中插入数据一样，用户也可以通过如下方式指定多个输出文件夹目录
FROM staged_employees se
INSERT OVERWRITE DIRECTORY '/tmp/or_employees'
SELECT * WHERE se.cty='US' AND se.st='OR'
INSERT OVERWRITE DIRECTORY '/tmp/ca_employees'
SELECT * WHERE se.cty='US' AND se.st='CA';
扫一扫
1019
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
