黑洞

这里藏着一些独特的想法

0%

Hive常用SQL之DML

load

load加载操作是将数据文件移动到与Hive表对应的位置的纯复制/移动操作。若文件从本地文件系统加载,则将文件复制一份到HDFS的数据库目录中;若文件从HDFS中加载,则将文件从原位置移动到数据库目录下。

使用语法:

1
2
LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO
TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)]

若指定local参数,表示从本地文件系统加载数据,反之从HDFS。

建议在Hive中使用load命令加载数据到表中,这也是Hive比Mysql加载数据高效地方所在。

值得注意的是,指定local后并不是从客户端的本地文件系统加载数据,而是从Hive服务端(HS2)的文件系统中寻找。

insert

与Mysql相同,Hive中也支持insert+values的方式插入数据,但是效率极其低下。因为底层是通过MapReduce插入数据的 因此实际中推荐使用load加载数据。

1
2
create table t_insert(id int,name string);
insert into table t_insert values(1,"allen");

insert + select

在hive中,insert主要是结合select查询语句使用,将查询结果插入到表中。

  • 保证后面select查询语句返回的结果字段个数、类型、顺序和待插入表一致
  • 如果不一致,Hive会尝试帮你转换,但是不保证成功;
  • insert+select也是在数仓中ETL数据常见的操作。
1
2
3
4
5
6
7
8
9
10
11
12
13
--step1:创建一张源表student
drop table if exists student;
create table student(num int,name string,sex string,age int,dept string)
row format delimited
fields terminated by ',';

--加载数据
load data local inpath '/root/hivedata/students.txt' into table student;

--step2:创建一张目标表 只有两个字段
create table student_from_insert(sno int,sname string);
--使用insert+select插入数据到新表中
insert into table student_from_insert select num,name from student;

Multi inserts 多重插入

  • 功能:一次扫描,多次插入
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
create table source_table (id int, name string) row format delimited fields terminated by ',';

create table test_insert1 (id int) row format delimited fields terminated by ',';
create table test_insert2 (name string) row format delimited fields terminated by ',';

--普通插入:
insert into table test_insert1 select id from source_table;
insert into table test_insert2 select name from source_table;

--在上述需求实现中 从同一张表扫描了2次 分别插入不同的目标表中 性能低下。

--多重插入:
from source_table
insert overwrite table test_insert1
select id
insert overwrite table test_insert2
select name;
--只需要扫描一次表 分别把不同字段插入到不同的表中即可 减少扫描次数 提高效率

Dynamic partition inserts 动态分区插入

  • 何谓动态分区,静态分区

    1
    2
    3
    4
    5
    6
    7
    8
    9
    针对的是分区表。
    --问题:分区表中分区字段值是如何确定的?

    1、如果是在加载数据的时候人手动写死指定的 叫做静态分区
    load data local inpath '/root/hivedata/usa_texas.txt'
    into table t_user_double_p partition(guojia="usa",sheng="texas");

    2、如果是通过insert+select 动态确定分区值的,叫做动态分区
    insert table partition (分区字段) + select
  • 栗子

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    --1、首先设置动态分区模式为非严格模式 默认已经开启了动态分区功能
    set hive.exec.dynamic.partition = true;
    set hive.exec.dynamic.partition.mode = nonstrict;

    --2、当前库下已有一张表student
    select * from student;

    --3、创建分区表 以sdept作为分区字段
    create table student_partition(Sno int,Sname string,Sex string,Sage int)
    partitioned by(Sdept string);

    --4、执行动态分区插入操作
    insert into table student_partition partition(Sdept)
    select num,name,sex,age,dept from student;
    --其中,num,name,sex,age作为表的字段内容插入表中
    --dept作为分区字段值

    select *
    from student_partition;

    show partitions student_partition;

导出数据

  • 功能:把select查询的结果导出成为一个文件。

  • 注意:导出操作是一个overwrite操作,将会清空整个目标文件夹,效果不亚于rm -rf /*

  • 语法

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    --当前库下已有一张表student
    select * from student;

    --1、导出查询结果到HDFS指定目录下
    insert overwrite directory '/tmp/hive_export/e1'
    select num,name,age
    from student limit 2; --默认导出数据字段之间的分隔符是\001

    --2、导出时指定分隔符和文件存储格式
    insert overwrite directory '/tmp/hive_export/e2'
    row format delimited fields terminated by ','
    stored as orc
    select * from student;

    --3、导出数据到本地文件系统指定目录下
    insert overwrite local directory '/root/hive_export/e1' select * from student;
如果觉得文章写得不错或对您有帮助,请我喝杯柠檬茶吧!