Hive数据导入导出

数据导入

1.load 方式

--load数据加载语法格式
LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)]
-- 有local,从linux目录load--->复制 CP（原数据不变）
-- 无local,从 hdfs目录load--->剪切 MV（原数据消失）

说明：
• 分区表要指定具体加载到哪一个数据分区.        
• filepath 可以是目录,但该目录下不能包含子目录(只能有文件).
• 文件加载hive没有做严格校验,文件格式和压缩选项等匹配需要用户自己保证. 
• 有'LOCAL'代表Linux本地文件上传;无'LOCAL',则使用表一样的文件系统hdfs.
• hive 的数据加载不会对本地数据文件做任何处理,只是将文件或目录中的所有文件拷贝到表定义的数据目录;
• hive的数据加载会对hdfs上的数据文件进行mv操作，当你使用load data inpath ''的方式将数据load到hive表时，hdfs原有的文件会消失，但是目录会依旧存在  
• 如果指定overwrite会覆盖相应表数据或分区数据,相当于先删除原有目录(表)数据,再上传新数据文件到HDFS执行MV操作.

hdfs文件load到hive表

--先rm原有目录数据,然后上传新数据文件,hdfs执行mv操作.
load data INPATH 'hdfs://ns1/hive/age16' overwrite into table student_dyna PARTITION (age=16);

-- 由于之前没有创建分区age=16,load数据时,会自动创建分区目录,目录名为age=16;
-- 又因为加了'overwrite',所以其他分区的数据消失了.  

linux文件load到hive表

--添加partition
alter table ext_task add IF NOT EXISTS partition(taskname='wordcount03') location 'wordcount03';

load data LOCAL INPATH '/home/panniu/hive_test/0214/data' overwrite into table ext_task PARTITION  (taskname='wordcount03');

-- 加overwrite时，如果原来表内有数据，则新数据会覆盖老数据，老数据会被移到垃圾桶里
-- 不加overwrite时，相当于追加,追加到原有数据的后面
-- 本地load到表中时,即使给表创建分区,也不用创建分区目录,load时会自动创建目录.

2.select 方式

--通过select,将select数据覆盖表或分区的语法格式
INSERT OVERWRITE TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...) [IF NOT EXISTS]] select_statement1 FROM from_statement;

--通过select,将select数据追加到表或分区的语法格式
INSERT INTO TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...)] select_statement1 FROM from_statement;

说明：
• insert into 向表或分区追加数据,不影响历史数据.
• 当加了 IF NOT EXISTS,如果存在分区就跳过下面的select语句.
• insert overwrite 会覆盖表或分区数据;但覆盖分区时增加IF NOT EXISTS,如果分区已经存在则不会覆盖.

1）如果分区不存在,则先自动创建分区,再将select的数据覆盖到新创建的表分区.

--直接插入表中
insert overwrite table ext_task partition(taskname='wordcount04') IF NOT EXISTS
select
word,
num
from ext_task
where taskname='wordcount03';

--直接插入表分区路径中(执行完之后需要执行msck语句)
insert overwrite directory '/user/hive/warehouse/ext_task/taskname=wordcount04'
select
word,
num
from ext_task
where taskname='wordcount03';

msck repair table ext_task;

2）通过select,将select数据追加到表分区.

--直接追加插入表
insert into table ext_task partition(taskname='wordcount04')    
select
word,
num
from ext_task
where taskname='wordcount03';
--不能 insert into 到hdfs路径中，只能insert overwrite

3.import 方式

--  在Hive的命令行执行如下命令(仅限内部表)
import table xxx from '存放数据的hdfs地址';

数据导出

1.hive数据写入一个文件

--语法格式
INSERT OVERWRITE [LOCAL] DIRECTORY directory1 [ROW FORMAT row_format] [STORED AS file_format]  SELECT ... FROM ...

1）将select的数据写入到hdfs文件中,多层目录会自动创建.

-- 查询ext_task 表中分区为wordcount03 的数据,以AVRO 格式写入到hdfs指定目录;此处必须是 overwrite 不能是into,否则报错
insert overwrite directory 'hdfs://ns1/user/panniu/hive/output1' STORED AS AVRO
select word, num from ext_task where taskname='wordcount03';

注意：  
如果是分区表,导出数据时,导出的格式是avro、orc文件格式,如果不加分区导出时,会报错.
如何把分区表中所有数据都导出来avro、orc文件格式:需要通过临时表,作为中间表,先导出到中间表,再从中间表导出avro、orc文件格式的数据.

2）将select的数据写入到linux本地文件中,多层目录会自动帮你创建(本地加local).

-- 查询ext_task 表中分区为wordcount03 的数据，以AVRO 格式写入到本地目录中
insert overwrite local directory '/home/panniu/hive_test/0214/output1' STORED AS AVRO 
select word, num from ext_task where taskname='wordcount03';


-- 导出数据时，除了可以指定文件的存储格式，还可以指定列的分割符号
-- 查询ext_task 表中分区为wordcount03 的数据，以 TEXTFILE 格式写入到本地目录中，并将列使用','分割
insert overwrite local directory '/home/panniu/hive_test/0214/output1' 
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
STORED AS TEXTFILE
select word, num from ext_task where taskname='wordcount03';

2.hive数据写入多个文件

--语法格式
FROM from_statement
INSERT OVERWRITE [LOCAL] DIRECTORY directory1 row_format
select_statement1 where 
[INSERT OVERWRITE [LOCAL] DIRECTORY directory2 row_format
select_statement2 where ] ...
row_format
  : DELIMITED [FIELDS TERMINATED BY char [ESCAPED BY char]] [COLLECTION ITEMS TERMINATED BY char]
        [MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char]
        [NULL DEFINED AS char]

说明:
• 批量导出多个文件,需要导出文件的类型一致;如果一个是avro,一个是text,报错.
• 导出到文件系统的数据都序列化成text,非原始类型字段会序列化成json,导出文件以^A分隔 \n结尾的文本数据.
• INSERT OVERWRITE 到HDFS目录,可以通过MR job实现并行写入;这样在集群上抽取数据不仅速度快,而且还很方便.
-- 从一张表中导出两个文件到不同的路径
from ext_task 
insert overwrite directory 'hdfs://ns1/user/panniu/hive/output2' STORED AS AVRO 
select 
word,
num 
where taskname='wordcount03' 
insert overwrite local directory '/home/panniu/hive_test/0214/output2' STORED AS AVRO 
select 
word,
num 
where taskname='wordcount03';

3.通过hive -e 命令导出

# hive -e "sql"

# 导出的数据导出到out1;错误的信息，导出到err.log
nohup hive -e "use class10;select word, num from ext_task where taskname='wordcount03';" 1>./out1 2>./err.log &

# 数据输出，及错误输出均输出到u1
nohup hive -e "use class10;select * from user_install_status_limit;" > ~/u1  2>&1 &

# 数据输出到u3,错误日志输出到无底洞
nohup hive -e "use class10;set mapred.reduce.tasks=1;select * from user_install_status_limit;" 1> ~/u3 2> /dev/null &

# 数据输出到u3,错误日志输出到err.log
nohup hive -e "use class10;set mapred.reduce.tasks=1;select * from user_install_status_limit;" 1> ~/u3 2> ~/err.log &

说明：
1.使用hive -e的方式是将这些语句写到shell脚本中
2.将sql执行的结果写入到文件，文件可以不存在，执行的过程中会自动创建
3.sql执行的结果既可以'追加'--(>>)，又可以'覆盖'--(>)

4.export 方式

--  在Hive的命令行执行如下命令(仅限内部表)
export table xxx to '数据存放的目标hdfs地址';