2021-11-02

最新推荐文章于 2024-08-16 13:48:12 发布

Michelangelo-li

最新推荐文章于 2024-08-16 13:48:12 发布

阅读量924

点赞数

文章标签： hive ddl apache 分区表

本文链接：https://blog.csdn.net/qq_41953856/article/details/121095751

版权

hive 4.0.0 DDL 第一部分

创建表

CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name    -- (Note: TEMPORARY available in Hive 0.14.0 and later)
  [(col_name data_type [column_constraint_specification] [COMMENT col_comment], ... [constraint_specification])]
  [COMMENT table_comment]
  [PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)]
  [CLUSTERED BY (col_name, col_name, ...) [SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS]
  [SKEWED BY (col_name, col_name, ...)                  -- (Note: Available in Hive 0.10.0 and later)]
     ON ((col_value, col_value, ...), (col_value, col_value, ...), ...)
     [STORED AS DIRECTORIES]
  [
   [ROW FORMAT row_format] 
   [STORED AS file_format]
     | STORED BY 'storage.handler.class.name' [WITH SERDEPROPERTIES (...)]  -- (Note: Available in Hive 0.6.0 and later)
  ]
  [LOCATION hdfs_path]
  [TBLPROPERTIES (property_name=property_value, ...)]   -- (Note: Available in Hive 0.6.0 and later)
  [AS select_statement];   -- (Note: Available in Hive 0.5.0 and later; not supported for external tables)
 
CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name
  LIKE existing_table_or_view_name
  [LOCATION hdfs_path];
 
data_type
  : primitive_type
  | array_type
  | map_type
  | struct_type
  | union_type  -- (Note: Available in Hive 0.7.0 and later)
 
primitive_type
  : TINYINT
  | SMALLINT
  | INT
  | BIGINT
  | BOOLEAN
  | FLOAT
  | DOUBLE
  | DOUBLE PRECISION -- (Note: Available in Hive 2.2.0 and later)
  | STRING
  | BINARY      -- (Note: Available in Hive 0.8.0 and later)
  | TIMESTAMP   -- (Note: Available in Hive 0.8.0 and later)
  | DECIMAL     -- (Note: Available in Hive 0.11.0 and later)
  | DECIMAL(precision, scale)  -- (Note: Available in Hive 0.13.0 and later)
  | DATE        -- (Note: Available in Hive 0.12.0 and later)
  | VARCHAR     -- (Note: Available in Hive 0.12.0 and later)
  | CHAR        -- (Note: Available in Hive 0.13.0 and later)
 
array_type
  : ARRAY < data_type >
 
map_type
  : MAP < primitive_type, data_type >
 
struct_type
  : STRUCT < col_name : data_type [COMMENT col_comment], ...>
 
union_type
   : UNIONTYPE < data_type, data_type, ... >  -- (Note: Available in Hive 0.7.0 and later)
 
row_format
  : 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]   -- (Note: Available in Hive 0.13 and later)
  | SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, ...)]
 
file_format:
  : SEQUENCEFILE
  | TEXTFILE    -- (Default, depending on hive.default.fileformat configuration)
  | RCFILE      -- (Note: Available in Hive 0.6.0 and later)
  | ORC         -- (Note: Available in Hive 0.11.0 and later)
  | PARQUET     -- (Note: Available in Hive 0.13.0 and later)
  | AVRO        -- (Note: Available in Hive 0.14.0 and later)
  | JSONFILE    -- (Note: Available in Hive 4.0.0 and later)
  | INPUTFORMAT input_format_classname OUTPUTFORMAT output_format_classname
 
column_constraint_specification:
  : [ PRIMARY KEY|UNIQUE|NOT NULL|DEFAULT [default_value]|CHECK  [check_expression] ENABLE|DISABLE NOVALIDATE RELY/NORELY ]
 
default_value:
  : [ LITERAL|CURRENT_USER()|CURRENT_DATE()|CURRENT_TIMESTAMP()|NULL ] 
 
constraint_specification:
  : [, PRIMARY KEY (col_name, ...) DISABLE NOVALIDATE RELY/NORELY ]
    [, PRIMARY KEY (col_name, ...) DISABLE NOVALIDATE RELY/NORELY ]
    [, CONSTRAINT constraint_name FOREIGN KEY (col_name, ...) REFERENCES table_name(col_name, ...) DISABLE NOVALIDATE 
    [, CONSTRAINT constraint_name UNIQUE (col_name, ...) DISABLE NOVALIDATE RELY/NORELY ]
    [, CONSTRAINT constraint_name CHECK [check_expression] ENABLE|DISABLE NOVALIDATE RELY/NORELY ]

CREATE TABLE使用指定的名称创建一个表。如果已经存在同名的表或视图，则会抛出错误。可以使用IF NOT EXISTS跳过该错误。
	·表名和列名不区分大小写，但SerDe和property name区分大小写
		·在Hive 0.12及更早的版本中，表和列名只允许使用字母数字和下划线字符。
		·在Hive 0.13及以后版本中，列名可以包含任何Unicode字符，但是点(.)和冒号(:)在查询时会出错，因此在Hive 1.2.0中禁用。用反勾号(')指定的任何列名都按字面处理。在反勾号字符串中，使用双反勾号(``)来表示反勾号字符。反勾号还允许对表和列标识符使用保留关键字。
		·要恢复到0.13.0的将列名限制为字母数字和下划线字符，可以通过配置属性hive.support. quotes .identifiers设置为none。在此配置中，带有反引号的名称被解释为正则表达式。
	·Table and column comments：字符串文字(单引号)。
	·没有使用EXTERNAL子句创建的表称为内部表（也叫管理表），因为Hive管理它的数据。要确定一个表是内部的还是外部的，可以使用命令DESCRIBE EXTENDED table_name来确定tableType。
	·TBLPROPERTIES子句允许使用自己的property_name=property_value对标记表定义。有一些预定义的表属性，如last_modified_user和last_modified_time，这些属性是由Hive自动添加和管理的。其他预定义的表属性包括:
		·TBLPROPERTIES ("comment"="table_comment")：表的注释
		·TBLPROPERTIES ("hbase.table.name"="table_name") 
		·TBLPROPERTIES ("immutable"="true") or ("immutable"="false") in release 0.13.0+ (HIVE-6406) –immutable：不可变的。immutable为"true"则不开启从查询中插入数据。immutable为"false"则开启从查询中插入数据。
		·TBLPROPERTIES ("orc.compress"="ZLIB") or ("orc.compress"="SNAPPY") or ("orc.compress"="NONE") and other ORC properties – 选择压缩方式。
		·TBLPROPERTIES ("transactional"="true") or ("transactional"="false") in release 0.14.0+, the default is "false" – 是否开启事务，默认为"false"。
		·TBLPROPERTIES ("NO_AUTO_COMPACTION"="true") or ("NO_AUTO_COMPACTION"="false"), the default is "false" – 是否开启自动压缩。默认为"false".
		·TBLPROPERTIES ("compactor.mapreduce.map.memory.mb"="mapper_memory") – 设置mapper内存
		·TBLPROPERTIES ("compactorthreshold.hive.compactor.delta.num.threshold"="threshold_num") – 开启触发压缩的最小增量目录。
		·TBLPROPERTIES ("compactorthreshold.hive.compactor.delta.pct.threshold"="threshold_pct") – see Hive Transactions. 如果增量文件的大小与基本文件的大小之比大于50%，则触发主压缩
		·TBLPROPERTIES ("auto.purge"="true") or ("auto.purge"="false") in release 1.2.0+ (HIVE-9118) –当设置为ture时，删除或者覆盖的数据会不经过回收站，直接被删除。配置了此属性会影响到这些操作： Drop Table, Drop Partitions, Truncate Table,Insert Overwrite。
		·TBLPROPERTIES ("EXTERNAL"="TRUE") in release 0.6.0+ (HIVE-1329) – 将一个表改为外部表，反之则为"false".
		·TBLPROPERTIES ("external.table.purge"="true")在4.0.0+版本(HIVE-19981)中设置外部表时也会删除数据，不只是会删除元数据。
		·要为表指定一个数据库，可以在CREATE table语句之前执行USE database_name语句(在Hive 0.6及以后版本中)，或者用一个数据库名称来限定表名(在Hive 0.7及以后版本中使用"database_name.table.name")。关键字“default”可以用于默认数据库。

Hive表的存储格式

存储格式	描述
STORED BY	由非本机表格式存储。创建或链接非本地表，例如HBase、Druid或Accumulo支持的表。
STORED AS RCFILE	列式记录文件，是一种类似于SequenceFile的键值对（Key/Value Pairs）数据文件。详情可点击此处
STORED AS TEXTFILE	存储为纯文本文件。TEXTFILE是默认的文件格式，配置参数hive.default.fileformat进行更改设置。使用DELIMITED子句读取带分隔符的文件。通过使用’ escape by ‘子句(如’’)启用分隔符转义。如果要处理可能包含这些分隔符的数据，则需要进行转义。自定义NULL格式也可以使用’NULL DEFINED AS’子句(默认为’\N’)来指定。 (Hive 4.0)假设表中所有的二进制列都是base64编码。将数据读取为原始字节: TBLPROPERTIES(“hive.serialization.decode.binary.as.base64”=“false”)
STORED AS PARQUET	存储为Parquet格式，用于Hive 0.13.0及后续版本
STORED AS ORC	存储为ORC文件格式。支持ACID事务和基于成本的优化(CBO)。存储列级元数据。
STORED AS JSONFILE	在Hive 4.0.0及以上版本中以Json文件格式存储。
STORED AS SEQUENCEFILE	存储为sequence文件。
STORED AS AVRO	以Avro格式存储，用于Hive 0.14.0及更高版本
INPUTFORMAT and OUTPUTFORMAT	在file_format中以字符串指定相应的InputFormat和OutputFormat类。

行格式和序列化器

行格式	描述
RegEx 正则表达式 :`ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe' WITH SERDEPROPERTIES( "input.regex" = "<regex>" ) STORED AS TEXTFILE;`	存储为纯文本文件，由正则表达式翻译。下面的示例定义了一个Apache Weblog格式的表。 `CREATE TABLE apachelog ( host STRING,identity STRING,user STRING,time STRING,request STRING, status STRING,size STRING,referer STRING,agent STRING) ROW FORMAT SERDE'org.apache.hadoop.hive.serde2.RegexSerDe' WITH SERDEPROPERTIES ("input.regex" = "([^]) ([^]) ([^]) (-\|\\[^\\]\\]) ([^ \"]\|\"[^\"]\") (-\|[0-9]) (-\|[0-9])(?: ([^ \"]\|\".\") ([^ \"]\|\".\"))?") STORED AS TEXTFILE;`
JSON : `ROW FORMAT SERDE'org.apache.hive.hcatalog.data.JsonSerDe'STORED AS TEXTFILE`	存储为JSON格式的纯文本文件。JSON文件的JsonSerDe在Hive 0.12及更高版本中可用。在某些发行版中，需要引用hive-hcatalog-core.jar ： ADD JAR /usr/lib/hive-hcatalog/lib/hive-hcatalog-core.jar; 示例：`CREATE TABLE my_table(a string, b bigint, ...) ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe' STORED AS TEXTFILE;` JSON文件的JsonSerDe在Hive 0.12及更高版本中可用。在Hive 3.0 之后使用JSON的SerDe ：`CREATE TABLE my_table(a string, b bigint, ...) ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.JsonSerDe' STORED AS TEXTFILE;` 在Hive 4.0之后使用JSON的SerDe： `CREATE TABLE my_table(a string, b bigint, ...) STORED AS JSONFILE;`
CSV/TSV： `ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde' STORED AS TEXTFILE`	存储为CSV / TSV格式的纯文本文件。CSVSerde在Hive 0.14及更高版本中可用。创建一个以TAB分割的文件：`CREATE TABLE my_table(a string, b string, ...)ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'WITH SERDEPROPERTIES ( "separatorChar" = "\t","quoteChar" = "'","escapeChar" = "\\" ) STORED AS TEXTFILE;` CSV Serde默认是以逗号分割 `DEFAULT_ESCAPE_CHARACTER \ 默认的转义字符 DEFAULT_QUOTE_CHARACTER " 默认的引用字符 DEFAULT_SEPARATOR ,默认的分隔符` 。此SerDe适合大多数CSV数据,但不能处理嵌入式换行。要使用SerDe，请指定完全限定类名org.apache.hadoop.hive.serde2.OpenCSVSerde。这个SerDe将所有列都视为String类型。即使使用这个SerDe创建一个非字符串列类型的表，使用description table 命令输出也会显示字符串列类型。

分区表

分区表可以使用Partitioned BY子句创建。一个表可以有一个或多个分区列，并且为分区列中的每个不同的值组合创建一个单独的数据目录。此外，表或分区可以使用CLUSTERED BY列进行存储，数据可以通过SORT BY列在该存储桶中进行排序。这可以提高某些类型查询的性能。

如果，在创建分区表时得到这个错误:“FAILED: error in semantic analysis: Column repeated in partitioning columns，”这意味着你正在设置的分区列包含在表的本身中。在表中你可能真的定义了此列。创建的分区会生成一个伪列，可以在该伪列上进行查询，因此必须将该列重命名为其他字段(用户不应该在该字段上进行查询!)

例如，假设原始的未分区表有三列:id、date和name。

id     int,
date   date,
name   varchar

现在要按日期进行分区。Hive定义可以使用“dtDontQuery”作为列名，这样“date”就可以用于分区(和查询)。

create table table_name (
  id                int,
  dtDontQuery       string,
  name              string
)
partitioned by (date string)

创建一个分区表语句示例：

CREATE TABLE page_view(viewTime INT, userid BIGINT,
     page_url STRING, referrer_url STRING,
     ip STRING COMMENT 'IP Address of the User')
 COMMENT 'This is the page view table'
 PARTITIONED BY(dt STRING, country STRING)
 STORED AS SEQUENCEFILE;

等价于：

CREATE TABLE page_view(viewTime INT, userid BIGINT,
     page_url STRING, referrer_url STRING,
     ip STRING COMMENT 'IP Address of the User')
 COMMENT 'This is the page view table'
 PARTITIONED BY(dt STRING, country STRING)
 ROW FORMAT DELIMITED
   FIELDS TERMINATED BY '\001'
STORED AS SEQUENCEFILE;

外部表

EXTERNAL关键字可以创建一个表并提供一个LOCATION，这样Hive就不会为该表使用默认的位置。如果您已经生成了数据，这个就能排上用场了。

当删除一个EXTERNAL表时，表中的数据不会从文件系统中删除。

在Hive 4.0.0通过设置表属性external.table.purge=true也将删除数据。

被HiveStrictManagedMigration工具转换为外部表的托管表应该设置为在表被删除时删除数据。

创建外部表示例：

CREATE EXTERNAL TABLE page_view(viewTime INT, userid BIGINT,
     page_url STRING, referrer_url STRING,
     ip STRING COMMENT 'IP Address of the User',
     country STRING COMMENT 'country of origination')
 COMMENT 'This is the staging page view table'
 ROW FORMAT DELIMITED FIELDS TERMINATED BY '\054'
 STORED AS TEXTFILE
 LOCATION '<hdfs_location>';

通过select创建表

通过create-table-as-select (cas)语句使用查询的结果创建和填充表。CTAS创建的表是原子的，这意味着在填充所有查询结果之前，其他用户不会看到该表。因此，其他用户要么看到包含完整查询结果的表，要么根本看不到此表。

在CTAS中有两个部分，SELECT部分可以是HiveQL支持的任何SELECT语句。CTAS 的 CREATE 部分从 SELECT 部分获取结果，并创建具有其他表属性（例如 SerDe 和存储格式）的目标表。

从Hive 3.2.0(Hive -20241)开始，CTAS语句可以为目标表定义分区规范。

CTAS有以下限制:
 - 目标表不能是外部表。
 - 目标表不能是列表bucket表。

示例：

CREATE TABLE new_key_value_store
   ROW FORMAT SERDE "org.apache.hadoop.hive.serde2.columnar.ColumnarSerDe"
   STORED AS RCFile
   AS
SELECT (key % 1024) new_key, concat(key, value) key_value_pair
FROM key_value_store
SORT BY new_key, key_value_pair;

上面的 CTAS 语句使用从 SELECT 语句的结果派生的模式（new_key DOUBLE，key_value_pair STRING）创建目标表 new_key_value_store。如果 SELECT 语句没有指定列别名，列名将自动分配给 _col0、_col1 和 _col2 等。此外，新的目标表是使用特定的 SerDe 和独立于源表中的存储格式创建的。

从 Hive 0.13.0 开始，SELECT 语句可以包含一个或多个公共表表达式 (CTEs)。

能够从一张表中选择数据到另一张表是 Hive 最强大的功能之一。在执行查询时，Hive 会完成数据从源格式到目标格式的转换。

通过like创建表

CREATE TABLE 的 LIKE 形式可以精确复制现有表定义（不复制其数据）。与 CTAS 不同的是，下面的语句创建了一个新的 empty_key_value_store 表，其定义与现有的 key_value_store 表名以外的所有细节完全匹配。新表不包含任何行。

CREATE TABLE empty_key_value_store
LIKE key_value_store [TBLPROPERTIES (property_name=property_value, ...)];

在Hive 0.8.0之前，CREATE TABLE LIKE view_name会复制视图。在Hive 0.8.0及后续版本中，CREATE TABLE LIKE view_name通过采用view_name模式(字段和分区列)创建一个表，使用默认的SerDe和文件格式。

桶排序表

CREATE TABLE page_view(viewTime INT, userid BIGINT,
     page_url STRING, referrer_url STRING,
     ip STRING COMMENT 'IP Address of the User')
 COMMENT 'This is the page view table'
 PARTITIONED BY(dt STRING, country STRING)
 CLUSTERED BY(userid) SORTED BY(viewTime) INTO 32 BUCKETS
 ROW FORMAT DELIMITED
   FIELDS TERMINATED BY '\001'
   COLLECTION ITEMS TERMINATED BY '\002'
   MAP KEYS TERMINATED BY '\003'
 STORED AS SEQUENCEFILE;

在上面的示例中，page_view 表被分桶（由userid 分组），并且在每个桶中，数据按 viewTime 的升序排序。这样的形式允许用户对聚集列(在本例中为 userid)进行高效抽样。排序属性允许内部运算符在评估查询时利用更有效的数据结构，提高查询效率。如果任何一个列是list或map，则可以使用 MAP KEYS 和 COLLECTION ITEMS 关键字。

CLUSTERED BY 和 SORTED BY 命令不影响数据插入表的方式——只影响读取数据的方式。这意味着用户必须小心正确插入数据，将reducer 的数量指定为等于bucket 的数量，并在查询中使用CLUSTER BY 和SORT BY 命令。