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,追加数据到表中
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;
-- 注意点和上方一样,该操作是追加数据
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;
-- 从一个表,插入数据到多个目录
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;
来自@若泽大数据