创库语法
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数据操作
数据导入 向表中装载数据(load)数据操作语言，在hive中主要包括数据导入与导出
语法
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)
查询语句中创建表并加载数据(as select)insert into select …from table,这种方式会用的比较多些
基于查询建表，根据查询结果创建表（查询的结果会添加到新创建的表中） 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';
数据导出 Insert 导出export和import主要用于两个Hadoop平台集群之间Hive表迁移。
注意一个现象，如果是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.txtExport导出到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语句
等值Join其实就两种：内连接，内连接的结果取交集
外连接：主表（驱动表）和从表（匹配表），外连接的结果集为主表的所有数据+从表中与主表匹配的数据
与mysql一样。
根据员工表和部门表中的部门编号相等，查询员工编号、员工名称和部门名称； 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
等等
排序
