HiveQL:数据操作-导入和导出

–向管理中加载数据 (分区目录不存在，则会先创建分区)

load data local inpath '${env:HOME}/california-employees'
overwirte into table employees
partition (country = 'US', state ='CA')

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

insert overwrite table employees
partition (country = 'US', state = 'OR')
select * from staged_employee se 
where se.cntry = 'US' AND se.st = 'OR';

–通过查询语句向表中插入数据(使用分区)

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'

–动态分区插入数据

insert overwrite table employees
partition (country , state)
select ...,se.cnty, se.st
from staged_employees se;

–静态+动态分区插入数据

insert overwrite table employees
partition (country = 'US', state)
select ...,se.cnty, se.st
from staged_employees se
where sc.cnty = 'US';

–单个查询语句中创建表并加载数据

create table ca_employees
as select name ,salary ,address 
from employees 
where sc.state = 'CA';

–导出数据

insert overwirte local directory '/tmp/ca_employees'
select name, salary ,address
from employees
where sc.state = 'CA';

–批量导出数据

from staged_employees se
insert overwrite directory 'tmp/or_employees'
	select * where se.vty = 'US' and se.st = 'OR'
insert overwrite directory 'tmp/ca_employees'
	select * where se.vty = 'US' and se.st = 'CA'
insert overwrite directory 'tmp/il_employees'
	select * where se.vty = 'US' and se.st = 'IL'

–参考<<Hive权威指南>>