10.Hive DDL数据定义

Leefs 2021-12-04 PM 1646℃ 0条

[TOC]

### 前言

本篇讲述Hive语句中对数据库和数据表的操作。

### 一、数据库操作语句

#### 1.1 创建数据库

**语法结构：**

```sql
CREATE (DATABASE|SCHEMA) [IF NOT EXISTS] database_name
 	[COMMENT database_comment]　　　　　　//关于数据块的描述
	[LOCATION hdfs_path]　　　　　　　　　　//指定数据库在HDFS上的存储位置
	[WITH DBPROPERTIES (property_name=property_value, ...)];　　　//指定数据块属性
```

说明：`[]`括号中的内容代表可选项，如果不填会有默认值。

**（1）创建一个数据库名称为`db_hive`**

```sql
hive> create database db_hive;
```

+ 数据库在HDFS上的默认存储路径是`/user/hive/warehouse/*.db`

**（2）避免要创建的数据库已经存在错误，增加if not exists判断。（标准写法）**

```sql
hive> create database if not exists db_hive;
```

**（3）指定数据库在HDFS上存放的位置**

```sql
hive> create database db_hive location '/db_hive.db';
```

#### 1.2 查询数据库

##### 1、查询数据库

**（1）显示所有数据库名称**

```sql
hive> show databases;
```

**（2）过滤显示查询的数据库名称**

```sql
hive> show databases like 'db_hive*';
```

说明：表示查询出以`db_hive`开头的所有数据库

##### 2、查看数据库详情

**语法**

> desc database [extended] dbname;

**示例：查询数据库db_hive的详细信息**

```sql
hive> desc database extended db_hive;

db_hive		hdfs://hadoop001:9000/user/hive/warehouse/db_hive.db	hadoop	USER
```

**3、查看正在使用哪个库**

```sql
hive> select current_database();
```

**4、查看创建库的详细语句**

```sql
hive> show create database db_hive;

CREATE DATABASE `db_hive`
LOCATION
  'hdfs://hadoop001:9000/user/hive/warehouse/db_hive.db'
```

**5、切换当前数据库**

**语法**

> use dbname;

**示例：将数据库当前位置切换到db_hive**

```hive
hive> use db_hive;
```

#### 1.3 修改数据库

用户可以使用alter database 命令为某个数据库的 `dbproperties` 设置键-值对属性值，来描述这个数据库的属性信息。

**语法**

> alter database dbname dpproperties (createtime=datetime);

数据库的其他元数据信息都是不可更改的，包括数据库名和数据库所在的目录位置。

**示例：将db_hive数据库创建时间修改为2021-12-04**

```sql
alter database db_hive dpproperties (createtime=20211204);
```

#### 1.4 删除数据库

**1、删除空数据库**

```sql
hive> drop database db_hive;
```

**2、如果删除的数据库不存在，最好采用 if exists判断数据库是否存在**

```sql
hive> drop database db_hive;
Error: Error while compiling statement: FAILED: SemanticException [Error 10072]: Database does not exist: db_hive (state=42000,code=10072)
hive> drop database if exists db_hive;
```

**3、如果数据库不为空，可以采用cascade命令，强制删除**

```sql
hive> drop database db_hive cascade;
```

### 二、表操作

**Hive创建表结构有两种方式：**

+ **内部表，也叫管理表：**当删除表时元数据信息和HDFS中存储的数据都会删除
+ **外部表：**当删除表时只删除元数据信息，不会删除HDFS中存储的数据

**管理表和外部表的使用场景：**

每天将收集到的网站日志定期流入HDFS文本文件。在外部表（原始日志表）的基础上做大量的统计分析，用到的中间表、结果表使用内部表存储，数据通过SELECT+INSERT进入内部表。

#### 2.1 创建表

**语法结构**

```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]
```

**字段说明**

+ **CREATE TABLE ：**创建一个指定名字的表。如果相同名字的表已经存在，则抛出异常；用户可以用 IF NOT EXIST 选项来忽略这个异常

+ **EXTERNAL ：**关键字可以让用户创建一个外部表，在建表的同时指定一个指向实际数据的路径（LOCATION）

+ **COMMENT：**可以为表与字段增加描述

+ **PARTITIONED BY ：**指定分区

+ **ROW FORMAT：**

```sql
  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 确定表的具体的列的数据。

+ **STORED AS：**指定存储文件类型

```sql
  SEQUENCEFILE //序列化文件
  　　| TEXTFILE //普通的文本文件格式
  　　| RCFILE　　//行列存储相结合的文件
  　　| INPUTFORMAT input_format_classname OUTPUTFORMAT output_format_classname //自定义文件格式
  ```

如果文件数据是纯文本，可以使用 `STORED AS TEXTFILE`。如果数据需要压缩，使用 `STORED AS SEQUENCE`。

+ **LOCATION：**指定表在HDFS的存储路径

+ **LIKE：**允许用户复制现有的表结构，但是不复制数据

#### 2.2 管理表(MANAGED_TABLE)

**1、简介**

默认创建的表都是所谓的管理表，有时也被称为内部表。

因为这种表，Hive会（或多或少地）控制着数据的生命周期。

Hive默认情况下会将这些表的数据存储在由配置项`hive.metastore.warehouse.dir`(例如，/user/hive/warehouse)所定义的目录的子目录下。

**当我们删除一个管理表时，Hive也会删除这个表中数据。**管理表不适合和其他工具共享数据。

**2、案例实操**

+ **数据准备**

```basic
1001	zhanghua	12
1002	wangfang	13
1003	huixiaolin	12
1004	huojiaoyu	11
1005	huixiaofeng	12
```

**（1）普通创建表**

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

**将数据导入到创建好的表中**

```sql
hive> load data local inpath '/home/datas/student.txt' into table student;
```

**（2）根据查询结果创建表（查询的结果会添加到新创建的表中）**

```sql
create table if not exists student2 as select id,name,age from student;
```

当执行这个命令时我也不知道发生了啥,Hive报如下错误（如果能够正常执行的请忽略）：

```
Execution Error, return code 2 from org.apache.hadoop.hive.ql.exec.mr.MapRedTask
```

很显然，MR执行出现了问题，加下来通过`http://192.168.61.129:8088/`地址在Yarn中查看任务信息。

信息详情中报如下错误：

```basic
Last 4096 bytes of stderr : /bin/bash: /bin/java: 没有那个文件或目录
```

![10.Hvie DDL数据定义_01.jpg](https://lilinchao.com/usr/uploads/2021/12/1276824674.jpg)

这个错误原因很显然，Java需要创建软链接

**执行如下命令创建软链接：**

```shell
ln -s $JAVA_HOME/bin/java /usr/bin/java 
```

接下来执行过程中又报如下错误：

```basic
错误: 找不到或无法加载主类 org.apache.hadoop.mapreduce.v2.app.MRAppMaster
```

![10.Hvie DDL数据定义_02.jpg](https://lilinchao.com/usr/uploads/2021/12/3436932982.jpg)

**解决方法**

在命令行输入：hadoop classpath

```shell
[hadoop@hadoop001 root]$ hadoop classpath
/opt/software/hadoop-3.2.1/etc/hadoop:/opt/software/hadoop-3.2.1/share/hadoop/common/lib/*:/opt/software/hadoop-3.2.1/share/hadoop/common/*:/opt/software/hadoop-3.2.1/share/hadoop/hdfs:/opt/software/hadoop-3.2.1/share/hadoop/hdfs/lib/*:/opt/software/hadoop-3.2.1/share/hadoop/hdfs/*:/opt/software/hadoop-3.2.1/share/hadoop/mapreduce/lib/*:/opt/software/hadoop-3.2.1/share/hadoop/mapreduce/*:/opt/software/hadoop-3.2.1/share/hadoop/yarn:/opt/software/hadoop-3.2.1/share/hadoop/yarn/lib/*:/opt/software/hadoop-3.2.1/share/hadoop/yarn/*
```

把上述输出的值添加到`yarn-site.xml`文件对应的属性`yarn.application.classpath`中，如果没有直接增加：

```xml
<property>
	<name>yarn.application.classpath</name>
  	<value>/opt/software/hadoop-3.2.1/etc/hadoop:/opt/software/hadoop-3.2.1/share/hadoop/common/lib/*:/opt/software/hadoop-3.2.1/share/hadoop/common/*:/opt/software/hadoop-3.2.1/share/hadoop/hdfs:/opt/software/hadoop-3.2.1/share/hadoop/hdfs/lib/*:/opt/software/hadoop-3.2.1/share/hadoop/hdfs/*:/opt/software/hadoop-3.2.1/share/hadoop/mapreduce/lib/*:/opt/software/hadoop-3.2.1/share/hadoop/mapreduce/*:/opt/software/hadoop-3.2.1/share/hadoop/yarn:/opt/software/hadoop-3.2.1/share/hadoop/yarn/lib/*:/opt/software/hadoop-3.2.1/share/hadoop/yarn/*</value>
</property>
```

**（3）根据已经存在的表结构创建表**

```sql
hive> create table if not exists student3 like student;
```

*注意：该创建语句不包含数据*

**（4）查询表的类型**

```sql
 hive> desc formatted student;
```

#### 2.3 外部表(external  table)

**1、简介**

因为表是外部表，所以Hive并非认为其完全拥有这份数据。**删除该表并不会删除掉这份数据，不过描述表的元数据信息会被删除掉。**

**2、案例实操**

**需求**

> 分别创建部门和员工外部表，并向表中导入数据。

**数据准备**

+ dept

```basic
10	ACCOUNTING	1700
20	RESEARCH	1800
30	SALES	1900
40	OPERATIONS	1700
```

+ emp

```basic
7499	ALLEN	SALESMAN	7698	1981-2-20	1600.00	300.00	30
7521	WARD	SALESMAN	7698	1981-2-22	1250.00	500.00	30
7654	MARTIN	SALESMAN	7698	1981-9-28	1250.00	1400.00	30
7698	BLAKE	MANAGER	7839	1981-5-1	2850.00	300.00	30
7782	CLARK	MANAGER	7839	1981-6-9	2450.00	300.00	10
7788	SCOTT	ANALYST	7566	1987-4-19	3000.00	300.00	20
7844	TURNER	SALESMAN	7698	1981-9-8	1500.00	0.00	30
7876	ADAMS	CLERK	7788	1987-5-23	1100.00	1100.00	20
7900	JAMES	CLERK	7698	1981-12-3	950.00	1100.00	30
7902	FORD	ANALYST	7566	1981-12-3	3000.00	1100.00	20
7934	MILLER	CLERK	7782	1982-1-23	1300.00	1100.00	10
```

**（1）建表语句，创建外部表**

+ 创建部门表

```sql
create external table if not exists dept(
  deptno int,
  dname string,
  loc int
)
row format delimited fields terminated by '\t';
```

+ 创建员工表

```sql
create external 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';
```

**（2）将数据导入到创建好的表中**

```sql
# 导入dept表
load data local inpath '/home/datas/dept.txt' into table dept;
# 导入emp表
load data local inpath '/home/datas/emp.txt' into table emp;
```

**（3）查看创建的表**

```sql
hive> show tables;
```

**（4）查看表格式化数据**

```sql
hive> desc formatted dept;
```

**（5）删除外部表**

```sql
hive> drop table dept;
```

外部表删除后，hdfs中的数据还在，但是`metadata`中`dept`的元数据已被删除。

#### 2.4 管理表与外部表的互相转换

**（1）查询表的类型**

```sql
hive> desc formatted student;

Table Type: MANAGED_TABLE
```

**（2）修改内部表 student为外部表**

```sql
hive> alter table student set tblproperties('EXTERNAL'='TRUE');
```

**（3）修改外部表 student 为内部表**

```sql
hive> alter table student2 set tblproperties('EXTERNAL'='FALSE');
```

**注意：`('EXTERNAL'='TRUE')`和`('EXTERNAL'='FALSE')`为固定写法，区分大小写！**

#### 2.5 修改表

**1、修改表名**

**（1）将表名`student`修改为`new_student`**

```sql
hive> alter table student rename to new_student;
```

**2、修改字段定义**

**（1）增加一个字段,在new_student中增加一个int类型的score字段**

```sql
hive> alter table new_student add columns (score int);
```

ADD 是代表新增一字段，字段位置在所有列后面(partition 列前)

**（2）修改一个字段的定义**

```sql
hive> alter table new_student change name new_name string;
```

可以修改字段的名称和类型

**（3）替换所有字段**

```sql
hive> alter table new_student replace columns (id int, name string, address string);
```

REPLACE 表示替换表中所有字段

#### 2.6 删除表

```sql
hive (default)> drop table dept;
```

标签: Hadoop, Hive

非特殊说明，本博所有文章均为博主原创。

如若转载，请注明出处：https://lilinchao.com/archives/1711.html

上一篇 09.Hive类型转换

下一篇 11.Hive DML数据操作

10.Hive DDL数据定义

评论已关闭

栏目分类

标签云

友情链接申请

10.Hive DDL数据定义

评论已关闭

 栏目分类

标签云

友情链接申请

栏目分类

标签云

友情链接申请