数据导入

Load

hive提供了load命令，可以将数据文件直接导入进表中。

语法：

LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename 
[PARTITION (partcol1=val1,partcol2=val2 ...)]

（1）load data:表示加载数据
（2）local:表示从本地加载数据到 hive 表；否则从 HDFS 加载数据到 hive 表
（3）inpath:表示加载数据的路径
（4）overwrite:表示覆盖表中已有数据，否则表示追加
（5）into table:表示加载到哪张表
（6）tablename :表示具体的表
（7）partition:表示上传到指定分区

案例：
创建一张用户表，并将一份用户数据导入到hive表中。

# 创建表并指定分隔符为,
create table t_user(id int,name string,age int) row format delimited fields  terminated by ',';
# 准备输入文件
vim user.txt
0001,zhangsan,18
0002,lisi,20
0003,wangwu,30
# 从本地文件load数据，会复制到HDFS中，未加local 将从HDFS路径中查找，并剪切数据到表目录中
load data local inpath './user.txt' into table t_user ;
# 查询数据
select * from t_user;

ast hive 语法树解析 hive load语法_ast hive 语法树解析

Insert

# 插入数据
insert into table t_user values(0004,'wuming',90),(0005,'bujingyun',28);
# 插入数据并覆盖表中已存在的数据
insert overwrite  table t_user values(0004,'wuming',90),(0005,'bujingyun',28);
# 将单张表的查询结果数据导入到hive表中
# 单表
insert into table t_user select id ,name ,age from test01;
# 多表
from t_user
insert overwrite table test01
select id, name ，age 
insert overwrite table test02
select id, name ，age

As Select

把表中数据导入到另一张空表中，会自动创建数据结构

create table if not exists test4
as select * from t_user;

Location

创建表时通过 Location 指定加载数据路径导入数据。

# 创建HDFS目录，并将数据文件上传到此目录
hive (d_test)> dfs -mkdir /test/;
hive (d_test)> dfs -put ./t_user01.txt /test;
# 创建表并导入数据
hive (d_test)>  create external table if not exists t_user01
             > (id int,name string ,age int)
             > row format delimited fields terminated by ','
             > location '/test';
# 查看导入的数据
select * from t_user01;

数据导出

Insert导出

# 将查询的结果导出到本地（未格式化）
mkdir -p /tmp/test
hive (d_test)> insert overwrite local directory '/tmp/test'
             > select * from t_user;
# 将查询的结果导出到本地（用‘,’作为分隔符）
hive (d_test)> insert overwrite local directory '/tmp/test'
             > row format delimited fields terminated by ','
             > select * from t_user;
# 将查询的结果导出到HDFS中
hive (d_test)> insert overwrite  directory '/test'
             > row format delimited fields terminated by ','
             > select * from t_user;

Shell命令导出

语法：

# 把SQL语句查询结果写入到文件中
hive -e "sql"  > xxx.txt
# 读取文件xxx.sql中的SQL，并把SQL语句查询结果写入到文件中
hive -f  xxx.sql > xxx.txt
# 示例
hive -e 'select * from d_test.t_user;' > /opt/soft/test/user.txt

Export导出

将hive表中的数据导出到外部。
语法：

EXPORT TABLE tablename [PARTITION (part_column="value"[, ...])]
  TO 'export_target_path' [ FOR replication('eventid') ]

案例：

export table d_test.t_user to '/test/t_user';

查询

语法：

SELECT [ALL | DISTINCT] select_expr, select_expr, ...
  FROM table_reference
  [WHERE where_condition]
  [GROUP BY col_list]
  [ORDER BY col_list]
  [CLUSTER BY col_list
    | [DISTRIBUTE BY col_list] [SORT BY col_list]
  ]
 [LIMIT [offset,] rows]

准备数据：

cd /opt/soft/test
vim dept.txt
# 插入
10 ACCOUNTING 1700
20 RESEARCH 1800
30 SALES 1900
40 OPERATIONS 1700
# 
vim emp.txt
# 插入
7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30
7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30
7566 JONES MANAGER 7839 1981-4-2 2975.00 20
7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30
7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30
7782 CLARK MANAGER 7839 1981-6-9 2450.00 10
7788 SCOTT ANALYST 7566 1987-4-19 3000.00 20
7839 KING PRESIDENT 1981-11-17 5000.00 10
7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30
7876 ADAMS CLERK 7788 1987-5-23 1100.00 20
7900 JAMES CLERK 7698 1981-12-3 950.00 30
7902 FORD ANALYST 7566 1981-12-3 3000.00 20
7934 MILLER CLERK 7782 1982-1-23 1300.00 10
7369 SMITH CLERK 7902 1980-12-17 800.00 20
# 创建数据库
cd /opt/soft/hive-2.3.7/bin
hive
# 部门表
hive (default)> create table if not exists dept(
              > deptno int,
              > dname string,
              > loc int
              > )
              > row format delimited fields terminated by '\t';
# 员工表
hive (default)> create table if not exists emp(
              > empno int,
              > ename string,
              > job string,
              > mgr int,
              > hiredate string, 
              > sal double, 
              > comm double,
              > deptno int)
              > row format delimited fields terminated by '\t';
# 导入数据
load data local inpath '/opt/soft/test/dept.txt' into table dept;
load data local inpath '/opt/soft/test/emp.txt' into table emp;

基本查询

# 全表查询
select * from emp;
# 特定列全表查询
select empno, ename, job from emp;
# 列别名AS,可以省略
select empno as nu, ename as name, job from emp;
select empno  nu, ename  name, job from emp;

算术运算符

ast hive 语法树解析 hive load语法_java_02

# 算术运算（+ - * / %...）所有的empno加上9
select empno+9  as nu  from emp;

常用函数

# 总记录数(count)
select count(*) count from emp;
# 最大值(max)
select max(sal) max_sal from emp;
# 最小值（min)
select min(sal) min_sal from emp;
# 总和（sum）
select sum(sal) sum_sal from emp;
# 平均值（avg）
select avg(sal) avg_sal from emp;

Limit限制返回的行数

select *  from emp limit 1 ;

Where行过滤

# 查询名字为KING的记录
select * from emp where ename = 'KING';

比较运算符

ast hive 语法树解析 hive load语法_hive_03


ast hive 语法树解析 hive load语法_ast hive 语法树解析_04

# 查询工资大于2000的员工
select * from emp where sal > 2000;
# 查询 comm 为空的所有员工信息
select * from  emp where comm is null
# 查询mgr及comm都为null的员工
select * from emp where mgr  <=> comm;
# 查询工资1000到1500的员工
select sal  from emp where sal between 1000 and 1500;
# 查询工资是 1500 或 5000 的员工信息
select * from  emp where sal in (1000,1500);
# 模糊查询查名字以 A 开头的员工信息
select * from emp where ename LIKE 'A%';

逻辑运算符

ast hive 语法树解析 hive load语法_hive_05

# 查询工资大于1000并且为30部门的员工
select * from emp where sal > 1000 and deptno = 30;
# 查询工资小于等于1000或者工资>=3000的人
select * from emp sal where sal <= 1000 or sal >=3000;
# 查询工资大于等于1000并且部门不是20的员工
select * from emp sal where sal >= 1000 and not deptno = 20 ;

Group By

GROUP BY 语句用于结合聚合函数，根据一个或多个列对结果集进行分组。

# 查询每个部门的总工资数
select deptno,sum(sal) sam_sql from emp group by deptno;
# 计算 emp 每个部门中每个岗位的最高薪水
select deptno,job,max(sal) max_sal from emp group by deptno,job ;

Having

WHERE 关键字无法与聚合函数一起使用。HAVING 子句可以让我们筛选分组后的各组数据。

# 求每个部门的平均薪水大于 2000 的部门
select deptno, avg(sal) avg_sal from emp group by deptno having avg_sal > 1500;

Join

JOIN 子句用于把来自两个或多个表的行结合起来，基于这些表之间的共同字段。类似于SQL join 。

ast hive 语法树解析 hive load语法_数据库_06

内连接 INNER JOIN

关键字在表中存在至少一个匹配时返回行。

# 
select * from emp inner join dept on dept.deptno = emp.deptno;

左外连接LEFT JOIN

从左表（table1）返回所有的行，即使右表（table2）中没有匹配。如果右表中没有匹配，则结果为 NULL。

select e.empno, e.ename, d.deptno from emp e left join dept d on e.deptno = d.deptno;

右外连接RIGHT JOIN

从右表（table2）返回所有的行，即使左表（table1）中没有匹配。如果左表中没有匹配，则结果为 NULL。

select e.empno, e.ename, d.deptno from emp e right join dept d on e.deptno = d.deptno;

满外连接 FULL JOIN

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

select e.empno, e.ename, d.deptno from emp e full join dept d on e.deptno = d.deptno;

排序

全局排序ORDER BY

对结果集按照一个列或者多个列进行排序。默认按照升序对记录进行排序。如果需要按照降序对记录进行排序，您可以使用 DESC 关键字。全局排序，只有一个 Reducer。这也就是说会有一个所有的数据都通过一个reduce进行处理的过程，对于大数据集，这个过程将消耗很大的时间来执行。

# 按照ID升序排序
select * from emp order by empno;
# 按照ID降序排序
select * from emp order by empno  desc;
# 多个列进行排序
select ename, deptno, sal from emp order by deptno, sal;

内部排序Sort By

会在每个reduce中对数据进行排序，也就是执行一个局部排序过程。这可以保证每个reduce的输出数据都是有序的(但并非全局有效)。

# 设置 reduce 个数
set mapreduce.job.reduces=3;
# 查看设置 reduce 个数
set mapreduce.job.reduces;
# 部门ID降序排
select * from emp sort by deptno desc;

Distribute By

distribute by 是控制map端在reduce上是如何区分的，distribute by会把相同的Key发到同一个reduce中。一般情况下可以结合sort by 使用，distribute by必须要写在sort by之前。先进行分组reduce，再进行排序（相当于mapreduce中的分区函数）。

Order by 能够预期产生完全排序的结果，但是它是通过只用一个reduce来做到这点的。所以对于大规模的数据集它的效率非常低。在很多情况下，并不需要全局排序，此时可以换成Hive的非标准扩展sort by。Sort by为每个reducer产生一个排序文件。在有些情况下，你需要控制某个特定行应该到哪个reducer，通常是为了进行后续的聚集操作。Hive的distribute by 子句可以做这件事。

# 设置 reduce 个数
set mapreduce.job.reduces=3;
# 查看设置 reduce 个数
set mapreduce.job.reduces;
# 先按照部门进行分区  再通过工资排序，
select deptno, ename, sal from emp distribute by deptno sort by deptno, sal ;

Cluster By

Cluster by 除了具有distribute by的功能外，还会对该字段进行排序。当distribute by和sort by 字段相同时，可以使用cluster by 代替。不能指定排序规则为 ASC 或者 DESC。

select * from emp cluster by deptno;