Hsql语法
DDL数据定义
数据库的ddl
创库语法
create database [if not exists] database_name
[comment database_comment]
[location hdfs_path] -- 先不指定location
[with dbproperties (property_name=property_value,...)];
# 创建库mydb
create database if not exists mydb
comment "my first db"
with dbproperties("createtime"="2021-10-01");
# mydb1
create database if not exists mydb1
location "/mydb1.db"
查看数据库
show databases;
desc database mydb;
desc database extended mydb;
删除库
# 空表才能删除,危险操作。
drop database mydb1;
表的ddl
创建表语法
CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name -- external:外部表,默认内部表
[(col_name data_type [COMMENT col_comment], ...)] -- 指定列名,列类型[列描述信息...]
[COMMENT table_comment] -- 表描述信息
[PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)] -- 创建分区表,指定分区字段
[CLUSTERED BY (col_name, col_name, ...) -- 创建分桶表,指定分桶字段
[SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS] -- 指定分桶数
[ROW FORMAT delimited fields terminated by ...] -- 指定一条数据字段与字段的分隔符
[collection items terminated by ...] -- 指定集合元素与元素的分隔符
[map keys terminated by ...] -- 指定map的K,V分隔符
[STORED AS file_format] -- 指定文件存储格式,默认为textfile
[LOCATION hdfs_path] -- 指定表在hdfs中对应的路径
[TBLPROPERTIES (property_name=property_value, ...)] -- 指定表的属性
[AS select_statement] -- 基于某个查询建表
# 建表:use mydb;没有指定location的话,默认就在mydb库对应的目录的下面
# 1001,zhangssan
# 1002,lisi
# 1003,wangwu
create table if not exists test1(
id int comment "this is id",
name string comment "this is name"
)
comment "this is a table"
row format delimited fields terminated by ','
stored as textfile
TBLPROPERTIES("createtime"="2021-10-01");
# 创建表2,这个location很灵活的,无论是先有数据还是先有表,只有数据在对应的目录下,那么就能分析数据
create table if not exists test2(
id int ,
name string
)
row format delimited fields terminated by ','
location "/test2.table";
查看表
show tables;
desc tablename;
desc formattd tablename; # 可以看到表的存放位置MYSQL: select * from SDS;
内外部表转换
# 修改内部表student2为外部表(严格大写)
alter table student2 set tblproperties('EXTERNAL'='TRUE');
# 修改外部表student2为内部表
alter table student2 set tblproperties('EXTERNAL'='FALSE');
MANAGED_TABLE和EXTERNAL_TABLE有什么区别呢
当两者在删除的时候,内部表删除的时候会在在hdfs中对应的目录也给删除掉
而外部表在删除的时候会保留在hdfs中的目录
修改表
可以对表的名字,列等进行修改
# 创建数据文件 emptest.txt
1001 zhangsan 10000.1
1002 lisi 10000.2
1003 wangwu 10000.3
# 创建表
create table emp(
id int,
name string,
salary double
)
row format delimited fields terminated by '\t';
# 加载数据
load data local inpath '/opt/module/hive-3.1.2/datas/emptest.txt' into table emp;
# 查询数据
0: jdbc:hive2://hadoop102:10000> select * from emp;
+---------+-----------+-------------+
| emp.id | emp.name | emp.salary |
+---------+-----------+-------------+
| 1001 | zhangsan | 10000.1 |
| 1002 | lisi | 10000.2 |
| 1003 | wangwu | 10000.3 |
+---------+-----------+-------------+
3 rows selected (7.477 seconds)
0: jdbc:hive2://hadoop102:10000>
# 修改表
alter table rmp rename to emptest;
# 更新列(要注意列的类型,大于等于原来的类型)
alter table emptest change column salary sal double;
# 增加列
alter table emptest add columns (addr string,deptno int);
注意:假设数据一开始就是有3列,但是呢,我们创建表的时候只写了三列,这样也是可以的,因为是从左往右匹配的;
# 替换列(也是可以的,按顺序匹配从左往右)
alter table emptest replace columns (empid int,empname string);
删除表
drop table emptest;
DML数据操作
数据操作语言,在hive中主要包括数据导入与导出
数据导入
向表中装载数据(load)
语法
hive> load data [local] inpath '数据的path' [overwrite] into table student [partition (partcol1=val1,…)];
(1)load data:表示加载数据
(2)local:表示从本地加载数据到hive表;否则从HDFS加载数据到hive表
(3)inpath:表示加载数据的路径
(4)overwrite:表示覆盖表中已有数据,否则表示追加
(5)into table:表示加载到哪张表
(6)student:表示具体的表
(7)partition:表示上传到指定分区
重复装载(没有overwrite)会追加,overwrite是全覆盖
# 从本地装载数据到hive表中
load data local inpath '/opt/module/hive-3.1.2/datas/student.txt' into table student;
# 从hdfs中装载数据到hive表中(hdfs中数据会删除,相当于剪切到了hive表中)
load data inpath '/hivedatas/student.txt' into table student;
通过查询语句向表中插入数据(insert)
insert into select …from table,这种方式会用的比较多些
查询语句中创建表并加载数据(as select)
基于查询建表,根据查询结果创建表(查询的结果会添加到新创建的表中)
create table if not exists emptest1
as select empid, empname from emptest;
创建表时通过Location指定加载数据路径
这种方式用的最多
1)上传数据到hdfs上
hive (default)> dfs -mkdir /student;
hive (default)> dfs -put /opt/module/datas/student.txt /student;
2)创建表,并指定在hdfs上的位置
hive (default)> create external table if not exists student5(
id int, name string
)
row format delimited fields terminated by '\t'
location '/student;
3)查询数据
hive (default)> select * from student5;
import数据到指定Hive表中
注意:先用export导出后,再将数据导入
import table emptest2 from '/emptest';
export和import主要用于两个Hadoop平台集群之间Hive表迁移。
数据导出
Insert 导出
注意一个现象,如果是null的数据,导出来就是 \N
# 导出到本地,没有格式
insert overwrite local directory '/opt/module/hive-3.1.2/datas/insert-result' select empid,empname from emptest;
# 导出到本地,指定格式
insert overwrite local directory '/opt/module/hive-3.1.2/datas/insert-result'
row format delimited fields terminated by '\t' select empid,empname from emptest;
# 将查询结果导出到HDFS(没有Local)
insert overwrite directory '/insert-result'
row format delimited fields terminated by '\t' select empid,empname from emptest;
Hadoop命令导出到本地
hive (default)> dfs -get /user/hive/warehouse/student/student.txt
/opt/module/datas/export/student3.txt;
Hive Shell命令导出
基本语法:(hive -f/-e 执行语句或者脚本 > file)
[atguigu@hadoop102 insert-result]$ hive -e "select * from mydb.emptest" > /opt/module/hive-3.1.2/datas/hive-result/0.txt
Export导出到HDFS上
不仅会导出数据,还会把元数据信息也导出,注意:只能导出到HDFS,不能导出到本地
export和import主要用于两个Hadoop平台集群之间Hive表迁移。
export table emptest to '/emptest';
Sqoop导出
后面专门会讲
清除表中数据(Truncate)
注意:Truncate只能删除管理表(内部表),不能删除外部表中数据
truncate table emptest2;
DQL数据查询
基本语法
SELECT [ALL | DISTINCT] select_expr, select_expr, ...
FROM table_reference
[WHERE where_condition]
[GROUP BY col_list]
[HAVING having_condition]
[ORDER BY col_list] -- 全局排序
[CLUSTER BY col_list -- 分区排序(对应MR中的分区排序)
|
[DISTRIBUTE BY col_list] -- 分区
[SORT BY col_list] -- 区内排序
]
[LIMIT number]
基本查询
数据准备
dept.txt:
10 ACCOUNTING 1700
20 RESEARCH 1800
30 SALES 1900
40 OPERATIONS 1700
emp.txt:
7369 SMITH CLERK 7902 1980-12-17 800.00 20
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
(1)创建部门表
create table if not exists dept(
deptno int,
dname string,
loc int
)
row format delimited fields terminated by '\t';
(2)创建员工表
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';
(3)导入数据
load data local inpath '/opt/module/hive-3.1.2/datas/dept.txt' into table dept;
load data local inpath '/opt/module/hive-3.1.2/datas/emp.txt' into table emp;
全表和特定列查询
select * from emp;
select empno, ename from emp;
注意:
(1)SQL 语言大小写不敏感。
(2)SQL 可以写在一行或者多行
(3)关键字不能被缩写也不能分行
(4)各子句一般要分行写。
(5)使用缩进提高语句的可读性。
列别名
select ename AS name, deptno dn from emp;
select sal+1 as salary from emp;
算术运算符
运算符 | 描述 |
---|---|
A+B | A和B 相加 |
A-B | A减去B |
A*B | A和B 相乘 |
A/B | A除以B |
A%B | A对B取余 |
A&B | A和B按位取与 |
A|B | A和B按位取或 |
A^B | A和B按位取异或 |
~A | A按位取反 |
常用函数
count,max,min,sum,avg
1)求总行数(count)
hive (default)> select count(*) cnt from emp;
2)求工资的最大值(max)
hive (default)> select max(sal) max_sal from emp;
3)求工资的最小值(min)
hive (default)> select min(sal) min_sal from emp;
4)求工资的总和(sum)
hive (default)> select sum(sal) sum_sal from emp;
5)求工资的平均值(avg)
hive (default)> select avg(sal) avg_sal from emp;
limit语句
hive (default)> select * from emp limit 5;
hive (default)> select * from emp limit 2,3;
where语句
(1)查询出薪水大于1000的所有员工
hive (default)> select * from emp where sal >1000;
(2)查询出薪水等于5000的所有员工
hive (default)> select * from emp where sal =5000;
(3)查询工资在500到1000的员工信息
hive (default)> select * from emp where sal between 500 and 1000;
(4)查询comm为空的所有员工信息
hive (default)> select * from emp where comm is null;
(5)查询工资是1500或5000的员工信息
hive (default)> select * from emp where sal IN (1500, 5000);
Like和RLike
-- 查找名字中带有A的员工信息
hive (default)> select * from emp where ename RLIKE '[A]';
逻辑运算符
AND ,OR ,NOT
分组
Group By语句
GROUP BY语句通常会和聚合函数一起使用,按照一个或者多个列队结果进行分组,然后对每个组执行聚合操作。在集群上每次运行的时候都很慢,可以开启本地模式 set hive.exec.mode.local.auto=true;发现启动报错,原因是内存溢出,需要修改conf下面的hive-env.sh.template文件: export HADOOP_HEAPSIZE=1024
(1)计算emp表每个部门的平均工资
select t.deptno, avg(t.sal) avg_sal from emp t group by t.deptno;
(2)计算emp每个部门中每个岗位的最高薪水
select t.deptno, t.job, max(t.sal) max_sal from emp t group by
t.deptno, t.job;
Having语句
求每个部门的平均薪水大于2000的部门
hive (default)> select deptno, avg(sal) avg_sal from emp group by deptno having
avg_sal > 2000;
Join语句
其实就两种:内连接,内连接的结果取交集
外连接:主表(驱动表)和从表(匹配表),外连接的结果集为主表的所有数据+从表中与主表匹配的数据
与mysql一样。
等值Join
根据员工表和部门表中的部门编号相等,查询员工编号、员工名称和部门名称;
select e.empno, e.ename, d.deptno, d.dname from emp e join dept d on e.deptno = d.deptno;
left outer join
right outer join
等等
排序
全局排序(Order By)
order by:全局排序,只有一个Reducer
使用 ORDER BY 子句排序
ASC(ascend): 升序(默认)
DESC(descend): 降序
(1)查询员工信息按工资升序排列
select * from emp order by sal;
(2)查询员工信息按工资降序排列
select * from emp order by sal desc;
(3) 按照别名排序,按照员工薪水的2倍排序
select ename, sal*2 twosal from emp order by twosal;
(4) 多个列排序,按照部门和工资升序排序
select ename, deptno, sal from emp order by deptno, sal ;
每个Reducer内部排序(Sort By)
Sort By:对于大规模的数据集order by的效率非常低。在很多情况下,并不需要全局排序,此时可以使用sort by。
Sort by为每个reducer产生一个排序文件。每个Reducer内部进行排序,对全局结果集来说不是排序。
注意:Sort By最好配置Distribute By使用,一定是先分区后排序。
分区(Distribute By)
Distribute By: 在有些情况下,我们需要控制某个特定行应该到哪个reducer,通常是为了进行后续的聚集操作。distribute by 子句可以做这件事。distribute by类似MR中partition(自定义分区),进行分区,结合sort by使用。
对于distribute by进行测试,一定要分配多reduce进行处理,否则无法看到distribute by的效果。
案例实操
(1)先按照部门编号分区,再按照员工编号降序排序。
hive (default)> set mapreduce.job.reduces=3;
hive (default)> insert overwrite local directory '/opt/module/hive/datas/distribute-result' select * from emp distribute by deptno sort by empno desc;
注意:
- distribute by的分区规则是根据分区字段的hash码与reduce的个数进行模除后,余数相同的分到一个区
- Hive要求DISTRIBUTE BY语句要写在SORT BY语句之前。
Cluster By
当distribute by和sort by字段相同时,可以使用cluster by方式。
cluster by除了具有distribute by的功能外还兼具sort by的功能。但是排序只能是升序排序,不能指定排序规则为ASC或者DESC。
以下两种写法等价
# 注意:按照部门编号分区,不一定就是固定死的数值,可以是20号和30号部门分到一个分区里面去。
select * from emp cluster by deptno;
select * from emp distribute by deptno sort by deptno;