hive DML 简单梳理

环境相关：
OS：CentOS release 6.9
IP：192.168.77.10
hadoop-2.6.0-cdh5.7.1
hive-1.1.0-cdh5.7.1
mysql5.1

1. 相关说明

HIVE的DML中，update和delete一般不会用，生产数据是不会删除的，常用overwrite和load做数据的转换。insert into values 也一般不会用，离线批处理，一些实时的数据insert场景并不多，这个命令只是从关系型数据库兼容过来的。

2. LOAD操作

HIVE的DML，从OS或者hdfs中load数据到表相关：

use test1;

create table emp(
empno int, 
ename string, 
job string, 
mgr int, 
hiredate string, 
sal double, 
comm double, 
deptno int)
row format delimited fields terminated by '\t';
-- 创建一张emp表
show create table emp;
-- 查看emp表的创建语句

create table dept(
deptno int,
dname  string,
loc    string)
row format delimited fields terminated by '\t';
-- 创建一张dept表
load data local inpath '/tmp/dept' overwrite into table dept;
-- 从OS文件装载数据到表

load data local inpath '/tmp/emp' overwrite into table emp;
-- 将OS上的对应目录下的对应文件上传关联到emp表
-- 该文件是oracle的scott.emp表的数据导出，列分隔符为tab
-- 使用overwrite 参数覆盖原表

load data local inpath '/tmp/emp' into table emp;
-- 再次上传，不使用关键字overwrite，追加数据到表中

-- 手动上传/tmp/emp为hdfs的一个目录下的test文件
-- 注意该目录不能和当前表一个目录
-- hdfs dfs -put /tmp/emp /user/hive/warehouse/test1.db/test
load data inpath '/user/hive/warehouse/test1.db/test' overwrite into table emp;
-- 将hdfs中的一个文件overwrite到emp表中
-- 该操作实际上是将hdfs的对应文件移动到了emp表对应的目录中
-- 因此该文件不能是emp目录中的文件

-- hdfs dfs -put /tmp/emp /user/hive/warehouse/test1.db/test
load data inpath '/user/hive/warehouse/test1.db/test' into table emp;
-- 再次关联，不使用关键字overwrite，追加数据到表中

3. INSERT到表的操作

HIVE的DML，insert表数据到另外一张表：

INSERT OVERWRITE TABLE tablename1
  [PARTITION (partcol1=val1, partcol2=val2 ...)
  [IF NOT EXISTS]]
  select_statement1 FROM from_statement;
INSERT INTO TABLE tablename1
  [PARTITION (partcol1=val1, partcol2=val2 ...)]
  select_statement1 FROM from_statement;

insert overwrite table emp2 select * from emp;
-- 表emp2结构和select子句查出来的列数量和类型要一致，否则报错
-- 如果列数量和类型一致，但是列顺序相反，则会造成业务上的问题
-- 比如将ename和job插入成了job和ename，或者俩job
-- 不会报错，但是表的数据就是混乱的了

insert into table emp2 select * from emp;
-- 注意点和上方一样，该操作是追加数据

4. INSERT到目录的操作

HIVE的DML，insert表数据到OS目录或hdfs目录相关：

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

use test1;
INSERT OVERWRITE LOCAL DIRECTORY '/tmp/test_emp'
  ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t"
  select * from emp;
-- 会查询test1.emp表，写入操作系统的/tmp/test_emp目录中的000000_0文件
-- 目录和文件会自动创建，当然运行hive的用户要有相应的OS权限才可以

INSERT OVERWRITE DIRECTORY '/user/hive/warehouse/insert_test_emp'
  ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t"
  select * from emp;
-- 查询test1.emp表，写入hdfs的/user/hive/warehouse/insert_test_emp目录的000000_0文件
-- 目录和文件会自动创建，当然运行hive的用户要有相应的hdfs权限才可以

FROM from_statement
INSERT OVERWRITE [LOCAL] DIRECTORY directory1 select_statement1
[INSERT OVERWRITE [LOCAL] DIRECTORY directory2 select_statement2] ...;

from (select * from emp) tmp
INSERT OVERWRITE LOCAL DIRECTORY '/tmp/hivetmp1'
ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t"
select empno, ename  
INSERT OVERWRITE DIRECTORY '/user/hive/warehouse/hivetmp2'
ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t"
select ename;
-- 从一个from子句，插入数据到多个目录
-- 如果数据来自一个from子句，那么这个子句必须起一个别名

from emp
INSERT OVERWRITE LOCAL DIRECTORY '/tmp/hivetmp2'
ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t"
select empno, ename  
INSERT OVERWRITE DIRECTORY '/user/hive/warehouse/hivetmp3'
ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t"
select ename;
-- 从一个表，插入数据到多个目录

5. SELECT操作

HIVE的DML，select相关：跟通用的SQL是一样的，略。
分组聚合会产生数据倾斜，而 union all 常用于处理数据倾斜问题。
CASE语句和通用的SQL语法中的CASE一样的：

select ename, salary, 
       case
        when salary > 1 and salary <= 1000 then 'LOWER'
        when salary > 1000 and salary <= 2000 then 'MIDDLE'
        when salary > 2000 and salary <= 4000 then 'HIGH'
        else 'HIGHEST'
       end
  from emp;
-- 就是标准的CASE语句格式

[TOC]