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相关的配置参数:
Key | Default | Notes |
orc.compress | ZLIB | high level compression (one of NONE, ZLIB, SNAPPY) |
orc.compress.size | 262,144 | number of bytes in each compression chunk |
orc.stripe.size | 67,108,864 | number of bytes in each stripe |
orc.row.index.stride | 10,000 | number of rows between index entries (must be >= 1000) |
orc.create.index | true | whether to create row indexes |
orc.bloom.filter.columns | “” | comma separated list of column names for which bloom filter should be created |
orc.bloom.filter.fpp | 0.05 | false positive probability for bloom filter (must >0.0 and <1.0) |
其中用的较多的是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 内置函数
数值函数
Return Type | Name(Signature) | Description |
DOUBLE | round(DOUBLE a) | Returns the rounded BIGINT value of a. |
DOUBLE | round(DOUBLE a, INT d) | Returns a rounded to d decimal places. |
DOUBLE | bround(DOUBLE a) | Returns the rounded BIGINT value of a using HALF_EVEN rounding mode (as of Hive 1.3.0, 2.0.0). Also known as Gaussian rounding or bankers’ rounding. Example: bround(2.5) = 2, bround(3.5) = 4. |
DOUBLE | bround(DOUBLE a, INT d) | Returns a rounded to d decimal places using HALF_EVEN rounding mode (as of Hive 1.3.0, 2.0.0). Example: bround(8.25, 1) = 8.2, bround(8.35, 1) = 8.4. |
BIGINT | floor(DOUBLE a) | Returns the maximum BIGINT value that is equal to or less than a. |
BIGINT | ceil(DOUBLE a), ceiling(DOUBLE a) | Returns the minimum BIGINT value that is equal to or greater than a. |
DOUBLE | rand(), rand(INT seed) | Returns a random number (that changes from row to row) that is distributed uniformly from 0 to 1. Specifying the seed will make sure the generated random number sequence is deterministic. |
DOUBLE | exp(DOUBLE a), exp(DECIMAL a) | Returns ea where e is the base of the natural logarithm. Decimal version added in Hive 0.13.0. |
DOUBLE | ln(DOUBLE a), ln(DECIMAL a) | Returns the natural logarithm of the argument a. Decimal version added in Hive 0.13.0. |
DOUBLE | log10(DOUBLE a), log10(DECIMAL a) | Returns the base-10 logarithm of the argument a. Decimal version added in Hive 0.13.0. |
DOUBLE | log2(DOUBLE a), log2(DECIMAL a) | Returns the base-2 logarithm of the argument a. Decimal version added in Hive 0.13.0. |
DOUBLE | log(DOUBLE base, DOUBLE a)
log(DECIMAL base, DECIMAL a) |
Returns the base-base logarithm of the argument a. Decimal versions added in Hive 0.13.0. |
DOUBLE | pow(DOUBLE a, DOUBLE p), power(DOUBLE a, DOUBLE p) | Returns ap. |
DOUBLE | sqrt(DOUBLE a), sqrt(DECIMAL a) | Returns the square root of a. Decimal version added in Hive 0.13.0. |
STRING | bin(BIGINT a) | Returns the number in binary format (see http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_bin). |
STRING | hex(BIGINT a) hex(STRING a) hex(BINARY a) | If the argument is an INT or binary, hex returns the number as a STRING in hexadecimal format. Otherwise if the number is a STRING, it converts each character into its hexadecimal representation and returns the resulting STRING. (See http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_hex, BINARY version as of Hive 0.12.0.) |
BINARY | unhex(STRING a) | Inverse of hex. Interprets each pair of characters as a hexadecimal number and converts to the byte representation of the number. (BINARY version as of Hive 0.12.0, used to return a string.) |
STRING | conv(BIGINT num, INT from_base, INT to_base), conv(STRING num, INT from_base, INT to_base) | Converts a number from a given base to another (see http://dev.mysql.com/doc/refman/5.0/en/mathematical-functions.html#function_conv). |
DOUBLE | abs(DOUBLE a) | Returns the absolute value. |
INT or DOUBLE | pmod(INT a, INT b), pmod(DOUBLE a, DOUBLE b) | Returns the positive value of a mod b. |
DOUBLE | sin(DOUBLE a), sin(DECIMAL a) | Returns the sine of a (a is in radians). Decimal version added in Hive 0.13.0. |
DOUBLE | asin(DOUBLE a), asin(DECIMAL a) | Returns the arc sin of a if -1<=a<=1 or NULL otherwise. Decimal version added in Hive 0.13.0. |
DOUBLE | cos(DOUBLE a), cos(DECIMAL a) | Returns the cosine of a (a is in radians). Decimal version added in Hive 0.13.0. |
DOUBLE | acos(DOUBLE a), acos(DECIMAL a) | Returns the arccosine of a if -1<=a<=1 or NULL otherwise. Decimal version added in Hive 0.13.0. |
DOUBLE | tan(DOUBLE a), tan(DECIMAL a) | Returns the tangent of a (a is in radians). Decimal version added in Hive 0.13.0. |
DOUBLE | atan(DOUBLE a), atan(DECIMAL a) | Returns the arctangent of a. Decimal version added in Hive 0.13.0. |
DOUBLE | degrees(DOUBLE a), degrees(DECIMAL a) | Converts value of a from radians to degrees. Decimal version added in Hive 0.13.0. |
DOUBLE | radians(DOUBLE a), radians(DOUBLE a) | Converts value of a from degrees to radians. Decimal version added in Hive 0.13.0. |
INT or DOUBLE | positive(INT a), positive(DOUBLE a) | Returns a. |
INT or DOUBLE | negative(INT a), negative(DOUBLE a) | Returns -a. |
DOUBLE or INT | sign(DOUBLE a), sign(DECIMAL a) | Returns the sign of a as ‘1.0’ (if a is positive) or ‘-1.0’ (if a is negative), ‘0.0’ otherwise. The decimal version returns INT instead of DOUBLE. Decimal version added in Hive 0.13.0. |
DOUBLE | e() | Returns the value of e. |
DOUBLE | pi() | Returns the value of pi. |
BIGINT | factorial(INT a) | Returns the factorial of a (as of Hive 1.2.0). Valid a is [0..20]. |
DOUBLE | cbrt(DOUBLE a) | Returns the cube root of a double value (as of Hive 1.2.0). |
INT
BIGINT |
shiftleft(TINYINT|SMALLINT|INT a, INT b)
shiftleft(BIGINT a, INT b) |
Bitwise left shift (as of Hive 1.2.0). Shifts a b positions to the left.
Returns int for tinyint, smallint and int a. Returns bigint for bigint a. |
INT
BIGINT |
shiftright(TINYINT|SMALLINT|INT a, INT b)
shiftright(BIGINT a, INT b) |
Bitwise right shift (as of Hive 1.2.0). Shifts a b positions to the right.
Returns int for tinyint, smallint and int a. Returns bigint for bigint a. |
INT
BIGINT |
shiftrightunsigned(TINYINT|SMALLINT|INT a, INT b),
shiftrightunsigned(BIGINT a, INT b) |
Bitwise unsigned right shift (as of Hive 1.2.0). Shifts a b positions to the right.
Returns int for tinyint, smallint and int a. Returns bigint for bigint a. |
T | greatest(T v1, T v2, …) | Returns the greatest value of the list of values (as of Hive 1.1.0). Fixed to return NULL when one or more arguments are NULL, and strict type restriction relaxed, consistent with “>” operator (as of Hive 2.0.0). |
T | least(T v1, T v2, …) | Returns the least value of the list of values (as of Hive 1.1.0). Fixed to return NULL when one or more arguments are NULL, and strict type restriction relaxed, consistent with “<” operator (as of Hive 2.0.0). |
INT | width_bucket(NUMERIC expr, NUMERIC min_value, NUMERIC max_value, INT num_buckets) | Returns an integer between 0 and num_buckets+1 by mapping expr into the ith equally sized bucket. Buckets are made by dividing [min_value, max_value] into equally sized regions. If expr < min_value, return 1, if expr > max_value return num_buckets+1. See https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions214.htm (as of Hive 3.0.0) |
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 函数,在使用时需要检查你的工具是否支持这个函数。
集合函数
Return Type | Name(Signature) | Description |
int | size(Map<K.V>) | Returns the number of elements in the map type. |
int | size(Array<T>) | Returns the number of elements in the array type. |
array<K> | map_keys(Map<K.V>) | Returns an unordered array containing the keys of the input map. |
array<V> | map_values(Map<K.V>) | Returns an unordered array containing the values of the input map. |
boolean | array_contains(Array<T>, value) | Returns TRUE if the array contains value. |
array<t> | sort_array(Array<T>) | Sorts the input array in ascending order according to the natural ordering of the array elements and returns it (as of version 0.9.0). |
类型转换函数
Return Type | Name(Signature) | Description |
binary | binary(string|binary) | Casts the parameter into a binary. |
Expected “=” to follow “type” | cast(expr as <type>) | Converts the results of the expression expr to <type>. For example, cast(‘1’ as BIGINT) will convert the string ‘1’ to its integral representation. A null is returned if the conversion does not succeed. If cast(expr as boolean) Hive returns true for a non-empty string. |
日期/时间函数
Return Type | Name(Signature) | Description |
string | from_unixtime(bigint unixtime[, string pattern]) | Converts a number of seconds since epoch (1970-01-01 00:00:00 UTC) to a string representing the timestamp of that moment in the current time zone(using config “hive.local.time.zone”) using the specified pattern. If the pattern is missing the default is used (‘uuuu-MM-dd HH:mm:ss’ or yyyy-MM-dd HH:mm:ss’). Example: from_unixtime(0)=1970-01-01 00:00:00 (hive.local.time.zone=Etc/GMT)
As of Hive 4.0.0 (HIVE-25576), the “hive.datetime.formatter” property can be used to control the underlying formatter implementation, and as a consequence the accepted patterns and their behavior. Prior versions always used https://docs.oracle.com/javase/8/docs/api/java/text/SimpleDateFormat.html as the underlying formatter. |
bigint | unix_timestamp() | Gets current Unix timestamp in seconds. This function is not deterministic and its value is not fixed for the scope of a query execution, therefore prevents proper optimization of queries – this has been deprecated since 2.0 in favour of CURRENT_TIMESTAMP constant. |
bigint | unix_timestamp(string date) | Converts a datetime string to unix time (seconds since epoch) using the default pattern(s). The default accepted patterns depend on the underlying formatter implementation. The datetime string does not contain a timezone so the conversion uses the local time zone as specified by “hive.local.time.zone” property. Returns null when the conversion fails. Example: unix_timestamp(‘2009-03-20 11:30:01’) = 1237573801
As of Hive 4.0.0 (HIVE-25576), the “hive.datetime.formatter” property can be used to control the underlying formatter implementation, and as a consequence the accepted patterns and their behavior. Prior versions always used https://docs.oracle.com/javase/8/docs/api/java/text/SimpleDateFormat.html as the underlying formatter. |
bigint | unix_timestamp(string date, string pattern) | Converts a datetime string to unix time (seconds since epoch) using the specified pattern. The accepted patterns and their behavior depend on the underlying formatter implementation. Returns null when the conversion fails. Example: unix_timestamp(‘2009-03-20’, ‘uuuu-MM-dd’) = 1237532400
As of Hive 4.0.0 (HIVE-25576), the “hive.datetime.formatter” property can be used to control the underlying formatter implementation, and as a consequence the accepted patterns and their behavior. Prior versions always used https://docs.oracle.com/javase/8/docs/api/java/text/SimpleDateFormat.html as the underlying formatter. |
pre 2.1.0: string
2.1.0 on: date |
to_date(string timestamp) | Returns the date part of a timestamp string (pre-Hive 2.1.0): to_date(“1970-01-01 00:00:00”) = “1970-01-01”. As of Hive 2.1.0, returns a date object.
Prior to Hive 2.1.0 (HIVE-13248) the return type was a String because no Date type existed when the method was created. |
int | year(string date) | Returns the year part of a date or a timestamp string: year(“1970-01-01 00:00:00”) = 1970, year(“1970-01-01”) = 1970. |
int | quarter(date/timestamp/string) | Returns the quarter of the year for a date, timestamp, or string in the range 1 to 4 (as of Hive 1.3.0). Example: quarter(‘2015-04-08’) = 2. |
int | month(string date) | Returns the month part of a date or a timestamp string: month(“1970-11-01 00:00:00”) = 11, month(“1970-11-01”) = 11. |
int | day(string date) dayofmonth(date) | Returns the day part of a date or a timestamp string: day(“1970-11-01 00:00:00”) = 1, day(“1970-11-01”) = 1. |
int | hour(string date) | Returns the hour of the timestamp: hour(‘2009-07-30 12:58:59′) = 12, hour(’12:58:59’) = 12. |
int | minute(string date) | Returns the minute of the timestamp. |
int | second(string date) | Returns the second of the timestamp. |
int | weekofyear(string date) | Returns the week number of a timestamp string: weekofyear(“1970-11-01 00:00:00”) = 44, weekofyear(“1970-11-01”) = 44. |
int | extract(field FROM source) | Retrieve fields such as days or hours from source (as of Hive 2.2.0). Source must be a date, timestamp, interval or a string that can be converted into either a date or timestamp. Supported fields include: day, dayofweek, hour, minute, month, quarter, second, week and year.
Examples: select extract(month from “2016-10-20”) results in 10. select extract(hour from “2016-10-20 05:06:07”) results in 5. select extract(dayofweek from “2016-10-20 05:06:07”) results in 5. select extract(month from interval ‘1-3’ year to month) results in 3. select extract(minute from interval ‘3 12:20:30’ day to second) results in 20. |
int | datediff(string enddate, string startdate) | Returns the number of days from startdate to enddate: datediff(‘2009-03-01’, ‘2009-02-27’) = 2. |
pre 2.1.0: string
2.1.0 on: date |
date_add(date/timestamp/string startdate, tinyint/smallint/int days) | Adds a number of days to startdate: date_add(‘2008-12-31’, 1) = ‘2009-01-01’.
Prior to Hive 2.1.0 (HIVE-13248) the return type was a String because no Date type existed when the method was created. |
pre 2.1.0: string
2.1.0 on: date |
date_sub(date/timestamp/string startdate, tinyint/smallint/int days) | Subtracts a number of days to startdate: date_sub(‘2008-12-31’, 1) = ‘2008-12-30’.
Prior to Hive 2.1.0 (HIVE-13248) the return type was a String because no Date type existed when the method was created. |
timestamp | from_utc_timestamp({any primitive type} ts, string timezone) | Converts a timestamp* in UTC to a given timezone (as of Hive 0.8.0).
* timestamp is a primitive type, including timestamp/date, tinyint/smallint/int/bigint, float/double and decimal. Fractional values are considered as seconds. Integer values are considered as milliseconds. For example, from_utc_timestamp(2592000.0,’PST’), from_utc_timestamp(2592000000,’PST’) and from_utc_timestamp(timestamp ‘1970-01-30 16:00:00′,’PST’) all return the timestamp 1970-01-30 08:00:00. |
timestamp | to_utc_timestamp({any primitive type} ts, string timezone) | Converts a timestamp* in a given timezone to UTC (as of Hive 0.8.0).
* timestamp is a primitive type, including timestamp/date, tinyint/smallint/int/bigint, float/double and decimal. Fractional values are considered as seconds. Integer values are considered as milliseconds. For example, to_utc_timestamp(2592000.0,’PST’), to_utc_timestamp(2592000000,’PST’) and to_utc_timestamp(timestamp ‘1970-01-30 16:00:00′,’PST’) all return the timestamp 1970-01-31 00:00:00. |
date | current_date | Returns the current date at the start of query evaluation (as of Hive 1.2.0). All calls of current_date within the same query return the same value. |
timestamp | current_timestamp | Returns the current timestamp at the start of query evaluation (as of Hive 1.2.0). All calls of current_timestamp within the same query return the same value. |
string | add_months(string start_date, int num_months, output_date_format) | Returns the date that is num_months after start_date (as of Hive 1.1.0). start_date is a string, date or timestamp. num_months is an integer. If start_date is the last day of the month or if the resulting month has fewer days than the day component of start_date, then the result is the last day of the resulting month. Otherwise, the result has the same day component as start_date. The default output format is ‘yyyy-MM-dd’.
Before Hive 4.0.0, the time part of the date is ignored. As of Hive 4.0.0, add_months supports an optional argument output_date_format, which accepts a String that represents a valid date format for the output. This allows to retain the time format in the output. For example : add_months(‘2009-08-31’, 1) returns ‘2009-09-30’. |
string | last_day(string date) | Returns the last day of the month which the date belongs to (as of Hive 1.1.0). date is a string in the format ‘yyyy-MM-dd HH:mm:ss’ or ‘yyyy-MM-dd’. The time part of date is ignored. |
string | next_day(string start_date, string day_of_week) | Returns the first date which is later than start_date and named as day_of_week (as of Hive 1.2.0). start_date is a string/date/timestamp. day_of_week is 2 letters, 3 letters or full name of the day of the week (e.g. Mo, tue, FRIDAY). The time part of start_date is ignored. Example: next_day(‘2015-01-14’, ‘TU’) = 2015-01-20. |
string | trunc(string date, string format) | Returns date truncated to the unit specified by the format (as of Hive 1.2.0). Supported formats: MONTH/MON/MM, YEAR/YYYY/YY. Example: trunc(‘2015-03-17’, ‘MM’) = 2015-03-01. |
double | months_between(date1, date2) | Returns number of months between dates date1 and date2 (as of Hive 1.2.0). If date1 is later than date2, then the result is positive. If date1 is earlier than date2, then the result is negative. If date1 and date2 are either the same days of the month or both last days of months, then the result is always an integer. Otherwise the UDF calculates the fractional portion of the result based on a 31-day month and considers the difference in time components date1 and date2. date1 and date2 type can be date, timestamp or string in the format ‘yyyy-MM-dd’ or ‘yyyy-MM-dd HH:mm:ss’. The result is rounded to 8 decimal places. Example: months_between(‘1997-02-28 10:30:00’, ‘1996-10-30’) = 3.94959677 |
string | date_format(date/timestamp/string ts, string pattern) | Converts a date/timestamp/string to a value of string using the specified pattern (as of Hive 1.2.0). The accepted patterns and their behavior depend on the underlying formatter implementation. The pattern argument should be constant. Example: date_format(‘2015-04-08’, ‘y’) = ‘2015’.
date_format can be used to implement other UDFs, e.g.: dayname(date) is date_format(date, ‘EEEE’) dayofyear(date) is date_format(date, ‘D’) As of Hive 4.0.0 ( HIVE-27673 – Configurable datetime formatter for date_format CLOSED ), the “hive.datetime.formatter” property can be used to control the underlying formatter implementation, and as a consequence the accepted patterns and their behavior. Prior versions always used https://docs.oracle.com/javase/8/docs/api/java/text/SimpleDateFormat.html as the underlying formatter. |
条件函数
Return Type | Name(Signature) | Description |
T | if(boolean testCondition, T valueTrue, T valueFalseOrNull) | Returns valueTrue when testCondition is true, returns valueFalseOrNull otherwise. |
boolean | isnull( a ) | Returns true if a is NULL and false otherwise. |
boolean | isnotnull ( a ) | Returns true if a is not NULL and false otherwise. |
T | nvl(T value, T default_value) | Returns default value if value is null else returns value (as of HIve 0.11). |
T | COALESCE(T v1, T v2, …) | Returns the first v that is not NULL, or NULL if all v’s are NULL. |
T | CASE a WHEN b THEN c [WHEN d THEN e]* [ELSE f] END | When a = b, returns c; when a = d, returns e; else returns f. |
T | CASE WHEN a THEN b [WHEN c THEN d]* [ELSE e] END | When a = true, returns b; when c = true, returns d; else returns e. |
T | nullif( a, b ) | Returns NULL if a=b; otherwise returns a (as of Hive 2.3.0).
Shorthand for: CASE WHEN a = b then NULL else a |
void | assert_true(boolean condition) | Throw an exception if ‘condition’ is not true, otherwise return null (as of Hive 0.8.0). For example, select assert_true (2<1). |
字符串函数
Return Type | Name(Signature) | Description | ||||||||
int | ascii(string str) | Returns the numeric value of the first character of str. | ||||||||
string | base64(binary bin) | Converts the argument from binary to a base 64 string (as of Hive 0.12.0). | ||||||||
int | character_length(string str) | Returns the number of UTF-8 characters contained in str (as of Hive 2.2.0). The function char_length is shorthand for this function. | ||||||||
string | chr(bigint|double A) | Returns the ASCII character having the binary equivalent to A (as of Hive 1.3.0 and 2.1.0). If A is larger than 256 the result is equivalent to chr(A % 256). Example: select chr(88); returns “X”. | ||||||||
string | concat(string|binary A, string|binary B…) | Returns the string or bytes resulting from concatenating the strings or bytes passed in as parameters in order. For example, concat(‘foo’, ‘bar’) results in ‘foobar’. Note that this function can take any number of input strings. | ||||||||
array<struct<string,double>> | context_ngrams(array<array<string>>, array<string>, int K, int pf) | Returns the top-k contextual N-grams from a set of tokenized sentences, given a string of “context”. See StatisticsAndDataMining for more information. | ||||||||
string | concat_ws(string SEP, string A, string B…) | Like concat() above, but with custom separator SEP. | ||||||||
string | concat_ws(string SEP, array<string>) | Like concat_ws() above, but taking an array of strings. (as of Hive 0.9.0) | ||||||||
string | decode(binary bin, string charset) | Decodes the first argument into a String using the provided character set (one of ‘US-ASCII’, ‘ISO-8859-1’, ‘UTF-8’, ‘UTF-16BE’, ‘UTF-16LE’, ‘UTF-16’). If either argument is null, the result will also be null. (As of Hive 0.12.0.) | ||||||||
string | elt(N int,str1 string,str2 string,str3 string,…) | Return string at index number. For example elt(2,’hello’,’world’) returns ‘world’. Returns NULL if N is less than 1 or greater than the number of arguments.
(see https://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_elt) |
||||||||
binary | encode(string src, string charset) | Encodes the first argument into a BINARY using the provided character set (one of ‘US-ASCII’, ‘ISO-8859-1’, ‘UTF-8’, ‘UTF-16BE’, ‘UTF-16LE’, ‘UTF-16’). If either argument is null, the result will also be null. (As of Hive 0.12.0.) | ||||||||
int | field(val T,val1 T,val2 T,val3 T,…) | Returns the index of val in the val1,val2,val3,… list or 0 if not found. For example field(‘world’,’say’,’hello’,’world’) returns 3. All primitive types are supported, arguments are compared using str.equals(x). If val is NULL, the return value is 0. (see https://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_field) |
||||||||
int | find_in_set(string str, string strList) | Returns the first occurance of str in strList where strList is a comma-delimited string. Returns null if either argument is null. Returns 0 if the first argument contains any commas. For example, find_in_set(‘ab’, ‘abc,b,ab,c,def’) returns 3. | ||||||||
string | format_number(number x, int d) | Formats the number X to a format like ‘#,###,###.##’, rounded to D decimal places, and returns the result as a string. If D is 0, the result has no decimal point or fractional part. (As of Hive 0.10.0; bug with float types fixed in Hive 0.14.0, decimal type support added in Hive 0.14.0) | ||||||||
string | get_json_object(string json_string, string path) | Extracts json object from a json string based on json path specified, and returns json string of the extracted json object. It will return null if the input json string is invalid. NOTE: The json path can only have the characters [0-9a-z_], i.e., no upper-case or special characters. Also, the keys *cannot start with numbers.* This is due to restrictions on Hive column names. | ||||||||
boolean | in_file(string str, string filename) | Returns true if the string str appears as an entire line in filename. | ||||||||
int | instr(string str, string substr) | Returns the position of the first occurrence of substr in str. Returns null if either of the arguments are null and returns 0 if substr could not be found in str. Be aware that this is not zero based. The first character in str has index 1. | ||||||||
int | length(string A) | Returns the length of the string. | ||||||||
int | locate(string substr, string str[, int pos]) | Returns the position of the first occurrence of substr in str after position pos. | ||||||||
string | lower(string A) lcase(string A) | Returns the string resulting from converting all characters of B to lower case. For example, lower(‘fOoBaR’) results in ‘foobar’. | ||||||||
string | lpad(string str, int len, string pad) | Returns str, left-padded with pad to a length of len. If str is longer than len, the return value is shortened to len characters. In case of empty pad string, the return value is null. | ||||||||
string | ltrim(string A) | Returns the string resulting from trimming spaces from the beginning(left hand side) of A. For example, ltrim(‘ foobar ‘) results in ‘foobar ‘. | ||||||||
array<struct<string,double>> | ngrams(array<array<string>>, int N, int K, int pf) | Returns the top-k N-grams from a set of tokenized sentences, such as those returned by the sentences() UDAF. See StatisticsAndDataMining for more information. | ||||||||
int | octet_length(string str) | Returns the number of octets required to hold the string str in UTF-8 encoding (since Hive 2.2.0). Note that octet_length(str) can be larger than character_length(str). | ||||||||
string | parse_url(string urlString, string partToExtract [, string keyToExtract]) | Returns the specified part from the URL. Valid values for partToExtract include HOST, PATH, QUERY, REF, PROTOCOL, AUTHORITY, FILE, and USERINFO. For example, parse_url(‘http://facebook.com/path1/p.php?k1=v1&k2=v2#Ref1’, ‘HOST’) returns ‘facebook.com’. Also a value of a particular key in QUERY can be extracted by providing the key as the third argument, for example, parse_url(‘http://facebook.com/path1/p.php?k1=v1&k2=v2#Ref1’, ‘QUERY’, ‘k1’) returns ‘v1’. | ||||||||
string | printf(String format, Obj… args) | Returns the input formatted according do printf-style format strings (as of Hive 0.9.0). | ||||||||
string | quote(String text) | Returns the quoted string (Includes escape character for any single quotes HIVE-4.0.0)
|
||||||||
string | regexp_extract(string subject, string pattern, int index) | Returns the string extracted using the pattern. For example, regexp_extract(‘foothebar’, ‘foo(.*?)(bar)’, 2) returns ‘bar.’ Note that some care is necessary in using predefined character classes: using ‘\s’ as the second argument will match the letter s; ‘\\s’ is necessary to match whitespace, etc. The ‘index’ parameter is the Java regex Matcher group() method index. See docs/api/java/util/regex/Matcher.html for more information on the ‘index’ or Java regex group() method. | ||||||||
string | regexp_replace(string INITIAL_STRING, string PATTERN, string REPLACEMENT) | Returns the string resulting from replacing all substrings in INITIAL_STRING that match the java regular expression syntax defined in PATTERN with instances of REPLACEMENT. For example, regexp_replace(“foobar”, “oo|ar”, “”) returns ‘fb.’ Note that some care is necessary in using predefined character classes: using ‘\s’ as the second argument will match the letter s; ‘\\s’ is necessary to match whitespace, etc. | ||||||||
string | repeat(string str, int n) | Repeats str n times. | ||||||||
string | replace(string A, string OLD, string NEW) | Returns the string A with all non-overlapping occurrences of OLD replaced with NEW (as of Hive 1.3.0 and 2.1.0). Example: select replace(“ababab”, “abab”, “Z”); returns “Zab”. | ||||||||
string | reverse(string A) | Returns the reversed string. | ||||||||
string | rpad(string str, int len, string pad) | Returns str, right-padded with pad to a length of len. If str is longer than len, the return value is shortened to len characters. In case of empty pad string, the return value is null. | ||||||||
string | rtrim(string A) | Returns the string resulting from trimming spaces from the end(right hand side) of A. For example, rtrim(‘ foobar ‘) results in ‘ foobar’. | ||||||||
array<array<string>> | sentences(string str, string lang, string locale) | Tokenizes a string of natural language text into words and sentences, where each sentence is broken at the appropriate sentence boundary and returned as an array of words. The ‘lang’ and ‘locale’ are optional arguments. For example, sentences(‘Hello there! How are you?’) returns ( (“Hello”, “there”), (“How”, “are”, “you”) ). | ||||||||
string | space(int n) | Returns a string of n spaces. | ||||||||
array | split(string str, string pat) | Splits str around pat (pat is a regular expression). | ||||||||
map<string,string> | str_to_map(text[, delimiter1, delimiter2]) | Splits text into key-value pairs using two delimiters. Delimiter1 separates text into K-V pairs, and Delimiter2 splits each K-V pair. Default delimiters are ‘,’ for delimiter1 and ‘:’ for delimiter2. | ||||||||
string | substr(string|binary A, int start) substring(string|binary A, int start) | Returns the substring or slice of the byte array of A starting from start position till the end of string A. For example, substr(‘foobar’, 4) results in ‘bar’ (see [http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_substr]). | ||||||||
string | substr(string|binary A, int start, int len) substring(string|binary A, int start, int len) | Returns the substring or slice of the byte array of A starting from start position with length len. For example, substr(‘foobar’, 4, 1) results in ‘b’ (see [http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_substr]). | ||||||||
string | substring_index(string A, string delim, int count) | Returns the substring from string A before count occurrences of the delimiter delim (as of Hive 1.3.0). If count is positive, everything to the left of the final delimiter (counting from the left) is returned. If count is negative, everything to the right of the final delimiter (counting from the right) is returned. Substring_index performs a case-sensitive match when searching for delim. Example: substring_index(‘www.apache.org’, ‘.’, 2) = ‘www.apache’. | ||||||||
string | translate(string|char|varchar input, string|char|varchar from, string|char|varchar to) | Translates the input string by replacing the characters present in the from string with the corresponding characters in the to string. This is similar to the translate function in PostgreSQL. If any of the parameters to this UDF are NULL, the result is NULL as well. (Available as of Hive 0.10.0, for string types)
Char/varchar support added as of Hive 0.14.0. |
||||||||
string | trim(string A) | Returns the string resulting from trimming spaces from both ends of A. For example, trim(‘ foobar ‘) results in ‘foobar’ | ||||||||
binary | unbase64(string str) | Converts the argument from a base 64 string to BINARY. (As of Hive 0.12.0.) | ||||||||
string | upper(string A) ucase(string A) | Returns the string resulting from converting all characters of A to upper case. For example, upper(‘fOoBaR’) results in ‘FOOBAR’. | ||||||||
string | initcap(string A) | Returns string, with the first letter of each word in uppercase, all other letters in lowercase. Words are delimited by whitespace. (As of Hive 1.1.0.) | ||||||||
int | levenshtein(string A, string B) | Returns the Levenshtein distance between two strings (as of Hive 1.2.0). For example, levenshtein(‘kitten’, ‘sitting’) results in 3. | ||||||||
string | soundex(string A) | Returns soundex code of the string (as of Hive 1.2.0). For example, soundex(‘Miller’) results in M460. |
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 函数
Return Type | Name(Signature) | Description |
string | mask(string str[, string upper[, string lower[, string number]]]) | Returns a masked version of str (as of Hive 2.1.0). By default, upper case letters are converted to “X”, lower case letters are converted to “x” and numbers are converted to “n”. For example mask(“abcd-EFGH-8765-4321”) results in xxxx-XXXX-nnnn-nnnn. You can override the characters used in the mask by supplying additional arguments: the second argument controls the mask character for upper case letters, the third argument for lower case letters and the fourth argument for numbers. For example, mask(“abcd-EFGH-8765-4321”, “U”, “l”, “#”) results in llll-UUUU-####-####. |
string | mask_first_n(string str[, int n]) | Returns a masked version of str with the first n values masked (as of Hive 2.1.0). Upper case letters are converted to “X”, lower case letters are converted to “x” and numbers are converted to “n”. For example, mask_first_n(“1234-5678-8765-4321”, 4) results in nnnn-5678-8765-4321. |
string | mask_last_n(string str[, int n]) | Returns a masked version of str with the last n values masked (as of Hive 2.1.0). Upper case letters are converted to “X”, lower case letters are converted to “x” and numbers are converted to “n”. For example, mask_last_n(“1234-5678-8765-4321”, 4) results in 1234-5678-8765-nnnn. |
string | mask_show_first_n(string str[, int n]) | Returns a masked version of str, showing the first n characters unmasked (as of Hive 2.1.0). Upper case letters are converted to “X”, lower case letters are converted to “x” and numbers are converted to “n”. For example, mask_show_first_n(“1234-5678-8765-4321”, 4) results in 1234-nnnn-nnnn-nnnn. |
string | mask_show_last_n(string str[, int n]) | Returns a masked version of str, showing the last n characters unmasked (as of Hive 2.1.0). Upper case letters are converted to “X”, lower case letters are converted to “x” and numbers are converted to “n”. For example, mask_show_last_n(“1234-5678-8765-4321”, 4) results in nnnn-nnnn-nnnn-4321. |
string | mask_hash(string|char|varchar str) | Returns a hashed value based on str (as of Hive 2.1.0). The hash is consistent and can be used to join masked values together across tables. This function returns null for non-string types. |
其他函数
Return Type | Name(Signature) | Description |
varies | java_method(class, method[, arg1[, arg2..]]) | Synonym for reflect. (As of Hive 0.9.0.) |
varies | reflect(class, method[, arg1[, arg2..]]) | Calls a Java method by matching the argument signature, using reflection. (As of Hive 0.7.0.) See Reflect (Generic) UDF for examples. |
int | hash(a1[, a2…]) | Returns a hash value of the arguments. (As of Hive 0.4.) |
string | current_user() | Returns current user name from the configured authenticator manager (as of Hive 1.2.0). Could be the same as the user provided when connecting, but with some authentication managers (for example HadoopDefaultAuthenticator) it could be different. |
string | logged_in_user() | Returns current user name from the session state (as of Hive 2.2.0). This is the username provided when connecting to Hive. |
string | current_database() | Returns current database name (as of Hive 0.13.0). |
string | md5(string/binary) | Calculates an MD5 128-bit checksum for the string or binary (as of Hive 1.3.0). The value is returned as a string of 32 hex digits, or NULL if the argument was NULL. Example: md5(‘ABC’) = ‘902fbdd2b1df0c4f70b4a5d23525e932’. |
string | sha1(string/binary)
sha(string/binary) |
Calculates the SHA-1 digest for string or binary and returns the value as a hex string (as of Hive 1.3.0). Example: sha1(‘ABC’) = ‘3c01bdbb26f358bab27f267924aa2c9a03fcfdb8’. |
bigint | crc32(string/binary) | Computes a cyclic redundancy check value for string or binary argument and returns bigint value (as of Hive 1.3.0). Example: crc32(‘ABC’) = 2743272264. |
string | sha2(string/binary, int) | Calculates the SHA-2 family of hash functions (SHA-224, SHA-256, SHA-384, and SHA-512) (as of Hive 1.3.0). The first argument is the string or binary to be hashed. The second argument indicates the desired bit length of the result, which must have a value of 224, 256, 384, 512, or 0 (which is equivalent to 256). SHA-224 is supported starting from Java 8. If either argument is NULL or the hash length is not one of the permitted values, the return value is NULL. Example: sha2(‘ABC’, 256) = ‘b5d4045c3f466fa91fe2cc6abe79232a1a57cdf104f7a26e716e0a1e2789df78’. |
binary | aes_encrypt(input string/binary, key string/binary) | Encrypt input using AES (as of Hive 1.3.0). Key lengths of 128, 192 or 256 bits can be used. 192 and 256 bits keys can be used if Java Cryptography Extension (JCE) Unlimited Strength Jurisdiction Policy Files are installed. If either argument is NULL or the key length is not one of the permitted values, the return value is NULL. Example: base64(aes_encrypt(‘ABC’, ‘1234567890123456’)) = ‘y6Ss+zCYObpCbgfWfyNWTw==’. |
binary | aes_decrypt(input binary, key string/binary) | Decrypt input using AES (as of Hive 1.3.0). Key lengths of 128, 192 or 256 bits can be used. 192 and 256 bits keys can be used if Java Cryptography Extension (JCE) Unlimited Strength Jurisdiction Policy Files are installed. If either argument is NULL or the key length is not one of the permitted values, the return value is NULL. Example: aes_decrypt(unbase64(‘y6Ss+zCYObpCbgfWfyNWTw==’), ‘1234567890123456’) = ‘ABC’. |
string | version() | Returns the Hive version (as of Hive 2.1.0). The string contains 2 fields, the first being a build number and the second being a build hash. Example: “select version();” might return “2.1.0.2.5.0.0-1245 r027527b9c5ce1a3d7d0b6d2e6de2378fb0c39232”. Actual results will depend on your build. |
bigint | surrogate_key([write_id_bits, task_id_bits]) | Automatically generate numerical Ids for rows as you enter data into a table. Can only be used as default value for acid or insert-only tables. |
聚合函数
Return Type | Name(Signature) | Description |
BIGINT | count(*), count(expr), count(DISTINCT expr[, expr…]) | count(*) – Returns the total number of retrieved rows, including rows containing NULL values.
count(expr) – Returns the number of rows for which the supplied expression is non-NULL. count(DISTINCT expr[, expr]) – Returns the number of rows for which the supplied expression(s) are unique and non-NULL. Execution of this can be optimized with hive.optimize.distinct.rewrite. |
DOUBLE | sum(col), sum(DISTINCT col) | Returns the sum of the elements in the group or the sum of the distinct values of the column in the group. |
DOUBLE | avg(col), avg(DISTINCT col) | Returns the average of the elements in the group or the average of the distinct values of the column in the group. |
DOUBLE | min(col) | Returns the minimum of the column in the group. |
DOUBLE | max(col) | Returns the maximum value of the column in the group. |
DOUBLE | variance(col), var_pop(col) | Returns the variance of a numeric column in the group. |
DOUBLE | var_samp(col) | Returns the unbiased sample variance of a numeric column in the group. |
DOUBLE | stddev_pop(col) | Returns the standard deviation of a numeric column in the group. |
DOUBLE | stddev_samp(col) | Returns the unbiased sample standard deviation of a numeric column in the group. |
DOUBLE | covar_pop(col1, col2) | Returns the population covariance of a pair of numeric columns in the group. |
DOUBLE | covar_samp(col1, col2) | Returns the sample covariance of a pair of a numeric columns in the group. |
DOUBLE | corr(col1, col2) | Returns the Pearson coefficient of correlation of a pair of a numeric columns in the group. |
DOUBLE | percentile(BIGINT col, p) | Returns the exact pth percentile of a column in the group (does not work with floating point types). p must be between 0 and 1. NOTE: A true percentile can only be computed for integer values. Use PERCENTILE_APPROX if your input is non-integral. |
array<double> | percentile(BIGINT col, array(p1 [, p2]…)) | Returns the exact percentiles p1, p2, … of a column in the group (does not work with floating point types). pi must be between 0 and 1. NOTE: A true percentile can only be computed for integer values. Use PERCENTILE_APPROX if your input is non-integral. |
DOUBLE | percentile_approx(DOUBLE col, p [, B]) | Returns an approximate pth percentile of a numeric column (including floating point types) in the group. The B parameter controls approximation accuracy at the cost of memory. Higher values yield better approximations, and the default is 10,000. When the number of distinct values in col is smaller than B, this gives an exact percentile value. |
array<double> | percentile_approx(DOUBLE col, array(p1 [, p2]…) [, B]) | Same as above, but accepts and returns an array of percentile values instead of a single one. |
double | regr_avgx(independent, dependent) | Equivalent to avg(dependent). As of Hive 2.2.0. |
double | regr_avgy(independent, dependent) | Equivalent to avg(independent). As of Hive 2.2.0. |
double | regr_count(independent, dependent) | Returns the number of non-null pairs used to fit the linear regression line. As of Hive 2.2.0. |
double | regr_intercept(independent, dependent) | Returns the y-intercept of the linear regression line, i.e. the value of b in the equation dependent = a * independent + b. As of Hive 2.2.0. |
double | regr_r2(independent, dependent) | Returns the coefficient of determination for the regression. As of Hive 2.2.0. |
double | regr_slope(independent, dependent) | Returns the slope of the linear regression line, i.e. the value of a in the equation dependent = a * independent + b. As of Hive 2.2.0. |
double | regr_sxx(independent, dependent) | Equivalent to regr_count(independent, dependent) * var_pop(dependent). As of Hive 2.2.0. |
double | regr_sxy(independent, dependent) | Equivalent to regr_count(independent, dependent) * covar_pop(independent, dependent). As of Hive 2.2.0. |
double | regr_syy(independent, dependent) | Equivalent to regr_count(independent, dependent) * var_pop(independent). As of Hive 2.2.0. |
array<struct {‘x’,’y’}> | histogram_numeric(col, b) | Computes a histogram of a numeric column in the group using b non-uniformly spaced bins. The output is an array of size b of double-valued (x,y) coordinates that represent the bin centers and heights |
array | collect_set(col) | Returns a set of objects with duplicate elements eliminated. |
array | collect_list(col) | Returns a list of objects with duplicates. (As of Hive 0.13.0.) |
INTEGER | ntile(INTEGER x) | Divides an ordered partition into x groups called buckets and assigns a bucket number to each row in the partition. This allows easy calculation of tertiles, quartiles, deciles, percentiles and other common summary statistics. (As of Hive 0.11.0.) |
表生成函数
Row-set columns types | Name(Signature) | Description |
T | explode(ARRAY<T> a) | Explodes an array to multiple rows. Returns a row-set with a single column (col), one row for each element from the array. |
Tkey,Tvalue | explode(MAP<Tkey,Tvalue> m) | Explodes a map to multiple rows. Returns a row-set with a two columns (key,value) , one row for each key-value pair from the input map. (As of Hive 0.8.0.). |
int,T | posexplode(ARRAY<T> a) | Explodes an array to multiple rows with additional positional column of int type (position of items in the original array, starting with 0). Returns a row-set with two columns (pos,val), one row for each element from the array. |
T1,…,Tn | inline(ARRAY<STRUCT<f1:T1,…,fn:Tn>> a) | Explodes an array of structs to multiple rows. Returns a row-set with N columns (N = number of top level elements in the struct), one row per struct from the array. (As of Hive 0.10.) |
T1,…,Tn/r | stack(int r,T1 V1,…,Tn/r Vn) | Breaks up n values V1,…,Vn into r rows. Each row will have n/r columns. r must be constant. |
string1,…,stringn | json_tuple(string jsonStr,string k1,…,string kn) | Takes JSON string and a set of n keys, and returns a tuple of n values. This is a more efficient version of the get_json_object UDF because it can get multiple keys with just one call. |
string 1,…,stringn | parse_url_tuple(string urlStr,string p1,…,string pn) | Takes URL string and a set of n URL parts, and returns a tuple of n values. This is similar to the parse_url() UDF but can extract multiple parts at once out of a URL. Valid part names are: HOST, PATH, QUERY, REF, PROTOCOL, AUTHORITY, FILE, USERINFO, QUERY:<KEY>. |
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