Hive SQL系统化学习

Apache Hive是一个开源的数据仓库框架，用于查询和分析大数据集存储在Hadoop文件系统中。

Hive 提供了一种类 SQL 的查询语言，叫做 HiveQL，它使得熟悉 SQL 的用户可以在 Hive 上查询、汇总和分析数据。同时，还支持用户将自定义的 map/reduce 程序插入语句中。

Hive 文件格式

Hive 支持多种文件格式，主要包括以下几种：

• 文本文件（TextFile）：默认的文件格式，每行一条记录。
• SequenceFile：Hadoop 的二进制文件格式，支持分割和压缩。
• Avro：一种 JSON 格式的文件，支持 schema 定义，数据和 schema 都以 JSON 格式存储。
• ORC (Optimized Row Columnar)：Hive 的专用文件格式，它是一种列式存储的文件格式，设计目标是为了提升 Hive 在读、写和处理数据时的性能。
• Parquet：一种列式存储格式，用于 Hadoop 生态系统，特别适合于嵌套数据结构。
• RCFile (Record Columnar File)：Hive 的一种列式存储格式，适合于数据仓库的应用。

在 Hive 中，压缩是一种常用的优化手段，它可以减少存储空间的使用和减少数据在网络中的传输时间。Hive 支持多种压缩格式，包括：

• gzip：在 Hive 中，gzip 提供了最高级别的压缩。然而，gzip 文件不能被分割，所以它不适合大规模的并行处理。
• bzip2：bzip2 提供了与 gzip 类似的压缩级别，但是 bzip2 文件可以被分割，所以它适合并行处理。
• snappy：snappy 提供了较低的压缩级别，但它的压缩和解压速度非常快。snappy 文件不能被分割。
• lz4：lz4 是一种很快的压缩算法，它提供了较低的压缩级别。lz4 文件不能被分割。
• LZO：LZO 是一种压缩率较低但速度非常快的压缩算法。LZO 文件可以被分割，但需要进行额外的索引步骤。

在选择压缩格式时，需要根据数据的特性和查询需求来进行权衡。例如，如果 CPU 资源充足，网络带宽紧张，那么可以选择压缩率高的格式，如 gzip 或 bzip2。反之，如果 CPU 资源紧张，网络带宽充足，那么可以选择压缩速度快的格式，如 snappy 或 LZO。

ORC (Optimized Row Columnar)详解

ORC (Optimized Row Columnar) 是 Hive 的一种列式存储格式。它是专门为了在 Hive 中提高性能而设计的。以下是一些 ORC 的主要特点：

• 列式存储：与行式存储方式相比，列式存储可以大大提高查询性能，因为它允许数据库只读取需要的数据列，而不是整个记录。这样可以显著减少磁盘 IO，提高查询速度。
• 压缩：ORC 文件支持不同的压缩技术，包括 ZLIB、SNAPPY、LZO 和 Zstandard 等。压缩可以显著减少存储空间的使用。
• 类型感知：ORC 是类型感知的，它知道列中的数据类型，这允许它使用更有效的压缩技术。
• 索引：ORC 文件包含了轻量级的索引数据，这些索引数据包括每个数据块的最小值、最大值等统计信息。这可以在查询时用来跳过不相关的数据块，提高查询性能。
• 向量化查询：ORC 支持向量化查询，这是一种将多行数据作为一个矢量进行处理的技术，可以大大提高查询的吞吐量。
• ACID 事务：在 Hive 0.14 版本以后，ORC 文件支持了 ACID 事务。这意味着你可以在 ORC 文件上进行更新、删除和合并操作。

总的来说，ORC 存储格式通过列式存储、压缩、索引和向量化查询等技术，可以在 Hive 中提供很高的查询性能和存储效率。

ORC的文件结构

ORC文件的结构是由一系列的条纹（stripes）组成的，每个条纹包含了一定数量的行数据。

• 条纹（Stripes）：ORC文件将数据划分为条纹，每个条纹包含一定数量的行。默认情况下，每个条纹包含250MB的数据。条纹的设置使得HDFS能够进行大规模、高效的数据读取。
• 文件尾部（Footer）：文件尾部包含了一系列的元数据，如条纹的列表、每个条纹中的行数、每列的数据类型，以及每列的一些统计信息（如计数、最小值、最大值、总和）。这些信息对快速读取和处理数据很有帮助。
• 后记（Postscript）：在文件的最后，有一个后记部分，包含了压缩参数以及压缩后的尾部大小。这些信息对解压缩文件和读取尾部数据很重要。

以上这种结构使ORC文件在读取、写入和处理数据时具有很高的效率和性能。

默认的条纹大小是 250 MB。大的条纹大小可以实现从 HDFS 的大规模、高效读取。此图示展示了 ORC 文件的结构：

如图所示，ORC文件中的每个条纹都包含Index Data，Row Data和Strip Footer。Row Data被用于表扫描。Index Data数据包含每列的最小值和最大值以及每列中的行位置。（也可能包含位域或布隆过滤器）。行索引条目提供了偏移量，使得在已解压的块中寻找到正确的压缩块和字节成为可能。注意，ORC索引仅用于选择条纹和行组，而不用于回答查询。有相对频繁的行索引条目可以使条纹内的行跳过，实现快速读取。默认情况下，每10000行可以跳过。通过基于过滤谓词跳过大量行的能力，你可以对表的次要键进行排序，以实现执行时间的大幅度减少。例如，如果主分区是交易日期，表可以根据州，邮政编码和姓氏进行排序。然后查找一个州的记录会跳过所有其他州的记录。

HiveQL语法

文件格式是在表（或分区）级别指定的。你可以使用如下的 HiveQL 语句指定 ORC 文件格式：

• CREATE TABLE … STORED AS ORC
• ALTER TABLE … [PARTITION partition_spec] SET FILEFORMAT ORC
• SET hive.default.fileformat=Orc

TBLPROPERTIES 是 HiveQL 中的一个命令，用于在创建表或修改表时存储表的元数据属性。这些属性可以包括一些关于表的附加信息，如表的创建时间、表的所有者、序列化和反序列化的类名等。你也可以使用它来设置一些特定的表属性，比如表的文件格式（例如 ORC）、压缩编码等。

一个基本的 TBLPROPERTIES 的使用示例如下：

CREATE TABLE my_table(id INT, name STRING)
COMMENT 'This is my table'
TBLPROPERTIES('orc.compress'='ZLIB');

与ORC相关的配置参数：

其中用的较多的是orc.compress，以下是可选的压缩格式：

• NONE： 不进行压缩。
• ZLIB： ZLIB 是一种带压缩的文件格式，压缩率较高但速度较慢。
• SNAPPY： SNAPPY 是一种带压缩的文件格式，速度较快但压缩率较低。
• LZO： LZO 是一种带压缩的文件格式，其压缩和解压缩的速度都很快，但压缩率相对较低。
• ZSTD： ZSTD (Zstandard) 是一种带压缩的文件格式，它是由 Facebook 开发的，提供了较高的数据压缩比，同时压缩和解压缩的速度也很快。

默认的压缩格式是 ZLIB。

Hive 数据类型

Hive 支持以下数据类型：

• 数值类型
  • TINYINT：1字节有符号整数，范围从-128到127。
  • SMALLINT：2字节有符号整数，范围从-32,768到32,767。
  • INT/INTEGER：4字节有符号整数，范围从-2,147,483,648到2,147,483,647。
  • BIGINT：8字节有符号整数，范围从-9,223,372,036,854,775,808到9,223,372,036,854,775,807。
  • FLOAT：4字节单精度浮点数。
  • DOUBLE：8字节双精度浮点数。
  • DOUBLE PRECISION：双精度的别名，从 Hive 2.2.0开始可用。
  • DECIMAL：在 Hive 0.11.0 中引入，精度为38位数字，Hive 0.13.0 引入了用户可定义的精度和刻度。
  • NUMERIC：与 DECIMAL 相同，从 Hive 3.0.0开始。
• 日期/时间类型
  • TIMESTAMP：表示时间戳，只从 Hive 0.8.0 版本开始可用。它可以存储日期和时间，精度为毫秒。
  • DATE：表示日期，只从 Hive 0.12.0 版本开始可用。它可以存储年、月和日信息。
  • INTERVAL：表示时间间隔，只从 Hive 1.2.0 版本开始可用。它用于表示两个时间点之间的时间间隔。
• 字符串类型
  • STRING：这种类型用于表示一串字符。在 Hive 中，字符串是由 Unicode 字符组成的，并且没有长度限制。
  • VARCHAR：这种类型的数据也是字符串，但是长度被限制。只从 Hive 0.12.0 版本开始可用。你需要在声明 VARCHAR 类型时指定长度，例如 VARCHAR(20) 就是一个长度不超过 20 个字符的字符串。
  • CHAR：这也是一种字符串数据类型，但与 VARCHAR 不同的是，CHAR 类型的字符串长度是固定的。只从 Hive 0.13.0 版本开始可用。你需要在声明 CHAR 类型时指定长度，例如 CHAR(10) 是一个长度为 10 的字符串。如果实际字符串长度不足，Hive 会在其后面用空格填充。
• 其他类型
  • BOOLEAN：这种类型用于表示逻辑值，只有两种可能的值：TRUE（真）或 FALSE（假）。
  • BINARY：这种类型用于存储二进制数据，只从 Hive 0.8.0 版本开始可用。
• 复杂数据类型
  • ARRAYS：数组类型，由同一类型的元素组成，例如 ARRAY<int> 表示一个整数数组。注意，从 Hive 0.14 开始，允许使用负值和非常量表达式。
  • MAPS：映射类型，包含键值对，键是原始类型，值可以是任何类型，例如 MAP<string, int> 表示一个键为字符串、值为整数的映射。同样，从 Hive 0.14 开始，允许使用负值和非常量表达式。
  • STRUCTS：结构体类型，可包含不同类型的字段，例如 STRUCT<name: string, age: int> 表示一个包含名称和年龄的结构体。每个字段都有一个名称和类型，还可以有一个可选的注释。
  • UNIONTYPE：联合类型，可以是多种类型中的任何一种，例如 UNIONTYPE<int, string> 可以是整数或字符串。注意，这种类型只从 Hive 0.7.0 版本开始可用。

允许的隐式转换：

HiveQL DDL语句

HiveQL DDL语句包含：

• CREATE DATABASE/SCHEMA, TABLE, VIEW, FUNCTION, INDEX
• DROP DATABASE/SCHEMA, TABLE, VIEW, INDEX
• TRUNCATE TABLE
• ALTER DATABASE/SCHEMA, TABLE, VIEW
• MSCK REPAIR TABLE (or ALTER TABLE RECOVER PARTITIONS)
• SHOW DATABASES/SCHEMAS, TABLES, TBLPROPERTIES, VIEWS, PARTITIONS, FUNCTIONS, INDEX[ES], COLUMNS, CREATE TABLE
• DESCRIBE DATABASE/SCHEMA, table_name, view_name, materialized_view_name

在 HiveQL 中，分区语句（PARTITION statements）通常是表语句（TABLE statements）的选项，除了 SHOW PARTITIONS 命令。

Hive SQL保留关键词

Create/Drop/Alter/Use Database

Create Database

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

这是 Hive 中用于创建数据库的命令。下面是对这个命令各部分的解释：

• CREATE [REMOTE] (DATABASE|SCHEMA)：这是创建数据库的基本命令。DATABASE 和 SCHEMA 在 Hive 中是同义词，你可以使用其中任何一个。REMOTE 是一个可选的关键字，用于创建远程数据库。
• [IF NOT EXISTS]：这是一个可选的语句，如果存在具有指定名称的数据库，则不会创建新的数据库。
• database_name：这是你想要创建的数据库的名称。
• [COMMENT database_comment]：这是一个可选的语句，可以为你的数据库添加注释。
• [LOCATION hdfs_path]：这是一个可选的语句，用于指定数据库在 HDFS（Hadoop Distributed File System）上的存储位置。如果不指定，Hive 会使用默认的位置。
• [MANAGEDLOCATION hdfs_path]：这是一个可选的语句，用于指定 Hive 管理表的存储位置。这个选项只在某些 Hive 版本中可用。
• [WITH DBPROPERTIES (property_name=property_value, …)]：这是一个可选的语句，用于设置和数据库相关联的属性。

这个命令的一个例子：

CREATE DATABASE IF NOT EXISTS my_database
COMMENT 'This is my database'
LOCATION '/user/hive/my_database'
WITH DBPROPERTIES ('creator'='me', 'date'='2022-01-01');

这将创建一个名为 my_database 的数据库，如果它还不存在的话。数据库有一个注释 ‘This is my database’，并存储在 HDFS 的 /user/hive/my_database 位置。该数据库有两个属性，’creator’ 属性的值为 ‘me’，’date’ 属性的值为 ‘2022-01-01’。

Drop Database

DROP (DATABASE|SCHEMA) [IF EXISTS] database_name [RESTRICT|CASCADE];

这是 Hive 中用于删除数据库的命令。下面是对这个命令各部分的解释：

• DROP (DATABASE|SCHEMA)：这是删除数据库的基本命令。DATABASE 和 SCHEMA 在 Hive 中是同义词，你可以使用其中任何一个。
• [IF EXISTS]：这是一个可选的语句，如果不存在具有指定名称的数据库，则不会引发错误。
• database_name：这是你想要删除的数据库的名称。
• [RESTRICT|CASCADE]：这是一个可选的语句，用于指定删除数据库时如何处理其中的表。RESTRICT 是默认行为，如果数据库中存在任何表，则不会删除数据库。CASCADE 将删除数据库和其中的所有表。

这个命令的一个例子：

DROP DATABASE IF EXISTS my_database CASCADE;

这将删除名为 my_database 的数据库，如果它存在的话。如果数据库中有任何表，也会一并删除。

Alter Database

ALTER (DATABASE|SCHEMA) database_name SET DBPROPERTIES (property_name=property_value, ...);   -- (Note: SCHEMA added in Hive 0.14.0)
ALTER (DATABASE|SCHEMA) database_name SET OWNER [USER|ROLE] user_or_role;   -- (Note: Hive 0.13.0 and later; SCHEMA added in Hive 0.14.0)
ALTER (DATABASE|SCHEMA) database_name SET LOCATION hdfs_path; -- (Note: Hive 2.2.1, 2.4.0 and later)
ALTER (DATABASE|SCHEMA) database_name SET MANAGEDLOCATION hdfs_path; -- (Note: Hive 4.0.0 and later)

Use Database

USE database_name;
USE DEFAULT;

Create/Drop/Truncate Table

Create Table

CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name    -- (Note: TEMPORARY available in Hive 0.14.0 and later)
  [(col_name data_type [column_constraint_specification] [COMMENT col_comment], ... [constraint_specification])]
  [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]
  [SKEWED BY (col_name, col_name, ...)                  -- (Note: Available in Hive 0.10.0 and later)]
     ON ((col_value, col_value, ...), (col_value, col_value, ...), ...)
     [STORED AS DIRECTORIES]
  [
   [ROW FORMAT row_format] 
   [STORED AS file_format]
     | STORED BY 'storage.handler.class.name' [WITH SERDEPROPERTIES (...)]  -- (Note: Available in Hive 0.6.0 and later)
  ]
  [LOCATION hdfs_path]
  [TBLPROPERTIES (property_name=property_value, ...)]   -- (Note: Available in Hive 0.6.0 and later)
  [AS select_statement];   -- (Note: Available in Hive 0.5.0 and later; not supported for external tables)

这是 Hive 中用于创建表的命令。下面是对这个命令各部分的解释：

• CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name：这是创建表的基本命令。TEMPORARY 是一个可选的关键字，用于创建临时表。EXTERNAL 是一个可选的关键字，用于创建外部表。IF NOT EXISTS 是一个可选的语句，如果存在具有指定名称的表，则不会创建新的表。是一个可选的语句，用于指定数据库的名称。
• [(col_name data_type [column_constraint_specification] [COMMENT col_comment], … [constraint_specification])]：这部分定义了表的列和数据类型。你可以为每一列添加一个可选的注释。
• [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]：这部分是一个可选的语句，用于定义表的分桶。
• [SKEWED BY (col_name, col_name, …) ON ((col_value, col_value, …), (col_value, col_value, …), …)]：这部分是一个可选的语句，用于定义表的数据偏斜。
• [STORED AS DIRECTORIES]：这是一个可选的语句，如果你的表是偏斜的，那么你可以选择将数据存储为目录。
• [ROW FORMAT row_format]：这是一个可选的语句，用于定义行的格式。
• [STORED AS file_format]：这是一个可选的语句，用于定义文件的格式。
• STORED BY ‘storage.handler.class.name’ [WITH SERDEPROPERTIES (…)]：这是一个可选的语句，用于定义表的存储处理程序。你可以添加一个可选的 SERDEPROPERTIES 语句来定义序列化/反序列化属性。
• [LOCATION hdfs_path]：这是一个可选的语句，用于定义表在 HDFS（Hadoop Distributed File System）上的位置。
• [TBLPROPERTIES (property_name=property_value, …)]：这是一个可选的语句，用于定义表的属性。
• [AS select_statement]：这是一个可选的语句，用于使用查询结果创建表。

ROW FORMAT

• ROW FORMAT 用于定义 Hive 表的行格式。有两种方式来定义行格式：
  • DELIMITED：这是最常见的方式，用于定义字段、集合项、地图键的分隔符等。以下是这个选项中各部分的解释：
  • FIELDS TERMINATED BY char：定义字段之间的分隔符。
  • ESCAPED BY char：定义用于转义特殊字符的转义符。
  • COLLECTION ITEMS TERMINATED BY char：定义集合项之间的分隔符。
  • MAP KEYS TERMINATED BY char：定义键值对之间的分隔符。
  • LINES TERMINATED BY char：定义行之间的分隔符。
  • NULL DEFINED AS char：定义用于表示 null 值的字符。
• SERDE：如果你的数据有自定义的格式，你可以使用 SERDE（序列化/反序列化）来解析。以下是这个选项中各部分的解释：
  • SERDE serde_name：定义用于处理数据的 SERDE 的名称。
  • WITH SERDEPROPERTIES (property_name=property_value, …)：定义 SERDE 的属性。

例如：

CREATE TABLE my_table (col1 string, col2 int)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
ESCAPED BY '\\'
LINES TERMINATED BY '\n'
NULL DEFINED AS 'null';

这将创建一个名为 my_table 的新表，字段之间的分隔符是逗号，转义符是反斜线，行之间的分隔符是换行符，null 值表示为 ‘null’。

column_constraint_specification

column_constraint_specification 是用来在 Hive 中为表的列定义约束的。

以下是这个选项中各部分的解释：

• PRIMARY KEY：这是一个用于标识表中每行唯一性的关键字。一个表只能有一个主键。
• UNIQUE：这是一个用于确保列中所有的值都是唯一的关键字。
• NOT NULL：这是一个用于确保列中没有空值的关键字。
• DEFAULT [default_value]：这是一个用于设置列的默认值的关键字。如果在插入数据时没有指定列的值，那么将使用默认值。
• CHECK [check_expression] ENABLE|DISABLE NOVALIDATE RELY/NORELY：这是一个用于对列中的值进行检查的关键字。你可以定义一个表达式，所有的值都必须满足这个表达式。ENABLE|DISABLE 控制是否启用这个约束，NOVALIDATE 表示不检查现有的数据是否满足这个约束，RELY/NORELY 控制是否信任这个约束。

请注意，Hive 是一个为大数据设计的框架，对于一些操作，它的行为可能与传统的关系数据库有所不同。例如，尽管 Hive 支持这些约束关键字，但它并不强制执行它们，这意味着你可以插入不满足约束的数据。Hive 中的约束主要用于优化查询，而不是保证数据质量。你应该在数据插入之前进行清洗，以确保它满足你的约束。

default_value

default_value 是用于设置 Hive 表的列的默认值的选项。

以下是这个选项中各部分的解释：

• LITERAL：这是一个常量值，可以是数字、字符串或布尔值。例如，你可以设置一个列的默认值为 1，’default’ 或 true。
• CURRENT_USER()：这是一个函数，返回执行当前查询的用户名。如果在插入数据时没有指定列的值，那么将使用当前用户名作为默认值。
• CURRENT_DATE()：这是一个函数，返回当前日期。如果在插入数据时没有指定列的值，那么将使用当前日期作为默认值。
• CURRENT_TIMESTAMP()：这是一个函数，返回当前时间戳。如果在插入数据时没有指定列的值，那么将使用当前时间戳作为默认值。
• NULL：这是一个关键字，表示空值。如果在插入数据时没有指定列的值，那么将使用 NULL 作为默认值。

例如，下面的命令创建了一个新表，其中 col1 的默认值为 1，col2 的默认值为当前用户，col3 的默认值为当前日期，col4 的默认值为当前时间戳，col5 的默认值为 NULL：

CREATE TABLE my_table (
  col1 INT DEFAULT 1,
  col2 STRING DEFAULT CURRENT_USER(),
  col3 DATE DEFAULT CURRENT_DATE(),
  col4 TIMESTAMP DEFAULT CURRENT_TIMESTAMP(),
  col5 STRING DEFAULT NULL
);

constraint_specification

constraint_specification 是用来在 Hive 中为整个表定义约束的。这些约束可以涉及到一个或多个列。

以下是这个选项中各部分的解释：

• PRIMARY KEY (col_name, …) DISABLE NOVALIDATE RELY/NORELY：这是用来定义主键的。主键是唯一标识表中每一行的一个或多个列。DISABLE NOVALIDATE RELY/NORELY 控制是否启用和验证这个约束。
• CONSTRAINT constraint_name FOREIGN KEY (col_name, …) REFERENCES table_name(col_name, …) DISABLE NOVALIDATE：这是用来定义外键的。外键是一个或多个列，它们的值必须匹配另一个表的主键。DISABLE NOVALIDATE 控制是否启用和验证这个约束。
• CONSTRAINT constraint_name UNIQUE (col_name, …) DISABLE NOVALIDATE RELY/NORELY：这是用来定义唯一约束的。唯一约束确保一个或多个列的值是唯一的。DISABLE NOVALIDATE RELY/NORELY 控制是否启用和验证这个约束。
• CONSTRAINT constraint_name CHECK [check_expression] ENABLE|DISABLE NOVALIDATE RELY/NORELY：这是用来定义检查约束的。检查约束是一个布尔表达式，所有的行都必须使这个表达式为真。ENABLE|DISABLE NOVALIDATE RELY/NORELY 控制是否启用和验证这个约束。

请注意，Hive 是一个为大数据设计的框架，对于一些操作，它的行为可能与传统的关系数据库有所不同。尽管 Hive 支持这些约束关键字，但它并不强制执行它们，这意味着你可以插入不满足约束的数据。Hive 中的约束主要用于优化查询，而不是保证数据质量。你应该在数据插入之前进行清洗，以确保它满足你的约束。

内部表与外部表

在 Hive 中，你可以创建两种类型的表：Managed Tables（也称为内部表）和 External Tables。

• Managed Tables（内部表）：当你创建一个 Managed Table 时，Hive 就会对表的生命周期进行完全的控制。这意味着，当你删除一个 Managed Table，Hive 不仅会删除表的元数据（也就是表的定义），还会删除表的数据。内部表通常在你完全控制数据，且当表删除时也希望删除数据的情况下使用。
• External Tables：当你创建一个 External Table 时，Hive 只会管理表的元数据，而不会管理表的数据。这意味着，当你删除一个 External Table，Hive 只会删除表的元数据，而不会删除表的数据。外部表通常在以下情况下使用：数据是在 Hive 之外管理的；数据需要被 Hive 以及其他工具共享；当表被删除时，你不希望删除数据。

创建 Managed Table 的命令：

CREATE TABLE managed_table (col1 int, col2 string);

创建 External Table 的命令：

CREATE EXTERNAL TABLE external_table (col1 int, col2 string)
LOCATION '/path/to/data';

请注意，对于 External Table，你需要指定数据的位置（LOCATION ‘/path/to/data’）。这是因为 Hive 不管理 External Table 的数据，所以需要知道数据在哪里。另一方面，对于 Managed Table，Hive 会自动在默认的位置管理数据，所以你不需要指定数据的位置（当然，你也可以指定）。

Create Table Like

CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name
  LIKE existing_table_or_view_name
  [LOCATION hdfs_path];

这是 Hive 中用于创建一个新表，其结构与现有表或视图相同的命令。下面是对这个命令各部分的解释：

• CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name：这是创建表的基本命令。TEMPORARY 是一个可选的关键字，用于创建临时表。EXTERNAL 是一个可选的关键字，用于创建外部表。IF NOT EXISTS 是一个可选的语句，如果存在具有指定名称的表，则不会创建新的表。是一个可选的语句，用于指定数据库的名称。
• LIKE existing_table_or_view_name：这部分指定了新表的结构应该如何。这将使新表具有与现有表或视图相同的列名和数据类型。请注意，这不会复制现有表或视图的数据，也不会复制任何关联的元数据，如分区、位置等信息。
• [LOCATION hdfs_path]：这是一个可选的语句，用于定义新表在 HDFS（Hadoop Distributed File System）上的位置。

这个命令的一个例子：

CREATE TABLE IF NOT EXISTS new_table
LIKE existing_table
LOCATION '/user/hive/new_table';

这将创建一个名为 new_table 的新表，如果它还不存在的话。新表将具有与 existing_table 相同的列名和数据类型，并存储在 HDFS 的 /user/hive/new_table 位置。

Create Table As Select (CTAS)

CREATE TABLE AS SELECT（经常简写为 CTAS）是一种在 SQL（包括 Hive）中用来创建并填充一个新表的常用方法。

CTAS 语句会创建一个新的表，并将 SELECT 查询的结果插入到这个新表中。新表的结构会自动匹配 SELECT 查询的结果。

以下是一个 CTAS 语句的基本格式：

CREATE TABLE new_table_name AS
SELECT column1, column2, ...
FROM existing_table_name
WHERE conditions;

在这个例子中，新的表 new_table_name 会被创建出来，然后 existing_table_name 中满足 WHERE 条件的行的 column1，column2 等列的值会被插入到新表中。

一个实际的例子可能如下：

CREATE TABLE employee_salaries AS
SELECT name, salary
FROM employee
WHERE salary > 50000;

在这个例子中，employee_salaries 表会包含 employee 表中所有 salary 大于 50000 的员工的 name 和 salary。

CTAS 是一种非常高效的方式来创建并填充一个新表，因为它只需要一次磁盘 I/O 操作。相比之下，如果你先创建一个空表，然后再用 INSERT 语句来填充，那么就需要两次磁盘 I/O 操作。

需要注意的是，CTAS 语句创建的新表默认是一个 Managed Table（内部表）。如果你想要创建一个 External Table（外部表），你需要使用 CREATE EXTERNAL TABLE 语句并指定数据的位置。

Drop Table

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

DROP TABLE 是一个 SQL 语句，用来删除已经存在的表。这个语句会删除表的定义（元数据）以及表的数据。

• IF EXISTS：这是一个可选的子句，表示如果表不存在，那么就忽略这个命令，而不是报错。这对于在脚本中使用 DROP TABLE 很有用，因为你不需要事先检查表是否存在。
• table_name：这是你要删除的表的名字。
• PURGE：这是一个可选的子句，表示立即删除数据，而不是放到回收站（Trash）。PURGE 子句在 Hive 0.14.0 及其后续版本中可用。

例如，下面的命令会删除 my_table 表，如果表不存在，那么就忽略这个命令，而不是报错。数据会立即删除，而不是放到回收站：

DROP TABLE IF EXISTS my_table PURGE;

请注意，对于 Managed Table（内部表），DROP TABLE 会删除表的数据。而对于 External Table（外部表），DROP TABLE 只会删除表的定义，而不会删除表的数据。这是因为 Hive 假定 External Table 的数据可能被其他工具共享，所以不会删除这些数据。如果你想要删除 External Table 的数据，你需要手动删除数据文件。

Truncate Table

TRUNCATE [TABLE] table_name [PARTITION partition_spec];

TRUNCATE TABLE 是 SQL 中的一个命令，用于删除表中的所有行，但保留表的结构（即列定义）以及其它属性，例如分区，索引等。

以下是这个选项中各部分的解释：

• TABLE：这是一个关键字，指定要清空的是一个表。在某些数据库系统中，TABLE 关键字是可选的。
• table_name：这是你要清空的表的名字。
• PARTITION partition_spec：这是一个可选的子句，用于指定你要截断的表的分区。如果你指定了这个子句，那么只有指定的分区会被清空，其他分区的数据将被保留。如果你没有指定这个子句，那么整个表都会被清空。

以下是一个例子：

TRUNCATE TABLE my_table PARTITION (year=2021, month=7);

在这个例子中，my_table 表的 year=2021, month=7 分区会被截断，也就是说，这个分区的所有数据会被删除，但分区本身和分区的结构会被保留。

请注意，与 DELETE 语句相比，TRUNCATE 语句执行更快，且使用更少的系统和事务日志资源。这是因为 TRUNCATE 语句删除整个表或分区，而不是一行一行地删除。但是，TRUNCATE 语句不能在具有外键约束的表上执行，因为它不会检查这些约束。

Alter Table/Partition/Column

Alter Table

Rename Table

ALTER TABLE table_name RENAME TO new_table_name;

Alter Table Properties

ALTER TABLE table_name SET TBLPROPERTIES table_properties;

ALTER TABLE 是 SQL 中的一个命令，用于修改已经存在的表的属性。SET TBLPROPERTIES 是 ALTER TABLE 的一个变体，用于更改表的属性。

以下是这个选项中各部分的解释：

• table_name：这是你要修改的表的名字。
• SET TBLPROPERTIES：这是一个关键字，表示你要设置表的属性。
• table_properties：这是一个键值对，用于指定要设置的属性及其值。键和值之间用等号(=)连接，不同的键值对之间用逗号(,)分隔。键和值都应该写在单引号(‘)里。

以下是一个例子：

ALTER TABLE my_table SET TBLPROPERTIES ('comment' = 'This is my table', 'created_by' = 'John Doe');

在这个例子中，my_table 表的 comment 属性被设置为 This is my table，created_by 属性被设置为 John Doe。

你可以使用 SET TBLPROPERTIES 来设置各种表的属性，例如表的评论，表的创建者，表的创建时间，等等。这些属性对于管理表和理解表的用途非常有用。例如，你可以查看表的 comment 属性来理解。

Alter Table Comment

ALTER TABLE table_name SET TBLPROPERTIES ('comment' = new_comment);

Add SerDe Properties

ALTER TABLE table_name [PARTITION partition_spec] SET SERDE serde_class_name [WITH SERDEPROPERTIES serde_properties];
ALTER TABLE table_name [PARTITION partition_spec] SET SERDEPROPERTIES serde_properties;

SerDe 是 Serializer/Deserializer 的缩写，是一个在数据存储和处理中用来序列化和反序列化数据的组件。

在 Hive 中，SerDe 负责三件事情：

• 序列化：将 Hive 内部的数据结构（例如一行数据）转换为可以写入磁盘或传输到网络上的一种格式（例如 CSV，JSON，Avro 等）。这在写入数据到 Hive 表时发生。
• 反序列化：将从磁盘读取或从网络上接收的数据转换为 Hive 内部的数据结构。这在读取数据从 Hive 表时发生。
• 解析：对输入的行进行解析，产生一组字段以及它们的类型。这在创建表或读取数据时发生。

SerDe 定义了数据如何在 Hive 表和磁盘之间移动。Hive 使用 SerDe 来确定如何读取用户在 LOAD DATA INPATH 语句中指定的数据，以及如何存储在 INSERT INTO 语句中插入的数据。

你可以为 Hive 表选择合适的 SerDe 来满足你的需求。例如，如果你的数据是 CSV 格式的，你可以选择一个处理 CSV 数据的 SerDe。如果你的数据是 JSON 格式的，你可以选择一个处理 JSON 数据的 SerDe。Hive 提供了一些内置的 SerDe 供你选择，你也可以创建自己的 SerDe。

Alter Partition

Add Partitions

ALTER TABLE table_name ADD [IF NOT EXISTS] PARTITION partition_spec [LOCATION 'location'][, PARTITION partition_spec [LOCATION 'location'], ...];

ALTER TABLE … ADD PARTITION 是 Hive SQL 中的一个命令，用于向已经存在的表中添加分区。

以下是这个选项中各部分的解释：

• table_name：这是你想要添加分区的表的名字。
• IF NOT EXISTS：这是一个可选的子句，如果你指定了这个子句，那么当你试图添加的分区已经存在时，命令不会报错，而是直接忽略。如果你没有指定这个子句，那么当你试图添加的分区已经存在时，命令会报错。
• PARTITION partition_spec：这是一个关键字，后面跟着你要添加的分区的规格。partition_spec 是一个键值对，用于指定分区的列及其值。键和值之间用等号(=)连接，不同的键值对之间用逗号(,)分隔。键和值都应该写在单引号(‘)里。
• LOCATION ‘location’：这是一个可选的子句，用于指定分区的数据的位置。如果你指定了这个子句，那么分区的数据将存储在你指定的位置。如果你没有指定这个子句，那么分区的数据将存储在默认的位置。

以下是一个例子：

ALTER TABLE my_table ADD IF NOT EXISTS 
PARTITION (year='2021', month='7') 
LOCATION '/data/my_table/2021/07';

在这个例子中，向 my_table 表添加了一个新的分区 year=’2021′, month=’7’。这个分区的数据将存储在 /data/my_table/2021/07 路径下。如果这个分区已经存在，那么命令不会报错，而是直接忽略。

这个命令对于分区表非常有用。你可以使用这个命令将新的数据文件添加到分区表，或者将已经存在的数据文件关联到分区表。你可以为每个分区指定一个不同的位置，这样你可以将数据文件存储在最适合的位置，例如，你可以将频繁访问的数据文件存储在快速的磁盘上，将不常访问的数据文件存储在慢速但容量大的磁盘上。

动态分区

在 Hive 中，分区表是一种特殊的表，它的数据被分成多个分区，每个分区对应一个目录。每个分区都有一个或多个列（分区列）的值与之关联。对于静态分区，插入数据时需要显式指定分区列的值。而对于动态分区，插入数据时不需要显式指定分区列的值，Hive 会根据插入的数据自动创建并填充分区。

动态分区的特点是：在执行 INSERT 操作时，可以根据分区列的值动态地创建新的分区。如果某个分区列的值在表中还不存在，Hive 就会创建一个新的分区。这对于大规模的、需要频繁添加新分区的数据非常有用，因为你不需要事先知道所有可能的分区列的值。

以下是使用动态分区的一个例子：

INSERT INTO TABLE page_view PARTITION(date)
SELECT userID, pageID, date
FROM raw_page_view;

在这个例子中，date 是分区列。Hive 会自动根据 raw_page_view 表中的 date 列的值创建新的分区。

注意，虽然动态分区在处理大规模数据时非常有用，但是也有一些限制和注意事项。例如，Hive 默认限制了一个查询可以创建的分区的数量，以防止创建过多的分区导致性能问题。此外，如果一个表有多个分区列，那么动态分区列必须是分区列中的最后一个或几个。

Rename Partition

ALTER TABLE table_name PARTITION partition_spec RENAME TO PARTITION partition_spec;

Drop Partitions

ALTER TABLE table_name DROP [IF EXISTS] PARTITION partition_spec[, PARTITION partition_spec, ...]
  [IGNORE PROTECTION] [PURGE];            -- (Note: PURGE available in Hive 1.2.0 and later, IGNORE PROTECTION not available 2.0.0 and later)

(Un)Archive Partition

在 Hive 中，(Un)Archive Partition 指的是将分区归档或解归档的操作。 Hive 提供了归档分区的功能，以便于在不删除数据的情况下，减小 HDFS 上目录和文件的数量。这对大量小文件的处理非常有用，因为在 HDFS 中，大量小文件会占用大量的名字空间，从而影响 NameNode 的性能。

归档操作是通过使用 Hadoop 的 HAR（Hadoop Archive）文件格式来压缩分区数据实现的。HAR 文件是一种将大量文件压缩为单一文件的方式，这样可以减少 NameNode 需要管理的元数据。然而，HAR 文件有一定的访问开销，所以归档后的分区可能无法在现有的 Hive 查询中直接使用。

解归档操作则是将之前归档的分区恢复为原来的状态。

以下是一个归档分区的示例：

ALTER TABLE my_table ARCHIVE PARTITION (year='2021', month='7');

在这个例子中，my_table 表的 year=’2021′, month=’7′ 分区被归档。

以下是一个解归档分区的示例：

ALTER TABLE my_table UNARCHIVE PARTITION (year='2021', month='7');

在这个例子中，my_table 表的 year=’2021′, month=’7′ 分区被解归档。

这些操作可以帮助优化存储和查询性能，但需要根据你的具体需求和场景进行管理。

Alter Column

Change Column Name/Type/Position/Comment

ALTER TABLE table_name [PARTITION partition_spec] CHANGE [COLUMN] col_old_name col_new_name column_type
  [COMMENT col_comment] [FIRST|AFTER column_name] [CASCADE|RESTRICT];

ALTER TABLE … CHANGE COLUMN 是 Hive SQL 中的一个命令，用于修改已经存在的表或分区的列的名称、类型或注释。

以下是这个选项中各部分的解释：

• table_name：这是你要修改的表的名字。
• PARTITION partition_spec：这是一个可选的子句，用于指定你要修改的表的分区。如果你指定了这个子句，那么只有指定的分区的列会被更改，其他分区的列将不会被更改。如果你没有指定这个子句，那么整个表的列都会被更改。
• col_old_name：这是你要修改的列的当前（旧）名称。
• col_new_name：这是你要修改的列的新名称。
• column_type：这是你要修改的列的新类型。Hive 支持各种数据类型，包括基本类型（如 INT, STRING）和复合类型（如 ARRAY, MAP, STRUCT）。
• COMMENT col_comment：这是一个可选的子句，用于添加或修改列的注释。
• FIRST | AFTER column_name：这是一个可选的子句，用于指定新列的位置。如果你指定了 FIRST，那么新列将成为表的第一列；如果你指定了 AFTER column_name，那么新列将插入到指定的列之后。
• CASCADE | RESTRICT：这是一个可选的子句，用于指定当列被其他对象（如视图）引用时的行为。如果你指定了 CASCADE，那么这些对象也会被更新；如果你指定了 RESTRICT（默认），那么如果列被其他对象引用，命令将失败。

以下是一个例子：

ALTER TABLE my_table CHANGE COLUMN old_col new_col INT COMMENT 'This is a new column' FIRST;

在这个例子中，my_table 表的 old_col 列的名称被改为 new_col，类型被改为 INT，注释被改为 This is a new column，并且这个列被移动到表的第一列。

这个命令允许你更改列的属性，而不更改列的数据。这对于调整数据模式非常有用。例如，你可以更改列的名称来修复拼写错误，或者更改列的类型来处理新的数据格式。

Add/Replace Columns

ALTER TABLE table_name 
  [PARTITION partition_spec]                 -- (Note: Hive 0.14.0 and later)
  ADD|REPLACE COLUMNS (col_name data_type [COMMENT col_comment], ...)
  [CASCADE|RESTRICT]                         -- (Note: Hive 1.1.0 and later)

ALTER TABLE … ADD|REPLACE COLUMNS 是 Hive SQL 中的一个命令，用于添加新的列或者替换已经存在的列。

以下是这个选项中各部分的解释：

• table_name：这是你要修改的表的名字。
• PARTITION partition_spec：这是一个可选的子句，用于指定你要修改的表的分区。如果你指定了这个子句，那么只有指定的分区的列会被添加或替换，其他分区的列将不会被改变。这个选项在 Hive 0.14.0 及以后的版本中可用。
• ADD|REPLACE：这是一个关键字，用于指定你要添加新的列还是替换已经存在的列。如果你指定了 ADD，那么新的列将被添加到表的末尾；如果你指定了 REPLACE，那么新的列将替换掉表中的所有列。
• COLUMNS (col_name data_type [COMMENT col_comment], …)：这是一个列表，用于指定你要添加或替换的列的名字、类型和注释。每个列都由列名、数据类型和可选的注释组成。列之间用逗号(,)分隔。
• CASCADE|RESTRICT：这是一个可选的子句，用于指定当列被其他对象（如视图）引用时的行为。如果你指定了 CASCADE，那么这些对象也会被更新；如果你指定了 RESTRICT（默认），那么如果列被其他对象引用，命令将失败。这个选项在 Hive 1.1.0 及以后的版本中可用。

以下是一个添加列的例子：

ALTER TABLE my_table ADD COLUMNS (new_col1 INT COMMENT 'This is a new column', new_col2 STRING);

在这个例子中，两个新的列 new_col1 和 new_col2 被添加到 my_table 表的末尾。

以下是一个替换列的例子：

ALTER TABLE my_table REPLACE COLUMNS (new_col1 INT COMMENT 'This is a new column', new_col2 STRING);

在这个例子中，my_table 表中的所有列都被替换为两个新的列 new_col1 和 new_col2。

这个命令允许你调整表的模式，而不更改表的数据。这对于处理模式演变非常有用。例如，你可以添加新的列来存储新的数据字段，或者替换所有的列来应对大的模式更改。

Create/Drop/Alter View

Create View

CREATE VIEW [IF NOT EXISTS] [db_name.]view_name [(column_name [COMMENT column_comment], ...) ]
  [COMMENT view_comment]
  [TBLPROPERTIES (property_name = property_value, ...)]
  AS SELECT ...;

CREATE VIEW 是 Hive SQL 中的一个命令，用于创建一个视图。视图是一个虚拟的表，其内容由查询定义。换句话说，视图并不包含实际的数据，而只是保存了获取数据的 SQL 查询。当查询视图时，Hive 会执行该视图的定义查询并返回结果。

以下是这个选项中各部分的解释：

• IF NOT EXISTS：这是一个可选的子句，表示如果指定的视图已经存在，那么 Hive 将不会做任何事情，也不会报错。如果你没有指定这个子句，而指定的视图已经存在，那么 Hive 将报错。
• view_name：这是你要创建的视图的名字。你可以包含一个可选的数据库名，如果没有指定数据库名，那么视图将在当前数据库中创建。
• (column_name [COMMENT column_comment], …)：这是一个可选的列表，用于指定视图的列名和可选的列注释。如果你没有指定这个列表，那么视图的列将直接从定义查询中获取。
• COMMENT view_comment：这是一个可选的子句，用于添加给视图的注释。
• TBLPROPERTIES (property_name = property_value, …)：这是一个可选的子句，用于添加视图的属性。这些属性可以用于在 Hive 元数据中存储关于视图的额外信息。
• AS SELECT …：这是定义视图的查询。这个查询确定了视图的内容。当查询视图时，Hive 将执行这个查询并返回结果。

以下是一个创建视图的例子：

CREATE VIEW IF NOT EXISTS my_view AS SELECT col1, col2 FROM my_table WHERE col3 > 100;

在这个例子中，一个名为 my_view 的视图被创建，该视图由 my_table 中 col3 > 100 的 col1 和 col2 列的所有行组成。

视图对于封装复杂的查询和提供对数据的抽象视图非常有用。你可以将视图看作是一个已经写好的查询，而不必每次都写完整的查询。

Drop View

DROP VIEW [IF EXISTS] [db_name.]view_name;

Alter View Properties

ALTER VIEW [db_name.]view_name SET TBLPROPERTIES table_properties;

Create/Drop/Alter Materialized View

Create Materialized View

CREATE MATERIALIZED VIEW [IF NOT EXISTS] [db_name.]materialized_view_name
  [DISABLE REWRITE]
  [COMMENT materialized_view_comment]
  [PARTITIONED ON (col_name, ...)]
  [CLUSTERED ON (col_name, ...) | DISTRIBUTED ON (col_name, ...) SORTED ON (col_name, ...)]
  [
    [ROW FORMAT row_format]
    [STORED AS file_format]
      | STORED BY 'storage.handler.class.name' [WITH SERDEPROPERTIES (...)]
  ]
  [LOCATION hdfs_path]
  [TBLPROPERTIES (property_name=property_value, ...)]
AS SELECT ...;

CREATE MATERIALIZED VIEW 是 Hive SQL 中的一个命令，用于创建一个物化视图。物化视图和基本视图类似，都是一个虚拟表，其内容由查询定义。然而，与基本视图不同，物化视图的结果会被实际存储在 Hive 中，就像一个真实的表一样。因此，查询物化视图通常比查询基本视图要快，因为物化视图的数据已经预先计算并存储了。

以下是这个选项中各部分的解释：

• IF NOT EXISTS：这是一个可选的子句，表示如果指定的物化视图已经存在，那么 Hive 将不会做任何事情，也不会报错。如果你没有指定这个子句，而指定的物化视图已经存在，那么 Hive 将报错。
• materialized_view_name：这是你要创建的物化视图的名字。你可以包含一个可选的数据库名，如果没有指定数据库名，那么物化视图将在当前数据库中创建。
• DISABLE REWRITE：这是一个可选的子句，用于禁止 Hive 对查询进行重写，使其使用物化视图。如果你指定了这个子句，那么当查询可以使用物化视图时，Hive 仍然会执行原始查询。
• COMMENT materialized_view_comment：这是一个可选的子句，用于添加给物化视图的注释。
• PARTITIONED ON (col_name, …)：这是一个可选的子句，用于指定物化视图的分区列。物化视图的分区策略和表的分区策略类似，可以帮助改进查询性能。
• CLUSTERED ON (col_name, …) | DISTRIBUTED ON (col_name, …) SORTED ON (col_name, …)：这是一些可选的子句，用于指定物化视图的分布策略和排序策略。这些策略可以帮助改进查询性能。
• ROW FORMAT row_format：这是一个可选的子句，用于指定物化视图的行格式，包括字段分隔符、集合分隔符等。
• STORED AS file_format：这是一个可选的子句，用于指定物化视图的存储格式，如 ORC、Parquet 等。
• STORED BY ‘storage.handler.class.name’ [WITH SERDEPROPERTIES (…)]：这是一个可选的子句，用于指定物化视图的存储处理类和序列化/反序列化属性。
• LOCATION hdfs_path：这是一个可选的子句，用于指定物化视图的 HDFS 存储位置。
• TBLPROPERTIES (property_name=property_value, …)：这是一个可选的子句，用于添加物化视图的属性。这些属性可以用于在 Hive 元数据中存储关于物化视图的额外信息。
• AS SELECT …：这是定义物化视图的查询。这个查询确定了物化视图的内容。当查询物化视图时，Hive 不需要执行这个查询，而是直接从存储的结果中获取数据。

总的来说，物化视图是一个强大的性能优化工具。通过预先计算和存储查询结果，物化视图可以显著减少查询时间。然而，物化视图需要额外的存储空间，并且需要定期刷新以保持与基础数据的同步。在使用物化视图时，需要考虑这些权衡。

Drop Materialized View

DROP MATERIALIZED VIEW [db_name.]materialized_view_name;

Alter Materialized View

ALTER MATERIALIZED VIEW [db_name.]materialized_view_name ENABLE|DISABLE REWRITE;

Show

Show Databases

SHOW (DATABASES|SCHEMAS) [LIKE 'identifier_with_wildcards'];

Show Tables/Views/Materialized Views/Partitions/Indexes

Show Tables

SHOW TABLES [IN database_name] ['identifier_with_wildcards'];

SHOW TABLES 是 Hive SQL 中的一个命令，用于列出数据库中的表名。你可以使用这个命令来查看数据库中有哪些表，或者查看是否存在具有特定名称的表。

以下是这个选项中各部分的解释：

• IN database_name：这是一个可选的子句，用于指定要列出表名的数据库。如果你没有指定这个子句，那么 Hive 将列出当前数据库中的表名。
• ‘identifier_with_wildcards’：这是一个可选的参数，用于指定要列出的表名的模式。你可以使用 % 符号作为通配符，代表任意字符。例如，’user%’ 将匹配所有以 user 开头的表名。如果你没有指定这个参数，那么 Hive 将列出所有的表名。

以下是一些使用 SHOW TABLES 的例子：

SHOW TABLES;

这个命令将列出当前数据库中所有的表名。

SHOW TABLES IN my_database;

这个命令将列出 my_database 数据库中所有的表名。

SHOW TABLES 'user%';

这个命令将列出当前数据库中所有以 user 开头的表名。

SHOW TABLES 命令是一个快速查看数据库中表的简单工具，可以帮助你理解数据库的结构和内容。

Show Views

SHOW VIEWS [IN/FROM database_name] [LIKE 'pattern_with_wildcards'];

示例：

SHOW VIEWS;                                -- show all views in the current database
SHOW VIEWS 'test_*';                       -- show all views that start with "test_"
SHOW VIEWS '*view2';                       -- show all views that end in "view2"
SHOW VIEWS LIKE 'test_view1|test_view2';   -- show views named either "test_view1" or "test_view2"
SHOW VIEWS FROM test1;                     -- show views from database test1
SHOW VIEWS IN test1;                       -- show views from database test1 (FROM and IN are same)
SHOW VIEWS IN test1 "test_*";              -- show views from database test2 that start with "test_"

Show Materialized Views

SHOW MATERIALIZED VIEWS [IN/FROM database_name] [LIKE 'pattern_with_wildcards’];

Show Partitions

SHOW PARTITIONS [db_name.]table_name [PARTITION(partition_spec)];   -- (Note: Hive 0.13.0 and later)

SHOW PARTITIONS 是 Hive SQL 中的一个命令，用于列出表的所有分区。在 Hive 中，表可以被分区，每个分区都有它自己的数据和元数据。每个分区可以看作是表的一个子集，它们一起构成了整个表。在处理大量数据时，分区可以显著提高查询性能。

以下是这个选项中各部分的解释：

• table_name：这是你要列出分区的表的名字。你可以包含一个可选的数据库名，如果没有指定数据库名，那么 Hive 将列出当前数据库中的表的分区。
• PARTITION(partition_spec)：这是一个可选的子句，用于指定要列出的分区的模式。你可以使用这个子句来过滤分区，例如，只列出满足特定条件的分区。如果你没有指定这个子句，那么 Hive 将列出所有的分区。

以下是一个使用 SHOW PARTITIONS 的例子：

SHOW PARTITIONS my_table;

这个命令将列出 my_table 表的所有分区。

SHOW PARTITIONS my_table PARTITION(day='2022-01-01');

这个命令将列出 my_table 表中 day 分区值为 2022-01-01 的分区。

SHOW PARTITIONS 命令是一个快速查看表分区的简单工具，可以帮助你理解表的结构和分区策略。对于大型表，分区是一种重要的性能优化策略，SHOW PARTITIONS 命令可以帮助你验证你的分区策略是否正确。

Show Table/Partition Extended

SHOW TABLE EXTENDED [IN|FROM database_name] LIKE 'identifier_with_wildcards' [PARTITION(partition_spec)];

SHOW TABLE EXTENDED 是 Hive SQL 中的一个命令，用于显示关于一个或多个表的扩展信息。这个命令类似于 SHOW TABLES，但是它提供的信息更详细，包括表的所有者、创建时间、最后一次修改时间、表的备注、表的位置等。

以下是这个选项中各部分的解释：

• IN|FROM database_name：这是一个可选的子句，用于指定要显示表信息的数据库。如果你没有指定这个子句，那么 Hive 将显示当前数据库中的表信息。
• LIKE ‘identifier_with_wildcards’：这是一个参数，用于指定要显示的表名的模式。你可以使用 % 符号作为通配符，代表任意字符。例如，’user%’ 将匹配所有以 user 开头的表名。
• PARTITION(partition_spec)：这是一个可选的子句，用于指定要显示的表的分区。如果你指定了这个子句，那么 Hive 将只显示指定分区的信息。

以下是一个使用 SHOW TABLE EXTENDED 的例子：

SHOW TABLE EXTENDED LIKE 'my_table';

这个命令将显示 my_table 的扩展信息。

SHOW TABLE EXTENDED IN my_database LIKE 'user%';

这个命令将显示 my_database 数据库中所有以 user 开头的表的扩展信息。

SHOW TABLE EXTENDED 命令是一个查看表详细信息的工具，可以帮助你理解表的结构、表的创建和修改历史、表的存储位置和表的分区情况等。这个命令对于调试和优化查询非常有用。

Show Table Properties

SHOW TBLPROPERTIES tblname;
SHOW TBLPROPERTIES tblname("foo");

Show Create Table

SHOW CREATE TABLE ([db_name.]table_name|view_name);

Show Indexes

SHOW [FORMATTED] (INDEX|INDEXES) ON table_with_index [(FROM|IN) db_name];

SHOW INDEXES 或 SHOW INDEX 是 Hive SQL 中的一个命令，用于显示表的索引信息。在 Hive 中，索引是一种数据结构，可以帮助改进查询性能。

以下是这个选项中各部分的解释：

• FORMATTED：这是一个可选的子句，用于以格式化的形式显示索引信息。如果你指定了这个子句，那么 Hive 将以更易读的格式显示索引信息。
• INDEX|INDEXES：这两个词是等价的，你可以使用任何一个。它们表示你要显示的是索引信息。
• ON table_with_index：这是你要显示索引信息的表的名字。
• (FROM|IN) db_name：这是一个可选的子句，用于指定表所在的数据库。如果你没有指定这个子句，那么 Hive 将显示当前数据库中的表的索引信息。

以下是一个使用 SHOW INDEXES 的例子：

SHOW INDEXES ON my_table;

这个命令将显示 my_table 的索引信息。

SHOW FORMATTED INDEXES ON my_table IN my_database;

这个命令将以格式化的形式显示 my_database 数据库中 my_table 的索引信息。

SHOW INDEXES 命令是一个查看表索引信息的工具，可以帮助你理解表的索引结构和索引策略。这个命令对于调试和优化查询非常有用。

Show Columns

SHOW COLUMNS (FROM|IN) table_name [(FROM|IN) db_name];

示例：

-- SHOW COLUMNS
CREATE DATABASE test_db;
USE test_db;
CREATE TABLE foo(col1 INT, col2 INT, col3 INT, cola INT, colb INT, colc INT, a INT, b INT, c INT);
  
-- SHOW COLUMNS basic syntax
SHOW COLUMNS FROM foo;                            -- show all column in foo
SHOW COLUMNS FROM foo "*";                        -- show all column in foo
SHOW COLUMNS IN foo "col*";                       -- show columns in foo starting with "col"                 OUTPUT col1,col2,col3,cola,colb,colc
SHOW COLUMNS FROM foo '*c';                       -- show columns in foo ending with "c"                     OUTPUT c,colc
SHOW COLUMNS FROM foo LIKE "col1|cola";           -- show columns in foo either col1 or cola                 OUTPUT col1,cola
SHOW COLUMNS FROM foo FROM test_db LIKE 'col*';   -- show columns in foo starting with "col"                 OUTPUT col1,col2,col3,cola,colb,colc
SHOW COLUMNS IN foo IN test_db LIKE 'col*';       -- show columns in foo starting with "col" (FROM/IN same)  OUTPUT col1,col2,col3,cola,colb,colc
  
-- Non existing column pattern resulting in no match
SHOW COLUMNS IN foo "nomatch*";
SHOW COLUMNS IN foo "col+";                       -- + wildcard not supported
SHOW COLUMNS IN foo "nomatch";

HiveQL DML语句

LOAD

在 Hive SQL 中，LOAD 命令是用来加载数据到 Hive 表中的。它主要用于将数据从 HDFS 或者本地文件系统中的文件或目录，加载到 Hive 表中。

以下是 LOAD 命令的基本语法：

LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)]

以下是这个命令中各部分的解释：

• DATA：这个关键字后面跟着源数据的路径。
• LOCAL：这是一个可选的关键字，表示源数据位于本地文件系统。如果没有指定 LOCAL，那么 Hive 会假定源数据在 HDFS 上。
• INPATH：这个关键字后面跟着的是数据文件或目录的路径。必须是单引号括起来的字符串。
• OVERWRITE：这是一个可选的关键字，表示如果目标表已经有数据，那么新加载的数据将覆盖旧数据。如果没有指定 OVERWRITE，那么新数据将被添加到表中，不会删除旧数据。
• INTO TABLE：这个关键字后面跟着的是目标表的名字。
• PARTITION：这是一个可选的子句，用于指定数据加载到哪个分区。如果没有指定这个子句，那么数据将被加载到表的默认分区。

以下是一个使用 LOAD 命令的例子：

LOAD DATA LOCAL INPATH '/home/user/data.txt' OVERWRITE INTO TABLE my_table;

这个命令将从本地文件系统中的 /home/user/data.txt 文件中加载数据，然后覆盖 my_table 表中的数据。

LOAD 命令是一个快速加载大量数据到 Hive 表中的工具。注意，LOAD 命令不会对数据进行任何转换或处理，它只是简单地将数据文件或目录复制或移动到 Hive 表的存储位置。如果你需要对数据进行转换或处理，你可能需要使用其他的 Hive SQL 命令，如 INSERT。

INSERT

在 Hive SQL中，INSERT命令用于插入数据到 Hive 表或分区中。与其他SQL数据库中的 INSERT 类似，但是 Hive 支持多种插入数据的方式，包括从查询结果插入，插入到一个或多个表或分区。

以下是 INSERT 命令的基本语法：

INSERT OVERWRITE|INTO TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...)] select_statement1 FROM from_statement;
INSERT OVERWRITE|INTO TABLE tablename2 [PARTITION (partcol1=val1, partcol2=val2 ...)] select_statement2 FROM from_statement;
...

以下是这个命令中各部分的解释：

• OVERWRITE|INTO：这两个关键字用于指定插入方式。如果使用 OVERWRITE，那么新插入的数据将覆盖表或分区中现有的数据。如果使用 INTO，那么新数据将添加到表或分区中现有的数据之后。
• TABLE：这个关键字后面跟着的是目标表的名字。
• PARTITION：这是一个可选的子句，用于指定数据插入到哪个分区。如果没有指定这个子句，那么数据将被插入到表的默认分区。
• select_statement：这是从源表选择数据的 SQL 查询语句。
• from_statement：这是指定源表的子句。

以下是一个使用 INSERT 命令的例子：

INSERT OVERWRITE TABLE my_table SELECT * FROM another_table;

这个命令将从 another_table 查询所有数据，并覆盖 my_table 表中的数据。

INSERT 命令是一个插入数据到 Hive 表中的工具。注意，与 LOAD 命令不同，INSERT 命令可以在插入数据的同时进行数据转换和处理。

UPDATE

在 Hive SQL 中，UPDATE 命令用于修改已存在的记录中的数据。这个命令可以让你根据指定的条件更改表中的数据。 但是，这个命令在 Hive 中的使用有一些限制，因为 Hive 主要是用于处理大规模的数据，而不是单行级别的更新。

以下是 UPDATE 命令的基本语法：

UPDATE tablename SET column = value [, column = value ...] [WHERE expression]

以下是这个命令中各部分的解释：

• tablename：这是你要更新的表的名字。
• SET：这个关键字后面跟着的是一个或多个列和新值的对，用于指定应该更新哪些列和新的值。列和值之间用等号连接，多个列和值之间用逗号分隔。
• WHERE：这是一个可选的子句，用于指定应该更新哪些行。这个子句后面跟着的是一个布尔表达式，只有满足这个表达式的行才会被更新。

以下是一个使用 UPDATE 命令的例子：

UPDATE my_table SET column1 = 'new_value' WHERE column2 = 'old_value';

这个命令将 my_table 表中 column2 的值为 old_value 的行的 column1 更新为 new_value。

UPDATE 命令是一个更新 Hive 表中数据的工具。但是，由于 Hive 的设计目标是处理大规模的数据，所以 UPDATE 命令在 Hive 中可能没有其他 SQL 数据库中那么高效。如果你需要更新大量的数据，可能需要使用其他的 Hive SQL 命令，如 INSERT OVERWRITE。

DELETE

在 Hive SQL 中，DELETE 命令用于删除表中的已存在的记录。这个命令可以让你根据指定的条件删除表中的数据。但是，这个命令在 Hive 中的使用有一些限制，因为 Hive 主要是用于处理大规模的数据，而不是单行级别的删除。

以下是 DELETE 命令的基本语法：

DELETE FROM tablename [WHERE expression]

以下是这个命令中各部分的解释：

• FROM：这个关键字后面跟着的是你要删除数据的表的名字。
• WHERE：这是一个可选的子句，用于指定应该删除哪些行。这个子句后面跟着的是一个布尔表达式，只有满足这个表达式的行才会被删除。

以下是一个使用 DELETE 命令的例子：

DELETE FROM my_table WHERE column1 = 'value';

这个命令将删除 my_table 表中 column1 的值为 value 的所有行。

DELETE 命令是一个删除 Hive 表中数据的工具。但是，由于 Hive 的设计目标是处理大规模的数据，所以 DELETE 命令在 Hive 中可能没有其他 SQL 数据库中那么高效。如果你需要删除大量的数据，可能需要使用其他的 Hive SQL 命令，如 INSERT OVERWRITE。

MERGE

在 Hive SQL 中，MERGE 命令用于合并两个表或分区，它可以根据特定的条件在目标表中插入、更新或删除数据。它主要用于处理 ETL（Extract, Transform, Load）任务，在数据发生变化时，同步数据仓库和操作型数据系统。

以下是 MERGE 命令的基本语法：

MERGE INTO target AS T USING source AS S ON (T.id = S.id)
WHEN MATCHED AND condition THEN UPDATE SET column1 = value1 [, column2 = value2 ...]
WHEN MATCHED AND condition THEN DELETE
WHEN NOT MATCHED AND condition THEN INSERT VALUES(value1 [, value2 ...])

以下是这个命令中各部分的解释：

• target 和 source：是目标表和源表的名字。
• USING：这个关键字后面跟着的是源表的名字。
• ON：这个关键字后面跟着的是连接目标表和源表的条件。
• WHEN MATCHED：这个子句用于指定当目标表和源表的行匹配时应该执行的操作。可以是更新或删除操作。
• WHEN NOT MATCHED：这个子句用于指定当目标表中没有与源表匹配的行时应该执行的操作。可以是插入操作。
• AND condition：这是一个可选的部分，用于在 WHEN MATCHED 或 WHEN NOT MATCHED 子句中进一步指定条件。

以下是一个使用 MERGE 命令的例子：

MERGE INTO target AS T USING source AS S ON (T.id = S.id)
WHEN MATCHED AND T.amount > S.amount THEN UPDATE SET T.amount = S.amount
WHEN MATCHED AND T.amount <= S.amount THEN DELETE
WHEN NOT MATCHED AND S.amount > 0 THEN INSERT VALUES(S.id, S.amount)

这个命令将根据源表 source 更新目标表 target。当 target 的 amount 大于 source 的 amount 时，更新 target 的 amount。当 target 的 amount 小于等于 source 的 amount 时，删除 target 的行。当 source 有新的 amount 大于 0 的行时，插入到 target。

MERGE 命令是一个强大的工具，可以在一个查询中完成多种数据操作，大大简化了数据处理和同步的过程。但是，由于其复杂性，使用时需要谨慎，确保理解所有的操作条件和结果。

HiveQL查询语句

SELECT

[WITH CommonTableExpression (, CommonTableExpression)*]    (Note: Only available starting with Hive 0.13.0)
SELECT [ALL | DISTINCT] select_expr, select_expr, ...
  FROM table_reference
  [WHERE where_condition]
  [GROUP BY col_list]
  [ORDER BY col_list]
  [CLUSTER BY col_list
    | [DISTRIBUTE BY col_list] [SORT BY col_list]
  ]
 [LIMIT [offset,] rows]

这段代码是 Hive 中 SELECT 语句的一种结构化表示，包括了大部分常见选项。下面是对这段代码的部分解读：

• WITH CommonTableExpression (, CommonTableExpression)：这个部分是可选的，用于定义公共表达式（也称为 CTE）。公共表达式是一个临时的结果集，可以在查询中的多个位置引用。这可以让你简化复杂的查询，避免重复的子查询。这个选项在 Hive 0.13.0 以后的版本中可用。
• SELECT [ALL | DISTINCT] select_expr, select_expr, …：这是 SELECT 语句的核心部分，用于选择需要查询的列。默认是选择所有行（ALL），也可以选择唯一的行（DISTINCT）。
• FROM table_reference：指定查询的数据源，可以是一个表，也可以是子查询的结果。
• [WHERE where_condition]：这是一个可选的子句，用于限制返回的行，只有满足 where_condition 的行才会被返回。
• [GROUP BY col_list]：这是一个可选的子句，用于按照指定的列对结果进行分组。
• [ORDER BY col_list]：这是一个可选的子句，用于按照指定的列对结果进行排序。
• [CLUSTER BY col_list | [DISTRIBUTE BY col_list] [SORT BY col_list]]：这些是一些高级选项，用于控制查询的数据分发和排序。 CLUSTER BY 会在每个归约(reducer)内对数据进行排序。DISTRIBUTE BY 决定了如何将数据分发到归约器。SORT BY 在每个归约器内部对数据进行排序。
• [LIMIT [offset,] rows]：这是一个可选的子句，用于限制返回的行数。可以指定开始返回的偏移量（offset）和需要返回的行数（rows）。

这个结构化表示显示了 SELECT 语句的灵活性和能力，你可以根据需要选择使用这些选项，以创建从简单到复杂的各种查询。

ORDER BY、SORT BY、DISTRIBUTE BY与CLUSTER BY

ORDER BY

ORDER BY 语句在 Hive SQL 中用于对查询结果进行全局排序，返回的结果是按照指定的列和排序方向（升序或降序）排序的。以下是一个基本的 ORDER BY 语法：

SELECT column1, column2 FROM table_name ORDER BY column1 [ASC|DESC], column2 [ASC|DESC];

在上面的语句中，table_name 是你要查询的表名，column1 和 column2 是你要选择的列，ORDER BY 后面是你要排序的列以及排序的方向（升序 ASC 或者降序 DESC）。

但是，ORDER BY 在 Hive 中的效率可能不是很高，因为它需要所有的数据都在一个 reducer 中进行排序，这可能导致处理速度较慢和内存使用过高。

SORT BY

SORT BY 语句在 Hive SQL 中也用于对数据进行排序，但是不同于 ORDER BY，SORT BY 对每个 reducer 的输出进行排序，而不是全局排序。这意味着，如果你的查询使用了多个 reducer，那么你将得到一个每个 reducer 输出都已经排序的结果，而不是整体上的排序。

以下是一个基本的 SORT BY 语法：

SELECT column1, column2 FROM table_name SORT BY column1 [ASC|DESC], column2 [ASC|DESC];

在上面的语句中，table_name 是你要查询的表名，column1 和 column2 是你要选择的列，SORT BY 后面是你要排序的列以及排序的方向（升序 ASC 或者降序 DESC）。

而与 ORDER BY 相比，SORT BY 的效率更高，因为它可以同时在多个 reducer 中进行排序。但是，它的结果可能不如 ORDER BY 完全排序。

因此，你应该根据你的需求和数据的大小选择使用 ORDER BY 还是 SORT BY。如果你需要全局排序并且数据量不大，那么 ORDER BY 可能是一个好选择。如果你的数据量非常大并且可以接受每个 reducer 输出的结果分别排序，那么 SORT BY 可能更适合你。

DISTRIBUTE BY

DISTRIBUTE BY 语句用于控制将数据分发到 reducers 的方式。它按照指定的列的值将数据分发给 reducers，确保具有相同值的行被分发到同一个 reducer。这对于某些需要在每个键值上进行处理的操作很有用，例如排序或聚合。

下面是一个基本的 DISTRIBUTE BY 语法：

SELECT column1, column2 FROM table_name DISTRIBUTE BY column1;

在上述语句中，table_name 是你要查询的表名，column1 和 column2 是你要选择的列，DISTRIBUTE BY column1 指定了应按照 column1 的值将数据分发给 reducers。

CLUSTER BY

CLUSTER BY 语句是 DISTRIBUTE BY 和 SORT BY 的结合。它首先按照 DISTRIBUTE BY 分发数据，确保具有相同值的行被分发到同一个 reducer。然后，在每个 reducer 内部，它按照 SORT BY 对数据进行排序。

下面是一个基本的 CLUSTER BY 语法：

SELECT column1, column2 FROM table_name CLUSTER BY column1;

在上述语句中，table_name 是你要查询的表名，column1 和 column2 是你要选择的列，CLUSTER BY column1 指定了应按照 column1 的值将数据分发，并在每个 reducer 内部排序。

JOIN性能优化

在 Hive 中，数据量巨大，因此对 JOIN 操作进行优化至关重要。下面列出了一些 Hive JOIN 操作的优化方法：

• Map Join (Map Side Join)：如果你在大表和小表之间进行连接，其中小表的数据量小到可以装入内存，那么可以使用 Map Join。Map Join 通过预加载小表的数据到内存，避免了复杂的 shuffle 和 reduce 过程，从而大大提高了性能。你可以设置auto.convert.join 为 true 以使 Hive 自动决定何时使用 Map Join。
• Bucketed Map Join：如果你的数据已经被分桶（bucketed），并且小表的所有必要列都已分桶，你可以使用 Bucketed Map Join。由于分桶数据已经预先排序并按 hash 值落入每个桶，这种 JOIN 操作只需读取对应的桶，而无需扫描整个表。
• Sort Merge Join：如果两个表已经按连接键排序和分桶，Sort Merge Join 可以是一个更有效的选项。这种 JOIN 操作可以并行进行，并且在处理大规模数据时更具效率。
• Map-side Joins with Bloom Filters：这种优化策略使用布隆过滤器 (Bloom Filters)，它是一种空间效率极高的数据结构，可以快速判断一个元素是否在集合中。在 Hive 中，对大表使用布隆过滤器可以大大减少需要处理的数据量。
• 使用分区：如果表已经按照 JOIN 列进行了分区，那么在执行 JOIN 操作时只需要扫描相关的分区，而不是整个表。这可以大大节省计算和 I/O 成本。
• 减少数据的倾斜：数据倾斜是指某些键的数据量过大，导致处理这些键的 reducer 过载。你可以使用optimize.skewjoin 配置开启倾斜数据的优化。当开启后，Hive 会在执行过程中检测到倾斜的键，并在单独的 MapReduce Job 中处理这些键。
• 选择正确的 JOIN 顺序：在多表 JOIN 操作中，表的顺序可能影响性能。一般来说，你应该尽量使最大的表在 JOIN 操作的最后进行，因为 Hive 会在内存中缓存先前的表。

这些优化方法可以根据你的数据特性和查询需求进行选择和组合，以达到最佳性能。同时，你也应该对你的数据和 Hive 的行为有一个深入的理解，以方便你做出正确的优化决策。

Map Join

在 Hive 中，Map Join（也被称为 Map Side Join）是一种针对特定条件下的 JOIN 操作进行优化的策略。这种方法主要用于处理一个大表和一个小表之间的 JOIN 操作，其中小表的数据量足够小，可以被全部加载到内存中。

在执行 Map Join 时，Hive 会将小表的全部数据加载到内存中，并为每个键创建一个哈希表。然后，Hive 会读取大表的数据，并对每行使用内存中的哈希表进行查找，以找到匹配的 JOIN 键。由于这个操作可以在 Map 阶段完成，因此避免了复杂的 shuffle 和 reduce 过程，从而大大提高了 JOIN 操作的性能。

以下是一个 Map Join 的基本语法：

SELECT /*+ MAPJOIN(small_table) */ column1, column2
FROM big_table
JOIN small_table ON big_table.key = small_table.key;

在上述语句中，/*+ MAPJOIN(small_table) */ 是一个提示，指示 Hive 对 small_table 使用 Map Join。注意，这个提示只是一个建议，并不保证 Hive 一定会使用 Map Join。实际的执行计划取决于 Hive 的配置和数据的大小。

要使 Hive 自动决定何时使用 Map Join，你可以在设置中将 hive.auto.convert.join 设置为 true。

虽然 Map Join 可以提高性能，但是它也有一些限制。最重要的限制是小表的大小不能超过 Hive 配置中的 hive.mapjoin.smalltable.filesize 值。此外，如果小表的数据量过大，而内存空间不足，可能会导致 OutOfMemory 错误。

总的来说，Map Join 是一种强大的优化策略，可以在处理大表和小表之间的 JOIN 操作时大大提高性能。然而，它也需要我们对数据和内存使用有一定的理解，以避免潜在的问题。

Bucketed Map Join

Bucketed Map Join 是 Hive 中一种优化 JOIN 操作的策略，主要适用于已经被分桶的表。对数据进行分桶是一种优化数据读取的方式，它通过将数据分成更小、更容易管理的部分（称为“桶”）来实现。

在 Hive 中，可以在创建表时指定将数据分桶到一定数量的桶中，以及指定用于分桶的列。然后，Hive 会根据列的哈希值将数据分配到不同的桶。

当对已经按照 JOIN 列分桶的表进行 JOIN 操作时，Bucketed Map Join 可以发挥作用。它根据每个表的分桶情况，只需要读取对应的桶，而不需要读取整个表。这样，Hive 可以避免对大量不必要的数据进行扫描，从而大大提高了 JOIN 操作的性能。

以下是一个 Bucketed Map Join 的基本示例：

SET hive.optimize.bucketmapjoin = true;
SET hive.enforce.bucketmapjoin = true;

SELECT /*+ MAPJOIN(small_table) */ column1, column2
FROM big_table
JOIN small_table ON big_table.key = small_table.key;

在上述语句中，首先设置了 hive.optimize.bucketmapjoin 和 hive.enforce.bucketmapjoin 为 true，这样 Hive 会尝试对满足条件的 JOIN 操作使用 Bucketed Map Join。然后，使用了和 Map Join 相同的语法进行 JOIN，但是 Hive 会根据表的分桶情况来优化 JOIN 操作。

需要注意的是，Bucketed Map Join 的效果取决于数据的分桶情况。如果数据的分桶情况和 JOIN 的列不一致，或者分桶不均匀，可能会导致性能下降。因此，使用 Bucketed Map Join 需要对数据分布有一定的理解，并在创建表时进行正确的分桶。

Sort Merge Join

Sort Merge Join 是 Hive 中一种优化 JOIN 操作的策略，它在处理两个或多个大表 JOIN 时具有优势。在这种优化策略中，如果两个需要进行 JOIN 操作的表都已经根据 JOIN 的键进行了排序和分桶，那么就可以使用 Sort Merge Join。

在 Sort Merge Join 中，Hive 并不需要将所有数据加载到内存中，而是通过读取两个表的排序数据，并同时遍历这两个表来找出匹配的行，这大大减少了内存使用量。这使得 Sort Merge Join 在处理大规模数据时更具优势。

以下是启用 Sort Merge Join 的步骤：

首先，你需要在 Hive 配置中启用 Sort Merge Join，你可以通过以下设置来启用：

SET hive.optimize.sort.dynamic.partition=true;

然后，在你的 JOIN 查询中，确保两个表都已经根据 JOIN 的键进行了排序和分桶。

以下是一个 Sort Merge Join 的示例：

SELECT table1.column1, table2.column2
FROM table1
JOIN table2 ON table1.key = table2.key;

在使用 Sort Merge Join 时，需要注意的是，并不是所有情况下 Sort Merge Join 都是最优的选择。它主要适用于两个大表的 JOIN 操作，并且这两个表都已经根据 JOIN 的键进行了排序和分桶。如果表的大小差异很大，或者表没有进行排序和分桶，那么其他的 JOIN 策略（如 Map Join 或 Bucketed Map Join）可能会更有效。

Map-side Joins with Bloom Filters

Map-side Joins with Bloom Filters 是 Hive 中一种优化 JOIN 操作的策略。布隆过滤器（Bloom Filter）是一种空间效率极高的数据结构，用于测试一个元素是否是集合的成员。布隆过滤器允许存在一定的误报概率，也就是说，对于查询是否存在某元素，它可能会错误地返回“存在”，但绝不会错误地返回“不存在”。

在 Hive 中使用布隆过滤器进行 Map-side Join，主要是为了优化大表和小表之间的 JOIN 操作。这种优化策略的基础是，你可以创建一个布隆过滤器来表示小表中的 JOIN 键，然后将这个过滤器应用到大表上，以这种方式减少大表中需要处理的数据量。

在实际操作中，Hive 首先会在 Map 阶段读取小表，并为其 JOIN 键创建一个布隆过滤器。然后，Hive 会读取大表，并使用布隆过滤器来检查每行的 JOIN 键。只有当 JOIN 键存在于布隆过滤器中时，Hive 才会将这行数据发送到 Reduce 阶段进行 JOIN 操作。这样，大量不存在于小表中的数据将在 Map 阶段就被过滤掉，从而大大减少了进入 Reduce 阶段的数据量，并提高了 JOIN 操作的性能。

为了启用布隆过滤器，你需要在 Hive 配置中将 hive.optimize.bloomfilter.join 设置为 true。

虽然使用布隆过滤器可以大大提高 JOIN 操作的性能，但是也需要注意，布隆过滤器并不能保证 100% 的准确性。由于其允许误报的特性，有可能将一些实际上并不存在于小表中的数据发送到了 Reduce 阶段。此外，布隆过滤器的大小和效率取决于你为其分配的内存大小，如果内存分配过小，布隆过滤器的效率可能会降低。

Hive数据采样

Hive采用支持的采样方法：

随机采样

Hive 提供了 TABLESAMPLE（或 SAMPLE）关键字来进行随机采样。你可以通过指定采样的比例（如 TABLESAMPLE(0.1 PERCENT)）或者采样的行数（如 TABLESAMPLE(100 ROWS)）来进行采样。需要注意的是，这种采样方法并不保证结果的随机性或均匀性。

SELECT * FROM table
TABLESAMPLE(0.1 PERCENT);

或者

SELECT * FROM table
TABLESAMPLE(100 ROWS);

分区采样

在 Hive 中，可以通过 WHERE 子句和分区列来进行分区采样。例如，你可以通过 WHERE date=’2022-01-01′ 来只查询 2022 年 1 月 1 日的数据。

SELECT * FROM table
WHERE date='2022-01-01';

分桶采样

如果你的表已经进行了分桶，你可以通过 TABLESAMPLE(BUCKET x OUT OF y ON column) 语句来进行分桶采样。这种采样方法会选择第 x 个桶作为采样结果。

SELECT * FROM table
TABLESAMPLE(BUCKET 1 OUT OF 10 ON id);

块采样

Hive 还支持通过文件的块来进行采样。你可以通过 TABLESAMPLE(x PERCENT) 或 TABLESAMPLE(x BLOCKS) 语句来进行块采样。

这些采样方法可以帮助你在大量数据中快速获取一部分数据进行分析或调试。然而，需要注意的是，采样结果并不能完全代表全部数据，因此在使用采样结果进行决策时需要格外小心。

SELECT * FROM table
TABLESAMPLE(0.1 PERCENT);

或者

SELECT * FROM table
TABLESAMPLE(10 BLOCKS);

HiveQL 内置函数

数值函数

bround

bround 是一种常见的数值处理函数，该函数通常用于数据库和数据处理工具中，包括 Hive、Spark SQL、Pandas 等。它是 “banker’s rounding” 的缩写，也被称为 “half to even” rounding。

bround 函数的工作方式是，当一个数的小数部分刚好是 0.5 时，它会将这个数四舍五入到最近的偶数。例如，1.5 和 2.5 都会被四舍五入到 2，3.5 和 4.5 都会被四舍五入到 4。对于其它的小数部分，bround 函数的行为与常规的四舍五入函数相同。

以下是 Hive 中 bround 函数的使用示例：

SELECT bround(1.5);  — 结果是 2

SELECT bround(2.5);  — 结果也是 2

SELECT bround(1.6);  — 结果是 2

SELECT bround(1.4);  — 结果是 1

使用 bround 函数的好处是，它可以减少因四舍五入引入的误差。在财务和银行业务中，bround 函数通常被用于处理货币和金融数据，因为这些场景中对于精度的要求很高。

然而，需要注意的是，不是所有的数据库和数据处理工具都支持 bround 函数，在使用时需要检查你的工具是否支持这个函数。

集合函数

类型转换函数

日期/时间函数

条件函数

字符串函数

get_json_object

JSONPath 是一种用于提取和操作 JSON 数据的查询语言，类似于 XML 的 XPath。

在 Hive 中，可以使用 get_json_object 函数和 JSONPath 来提取 JSON 数据中的字段。下面是支持的一些基本 JSONPath 语法：

• $ : 表示 JSON 对象的根元素。
• . : 表示子对象操作符，用于访问子元素。
• [] : 表示数组的下标操作符，用于访问数组元素。
• : 表示数组的通配符，可以用于匹配数组中的任意元素。

然而，Hive 中的 JSONPath 支持是有限的，不支持一些复杂的语法，如：

• : : 零长度字符串作为键。
• .. : 递归下降。
• @ : 当前对象/元素。
• () : 脚本表达式。
• ?() : 过滤（脚本）表达式。
• , : 并集运算符。
• [start:end.step] : 数组切片运算符。

下面是一个示例，假设有一个名为 src_json 的表，该表只有一列（名为 json），并且只有一行数据。这行数据是一个 JSON 对象，包含了几个嵌套的字段和数组。你可以使用类似以下的查询来提取 JSON 数据中的字段：

SELECT get_json_object(src_json.json, '$.owner') FROM src_json;

这个查询会返回 “amy”，因为 $.owner 对应于 JSON 数据中的 “owner” 字段。

你还可以使用下标操作符 [] 来访问数组元素，如：

SELECT get_json_object(src_json.json, '$.store.fruit[0]') FROM src_json;

这个查询会返回 {“weight”:8,”type”:”apple”}，因为 $.store.fruit[0] 对应于 “fruit” 数组的第一个元素。

如果你试图访问一个不存在的键，get_json_object 函数会返回 NULL，如：

SELECT get_json_object(src_json.json, '$.non_exist_key') FROM src_json;

这个查询会返回 NULL，因为 JSON 数据中没有 “non_exist_key” 这个键。

Data Masking 函数

其他函数

聚合函数

表生成函数

HiveQL 窗口函数

窗口函数

窗口函数是一种在数据集的特定窗口（分组的子集）上执行计算的函数。窗口函数有助于解决一些需要对数据进行复杂转换的问题，例如计算移动平均或者获取每个分组的最大值。

以下是你提到的一些窗口函数的简单介绍：

• LEAD: LEAD 函数用于获取当前行的下一行（或往后几行）的值。例如，LEAD(column, 1) 可以获取当前行下一行的 column 列的值。如果没有下一行，LEAD 函数会返回 NULL。
• LAG: LAG 函数用于获取当前行的前一行（或往前几行）的值。例如，LAG(column, 1) 可以获取当前行前一行的 column 列的值。如果没有前一行，LAG 函数会返回 NULL。
• FIRST_VALUE: FIRST_VALUE 函数用于获取窗口内的第一行的值。例如，FIRST_VALUE(column) 可以获取窗口内第一行的 column 列的值。
• LAST_VALUE: LAST_VALUE 函数用于获取窗口内的最后一行的值。例如，LAST_VALUE(column) 可以获取窗口内最后一行的 column 列的值。

然而，需要注意的是，窗口函数的行为可能会受到窗口定义（包括分区、排序和窗口范围）的影响。在使用窗口函数时，你需要确保你理解了窗口的定义，以及它如何影响窗口函数的结果。

举例来说，以下是一个 SQL 查询示例，它使用 LEAD 函数来获取每个用户下一次购买的日期：

SELECT userId, purchaseDate,
       LEAD(purchaseDate, 1) OVER (PARTITION BY userId ORDER BY purchaseDate) as nextPurchaseDate
FROM purchases

在这个查询中，PARTITION BY 和 ORDER BY 语句定义了一个窗口，窗口按 userId 分区，并按 purchaseDate 排序。然后，LEAD 函数在这个窗口上执行，为每个用户的每次购买获取下一次购买的日期。

OVER子句

OVER子句在Hive SQL中用于定义窗口函数操作的窗口。窗口函数在输入的数据集的窗口（子集）上执行计算，而不是在整个数据集上执行。OVER子句可以用于指定窗口的分区和排序方式，以及窗口范围的起始和结束。

窗口定义中的组成部分：

• PARTITION BY：定义窗口的分区。对于具有相同PARTITION BY列值的行，它们将在同一个窗口内。类似于GROUP BY，但不会减少结果集的行数。
• ORDER BY：在每个窗口内对行进行排序。这对于一些窗口函数（如ROW_NUMBER、LEAD、LAG等）至关重要，它们的结果取决于行的顺序。
• ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW：定义窗口内的行范围。这个范围从分区的第一行开始，到当前行结束，包括当前行。

例子：

以下是一个使用OVER子句的示例，它使用RANK()函数为每个部门的员工按薪水进行排名：

SELECT department, employee, salary,
       RANK() OVER (PARTITION BY department ORDER BY salary DESC) as rank
FROM employees

在这个查询中，OVER子句定义了一个窗口，该窗口按department列分区，并在每个分区内按salary列的降序排序。然后，RANK()函数在这个窗口上执行，为每个部门的每个员工计算薪水的排名。

(ROWS | RANGE) BETWEEN (UNBOUNDED | [num]) PRECEDING AND ([num] PRECEDING | CURRENT ROW | (UNBOUNDED | [num]) FOLLOWING)
(ROWS | RANGE) BETWEEN CURRENT ROW AND (CURRENT ROW | (UNBOUNDED | [num]) FOLLOWING)
(ROWS | RANGE) BETWEEN [num] FOLLOWING AND (UNBOUNDED | [num]) FOLLOWING

这些是在 SQL 中使用窗口函数时定义窗口范围的方法。在使用窗口函数时，除了可以通过 PARTITION BY 和 ORDER BY 来定义窗口的分区和排序外，你还可以通过 ROWS BETWEEN 或 RANGE BETWEEN 来定义窗口内的行范围。

• ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW：这定义了一个从分区的开始到当前行的窗口。也就是说，窗口包含了从分区开始到（包括）当前行的所有行。
• ROWS BETWEEN [num] PRECEDING AND CURRENT ROW：这定义了一个从当前行前的 [num] 行到当前行的窗口。如果当前行前的行数少于 [num]，则窗口从分区开始。
• ROWS BETWEEN CURRENT ROW AND [num] FOLLOWING：这定义了一个从当前行到当前行后的 [num] 行的窗口。如果当前行后的行数少于 [num]，则窗口一直到分区结束。
• ROWS BETWEEN [num] FOLLOWING AND [num2] FOLLOWING：这定义了一个窗口，从当前行后的 [num] 行开始，到当前行后的 [num2] 行结束。如果当前行后的行数少于 [num] 或 [num2]，则窗口的开始或结束可能会在分区结束之后。

请注意，RANGE BETWEEN 和 ROWS BETWEEN 的行为可能会有所不同。RANGE BETWEEN 的行为取决于窗口的排序方式，它会包含所有具有与当前行相同排序值的行，而 ROWS BETWEEN 则严格按照物理行数来定义窗口。

分析函数

在 SQL 中，分析函数是用于复杂数据处理的一类函数，常常与窗口函数一起使用，例如：聚合、排序和窗口计算。以下是一些常用的分析函数：

• RANK()：为每行分配一个唯一的排名。如果两行具有相同的排序值，那么它们会获得相同的排名。RANK() 函数会在排序值相等的行之间留下“空隙”，这意味着如果有两行并列第一名，那么下一行将会排名为第三，而不是第二。
• ROW_NUMBER()：为每行分配一个唯一的编号。即使两行具有相同的排序值，它们也会获得不同的编号。
• DENSE_RANK()：与 RANK() 类似，为每行分配一个唯一的排名。但是，DENSE_RANK() 在排序值相等的行之间不会留下“空隙”。这意味着如果有两行并列第一名，那么下一行将会排名为第二，而不是第三。
• CUME_DIST()：计算给定行的累积分布。这是通过计算排序值小于或等于当前行的行数，然后除以总行数得到的。
• PERCENT_RANK()：计算相对于第一行的排名位置。这是通过计算 (RANK() – 1) / (总行数 – 1) 得到的。
• NTILE(num)：将窗口的行分为指定数量的组，每组包含相似数量的行。每一行都被分配到一个组，这个组的编号是从 1 到 num 。

以下是一个使用 RANK() 函数的 SQL 查询示例：

SELECT department, employee, salary,
       RANK() OVER (PARTITION BY department ORDER BY salary DESC) as rank
FROM employees

在这个查询中，RANK() 函数会为每个部门的每个员工计算薪水的排名。窗口是按 department 分区并按 salary 降序排序的。在每个部门内，薪水最高的员工的排名为 1，薪水第二高的员工的排名为 2，依此类推。如果两个员工的薪水相同，他们会获得相同的排名。

参考链接：LanguageManual – Apache Hive – Apache Software Foundation