Hive-HQL之数据操作

数据导入

装载数据(Load)

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

LOCAL：

LOAD DATA LOCAL … 从本地文件系统加载数据到Hive表中。本地文件会拷贝到Hive表指定的位置
LOAD DATA … 从HDFS加载数据到Hive表中。HDFS文件移动到Hive表指定的位置
INPATH：加载数据的路径
OVERWRITE：覆盖表中已有数据；否则表示追加数据
PARTITION：将数据加载到指定的分区

-- 创建表
CREATE TABLE tabA (
  id int ,
  name string ,
  area string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' ;

数据文件（~/data/sourceA.txt）：
1,fish1,SZ
2,fish2,SH
3,fish3,HZ
4,fish4,QD
5,fish5,SR

-- 拷贝文件到
HDFS hdfs dfs -put sourceA.txt data/

-- 加载本地文件到hive(tabA)
LOAD DATA LOCAL INPATH '/home/hadoop/data/sourceA.txt' INTO TABLE tabA;
-- 检查本地文件还在

-- 加载hdfs文件到hive(tabA)
LOAD DATA INPATH 'data/sourceA.txt' INTO TABLE tabA;
-- 检查HDFS文件，已经被转移

-- 加载数据覆盖表中已有数据
LOAD DATA INPATH 'data/sourceA.txt' OVERWRITE INTO TABLE tabA;

-- 创建表时加载数据
hdfs dfs -mkdir /user/hive/tabB
hdfs dfs -put sourceA.txt /user/hive/tabB
CREATE TABLE tabB (
  id INT ,
  name string ,
  area string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
Location '/user/hive/tabB';

插入数据(Insert)

-- 创建分区表
CREATE TABLE tabC (
  id INT ,
  name string ,
  area string
)
partitioned by (month string)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';

-- 插入数据
insert into table tabC partition(month='202001') values (5, 'wangwu', 'BJ'), (4, 'lishi', 'SH'), (3, 'zhangsan', 'TJ');

-- 插入查询的结果数据
insert into table tabC partition(month='202002') select id, name, area from tabC where month='202001';

-- 多表（多分区）插入模式
from tabC
insert overwrite table tabC partition(month='202003')
select id, name, area where month='202002'
insert overwrite table tabC partition(month='202004')
select id, name, area where month='202002';

创建表并插入数据(as select)

1 2	-- 根据查询结果创建表可复制结构和数据，但表结构可能发生变化，如分区会缺失。 create table if not exists tabD as select * from tabC;

插入数据(import)

1	import table student2 partition(month='201709') from '/user/hive/warehouse/export/student';

数据导出

-- 将查询结果导出到本地
insert overwrite local directory '/home/hadoop/data/tabC' select * from tabC;

-- 将查询结果格式化输出到本地
insert overwrite local directory '/home/hadoop/data/tabC2'
row format delimited fields terminated by ' '
select * from tabC;

-- 将查询结果导出到HDFS
insert overwrite directory '/user/hadoop/data/tabC3'
row format delimited fields terminated by ' '
select * from tabC;

-- dfs 命令导出数据到本地。本质是执行数据文件的拷贝
dfs -get /user/hive/warehouse/mydb.db/tabc/month=202001 /home/hadoop/data/tabC4

-- hive 命令导出数据到本地。执行查询将查询结果重定向到文件
hive -e "select * from tabC" > a.log

-- export 导出数据到HDFS。使用export导出数据时，不仅有数还有表的元数据信息
export table tabC to '/user/hadoop/data/tabC4';

-- export 导出的数据，可以使用 import 命令导入到 Hive 表中
-- 使用 like tname创建的表结构与原表一致。create ... as select ... 结构可能不一致,但是新建的表中有数据
create table tabE like tabc;
import table tabE from /user/hadoop/data/tabC4';

-- 截断表，清空数据。(注意：仅能操作内部表)
truncate table tabE;

-- 以下语句报错，外部表不能执行 truncate 操作
alter table tabC set tblproperties("EXTERNAL"="TRUE"); truncate table tabC;