Hive Tutorial

Concepts

什么是配置单元

Hive 是基于Apache Hadoop的数据仓库基础架构。 Hadoop 为商品硬件上的数据存储和处理提供了大规模的横向扩展和容错功能。

Hive 旨在简化数据汇总，临时查询和分析大量数据的过程。它提供了 SQL，使用户可以轻松地进行即席查询，摘要和数据分析。同时，Hive 的 SQL 为用户提供了多个位置，以集成他们自己的功能以进行自定义分析，例如用户定义函数(UDF)。

什么不是配置单元

Hive 不适用于在线 Transaction 处理。最好用于传统的数据仓库任务。

Getting Started

有关设置 Hive，HiveServer2 和 Beeline 的详细信息，请参阅GettingStarted指南。

关于 Hive 的书列出了一些可能对 Hive 入门也很有帮助的书。

在以下各节中，我们提供有关系统功能的教程。我们首先描述数据类型，表和分区的概念(它们与传统的关系 DBMS 中的概念非常相似)，然后借助一些示例来说明 Hive 的功能。

Data Units

按照粒度 Sequences-Hive 数据被组织为：

  • 数据库 ：命名空间的作用是避免表，视图，分区，列等的命名冲突。数据库还可以用于为一个用户或一组用户强制实施安全性。

  • 表格 ：具有相同架构的同类数据单元。表格的示例可以是 page_views 表，其中每一行可以包含以下列(模式)：

  • timestamp，它是 INT 类型，与查看该页面的 UNIX 时间戳相对应。

    • userid — BIGINT 类型，用于标识查看该页面的用户。

    • page_url—类型为 STRING，用于捕获页面的位置。

    • referer_url—属于 STRING，用于捕获用户到达当前页面的页面位置。

    • IP—是 STRING 类型，用于捕获发出页面请求的 IP 地址。

  • 分区 ：每个表可以具有一个或多个分区键，这些键确定数据的存储方式。除作为存储单元之外，分区还使用户可以有效地标识满足指定条件的行；例如，类型为 STRING 的 date_partition 和类型为 STRING 的 country_partition。分区键的每个唯一值定义表的一个分区。例如，来自“ 2009-12-23”的所有“ US”数据都是 page_views 表的分区。因此，如果仅对 2009-12-23 的“美国”数据运行分析，则可以仅对表的相关分区运行该查询，从而大大加快了分析速度。但是请注意，仅因为分区名为 2009-12-23 并不意味着该分区包含该日期之后的全部或仅数据。为方便起见，分区以日期命名；保证分区名称和数据内容之间的关系是用户的工作！分区列是虚拟列，它们不是数据本身的一部分，而是在加载时派生的。

  • Buckets (或 Clusters )：每个分区中的数据可以根据表中某些列的哈希函数的值依次分为 Buckets。例如，page_views 表可以按 userid 进行存储，userid 是 page_view 表中除分区列之外的列之一。这些可用于有效采样数据。

请注意，不必对表进行分区或存储，但是这些抽象使系统可以在查询处理期间修剪大量数据，从而加快查询执行速度。

Type System

Hive 支持原始数据类型和复杂数据类型，如下所述。有关其他信息，请参见Hive 数据类型。

Primitive Types

  • 类型与表中的列关联。支持以下基本类型：

  • Integers

  • TINYINT-1 个字节的整数

    • SMALLINT-2 字节整数

    • INT—4 字节整数

    • BIGINT-8 字节整数

  • Boolean type

  • BOOLEAN—TRUE/FALSE

  • 浮点数字

  • FLOAT—single precision

    • DOUBLE—Double precision

  • 定点数

  • DECIMAL-用户定义的比例和精度的固定点值

  • String types

  • STRING-指定字符集中的字符序列

    • VARCHAR-指定字符集中最大长度的字符序列

    • CHAR-具有指定长度的指定字符集中的字符序列

  • 日期和时间类型

  • TIMESTAMP —不带时区的日期和时间(“ LocalDateTime”语义)

    • 带有本地时区的时间戳—精确到纳秒的时间点(“即时”语义)

    • DATE—a date

  • Binary types

  • BINARY-字节序列

这些类型按以下层次结构进行组织(其中父级是所有子级实例的超类型)：

  • Type

Primitive Type
Number
DOUBLE
FLOAT
BIGINT
INT
SMALLINT
TINYINT
 
   - |----|

|STRING|

 - |----|

|BOOLEAN|

此类型层次结构定义如何在查询语言中隐式转换类型。允许从子类型到祖先类型的隐式转换。因此，当查询表达式期望类型 1 并且数据为类型 2 时，如果类型 1 是类型层次结构中类型 2 的祖先，则类型 2 将隐式转换为类型 1.请注意，类型层次结构允许将 STRING 隐式转换为 DOUBLE。

可以使用强制转换运算符来进行显式类型转换，如下面的#内置功能部分所示。

Complex Types

可以使用以下方法从原始类型和其他组合类型构建复杂类型：

  • 结构：可以使用 DOT(.)表示法访问类型内的元素。例如，对于 STRUCT{a INT; b INT}类型的列 c，表达式 c.a 将访问 a 字段。

  • Map(键值 Tuples)：使用['element name']表示法访问元素。例如，在包含来自'group'-> gid 的 Map 的 MapM 中，可以使用 M ['group']访问 gid 值

  • 数组(可索引列表)：数组中的元素必须具有相同的类型。可以使用[n]表示法访问元素，其中 n 是数组的索引(从零开始)。例如，对于具有元素['a'，'b'，'c']的数组 A，A [1]重新运行'b'。

使用原始类型和用于创建复杂类型的结构，可以创建具有任意嵌套级别的类型。例如，用户类型可以包括以下字段：

  • 性别-这是 STRING。

  • 活跃的-这是 BOOLEAN。

Timestamp

时间戳一直是造成混乱的根源，因此我们尝试记录 Hive 的预期语义。

{#Tutorial-Timestamp("LocalDateTime"semantics)}时间戳记(“ LocalDateTime”语义)

Java 的“ LocalDateTime”时间戳将日期和时间记录为年，月，日，时，分和秒，没有时区。无论本地时区如何，这些时间戳始终具有相同的值。

例如，时间戳值“ 2014-12-12 12:34:56”被分解为年，月，日，小时，分钟和秒字段，但是没有可用的时区信息。它不对应于任何特定 Moment。无论本地时区如何，它将始终是相同的值。除非您的应用程序始终使用 UTC，否则对于大多数应用程序，时间戳优先于本地时区。当用户说某个事件是在 10:00 时，它始终是相对于某个时区的，它表示一个时间点，而不是在任意时区中的 10:00.

{#Tutorial-Timestampwithlocaltimezone("Instant"semantics)}具有本地时区的时间戳(“即时”语义)

Java 的“即时”时间戳定义了一个时间点，该时间点无论读取数据的位置如何都保持不变。因此，时间戳将通过本地时区进行调整以匹配原始时间点。

Type美国价值/洛杉矶在美国的价值/纽约
timestamp2014-12-12 12:34:562014-12-12 12:34:56
带有本地时区的时间戳2014-12-12 12:34:562014-12-12 15:34:56

与其他工具的比较

SQL 2003OracleSybasePostgresMySQLMicrosoft SQLIBM DB2PrestoSnowflakeHive> = 3.1IcebergSpark
timestampLocalLocalLocalLocalInstantOtherLocalLocalLocalLocalLocalInstant
带有本地时区的时间戳InstantInstantInstant
带时区的时间戳OffsetOffsetOffsetInstantOffsetOffsetOffsetInstant
没有时区的时间戳LocalLocalLocalLocal

Other definitions:

  • 偏移量=记录时间点以及编写者所在时区中的时区偏移量。

内置运算符和函数

下面列出的运算符和功能不一定是最新的。 (Hive 运算符和 UDF具有更多当前信息。)在Beeline或 Hive CLI中，使用以下命令显示最新文档：

SHOW FUNCTIONS;
DESCRIBE FUNCTION <function_name>;
DESCRIBE FUNCTION EXTENDED <function_name>;

Case-insensitive

所有 Hive 关键字都不区分大小写，包括 Hive 运算符和函数的名称。

内置运算符

  • 关系运算符 -以下运算符比较传递的操作数并生成 TRUE 或 FALSE 值，具体取决于操作数之间的比较是否成立。
Relational OperatorOperand typesDescription
A = B所有原始类型如果表达式 A 等于表达式 B，则为 TRUE；否则为。否则为 FALSE
A！= B所有原始类型如果表达式 A 不等于表达式 B，则为 TRUE；否则为 FALSE
A <B所有原始类型如果表达式 A 小于表达式 B，则为 TRUE；否则为。否则为 FALSE
A <= B所有原始类型如果表达式 A 小于或等于表达式 B，则为 TRUE；否则为。否则为 FALSE
A> B所有原始类型如果表达式 A 大于表达式 B，则为 TRUE，否则为 FALSE
A> = B所有原始类型如果表达式 A 大于或等于表达式 B，则为 TRUE，否则为 FALSE
A IS NULLall types如果表达式 A 的计算结果为 NULL，则为 TRUE，否则为 FALSE
一个不为空all types如果表达式 A 的计算结果为 NULL，则为 FALSE，否则为 TRUE
像 Bstrings如果字符串 A 与 SQL 简单正则表达式 B 匹配，则为 TRUE，否则为 FALSE。逐个字符进行比较。 B 中的字符匹配 A 中的任何字符(类似于 posix 正则表达式中的 . )，B 中的％字符匹配 A 中任意数目的字符(类似于 posix 正则表达式中的 . *表达式)。例如，'foobar' LIKE 'foo'的评估结果为 FALSE，而'foobar' LIKE 'foo___'的评估结果为 TRUE，而'foobar' LIKE 'foo%'则评估为 TRUE。要转义％，请使用(％匹配一个％字符)。如果数据包含分号，并且您要搜索它，则需要对其进行转义，columnValue LIKE 'a\;b'
阿里克 Bstrings如果 A 或 B 为 NULL，则为 NULL，如果 A 的任何子字符串(可能为空)与 Java 正则表达式 B(请参见Java 正则表达式语法)匹配，则为 TRUE，否则为 FALSE。例如，'foobar'rlike'foo'的计算结果为 TRUE，'foobar'rlike'^ f.* r $'的计算结果也为 TRUE。
A REGEXP Bstrings与 RLIKE 相同
  • 算术运算符 -以下运算符支持对操作数的各种常见算术运算。它们都返回数字类型。
Arithmetic OperatorsOperand typesDescription
乙所有数字类型给出将 A 和 B 相加的结果。结果的类型与操作数类型的公共父级(在类型层次结构中)相同，例如，因为每个整数都是浮点数。因此，float 是整数的包含类型，因此 float 和 int 上的运算符将导致 float。
A-B所有数字类型给出从 A 减去 B 的结果。结果的类型与操作数类型的公共父级(在类型层次结构中)相同。
A * B所有数字类型给出将 A 和 B 相乘的结果。结果的类型与操作数类型的公共父级(在类型层次结构中)相同。请注意，如果乘法导致溢出，则必须将其中一个运算符转换为类型层次结构中较高的类型。
A/B所有数字类型给出从 A 除以 B 的结果。结果的类型与操作数类型的公共父级(在类型层次结构中)相同。如果操作数是整数类型，则结果是除法的商。
A％B所有数字类型给出 A 除以 B 所得的提示。结果的类型与操作数类型的公共父级(在类型层次结构中)相同。
A＆B所有数字类型给出 A 和 B 的按位与的结果。结果的类型与操作数类型的公共父级(在类型层次结构中)相同。
A乙所有数字类型给出 A 和 B 的按位或的结果。结果的类型与操作数类型的公共父级(在类型层次结构中)相同。
A ^ B所有数字类型给出 A 和 B 的按位 XOR 结果。结果的类型与操作数类型的公共父级(在类型层次结构中)相同。
~A所有数字类型给出 A 的按位 NOT 的结果。结果的类型与 A 的类型相同。
  • 逻辑运算符 -以下运算符为创建逻辑表达式提供支持。它们都根据操作数的布尔值返回布尔值 TRUE 或 FALSE。
Logical OperatorsOperands typesDescription
A 和 Bboolean如果 A 和 B 均为 TRUE，则为 TRUE，否则为 FALSE
A && Bboolean与 A 和 B 相同
A 或 Bboolean如果 A 或 B 或两者均为 TRUE，则为 TRUE，否则为 FALSE
A乙boolean与 A 或 B 相同
NOT Aboolean如果 A 为 FALSE，则为 TRUE，否则为 FALSE
!Aboolean与 NOT A 相同
  • 复杂类型上的运算符 -以下运算符提供了访问复杂类型上的元素的机制
OperatorOperand typesDescription
A[n]A 是一个数组，n 是一个整数返回数组 A 中的第 n 个元素。第一个元素的索引为 0，例如，如果 A 是包含['foo'，'bar']的数组，则 A [0]返回'foo'和 A [1]返回'酒吧'
M[key]M 是 Map\ <K, V>，并且密钥的类型为 K例如，如果 M 是包含以下内容的 Map，则返回对应于 Map 中键的值：

\ {'f' -> 'foo', 'b' -> 'bar', 'all' -> 'foobar'}，然后 M ['all']返回'foobar'
S.xS 是一个结构返回 S 的 x 字段，例如，对于结构 foobar{int foo, int bar} foobar.foo 返回存储在该结构的 foo 字段中的整数。

内置函数

Return Type功能名称(签名)Description
BIGINTround(double a)返回双精度的四舍五入的 BIGINT 值
BIGINTfloor(double a)返回等于或小于双精度值的最大 BIGINT 值
BIGINTceil(double a)返回等于或大于 double 的最小 BIGINT 值
doublerand()，rand(int 种子)返回一个随机数(逐行变化)。指定种子将确保生成的随机数序列具有确定性。
stringconcat(字符串 A，字符串 B，...)返回将 B 接在 A 之后的字符串。例如，concat('foo'，'bar')的结果为'foobar'。此函数接受任意数量的参数，并返回所有参数的串联。
stringsubstr(字符串 A，int 开始)从起始位置开始直到字符串 A 的结尾，返回 A 的子字符串。例如，substr('foobar'，4)的结果为'bar'
stringsubstr(字符串 A，int 开头，int 长度)从起始位置开始以给定的长度返回 A 的子字符串，例如，

substr('foobar'，4，2)结果为'ba'
stringupper(字符串 A)返回将 A 的所有字符都转换为大写形式的字符串，例如，upper('fOoBaR')会导致'FOOBAR'。
stringucase(字符串 A)与鞋面相同
stringlower(字符串 A)返回将 B 的所有字符转换为小写字母的字符串，例如，lower('fOoBaR')会导致'foobar'。
stringlcase(string A)与低位相同
stringtrim(string A)返回从 A 的两端修剪空格产生的字符串，例如，trim('foobar')会导致'foobar'
stringltrim(字符串 A)返回从 A 的开头(左侧)起修剪空格所得到的字符串。例如，ltrim('foobar')会导致'foobar'。
stringrtrim(字符串 A)返回从 A 的末端(右侧)修剪空格所得到的字符串。例如，rtrim('foobar')会导致'foobar'。
stringregexp_replace(字符串 A，字符串 B，字符串 C)返回将 B 中所有与 Java 正则表达式语法(请参见Java 正则表达式语法)匹配的子字符串替换为 C 所得到的字符串。例如，regexp_replace('foobar'，'ooar'，)返回'fb'
intsize(Map<K.V>)返回 Map 类型中的元素数
intsize(Array)返回数组类型中的元素数
*<type> *cast(* * as * *)的值将表达式 expr 的结果转换为\ ，例如，cast('1'as BIGINT)将转换字符串'1'对其进行整体表示。如果转换不成功，则返回 null。
stringfrom_unixtime(int unixtime)将 UNIX 纪元(1970-01-01 00:00:00 UTC)的秒数转换为一个字符串，该字符串表示当前系统时区中该 Moment 的时间戳，格式为“ 1970-01-01 00:00:00”
stringto_date(字符串时间戳)返回时间戳字符串的日期部分：to_date(“ 1970-01-01 00:00:00”)=“ 1970-01-01”
intyear(字符串日期)返回日期或时间戳字符串的年份部分：year(“ 1970-01-01 00:00:00”)= 1970，year(“ 1970-01-01”)= 1970
intmonth(字符串日期)返回日期或时间戳记字符串的月份部分：month(“ 1970-11-01 00:00:00”)= 11，month(“ 1970-11-01”)= 11
intday(字符串日期)返回日期或时间戳字符串的日期部分：day(“ 1970-11-01 00:00:00”)= 1，day(“ 1970-11-01”)= 1
stringget_json_object(字符串 json_string，字符串路径)基于指定的 json 路径从 json 字符串中提取 json 对象，并返回提取的 json 对象的 json 字符串。如果 Importing 的 json 字符串无效，它将返回 null。
  • Hive 支持以下内置的聚合函数：
Return Type汇总功能名称(签名)Description
BIGINTcount(*)，count(expr)，count(DISTINCT expr [，expr_.])count(*)-返回已检索的行总数，包括包含 NULL 值的行； count(expr)-返回为其提供的表达式为非 NULL 的行数； count(DISTINCT expr [，expr])-返回所提供的表达式唯一且非 NULL 的行数。
DOUBLEsum(col)，sum(DISTINCT col)返回组中元素的总和或组中列的不同值的总和
DOUBLE平均(col)，平均(DISTINCT col)返回组中元素的平均值或组中列的不同值的平均值
DOUBLEmin(col)返回组中列的最小值
DOUBLEmax(col)返回组中列的最大值

Language Capabilities

Hive's SQL提供了基本的 SQL 操作。这些操作适用于表或分区。这些操作是：

  • 能够使用 WHERE 子句从表中过滤行。

  • 能够使用 SELECT 子句从表中选择某些列。

  • 在两个表之间进行等值联接的能力。

  • 能够针对表中存储的数据评估多个“分组依据”列上的聚合。

  • 能够将查询结果存储到另一个表中。

  • 能够将表的内容下载到本地(例如，nfs)目录。

  • 能够将查询结果存储在 hadoop dfs 目录中。

  • 能够 Management 表和分区(创建，删除和更改)。

  • 能够以自定义 Map/缩小作业选择的语言插入自定义脚本。

用法和示例

注意：以下许多示例已过时.您可以在LanguageManual中找到更多最新信息.

以下示例突出了系统的一些显着功能。可以在配置单元查询测试用例找到一组详细的查询测试用例，而在查询测试用例结果可以找到相应的结果。

创建，显示，更改和删除表

有关创建，显示，更改和删除表的详细信息，请参见Hive 数据定义语言。

Creating Tables

创建上面提到的 page_view 表的示例语句如下：

CREATE TABLE page_view(viewTime INT, userid BIGINT,
                    page_url STRING, referrer_url STRING,
                    ip STRING COMMENT 'IP Address of the User')
    COMMENT 'This is the page view table'
    PARTITIONED BY(dt STRING, country STRING)
    STORED AS SEQUENCEFILE;

在此示例中，使用相应的类型指定表的列。Comments 可以附加在列级和表级。此外，partitioned by 子句定义了分区列，这些分区列与数据列不同，并且实际上并未与数据一起存储。以这种方式指定时，假定文件中的数据使用 ASCII 001(ctrl-A)作为字段定界符，使用换行符作为行定界符来定界。

如果数据不是上述格式的数据，则可以对字段定界符进行参数设置，如以下示例所示：

CREATE TABLE page_view(viewTime INT, userid BIGINT,
                    page_url STRING, referrer_url STRING,
                    ip STRING COMMENT 'IP Address of the User')
    COMMENT 'This is the page view table'
    PARTITIONED BY(dt STRING, country STRING)
    ROW FORMAT DELIMITED
            FIELDS TERMINATED BY '1'
    STORED AS SEQUENCEFILE;

由于行分隔符不是由 Hive 而是由 Hadoop 分隔符确定，因此当前无法更改行分隔符。

将表存储在某些列上也是一个好主意，以便可以对数据集执行有效的采样查询。如果没有存储分区，仍然可以在表上进行随机采样，但是效率不高，因为查询必须扫描所有数据。以下示例说明了在 userid 列上存储的 page_view 表的情况：

CREATE TABLE page_view(viewTime INT, userid BIGINT,
                    page_url STRING, referrer_url STRING,
                    ip STRING COMMENT 'IP Address of the User')
    COMMENT 'This is the page view table'
    PARTITIONED BY(dt STRING, country STRING)
    CLUSTERED BY(userid) SORTED BY(viewTime) INTO 32 BUCKETS
    ROW FORMAT DELIMITED
            FIELDS TERMINATED BY '1'
            COLLECTION ITEMS TERMINATED BY '2'
            MAP KEYS TERMINATED BY '3'
    STORED AS SEQUENCEFILE;

在上面的示例中，该表通过 userid 的哈希函数聚集到 32 个存储桶中。在每个存储桶中，数据按 viewTime 的升序排序。这样的组织允许用户在集群列上进行高效采样(在本例中为 userid)。排序属性使内部运算符可以利用众所周知的数据结构，同时以更高的效率评估查询。

CREATE TABLE page_view(viewTime INT, userid BIGINT,
                    page_url STRING, referrer_url STRING,
                    friends ARRAY<BIGINT>, properties MAP<STRING, STRING>
                    ip STRING COMMENT 'IP Address of the User')
    COMMENT 'This is the page view table'
    PARTITIONED BY(dt STRING, country STRING)
    CLUSTERED BY(userid) SORTED BY(viewTime) INTO 32 BUCKETS
    ROW FORMAT DELIMITED
            FIELDS TERMINATED BY '1'
            COLLECTION ITEMS TERMINATED BY '2'
            MAP KEYS TERMINATED BY '3'
    STORED AS SEQUENCEFILE;

在此示例中，以与类型定义类似的方式指定组成表行的列。Comments 可以附加在列级和表级。此外，partitioned by 子句定义了分区列，这些分区列与数据列不同，并且实际上并未与数据一起存储。 CLUSTERED BY 子句指定用于存储的列以及要创建的存储桶数。分隔行格式指定行如何在配置单元表中存储。在定界格式的情况下，这指定如何终止字段，如何终止集合(数组或 Map)中的项目以及终止 Map 键。 STORED AS SEQUENCEFILE 表示此数据以二进制格式(使用 hadoop SequenceFiles)存储在 hdfs 上。上面示例中的 ROW FORMAT 和 STORED AS 子句显示的值代表系统默认值。

表名和列名不区分大小写。

浏览表和分区

SHOW TABLES;

列出仓库中的现有表；其中有很多，可能比您想要浏览的更多。

SHOW TABLES 'page.*';

列出前缀为“ page”的表。该模式遵循 Java 正则表达式语法(因此句点是通配符)。

SHOW PARTITIONS page_view;

列出表的分区。如果该表不是分区表，则将引发错误。

DESCRIBE page_view;

列出表的列和列类型。

DESCRIBE EXTENDED page_view;

列出表的列和所有其他属性。这会打印很多信息，但也不会以漂亮的格式显示。通常用于调试。

DESCRIBE EXTENDED page_view PARTITION (ds='2008-08-08');

列出分区的列和所有其他属性。这还会打印很多通常用于调试的信息。

Altering Tables

将现有表重命名为新名称。如果具有新名称的表已经存在，则返回错误：

ALTER TABLE old_table_name RENAME TO new_table_name;

重命名现有表的列。确保使用相同的列类型，并为每个现有列包括一个条目：

ALTER TABLE old_table_name REPLACE COLUMNS (col1 TYPE, ...);

要将列添加到现有表：

ALTER TABLE tab1 ADD COLUMNS (c1 INT COMMENT 'a new int column', c2 STRING DEFAULT 'def val');

请注意，架构的更改(例如添加列)会保留表的旧分区的架构，以防它是分区表。所有访问这些列并在旧分区上运行的查询都隐式地为这些列返回 null 值或指定的默认值。

在以后的版本中，我们可以使某些行为具有可假设性，而不是在特定分区中未找到该列的情况下抛出错误。

删除表和分区

删除表相当简单。将该表放下将隐式删除该表上已构建的所有索引(这是将来的功能)。关联的命令是：

DROP TABLE pv_users;

删除分区。更改表以删除分区。

ALTER TABLE pv_users DROP PARTITION (ds='2008-08-08')
  • 请注意，此表或分区的任何数据都将被删除并且可能无法恢复。 *

Loading Data

有多种方法可以将数据加载到 Hive 表中。用户可以创建一个指向HDFS内指定位置的外部表。在这种特定用法中，用户可以使用 HDFS 放置或复制命令将文件复制到指定位置，并创建一个指向该位置的表以及所有相关的行格式信息。完成此操作后，用户可以转换数据并将其插入任何其他 Hive 表中。例如，如果文件/tmp/pv_2008-06-08.txt 包含 2008 年 6 月 8 日提供的用逗号分隔的页面视图，并且需要将其加载到适当分区中的 page_view 表中，则可以按照以下命令 Sequences 进行操作实现这一目标：

CREATE EXTERNAL TABLE page_view_stg(viewTime INT, userid BIGINT,
                    page_url STRING, referrer_url STRING,
                    ip STRING COMMENT 'IP Address of the User',
                    country STRING COMMENT 'country of origination')
    COMMENT 'This is the staging page view table'
    ROW FORMAT DELIMITED FIELDS TERMINATED BY '44' LINES TERMINATED BY '12'
    STORED AS TEXTFILE
    LOCATION '/user/data/staging/page_view';

    hadoop dfs -put /tmp/pv_2008-06-08.txt /user/data/staging/page_view

    FROM page_view_stg pvs
    INSERT OVERWRITE TABLE page_view PARTITION(dt='2008-06-08', country='US')
    SELECT pvs.viewTime, pvs.userid, pvs.page_url, pvs.referrer_url, null, null, pvs.ip
    WHERE pvs.country = 'US';

*由于 LINES TERMINATED BY 限制，此代码导致错误

失败：SemanticException 6:67 终止符行现在仅支持换行符' n'。令牌“ 12”附近遇到错误

请参阅HIVE-5999-获取问题的详细信息...状态HIVE-11996-获取问题的详细信息...状态

在上面的示例中，为目标表中的数组和 Map 类型插入了空值，但如果指定了正确的行格式，则它们也可能来自外部表。

如果 HDFS 中已有旧数据，用户希望在其中放置一些元数据，以便可以使用 Hive 查询和操作该数据，则此方法很有用。

此外，系统还支持语法，可以将本地文件系统中文件中的数据直接加载到 Hive 表中，其中 Importing 数据格式与表格式相同。如果/tmp/pv_2008-06-08_us.txt 已经包含美国的数据，则我们不需要任何其他过滤，如前面的示例所示。在这种情况下，可以使用以下语法完成加载：

LOAD DATA LOCAL INPATH /tmp/pv_2008-06-08_us.txt INTO TABLE page_view PARTITION(date='2008-06-08', country='US')

path 参数可以使用目录(在这种情况下，将加载目录中的所有文件)，单个文件名或通配符(在这种情况下，将所有匹配的文件上载)。如果参数是目录，则它不能包含子目录。同样，通配符必须仅与文件名匹配。

在 Importing 文件/tmp/pv_2008-06-08_us.txt 非常大的情况下，用户可以决定并行加载数据(使用 Hive 外部的工具)。将文件放入 HDFS 后，可以使用以下语法将数据加载到 Hive 表中：

LOAD DATA INPATH '/user/data/pv_2008-06-08_us.txt' INTO TABLE page_view PARTITION(date='2008-06-08', country='US')

对于这些示例，假设 input.txt 文件中的 array 和 map 字段为空字段。

有关将数据加载到 Hive 表中的更多信息，请参见Hive 数据处理语言；有关创建外部表的另一个示例，请参见External Tables。

查询和插入数据

Hive 查询操作记录在Select中，插入操作记录在通过查询将数据插入 Hive 表和通过查询将数据写入文件系统中。

Simple Query

对于所有活动用户，可以使用以下形式的查询：

INSERT OVERWRITE TABLE user_active
    SELECT user.*
    FROM user
    WHERE user.active = 1;

请注意，与 SQL 不同，我们总是将结果插入表中。稍后我们将说明用户如何检查这些结果，甚至将其转储到本地文件中。您还可以在Beeline或 Hive CLI中运行以下查询：

SELECT user.*
    FROM user
    WHERE user.active = 1;

这将在内部重写为一些临时文件，并显示在 HiveClient 端。

基于分区的查询

系统根据分区列上的 where 子句条件自动确定要在查询中使用的分区。例如，为了获取域 xyz.com 引用的 03/2008 月份的所有 page_views，可以编写以下查询：

INSERT OVERWRITE TABLE xyz_com_page_views
    SELECT page_views.*
    FROM page_views
    WHERE page_views.date >= '2008-03-01' AND page_views.date <= '2008-03-31' AND
          page_views.referrer_url like '%xyz.com';

请注意，此处使用 page_views.date，因为该表(上面)是使用 PARTITIONED BY(date DATETIME，country STRING)定义的；如果您给分区命名不同，请不要期望.date 发挥您的想法！

Joins

为了获得 2008 年 3 月 3 日 page_view 的人口统计细分(按性别)，需要在 userid 列上连接 page_view 表和 user 表。如下面的查询所示，这可以通过联接来完成：

INSERT OVERWRITE TABLE pv_users
    SELECT pv.*, u.gender, u.age
    FROM user u JOIN page_view pv ON (pv.userid = u.id)
    WHERE pv.date = '2008-03-03';

为了进行外部联接，用户可以使用 LEFT OUTER，RIGHT OUTER 或 FULL OUTER 关键字限定联接，以指示外部联接的类型(保留的左侧，保留的右侧或两侧保留)。例如，为了在上面的查询中进行完全外部联接，相应的语法应类似于以下查询：

INSERT OVERWRITE TABLE pv_users
    SELECT pv.*, u.gender, u.age
    FROM user u FULL OUTER JOIN page_view pv ON (pv.userid = u.id)
    WHERE pv.date = '2008-03-03';

为了检查另一个表中是否存在键，用户可以使用 LEFT SEMI JOIN，如以下示例所示。

INSERT OVERWRITE TABLE pv_users
    SELECT u.*
    FROM user u LEFT SEMI JOIN page_view pv ON (pv.userid = u.id)
    WHERE pv.date = '2008-03-03';

为了联接多个表，用户可以使用以下语法：

INSERT OVERWRITE TABLE pv_friends
    SELECT pv.*, u.gender, u.age, f.friends
    FROM page_view pv JOIN user u ON (pv.userid = u.id) JOIN friend_list f ON (u.id = f.uid)
    WHERE pv.date = '2008-03-03';

请注意，Hive 仅支持equi-joins。另外，最好将最大的表放在连接的最右侧，以获得最佳性能。

Aggregations

为了按性别计算不同用户的数量，可以编写以下查询：

INSERT OVERWRITE TABLE pv_gender_sum
    SELECT pv_users.gender, count (DISTINCT pv_users.userid)
    FROM pv_users
    GROUP BY pv_users.gender;

可以同时完成多个聚合，但是任何两个聚合都不能具有不同的 DISTINCT 列，例如，以下是可能的

INSERT OVERWRITE TABLE pv_gender_agg
    SELECT pv_users.gender, count(DISTINCT pv_users.userid), count(*), sum(DISTINCT pv_users.userid)
    FROM pv_users
    GROUP BY pv_users.gender;

但是，不允许以下查询

INSERT OVERWRITE TABLE pv_gender_agg
    SELECT pv_users.gender, count(DISTINCT pv_users.userid), count(DISTINCT pv_users.ip)
    FROM pv_users
    GROUP BY pv_users.gender;

多表/文件插入

聚合或简单选择的输出可以进一步发送到多个表中，甚至可以发送到 hadoop dfs 文件(然后可以使用 hdfsUtil 对其进行操作)。例如，如果与性别细分一起，需要按年龄查找唯一页面浏览的细分，则可以通过以下查询来完成：

FROM pv_users
    INSERT OVERWRITE TABLE pv_gender_sum
        SELECT pv_users.gender, count_distinct(pv_users.userid)
        GROUP BY pv_users.gender

    INSERT OVERWRITE DIRECTORY '/user/data/tmp/pv_age_sum'
        SELECT pv_users.age, count_distinct(pv_users.userid)
        GROUP BY pv_users.age;

第一个 insert 子句将第一个 group by 的结果发送到 Hive 表，而第二个 insert 子句将结果发送到 hadoop dfs 文件。

Dynamic-Partition Insert

在前面的示例中，用户必须知道要插入哪个分区，并且在一个 insert 语句中只能插入一个分区。如果要加载到多个分区，则必须使用多插入语句，如下所示。

FROM page_view_stg pvs
    INSERT OVERWRITE TABLE page_view PARTITION(dt='2008-06-08', country='US')
           SELECT pvs.viewTime, pvs.userid, pvs.page_url, pvs.referrer_url, null, null, pvs.ip WHERE pvs.country = 'US'
    INSERT OVERWRITE TABLE page_view PARTITION(dt='2008-06-08', country='CA')
           SELECT pvs.viewTime, pvs.userid, pvs.page_url, pvs.referrer_url, null, null, pvs.ip WHERE pvs.country = 'CA'
    INSERT OVERWRITE TABLE page_view PARTITION(dt='2008-06-08', country='UK')
           SELECT pvs.viewTime, pvs.userid, pvs.page_url, pvs.referrer_url, null, null, pvs.ip WHERE pvs.country = 'UK';

为了将数据加载到特定日期的所有国家/地区分区中，您必须在 Importing 数据中为每个国家/地区添加一条插入语句。这非常不方便，因为您必须先了解 Importing 数据中存在的国家/地区列表，并事先创建分区。如果列表更改了另一天，则必须修改插入 DML 以及分区创建 DDL。由于每个插入语句都可以转换为 MapReduce 作业，因此效率也不高。

  • Dynamic-partition insert *(或多分区插入)旨在通过动态确定在扫描 Importing 表时应创建和填充哪些分区来解决此问题。这是新增功能，仅从 0.6.0 版本开始可用。在动态分区插入中，将评估 Importing 列的值以确定应将此行插入哪个分区。如果尚未创建该分区，它将自动创建该分区。使用此功能，您只需一个插入语句即可创建并填充所有必要的分区。另外，由于只有一个 insert 语句，所以只有一个对应的 MapReduce 作业。与多次插入的情况相比，这可以显着提高性能并减少 Hadoop 集群的工作量。

以下是使用一个插入语句将数据加载到所有国家/地区分区的示例：

FROM page_view_stg pvs
    INSERT OVERWRITE TABLE page_view PARTITION(dt='2008-06-08', country)
           SELECT pvs.viewTime, pvs.userid, pvs.page_url, pvs.referrer_url, null, null, pvs.ip, pvs.country

与多插入语句有一些语法差异：

  • 国家/地区出现在 PARTITION 规范中，但没有关联的值。在这种情况下，country 是动态分区列。另一方面，ds 具有与其关联的值，这意味着它是静态分区列。如果列是动态分区列，则其值将来自 Importing 列。当前，我们仅允许动态分区列成为 partition 子句中的最后一列，因为分区列 Sequences 指示其层次结构 Sequences(意味着 dt 是根分区，而 country 是子分区)。您不能使用(dt，country ='US')指定分区子句，因为这意味着您需要使用任何日期更新所有分区，并且其国家子分区为'US'。

  • 在 select 语句中添加了一个附加的 pvs.country 列。这是动态分区列的相应 Importing 列。请注意，您不需要为静态分区列添加 Importing 列，因为它的值在 PARTITION 子句中是已知的。请注意，动态分区值是通过排序而不是名称来选择的，并作为 select 子句的最后一列。

动态分区插入语句的语义：

  • 当动态分区列中已经存在非空分区时(例如，在某些 ds 根分区下存在 country ='CA')，如果动态分区插入看到相同的值(例如'CA')，它将被覆盖)Importing 数据中。这符合“插入覆盖”的语义。但是，如果分区值“ CA”未出现在 Importing 数据中，则现有分区将不会被覆盖。

  • 由于 Hive 分区对应于 HDFS 中的目录，因此分区值必须符合 HDFS 路径格式(Java 中的 URI)。 URI 中具有特殊含义的任何字符(例如'％'，'：'，'/'，'#')都将以'％'进行转义，后跟 2 个字节的 ASCII 值。

  • 如果 Importing 列的类型不同于 STRING，则其值将首先转换为 STRING 以用于构造 HDFS 路径。

  • 如果 Importing 列的值为 NULL 或空字符串，则该行将放入一个特殊分区，其名称由配置单元参数 hive.exec.default.partition.name 控制。默认值为HIVE_DEFAULT_PARTITION{ *} *。基本上，该分区将包含其值不是有效分区名称的所有“坏”行。这种方法的警告是，错误的值将丢失，并且如果选择“ Hive”，则将其替换为HIVE_DEFAULT_PARTITION{ *} *。 JIRA HIVE-1309 是一种允许用户指定“错误文件”以保留 Importing 分区列值的解决方案。

  • 动态分区插入可能会占用大量资源，因为它可能会在短时间内生成大量分区。为了使自己安全，我们定义了三个参数：

  • hive.exec.max.dynamic.partitions.pernode (默认值为 100)是每个 Map 器或化简器可以创建的最大动态分区。如果一个 Map 器或缩减器创建的阈值多于该阈值，则会从 Map 器/缩减器中引发致命错误(通过计数器)，并且整个工作将被杀死。

    • hive.exec.max.dynamic.partitions (默认值为 1000)是一个 DML 可以创建的动态分区总数。如果每个 Map 器/缩减器未超过限制，但动态分区的总数未超过限制，则在作业结束时会引发异常，然后才将中间数据移至最终目标。

    • hive.exec.max.created.files (默认值为 100000)是所有 Map 器和精简器创建的最大文件总数。每当创建新文件时，每个 Map 器/缩减器都会更新 Hadoop 计数器，从而实现此目的。如果总数超过 hive.exec.max.created.files，将引发致命错误并杀死作业。

  • 我们要防止动态分区插入的另一种情况是，用户可能无意中将所有分区指定为动态分区，而没有指定一个静态分区，而最初的意图是仅覆盖一个根分区的子分区。我们定义另一个参数 hive.exec.dynamic.partition.mode = strict 以防止出现全动态分区情况。在严格模式下，您必须指定至少一个静态分区。默认模式为严格。另外，我们有一个参数 hive.exec.dynamic.partition = true/false 来控制是否完全允许动态分区。默认值在 Hive 0.9.0 之前为 false，在 Hive 0.9.0 及更高版本中为 true。

  • 在 Hive 0.6 中，动态分区插入不适用于 hive.merge.mapfiles = true 或 hive.merge.mapredfiles = true，因此它在内部关闭了合并参数。 Hive 0.7 支持在动态分区插入中合并文件(有关详细信息，请参见 JIRA HIVE-1307)。

故障排除和最佳做法：

  • 如上所述，特定的 Map 器/缩减程序创建的动态分区过多，可能会引发致命错误，并且作业将被杀死。错误消息看起来像：
beeline> set hive.exec.dynamic.partition.mode=nonstrict;
    beeline> FROM page_view_stg pvs
          INSERT OVERWRITE TABLE page_view PARTITION(dt, country)
                 SELECT pvs.viewTime, pvs.userid, pvs.page_url, pvs.referrer_url, null, null, pvs.ip,
                        from_unixtimestamp(pvs.viewTime, 'yyyy-MM-dd') ds, pvs.country;
...
2010-05-07 11:10:19,816 Stage-1 map = 0%,  reduce = 0%
[Fatal Error] Operator FS_28 (id=41): fatal error. Killing the job.
Ended Job = job_201005052204_28178 with errors
...

这样的问题是，一个 Map 器将随机获取一组行，并且不同的(dt，国家/地区)对的数量很可能会超过 hive.exec.max.dynamic.partitions.pernode 的限制。解决它的一种方法是按 Map 器中的动态分区列对行进行分组，然后将其分配到将创建动态分区的 reducer。在这种情况下，不同动态分区的数量将大大减少。上面的示例查询可以重写为：

beeline> set hive.exec.dynamic.partition.mode=nonstrict;
    beeline> FROM page_view_stg pvs
          INSERT OVERWRITE TABLE page_view PARTITION(dt, country)
                 SELECT pvs.viewTime, pvs.userid, pvs.page_url, pvs.referrer_url, null, null, pvs.ip,
                        from_unixtimestamp(pvs.viewTime, 'yyyy-MM-dd') ds, pvs.country
                 DISTRIBUTE BY ds, country;

该查询将生成一个 MapReduce 作业，而不是仅 Map 作业。 SELECT 子句将转换为 Map 器的计划，并且输出将基于(ds，country)对的值分配给 reduce。 INSERT 子句将在化简器中转换为计划，并写入动态分区。

Additional documentation:

插入本地文件

在某些情况下，您可能希望将输出写入本地文件，以便可以将其加载到 excel 电子表格中。这可以通过以下命令来完成：

INSERT OVERWRITE LOCAL DIRECTORY '/tmp/pv_gender_sum'
    SELECT pv_gender_sum.*
    FROM pv_gender_sum;

Sampling

采样子句允许用户为数据 samples 而不是整个表编写查询。当前，采样是在 CREATE TABLE 语句的 CLUSTERED BY 子句中指定的列上完成的。在以下示例中，我们从 pv_gender_sum 表的 32 个存储桶中选择第三个存储桶：

INSERT OVERWRITE TABLE pv_gender_sum_sample
    SELECT pv_gender_sum.*
    FROM pv_gender_sum TABLESAMPLE(BUCKET 3 OUT OF 32);

通常，TABLESAMPLE 语法如下所示：

TABLESAMPLE(BUCKET x OUT OF y)

y 必须是表创建时指定的表中存储桶数的倍数或除数。如果 bucket_number 模块 y 等于 x，则确定所选的桶。因此，在上面的示例中，以下 tablesample 子句

TABLESAMPLE(BUCKET 3 OUT OF 16)

会挑选第三个和第 19 个桶。桶的编号从 0 开始。

另一方面，tablesample 子句

TABLESAMPLE(BUCKET 3 OUT OF 64 ON userid)

会挑出第三个桶的一半。

Union All

该语言还支持全部联合，例如，如果我们假设有两个不同的表来跟踪哪个用户发布了视频并且哪个用户发布了 Comment，则以下查询将全部联合的结果与 user 表结合起来以创建所有视频发布和 Comment 发布事件的单个 Comments 流：

INSERT OVERWRITE TABLE actions_users
    SELECT u.id, actions.date
    FROM (
        SELECT av.uid AS uid
        FROM action_video av
        WHERE av.date = '2008-06-03'

        UNION ALL

        SELECT ac.uid AS uid
        FROM action_comment ac
        WHERE ac.date = '2008-06-03'
        ) actions JOIN users u ON(u.id = actions.uid);

Array Operations

表中的数组列可以如下：

CREATE TABLE array_table (int_array_column ARRAY<INT>);

假设 pv.friends 的类型为 ARRAY<INT>(即它是整数数组)，则用户可以通过其索引在数组中获得特定元素，如以下命令所示：

SELECT pv.friends[2]
    FROM page_views pv;

select 表达式获取 pv.friends 数组中的第三项。

用户还可以使用 size 函数获得数组的长度，如下所示：

SELECT pv.userid, size(pv.friends)
   FROM page_view pv;

Map(关联数组)操作

Map 提供的集合类似于关联数组。目前只能以编程方式创建此类结构。我们将尽快扩展。就当前示例而言，假定 pv.properties 的类型为 map<String, String>，即，它是从字符串到字符串的关联数组。相应地，以下查询：

INSERT OVERWRITE page_views_map
    SELECT pv.userid, pv.properties['page type']
    FROM page_views pv;

可用于从 page_views 表中选择“ page_type”属性。

与数组类似，size 函数也可以用于获取 Map 中元素的数量，如以下查询所示：

SELECT size(pv.properties)
   FROM page_view pv;

自定义 Map/减少脚本

用户还可以使用 Hive 语言本身支持的功能，在数据流中插入自己的自定义 Map 器和精简器。例如，为了运行自定义 Map 器脚本 map_script 和自定义归约器脚本 reduce_script，用户可以发出以下命令，该命令使用 TRANSFORM 子句嵌入 Map 器和归约器脚本。

请注意，在馈送给用户脚本之前，列将被转换为字符串并由 TAB 分隔，并且用户脚本的标准输出将被视为 TAB 分隔的字符串列。用户脚本可以将调试信息输出为标准错误，这将显示在 hadoop 的任务详细信息页面上。

FROM (
        FROM pv_users
        MAP pv_users.userid, pv_users.date
        USING 'map_script'
        AS dt, uid
        CLUSTER BY dt) map_output

    INSERT OVERWRITE TABLE pv_users_reduced
        REDUCE map_output.dt, map_output.uid
        USING 'reduce_script'
        AS date, count;

样例 Map 脚本(weekday_mapper.py)

import sys
import datetime

for line in sys.stdin:
  line = line.strip()
  userid, unixtime = line.split('\t')
  weekday = datetime.datetime.fromtimestamp(float(unixtime)).isoweekday()
  print ','.join([userid, str(weekday)])

当然，对于更一般的选择转换，MAP 和 REDUCE 都是“语法糖”。内部查询也可以这样写：

SELECT TRANSFORM(pv_users.userid, pv_users.date) USING 'map_script' AS dt, uid CLUSTER BY dt FROM pv_users;

无模式 Map/减少：如果在“ USING map_script”之后没有“ AS”子句，则 Hive 假定脚本的输出包含两部分：key 在第一个选项卡之前，value 则在第一个选项卡之后。请注意，这与指定“ AS 键，值”不同，因为在这种情况下，如果存在多个选项卡，则值将仅包含第一个选项卡和第二个选项卡之间的部分。

通过这种方式，我们允许用户迁移旧的 map/reduce 脚本，而无需了解 Map 输出的架构。用户仍然需要知道 reduce 输出模式，因为它必须与我们要插入的表中的内容匹配。

FROM (
        FROM pv_users
        MAP pv_users.userid, pv_users.date
        USING 'map_script'
        CLUSTER BY key) map_output

    INSERT OVERWRITE TABLE pv_users_reduced

        REDUCE map_output.dt, map_output.uid
        USING 'reduce_script'
        AS date, count;

分发方式和排序方式：用户可以指定“分发方式”和“排序方式”，而不是指定“群集方式”，因此分区列和排序列可以不同。通常的情况是分区列是排序列的前缀，但这不是必需的。

FROM (
        FROM pv_users
        MAP pv_users.userid, pv_users.date
        USING 'map_script'
        AS c1, c2, c3
        DISTRIBUTE BY c2
        SORT BY c2, c1) map_output

    INSERT OVERWRITE TABLE pv_users_reduced

        REDUCE map_output.c1, map_output.c2, map_output.c3
        USING 'reduce_script'
        AS date, count;

Co-Groups

在使用 map/reduce 的用户社区中，共同分组是一种相当常见的操作，其中将来自多个表的数据发送到自定义的 reducer，以便按表上某些列的值对行进行分组。使用 UNION ALL 运算符和 CLUSTER BY 规范，可以通过以下方式在 Hive 查询语言中实现。假设我们希望将 uid 列上的 actions_video 和 action_comments 表中的行进行分组，并将它们发送到'reduce_script'自定义 reducer，则用户可以使用以下语法：

FROM (
        FROM (
                FROM action_video av
                SELECT av.uid AS uid, av.id AS id, av.date AS date

               UNION ALL

                FROM action_comment ac
                SELECT ac.uid AS uid, ac.id AS id, ac.date AS date
        ) union_actions
        SELECT union_actions.uid, union_actions.id, union_actions.date
        CLUSTER BY union_actions.uid) map

    INSERT OVERWRITE TABLE actions_reduced
        SELECT TRANSFORM(map.uid, map.id, map.date) USING 'reduce_script' AS (uid, id, reduced_val);
Name Apache Hive 中文手册
Version
Language
Badge
Last Updated 2021-07-05T14:25:28