【关于 SQL ，你知道的有多少？】-CSDN博客

SQL

结构化查询语言（Structured Query Language），简称SQL，是一种专门用来与关系型数据库管理系统（RDBMS）进行交互的编程语言。

结构化：
SQL操作的数据是以表格形式组织的，每个表由行（记录）和列（字段）组成。这种结构化的数据模型使得数据的存储和检索更加有序和高效。

查询语言：
SQL主要用于查询、更新、管理和修改数据库中存储的数据。它是声明式语言，意味着用户只需指定要查询什么（What），而不需要指定如何做（How）。

Hive

Hive 是基于 Hadoop 的数据仓库工具。

Hive 提供了类似于关系型数据库SQL语言的 HiveQL 工具，常用于对存储在Hadoop集群中的 HDFS 文件数据集进行数据整理、特殊查询和分析处理。

Hive的本质就是将 HiveQL 语句转换为 MapReduce 任务后运行，让我们在处理对数据处理时只需要编写 SQL 实现逻辑，而不需要编写大量复杂的 MapReduce 逻辑，大大降低了学习成本和维护成本，更关注于如何组织数据实现目标，提高工作效率等。

SQL 分类

DDL（Data Definition Language）：数据定义语言，如CREATE、ALTER 和D ROP，用来定义数据库对象：库、表、视图、列和其他数据库对象的结构等；

DML（Data Manipulation Language）：数据操作语言，用来定义数据库记录（数据），如SELECT、INSERT、UPDATE和DELETE，用于检索、插入、更新和删除数据库中的数据；

DCL（Data Control Language）：数据控制语言，如GRANT和REVOKE，用来定义访问权限和安全级别；

DQL（Data Query Language）：数据查询语言，用来查询记录（数据）。

DDL（Data Definition Language）：数据定义语言

这里基于基于 Hive 3.1.3 版本介绍其中的 DDL-数据定义语言。

数据库操作（Database）

Hive 中数据库的概念本质上仅仅是表的一个目录或者命名空间。如果用户没有显示指定具体数据库，则会使用默认的数据库 default，可以通过 use database_name 或在查询表时使用 database.table_name指定查询某个数据库的某张表。假设 database_name 为 test，可以用如下命令创建一个 test 的数据库：

-- 方式一：
CREATE database IF NOT EXISTS test;

-- 方式二：
CREATE database IF NOT EXISTS test
COMMENT '存储所有测试的表';

Hive 会为每个数据库创建一个目录。在某个数据库下创建的表将会在该数据库目录下以子目录的形式存储。（default 数据库的表除外，因为这个数据库本身没有自己的目录）。

假设用户使用的默认数据库所在目录为 hive.metastore.warehouse.dir，则在创建数据库 test 时Hive 会对应创建一个目录：

/user/hive/warehouse/test.db(注意数据库的文件目录名以 .db 结尾)

我们可以通过下面的语句来修改默认的位置，自定义一个数据库存储目录：

CREATE database IF NOT EXISTS test
LOCATION '自定义数据库目录';

创建好数据库后，我们可以使用以下命令对数据库操作：

USE test; -- 使用 test 数据库，可以操作该数据库下存储的表
DESCRIBE DATABASE test; -- 列举数据库信息，如描述信息，所在文件目录位置路径。
DESCRIBE DATABASE EXTENDED test; -- 查看更多数据库信息，如 user等
SHOW DATABASES; -- 列举所有数据库清单

Hive 支持正则表达式匹配筛选数据库，下面语句实现列举出所有以字母 c开头，以其他字符结尾（即.*部分含义）的数据库名：

SHOW DATABASES LIKE 'c.*'; -- 支持正则表达式匹配筛选数据库

删除数据库：

DROP DATABASE IF EXISTS test;

注意事项：
默认情况下，Hive 是不允许用户删除一个包含有表的数据库的。
如果想要删除包含表的数据库，可以采取如下操作：
1.先删除数据库中的表，再删除数据库；
2.在删除命令的最后面加上关键字 cascade ，可以实现 Hive 自行先删除数据库中的表；

DROP DATABASE IF EXISTS test CASCADE;

表操作（Table）

基本概念：

Hive 数据库类似传统数据库，也是有数据库与表的概念。Hive表分内部表与外部表，其区别主要体现在存储上。Hive 在创建表功能中有一定的扩展，比如可以定义表的数据文件存储位置、使用什么样的存储格式、是否压缩等等，提供了更广泛的灵活性。

内部表&外部表区别：

内部表(Managed table): 未被 External 修饰的表，也叫管理表。

外部表(External table): 被 External 修饰的表。

数据管理方式：

数据(data)都存储在 HDFS 上，内部表由Hive自身管理，外部表数据由HDFS管理；

元数据(metadata)存储在关系型数据库上，一般由 MySQL 管理，默认情况下，Hive 元数据保存在内嵌的 Derby 数据库中，但是 Derby 数据库只能允许一个会话连接，如果开启了Hive服务，其他客户端将无法连接到元数据库，因为Derby不与其他客户端共享数据，只适合简单的测试，相比之下，MySQL支持多个会话连接，更适合多用户环境和生产环境的需求，Hive 内部对 MySQL 提供了很好的支持。

创建表

内部表（Managed Table）

CREATE TABLE IF NOT EXISTS 库名.表名 (
字段英文名 字段数据类型 COMMENT '字段中文名'
,字段英文名 字段数据类型 COMMENT '字段中文名'
,字段英文名 字段数据类型 COMMENT '字段中文名'
...
) COMMENT '表中文名'
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
STORED AS TEXTFILE
;

外部表（External Table）

CREATE EXTERNAL TABLE IF NOT EXISTS app.cust_info (
cust_id          STRING COMMENT '客户编号'
,cust_name       STRING COMMENT '客户名称'
,cust_typ_id     STRING COMMENT '客户类型代码'
,cust_state_id   STRING COMMENT '客户状态代码'
) COMMENT '客户信息表'
STORED AS PARQUET
LOCATION 'data/2024-12-20'
;

关键字 EXTERNAL 告诉Hive这个表是外部的，LOCATION 用于告诉 Hive 数据位于哪个路径下。

分区表

管理表和外部表都支持对数据进行分区，建立分区表是管理大型生产数据最常见的方式，可以大大优化查询性能。可以按日期、地区等进行分区。

CREATE [EXTERNAL] TABLE IF NOT EXISTS app.cust_info (
cust_id          STRING COMMENT '客户编号'
,cust_name       STRING COMMENT '客户名称'
,cust_typ_id     STRING COMMENT '客户类型代码'
,cust_state_id   STRING COMMENT '客户状态代码'
) COMMENT '客户信息表'
PARTITION BY ( data_dt DATE  COMMENT '数据批量日期(YYYY-MM-DD)' )
STORED AS PARQUET
LOCATION 'data/2024-12-20/'
;

小提示：

Impala 建表语句中 COMMENT 语句要放在 PARTITION BY 语句后面，顺序与Hive 相反。

复制表结构[不会复制数据]：按 tabA的表结构创建tabB

CREATE TABLE IF NOT EXISTS tabB LIKE tabA
;

复制表结构及数据：按 tabA的表结构创建tabB，并将 tabA 的数据复制到 tabB中

CREATE TABLE IF NOT EXISTS tabB 
AS
SELECT * FROM tabA 
;

列举表清单

USE database_name;
SHOW TABLES;
SHOW TABLES IN database_name;
SHOW TABLES 'cust.*'

查看表结构及详细信息

DESCRIBE database_name.table_name;

DESCRIBE EXTENDED database_name.table_name;

修改表

常用操作：

-- 增加列
ALTER TABLE dbname.table_name ADD COLUMN IF NOT EXISTS column_name STRING COMMENT '字段中文名'
;
-- 删除列
ALTER TABLE dbname.table_name DROP column_name
;
-- 修改字段中文名及字段英文名
ALTER TABLE dbname.table_name CHANGE old_column_name new_column_name STRING COMMONT '修改后的字段中文名'
;
-- 仅修改数据类型 STRING -> VARCHAR(200)
ALTER TABLE dbname.table_name CHANGE column_name column_name VARCHAR(200) COMMONT '字段中文名'
;
-- 分区
-- 新增分区
ALTER TABLE dbname.table_name ADD  IF NOT EXISTS PARTITION BY ( data_dt = '2024-12-31')
;
-- 删除分区
ALTER TABLE dbname.table_name DROP IF     EXISTS PARTITION BY ( data_dt = '2024-12-31')
;