Hive - DML整理版_综合

1. 相关说明

DML=Data Manipulation Language 数据操作语言。

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

2. LOAD操作

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

LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)]
LOCAL：本地系统
非LOCAL： HDFS
OVERWRITE: 覆盖
非OVERWRITE：追加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文件装载数据到表—[local]是本地系统，不写指的是HDFSload 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/testload 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/testload 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;-- 注意点和上方一样，该操作是追加数据16

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_statementINSERT OVERWRITE [LOCAL] DIRECTORY directory1 select_statement1[INSERT OVERWRITE [LOCAL] DIRECTORY directory2 select_statement2] ...;from (select * from emp) tmpINSERT 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 empINSERT 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, casewhen 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'endfrom emp;