使用Hive语法创建OBS表

功能描述

使用Hive语法创建OBS表。DataSource语法和Hive语法主要区别在于支持的表数据存储格式范围、支持的分区数等有差异，详细请参考语法格式和注意事项说明。

推荐使用OBS并行文件系统进行存储。并行文件系统是一种高性能文件系统，提供毫秒级别访问时延，TB/s级别带宽和百万级别的IOPS，适用于大数据交互式分析场景。

注意事项

• 创建表时会统计大小。
• 添加数据时不会修改大小。
• 如需查看表大小可以通过OBS查看。
• CTAS建表语句不能指定表的属性。
• 关于分区表的使用说明：
  • 创建分区表时，PARTITONED BY中指定分区列必须是不在表中的列，且需要指定数据类型。分区列支持string, boolean, tinyint, smallint, short, int, bigint, long, decimal, float, double, date, timestamp等hive开源支持的类型。
  • 支持指定多个分区字段，分区字段只需在PARTITIONED BY关键字后指定，不能像普通字段一样在表名后指定，否则将出错。
  • 单表分区数最多允许200000个。
  • Spark 3.3及以上版本支持使用Hive语法的CTAS语句创建分区表。
• 关于创建表时设置多字符的分隔符：
  • 只有指定ROW FORMAT SERDE为org.apache.hadoop.hive.contrib.serde2.MultiDelimitSerDe时，字段分隔符才支持设置为多字符。
  • 只有Hive OBS表支持在建表时指定多字符的分隔符，Hive DLI表不支持在建表时指定多字符的分隔符。
  • 指定了多字符分隔的表不支持INSERT、IMPORT等写数语句。如需添加数据，请将数据文件直接放到表对应的OBS路径下即可，例如示例7：创建表并设置多字符的分割符中，将数据文件放到obs://bucketName/filePath下。

语法格式

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

CREATE [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name 
  [(col_name1 col_type1 [COMMENT col_comment1], ...)]
  [COMMENT table_comment] 
  [PARTITIONED BY (col_name2 col_type2, [COMMENT col_comment2], ...)] 
  [ROW FORMAT row_format]
  [STORED AS file_format] 
  LOCATION 'obs_path'
  [TBLPROPERTIES (key = value)]
  [AS select_statement]
row_format:
  : SERDE serde_cls [WITH SERDEPROPERTIES (key1=val1, key2=val2, ...)]
  | 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]

关键字

• EXTERNAL：指创建OBS表。
• IF NOT EXISTS：指定该关键字以避免表已经存在时报错。
• COMMENT：字段或表描述。
• PARTITIONED BY：指定分区字段。
• ROW FORMAT：行数据格式。
• STORED AS：指定所存储的文件格式，当前该关键字只支持指定TEXTFILE, AVRO, ORC, SEQUENCEFILE, RCFILE, PARQUET格式。
• LOCATION：指定OBS的路径。创建OBS表时必须指定此关键字。
• TBLPROPERTIES：TBLPROPERTIES子句允许用户给表添加key/value的属性。
  • 开启数据多版本功能，用于表数据的备份与恢复。开启多版本功能后，在进行删除或修改表数据时（insert overwrite或者truncate操作），系统会自动备份历史表数据并保留一定时间，后续您可以对保留周期内的数据进行快速恢复，避免因误操作而丢失数据。多版本功能SQL语法请参考开启或关闭数据多版本和多版本备份恢复数据。

    创建OBS表时，通过指定TBLPROPERTIES ("dli.multi.version.enable"="true")开启DLI数据多版本功能，具体可以参考示例说明。

    表1 TBLPROPERTIES主要参数说明

    key值	value说明
    dli.multi.version.enable	true：开启DLI数据多版本功能。 false：关闭DLI数据多版本功能。
    comment	表描述信息。
    orc.compress	orc存储格式表的一个属性，用来指定orc存储的压缩方式。支持取值为： ZLIB SNAPPY NONE PARQUET
    auto.purge	当设置为true时，删除或者覆盖的数据会不经过回收站，直接被删除。

• AS：使用CTAS创建表。
• ROW FORMAT SERDE为org.apache.hadoop.hive.contrib.serde2.MultiDelimitSerDe时，字段分隔符才支持设置为多字符。使用方法参考示例7：创建表并设置多字符的分割符。

参数说明

示例1：创建OBS非分区表

示例说明：创建名为table1的OBS非分区表，并用STORED AS关键字指定该表的存储格式为orc格式。

在您的实际使用中，可以将OBS表存储为textfile, avro, orc, sequencefile, rcfile, parquet等类型。

1
2
3
4
5
6

CREATE TABLE IF NOT EXISTS table1 (
    col_1   STRING,
    col_2   INT
)
STORED AS orc
LOCATION 'obs://bucketName/filePath';

示例2：创建OBS分区表

示例说明：创建一个名为student的分区表，该分区表使用院系编号（facultyNo）和班级编号（classNo）进行分区，该student表会同时按照不同的院系编号（facultyNo）和不同的班级编号（classNo）分区。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

CREATE TABLE IF NOT EXISTS student(
    id      INT,
    name    STRING
)
STORED AS avro
LOCATION 'obs://bucketName/filePath'
PARTITIONED BY (
    facultyNo   INT,
    classNo     INT
);

示例3：使用CTAS语句将源表的全部数据或部分数据创建新的OBS表

示例说明：根据示例1：创建OBS非分区表中创建的OBS表table1，使用CTAS语法将table1中的数据复制到table1_ctas表中。

在使用CTAS建表的时候，可以忽略被复制的表在建表时所使用的语法，即不论在创建table1时使用的是何种语法，都可以使用DataSource语法的CTAS创建table1_ctas。

此外，本例中table1中OBS表的存储格式为orc，而table1_ctas表的存储格式可以为sequencefile或者parquet，即CTAS创建的表存储格式可以不同于原表。

在AS关键字后使用SELECT语句选择需要的数据插入到table1_ctas表中。

SELECT语法为：SELECT <列名称> FROM <表名称> WHERE <相关筛选条件>。

• 示例中使用“SELECT * FROM table1”，'*'表示会从table1中选择所有列，并将table1中所有数据插入到table1_ctas表中。

  1 2 3 4 5 6

  CREATE TABLE IF NOT EXISTS table1_ctas STORED AS sequencefile LOCATION 'obs://bucketName/filePath' AS SELECT * FROM table1;

• 若需要按照自定义方式筛选数据插入table1_ctas中，可以使用如下的SELECT语句“SELECT col_1 FROM table1 WHERE col_1 = 'Ann' ”，这样就可以通过执行SELECT语句从table1中单独选定col_1，并只将其中值等于'Ann'的数据插入到table1_ctas中。

  1 2 3 4 5 6 7

  CREATE TABLE IF NOT EXISTS table1_ctas USING parquet OPTIONS (path 'obs:// bucketName/filePath') AS SELECT col_1 FROM table1 WHERE col_1 = 'Ann';

示例4：创建OBS非分区表，并自定义列字段数据类型

示例说明：创建名为table2的OBS非分区表，您可以根据业务需求自定义列字段的原生数据类型：

• 与文字字符有关可以使用STRING、CHAR或者VARCHAR。
• 与时间有关的可以使用TIMESTAMP、DATE。
• 与整数有关的可以使用INT、SMALLINT/SHORT、BIGINT/LONG、TINYINT。
• 涉及小数运算可以使用FLOAT、DOUBLE、DECIMAL。
• 若数据只涉及逻辑开关可以使用BOOLEAN类型。

具体使用方法与明细可以参照“数据类型 >原生数据类型”。

请参考原生数据类型。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

CREATE TABLE IF NOT EXISTS table2 (
    col_01  STRING,
    col_02  CHAR (2),
    col_03  VARCHAR (32),
    col_04  TIMESTAMP,
    col_05  DATE,
    col_06  INT,
    col_07  SMALLINT,
    col_08  BIGINT,
    col_09  TINYINT,
    col_10  FLOAT,
    col_11  DOUBLE,
    col_12  DECIMAL (10, 3),
    col_13  BOOLEAN
)
STORED AS parquet
LOCATION 'obs://bucketName/filePath';

示例5：创建OBS分区表，自定义表的TBLPROPERTIES参数

示例说明：创建名为table3，并以col_3为分区依据的OBS分区表。在TBLPROPERTIES中配置dli.multi.version.enable、comment、orc.compress和auto.purge。

• dli.multi.version.enable：本例配置为true，即代表开启DLI数据多版本功能，用于表数据的备份与恢复。
• comment：表描述信息，comment描述信息支持后续修改。
• orc.compress：指定orc存储的压缩方式，本例定义为ZLIB。
• auto.purge：本例配置为true，即删除或者覆盖的数据会不经过回收站，直接被删除。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

CREATE TABLE IF NOT EXISTs table3 (
    col_1 STRING,
    col_2 STRING
)
PARTITIONED BY (col_3 DATE)
STORED AS rcfile
LOCATION 'obs://bucketName/filePath'
TBLPROPERTIES (
    dli.multi.version.enable  = true,
    comment                   = 'Created by dli',
    orc.compress              = 'ZLIB',
    auto.purge                = true
);

示例6：创建textfile格式的非分区表，并设置ROW FORMAT

示例说明：创建名为table4的textfile类型的非分区表，并设置ROW FORMAT（ROW FORMAT功能只支持textfile类型的表）。

• FIELDS：字段表格中的列，每个字段有一个名称和数据类型，表中字段之间以'/'分隔。
• COLLECTION ITEMS：集合项指的是一组数据中的元素，可以是数组、列表或集合等，表中集合项以'$'分隔。
• MAP KEYS：映射键是一种键值对的数据结构，用于存储一组相关联的数据，表中Map键以'#'分隔。
• LINES：表格中的行，每一行包含一组字段值，表中行以'\n'结束（注意，只支持用'\n'作为行分隔符）。
• NULL：表示缺少值或未知值的特殊值。在表格中，NULL表示该字段没有值或该值未知。如果数据中存在null值，则用字符串“null”表示。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

CREATE TABLE IF NOT EXISTS table4 (
    col_1   STRING,
    col_2   INT
)
STORED AS textfile
LOCATION 'obs://bucketName/filePath'
ROW FORMAT
DELIMITED FIELDS TERMINATED   BY '/'
COLLECTION ITEMS TERMINATED   BY '$'
MAP KEYS TERMINATED           BY '#'
LINES TERMINATED              BY '\n'
NULL DEFINED                  AS 'null';

示例7：创建表并设置多字符的分割符

示例说明：创建了一个名为table5的Hive表。表指定序列化和反序列化类ROW FORMAT SERDE，字段之间的分隔符被设置为/#，并且数据以文本文件格式存储。

• 只有指定ROW FORMAT SERDE为org.apache.hadoop.hive.contrib.serde2.MultiDelimitSerDe时，字段分隔符才支持设置为多字符。
• 只有Hive OBS表支持在建表时指定多字符的分隔符，Hive DLI表不支持在建表时指定多字符的分隔符。
• 指定了多字符分隔的表不支持INSERT、IMPORT等写数语句。如需添加数据，请将数据文件直接放到表对应的OBS路径下即可，本例中，将数据文件放到obs://bucketName/filePath下。
• 本例指定字段分割符 field.delim'为“/#”。
• ROW FORMAT功能只支持textfile类型的表。

  1 2 3 4 5 6 7 8 9 10

  CREATE TABLE IF NOT EXISTS table5 ( col_1 STRING, col_2 INT ) ROW FORMAT SERDE 'org.apache.hadoop.hive.contrib.serde2.MultiDelimitSerDe' WITH SERDEPROPERTIES ( 'field.delim' = '/#' ) STORED AS textfile LOCATION 'obs://bucketName/filePath';