03 hive 的表操作、数据加载、导出

教程 ⋅ 薪牛 ⋅ 于 2023-01-17 23:28:21 ⋅ 944 阅读

1 表SQL操作

1.1 通过select数据集创建表

--通过select数据集创建表语法格式
create table table_name [stored as orc]  
as
select  ......

只能是内部表，不支持分区，分桶

示例：

--创建内部表inner_test1
create table inner_test1 as select word,num from word_table;
--查看
select * from inner_test1;

file

1.2 复制一个空表

--只复制表结构，不复制表数据
CREATE TABLE tabname LIKE tablename;
create table inner_test2 like inner_test1;

file

也可以复制分区表

create table inner_test3 like student_par;

file

1.3 显示表列表

--显示所有表
SHOW TABLES;
-- 最通用的写法
show tables like '*user*';
-- 显示函数
show fuctions like '*count*';

结果显示

file

1.4 删除表

--删除表
DROP TABLE [IF EXISTS] table_name [PURGE];

说明：

​ 1）对于内部表来说，删除表的操作的本质是删除表的元数据，然后hadoop fs -rm(将表数据挪到回收站目录， .Trash/Current)。

​ 2）如果增加PURGE则不进回收站数据直接删除，不能恢复；

​ 3）对外部表而言只删除元数据， 不删除数据目录，加PURGE也不删。

示例：

drop table inner_test1;

file

对于外部表：

drop table book purge;

file

1.5 清除表（只针对内部表）

--清除表
TRUNCATE TABLE table_name [PARTITION partition_spec];

说明：

​ 1）可以删除表和删除分区数据，和drop的区别是不删除元数据（表结构），只删除数据，外部表是不能truncate操作的。

file

​ 2）外部表不支持使用TRUNCATE语句。

file

1.6 常用修改表操作

file

1.6.1 改表名

--改表名
ALTER TABLE table_name RENAME TO new_table_name;
--内部表修改表名
ALTER TABLE user_info RENAME TO user_info_2;
--外部表修改表名
alter table ext_test rename to ext_test_2;

说明：

​ 内部表修改了表名之后，表对应的存储文件地址也跟着改，相当于作了HDFS的目录重命名。

​ 外部表不会改对应的location地址。

示例：

-- 分区表外部表
CREATE EXTERNAL TABLE ext_test(
word STRING,
num INT
)
PARTITIONED BY (taskname STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
LOCATION '/user/cz/ext_test';
-- 分区表内部表
CREATE TABLE inner_test(
word STRING,
num INT
)
PARTITIONED BY (taskname STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';

hdfs路径：

file

内部表重命名

ALTER TABLE inner_test RENAME TO inner_test_c;

hdfs 作了目录重命名

file

外部表重命名

外部表hdfs位置：

file

ALTER TABLE ext_test RENAME TO ext_test_c;

hdfs位置没有发生变化

file

1.6.2 增加表分区

​ 内部表增加表分区，自动创建目录。

​ 外部表增加表分区，自动创建目录。

示例：

1）创建分区表：

-- 分区表外部表
CREATE EXTERNAL TABLE ext_task(
word STRING,
num INT
)
PARTITIONED BY (taskname STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
LOCATION '/user/cz/hive46/ext_task';
-- 分区表内部表
CREATE TABLE inner_task(
word STRING,
num INT
)
PARTITIONED BY (taskname STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';

2）添加表分区

-- 分区表外部表添加分区
alter table ext_task add IF NOT EXISTS partition(taskname='wordcount') location 'wordcount';
alter table ext_task add IF NOT EXISTS partition(taskname='maxword') location 'maxword';
alter table ext_task add IF NOT EXISTS partition(taskname='sortword') location 'sortword';
-- 分区表内部表添加分区
alter table inner_task add IF NOT EXISTS partition(taskname='wordcount') location 'wordcount';
alter table inner_task add IF NOT EXISTS partition(taskname='maxword') location 'maxword';
alter table inner_task add IF NOT EXISTS partition(taskname='sortword') location 'sortword';

分区目录在添加分区时自动创建

file

3）查看表的元数据和表分区元数据

-- 查询具体表的分区目录
select t1.`NAME`, t2.TBL_NAME,t4.PART_NAME, t3.LOCATION from DBS t1, TBLS t2 , SDS t3 , `PARTITIONS` t4 
where t1.DB_ID=t2.DB_ID and 
            t4.SD_ID = t3.SD_ID AND 
            t2.TBL_ID = t4.TBL_ID and 
            t1.`NAME` = 'hainiu' AND 
      t2.TBL_NAME like '%ext_task' 
UNION
-- 查询具体表的目录
select t1.`NAME`, t2.TBL_NAME,'null', t3.LOCATION from DBS t1, TBLS t2 , SDS t3 
where t1.DB_ID=t2.DB_ID and 
            t2.SD_ID = t3.SD_ID AND
            t1.`NAME` = 'hainiu' AND 
      t2.TBL_NAME like '%ext_task' ;

file

file

1.6.4 删除分区

--删除表分区的语法格式
ALTER TABLE table_name DROP [IF EXISTS] PARTITION partition_spec[, PARTITION partition_spec, ...];

说明：

​ 内部表删除分区，同时删除分区对应的目录。

​ 外部表删除分区不删除分区对应的目录。

示例：

-- 内部表删除分区
alter table inner_task DROP IF EXISTS partition(taskname='sortword');
-- 外部表删除分区
alter table ext_task DROP IF EXISTS partition(taskname='sortword');

内部表删除：sortword 分区

元数据删除：

file

数据也删除：

file

外部表删除：sortword 分区

元数据删除

file

数据没删除：

file

1.6.5 修改表或分区的路径

--修改表或分区路径的语法格式
ALTER TABLE table_name [PARTITION partition_spec] SET LOCATION "new location";

说明：

​ 内部表修改分区路径，元数据修改，但hdfs目录没有创建，等导入数据时创建，或者自己主动创建。

​ 外部表修改分区路径，元数据修改，但hdfs目录没有创建，等导入数据时创建，或者自己主动创建。

示例：

--内部表修改分区路径
alter table inner_task PARTITION  (taskname='wordcount') set location "hdfs://ns1/hive/warehouse/hainiu.db/inner_task/wordcount1";
--外部表修改分区路径
alter table ext_task PARTITION  (taskname='wordcount') set location "hdfs://ns1/user/cz/ext_task/wordcount1";

内部表：

修改分区路径：

file

导入数据后

insert into table inner_task partition(taskname='wordcount') select * from word_table;

file

-----------------------------

外部表：

修改分区路径：

file

导入数据后：

insert into table ext_task partition(taskname='wordcount') select * from word_table;

file

结论：无论是外部表还是内部表，修改分区路径，需要自己手动创建路径，或者等导入数据时创建。

1.6.6 分区重命名

--分区重命名语法格式
ALTER TABLE table_name PARTITION partition_spec RENAME TO PARTITION partition_spec;

说明：

​ 如果是内部表，分区重命名，分区对应的地址也会跟着改变，外部表不会。

示例：

--内部表分区重命名会改变分区对应的地址
alter table inner_task partition (taskname='maxword') rename to partition (taskname='maxword01');
--外部表分区重命名不会改变分区对应的地址
alter table ext_task partition (taskname='maxword') rename to partition (taskname='maxword01');

内部表修改分区名称，hdfs目录也修改

file

hdfs目录名称也修改

file

外部表修改分区名称，只修改元数据

file

file

insert into table ext_task partition(taskname='maxword01')
select word,num from word_table;

maxword01分区导入数据后

file

1.6.7 增加表字段

--增加表字段、使用新列集合替换现有数据列的语法格式
ALTER TABLE table_name ADD|REPLACE COLUMNS (col_name data_type [COMMENT col_comment], ...)

说明：

​ 1）ADD COLUMNS 可以在表列的最后和分区字段前面增加字段。

示例：

1）增加表字段

alter table ext_test add columns (test_col string);

file

导入数据

insert into table ext_test_c partition(taskname='wordcount')  select word,num, 'aaa' from word_table;

file

1.6.8 修改表字段

--语法格式
ALTER TABLE table_name CHANGE [COLUMN] col_old_name col_new_name column_type [COMMENT col_comment] [FIRST|AFTER column_name]

示例：

alter table ext_test_c CHANGE  COLUMN test_col test_col_new string;

file

3 数据加载

3.1 load 加载数据

向表添加数据除了可以使用insert语法（不推荐），还可以用hadoop put的方式向表中添加数据。

当然还有一种比较简单的用法就是可以直接通过load的方式加载数据。

file

3.1.1 语法

--load数据加载语法格式
LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)]

说明：

​ 1）hive 的数据加载不会对本地数据文件做任何处理，只是将文件或目录中的所有文件拷贝到表定义的数据目录,分桶表使用load加载数据会生成mapreduce任务，将数据分到多个桶文件进行存放

​ 2）指定local本地文件上传，如果没有指定local，则是从hdfs上传数据。

​ 3）文件加载hive没有做严格校验，文件格式和压缩选项等匹配需要用户自己保证。

​ 4）分区表要指定具体加载数据分区。

​ 5）如果指定OVERWRITE会覆盖相应表数据或分区数据，相当于 rm 原有目录数据，然后上传新数据文件

-- 以覆盖或追加的方式 ， 从linux本地 copy文件到表或表分区里
LOAD DATA LOCAL INPATH 'filepath' [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)]
--以覆盖或追加的方式 ， 从hdfs move 文件到表或表分区里
LOAD DATA INPATH 'filepath' [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)]

3.1.2 将hdfs中的文件load到表中(move)

需要把 ext_task1 创建出来

-- 分区表外部表
CREATE EXTERNAL TABLE ext_task1(
word STRING,
num INT
)
PARTITIONED BY (taskname STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
LOCATION '/user/cz/ext_task1';

1）先创建分区wordcount1

-- 创建分区
alter table ext_task1 add if not exists partition (taskname='wordcount') location 'wordcount';

2）再load数据到表分区wordcount中

-- 将数据放到hdfs
hadoop fs -put word /user/cz

file

--加载hdfs上的数据到表的分区中
load data INPATH '/user/cz/word' overwrite into table ext_task1 PARTITION (taskname='wordcount');

file

/user/cz这个目录，word文件已经mv走了

file

file

如果没有创建分区 wordcount01， load时，会自动创建分区目录，目录名为taskname=wordcount01

-- 将数据放到hdfs
hadoop fs -put word /user/cz

分区没有提前创建的时候，load时会自动创建

load data INPATH '/user/cz/word' overwrite into table ext_task1 PARTITION (taskname='wordcount01');

file

也可以使用into导入，into导入时相当于追加数据

load data INPATH '/user/cz/word'  into table ext_task1 PARTITION (taskname='wordcount01');

file

3.1.3 将本地文件load到表中

--添加partition
alter table ext_task1 add IF NOT EXISTS partition(taskname='wordcount02') location 'wordcount02';
--本地文件load到表中，相当于将本地文件上传到hdfs中，本地文件不删除
load data local INPATH '/home/hadoop/word' overwrite into table ext_task1 PARTITION (taskname='wordcount02');

注意：从本地load数据到表的时候，如果用的是overwrite，会帮我们创建一个分区目录，并将数据上传到新创建的目录下

file

并且linux本地的数据不会消失

file

load data local INPATH '/home/hadoop/word'  into table ext_task1 PARTITION (taskname='wordcount02');

追加导入的方式

file

推荐使用从hdfs的方式导入数据

3.2 通过select加载数据

file

我们还可以通过查询一张表的数据，将查询出来的数据导入另一张表

--通过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;

说明：

​ 1）当加了 IF NOT EXISTS，如果存在分区就跳过下面的select语句。

​ 2）INSERT OVERWRITE 会覆盖表或分区数据，但覆盖分区时增加IF NOT EXISTS，如果分区已经存在则不会覆盖。

​ 3） INSERT INTO 向表或分区追加数据，不影响历史数据。

示例：

1）将select的数据覆盖到表分区

--添加分区
alter table ext_task1 add IF NOT EXISTS partition(taskname='wordcount03') location 'wordcount03';
--导入数据
insert overwrite table ext_task1 partition(taskname='wordcount03')
select
word,
num
from word_table;

file

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

insert into table ext_task1 partition(taskname='wordcount03') 
select
word,
num
from word_table;

file

3.3 动态分区

如果有这样一个需求，从一张不是分区表中查询数据导入到分区表中。如果分区的个数比较多的时候，就需要我们多次查询导入，比如下面这个例子：

-- 学生表
CREATE TABLE student(
id int,
name string,
age int
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';
-- 学生表数据
1   name1   12
2   name2   12
3   name3   13
4   name4   13
5   name5   14
6   name6   14
7   name7   15
8   name8   15
-- 学生分区表
CREATE TABLE student_dyna(
id int,
name string
)
partitioned by (age int)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';
-- 需求：把学生表的数据导入的学生分区表中
-- 静态分区导入  适合增量数据的处理
-- 添加12岁的分区，导入12岁数据
alter table student_dyna add IF NOT EXISTS partition(age=12) location '12';
insert overwrite table student_dyna partition(age=12)  
select id,name from student where age=12;
-- 添加13岁的分区，导入13岁数据
alter table student_dyna add IF NOT EXISTS partition(age=13) location '13';
insert overwrite table student_dyna partition(age=13)  
select id,name from student where age=13;
-- 添加14岁的分区，导入14岁数据
alter table student_dyna add IF NOT EXISTS partition(age=14) location '14';
insert overwrite table student_dyna partition(age=14)  
select id,name from student where age=14;
-- 添加15岁的分区，导入15岁数据
alter table student_dyna add IF NOT EXISTS partition(age=15) location '15';
insert overwrite table student_dyna partition(age=15)  
select id,name from student where age=15;
------------------------
-- 历史数据导入，不适合静态分区，需要要用动态分区
-- hive默认不开启动态分区
--开启动态分区
set hive.exec.dynamic.partition=true;  
set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table student_dyna partition (age)  
select id, name, age FROM student;

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

--设置参数动态分区
--开启动态分区
set hive.exec.dynamic.partition=true;  
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
--将select数据，覆盖到表的动态分区
insert overwrite table table1 partition (ds, hr)  
select key, value, ds, hr FROM table2 WHERE ds is not null;

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

动态分区：分区 字段值必须是有限，可控的。

应用场景：已经有表，然后，有需求需要把原来表按照某个字段进行分区统计时，导入时，可以 开启动态分区一次性导入数据。

file

4 将数据写入文件系统

4.1 将数据写入一个文件

查询表数据导出到某个文件【linux本地/ hdfs】

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

示例：

1）将select的数据写入到hdfs文件中，多层目录会帮你创建

-- 查询ext_task 表中分区为wordcount 的数据，以AVRO 格式写入到hdfs指定目录
insert overwrite directory 'hdfs://ns1/user/cz/export_data/d1_avro' STORED AS AVRO
select word, num from ext_task where taskname='wordcount';

file

2）将select的数据写入到本地文件中， 多层目录会自动帮你创建

-- 查询ext_task 表中分区为wordcount 的数据，以text 格式写入到本地目录中
insert overwrite local directory '/home/hadoop/hive_test/d1_text'
select word, num from ext_task where taskname='wordcount';

file

4.2 将数据写入多个文件

--语法格式
FROM from_statement
INSERT OVERWRITE [LOCAL] DIRECTORY directory1 row_format
select_statement1 where 
[INSERT OVERWRITE [LOCAL] DIRECTORY directory2 row_format
select_statement2 where

说明：

​ 1）导出到文件系统的数据都序列化成text，非原始类型字段会序列化成json，导出文件以^A分隔 \n结尾的文本数据。

​ 2）INSERT OVERWRITE 到HDFS目录，可以通过MR job实现并行写入。这样在集群上抽取数据不仅速度快，而且还很方便。

​ 3）批量导出多个文件，需要导出文件的类型一致，如果一个是avro，一个是text，报错。

示例：

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

无论是否是分区表：如果多个输出目录，导出的格式要相同，否则报错

from ext_task1 
insert overwrite directory 'hdfs://ns1/user/cz/output_avro4' STORED AS orc 
select 
word,
num 
where taskname='wordcount01' 
insert overwrite local directory '/home/hadoop/hive_test/output_avro4' STORED AS orc 
select 
word,
num 
where taskname='wordcount02' ;

file

如果多个输出目录，导出的格式不同，会报错。

报错例子：

from student 
insert overwrite directory 'hdfs://ns1/user/cz/hive/output_avro5' STORED AS AVRO 
select 
id,name
where age=12
insert overwrite local directory '/home/hadoop/hive_test/0403/output_avro4' STORED AS orc 
select 
id,name
where age=13

file

4.3 hive -e 执行命令 导出

-- 导出数据
hive -e "use hainiu;select word,num from  ext_task where taskname='wordcount';" > ./output_txt4
-- 后台运行，关心错误信息，输出到某个文件里
nohup hive -e "use hainiu;select word,num from  ext_task where taskname='wordcount';" 1> ./output_txt5 2>./err.log &
-- 后台运行，错误信息不关心，放到无底洞里
nohup hive -e "use hainiu;select word,num from  ext_task where taskname='wordcount';" 1> ./output_txt6 2>/dev/null &

file

4.4 hive -f 执行SQL文件 导出

[hadoop@op 03]$ vim hivesql.sql
use hainiu; select * from ext_task where taskname='wordcount';
hive -f hivesql.sql > output_data2

file

版权声明:原创作品,允许转载，转载时务必以超链接的形式表明出处和作者信息。否则将追究法律责任。来自海汼部落－薪牛,http://hainiubl.com/topics/76159
点赞
回复数量: 0
    暂无评论~~
    • 请注意单词拼写，以及中英文排版，参考此页
    • 支持 Markdown 格式, **粗体**、~~删除线~~、`单行代码`, 更多语法请见这里 Markdown 语法
    • 支持表情，可用Emoji的自动补全, 在输入的时候只需要 ":" 就可以自动提示了 :metal: :point_right: 表情列表 :star: :sparkles:
    • 上传图片, 支持拖拽和剪切板黏贴上传, 格式限制 - jpg, png, gif，教程
    • 发布框支持本地存储功能，会在内容变更时保存，「提交」按钮点击时清空
    Ctrl+Enter
    新 建 话 题

    作者：薪牛

    薪牛 的其他话题

    分类下其他主题

    随机推荐话题

    QQ群1：551936262 海牛大数据QQ群1

    关注海牛部落大数据技术社区