如何编写Hive建表语句？

Hive是一个基于Hadoop的数据仓库工具，广泛应用于大数据处理和分析，在Hive中，创建表是进行数据存储和管理的基础操作，本文将详细介绍Hive的建表语句，包括各种关键字、数据类型、约束规范以及实际案例。

Hive建表语句详解

基本语法

Hive的建表语句与SQL的创建表语法非常相似，基本的建表语句如下：

CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] table_name
(
    column1 datatype1,
    column2 datatype2,
    ...
)
COMMENT 'table comment'
PARTITIONED BY (partition_column1 datatype1, partition_column2 datatype2, ...)
CLUSTERED BY (cluster_column) INTO num_buckets BUCKETS
ROW FORMAT DELIMITED
FIELDS TERMINATED BY 'field_delimiter'
LINES TERMINATED BY '
'
STORED AS file_format
LOCATION 'hdfs_path'
TBLPROPERTIES ('property_name'='property_value', ...);

关键字解释

CREATE TABLE：用于创建一个新的表。

TEMPORARY：表示创建的是临时表，Hive 0.14.0版本开始支持。

EXTERNAL：表示创建的是外部表，默认情况下，没有参数时创建内部表；有参数时创建外部表，删除表时，内部表的元数据和数据都会被删除，而外部表的元数据被删除但HDFS上的数据不会被删除。

IF NOT EXISTS：如果表已经存在，则不执行创建操作。

table_name：表的名称。

column1 datatype1：列名及其数据类型，常见的数据类型包括INT（整型）、STRING（字符串）、FLOAT（单精度浮点数）、DOUBLE（双精度浮点数）、BOOLEAN（布尔类型）等。

COMMENT：为表添加注释。

PARTITIONED BY：用于定义表的分区列。

CLUSTERED BY：用于定义表的桶列。

ROW FORMAT DELIMITED：指定行的格式为分隔格式。

FIELDS TERMINATED BY：字段之间的分隔符。

LINES TERMINATED BY：行之间的分隔符，通常为换行符

STORED AS：指定数据文件的存储格式，如TEXTFILE、SEQUENCEFILE、ORC、PARQUET等。

LOCATION：指定外部表的数据存储路径。

TBLPROPERTIES：为表设置一些自定义属性。

数据类型

Hive支持多种数据类型，包括基本数据类型、数组类型、Map类型、结构体类型和Union类型。

基本数据类型：

TINYINT：微整型

SMALLINT：短整型

INT：整型

BIGINT：长整型

BOOLEAN：布尔类型

FLOAT：单精度浮点数

DOUBLE：双精度浮点数

DOUBLE PRECISION：支持精确度的双精度浮点数，Hive 2.2.0版本开始支持

STRING：字符串

BINARY：二进制数据，Hive 0.8.0版本开始支持

TIMESTAMP：时间戳，Hive 0.8.0版本开始支持

DECIMAL：十进制数字，Hive 0.11.0版本开始支持

DECIMAL(precision, scale)：支持精确度的十进制数字，Hive 0.13.0版本开始支持

DATE：日期，Hive 0.12.0版本开始支持

VARCHAR：字符串类型，Hive 0.12.0版本开始支持

CHAR：定长字符串类型，Hive 0.13.0版本开始支持

数组类型：格式为ARRAY < data_type >，数组包含一种任意数据类型。

Map类型：格式为MAP < primitive_type, data_type >，key必须是基本数据类型，value可以是任意数据类型。

结构体类型：格式为STRUCT < col_name : data_type [COMMENT col_comment], ...>，可以包含多个形如“列名:数据类型”的列，之间使用英文逗号分隔。

Union类型：格式为UNIONTYPE < data_type, data_type, ... >，可以包含多个任意数据类型的组合体，注意：Hive 0.7.0版本才开始支持union_type类型。

约束规范

Hive支持以下几种列约束规范：

PRIMARY KEY：主键，一个表只允许有一个主键。

UNIQUE：值唯一。

NOT NULL：值不为NULL。

DEFAULT [default_value]：设置默认值。

CHECK [check_expression]：检查。

ENABLE|DISABLE NOVALIDATE RELY/NORELY：指定是否开启约束，ENABLE NOVALIDATE表示启用约束，仅对新加入的数据强制执行约束，而不管表中的现有数据，DISABLE NOVALIDATE表示关闭约束，可以对约束列的数据进行修改等操作。

实际案例

为了更好地理解Hive建表语句和分隔符的使用，下面提供几个实际案例。

案例1：创建一个简单的内部表

假设我们需要创建一个存储员工信息的表，数据文件以逗号分隔，存储在Hive的默认目录下。

CREATE TABLE employees (
    id INT,
    name STRING,
    age INT,
    department STRING
)
COMMENT 'Employee details'
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
LINES TERMINATED BY '
'
STORED AS TEXTFILE;

案例2：创建一个带有分区的内部表

假设我们需要创建一个订单表，并按照订单日期进行分区。

CREATE TABLE orders (
    order_id INT,
    customer_id INT,
    product_id INT,
    amount DOUBLE
)
PARTITIONED BY (order_date STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY 't'
LINES TERMINATED BY '
'
STORED AS TEXTFILE;

在插入数据时，可以根据order_date字段将数据划分到不同的分区中。

案例3：创建一个外部表

假设我们有一些用户行为数据文件已经存储在HDFS的某个目录下，并且这些数据文件以制表符分隔。

CREATE EXTERNAL TABLE user_behavior (
    user_id INT,
    action STRING,
    timestamp STRING
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY 't'
STORED AS TEXTFILE
LOCATION '/user/hive/warehouse/user_behavior';

通过以上案例，我们可以看到Hive建表语句在实际中的应用，根据具体需求选择合适的数据类型、分隔符和存储格式，可以有效地管理和查询大数据。

常见问题解答

问题1：如何在Hive中创建临时表？

答：在Hive中，可以通过在CREATE TABLE语句中添加TEMPORARY关键字来创建临时表。

CREATE TEMPORARY TABLE temp_table (id INT, name STRING);

临时表在会话结束时会自动删除。

问题2：如何指定字段之间的分隔符和行之间的分隔符？

答：在创建表时，可以使用FIELDS TERMINATED BY和LINES TERMINATED BY子句来指定字段之间的分隔符和行之间的分隔符。

CREATE TABLE employees (
    id INT,
    name STRING,
    age INT,
    department STRING
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
LINES TERMINATED BY '
'
STORED AS TEXTFILE;

在这个例子中，字段之间的分隔符是逗号（,），行之间的分隔符是换行符（

）。

小编有话说

通过本文的介绍，相信大家对Hive的建表语句有了更深入的了解，Hive作为一个强大的数据仓库工具，其灵活性和扩展性使得它在大数据分析领域有着广泛的应用，掌握Hive的建表语句和相关配置，可以帮助我们更好地管理和查询数据，提高数据处理的效率，希望本文能为大家在使用Hive时提供一些帮助和参考。