Hive创建/删除/截断表_external.table.purge

目录

创建表

内部表和外部表

存储格式

行格式& SerDe

Partitioned Tables

External Tables

Create Table As Select (CTAS)

Create Table Like

Bucketed Sorted Tables

Skewed Tables(倾斜表)

Temporary Tables（临时表）

Transactional Tables（事务表）

Constraints（约束）

Drop Table

Truncate Table

---

创建表

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 [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)
  | INPUTFORMAT input_format_classname OUTPUTFORMAT output_format_classname
 
constraint_specification:
  : [, PRIMARY KEY (col_name, ...) DISABLE NOVALIDATE ]
    [, CONSTRAINT constraint_name FOREIGN KEY (col_name, ...) REFERENCES table_name(col_name, ...) DISABLE NOVALIDATE 

• 表名和列名不区分大小写，但是SerDe和属性名区分大小写。
• 表和列注释是字符串文字(单引号)。
• 没有 EXTERNAL 创建的表称为内部表，Hive 管理它的数据。要查明一个表是内部的还是外部的，请在 DESCRIBE EXTENDED table_name 的输出中查找 tableType。
• TBLPROPERTIES 子句允许使用自己的元数据键/值对标记表定义。还存在一些预定义的表属性，如 last_modified_user 和 last_modified_time，它们由Hive自动添加和管理。其他预定义的表属性包括：

1. TBLPROPERTIES ("comment"="table_comment")
2. TBLPROPERTIES ("hbase.table.name"="table_name")
3. TBLPROPERTIES ("immutable"="true") or ("immutable"="false")
4. TBLPROPERTIES ("orc.compress"="ZLIB") or ("orc.compress"="SNAPPY") or ("orc.compress"="NONE")
5. TBLPROPERTIES ("transactional"="true") or ("transactional"="false")
6. TBLPROPERTIES ("NO_AUTO_COMPACTION"="true") or ("NO_AUTO_COMPACTION"="false")
7. TBLPROPERTIES ("compactor.mapreduce.map.memory.mb"="mapper_memory")
8. TBLPROPERTIES ("compactorthreshold.hive.compactor.delta.num.threshold"="threshold_num")
9. TBLPROPERTIES ("compactorthreshold.hive.compactor.delta.pct.threshold"="threshold_pct")
10. TBLPROPERTIES ("auto.purge"="true") or ("auto.purge"="false")
11. TBLPROPERTIES ("EXTERNAL"="TRUE")-将内部表改为外部表
12. TBLPROPERTIES ("external.table.purge"="true")

• 要为表指定一个数据库，要么在 CREATE table 语句之前使用 USE database_name 语句(在Hive 0.6和更高版本中)，要么用一个数据库名称限定表名(在Hive 0.7和更高版本中是“database_name.table.name”)。

内部表和外部表

内部表的特点是，先有表  后有数据，数据被上传到表对应的hdfs目录下进行管理。
外部表的特点是，现有数据后有表，hive表关联到该位置管理其中的数据
默认情况下，Hive创建内部表，文件，元数据和统计信息由内部Hive进程管理。

存储格式

Hive支持内置和自定义开发的文件格式。

以下是 Hive 内置的格式：

行格式& SerDe

您可以使用自定义 SerDe 或使用本机 SerDe 创建表。如果未指定行格式或指定分隔的行格式，则使用本机 SerDe。

使用 SERDE 子句创建具有自定义 SERDE 的表。有关 SerDes 的更多信息，请参见：

• Hive SerDe
• SerDe
• HCatalog Storage Formats

必须为使用本机 SerDe 的表指定列列表。有关允许的列类型，请参阅用户指南的类型部分。

可以指定使用自定义 SerDe 的表的列列表，但 Hive 将查询 SerDe 以确定此表的实际列列表。

有关 SerDe 的一般信息，请参阅开发人员指南中的 Hive SerDe。有关输入和输出处理的详细信息，请参见 SerDe。

要更改表的 SerDe 或 SERDEPROPERTIES，请使用如下所述的 ALTER table 语句来添加 SerDe 属性。

Partitioned Tables

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

如果在创建分区表时，您得到这个错误:“FAILED: error in semantic analysis: Column repeat in partitioning columns”，这意味着您试图将分区的列包含在表本身的数据中。你可能确实定义了列。但是，您创建的分区会生成一个伪列，您可以对其进行查询，因此您必须将表列重命名为其他名称(用户不应该对其进行查询!)

例如，假设原始的未分区表有三列:id、日期和名称。

Example:

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

Example:

下面是一个创建分区表的示例语句：

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;

 上面的语句创建了包含viewTime、userid、page_url、referrer_url和ip列(包括注释)的page_view表。表也被分区，数据存储在序列文件中。文件中的数据格式假定为字段由ctrl-A分隔，行由换行分隔。

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;

 上面的语句允许您创建与前一个表相同的表。

在前面的示例中，数据存储在 <hive.metastore.warehouse.dir>/page_view。为key hive.metastore.warehouse 指定一个值。在配置文件 Hive -site.xml 中的目录。

External Tables

EXTERNAL关键字允许您创建一个表并提供一个位置，这样Hive就不会为这个表使用默认位置。如果已经生成了数据，这就很方便了。删除外部表时，不会从文件系统中删除表中的数据。

外部表指向其存储的任何HDFS位置，而不是存储在配置属性 hive.metastore.warehouse.dir 指定的文件夹中。

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>';

 您可以使用上面的语句创建一个page_view表，该表指向其存储的任何HDFS位置。但是，您仍然必须确保按照上面的CREATE语句中指定的方式分隔数据。

Create Table As Select (CTAS)

还可以在一个 create-table as-select (CTAS)语句中创建和填充查询结果。cta创建的表是内部表，这意味着在填充所有查询结果之前，其他用户不会看到该表。因此，其他用户要么看到具有完整查询结果的表，要么根本看不到表。

在 CTAS 中有两个部分，SELECT 部分可以是 HiveQ L支持的任何 SELECT 语句。CTAS 的 CREATE 部分从 SELECT 部分获取结果模式，并使用其他表属性(如 SerDe 和存储格式)创建目标表。

从Hive 3.2.0开始，CTAS语句可以为目标表定义一个分区规范。

CATS 有以下限制：

• 目标表不能是外部表。
• The target table cannot be a list bucketing table.

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字符串)创建目标表 new_key_value_store。如果 SELECT 语句没有指定列别名，那么列名将自动分配给 _col0、_col1 和 _col2 等。此外，新的目标表是使用特定的 SerDe 和与 SELECT 语句中的源表无关的存储格式创建的。

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

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

Create Table Like

与 CREATE TABLE 类似的表单允许您精确地复制现有的表定义(而无需复制其数据)。与 CATS 不同，下面的语句创建了一个新的 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之前，像view_name这样的创建表会复制视图。在Hive 0.8.0及以后的版本中，像view_name这样的CREATE TABLE通过采用view_name模式(字段和分区列)创建一个表，使用默认的SerDe和文件格式。

Bucketed Sorted Tables

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 组成，在每个 bucket 中，数据按照 viewTime 的递增顺序排序。这样的组织允许用户对聚集列进行有效的抽样——在本例中是 userid。排序属性允许内部操作人员在评估查询时利用更知名的数据结构，这也提高了效率。如果任何列是列表或映射，则可以使用映射键和集合项关键字。

 按创建 CLUSTERED BY 和 SORTED BY 命令不影响如何将数据插入到表中——只影响如何读取数据。这意味着用户必须小心地正确插入数据，方法是将还原器的数量指定为桶的数量，并在查询中使用 CLUSTER by 和 SORT by 命令。

Skewed Tables(倾斜表)

该特性可用于改善一个或多个列有歪斜值的表的性能。通过指定的值经常出现(重斜)蜂巢将这些分割成单独的文件(或目录的列表用桶装)自动查询期间，考虑到这一事实,以便它可以跳过或者包含整个文件(或目录的列表用桶装)如果可能的话。

这可以在创建表时在每个表级别上指定。

下面的示例显示了一个带有三个倾斜值的列，还可以使用存储为目录子句，该子句指定列表屏蔽。

CREATE TABLE list_bucket_single (key STRING, value STRING)
  SKEWED BY (key) ON (1,5,6) [STORED AS DIRECTORIES];

这是一个有两个倾斜列的表的例子。

CREATE TABLE list_bucket_multiple (col1 STRING, col2 int, col3 STRING)
  SKEWED BY (col1, col2) ON (('s1',1), ('s3',3), ('s13',13), ('s78',78)) [STORED AS DIRECTORIES];

 

Temporary Tables（临时表）

作为临时表创建的表仅对当前会话可见。数据将存储在用户的临时目录中，并在会话结束时删除。

如果使用数据库中已经存在的永久表的数据库/表名创建临时表，那么在该会话中对该表的任何引用都将解析为临时表，而不是永久表。如果不删除临时表或将其重命名为不冲突的名称，用户将无法在该会话中访问原始表。

临时表有以下限制：

• 不支持分区列。
• 不支持创建索引。

从Hive 1.1.0开始，临时表的存储策略可以设置为 memory、ssd 或 hive.execy.temporary.table 的默认值。

CREATE TEMPORARY TABLE list_bucket_multiple (col1 STRING, col2 int, col3 STRING);

Transactional Tables（事务表）

支持使用ACID语义的操作的表。有关事务性表的详细信息，请参阅Attempt to do update or delete using transaction manager that does not support these operations

CREATE TRANSACTIONAL TABLE transactional_table_test(key string, value string) PARTITIONED BY(ds string) STORED AS ORC;

Constraints（约束）

Hive 包含对未经验证的主键和外键约束的支持。当存在约束时，一些 SQL 工具生成更有效的查询。由于这些约束没有得到验证，上游系统需要在将数据加载到 Hive 之前确保数据完整性。

create table pk(id1 integer, id2 integer,
  primary key(id1, id2) disable novalidate);
 
create table fk(id1 integer, id2 integer,
  constraint c1 foreign key(id1, id2) references pk(id2, id1) disable novalidate);

Hive支持 UNIQUE, NOT NULL, DEFAULT 和 CHECK 约束。除了UNIQUE之外，所有三种类型的约束都是强制的。 

create table constraints1(id1 integer UNIQUE disable novalidate, id2 integer NOT NULL,
  usr string DEFAULT current_user(), price double CHECK (price > 0 AND price <= 1000));
 
create table constraints2(id1 integer, id2 integer,
  constraint c1_unique UNIQUE(id1) disable novalidate);
 
create table constraints3(id1 integer, id2 integer,
  constraint c1_check CHECK(id1 + id2 > 0));

 不支持map、struct、array等复杂数据类型的默认设置。

Drop Table

DROP TABLE [IF EXISTS] table_name [PURGE];     -- (Note: PURGE available in Hive 0.14.0 and later)

DROP TABLE 删除此表的元数据和数据。如果配置了 Trash (并且没有指定清除)，则数据实际上被移动到 .Trash/Current 目录。元数据完全丢失了。

删除外部表时，不会从文件系统中删除表中的数据。从 Hive 4.0.0开始，设置表属性 external.table.purge=true，也将删除数据。

当删除视图引用的表时，不会给出任何警告(视图被挂起为无效，必须由用户删除或重新创建)。

否则，表信息将从metastore中删除，原始数据将被‘hadoop dfs -rm’删除。在许多情况下，这将导致表数据被移动到用户主目录中的.Trash文件夹中;因此，误放表的用户可以通过使用相同的模式重新创建表、重新创建任何必要的分区，然后使用Hadoop手动将数据移回原位，从而恢复丢失的数据。此解决方案可能随时间或跨安装而更改，因为它依赖于底层实现；强烈建议用户不要随意删除表。

如果指定了清除，则表数据不会进入. trash /Current目录，因此在错误删除的情况下无法检索。还可以使用表属性 auto.purge。

在 Hive 0.7.0或更高版本中，如果表不存在，DROP 将返回一个错误，除非指定了存在或配置变量hive.execl.drop.ignorenonexists 设置为 true。

Truncate Table

TRUNCATE TABLE table_name [PARTITION partition_spec];
 
partition_spec:
  : (partition_column = partition_col_value, partition_column = partition_col_value, ...)

删除表或分区中的所有行。如果启用了文件系统垃圾，这些行将被丢弃，否则它们将被删除。当前目标表应该是内部表，否则将引发异常。用户可以为一次截断多个分区指定partial partition_spec，删除partition_spec将截断表中的所有分区。

Hive 2.3.0开始，表属性 auto.purge 设置为 true，当执行 TRUNCATE TABLE 命令时，数据不会被一道回收站，在错误截断的情况下无法检索。这仅适用于内部表。对于内部表来说，不使用 auto.purge 配置或者设置为 false，将关闭此行为。