1 装载数据
HiveQL装载数据其实就是向表里加载数据,但是这个装载是一次性操作,因为Hive没有行级别的数据插入、数据更新和删除操作。我们对Hive中的表一般是一次加载大量的数据或者自己将数据文件写入到Hive表中所在的存储位置下。
我们以管理表manage_table
为例,建表语句如下
CREATE TABLE IF NOT EXISTS manage_table(
id STRING,
name STRING
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '|'
STORED AS TEXTFILE;
我们用于装载的文件a.txt
文件内容
装载的SQL语句如下:
LOAD DATA LOCAL INPATH '/home/dongjinbao/data/a.txt' INTO TABLE manage_table;
如果你希望不保留原有表里的数据,可以增加OVERWRITE
表示覆盖原有数据
LOAD DATA LOCAL INPATH '/home/dongjinbao/data/a.txt' OVERWRITE INTO TABLE manage_table;
LOCAL INPATH
表示这是一个本地的文件或者目录,如果是目录就会自动将整个目录下的文件全部装载到表里去,不过这个目录下不能在包含目录了,并不支持多级目录的装载。
上面我们装载数据是以装载本地的数据文件,我们也可以以HDFS上的文件或者目录进行装载
LOAD DATA INPATH '/user/hive/warehouse/study.db/partition_table/year=2018/month=5/day=2/b.txt'
INTO TABLE manage_table;
装载完数据后,我们可以在HDFS上查看manage_table里的内容
hadoop fs -ls -R /user/hive/warehouse/study.db/manage_table
可以发现其实就是将相应的文件写入到manage_table
目录下去,所以才叫做装载数据。
外部表与管理表一样,只是存储的位置不一样。
2 向分区表装载数据
向分区表装载数据与管理表一样,只需要在额外指定装载到哪个分区下。
以分区表partition_table为例,建表语句如下
CREATE TABLE IF NOT EXISTS partition_table(
id STRING,
name STRING
)
PARTITIONED BY (year INT,month INT,day INT)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '|'
STORED AS TEXTFILE;
装载语句为
LOAD DATA LOCAL INPATH '/home/dongjinbao/data/a.txt'
INTO TABLE manage_table
PARTITION (year=2018,month=5,day=4);
如果目录/year=2018/month=5/day=4
不存在,它会自动创建这个目录的。
3 通过SELECT语句向表里插入数据
HiveQL支持使用SELECT语句向表里插入数据,插入时可以选择INSERT OVERWRITE
或者INSERT INTO
,前者表示覆盖之前的内容。
INSERT INTO TABLE partition_table
PARTITION (year=2018,month=5,day=4)
SELECT * FROM manage_table
WHERE id = '1';
我们查看一下分区表里/year=2018/month=5/day=4
分区下的文件
hadoop fs -ls /user/hive/warehouse/study.db/partition_table/year=2018/month=5/day=4
插入的数据存储在系统自动生成的00000_0文件中,每次插入都会生成新的文件。
如果我们插入分区的是多次操作,按上面的方式我们需要对表进行多次扫描,我们也可以只对表只进行一次扫描,多次插入的操作
FROM manage_table mt
INSERT INTO TABLE partition_table
PARTITION (year=2018,month=5,day=5)
SELECT * WHERE mt.id='5'
INSERT INTO TABLE partition_table
PARTITION (year=2018,month=5,day=6)
SELECT * WHERE mt.id='6';
在插入的时候也可以对多个表进行插入
FROM manage_table mt
INSERT OVERWRITE TABLE partition_table
PARTITION (year=2018,month=5,day=5)
SELECT * WHERE mt.id='5'
INSERT INTO TABLE external_table
SELECT * WHERE mt.id='6';
4 动态分区插入
有时候我们会对原本没有分区的表进行重建,而这张表的数据量非常大,如果我们采用上面的方式进行分区插入,那么我们需要写非常多分区,而且每一个分区值都必须知道,这种方式太麻烦了,所以Hive提供了一种支持动态分区插入的方式。
我们先新建一张表
CREATE TABLE IF NOT EXISTS nonpartition_table(
id STRING,
name STRING,
year INT,
month INT,
day INT
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '|'
STORED AS TEXTFILE;
并为其插入数据,数据c.txt的内容如下
插入语句为
LOAD DATA LOCAL INPATH '/home/dongjinbao/data/c.txt' INTO TABLE nonpartition_table;
现在我们将nonpartition_table
表里的数据按照year
,month
,day
进行动态分区插入
INSERT INTO TABLE partition_table
PARTITION (year,month,day)
SELECT nonpt.id,nonpt.name,nonpt.year,nonpt.month,nonpt.day
FROM nonpartition_table nonpt;
SELECT语句中的最后三个字段与分区字段进行对应的,它们是通过位置来对应的,而不是字段名进行对应的。
动态分区的好处是不需要对分区字段的值进行指定,之前我们使用的分区插入数据属于静态分区,动态与静态是可以结合使用的,但是静态分区的字段必须出现在动态分区之前
INSERT INTO TABLE partition_table
PARTITION (year=2018,month,day)
SELECT nonpt.id,nonpt.name,nonpt.year,nonpt.month,nonpt.day
FROM nonpartition_table nonpt;
字段year属于静态分区字段,month和day属于动态分区字段,如果要使得month也是静态分区字段,那么year必须得先是静态分区字段。
动态分区一般是默认没有开启的,所以我们使用之前必须设置动态分区属性
set hive.exec.dynamic.partition.mod=nonstrict;
动态分区属性如下
属性 | 默认值 | 描述 |
---|---|---|
hive.exec.dynamic.partition | false | 设置成true,表示开启动态分区功能 |
hive.exec.dynamic.partition.mode | strict | 设置成nonstrict,表示允许所有分区都是动态的 |
hive.exec.max.dynamic.partitions.pernode | 100 | 每个mapper或reducer可以创建的最大动态分区个数 |
hive.exec.max.dynamic.partitions | 1000 | 一个动态分区创建语句可以创建的最大动态分区个数 |
hive.exec.max.created.files | 100000 | 全局可以创建的最大文件个数 |
5 创建时装载数据
我们可以在创建表的时候通过将查询结果载入这个表
CREATE TABLE create_table
AS SELECT * FROM partition_table
WHERE id='1';
载入到表里的数据同时也包含了分区字段的值。
6 导出数据
我们对数据进行了导入,同样也可以对数据进行导出,最直接的方式是从HDFS上将文件或者文件夹copy出来就能实现对表的导出,HiveQL也提供了导出表的语句
INSERT OVERWRITE LOCAL DIRECTORY '/home/dongjinbao/data'
SELECT * FROM partition_table
WHERE id='1';