HIVE操作语句--DML篇

未经允许，禁止转载，一经发现，必定严究

1.1 数据导入

1）向表中装载数据（Load）

load data [local] inpath '数据路径' [overwrite] into table table_name [partition (partcoll=vall,...)];

load data：加载数据

local：表示从本地加载数据到 hive 表，否则从 HDFS 加载数据到 hive 表

inpath：表示加载数据的路径

overwrite：表示覆盖表中已有的数据，否则表示追加

into table：表示加载到具体哪张表

2）向表中装载数据（Insert）

insert into table_name values(...);
insert into table_name select * from table_names;
insert overwrite table table_name values(...);
insert overwrite table table_name select * from table_names;

insert into：以追加数据的方式插入到表或分区，原有数据不会删除

insert overwrite：会覆盖表中已存在的数据

3）建表时，向表中装载数据（as select）

create table table_name as select id from table_names;

注：相当于创建新数据表

4）建表时，向表中装载数据（location）

create table table_name(id int) location '集群数据路径';
hdfs dfs -put '本地数据' '集群数据路径';

5）向表中装载数据（import）

import table table_name from 'export导出的数据路径';

注：先用 export 导出数据路径，才能用 import 导入数据，export会将数据表的元数据与数据一同导出到HDFS上

1.2 数据导出

主要分为 Insert 以及 Export 两种导出方式。

Insert导出

1）将查询结果导出到本地

insert overwrite local directory
'本地路径'
select * from table_name;

2）将查询结果格式化导出到本地

insert overwrite local directory '本地路径'
row format delimited fields terminated by '\t'
select * from table_name;

3）将查询结果格式化导出到HDFS上

insert overwrite directory 'HDFS路径'
row format delimited fields terminated by '\t'
select * from table_name;

Export导出

3）将表中数据导出到HDFS

export table table_name to '本地路径';

export 和 import 主要用于两个 Hadoop 平台集群之间的 Hive 表迁移。

注：export会以文件的形式将表数据导出，表数据导出后包括表的元数据信息，以及表中的数据；

1.3 清除表中数据

truncate table table_name;

1.4 查看建表语句

show create table table_name;

1.4 查询语句

SELECT [ALL | DISTINCT] select_expr, select_expr, ...
FROM table_reference
[WHERE where_condition]
[GROUP BY col_list]
[HAVING condition(条件)]
[ORDER BY col_list]
[
    CLUSTER BY col_list | [DISTRIBUTE BY col_list] [SORT BY col_list]
]
[LIMIT number]

1.5 七种 JOIN

1）内连接

连接的两个表中存在与连接条件相匹配的数据才会被保留下来。

select o.id,o.name,t.class from table_name o (inner) join table_name_two t on o.num = t.num;

2）左外连接

JOIN 操作符左边表中符合 WHERE 子句的所有记录将会被返回。

select o.id,o.name,t.class from table_name o left (out) join table_name_two t on o.num = t.num;

3）右外连接

JOIN 操作符右边表中符合 WHERE 子句的所有记录将会被返回。

select o.id,o.name,t.class from table_name o right (out) join table_name_two t on o.num = t.num;

4）满连接

将会返回所有表中符合 WHERE 语句条件的所有记录。如果任一表的指定字段没有符合条件的值的话，那么就使用 NULL 值替代。

select o.id,o.name,t.class from table_name o full join table_name_two t on o.num = t.num;

5）取左表独有数据

select o.id,o.name,t.class from table_name o 
left (out) join table_name_two t 
on o.num = t.num
where t.num is null;

6）取右表独有数据

select o.id,o.name,t.class from table_name o 
right (out) join table_name_two t 
on o.num = t.num
where o.num is null;

7）取两表独有数据

select o.id,o.name,t.class from table_name o 
right (out) join table_name_two t 
on o.num = t.num
where o.num is null 
or t.num is null;

8）多表连接

连接 N 个表时，需要 N-1 个连接条件。

select s.id,s.name,t.class from student s 
join teacher t on s.num = t.num
join grade g on t.tepd = g.tepd;

1.6 排序

1）全局排序（Order By）

Order By 全局排序，只有一个 Reducer ，ACS升序，DESC降序。

select * from table_name order by column_name;

2）每个 Reduce 内部排序（Sort By）

Order By 对于大规模数据集进行，效率降低。在不需要全局排序的时候可以使用 Sort By 进行排序。Sort By 为每个 Reducer 产生一个排序文件，对每个 Reducer 进行排序，对全局结果来说不是排序。

select * from table_name sort by column_name;

3）分区（Distribute By）

将数据进行分区，通常与 Sort By 一起使用，先进行分区，再进行排序。

select * from table_name distribute by column_name_one sort by column_name_two;

distribute by 分区规则是根据分区字段的 hash 码与 reduce 的个数进行取模后，余数相同的分配到同一个分区。

4）Cluster By

cluster by 不仅仅有 distribute by 的功能，而且兼具 sort by 的功能，但是只能进行升序排序，不能指定排序规则（ASC|DESC）。

select * from table_name cluster by column_name;

HIVE 合集

1、hive 操作语句–DDL篇
2、hive 操作语句–DML篇