## 创建数据库
##### 语法
```sql
CREATE DATABASE [IF NOT EXISTS] database_name [COMMENT database_comment]
[LOCATION hdfs_path]
[WITH DBPROPERTIES (property_name=property_value, ...)];
```
**创建一个数据库,指定数据库在** **HDFS** **上存放的位置**
```sql
hive (default)> create database db_hive2 location '/db_hive2.db';
```
## 创建表table
**建表语法**
```sql
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 DLIMITED FIELDS TERMINATED BY ','
```mysql
create table test(
name string,
children map<string, int>,
address struct<street:string, city:string>)
row format delimited fields terminated by ',' collection items terminated by '_'
map keys terminated by ':' lines terminated by '\n';
```
字段解释:
| row format delimited fields terminated by ',' | -- 列分隔符 |
| :-------------------------------------------: | :----------------------------------------------------------- |
| collection items terminated by '_' | --MAP STRUCT 和 ARRAY 的分隔符(数据分割符号) map keys terminated by ':' |
| map keys terminated by ':' | -- MAP 中的 key 与 value 的分隔符 |
| lines terminated by '\n'; | 行分隔符 |
## 管理表与外部表的互相转换
(1)查询表的类型
```mysql
hive (default)> desc formatted student2;
Table Type: MANAGED_TABLE
```
**(2)修改内部表 student2 为外部表**
```mysql
alter table student2 set tblproperties('EXTERNAL'='TRUE');
```
##### (3)查询表的类型
```mysql
hive (default)> desc formatted student2;
Table Type: EXTERNAL_TABLE
```
**(4)修改外部表 student2 为内部表**
```mysql
alter table student2 set tblproperties('EXTERNAL'='FALSE');
```
##### (5)查询表的类型
```mysql
hive (default)> desc formatted student2;
Table Type: MANAGED_TABLE
```
### **注意:**
#### **('EXTERNAL'='TRUE')和('EXTERNAL'='FALSE')为固定写法,区分大小写!**
## 修改表
**4.6.1** **重命名表**
#### 1)语法
```mysql
ALTER TABLE table_name RENAME TO new_table_name
```
#### 2)实操案例
```mysql
hive (default)> alter table dept_partition2 rename to dept_partition3;
```
## 增加/修改/替换列信息
#### 1)语法
(1)更新列
```mysql
ALTER TABLE table_name CHANGE [COLUMN] col_old_name col_new_name column_type [COMMENT col_comment][FIRST|AFTER column_name]
```
(2)增加和替换列
```mysql
ALTER TABLE table_name ADD|REPLACE COLUMNS(col_name data_type [COMMENT col_comment],...)
```
**注:ADD 是代表新增一字段,字段位置在所有列后面(partition 列前),**
**REPLACE 则是表示替换表中所有字段**。
#### 实操案例
(1)查询表结构
```mysql
hive> desc dept;
```
(2)添加列
```mysql
hive (default)> alter table dept add columns(deptdesc string);
```
(3)查询表结构
```mysql
hive> desc dept;
```
(4)更新列
```mysql
hive (default)> alter table dept change columns deptdesc desc string;
```
(5)查询表结构
```mysql
hive> desc dept;
```
(6)替换列
```mysql
hive(default) > alter tabledept replace columns(deptnostring, dname string, loc string);
```
(7)查询表结构
```mysql
hive> desc dept;
```
```sql
ALTER TABLE test_table CHANGE col1 col2 STRING AFTER col3;
```
上面的语句将列名col1修改为col2,数据类型为STRING并添加注释,最后将这一列放在col3后面。
## 数据导入
#### 向表中装载数据(Load)
#### 1)语法
```mysql
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:表示具体的表
## 通过查询语句向表中插入数据(Insert)
#### 基本插入数据
```mysql
hive (default)> insert into table student_par values(1,'wangwu'),(2,'zhaoliu');
```
#### 基本模式插入(根据单张表查询结果)
```mysql
hive (default)> insert overwrite table student_par
select id, name from student where month='201709';
```
insert into:以追加数据的方式插入到表或分区,原有数据不会删除
insert overwrite:会覆盖表中已存在的数据
## 数据导出
**Insert 导出**
##### 将查询的结果导出到本地
```mysql
hive (default)> insert overwrite local directory '/opt/module/hive/data/export/student'
select * from student;
```
**将查询的结果格式化导出到本地**
```mysql
hive(default)>insert overwrite local directory
'/opt/module/hive/data/export/student1'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
select * from student;
```
**将查询的结果导出到 HDFS 上(没有 local)**
```mysql
hive (default)> insert overwrite directory '/user/atguigu/student2'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
select * from student;
```
**Hadoop 命令导出到本地**
```mysql
hive (default)> dfs -get /user/hive/warehouse/student/student.txt /opt/module/data/export/student3.txt;
```
## 清除表中数据(Truncate)
实质: 删除hdfs数据
注意:Truncate 只能删除管理表,不能删除外部表中数据
```mysql
hive (default)> truncate table student;
```
## 函数
> CONCAT(string A/col, string B/col…):返回输入字符串连接后的结果,支持任意个输入字 符串;
>
> CONCAT_WS(separator, str1, str2,...):它是一个特殊形式的 CONCAT()。第一个参数剩余参 数间的分隔符。分隔符可以是与剩余参数一样的字符串。如果分隔符是 NULL,返回值也将 为 NULL。这个函数会跳过分隔符参数后的任何 NULL 和空字符串。分隔符将被加到被连接 的字符串之间;
EXPLODE(col):将 hive 一列中复杂的 Array 或者 Map 结构拆分成多行。
LATERAL VIEW
用法:LATERAL VIEW udtf(expression) tableAlias AS columnAlias
> 解释:用于和 split, explode 等 UDTF 一起使用,它能够将**一列数据拆成多行数据**,在此 基础上可以对拆分后的数据进行聚合。
## 窗口函数(开窗函数)
相关函数说明**
OVER():指定分析函数工作的数据窗口大小,这个数据窗口大小可能会随着行的变而变化。
CURRENT ROW:当前行
n PRECEDING:往前 n 行数据 n FOLLOWING:往后 n 行数据 UNBOUNDED:起点,
UNBOUNDED PRECEDING 表示从前面的起点,
UNBOUNDED FOLLOWING 表示到后面的终点
LAG(col,n,default_val):往前第 n 行数据
LEAD(col,n, default_val):往后第 n 行数据
NTILE(n):把有序窗口的行分发到指定数据的组中,各个组有编号,编号从 1 开始,对 于每一行,NTILE 返回此行所属的组的编号。注意:n 必须为 int 类型。
## Rank
#### 1)函数说明
RANK() 排序相同时会重复,总数不会变
DENSE_RANK() 排序相同时会重复,总数会减少
ROW_NUMBER() 会根据顺序计算
## 分区表和分桶表
#### 分区表
分区表实际上就是对应一个 HDFS 文件系统上的独立的文件夹,该文件夹下是该分区所 有的数据文件。Hive 中的分区就是分目录,把一个大的数据集根据业务需要分割成小的数据 集。在查询时通过 WHERE 子句中的表达式选择查询所需要的指定的分区,这样的查询效率 会提高很多。
#### 创建分区表
```mysql
create table
dept_partition
( deptno int,
dname string,
loc string )
partitioned by (day string)
row format delimited fields terminated by '\t';
```
**1.数据准备**
##### 加载数据到分区表中
###### dept_20200401.log
| 10 | ACCOUNTING | 1700 |
| ---- | ---------- | ---- |
| 20 | RESEARCH | 1800 |
###### dept_20200402.log
| 30 | SALES | 1900 |
| ---- | ---------- | ---- |
| 40 | OPERATIONS | 1700 |
###### dept_20200403.log
| 50 | TEST | 2000 |
| ---- | ---- | ---- |
| 60 | DEV | 1900 |
**2.加载数据**
```mysql
load data local inpath '/opt/module/hive/datas/dept_20200403.log'
into table dept_partition partition(day='20200403');
```
## 增加分区
##### 创建单个分区
```mysql
hive (default)> alter table dept_partition add partition(day='20200404');
```
##### 同时创建多个分区
```mysql
hive (default)> alter table dept_partition add partition(day='20200405') partition(day='20200406');
```
## 删除分区
删除单个分区
```mysql
hive (default)> alter table dept_partition drop partition (day='20200406');
```
同时删除多个分区
```mysql
hive (default)> alter table dept_partition drop partition (day='20200404'), partition(day='20200405');
```
查看分区表有多少分区
```mysql
hive> show partitions dept_partition;
```
查看分区表结构
```mysql
hive> desc formatted dept_partition;
```
## 二级分区
#### 1)创建二级分区表
```mysql
hive (default)> create table dept_partition2( deptno int, dname string, loc string)
partitioned by (day string, hour string)
```
**正常的加载数据**
```mysql
hive (default)> alter table dept_partition2 add partition(day='201709',hour='14');
```
## 动态分区
(1)开启动态分区功能(默认 true,开启)
```mysql
hive.exec.dynamic.partition=true
```
(2)设置为非严格模式(动态分区的模式,默认 strict,表示必须指定至少一个分区为 静态分区,nonstrict 模式表示允许所有的分区字段都可以使用动态分区。)
```mysql
hive.exec.dynamic.partition.mode=nonstrict
```
#### 2)案例实操
需求:将 dept 表中的数据按照地区(loc 字段),插入到目标表 dept_partition 的相应 分区中。
(1)创建目标分区表
```mysql
create table dept_no_par3(dname string,loc string)
partitioned by (deptno int)
row format delimited fields terminated by '\t';
```
设置动态分区
```mysql
set hive.exec.dynamic.partition.mode = nonstrict;
```
动态分区:
```mysql
hive (default)> insert into table dept_partition_dy partition(loc) select deptno, dname, loc from dept;
```
hive3版本动态分区 新增特性
```mysql
insert into table dept_no_par3
select dname,loc,deptno from dept_partition;
```
## 分桶表
分区提供一个隔离数据和优化查询的便利方式。不过,并非所有的数据集都可形成合理 的分区。对于一张表或者分区,Hive 可以进一步组织成桶,也就是更为细粒度的数据范围 划分。
分桶是将数据集分解成更容易管理的若干部分的另一个技术。
**分区针对的是数据的存储路径;分桶针对的是数据文件。**
### 创建分桶表
```mysql
create table stu_buck(id int, name string) clustered by(id)
into 4 buckets
row format delimited fields terminated by '\t';
```
查看表结构
```mysql
hive (default)> desc formatted stu_buck;
Num Buckets: 4
```
导入数据到分桶表中,load的方式
```mysql
hive (default)> load data inpath '/student.txt' into table stu_buck;
```
查看创建的分桶表中是否分成 4 个桶
分桶规则:
根据结果可知:Hive 的分桶采用对分桶字段的值进行哈希,
然后除以桶的个数求余的方 式决定该条记录存放在哪个桶当中
#### 分桶表操作需要注意的事项:
(1)reduce 的个数设置为-1,让 Job 自行决定需要用多少个 reduce 或者将 reduce 的个 数设置为大于等于分桶表的桶数
(2)从 hdfs 中 load 数据到分桶表中,避免本地文件找不到问题
(3)不要使用本地模式
insert 方式将数据导入分桶表
```mysql
hive(default)>insert into table
stu_buck select * from
student_insert;
```
## 自定义 UDF 函数
#### 需求:
自定义一个 UDF 实现计算给定字符串的长度,例如:
```mysql
hive(default)>select my_len("abcd");
4
```
1)创建一个 Maven 工程 Hive 2)导入依赖
```xml
<dependencies>
<dependency>
<groupId>org.apache.hive</groupId>
<artifactId>hive-exec</artifactId>
<version>3.1.2</version>
</dependency>
</dependencies>
```
**3**)创建一个类
```java
import org.apache.hadoop.hive.ql.exec.UDFArgumentException;
import org.apache.hadoop.hive.ql.metadata.HiveException;
import org.apache.hadoop.hive.ql.udf.generic.GenericUDF;
import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspector;
import org.apache.hadoop.hive.serde2.objectinspector.primitive.PrimitiveObjectInspectorFactory;
public class MyUDF extends GenericUDF {
// 校验数据参数个数
public ObjectInspector initialize(ObjectInspector[] objectInspectors) throws UDFArgumentException {
if (objectInspectors.length !=1){
throw new UDFArgumentException("参数个数不为1");
}
return PrimitiveObjectInspectorFactory.javaIntObjectInspector;
}
// 处理数据
public Object evaluate(DeferredObject[] deferredObjects) throws HiveException {
//1.取出输入数据
String input = deferredObjects[0].get().toString();
//2.判断输入数据是否为null
if (input == null){
return 0;
}
//3.返回数据的输入长度
return input.length();
}
public String getDisplayString(String[] strings) {
return "";
}
}
```
**4**)打成 **jar** **包上传到服务器**/opt/module/data/myudf.jar
**5**)将 **jar** **包添加到** **hive** **的** **classpath**
```mysql
hive (default)> add jar /opt/module/data/myudf.jar;
```
#### 6)创建临时函数与开发好的 java class 关联
```mysql
hive (default)> create temporary function my_len as "com.atguigu.hive. MyStringLength";
```
**7)**即可在 **hql** **中使用自定义的函数**
```mysql
hive (default)> select ename,my_len(ename) ename_len from emp
```
### 自定义 UDTF 函数
案例
```java
import org.apache.hadoop.hive.ql.exec.UDFArgumentException;
import org.apache.hadoop.hive.ql.metadata.HiveException;
import org.apache.hadoop.hive.ql.udf.generic.GenericUDTF;
import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspector;
import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspectorFactory;
import org.apache.hadoop.hive.serde2.objectinspector.StructObjectInspector;
import org.apache.hadoop.hive.serde2.objectinspector.primitive.PrimitiveObjectInspectorFactory;
import java.util.ArrayList;
import java.util.List;
/**
* 输入数据:hello,hadoop,hive
* 输出数据:
* hello
* hadoop
* hive
*/
public class MyUDTF extends GenericUDTF {
//输出数据的集合
private final ArrayList<String> outPutList = new ArrayList<String>();
@Override
public StructObjectInspector initialize(StructObjectInspector argOIs) throws UDFArgumentException {
// 输出数据的默认列名,可以别别名覆盖
List<String> fieldNames = new ArrayList<String>();
fieldNames.add("word");
// 输出数据的类型
List<ObjectInspector> fieldOIs = new ArrayList<ObjectInspector>();
fieldOIs.add(PrimitiveObjectInspectorFactory.javaStringObjectInspector);
return ObjectInspectorFactory.getStandardStructObjectInspector(fieldNames, fieldOIs);
}
// 处理数输入数据 hello,hadoop,hive
public void process(Object[] objects) throws HiveException {
//1.取出输入数据
String input = objects[0].toString();
//2.按照“,”分割字符串
String[] words = input.split(",");
for (String word : words) {
//清空集合
outPutList.clear();
//将数据放入集合
outPutList.add(word);
//输出数据
forward(outPutList);
}
}
//收尾方法
public void close() throws HiveException {
}
}
```