hive语法

**

一、建表语法

**

CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name
[(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 row_format]
[STORED AS file_format]
[LOCATION hdfs_path]
[TBLPROPERTIES (property_name=property_value, ...)]
[AS select_statement]

【字段解释说明】
（1）CREATE TABLE 创建一个指定名字的表。如果相同名字的表已经存在，则抛出异常；用户可以用 IF NOT EXISTS 选项来忽略这个异常。

（2）EXTERNAL 关键字可以让用户创建一个外部表，在建表的同时可以指定一个指向实
际数据的路径（LOCATION），在删除表的时候，内部表的元数据和数据会被一起删除，而外
部表只删除元数据，不删除数据。

（3）COMMENT：为表和列添加注释。

（4）PARTITIONED BY 创建分区表

（5）CLUSTERED BY 创建分桶表

（6）SORTED BY 不常用，对桶中的一个或多个列另外排序

（7）ROW FORMAT
DELIMITED [FIELDS TERMINATED BY char] [COLLECTION ITEMS TERMINATED BY char]
[MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char]
| SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value,
property_name=property_value, …)]
用户在建表的时候可以自定义 SerDe 或者使用自带的 SerDe。如果没有指定 ROW
FORMAT 或者 ROW FORMAT DELIMITED，将会使用自带的 SerDe。在建表的时候，用户还需
要为表指定列，用户在指定表的列的同时也会指定自定义的 SerDe，Hive 通过 SerDe 确定表
的具体的列的数据。
SerDe 是 Serialize/Deserilize 的简称， hive 使用 Serde 进行行对象的序列与反序列化。

（8）STORED AS 指定存储文件类型
常用的存储文件类型：SEQUENCEFILE（二进制序列文件）、TEXTFILE（文本）、RCFILE（列
式存储格式文件）
如果文件数据是纯文本，可以使用STORED AS TEXTFILE。如果数据需要压缩，使用 STORED
AS SEQUENCEFILE。

（9）LOCATION ：指定表在 HDFS 上的存储位置。

（10）AS：后跟查询语句，根据查询结果创建表。 （11）LIKE 允许用户复制现有的表结构，但是不复制数据

【案例】
(1)创建表

create table if not exists student(
id int, 
name string
)
row format delimited fields terminated by '\t'
stored as textfile
location '/user/hive/warehouse/student';

默认位置在hadoop的HDFS系统的/user/hive/warehouse
(2)用hadoop命令上传表内容

 dfs -put /opt/module/datas/student.txt /student;

/opt/module/datas/student.txt --表示文件在linux系统上的路径
/student 表示在hdfs上已经创建好的表信息
查看表的具体信息，包含在HDFS上的位置，字段信息，创建时间，拥有者等

 desc formatted student;

**

二、向表中装载信息

**
向表中装载数据（Load）
语法

load data [local] inpath '数据的 path' [overwrite] into table 
student [partition (partcol1=val1,…)];

load data local inpath
    '/opt/hivedata/student.txt' into table xiong.student5;

将centos的路径/opt/hivedata下的student.txt文件上传到数据库xiong的表student5中
创建的库名xiong在HDFS中显示为xiong.db在填写路径过程中不需要带上.db
（1）load data:表示加载数据
（2）local:表示从本地加载数据到 hive 表；否则从 HDFS 加载数据到 hive 表 （3）inpath:表示加载数据的路径
（4）overwrite:表示覆盖表中已有数据，否则表示追加
（5）**into table:**表示加载到哪张表
（6）student:表示具体的表
（7）partition:表示上传到指定分区
向xiong库的表student5中插叙数值

insert into table xiong.student5
values(1,'wangwu'),(2,'zhaoliu');

insert into：以追加数据的方式插入到表或分区，原有数据不会删除
insert overwrite：会覆盖表中已存在的数据

三、hive函数

GROUP BY 语句通常会和聚合函数一起使用，按照一个或者多个列队结果进行分组，然后对每个组执行聚合操作。
计算 emp 每个部门中每个岗位的最高薪水
select t.deptno, t.job, max(t.sal) max_sal from emp t group by t.deptno, t.job;

【1、hive语句的书写顺序】

(1)select
(2)from
(3)join on
(4) where
(5)group by
(6)having
(7)distribute by/cluster by
(8) sort by
(9) order by
(10) limit
(11) union(去重不排序)/union all（不去重不排序）

【2、hive语句的执行顺序】

(1)from
(2)on
(3)join
(4)where
(5)group by
(6)having
(7)select
(8)distinct
(9)distribute by /cluster by
(10)sort by
(11) order by
(12) limit
(13) union /union all

【3Having 语句】

1）having 与 where 不同点
（1）where 后面不能写分组函数，而 having 后面可以使用分组函数。
（2）having 只用于 group by 分组统计语句。

2）案例实操

（1）求每个部门的平均薪水大于 2000 的部门
求每个部门的平均工资

hive (default)> select deptno, avg(sal) from emp group by deptno;

求每个部门的平均薪水大于 2000 的部门

hive (default)> select deptno, avg(sal) avg_sal from emp group by deptno having avg_sal > 2000;

**

【4Join 语句】

**
1 内连接
内连接：只有进行连接的两个表中都存在与连接条件相匹配的数据才会被保留下来。

hive (default)> select e.empno, e.ename, d.deptno from emp e join dept d on e.deptno = d.deptno;

2 左外连接
左外连接：JOIN 操作符左边表中符合 WHERE 子句的所有记录将会被返回。

hive (default)> select e.empno, e.ename, d.deptno from emp e left join dept d on e.deptno = d.deptno;

3右外连接
右外连接：JOIN 操作符右边表中符合 WHERE 子句的所有记录将会被返回。

hive (default)> select e.empno, e.ename, d.deptno from emp e right join dept d on e.deptno = d.deptno;

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

hive (default)> select e.empno, e.ename, d.deptno from emp e full join dept d on e.deptno = d.deptno;

**

【5创建分区表】

(1)创建分区表
**

create table dept_partition(
        deptno int,
        dname string,
        loc string
) partitioned by (day string)
row format delimited fields terminated by '\t';

创建dept_partition表，根据day来分区，通过\t，table空格键来分割一行字段。
(2)新增表分区

 alter table dept_partition add partition(day='20220604');

对dept_partition表增加分区，分区字段为day
(3)删除分区，分区日期为20220604

alter table dept_partition drop partition (day='20220604');

**

【6动态分区】

**
关系型数据库中，对分区表 Insert 数据时候，数据库自动会根据分区字段的值，将数据
插入到相应的分区中，Hive 中也提供了类似的机制，即动态分区(Dynamic Partition)，只不过，
使用 Hive 的动态分区，需要进行相应的配置。
（1）开启动态分区功能（默认 true，开启）,设置为非严格模式（动态分区的模)

set hive.exec.dynamic.partition.mode;
set hive.exec.dynamic.partition.mode=nonstrict;

分区字段和表中字段不一致

create table dept_partition_dy(id int, name string)  partitioned by (loc int) row format delimited fields terminated by '\t';

向分建的分区表中插入查询的字段

 insert into table dept_partition_dy partition(loc) select  deptno, dname, loc from dept;

查询的字段为deptno，dname,ioc其中deptno和dname是分区表中的字段，ioc是根据分区的信息，也就是分区表根据ioc来分区