创建 HIVEFORMAT 表

描述

CREATE TABLE 语句使用 Hive 格式定义了一个新表。

语法

CREATE [ EXTERNAL ] TABLE [ IF NOT EXISTS ] table_identifier
[ ( col_name1[:] col_type1 [ COMMENT col_comment1 ], ... ) ]
[ COMMENT table_comment ]
[ PARTITIONED BY ( col_name2[:] col_type2 [ COMMENT col_comment2 ], ... )
| ( col_name1, col_name2, ... ) ]
[ CLUSTERED BY ( col_name1, col_name2, ...)
[ SORTED BY ( col_name1 [ ASC | DESC ], col_name2 [ ASC | DESC ], ... ) ]
INTO num_buckets BUCKETS ]
[ ROW FORMAT row_format ]
[ STORED AS file_format ]
[ LOCATION path ]
[ TBLPROPERTIES ( key1=val1, key2=val2, ... ) ]
[ AS select_statement ]

请注意,列定义子句与 AS SELECT 子句之间的子句可以按任何顺序出现。例如,您可以在 TBLPROPERTIES 后面写评论 table_comment。

参数

示例

--使用 Hive 格式
CREATE TABLE student (id INT, name STRING, age INT) STORED AS ORC;
--使用来自另一张表的数据
CREATE TABLE student_copy STORED AS ORC
AS SELECT * FROM student;
--指定表注释和属性
CREATE TABLE student (id INT, name STRING, age INT)
COMMENT STORED AS ORC
TBLPROPERTIES (=);
--指定表注释和属性,使用不同的语句顺序
CREATE TABLE student (id INT, name STRING, age INT)
STORED AS ORC
TBLPROPERTIES (=)
COMMENT ;
--创建分区表
CREATE TABLE student (id INT, name STRING)
PARTITIONED BY (age INT)
STORED AS ORC;
--创建分区表,使用不同的语句顺序
CREATE TABLE student (id INT, name STRING)
STORED AS ORC
PARTITIONED BY (age INT);
--使用行格式和文件格式
CREATE TABLE student (id INT, name STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY STORED AS TEXTFILE;
--使用复杂数据类型
CREATE EXTERNAL TABLE family(
name STRING,
friends ARRAYSTRING,
children MAPSTRING, INT,
address STRUCTstreet: STRING, city: STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ESCAPED BY \COLLECTION ITEMS TERMINATED BY MAP KEYS TERMINATED BY LINES TERMINATED BY \nNULL DEFINED AS STORED AS TEXTFILE
LOCATION ;
--使用预定义的自定义 SerDe
CREATE TABLE avroExample
ROW FORMAT SERDE STORED AS INPUTFORMAT OUTPUTFORMAT TBLPROPERTIES (=);
--使用个性化自定义 SerDe(我们可能需要先 `ADD JAR xxx.jar` 确保能够找到 serde_class,
--否则可能会遇到 `CLASSNOTFOUND` 异常)
ADD JAR /tmp/hive_serde_example.jar;
CREATE EXTERNAL TABLE family (id INT, name STRING)
ROW FORMAT SERDE STORED AS INPUTFORMAT OUTPUTFORMAT LOCATION ;
--使用 `CLUSTERED BY` 子句创建没有 `SORTED BY` 的桶表
CREATE TABLE clustered_by_test1 (ID INT, AGE STRING)
CLUSTERED BY (ID)
INTO 4 BUCKETS
STORED AS ORC
--使用 `CLUSTERED BY` 子句来创建带有 `SORTED BY` 的桶表
CREATE TABLE clustered_by_test2 (ID INT, NAME STRING)
PARTITIONED BY (YEAR STRING)
CLUSTERED BY (ID, NAME)
SORTED BY (ID ASC)
INTO 3 BUCKETS
STORED AS PARQUET