Hive之HQL数据操作（导入导出）

------------本文笔记整理自《Hadoop海量数据处理：技术详解与项目实战》范东来

一、数据导入

1.1.装载数据

--将HDFS文件中数据 追加 导入Hive表
> use test;    --切换为test数据库
> load data inpath '/user/hadoop/data' into table test;--导入表test
> load data inpath '/user/hadoop/data1' into table test;
<注1：文件data中数据的列分隔符一定要与表test的列分隔符一致，否则无法导入>
<注2：数据导入后，HDFS上原文件data将被删除，实际上是data文件被移动到了hive指定的表数据路径下>
--表路径展示：
 /user/hive/warehouse/test.db/test
--表数据展示：
 /user/hive/warehouse/test.db/test/data
 /user/hive/warehouse/test.db/test/data1
<注：可见数据的装载直接是将指定源数据文件移动到了目标表目录下>

--将HDFS文件中数据 覆盖 导入Hive表
> load data inpath '/user/hadoop/data' overwrite into table test;
<注：数据导入后，原文件data将被删除>


--假定分区表建表语句
> create table test_p (
> value int)
> partitioned by (key int);
--假定：data1数据为：  data2数据为：
        1              11
        2              12
        3              13
        4              14
--导入分区表，需要指定数据所属分区
> load data inpath '/user/hadoop/data1' overwrite into table test_p partition(key=1);
> load data inpath '/user/hadoop/data2' overwrite into table test_p partition(key=2);
--查询导入的分区表数据
> select key, value from test_p;
1    1
1    2
1    3
1    4
2    11
2    12
2    13
2    14
<即导入分区数据结果>

--本地导入数据
> load data local inpath '/home/hadoop/data' into table test;
> load data local inpath '/home/hadoop/data' overwrite into table test;
> load data local inpath '/user/hadoop/data1' overwrite into table test_p partition(key=1);

--从本地local导入与HDFS导入的区别：
--1.从HDFS导入，只是将文件移动到了hive数据库（本质上也存储在HDFS上），所以数据导入后，原导入路径上的文件消失。
--2.从本地local导入，hive会复制一份数据，然后再上传至指定hive目录，所以本地文件不会消失。

--Hive在数据导入时，不会自动做数据格式的验证，需要手动保证文件数据格式与表定义格式一致。

 1.2.通过查询语句向表中插入数据

--通过查询语句向表中插入数据（启动了map任务，无reduce任务）
> insert overwrite table test select * from source; --覆盖（全量）
> insert into table test select * from source; --追加（增量）
<insert into 语句 是Hive 0.7之后增加的功能>

--向分区表中插入查询（需要指定分区，依然只启动了map任务）
> insert overwrite table test partition(part='1')
> select id, name from student;    --覆盖
> insert into table test partition(part='1')
> select id, name from student;    --追加

--*特性：一次查询出不相交的输出，插入分区表（需要指定分区，依然只启动了map任务）
> from student
> insert overwrite table test partition(part='1')
> select id, name where id < 10
> insert overwrite table test partition(part='2')
> select id, name where id >= 10 and id < 20

--向集合类型Array,Map,Struct中插入数据
<注：使用array(),str_to_map(),named_struct()来包装插入的三种集合数据>
> insert into test select array('math','music','chinese'),str_to_map('high=170,weight=60'),named_struct('math',90,'english',90);

 

1.3.利用动态分区向表中插入数据

--动态分区：用于优化一次查询，只需指定n个分区字段，无需为每个分区写一个HQL
> insert overwrite table test partition(time1, time2)
> select id, name, modify_time, create_time from student;
<注：指定两个分区字段time1和time2，默认依据select语句的最后两个字段作动态分区>

--开启动态分区（Hive默认关闭动态分区）
> set hive.exec.dynamic.partition = true;
> set hive.exec.dynamic.partition; -- 此语句可查看配置信息

--设置所有分区都是动态的
> set hive.exec.dynamic.partition.mode = nonstrict;
> set hive.exec.dynamic.partition.mode; -- 此语句可查看配置信息
<注1：Hive默认不允许所有分区都是动态的>
<注2：静态分区必须位于动态分区之前>

--其它几个参数
hive.exec.max.dynamic.partitions.pernode：每个Mapper或Reducer可以创建的最大分区数。
hive.exec.max.dynamic.partitions：一条动态分区创建语句能够创建的最大分区数。
hibe.exec.max.created.files：一个MapReduce作业能够创建的最大文件数。

--*记录案例：
--假定源数据表data:
       key1  key2
        1    2
        1    3
        2    1
--分区表结构：
> create table ptn (
> key1 int,
> key2 int)
> partitioned by (key3 int)    --分区子句需紧跟create () 的后面，不能放在最后
> row format delimited
> fields terminated by ' ';    --列分隔符为：空格
--利用动态分区导入数据（依然是map任务）
> insert overwrite table ptn partition(key3)
> select key1, key2, key1 from data;
--查询分区表
> select key1, key2, key3 from ptn where key3 = 1;
1 2 1
1 3 1
--查看分区表的HDFS存储路径
> dfs -ls /user/hive/warehouse/test.db/ptn/
    /user/hive/warehouse/test.db/ptn/key3=1
    /user/hive/warehouse/test.db/ptn/key3=2
<即：分区目录显示>
--分别查看key=1和key=2目录下的内容，发现不管数据有多少，都只有一个文件000000_0
--查看该文件
> dfs -cat /user/hive/warehouse/test.db/ptn/key3=1/000000_0
1 2
1 3
> dfs -cat /user/hive/warehouse/test.db/ptn/key3=2/000000_0
> dfs -text /user/hive/warehouse/test.db/ptn/key3=2/000000_0
2 1
<此即，分区文件的字段内容，列分隔为符空格>
<注：如果分区表建表时没有设定列分隔符，默认两个字段会挨在一起>

1.4.通过CTAS加载数据

--用一条语句在创建表时加载数据
<注：CTAS意为：create table ... as select >
create table test as select id, name from student;

 

二、数据导出

--利用查询语句选定数据格式，导出至HDFS或本地
> insert overwrite directory '/user/hadoop/export_hive_data' select * from test;
> insert overwrite local directory '/home/hadoop/export_hive_data' select * from test;

--如果Hive表数据格式已能满足，可直接复制文件或目录
$ hadoop fs -cp /user/hive/warehouse/test.db/test /user/hadoop/

--*参看之前的数据装载示例：（表数据文件存储说明）
> use test;    --切换数据库
> load data inpath '/user/hadoop/data' into table test;--导入表test
> load data inpath '/user/hadoop/data1' into table test;
<注1：文件data中数据的列分隔符一定要与表test的列分隔符一致，否则无法导入>
<注2：数据导入后，HDFS上原文件data将被删除，实际上是data文件被移动到了hive指定的表数据路径下>
--表路径展示：
 /user/hive/warehouse/test.db/test
--表数据展示：
 /user/hive/warehouse/test.db/test/data
 /user/hive/warehouse/test.db/test/data1
<注：可见数据的装载直接是将指定源数据文件移动到了目标表目录下>

三、参考链接

1.hive的集合类型Array、Map和Struct及相应的创建、插入、查询操作

2.hive复杂格式array,map,struct使用