Hive中关于DML的一些事儿

CTE 和嵌套查询

CTE(Common Table Express)

-- CTE语法
with t1 as (select ...)select * from t1；

CTE嵌套
写语句不需要考虑顺序

嵌套查询

select * from(select ...) [as] t1;

join

hive仅支持等值查询

问：join有几种连接方式？

内连接，外连接，交叉连接，等值连接

内连接(inner join)

-- 左右两表都存在于连接条件相匹配的数据保留
select * from emp e join dept d on e.deptno=d.deptno;

外连接(outer join)

左外连接

-- join 左侧满足where条件的将被保留
select * from emp e left join dept d on e.deptno=d.deptno;

右外连接

-- join 右侧满足where条件的将被保留
select * from emp e right join dept d on e.deptno=d.deptno;

满外连接

-- 返回所有表中满足where的记录
select * from emp e full join dept d on e.deptno=d.deptno;

交叉连接(cross join)

德卡尔积式连接

隐式连接(implicit join)

hive中的map-join和reduce-join

1. hive.auto.convert.join

0.11版本默认为true

2. hive.mapjoin.smalltable.filesize/hive.smalltable.filesize
   由此参数决定小表的总大小，大小为25M左右，大概两三万行

Map join

小表关联大表(小表在左，大表在右)
可进行不等值计算

开启mapjoin(运行时自动将连接转换为mapjoin)

set hive.auto.convert.join = true（默认值）

在UNION ALL, LATERAL VIEW, GROUP BY/JOIN/SORT BY/CLUSTER BY/DISTRIBUTE BY等操作后面
在UNION, JOIN 以及其他 MAPJOIN之前

Union

所有子集数据必须具有相同的名称和类型

所有子集数据必须具有相同的名称和类型
集合其他操作可以使用JOIN/OUTER JOIN来实现(差集、交集)

union：去重
union：不去重

insert

数据插入到表

-- INSERT支持OVERWRITE(覆盖)和INTO(追加)
INSERT OVERWRITE/INTO TABLE tablename1 
[PARTITION (partcol1=val1, partcol2=val2 ...)] 
select fileds,... from tb_other

table 关键字可选

INSERT OVERWRITE TABLE test select 'hello'; -- INSERT不支持的写法
insert into employee select * from ctas_employee; -- 通过查询语句插入
-- 多插入

from ctas_employee              --高性能：只需扫描一次输入数据
insert overwrite table employee select *
insert overwrite table employee_internal select *;

-- 插入到分区
from ctas_patitioned 
insert overwrite table employee PARTITION (year, month)
select *,'2018','09';

-- 通过指定列插入(insert into可以省略table关键字)  
insert into employee(name) select 'John' from test limit 1;
							-- 指定列有利于 data schema changes

-- 通过指定值插入
insert into employee(name) value('Judy'),('John');

数据插入/导出到文件

insert导出

只支持overwrite
支持来自同一个数据源/表的多次插入
LOCAL：写入本地文件系统
默认数据以TEXT格式写入，列由^A分隔
支持自定义分隔符导出文件为不同格式,CSV,JSON等

-- 从同一数据源插入本地文件，hdfs文件，表
from ctas_employee
insert overwrite local directory '/tmp/out1'  select *
insert overwrite directory '/tmp/out1' select *
insert overwrite table employee_internal select *;

-- 以指定格式插入数据
insert overwrite directory '/tmp/out3'
row format delimited fields terminated by ','
select * from ctas_employee;

-- 其他方式从表获取文件
hdfs dfs -getmerge <table_file_path>

hadoop命令导出到本地

hive (default)> dfs -get /user/hive/warehouse/student/month=201709/000000_0
/opt/module/datas/export/student3.txt;

hive shell 命令导出

基本语法：（hive -f/-e 执行语句或者脚本 > file）

[atguigu@hadoop102 hive]$ bin/hive -e 'select * from default.student;' >
 /opt/module/datas/export/student4.txt;

Export导出到HDFS上

除数据库，可导入导出所有数据和元数据

(defahiveult)> export table default.student to
 '/user/hive/warehouse/export/student';
 
 # import导入数据
IMPORT TABLE employee FROM '/tmp/output3';
IMPORT TABLE employee_partitioned partition (year=2014, month=11) FROM '/tmp/output5';

Sqoop

目前不知道

数据排序

order by

employee_id 	1~100
employee_id>95	96~100		1
employee_id<=95	1~95		0

select * from employee_hr
order by case when employee_id>95 then 1 else 0 end desc
limit 10;

sort by

distrubute by

distrubuted by 语句要写在sort by

cluster by

分组

Group by

除了聚合函数，其他选中的字段必须出现值group by 中有体现

if(表达式，条件满足的值，条件不满足的值)

case when 条件表达式 then 条件满足的值
	 when 条件表达式 then 条件满足的值
     when 条件表达式 then 条件满足的值
	 else 条件不满足的值

having

用于筛选group by