Hive建表语句详解

一、Hive建表语句详解

Hive是基于Hadoop的开源数据仓库，可以处理大规模的数据。在Hive中，按照表的方式来存储数据。创建表时需要指定表的名称、列名、数据类型等信息。Hive建表语句的基本语法如下：

CREATE TABLE [IF NOT EXISTS] table_name
(column_name data_type [COMMENT column_comment], ...)
[COMMENT table_comment]
[ROW FORMAT row_format]
[STORED AS file_format]

以上语法中，CREATE TABLE为创建表的指令，IF NOT EXISTS用于判断表是否已经存在，table_name为创建的表名称。column_name和data_type分别为列名和数据类型，可以指定多个列。

除此之外，还可以使用COMMENT来为表和列添加注释，使用ROW FORMAT指定行的格式（默认为Delimited）和分隔符（默认为'\t'），使用STORED AS指定存储的文件格式（默认为TEXTFILE）。

二、Hive建表语句指定空格分隔符

在默认情况下，Hive的行格式为Delimited，分隔符为'\t'。如果需要使用空格作为分隔符，可以按照以下语法指定：

ROW FORMAT DELIMITED
FIELDS TERMINATED BY ' '

以上语法中，ROW FORMAT DELIMITED用于指定行格式，FIELDS TERMINATED BY ' '用于指定分隔符为一个空格。同样的，也可以使用其他字符作为分隔符。

三、Hive建表语句注释

Hive建表语句不仅可以添加表级注释，还可以为列添加列级注释。注释在表的描述和数据分析中非常重要。

例如，下面是为表和列分别添加注释的示例：

CREATE TABLE IF NOT EXISTS student(
id INT COMMENT '学生编号',
name STRING COMMENT '学生姓名',
age INT COMMENT '学生年龄'
) COMMENT '学生信息表'
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ' ';

DESCRIBE STUDENT;

以上代码中，为表添加了注释“学生信息表”，同时为每个列添加了列级注释。

四、Hive建表语句string

Hive中的STRING类型表示一个字符串，可以使用单引号或双引号来表示，例如：

CREATE TABLE IF NOT EXISTS test_string(
id INT,
name STRING
) ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t';

INSERT INTO TABLE test_string VALUES
(1, 'Tom'),
(2, "Jerry");

SELECT * FROM test_string;

以上代码中，创建了一个名为“test_string”的表，包含两个列id和name，其中name使用了单引号和双引号分别表示字符串。

五、Hive查看建表语句

在Hive中，可以使用DESCRIBE命令查看表的结构，也可以使用SHOW CREATE TABLE命令查看建表语句。例如：

SHOW CREATE TABLE student;

以上代码将输出创建表的完整语句，方便查看表的结构和内容。

六、Hive分区表建表语句

Hive支持将表按照某个字段进行分区，以提高查询效率。分区表建表语法如下：

CREATE TABLE IF NOT EXISTS partition_table(
id INT,
name STRING
) PARTITIONED BY (city STRING, country STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t';

以上代码中，通过PARTITIONED BY关键字指定分区的字段为city和country，此时表将以分区方式存储数据。

七、Hive建外部表语句

与传统的表不同，外部表不会在Hive的数据目录下创建物理文件，而是引用已经存在于HDFS集群中的文件。外部表的建表语句如下：

CREATE EXTERNAL TABLE IF NOT EXISTS external_table(
id INT,
name STRING
) ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
LOCATION '/user/hadoop/external_table/';

以上代码中，LOCATION指定了HDFS集群中文件的位置。创建完外部表后，可以在Hive中对其进行查询、统计等操作。

八、Hive建表语句案例

以下是一个实际数据建表的案例，该表用于存储用户的基本信息以及订单信息：

CREATE TABLE IF NOT EXISTS user_order_stats(
user_id INT COMMENT '用户编号',
age INT COMMENT '用户年龄',
gender STRING COMMENT '用户性别',
order_id INT COMMENT '订单编号',
order_time STRING COMMENT '下单时间',
order_amount DOUBLE COMMENT '订单金额'
)
COMMENT '用户订单统计表'
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
STORED AS ORC;

以上建表语句指定了表名为“user_order_stats”，包含6个列，分别为用户编号、年龄、性别、订单编号、下单时间和订单金额。使用了ORC作为存储格式，ORC可以提高查询效率。

九、Hive建表语句导出

在Hive中，可以使用INSERT OVERWRITE命令将表中的数据导出到HDFS文件系统中。例如，将上面的表中的数据导出到/user/hadoop/user_order_stats.txt文件中：

INSERT OVERWRITE LOCAL DIRECTORY '/user/hadoop/output'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
SELECT *
FROM user_order_stats;

以上代码中，INSERT OVERWRITE LOCAL DIRECTORY指定了导出的目录为/user/hadoop/output，ROW FORMAT DELIMITED和FIELDS TERMINATED BY指定了导出文件的分隔符等信息。通过SELECT命令选择要导出的数据。

十、Hive建表语句分隔符选取

Hive建表语句中可以指定分隔符，常见的分隔符有'\t'和','等。选择分隔符需要考虑到数据的特点和需要处理的问题。例如，'\t'适用于列之间有任意空格，且数据中不包含制表符的文本数据；','适用于数据中不包含逗号的文本数据。

需要注意的是，在选择分隔符时，也需要考虑到不同操作系统之间的差异。对于Windows系统，需要使用'\r\n'作为分隔符，而对于Linux系统，则使用'\n'或'\r'作为分隔符。