Hive SQL 数据定义语言（DDL）

最新推荐文章于 2024-08-11 10:56:17 发布

@听风吟

最新推荐文章于 2024-08-11 10:56:17 发布

阅读量1.6k

点赞数 64

分类专栏： Hive 文章标签： hive sql hadoop

本文链接：https://blog.csdn.net/m0_70882914/article/details/140755017

版权

Hive 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

1. Hive SQL 数据定义语言（DDL）概述

1.1 DDL语法的作用

数据定义语言 (Data Definition Language, DDL)，是 SQL 语言集中对数据库内部的对象结构进行创建，删除，修改等的操作语言，这些数据库对象包括database（ schema）、 table、 view、 index 等。核心语法由 CREATE、 ALTER 与 DROP三个所组成。 DDL 并不涉及表内部数据的操作。
在某些上下文中，该术语也称为数据描述语言，因为它描述了数据库表中的字段和记录。
在这里插入图片描述

1.2 Hive 中DDL的使用

Hive SQL（HQL）与SQL的语法大同小异，基本上是相通的。只不过在学习HQL语法的时候，特别要注意Hive自己特有的语法知识点，比如partition相关的DDL操作。
基于Hive的设计、使用特点，HQL中create语法（尤其create table）将是学习掌握DDL语法的重中之重。可以说建表是否成功直接影响数据文件是否映射成功，从而影响后续是否可以基于SQL分析数据。

2. Hive DDL建表基础

2.1 完整建表语法树

在这里插入图片描述
蓝色字体是建表语法的关键字，用于指定某些功能。
[]中括号的语法表示可选。
|表示使用的时候，左右语法二选一。
建表语句中的语法顺序要和上述语法规则保持一致。

2.2 Hive数据类型详解

2.2.1 整体概述

Hive中的数据类型指的是Hive表中的列字段类型。Hive数据类型整体分为两个类别：原生数据类型（primitive data type）和复杂数据类型（complex data type）。
原生数据类型包括：数值类型、时间类型、字符串类型、杂项数据类型；
复杂数据类型包括：array数组、map映射、struct结构、union联合体。
在这里插入图片描述
关于Hive的数据类型，需要注意：
英文字母大小写不敏感；
除SQL数据类型外，还支持Java数据类型，比如：string；
int和string是使用最多的，大多数函数都支持；
复杂数据类型的使用通常需要和分隔符指定语法配合使用。
如果定义的数据类型和文件不一致，hive会尝试隐式转换，但是不保证成功。

2.2.2 原生数据类型

Hive支持的原生数据类型如下图所示：
在这里插入图片描述

2.2.3 复杂数据类型

在这里插入图片描述

2.2.4 数据类型隐式、显式转换

与SQL类似，HQL支持隐式和显式类型转换。
原生类型从窄类型到宽类型的转换称为隐式转换，反之，则不允许。
下表描述了类型之间允许的隐式转换：在这里插入图片描述
显式类型转换使用CAST函数。
cast (数据字段 as 新类型)
例如，CAST（'100’as INT）会将100字符串转换为100整数值。如果强制转换失败，例如CAST（'INT’as INT），该函数返回NULL。

3. Hive DDL其他语法

3.1 Database|schema（数据库） DDL操作

3.1.1 Create database

Hive中DATABASE的概念和RDBMS中类似，我们称之为数据库。在Hive中， DATABASE和SCHEMA是可互换的，使用DATABASE或SCHEMA都可以。

CREATE (DATABASE|SCHEMA) [IF NOT EXISTS] database_name
[COMMENT database_comment]
[LOCATION hdfs_path]
[WITH DBPROPERTIES (property_name=property_value, ...)];

COMMENT：数据库的注释说明语句。
LOCATION：指定数据库在HDFS存储位置，默认/user/hive/warehouse 。
WITH DBPROPERTIES：用于指定一些数据库的属性配置。
下面创建一个数据库tb_1:

create database if not exists tb_1
       comment "这是我的第一个库"
       with dbproperties('createdBy'='刘向阳');

3.1.2 Describe database

Hive中的DESCRIBE DATABASE语句用于显示Hive中数据库的名称，其注释（如果已设置）及其在文件系统上的位置等信息。
语法：DESCRIBE DATABASE/SCHEMA [EXTENDED] db_name;
EXTENDED：用于显示更多信息。

describe database tb_1;
describe database extended tb_1;

在这里插入图片描述

3.1.3 Use database

Hive中的USE DATABASE语句用于选择特定的数据库,切换当前会话使用哪一个数据库进行操作。
语法：USE database_name;

use tb_1;

3.1.4 Drop database

Hive中的DROP DATABASE语句用于删除（删除）数据库。
默认行为是RESTRICT，这意味着仅在数据库为空时才删除它。要删除带有表的数据库，我们可以使用。CASCADE。
语法：DROP (DATABASE|SCHEMA) [IF EXISTS] database_name [RESTRICT|CASCADE];
注意：当数据库中存在表时，默认是无法删除的，需要cascade强制删除。

3.1.5 Alter database

Hive中的ALTER DATABASE语句用于更改与Hive中的数据库关联的元数据。
语法：
更改数据库属性
ALTER (DATABASE|SCHEMA) database_name SET DBPROPERTIES (property_name=property_value, ...);

--更改数据库属性
-- ALTER (DATABASE|SCHEMA) database_name SET DBPROPERTIES (property_name=property_value, ...);
alter database tb_1 set dbproperties ('createdBy'='听风吟');
desc database extended tb_1;

在这里插入图片描述

更改数据库所有者
ALTER (DATABASE|SCHEMA) database_name SET OWNER [USER|ROLE] user_or_role;

更改数据库位置
ALTER (DATABASE|SCHEMA) database_name SET LOCATION hdfs_path;

在这里插入图片描述

--更改数据库位置
-- ALTER (DATABASE|SCHEMA) database_name SET LOCATION hdfs_path;
desc database extended tb_1;
alter database tb_1 set location 'hdfs://node1:8020/data/tb_1';
desc database extended tb_1;

3.2 Table（表）DDL操作

3.2.1 Create table

建表

create table if not exists db_1.employee (
    id int,
    name string,
    department string,
    salary float
)
row format delimited fields terminated by ',';

在linux上编辑：vim employee_data.csv

1,张三,工程部,75000
2,李四,市场部,80000
3,王五,财务部,85000
4,赵六,工程部,95000
5,孙七,人事部,60000

在这里插入图片描述
加载数据：

load data local inpath'/root/hivedata/employee_data.csv' into table db_1.employee;

3.2.2 Describe table

Hive中的DESCRIBE table语句用于显示Hive中表的元数据信息。
语法：

describe formatted [db_name.]table_name;
describe extended [db_name.]table_name;

如果指定了EXTENDED关键字，则它将以Thrift序列化形式显示表的所有元数据。

describe extended db_1.employee;

在这里插入图片描述

如果指定了FORMATTED关键字，则它将以表格格式显示元数据。

describe formatted db_1.employee;

在这里插入图片描述

3.2.3 Drop table

DROP TABLE删除该表的元数据和数据。如果已配置垃圾桶（且未指定PURGE），则该表对应的数据实际上将移动到.Trash/Current目录，而元数据完全丢失。删除EXTERNAL表时，该表中的数据不会从文件系统中删除，只删除元数据。
如果指定了PURGE，则表数据不会进入.Trash/Current目录，跳过垃圾桶直接被删除。因此如果DROP失败，则无法挽回该表数据。
语法：

DROP TABLE [IF EXISTS] table_name [PURGE];    -- (Note: PURGE available in Hive 0.14.0 and later)

3.2.4 Truncate table

从表中删除所有行。可以简单理解为清空表的所有数据但是保留表的元数据结构。如果HDFS启用了垃圾桶，数据将被丢进垃圾桶，否则将被删除。
语法：

TRUNCATE [TABLE] table_name;

3.2.5Alter table

--1、更改表名
ALTER TABLE table_name RENAME TO new_table_name;
--2、更改表属性
ALTER TABLE table_name SET TBLPROPERTIES (property_name = property_value, ... );
--更改表注释
ALTER TABLE student SET TBLPROPERTIES ('comment' = "new comment for student table");
--3、更改SerDe属性
ALTER TABLE table_name SET SERDE serde_class_name [WITH SERDEPROPERTIES (property_name = property_value, ... )];
ALTER TABLE table_name [PARTITION partition_spec] SET SERDEPROPERTIES serde_properties;
ALTER TABLE table_name SET SERDEPROPERTIES ('field.delim' = ',');
--移除SerDe属性
ALTER TABLE table_name [PARTITION partition_spec] UNSET SERDEPROPERTIES (property_name, ... );

--4、更改表的文件存储格式 该操作仅更改表元数据。现有数据的任何转换都必须在Hive之外进行。
ALTER TABLE table_name  SET FILEFORMAT file_format;
--5、更改表的存储位置路径
ALTER TABLE table_name SET LOCATION "new location";

--6、更改列名称/类型/位置/注释
CREATE TABLE test_change (a int, b int, c int);
// First change column a's name to a1.
ALTER TABLE test_change CHANGE a a1 INT;
// Next change column a1's name to a2, its data type to string, and put it after column b.
ALTER TABLE test_change CHANGE a1 a2 STRING AFTER b;
// The new table's structure is:  b int, a2 string, c int.
// Then change column c's name to c1, and put it as the first column.
ALTER TABLE test_change CHANGE c c1 INT FIRST;
// The new table's structure is:  c1 int, b int, a2 string.
// Add a comment to column a1
ALTER TABLE test_change CHANGE a1 a1 INT COMMENT 'this is column a1';

--7、添加/替换列
--使用ADD COLUMNS，您可以将新列添加到现有列的末尾但在分区列之前。
--REPLACE COLUMNS 将删除所有现有列，并添加新的列集。
ALTER TABLE table_name ADD|REPLACE COLUMNS (col_name data_type,...);