Hive建表语句详解及创建表时的分隔符使用

Apache Hive 是一个基于 Hadoop 的数据仓库工具，广泛应用于大数据处理和分析。Hive 提供类似 SQL 的查询语言 HiveQL，使得用户可以方便地在 Hadoop 上进行数据操作。在 Hive 中，创建表是进行数据存储和管理的基础操作。本文将详细介绍 Hive 的建表语句，并深入探讨在创建表时如何使用分隔符。

一、Hive建表语句

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

CREATE [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 '\n'
STORED AS file_format
LOCATION 'hdfs_path'
TBLPROPERTIES ('property_name'='property_value', ...);

1. 关键字解释

• CREATE TABLE：用于创建一个新的表。
• EXTERNAL：可选关键字，表示创建一个外部表。外部表的数据文件存储在 Hive 之外的位置，Hive 只管理表的元数据。
• IF NOT EXISTS：可选关键字，如果表已经存在，则不执行创建操作。
• table_name：表的名称。
• column1 datatype1：列名及其数据类型。常见的数据类型包括 INT, STRING, FLOAT, DOUBLE, BOOLEAN, DATE 等。
• COMMENT：为表添加注释。
• PARTITIONED BY：用于定义表的分区列。
• CLUSTERED BY：用于定义表的桶列。
• ROW FORMAT DELIMITED：指定行的格式为分隔格式。
• FIELDS TERMINATED BY：字段之间的分隔符。
• LINES TERMINATED BY：行之间的分隔符，通常为换行符 \n。
• STORED AS：指定数据文件的存储格式，如 TEXTFILE、SEQUENCEFILE、ORC、PARQUET 等。
• LOCATION：指定外部表的数据存储路径。
• TBLPROPERTIES：为表设置一些自定义属性。

二、创建表时使用分隔符

在 Hive 中，分隔符用于指定数据文件中字段和记录的分隔方式。通常，数据文件是以文本格式存储的，每行代表一条记录，每个字段之间用特定的字符进行分隔。创建表时，指定合适的分隔符对于正确解析和处理数据至关重要。

1. FIELDS TERMINATED BY

FIELDS TERMINATED BY

用于指定字段之间的分隔符。常见的分隔符有逗号（

,

）、制表符（

\t

）、空格（）等。例如：

• 逗号分隔：

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

• 制表符分隔：

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

• 空格分隔：

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

在选择分隔符时，应确保数据文件中的内容不会包含该分隔符，以避免解析错误。

2. LINES TERMINATED BY

LINES TERMINATED BY

用于指定行之间的分隔符。通常情况下，行分隔符是换行符（

\n

）。例如：

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

三、创建表的实际案例

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

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

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

CREATE TABLE employees (
  id INT,
  name STRING,
  age INT,
  department STRING
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
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 '\n'
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'
LINES TERMINATED BY '\n'
STORED AS TEXTFILE
LOCATION 'hdfs://path/to/user_behavior_data';

在该外部表中，数据文件存储在指定的 HDFS 目录中，Hive 只管理表的元数据。

四、深入理解分隔符的选择

在创建 Hive 表时，选择合适的分隔符对于数据解析和处理至关重要。以下是一些选择分隔符时需要考虑的因素：

1. 数据内容

不同的数据文件可能包含不同的字符。在选择分隔符时，应确保数据内容不会包含该分隔符。例如，如果数据文件中的内容可能包含逗号，则不应使用逗号作为字段分隔符。

2. 数据格式

根据数据文件的格式选择合适的分隔符。例如，CSV 文件通常以逗号分隔，而 TSV 文件则以制表符分隔。选择与数据文件格式一致的分隔符可以避免解析错误。

3. 数据处理要求

不同的分隔符可能会影响数据处理的效率。例如，制表符分隔的文件在某些情况下可能比空格分隔的文件更容易解析。因此，在选择分隔符时，也需要考虑数据处理的要求。

4. 数据导入和导出

在将数据从其他系统导入到 Hive 或将数据从 Hive 导出到其他系统时，选择合适的分隔符可以确保数据的一致性和可读性。例如，从数据库导出的 CSV 文件可以直接导入到 Hive 表中，而无需进行额外的格式转换。

五、分隔符的实际应用

为了更好地理解分隔符的实际应用，下面提供一些实际应用场景。

场景 1：从 CSV 文件导入数据

假设我们有一个包含员工信息的 CSV 文件，每行记录以逗号分隔。我们可以创建一个 Hive 表，并将 CSV 文件中的数据导入到该表中：

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

LOAD DATA INPATH 'hdfs://path/to/employees.csv' INTO TABLE employees;

场景 2：从 TSV 文件导入数据

假设我们有一个包含用户行为信息的 TSV 文件，每行记录以制表符分隔。我们可以创建一个 Hive 表，并将 TSV 文件中的数据导入到该表中：

CREATE TABLE user_behavior (
  user_id INT,
  action STRING,
  timestamp STRING
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\n'
STORED AS TEXTFILE;

LOAD DATA INPATH 'hdfs://path/to/user_behavior.tsv' INTO TABLE user_behavior;

场景 3：从定长文件导入数据

假设我们有一个包含订单信息的定长文件，每行记录的字段长度固定。我们可以创建一个 Hive 表，并使用自定义的 SerDe（序列化和反序列化）来解析数据：

CREATE TABLE orders (
  order_id INT,
  customer_id INT,
  product_id INT,
  amount DOUBLE
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe'
WITH SERDEPROPERTIES (
  "input.regex" = "(.{10})(.{10})(.{10})(.{10})",
  "output.format.string" = "%1$s %2$s %3$s %4$s"
)
STORED AS TEXTFILE;

LOAD DATA INPATH 'hdfs://path/to/orders.txt' INTO TABLE orders;

六、总结

Hive 建表语句和分隔符的使用是 Hive 数据管理的重要组成部分。在创建表时，选择合适的分隔符对于正确解析和处理数据至关重要。通过详细了解 Hive 建表语句的各个组成部分和分隔符的使用方法，可以更好地管理和处理大数据集。

本文通过多个实际案例和应用场景，介绍了如何创建内部表和外部表，如何选择和使用分隔符，以及在不同场景下的最佳实践。希望这些内容能帮助读者更好地理解和应用 Hive 建表语句和分隔符，从而提高数据处理和分析的效率。