excel hive查询导出 hive数据导入导出

转载

云端创新梦想家 2023-07-11 13:50:32

文章标签 excel hive查询导出 hive 数据 hdfs 文章分类 Hive 大数据

数据导入

load 加载数据

load数据加载语法格式 []内为可选
LOAD DATA [LOCAL] INPATH ‘filepath’ [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 …)]

说明：
1）hive 的数据加载不会对本地数据文件做任何处理，只是将文件或目录中的所有文件拷贝到表定义的数据目录。
2）filepath 可以是目录，但该目录下不能包含子目录。
3）指定LOCAL本地文件上传，如果没有指定LOCAL，则使用表一样的文件系统 (本地文件不删除)。
4）文件加载hive没有做严格校验，文件格式和压缩选项等匹配需要用户自己保证。
5）分区表要指定具体加载数据分区。
6）如果指定OVERWRITE会覆盖相应表数据或分区数据，相当于 rm 原有目录数据，然后上传新数据文件

将hdfs中的文件load到表中

先 rm 原有目录数据，然后上传新数据文件, hdfs 执行mv操作
load data INPATH ‘/user/su/task/wordcount_su’ overwrite into table inner_task2 PARTITION (taskname=‘wordcount’);

执行完后hdfs中原目录数据被移动到表目录下

如果load之前表中没有对应的分区，则自动创建分区目录
load之前

hive> show partitions uis_partition;
OK
dt=20141117
Time taken: 0.078 seconds, Fetched: 1 row(s)

创建一个结构相同的表

hive > create table uis_partition2 (
     >   `aid` string, 
     >   `pkgname` string, 
     >   `uptime` bigint, 
     >   `type` int, 
     >   `country` string, 
     >   `gpcategory` string)
     > PARTITIONED BY ( 
     >   `dt` string);
OK
Time taken: 2.08 seconds

在uis_partition2中创建一个与uis_partition不同的分区，并插入数据

hive > alter table uis_partition2 add partition(dt='20141228') location '2014/12/28';
hive > insert overwrite table uis_partition2 partition(dt='20141228')
     > select 
     > aid,pkgname,uptime,type,country,gpcategory
     > from user_install_status_orc
     > where dt = '20141228';

将uis_partition2 的分区目录导入到uis_partition中，uis_partition多了uis_partition2的分区，而uis_partition2的分区虽然还在，但数据消失了，被移动到了uis_partition

hive > load data inpath 'hdfs://ns1/hive/warehouse/wangchen22_test.db/uis_partition2/2014/12/28' overwrite into table uis_partition partition(dt='20141228');
hive (wangchen22_test)> show partitions uis_partition;
OK
dt=20141117
dt=20141228
Time taken: 0.067 seconds, Fetched: 2 row(s)

hive > show partitions uis_partition2;
OK
dt=20141228
Time taken: 0.062 seconds, Fetched: 1 row(s)
hive > select * from uis_partition2 where dt='20141228' limit 10;
OK
Time taken: 0.117 seconds

将本地文件load到表中

也会自动创建分区表

hive > load data local inpath '/home/wangchen22/file1' overwrite into table uis_partition partition(dt='20141222');

hive > show partitions uis_partition;
OK
dt=20141117
dt=20141222
dt=20141228

filepath不能包含子目录

hive (wangchen22_test)> load data local inpath '/home/wangchen22' overwrite into table uis_partition partition(dt='20141222');
FAILED: SemanticException Line 1:23 Invalid path ''/home/wangchen22'': source contains directory: file:/home/wangchen22/d1

select加载数据

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

overwrite和into只能有一个

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

hive > insert overwrite table uis_partition partition(dt='20141221')
     > select aid,pkgname,uptime,type,country,gpcategory
     > from user_install_status_orc
     > where dt='20141221';
     
自动创建了分区
hive > show partitions uis_partition;
OK
dt=20141117
dt=20141221
dt=20141222
dt=20141228

动态分区

Hive默认是静态分区，我们在插入数据的时候要手动设置分区，如果源数据量很大的时候，那么针对一个分区就要写一个insert，
比如说，有很多我们日志数据，我们要按日期作为分区字段，在插入数据的时候手动去添加分区，那样太麻烦。

因此，Hive提供了动态分区，动态分区简化了我们插入数据时的繁琐操作。

设置动态分区参数

开启动态分区
set hive.exec.dynamic.partition=true;
这个属性默认是strict，即限制模式，strict是避免全分区字段是动态的，必须至少一个分区字段是指定有值即静态的，且必
须放在最前面。设置为nonstrict之后所有的分区都可以是动态的了。
set hive.exec.dynamic.partition.mode=nonstrict;
表示每个节点生成动态分区的最大个数，默认是100
set hive.exec.max.dynamic.partitions.pernode=10000;
表示一个DML操作可以创建的最大动态分区数，默认是1000
set hive.exec.max.dynamic.partitions=100000;
表示一个DML操作可以创建的最大文件数，默认是100000
set hive.exec.max.created.files=150000;

建议不将表的所有字段做全分区字段，建议指明分区名，然后再插入数据。

创建原始表和分区表

-- 学生表
CREATE TABLE student(
id int,
name string,
age int
)

-- 学生动态分区表
CREATE TABLE student_dyna(
id int,
name string
)
partitioned by (age int)

--导入数据到student后
hive > select * from student;
OK
1       name1   12
2       name2   12
3       name3   13
4       name4   13
5       name5   14
6       name6   14
7       name7   15
8       name8   15

开启自动分区

set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;

查询数据动态插入student_dyna表

insert overwrite table student_dyna partition(age) 
select
id,name,age
from student;

hive > select * from student_dyna;
OK
1       name1   12
2       name2   12
3       name3   13
4       name4   13
5       name5   14
6       name6   14
7       name7   15
8       name8   15

查看分区目录：

hive > dfs -ls  hdfs://……/student_dyna;
Found 4 items
drwxrwxrwx   - wangchen22 supergroup          0 2020-05-31 11:29 hdfs://……/student_dyna/age=12
drwxrwxrwx   - wangchen22 supergroup          0 2020-05-31 11:29 hdfs://……/student_dyna/age=13
drwxrwxrwx   - wangchen22 supergroup          0 2020-05-31 11:29 hdfs://……/student_dyna/age=14
drwxrwxrwx   - wangchen22 supergroup          0 2020-05-31 11:29 hdfs://……/student_dyna/age=15

数据导出

将数据写入文件系统

将数据写入一个文件

语法格式

INSERT OVERWRITE [LOCAL] DIRECTORY directory1
 [ROW FORMAT row_format] [STORED AS file_format]
 SELECT … FROM …

导出到hdfs
insert overwrite directory 'hdfs://ns1/user/test/output' 
stored as avro
select * from student;
导出到本地
insert overwrite local directory '/home/user' 
stored as avro
select * from student;

将数据写入多个文件

语法格式
 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]

说明：
1）导出到文件系统的数据都序列化成text，非原始类型字段会序列化成json，导出文件以^A分隔 \n结尾的文本数据。
2）INSERT OVERWRITE 到HDFS目录，可以通过MR job实现并行写入。这样在集群上抽取数据不仅速度快，而且还很方便。
3）批量导出多个文件，需要导出文件的类型一致，如果一个是avro，一个是text，报错。

从一张表中导出两个文件到不同的路径、

from student
insert overwrite directory '/user/test/output' stored as textfile
select * 
insert overwrite local directory '/home/user' stored as textfile
select * ;