HIVE 第四章 数据操作

 

数据操作篇

加载数据（会生成partition，如果不存在的话；local字段表示为是本机目录，如果不加，则表示为hdfs上的目录；overwrite关键字表示删除目标目录，当没有则保留，但会覆盖同名旧目录）一些限制条件：使用partition，则table必须是partition的；hive不会去验证你的data是服务schema的，但是hive会验证文件是否符合表定义，如sequencefile的table，file也必须是sequence files

load data local inpath '/home/hadoop/hive/data'

overwrite into table employees

partition(country='china',state='beijing');

静态插入数据：

插入数据(select * from employee 这样选择一个分区表，会将其partition的column也返回)

insert overwrite table employees

partition (country='US',state='dallas')

select name,salary,subordinate,deductions,address from employees se

where se.country='china' and se.state='beijing'

插入多条数据

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';

动态插入并创建partition：

INSERT OVERWRITE TABLE employees

PARTITION (country, state)

SELECT ..., se.cnty, se.st

FROM staged_employees se;

动态静态创建partition

INSERT OVERWRITE TABLE employees

PARTITION (country = 'US', state)

SELECT ..., se.cnty, se.st

FROM staged_employees se

WHERE se.cnty = 'US';

 

有一些参数控制，动态创建partition

hive.exec.dynamic.partition

false 

Set to true to enable dynamic partitioning.

 

hive.exec.dynamic.partition.mode

strict 

Set to nonstrict to enable all partitions to be determined

dynamically.

 

hive.exec.max.dynamic.partitions.pernode

100 

The maximum number of dynamic partitions that can be created

by each mapper or reducer. Raises a fatal error if one

mapper or reducer attempts to create more than the threshold.

 

hive.exec.max.dynamic.partitions

+1000 

The total number of dynamic partitions that can be created by

one statement with dynamic partitioning. Raises a fatal error

if the limit is exceeded.

 

hive.exec.max.created.files

100000 

The maximum total number of files that can be created globally.

A Hadoop counter is used to track the number of files created.

Raises a fatal error if the limit is exceeded.

 

创建表，并加载数据

CREATE TABLE ca_employees

AS SELECT name, salary, address

FROM employees

WHERE se.state = 'CA';

 

导出数据

INSERT OVERWRITE LOCAL DIRECTORY '/tmp/ca_employees'

SELECT name, salary, address

FROM employees

WHERE se.state = 'CA';

 

批量导出数据

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'

INSERT OVERWRITE DIRECTORY '/tmp/il_employees'

SELECT * WHERE se.cty = 'US' and se.st = 'IL';