Hive 官方手册翻译 -- Hive DDL(数据定义语言)

时间：2022-08-26 20:17:13

Hive DDL(数据定义语言)

Confluence Administrator创建, Janaki Lahorani修改于 2018年9月19日

原文链接

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL

翻译：Google Google翻译，金山软件 金山词霸

校对：南大通用 范振勇 (2018.9.26)

一、概述

这里是HiveQL DDL语句的文档，其中包括：

CREATE 数据库/SCHEMA，表，视图，函数，索引

DROP 数据库/SCHEMA，表，视图，索引

TRUNCATE表

ALTER 数据库/SCHEMA，表，视图

MSCK REPAIR TABLE（或ALTER TABLE RECOVER PARTITIONS）

SHOW 数据库/SCHEMA，表，表属性，视图，分区，函数，索引 [ES]，列，创建表语句

DESCRIBE 数据库/SCHEMA，表 视图

PARTITION语句通常是TABLE语句的选项，除了SHOW分区。

二、关键词，非保留关键字和保留关键字

表格 1 关键词，非保留关键字和保留关键字

所有关键词

Hive版本

非保留关键字

保留关键字

1.2.0

ADD, ADMIN, AFTER, ANALYZE, ARCHIVE, ASC, BEFORE, BUCKET, BUCKETS, CASCADE, CHANGE, CLUSTER, CLUSTERED, CLUSTERSTATUS, COLLECTION, COLUMNS, COMMENT, COMPACT, COMPACTIONS, COMPUTE, CONCATENATE, CONTINUE, DATA, DATABASES, DATETIME, DAY, DBPROPERTIES, DEFERRED, DEFINED, DELIMITED, DEPENDENCY, DESC, DIRECTORIES, DIRECTORY, DISABLE, DISTRIBUTE, ELEM_TYPE, ENABLE, ESCAPED, EXCLUSIVE, EXPLAIN, EXPORT, FIELDS, FILE, FILEFORMAT, FIRST, FORMAT, FORMATTED, FUNCTIONS, HOLD_DDLTIME, HOUR, IDXPROPERTIES, IGNORE, INDEX, INDEXES, INPATH, INPUTDRIVER, INPUTFORMAT, ITEMS, JAR, KEYS, KEY_TYPE, LIMIT, LINES, LOAD, LOCATION, LOCK, LOCKS, LOGICAL, LONG, MAPJOIN, MATERIALIZED, METADATA, MINUS, MINUTE, MONTH, MSCK, NOSCAN, NO_DROP, OFFLINE, OPTION, OUTPUTDRIVER, OUTPUTFORMAT, OVERWRITE, OWNER, PARTITIONED, PARTITIONS, PLUS, PRETTY, PRINCIPALS, PROTECTION, PURGE, READ, READONLY, REBUILD, RECORDREADER, RECORDWRITER, REGEXP, RELOAD, RENAME, REPAIR, REPLACE, REPLICATION, RESTRICT, REWRITE, RLIKE, ROLE, ROLES, SCHEMA, SCHEMAS, SECOND, SEMI, SERDE, SERDEPROPERTIES, SERVER, SETS, SHARED, SHOW, SHOW_DATABASE, SKEWED, SORT, SORTED, SSL, STATISTICS, STORED, STREAMTABLE, STRING, STRUCT, TABLES, TBLPROPERTIES, TEMPORARY, TERMINATED, TINYINT, TOUCH, TRANSACTIONS, UNARCHIVE, UNDO, UNIONTYPE, UNLOCK, UNSET, UNSIGNED, URI, USE, UTC, UTCTIMESTAMP, VALUE_TYPE, VIEW, WHILE, YEAR

ALL, ALTER, AND, ARRAY, AS, AUTHORIZATION, BETWEEN, BIGINT, BINARY, BOOLEAN, BOTH, BY, CASE, CAST, CHAR, COLUMN, CONF, CREATE, CROSS, CUBE, CURRENT, CURRENT_DATE, CURRENT_TIMESTAMP, CURSOR, DATABASE, DATE, DECIMAL, DELETE, DESCRIBE, DISTINCT, DOUBLE, DROP, ELSE, END, EXCHANGE, EXISTS, EXTENDED, EXTERNAL, FALSE, FETCH, FLOAT, FOLLOWING, FOR, FROM, FULL, FUNCTION, GRANT, GROUP, GROUPING, HAVING, IF, IMPORT, IN, INNER, INSERT, INT, INTERSECT, INTERVAL, INTO, IS, JOIN, LATERAL, LEFT, LESS, LIKE, LOCAL, MACRO, MAP, MORE, NONE, NOT, NULL, OF, ON, OR, ORDER, OUT, OUTER, OVER, PARTIALSCAN, PARTITION, PERCENT, PRECEDING, PRESERVE, PROCEDURE, RANGE, READS, REDUCE, REVOKE, RIGHT, ROLLUP, ROW, ROWS, SELECT, SET, SMALLINT, TABLE, TABLESAMPLE, THEN, TIMESTAMP, TO, TRANSFORM, TRIGGER, TRUE, TRUNCATE, UNBOUNDED, UNION, UNIQUEJOIN, UPDATE, USER, USING, UTC_TMESTAMP, VALUES, VARCHAR, WHEN, WHERE, WINDOW, WITH

2.0.0

删除： REGEXP, RLIKE

补充： AUTOCOMMIT, ISOLATION, LEVEL, OFFSET, SNAPSHOT, 事务，工作，写

添加： COMMIT, ONLY, REGEXP, RLIKE, ROLLBACK, START

2.1.0

添加： ABORT, KEY, LAST, NORELY, NOVALIDATE, NULLS, RELY, VALIDATE

添加： CACHE, CONSTRAINT, FOREIGN, PRIMARY, REFERENCES

2.2.0

添加： DETAIL, DOW, EXPRESSION, OPERATOR, QUARTER, SUMMARY, VECTORIZATION, WEEK, YEARS, MONTHS, WEEKS, DAYS, HOURS, MINUTES, SECONDS

添加： DAYOFWEEK, EXTRACT, FLOOR, INTEGER, PRECISION, VIEWS

3.0.0

添加： TIMESTAMPTZ, ZONE

添加： TIME, NUMERIC, SYNC

版本信息

REGEXP和RLIKE在Hive2.0.0之前是非保留关键字，从Hive2.0.0开始是保留关键字。

按照“支持带引号的列名”，可以将保留关键字使用引号包围以便允许将其作为标识符。为了减少在语法歧义，大部分的关键字是保留关键字（1.2.0版本及更高版本）。

如果用户仍想使用这些保留的关键字作为标识符，有如下两种方式：

1)、使用带引号的标识符；

2)、设置hive.support.sql11.reserved.keywords =false。（版本2.1.0及更早版本）

三、创建/删除/修改/使用数据库

3.1、 创建数据库

CREATE (DATABASE|SCHEMA) [IF NOT EXISTS] database_name

  [COMMENT database_comment]

  [LOCATION hdfs_path]

  [WITH DBPROPERTIES (property_name=property_value, ...)];

SCHEMA和DATABASE的用途是可以互换的,他们的含义相同。

CREATE DATABASE在Hive0.6加入的（HIVE-675）。

WITH DBPROPERTIES子句是在Hive0.7加入的（HIVE-1836）。

3.2、 删除数据库

DROP (DATABASE|SCHEMA) [IF EXISTS] database_name [RESTRICT|CASCADE];

SCHEMA和DATABASE的用途是可以互换的,他们的含义相同。

在Hive0.6加入DROP DATABASE （HIVE-675）。

默认行为是RESTRICT，其中如果数据库不是空的DROP DATABASE将失败。要删除包含表的数据库，需要使用DROP DATABASE ... CASCADE。在Hive0.8开始支持RESTRICT和CASCADE。

3.3、 修改数据库

ALTER (DATABASE|SCHEMA) database_name SET DBPROPERTIES (property_name=property_value, ...);

ALTER (DATABASE|SCHEMA) database_name SET OWNER [USER|ROLE] user_or_role;

ALTER (DATABASE|SCHEMA) database_name SET LOCATION hdfs_path;

SCHEMA和DATABASE的用途是可以互换的,他们的含义相同。ALTER SCHEMA在Hive0.14加入（HIVE-6601）。

在ALTER DATABASE ... SET LOCATION语句并不将数据库的当前目录中的内容移到新指定的位置。它不改变指定的数据库下的所有表/分区相关联的位置。它只是修改了即将添加到这个数据库中的新表的默认父目录。此行为是类似于修改表目录而不移动现有的分区到另外的位置。

数据库中其他元数据是不可以修改的。

3.4、 使用数据库

USE database_name;

USE DEFAULT;

USE设置接下来所有HiveQL语句的当前数据库。要还原到默认的数据库，使用关键字“ default”，而不是数据库名称。

取得当前正在使用的数据库：SELECT current_database()（从Hive0.13.0）。

USE database_name在Hive0.6中加入（HIVE-675）。

四、创建/删除/截断表

4.1、 创建表

CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name

  [(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, ...)]

     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 (...)]]

  [LOCATION hdfs_path]

  [TBLPROPERTIES (property_name=property_value, ...)]

  [AS select_statement];

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

primitive_type

  : TINYINT

  | SMALLINT

  | INT

  | BIGINT

  | BOOLEAN

  | FLOAT

  | DOUBLE

  | DOUBLE PRECISION

  | STRING

  | BINARY

  | TIMESTAMP

  | DECIMAL

  | DECIMAL(precision, scale)

  | DATE

  | VARCHAR

  | CHAR

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, ... >

row_format

  : DELIMITED [FIELDS TERMINATED BY char [ESCAPED BY char]] [COLLECTION ITEMS TERMINATED BY char]

        [MAP KYS TERMINATED BY char] [LINES TERMINATED BY char]

        [NULL DEFINED AS char]

  | 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

  | ORC

  | PARQUET

  | AVRO

  | JSONFILE    -- (Note: Available in Hive 4.0.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

CREATE TABLE根据给定名称创建表。如果已经存在一个具有相同名称的表或视图，则会引发错误。您可以使用IF NOT EXISTS跳过错误。

  • 表名和列名不区分大小写，但SERDE和属性名称是区分大小写的。
    • 在Hive0.12和更早的版本中，表名和列名只允许由字母数字和下划线组成。
    • 在Hive0.13之后，列名可以包含任何的Unicode字符（见HIVE-6013），然而，点和冒号（ ：）上查询时会产生错误，所以它们在Hive1.2.0是不允许的（见HIVE-10120）。用反引号内指定的任何列名都按字面处理。在反引号字符串中，用双反引号（``）来表示一个反引号字符。反引号也使得表和列标识符可使用保留关键字。
    • 要恢复到0.13.0之前的模式，即列名为字符和下划线字符，需要设置配置属性hive.support.quoted.identifiers为none。在这种配置中，反引号名成被解释为正则表达式。有关详细信息，请参阅在列名中支持带引号的标识符。
  • 表和列注释字符串（单引号）。
  • 创建时没有External 子句的表被称为托管表，因为Hive管理其数据。要判断一个表托管表还是外部表，执行 DESCRIBE EXTENDED表名即可输出表的类型。
  • TBLPROPERTIES子句允许你用键/值对定义自己的元数据。一些预定义的表属性也是如此，如last_modified_user和last_modified_time就由Hive自动添加和管理。其他预定义的表属性包括：
    • TBLPROPERTIES ("comment"="table_comment")
    • TBLPROPERTIES ("hbase.table.name"="table_name") – 见集成HBASE.
    • TBLPROPERTIES ("immutable"="true") 或("immutable"="false")– 见通过查询查插入数据到Hive表.
    • TBLPROPERTIES ("orc.compress"="ZLIB") 或("orc.compress"="SNAPPY") 或 ("orc.compress"="NONE") 和其他ORC属性– 见ORC文件.
    • TBLPROPERTIES ("transactional"="true")或 ("transactional"="false")– 见Hive事务.
    • TBLPROPERTIES ("NO_AUTO_COMPACTION"="true") 或 ("NO_AUTO_COMPACTION"="false"), 缺省是 "false" – 见Hive事务.
    • TBLPROPERTIES ("compactor.mapreduce.map.memory.mb"="mapper_memory") – 见Hive事务.
    • TBLPROPERTIES ("compactorthreshold.hive.compactor.delta.num.threshold"="threshold_num") –见Hive事务.
    • TBLPROPERTIES ("compactorthreshold.hive.compactor.delta.pct.threshold"="threshold_pct") – 见Hive事务.
    • TBLPROPERTIES ("auto.purge"="true") 或 ("auto.purge"="false") – 见删除表、删除分区、截断表和覆盖式插入数据.
    • TBLPROPERTIES ("EXTERNAL"="TRUE")–修改托管表为外部表，反之亦然为“FALSE”.
      • 在Hive2.4.0中（HIVE-16324）属性“EXTERNAL”的值被解析为布尔型（不区分大小写的true或false），而不是比较时区分大小写字符串。
  • 要指定表的数据库，或者在CREATE TABLE语句之前调用USE数据库名称，或者在CREATE TABLE语句之内指明数据库名称（如database_name.table.name）。

关键字“ default”可用于默认的数据库。

请参阅下面的ALTER TABLE有关表注释，表属性，SERDE属性的详细信息。

请参见Hive类型系统和Hive数据类型中有关原生数据类型和复杂数据类型的详细信息。

4.1.1、  托管表和外部表

缺省情况下，Hive创建托管表，其中的文件，元数据和统计信息由Hive进程内部管理。一个托管表存储在hive.metastore.warehouse.dir.path属性指定的目录下，默认情况下，文件夹路径类似/user/hive/warehouse/databasename.db/tablename/。默认位置可以在创建表的过程中通过重写location属性修改。如果一个托管表或分区被删除，与该表或分区相关联的数据和元数据都被删除。如果未指定PURGE选项，则数据被移动到垃圾文件夹，再保留事先定义的一段时间。

当Hive负责托管表的生命周期或生成临时表时，就使用托管表。

外部表描述了外部文件的元数据/Schema。外部表文件可以由Hive之外的进程访问和处理。外部表可以访问存储在外的数据源，例如Azure存储卷（ASV）或远程HDFS位置的数据。当外部表的结构或分区被改变时，可用MSCK REPAIR TABLE TABLE_NAME语句刷新元数据信息。

当外部表的文件已经存在或位于远程位置时，即使删除了该表，文件也会保留。

托管表或外部表可以使用DESCRIBE FORMATTED TABLE_NAME命令识别，该命令将根据表类型显示MANAGED_TABLE或EXTERNAL_TABLE。

统计数据可以用于内部表、外部表和分区的查询优化。

4.1.2、  存储格式

Hive支持内置和定制开发的文件格式。见CompressedStorage 关于压缩表存储的详细说明。

下面是一些内置Hive格式：

存储格式

描述

STORED AS TEXTFILE

保存为纯文本文件。TEXTFILE是默认的文件格式中，除非配置参数hive.default.fileformat有其他的设置。

使用DELIMITED子句来分隔文件；允许使用转义为分隔符的'ESCAPED BY子句（如ESCAPED BY“\”），如果你的数据中包含分隔符，就需要用转义符进行转义。

可自定义NULL格式，由 “NULL DEFINED AS”子句指定（默认为“\N”）。

STORED AS SEQUENCEFILE

存储为压缩的序列文件

STORED AS ORC

存储为ORC文件格式。支持ACID事务和基于成本的优化器（CBO）。采用列存格式存储数据。

STORED AS PARQUET

在Hive0.13.0和更高版本中，STORED AS PARQUET 保存为PARQUET列存格式。

在Hive0.10，0.11，0.12中使用ROW FORMAT SERDE ...STORE AS INPUTFORMAT ... OUTPUTFORMAT语法... 。

STORED AS AVRO

存储为Avro的格式

STORED AS RCFILE

存储RCFILE格式

STORED AS JSONFILE

存储为JSON文件格式（Hive4.0.0以后）

STORED BY

由非本地表的格式存储。要创建或链接到一个非本地表，例如通过支持一个表的HBase或Druid或Accumulo 。

见StorageHandlers 中有关此选项的更多信息。

INPUTFORMAT and OUTPUTFORMAT

在file_format子句中，用一个字符串指定相应的InputFormat和OutputFormat类名。例如，

'org.apache.hadoop.hive.contrib.fileformat.base64.

Base64TextInputFormat'。

对于LZO压缩，要使用的值是

'INPUTFORMAT “com.hadoop.mapred.

DeprecatedLzoTextInputFormat”

OUTPUTFORMAT “ org.apache.hadoop.hive.ql.io .

HiveIgnoreKeyTextOutputFormat”'

（见LZO压缩）。

4.1.3、  行格式与SERDE

您可以在创建表时使用自定义SERDE或内置的SERDE。如果未指定ROW FORMAT或用ROW FORMAT DELIMITED指定，则采用内置SERDE。

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

Hive SerDe

SerDe

HCatalog存储格式

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

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

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

要修改表的SERDE或SERDEPROPERTIES，参考下面ALTER TABLE语句中添加SERDE属性的描述。

行格式

描述

正则表达式

ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe'

WITH SERDEPROPERTIES

（

“input.regex”= “<正则表达式>”

）

STORED AS TEXTFILE;

保存为纯文本文件，通过正则表达式转换。

下面的示例定义了Apache缺省博客格式的表。

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;

更多RegexSerDe的信息参见HIVE-662和HIVE-1719。

JSON

ROW FORMAT SERDE

'org.apache.hive.hcatalog.data.JsonSerDe'

STORED AS TEXTFILE

存储为JSON格式的纯文本文件。

提供JSON支持的JsonSerDe在Hive0.12及更高版本中支持。

在一些发布版中，需要添加Hivehcatalog-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;

该JsonSerDe是被从HCatalog移动到Hive的，在它成为Hive contrib项目之前。它由HIVE-4895添加Hive发布版。在0.12.0版本之前，可以使用亚马逊发布的SERDE， s3://elasticmapreduce/samples/hive-ads/libs/jsonserde.jar。

从Hive3.0.0开始，JsonSerDe被添加到Hive SERDE中“org.apache.hadoop.hive.serde2.JsonSerDe”（HIVE-19211）。

CREATE TABLE my_table(a string, b bigint, ...)

ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.JsonSerDe'

STORED AS TEXTFILE;

或者从Hive4.0.0开始支持STORED AS JSONFILE（HIVE-19899），这样你就可以如下创建表：

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是在Hive0.14和以后版本是可用的。

下面的示例创建一个TSV（制表符分隔）文件。

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;

对于SERDE默认属性是逗号分隔（CSV）文件

DEFAULT_ESCAPE_CHARACTER \

DEFAULT_QUOTE_CHARACTER  "

DEFAULT_SEPARATOR        ,

这SERDE适用于大多数CSV数据，但不处理嵌入换行符。要使用SERDE，指定完整类名org.apache.hadoop.hive.serde2.OpenCSVSerde。

文档是基于原始规范文件  https://github.com/ogrodnek/csv-serde。

限制：

此Serde将所有列视为String类型。即使使用该SerDe创建具有非字符串列类型的表，DESCRIBE TABLE的输出也会显示字符串列类型。类型信息将从Serde检索。

要将列转换为表中所需的类型，可以在表上创建一个视图来执行CAST转换为所需的类型。

CSV SerDe基于https：/github.com/ogrodnek/csv-serde，并添加到包含hive-7777中的Hive发布中。CSVSerde已经在Hive 0.14及更高版本上构建和测试，并且使用了OpenCSV2.3，它与Hive捆绑在一起发布。

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

4.1.4、  分区表

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

如果在创建分区表时，您得到了这样的错误：“Failure：Error in SemanticAnalysis：列在分区列中重复出现”，这意味着您试图将已分区的列包含在表本身的数据中。您可能确实已经定义了列。但是，您创建的分区时产生了一个伪列，您可以对其进行查询，因此您必须将表的已有列重命名为其他内容(用户不应该对其进行查询！)。

例如，假设您的原始未分区表有三列：ID，date和name。

例：

id     int,

date   date,

name   varchar

现在你想根据date分区。您的Hive定义可以使用“dtDontQuery”作为列名，这样“date”可用于分区（和查询）。

例：

create table table_name (

  id                int,

  dtDontQuery       string,

  name              string

)

partitioned by (date string)

现在，您的用户仍然在查询“ where date = '...'”但第二列dtDontQuery将保持原来的值。

下面是一个例子语句来创建分区表：

例：

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中。在Hive配置文件hive-site.xml中为hive.metastore.warehouse.dir指定一个值。

4.1.5、  外部表

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

外部表指向用于其存储的任何HDFS位置，无需存储在配置属性hive.asionore.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语句中指定的分隔符。

有关创建外部表的另一个示例，请参见本教程中的加载数据部分。

4.1.6、  CREATE TABLE AS SELECT（CTAS）

表也可以通过一个CREATE-TABLE-AS-SELECT(CTAS)语句中的查询结果来创建和填充。CTAS创建的表是原子的，这意味着在填充所有查询结果之前，其他用户不会看到该表。因此，其他用户要么会看到具有完整查询结果的表，要么根本不会看到该表。

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

CTAS有以下限制：

  1. 目标表不能是分区表。
  2. 目标表不能是外部表。
  3. 目标表不能是列表桶表。

例：

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等。此外，新目标表使用与SELECT语句中源表无关的特定SerDe和存储格式，。

从Hive 0.13.0开始，SELECT语句可以包含一个或多个公共表达式(CTE)，如SELECT语法所示。有关示例，请参见公共表达式。

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

4.1.7、  CREATE TABLE 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, ...)];

Hive0.8.0之前，CREATE TABLE LIKE VIEW_NAME将使视图的副本。在Hive0.8.0和以后的版本中，使用用于SERDE和文件格式的默认CREATE TABLE LIKE VIEW_NAME通过采用VIEW_NAME（字段和分区列）的模式创建一个表。

在Hive0.8.0之前，CREATE TABLE LIKE view_name将生成视图的副本。在Hive0.8.0和更高版本中，CREATE TABLE VIEW_NAME通过使用缺省设置的Serde和文件格式，根据view_name的Sechma(字段和分区列)来创建一个表。

4.1.8、  排序分桶表

例：

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表是按用户ID进行分桶的，并且在每个桶中，数据按照viewTime升序排序。这样的组织允许用户对集群列执行有效的抽样-在本例中是userid。排序属性允许内部操作符在评估查询时利用已知的数据结构，也可以提高效率。MAP KEYS和COLLECTION ITEMS 关键字可以使用任何列的列表或映射。

CLUSTERED BY 和SORTED BY不会影响数据插入表的方式-只影响数据的读取方式。这意味着用户必须小心地正确插入数据，方法是指定reducers的数量与存储桶的数量相等，并在查询中使用CLUSTER BY 和 SORT BY命令。

还有一个创建和填充桶表的例子。

4.1.9、  倾斜表

设计文档

有关更多信息，请阅读倾斜连接优化和桶列表存储设计文档。

此特性可用于在一个或多个列具有倾斜值的表中提高性能。通过指定经常出现的值(严重倾斜)，Hive将自动将这些文件拆分为单独的文件(或桶列表存储的情况下的目录)，并在查询过程中考虑到这一事实，以便在可能的情况下跳过或包含整个文件(或桶列表存储的情况下的目录)。

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

下面的示例显示了一个列，它有三个倾斜的值，可以选择使用STORED AS DIRECTORIES子句，该子句指定了对列表进行存储。

例：

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

对于相应的ALTER TABLE语句，请参阅下面ALTER TABLE倾斜或存储目录的信息。

4.1.10、     临时表

临时表只对当前会话可见。数据将被存储在用户的临时目录，并在会话结束时删除。

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

临时表有以下限制：

不支持分区列。

不支持创建索引。

从Hive1.1.0开始，通过hive.exec.temporary.table.storage配置参数，临时表的存储策略可以设置为memory，ssd或default（见 HDFS的存储类型和存储策略）。

例：

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

4.1.11、     事务性表

版本信息

由Hive4.0实现（HIVE-18453）。

支持与ACID语义操作的表。请参阅此有关事务表的更多细节。

例：

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

4.1.12、     约束

版本信息

由Hive2.1.0实现（HIVE-13290）。

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

4.2、 删除表

DROP TABLE [IF EXISTS] table_name [PURGE];

DROP TABLE删除此表的元数据和数据。如果配置了回收站(且未指定PURGE)，数据实际上会移动到.Trash/Current目录。元数据则完全丢失了。

删除外部表时，将不会从文件系统中删除表中的数据。

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

另外，该表信息被从metastore中删除，数据也将被删除，就好象由“hadoopdfs -rm”删除一样。在许多情况下，这会导致表中的数据被移动到用户主目录的的.Trash文件夹; 因此，错误地删除表的用户可能能够通过使用相同模式重新创建表、重新创建任何必要的分区，然后使用Hadoop命令手动将数据移回原处来恢复丢失的数据。因为依赖于底层实现,此恢复数据的解决方案可能会随着时间的推移或实际部署情况而更改；强烈建议用户不要心血来潮地删除表。

版本信息：PURGE

PURGE选项在0.14.0版本加入（HIVE-7100）。

如果指定PURGE，表中的数据不会转到.Trash /当前目录，因此在一个误操作DROP的情况下无法进行数据恢复。PURGE选项也可以用表属性中指定auto.purge（见上文的TBLPROPERTIES）。

在Hive0.7.0或更高版本，如果DROP不存在的表会返回一个错误，除非指定IF EXISTS或设置变量hive.exec.drop.ignorenonexistent为真。

有关如何删除分区，请参阅下面的“更改分区”部分。

4.3、 截断表

TRUNCATE TABLE table_name [PARTITION partition_spec];

partition_spec:

  : (partition_column = partition_col_value, partition_column = partition_col_value, ...)

删除表或分区（一个或多个分区）的所有行。如果启用了文件系统的垃圾回收，数据将被回收，否则它们会被直接删除（从Hive2.2.0的HIVE-14626）。目前，目标表应该是本机的/托管表，否则将引发异常。对于分区表，用户可以指定partition_spec一次截断多个分区，或者省略partition_spec截断表中的所有分区。

从HIVE 2.3.0开始（HIVE-15880），如果表属性“auto.purge”（见上面的TBLPROPERTIES）设置为“true”， 则当对表发出truncate table命令时，该表的数据不移动到回收站，并且如果误操作发出的truncate table命令也无法恢复数据。这仅适用于托管表（见托管表）。如果托管表的“auto.purge”属性未设置或设置为false，则可以关闭此行为模式。

4.4、 改变表/分区/列

ALTER TABLE

重命名表

ALTER TABLE属性

ALTER TABLE注释

添加SERDE属性

ALTER TABLE存储属性

ALTER TABLE倾斜或存储目录

ALTER TABLE倾斜

ALTER TABLE不倾斜

ALTER TABLE不存储目录

ALTER TABLE设置倾斜位置

ALTER TABLE约束

其他ALTER TABLE语句

改变分区

添加分区

动态分区

重命名分区

交换分区

修复分区（MSCK REPAIR TABLE）

删除分区

分区存档

改变表或分区

ALTER 表/分区 文件格式

ALTER 表/分区 位置

ALTER 表/分区TOUCH

改变表/分区保护

改变表/分区紧缩

改变表/分区串联

ALTER TABLE /分区更新列

ALTER COLUMN

列名规则

修改列的名称/类型/位置/注释

添加/替换列

部分分区规范

ALTER TABLE语句使您可以修改现有表的结构。您可以添加列/分区，改变SERDE，添加表和SERDE属性，或重命名表本身。同样，修改表分区语句允许您修改表中特定分区的属性。

4.4.1、  ALTER TABLE

4.4.1.1、    重命名表

ALTER TABLE table_name RENAME TO new_table_name;

此语句允许您将表的名称更改为不同的名称。

从0.6版开始，对托管表重命名会移动其HDFS位置。从2.2.0版(hive-14909)中重命名机制已经更改，只有当表创建时没有Location子句并位于其数据库目录下，托管表的HDFS位置才会被移动。而在HIVE0.6版本之前只是重命名了metastore中的表，而没有移动HDFS位置。

4.4.1.2、    ALTER TABLE属性

ALTER TABLE table_name SET TBLPROPERTIES table_properties;

table_properties:

  : (property_name = property_value, property_name = property_value, ... )

您可以使用此语句将自己的元数据添加到表。目前last_modified_user，last_modified_time属性由Hive自动添加。用户可以添加自己的属性到这个列表。您可以执行DESCRIBE EXTENDED TABLE来获取这些信息。

欲了解更多信息，请参阅上面创建表的TBLPROPERTIES子句。

4.4.1.3、    ALTER TABLE注释

要更改表的注释，你必须改变TBLPROPERTIES的comment属性：

ALTER TABLE table_name SET TBLPROPERTIES ('comment' = new_comment);

4.4.1.4、    添加SERDE属性

ALTER TABLE table_name [PARTITION partition_spec] SET SERDE serde_class_name [WITH SERDEPROPERTIES serde_properties];

ALTER TABLE table_name [PARTITION partition_spec] SET SERDEPROPERTIES serde_properties;

serde_properties:

  : (property_name = property_value, property_name = property_value, ... )

这些语句使您能够更改表的SERDE或给SERDE对象添加用户定义的元数据表。

当Hive初始化该表以序列化和反序列化数据时，Serde属性将传递给该表的Serde。因此，用户可以在这里存储自定义Serde所需的任何信息。有关更多信息，请参阅开发人员指南中的SerDe文档和Hive Serde。有关在CREATE TABLE语句中设置表的SerDe和SERDEPROPERTIES的详细信息，请参阅上面的行格式、存储格式和Serde。

请注意property_name和property_value这两个都必须加引号。

例：

ALTER TABLE table_name SET SERDEPROPERTIES ('field.delim' = ',');

4.4.1.5、    ALTER TABLE存储属性

ALTER TABLE table_name CLUSTERED BY (col_name, col_name, ...) [SORTED BY (col_name, ...)]

  INTO num_buckets BUCKETS;

这些语句更改表的物理存储性能。

注意：这些命令只会修改Hive的元数据，不会重新组织或重新格式化现有的数据。用户应确保实际数据布局符合元数据定义。

4.4.1.6、    ALTER TABLE倾斜或存储为目录

表的SKEWED和STORED AS DIRECTORIES属性可以由ALTER TABLE语句来改变。见上文中CREATE TABLE语法中相应的倾斜表部分。

  • ALTER TABLE倾斜
ALTER TABLE table_name SKEWED BY (col_name1, col_name2, ...)

  ON ([(col_name1_value, col_name2_value, ...) [, (col_name1_value, col_name2_value), ...]

  [STORED AS DIRECTORIES];

STORED AS DIRECTORIES选项确定是否倾斜表使用列表桶功能，该功能为倾斜值创建子目录。

  • ALTER TABLE不倾斜
ALTER TABLE table_name NOT SKEWED;

“NOT SKEWED”选项使表不倾斜，并关闭列表桶存储功能(因为列表桶存储表总是倾斜的)。这会影响ALTER语句之后创建的分区，但对ALTER语句之前创建的分区没有影响。

  • ALTER TABLE不存储为目录
ALTER TABLE table_name NOT STORED AS DIRECTORIES;

这将关闭列表桶存储功能，尽管表仍然是倾斜的。

  • ALTER TABLE设置倾斜表存储位置
ALTER TABLE table_name SET SKEWED LOCATION (col_name1="location1" [, col_name2="location2", ...] );

这将更改列表桶存储位置的映射。

4.4.1.7、    ALTER TABLE约束

版本信息

由Hive2.1.0发布。

表约束可以通过ALTER TABLE语句添加或移除。

ALTER TABLE table_name ADD CONSTRAINT constraint_name PRIMARY KEY (column, ...) DISABLE NOVALIDATE;

ALTER TABLE table_name ADD CONSTRAINT constraint_name FOREIGN KEY (column, ...) REFERENCES table_name(column, ...) DISABLE NOVALIDATE RELY;

ALTER TABLE table_name DROP CONSTRAINT constraint_name;
  • 其他ALTER TABLE语句

有关ALTER TABLE或Partition，请参见下面的更多修改表的DDL语句。

4.4.2、  改变分区

分区可以通过在ALTER TABLE语句中使用PARTITION子句来添加，重命名，交换（移动），删除，存档（或调档）。如下所述。为了让Metastore知道直接添加到HDFS中的分区，您可以使用Metastore Check命令(MSCK)或在AmazonEMR上使用ALTER TABLE的RECOVER PARTITIONS选项。

有关更改分区的更多方法，请参见下面的修改表/分区。

版本1.2+

从Hive1.2(hive-10307)开始，如果属性hive.typecheck.on.insert设置为true(默认)，分区规范中指定的分区值将被检查、转换和标准化，以符合其列类型。这些值可以是数字字符串。

4.4.2.1、    添加分区

ALTER TABLE table_name ADD [IF NOT EXISTS] PARTITION partition_spec [LOCATION 'location'][, PARTITION partition_spec [LOCATION 'location'], ...];

partition_spec:

  : (partition_column = partition_col_value, partition_column = partition_col_value, ...)

您可以使用ALTER TABLE ADD PARTITION给表添加分区。只有分区值是字符串时，才需要其被引号包围。Location必须是一个存放内部数据目录。（ADD PARTITION更改表的元数据，但不会加载数据。如果分区的位置不存在数据，查询将不会返回任何结果。）

如果表中的已经存在partition_spec的分区，则会引发错误。您可以使用IF NOT EXISTS跳过错误。

0.7版本

虽然在单个ALTER TABLE中有多个partition_spec语法是正确的，如果你在0.7版本这样做，则分区方案将失败。也就是说，每个指定分区的查询总是只使用第一个分区。

具体来说，下面的示例将在Hive 0.7中默默地、无错误地失败，并且所有查询都将只转到DT=‘2008-08-08’分区，而不管您指定哪个分区。

例：

ALTER TABLE page_view ADD

PARTITION (dt='2008-08-08', country='us') location '/path/to/us/part080808'

PARTITION (dt='2008-08-09', country='us') location '/path/to/us/part080809';

在Hive0.8及更高版本，如前面的示例，你可以在一条ALTER TABLE语句添加多个分区。

在Hive0.7，如果要添加多个分区，你应该使用以下表格：

ALTER TABLE table_name ADD PARTITION (partCol = 'value1') location 'loc1';

ALTER TABLE table_name ADD PARTITION (partCol = 'value2') location 'loc2';

...

ALTER TABLE table_name ADD PARTITION (partCol = 'valueN') location 'locN';

4.4.2.2、    动态分区

分区可以使用Hive INSERT语句（或Pig STORE语句）动态添加。有关详细信息和示例，请参阅这些文档：

动态分区设计文档

教程：动态分区插入

Hive DML：动态分区插入

HCatalog动态分区

与Pig配合的用法

MapReduce的用法

4.4.2.3、    重命名分区

ALTER TABLE table_name PARTITION partition_spec RENAME TO PARTITION partition_spec;

此语句允许您更改分区列的值。其中一个用例是，您可以使用此语句使遗留分区列值规范化，以符合其类型。在这种情况下，即使在属性hive.typecheck.on.insert设置为true(默认)下，旧partition_spec中的列值也不启用类型转换和规范化，这允许您在旧partition_spec中以字符串形式指定任何遗留数据。

4.4.2.4、    交换分区

分区可以表之间交换（移动）。

版本信息

从Hive0.12（HIVE-4095）。在Hive版本1.2.2、1.3.0和2.0.0中支持多个分区。

-- Move partition from table_name_1 to table_name_2

ALTER TABLE table_name_2 EXCHANGE PARTITION (partition_spec) WITH TABLE table_name_1;

-- multiple partitions

ALTER TABLE table_name_2 EXCHANGE PARTITION (partition_spec, partition_spec2, ...) WITH TABLE table_name_1;

此语句允许您将分区中的数据从一个表移动到另一个具有相同架构且还没有该分区的表。

有关此功能的详细信息，请参阅Exchange分区和hive-4095。

4.4.2.5、    恢复分区（MSCK REPAIR TABLE）

Hive在其metastore存储每个表的分区列表。然而，如果新的分区被直接加入到HDFS（比方说通过使用hadoop fs -put命令），或从HDFS移除，metastore（因此Hive也）将不知道这些变化，除非用户在分区表上每次新添或删除分区时分别运行ALTER TABLE table_name ADD/DROP PARTITION命令。

然而，用户可以运行与维修表选项的metastore检查命令：

MSCK [REPAIR] TABLE table_name [ADD/DROP/SYNC PARTITIONS];

它将更新Hive Metastore中关于分区的元数据，用于那些尚未存在此类元数据的分区。

  • MSC命令的默认选项是ADD PARTITIONS。使用此选项，它将向Metastore添加任何存在于HDFS上但不在Metastore中的分区。
  • DROP PARTITIONS选项将从Metastore中删除分区信息，对应那些已经从HDFS中删除的分区。
  • SYNC PARTITIONS选项等效于调用ADD PARTITIONS和DROP PARTITIONS。有关详细信息，请参阅hive-874和hive-17824。
  • 当有大量未跟踪分区时，有一项规定可以按批处理方式运行MSCK修复表，以避免OOME(内存不足错误)。通过为属性hive.msck.repair.batch.size提供配置的批处理大小，它可以在内部批处理中运行。该属性的默认值为零，这意味着它将一次执行所有分区。
  • 不带修复选项的MSCK命令可用于查找元数据错配的详细信息。

在Amazon Elastic MapReduce（EMR）版本Hive的等效命令是：

ALTER TABLE table_name RECOVER PARTITIONS;

从Hive 1.3开始，如果分区值中有HDFS目录不允许的字符，MSCK将抛出异常。在客户端上使用设置hive.msck.path.validation来改变此行为；“skip”将简单地跳过目录。“ignore”将尝试创建分区(旧的行为)。这可能成功，也可能不起作用。

4.4.2.6、    删除分区

ALTER TABLE table_name DROP [IF EXISTS] PARTITION partition_spec[, PARTITION partition_spec, ...]

  [IGNORE PROTECTION] [PURGE];            -- (Note: PURGE available in Hive 1.2.0 and later, IGNORE PROTECTION not available 2.0.0 and later)

您可以使用ALTER TABLE DROP PARTITION来删除表的分区。这删除了用于该分区中的数据和元数据。如果设置了回收策略，数据实际移动到.Trash /当前目录，除非另外指定PURGE。但元数据完全丢失（见上文的删除表）。

版本信息：IGNORE PROTECTION

IGNORE PROTECTION不在2.0.0及以后的版本可用。此功能由Hive几个可用的安全选项之一取代(请参见基于SQL标准的Hive授权)。见HIVE-11145了解详情。

对于由NO_DROP CASCADE保护的表，可以使用谓词IGNORE PROTECTION删除指定分区或一组分区（例如，在两个Hadoop集群之间拆分表时）：

ALTER TABLE table_name DROP [IF EXISTS] PARTITION partition_spec IGNORE PROTECTION;

上述命令将删除该分区，而不考虑保护状态。

版本信息：PURGE

ALTER TABLE的PURGE选项由1.2.1版本添加（HIVE-10934）。

如果指定PURGE的清除，则分区的数据不走的.Trash /当前目录，因此不能在一个失误删除的情况下恢复数据：

ALTER TABLE table_name DROP [IF EXISTS] PARTITION partition_spec PURGE;     -- (Note: Hive 1.2.0 and later)

PURGE选项也可以用表属性中auto.purge指定（见上文的TBLPROPERTIES）。

从Hive0.7.0到更高版本，DROP的分区如果不存在，返回一个错误，除非指定IF EXISTS或设置变量hive.exec.drop.ignorenonexistent为true。

ALTER TABLE page_view DROP PARTITION (dt='2008-08-08', country='us');

4.4.2.7、    分区存档（和解档）

ALTER TABLE table_name ARCHIVE PARTITION partition_spec;

ALTER TABLE table_name UNARCHIVE PARTITION partition_spec;

归档功能是移动的分区的文件到一个Hadoop归档（HAR）。请注意，只是文件的数量减少; HAR不提供任何压缩。见Language Manual存档 以了解更多信息

4.4.3、  改变表或分区

4.4.3.1、    ALTER TABLE /分区文件格式

ALTER TABLE table_name [PARTITION partition_spec] SET FILEFORMAT file_format;

这个语句更改表（或分区的）文件格式。有关可用file_format选项，看上面的CREATE TABLE部分。此操作只更改表的元数据。任何现有数据的转换必须Hive外完成。

4.4.3.2、    ALTER TABLE /分区位置

ALTER TABLE table_name [PARTITION partition_spec] SET LOCATION "new location";

4.4.3.3、    ALTER TABLE /分区TOUCH

ALTER TABLE table_name TOUCH [PARTITION partition_spec];

TOUCH读取的元数据，并将其写回。这具有使前/后执行挂钩触发的效果。一个示例是：如果您有一个钩子记录所有已修改的表/分区，以及一个直接更改HDFS上文件的外部脚本。因为脚本修改了Hive之外的文件，所以这个修改不会被钩子记录下来。外部脚本可以调用Touch来触发钩子，并将所述表或分区标记为修改后的表或分区。

另外，如果我们将可靠的最后修改时间合并起来，以后可能会很有用。TOUCH也会更新那个时间。

注意，如果TOUCH的表或分区不存在，它也不会创建表或分区。(请参见创建表。)

4.4.3.4、    改变表/分区保护

版本信息

由Hive0.7.0开始（HIVE-1413）。在HIVE 0.8.0在CASCADE子句加入NO_DROP（HIVE-2605）。

此功能在Hive2.0.0已被删除。此功能由Hive几个可用的安全选项之一替代（见基于SQL标准Hive授权）。见HIVE-11145 了解详情。

ALTER TABLE table_name [PARTITION partition_spec] ENABLE|DISABLE NO_DROP [CASCADE];

ALTER TABLE table_name [PARTITION partition_spec] ENABLE|DISABLE OFFLINE;

数据保护可以在任一表或分区级别设置。启用NO_DROP防止表被删除。启用OFFLINE防止查询表或分区中的数据，但元数据仍然可以被访问。

如果在一个表中的任何分区NO_DROP启用，该表不能删除。相反，如果一个表启用NO_DROP之后，分区仍然可能被删除，但NO_DROP CASCADE分区不能被删除，除非删除partition命令指定IGNORE PROTECTION。

4.4.3.5、    改变表/分区紧缩

版本信息

在Hive版本0.13.0及后续版本，当事务正在被使用，ALTER TABLE语句可以要求紧缩表或分区。在Hive发布1.3.0和2.1.0后，当事务正在被使用时，ALTER TABLE ... COMPACT语句可以包括TBLPROPERTIES子句，或者是改变紧缩MapReduce工作属性或覆盖任何其他Hive表的属性。更多细节可以见在这里。

ALTER TABLE table_name [PARTITION (partition_key = 'partition_value' [, ...])]

  COMPACT 'compaction_type'[AND WAIT]

  [WITH OVERWRITE TBLPROPERTIES ("property"="value" [, ...])];

一般来说，在使用Hive事务时，您不需要请求紧缩，因为系统将检测到它们的需要并自动启动紧缩。但是，如果关闭了表的紧缩功能，或者希望在系统不选择的情况下紧缩表，则ALTER TABLE可以启动紧缩。默认情况下，该语句将紧缩请求排队，然后返回。若要查看紧缩的进度，请使用“SHOW COMPACTIONS”。从Hive2.2.0开始，可以指定“AND WAIT”来让操作等待紧缩完成。

该compaction_type可以是MAJOR 或者 MINOR。请参阅Hive事务的基本设计部分以获取更多信息。

4.4.3.6、    改变表/分区串联

版本信息

在Hive 0.8.0中， 增加了使用串联命令块级快速合并小型小RCFiles的支持。在Hive版本0.14.0 ，添加了使用串联命令条带（stripe）级别快速合并ORC小文件的支持。

ALTER TABLE table_name [PARTITION (partition_key = 'partition_value' [, ...])] CONCATENATE;

如果表或分区包含许多小的RCFile或ORC文件，那么上面的命令将它们合并成更大的文件。对于RCFile，合并发生在块级，而对于ORC文件，合并发生在条带（stripe）级，从而避免了数据解压缩和解码的开销。

4.4.3.7、    ALTER TABLE /分区更新列

版本信息

在Hive释放3.0.0中加入该命令，让用户同步SERDE存储模式信息到metastore。

ALTER TABLE table_name [PARTITION (partition_key = 'partition_value' [, ...])] UPDATE COLUMNS;

具有自描述表模式的SERDES表在现实中可能有不同的模式，而存储在Hive Metastore中的表也可能有不同的模式。例如，当用户使用模式url或模式文字创建Avro存储表时，模式将被插入到HMS中，然后无论服务器中的url或文字如何更改，模式都不会在HMS中被更改。这可能导致问题，特别是在与其他Apache组件集成时。

更新列功能为用户提供了让在SERDE所做的任何模式更改能同步到HMS的方式。它适用于表和分区一级，而且显然只适用于其模式未被HMS跟踪的表（见metastore.serdes.using.metastore.for.schema）。在这些后来SERDE类型使用命令将导致错误。

4.4.4、  ALTER COLUMN

4.4.4.1、    列名规则

列名不区分大小写。

版本信息

在Hive版本0.12.0及更早版本，列名只能包含字母数字和下划线字符。

在Hive版本0.13.0及更高版本，默认情况下，列名可以反引号内指定的（`）和含有任何的Unicode字符（HIVE-6013），然而，点（.）和冒号(：）上查询会触发错误。用反引号内指定的任何列名都按字面处理。在反引号字符串中，用双反引号（``）来表示一个反引号字符。在0.13.0之前的行为可以通过设置使用hive.support.quoted.identifiers为none，在这种情况下反引号名称被解释为正则表达式。有关详细信息，请参阅在列名中支持带引号的标识符。

反引号包围符允许使用保留关键字用于列名，以及表名。

4.4.4.2、    更改列名称/类型/位置/注释

ALTER TABLE table_name [PARTITION partition_spec] CHANGE [COLUMN] col_old_name col_new_name column_type

  [COMMENT col_comment] [FIRST|AFTER column_name] [CASCADE|RESTRICT];

这个命令将允许用户更改列的名称，数据类型，注释或位置，或者它们的任意组合。PARTITION子句中从Hive0.14.0到更高版本;

CASCADE | RESTRICT子句中可用Hive1.1.0。ALTER TABLE CHANGE COLUMN与CASCADE配合更改表元数据的列，并对所有分区元数据进行相同的更改。RESTRICT是默认的，限制仅表中列的元数据发生变化。

ALTER TABLE CHANGE COLUMN CASCADE子句将覆盖表/分区相关列的元数据，而不管表或分区的保护模式如何。谨慎使用。

更改列命令将只修改配置单元的元数据，并且不会修改数据。用户应确保表/分区的实际数据与布局的元数据定义一致。

例：

CREATE TABLE test_change (a int, b int, c int);

// First change column a's name to a1.

ALTER TABLE test_change CHANGE a a1 INT;

// Next change column a1's name to a2, its data type to string, and put it after column b.

ALTER TABLE test_change CHANGE a1 a2 STRING AFTER b;

// The new table's structure is:  b int, a2 string, c int.

// Then change column c's name to c1, and put it as the first column.

ALTER TABLE test_change CHANGE c c1 INT FIRST;

// The new table's structure is:  c1 int, b int, a2 string.

// Add a comment to column a1

ALTER TABLE test_change CHANGE a1 a1 INT COMMENT 'this is column a1';

4.4.4.3、    添加/替换列

ALTER TABLE table_name

  [PARTITION partition_spec]                 -- (Note: Hive 0.14.0 and later)

  ADD|REPLACE COLUMNS (col_name data_type [COMMENT col_comment], ...)

  [CASCADE|RESTRICT]                         -- (Note: Hive 1.1.0 and later)

ADD COLUMNS允许您将新列添加到现有列之后，分区列之前。这个特性支持Avro的表， Hive0.14及更高版本。

REPLACE COLUMNS删除所有现有列，并增加了新的一组列。这只能是在表是本地SERDE（DynamicSerDe，MetadataTypedColumnsetSerDe，LazySimpleSerDe和ColumnarSerDe）的情况下完成。请参阅HiveSERDE以获取更多信息。REPLACE COLUMNS还可以用来删除列。例如，“ ALTER TABLE test_change REPLACE COLUMNS (a int, b int);”将于test_change的模式中删除列“C”。

PARTITION子句从Hive0.14.0 开始到更高版本;

CASCADE | RESTRICT子句中可用Hive1.1.0。ALTER TABLE CHANGE COLUMN与CASCADE配合更改表元数据的列，并对所有分区元数据进行相同的更改。RESTRICT是默认的，限制仅表中列的元数据发生变化。

ALTER TABLE ADD或REPLACE COLUMNS CASCADE将覆盖表分区的列的元数据，无视表或分区的的保护模式。请谨慎使用。

列更改命令将只修改配置单元的元数据，并且不会修改数据。用户应确保表/分区的实际数据与布局的元数据定义一致。

4.4.4.4、    部分分区规范

从Hive 0.14(hive-8411)开始，用户就可以为上述ALTER列语句提供部分分区规范，类似于动态分区。因此，不需要为需要更改的每个分区发出ALTER列语句：

ALTER TABLE foo PARTITION (ds='2008-04-08', hr=11) CHANGE COLUMN dec_column_name dec_column_name DECIMAL(38,18);

ALTER TABLE foo PARTITION (ds='2008-04-08', hr=12) CHANGE COLUMN dec_column_name dec_column_name DECIMAL(38,18);

...

...您可以使用带有部分分区规范的单个ALTER语句一次更改许多现有分区：

// hive.exec.dynamic.partition needs to be set to true to enable dynamic partitioning with ALTER PARTITION

SET hive.exec.dynamic.partition = true;

// This will alter all existing partitions in the table with ds='2008-04-08' -- be sure you know what you are doing!

ALTER TABLE foo PARTITION (ds='2008-04-08', hr) CHANGE COLUMN dec_column_name dec_column_name DECIMAL(38,18);

// This will alter all existing partitions in the table -- be sure you know what you are doing!

ALTER TABLE foo PARTITION (ds, hr) CHANGE COLUMN dec_column_name dec_column_name DECIMAL(38,18);

类似动态分区，hive.exec.dynamic.partition必须设置为true，以便在ALTER分区期间启用部分分区规范。以下操作支持此操作：

更改列

添加列

更换列

文件格式

Serde属性

五、创建/删除/更改视图

创建视图

删除视图

改变视图属性

Alter View As Select

5.1、 创建视图

CREATE VIEW [IF NOT EXISTS] [db_name.]view_name [(column_name [COMMENT column_comment], ...) ]

  [COMMENT view_comment]

  [TBLPROPERTIES (property_name = property_value, ...)]

  AS SELECT ...;

CREATE VIEW创建给定名称的视图。如果一个具有相同名称表或视图已经存在，则会引发错误。您可以使用IF NOT EXISTS跳过错误。

如果没有提供列名，该视图的列的名称将被自动地从SELECT子句投影列表达式取得。（如果SELECT包含无别名的标量表达式如X + Y，所得到的视图列名称将在形式如_C0，_c1等）。重命名列时，还可以选择提供列注释。(注释不会自动从基础列继承)。

如果定义视图的SELECT表达式是无效的，CREATE VIEW语句将失败。

注意，视图是没有相关联存储的纯逻辑对象（除了物化视图，该特性从Hive 2.3.0开始支持。）

当一个查询引用视图时，将计算视图的定义，以便生成一组查询结果集供进一步处理。(这是一种概念描述；事实上，作为查询优化的一部分，Hive可以将视图的定义与查询的定义结合起来，例如将过滤器从查询向下推到视图中（fan：即Where过滤条件下推至From子句）。)

视图的模式在视图创建时被冻结；对底层表的后续更改(例如添加列)将不会反映在视图的模式中。如果以不兼容的方式删除或更改基础表，则随后查询无效视图的尝试将失败。

视图只读的，也不可以作为LOAD / INSERT / ALTER的目标。如果需要改变元数据，请参见ALTER VIEW。

视图可以包含ORDER BY和LIMIT子句。如果引用视图的查询也包含这些子句，则在视图子句之后(以及在查询中的任何其他操作之后)计算查询级别的子句。例如，如果视图指定LIMIT 5，并将引用查询执行为(select * from v LIMIT 10)，则最多将返回5行。

从Hive 0.13.0开始，视图的SELECT语句可以包含一个或多个公共表达式(CTE)，如SELECT语法所示。有关CREATE VIEW语句中CTE的示例，请参见公共表达式。

例：

CREATE VIEW onion_referrers(url COMMENT 'URL of Referring page')

  COMMENT 'Referrers to The Onion website'

  AS

  SELECT DISTINCT referrer_url

  FROM page_view

  WHERE page_url='http://www.theonion.com';

使用SHOW CREATE TABLE来显示创建视图的CREATE VIEW语句。从Hive2.2.0，SHOW VIEWS显示在数据库中的视图列表。

版本信息

最初，视图的文件格式被硬编码为SequenceFile。从Hive2.1.0（HIVE-13736），视图与表和索引都遵循hive.default.fileFormat和hive.default.fileformat.managed属性的默认值。

5.2、 删除视图

DROP VIEW [IF EXISTS] [db_name.]view_name;

DROP VIEW移除指定视图元数据。（对视图使用DROP TABLE是非法的）。

当删除其他视图中引用的视图时，不会给出警告（依赖视图会非法引用而无效，并必须由用户删除或重新创建）。

从Hive0.7.0到更高版本，DROP一个不存在的视图会返回错误，除非指定IF EXISTS或设置变量hive.exec.drop.ignorenonexistent为true。

例：

DROP VIEW onion_referrers;

5.3、 改变视图属性

ALTER VIEW [db_name.]view_name SET TBLPROPERTIES table_properties;

table_properties:

  : (property_name = property_value, property_name = property_value, ...)

与ALTER TABLE一样，你可以用这个语句给视图添加自己的元数据。

5.4、 Alter View As Select

ALTER VIEW [db_name.]view_name AS select_statement;

Alter View As Select改变一个已存在视图的定义。语法类似于用于CREATE VIEW，效果和CREATE OR REPLACE VIEW相同。

注：该视图必须已经存在，且如果该视图有分区，它不能用ALTER VIEW AS SELECT所取代。(fan:视图也可以有分区吗？)

六、创建/删除/ ALTER INDEX

本节提供了一个简要介绍了Hive Index，更详细的文档如下：

Hive索引概述

索引设计文件

在Hive0.12.0和更早的版本，索引名在CREATE INDEX和DROP INDEX语句中是区分大小写的。但是，ALTER INDEX需要创建索引的名字是小写字母（见  HIVE-2752）。此错误是在Hive0.13.0修复的，所有HiveQL语句的索引名不再区分大小写。对于0.13.0之前的版本中，最好的做法是所有索引名都使用小写字母。

6.1、 创建索引

CREATE INDEX index_name

  ON TABLE base_table_name (col_name, ...)

  AS index_type

  [WITH DEFERRED REBUILD]

  [IDXPROPERTIES (property_name=property_value, ...)]

  [IN TABLE index_table_name]

  [

     [ ROW FORMAT ...] STORED AS ...

     | STORED BY ...

  ]

  [LOCATION hdfs_path]

  [TBLPROPERTIES (...)]

  [COMMENT "index comment"];

CREATE INDEX在表上使用给定的列创建索引。请参阅CREATE INDEX设计文档。

6.2、 DROP INDEX

DROP INDEX [IF EXISTS] index_name ON table_name;

DROP INDEX删除索引，以及删除索引表。

在Hive0.7.0或更高版本中，如果索引不存在，DROP将返回一个错误，除非指定了IF EXISTS或设置变量hive.exec.drop.ignorenonexistent  为true。

6.3、 ALTER INDEX

ALTER INDEX index_name ON table_name [PARTITION partition_spec] REBUILD;

ALTER INDEX ... REBUILD为使用WITH DEFERRED REBUILD子句的索引建立索引或重建先前建立的索引。如果指定分区，那么只有该分区重建。

七、创建/删除宏

Bug修复：

在Hive1.3.0和2.0.0之前，当处理同一行数据多次调用HiveQL宏时，即使参数是不同，Hive对于所有调用也返回相同的结果。（参见HIVE-11432）。

在Hive 1.3.0和2.0.0之前，当处理同一行数据调用多个HiveQL宏时， ORDER BY子句可能会给错误的结果。（参见HIVE-12277）。

在Hive2.1.0之前，当处理同一行数据调用多个HiveQL宏时，后面宏的结果会被第一个宏的结果覆盖。（参见HIVE-13372）。

从Hive0.12.0引入HiveQL宏，之前他们只能在Java中创建。

7.1、 创建临时宏

CREATE TEMPORARY MACRO macro_name([col_name col_type, ...]) expression;

CREATE TEMPORARY MACRO创建可作为可选参数化表达式的宏。宏的持续时间存在当前会话中。

例子：

CREATE TEMPORARY MACRO fixed_number() 42;

CREATE TEMPORARY MACRO string_len_plus_two(x string) length(x) + 2;

CREATE TEMPORARY MACRO simple_add (x int, y int) x + y;

7.2、 删除临时宏

DROP TEMPORARY MACRO [IF EXISTS] macro_name;

如果宏不存在，DROP TEMPORARY MACRO将返回错误，除非指定IF EXISTS。

八、创建/删除/重新装载函数

8.1、 临时函数

8.1.1、  创建临时函数

CREATE TEMPORARY FUNCTION function_name AS class_name;

使用这个语句允许您创建一个由class_name实现的函数。只要会话持续，就可以在Hive查询中使用此函数。您可以使用Hive类路径中的任何类。您也可以通过执行‘Add JAR’语句将JAR添加到类路径中。

有关如何从Hive类路径添加/删除文件的更多信息，请参阅CLI部分Hive Interactive Shell命令，包括Hive参考资料。使用它，您可以注册用户定义的函数(UDF)。

有关创建自定义UDF的一般信息，请参见Hive插件。

8.1.2、  删除临时函数

您可以按如下删除UDF：

DROP TEMPORARY FUNCTION [IF EXISTS] function_name;

在Hive0.7.0或更高版本中，如果该函数不存，DROP会返回一个错误，除非指定IF EXISTS或设置变量hive.exec.drop.ignorenonexistent为true。

8.2、 永久函数

在Hive0.13或更高版本中，函数可以注册到metastore，这样他们就可以在查询中引用而无需在每个会话创建临时函数。

8.2.1、  创建函数

CREATE FUNCTION [db_name.]function_name AS class_name

  [USING JAR|FILE|ARCHIVE 'file_uri' [, JAR|FILE|ARCHIVE 'file_uri'] ];

这个语句允许您创建一个由class_name实现的函数。JAR、文件或档案可以使用Using子句指定，将其添加到环境中的；当函数第一次被Hive会话引用时，这些资源将被添加到环境中，就像已经发出Add JAR/File一样。如果Hive没有处于本地模式，那么资源位置必须是一个非本地URI，例如HDFS目录。

该函数将被加入到指定的数据库，或在该函数的创建时的当前数据库。该函数可以通过完全限定符的函数名（db_name.function_name）引用，如果函数位于当前数据库中，则可以不加限定符引用该函数。

8.2.2、  删除函数

DROP FUNCTION [IF EXISTS] function_name;

如果该函数不存在，DROP返回一个错误，除非指定IF EXISTS或设置变量hive.exec.drop.ignorenonexistent为真。

8.2.3、  重新加载函数

RELOAD FUNCTION;

作为HIVE-2573，在一个HiveCLI会话创建永久的函数可能不会反映在创建函数之前被启动的HiveServer2或其他HiveCLI会话中。允许在HiveServer2或HiveCLI会话中执行RELOAD FUNCTION，以便装载其他HiveCLI会话所做的永久函数的任何变更。

九、创建/删除/授予/撤销角色和权限

Hive废弃的授权模式/Legacy模式，提供了以下DDL语句的信息：

CREATE ROLE

GRANT ROLE

REVOKE ROLE

GRANT privilege_type

REVOKE privilege_type

DROP ROLE

SHOW ROLE GRANT

SHOW GRANT

有关Hive 0.13.0和更高版本中基于SQL标准的授权，请参见以下DDL语句：

角色管理命令

CREATE ROLE

GRANT ROLE

REVOKE ROLE

DROP ROLE

SHOW ROLES

SHOW ROLE GRANT

SHOW CURRENT ROLES

SET ROLE

SHOW PRINCIPALS

对象权限命令

GRANT privilege_type

REVOKE privilege_type

SHOW GRANT

十、显示

显示数据库

显示表/视图/分区/索引

显示表

显示视图

显示分区

显示表/分区扩展

显示表属性

显示创建表

显示索引

显示列

显示函数

显示授予的角色和权限

显示锁

显示配置

显示事务

显示紧缩

这些语句提供了一种查询Hive Metastore以获取该Hive系统中可访问的现有数据和元数据的方法。

10.1、    显示数据库

SHOW (DATABASES|SCHEMAS) [LIKE 'identifier_with_wildcards'];

SHOW DATABASES或SHOW SCHEMAS列出所有在metastore定义的数据库。SCHEMA和数据库的用途是可以互换的,他们的含义相同。

可选的LIKE子句允许使用正则表达式筛选数据库列表。正则表达式中的通配符只能是匹配任意字符的“*”或表示可选的“|”。例如‘employees’、‘emp*’、‘emp*|*ee’，所有这些都将与名为“employees”的数据库相匹配。

10.2、    显示表/视图/分区/索引

10.2.1、     显示表

SHOW TABLES [IN database_name] ['identifier_with_wildcards'];

SHOW TABLES列出当前数据库所有的基础表和视图（或者使用IN或FROM子句显式命名的数据库）。表名与可选的正则表达式匹配。正则表达式中的通配符只能是任意字符的“*”或是表示可选的“|”。例如‘page_view’、‘page_v*’、‘*view|page*’，所有这些都将与‘page_view’视图相匹配。匹配视图按字母顺序列出。如果在Metastore中没有找到匹配的视图，则也不会报错。如果没有给出正则表达式，则列出所选数据库中的所有表和视图。

10.2.2、     显示视图

版本信息

在Hive2.2.0中通过hive-14558引入。

SHOW VIEWS [IN/FROM database_name] [LIKE 'pattern_with_wildcards'];

SHOW VIEWS列出当前数据库所有的视图（或使用IN或FROM子句显式命名的数据库）。视图名与可选的正则表达式匹配。正则表达式中的通配符只能是任意字符的“*”或是表示可选的“|”。例如‘page_view’、‘page_v*’、‘*view|page*’，所有这些都将与‘page_view’视图相匹配。匹配视图按字母顺序列出。如果在Metastore中没有找到匹配的视图，则也不会报错。如果没有给出正则表达式，则列出所选数据库中的所有视图。

例子

SHOW VIEWS;             -- show all views in the current database

SHOW VIEWS 'test_*';    -- show all views that start with "test_"

SHOW VIEWS '*view2';    -- show all views that end in "view2"

SHOW VIEWS LIKE 'test_view1|test_view2';   -- show views named either "test_view1" or "test_view2"

SHOW VIEWS FROM test1; -- show views from database test1

SHOW VIEWS IN test1;   -- show views from database test1 (FROM and IN are same)

SHOW VIEWS IN test1 "test_*"; -- show views from database test2 that start with "test_"

10.2.3、     显示分区

SHOW PARTITIONS table_name;

SHOW PARTITIONS列出所有给表中现有的分区。分区是按字母顺序列出。

版本信息

从Hive 0.6开始，显示分区可以过滤分区列表，如下所示。

另外，还可以指定一个分区规范的部分来筛选结果列表。

例子：

SHOW PARTITIONS table_name PARTITION(ds='2010-03-03');            -- (Note: Hive 0.6 and later)

SHOW PARTITIONS table_name PARTITION(hr='');                    -- (Note: Hive 0.6 and later)

SHOW PARTITIONS table_name PARTITION(ds='2010-03-03', hr='');   -- (Note: Hive 0.6 and later)

版本信息

从Hive0.13.0开始，SHOW分区可以指定一个数据库（HIVE-5912）。

SHOW PARTITIONS [db_name.]table_name [PARTITION(partition_spec)];   -- (Note: Hive 0.13.0 and later)

例：

SHOW PARTITIONS databaseFoo.tableBar PARTITION(ds='2010-03-03', hr='');   -- (Note: Hive 0.13.0 and later)

10.2.4、     扩展显示表/分区

SHOW TABLE EXTENDED [IN|FROM database_name] LIKE 'identifier_with_wildcards' [PARTITION(partition_spec)];

SHOW TABLE EXTENDED将列出匹配给定正则表达式所有表的信息。如果指定分区标识，则用户不能对表名使用正则表达式。此命令的输出包括像totalNumberFiles，totalFileSize，maxFileSize，minFileSize，LastAccessTime和lastUpdateTime等等基本表信息和文件系统信息。如果分区存在，它将输出给定分区的文件系统信息，而不是表的文件​​系统信息。

例

hive> show table extended like part_table;

OK

tableName:part_table

owner:thejas

location:file:/tmp/warehouse/part_table

inputformat:org.apache.hadoop.mapred.TextInputFormat

outputformat:org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat

columns:struct columns { i32 i}

partitioned:true

partitionColumns:struct partition_columns { string d}

totalNumberFiles:1

totalFileSize:2

maxFileSize:2

minFileSize:2

lastAccessTime:0

lastUpdateTime:1459382233000

10.2.5、     显示表属性

SHOW TBLPROPERTIES tblname;

SHOW TBLPROPERTIES tblname("foo");

第一种形式列出了所有的表属性，每行由制表符分隔。该命令的第二种形式只打印请求属性的值。

欲了解更多信息，请参阅上面创建表的TBLPROPERTIES内容。

10.2.6、     显示创建表

SHOW CREATE TABLE ([db_name.]table_name|view_name);

SHOW CREATE TABLE展示了创建一个给定的表CREATE TABLE语句，或者展示创建一个给定视图的CREATE VIEW语句。

10.2.7、     显示索引

SHOW [FORMATTED] (INDEX|INDEXES) ON table_with_index [(FROM|IN) db_name];

SHOW INDEX显示特定列上的所有索引，以及有关它们的信息：索引名称、表名、用作键的列名、索引表名称、索引类型和注释。如果使用FORMATTED关键字，则为每一列打印列标题，即按列输出。

10.3、    显示列

SHOW COLUMNS (FROM|IN) table_name [(FROM|IN) db_name];

SHOW COLUMNS显示表中包括分区列的所有列。

版本信息

SHOW COLUMNS（FROM | IN）TABLE_NAME [（FROM | IN）DB_NAME]  [ LIKE 'pattern_with_wildcards'];

在Hive3.0新增 （HIVE-18373）

SHOW COLUMNS列出表中的所有列，其名称与可选正则表达式相匹配。正则表达式中只能是匹配任意字符的“*”或表示可选的“|”。例如‘cola’，‘col*’，‘*a|col*’，所有这些都将与‘cola’列相匹配。匹配列按字母顺序列出。如果在表中没有找到匹配的列，也不会报告错误。如果没有给出正则表达式，则列出所选表中的所有列。

例子

-- SHOW COLUMNS

CREATE DATABASE test_db;

USE test_db;

CREATE TABLE foo(col1 INT, col2 INT, col3 INT, cola INT, colb INT, colc INT, a INT, b INT, c INT);

-- SHOW COLUMNS basic syntax

SHOW COLUMNS FROM foo;                            -- show all column in foo

SHOW COLUMNS FROM foo "*";                        -- show all column in foo

SHOW COLUMNS IN foo "col*";                       -- show columns in foo starting with "col"                 OUTPUT col1,col2,col3,cola,colb,colc

SHOW COLUMNS FROM foo '*c';                       -- show columns in foo ending with "c"                     OUTPUT c,colc

SHOW COLUMNS FROM foo LIKE "col1|cola";           -- show columns in foo either col1 or cola                 OUTPUT col1,cola

SHOW COLUMNS FROM foo FROM test_db LIKE 'col*';   -- show columns in foo starting with "col"                 OUTPUT col1,col2,col3,cola,colb,colc

SHOW COLUMNS IN foo IN test_db LIKE 'col*';       -- show columns in foo starting with "col" (FROM/IN same)  OUTPUT col1,col2,col3,cola,colb,colc

-- Non existing column pattern resulting in no match

SHOW COLUMNS IN foo "nomatch*";

SHOW COLUMNS IN foo "col+";                       -- + wildcard not supported

SHOW COLUMNS IN foo "nomatch";

10.4、    显示函数

SHOW FUNCTIONS "a.*";

SHOW FUNCTIONS列表显示所有匹配正则表达式的用户自定义函数和内置函数。要获得所有函数使用“.*”

10.5、    显示授予的角色和权限

Hive弃用授权模式/Legacy模式，其具有这些SHOW语句：

SHOW ROLE GRANT

SHOW GRANT

在Hive0.13.0及更高版本中，基于标准SQL的授权有以下SHOW语句：

SHOW ROLE GRANT

SHOW GRANT

SHOW CURRENT ROLES

SHOW ROLES

SHOW PRINCIPALS

10.6、    显示锁

SHOW LOCKS <table_name>;

SHOW LOCKS <table_name> EXTENDED;

SHOW LOCKS <table_name> PARTITION (<partition_spec>);

SHOW LOCKS <table_name> PARTITION (<partition_spec>) EXTENDED;

SHOW LOCKS (DATABASE|SCHEMA) database_name;

SHOW锁显示在表或分区上的锁。有关锁的信息，请参阅Hive并发模型。

SHOW LOCKS (DATABASE|SCHEMA)从Hive0.13支持数据库（见HIVE-2093）和从Hive0.14支持SCHEMA（见HIVE-6601）。SCHEMA和数据库是可以互换的,他们的含义相同。

当Hive正在使用事务时，SHOW LOCKS返回如下信息（见HIVE-6460）：

  • 数据库名称
  • 表名
  • 分区名称（如果表是分区表）
  • 锁处于的状态，其取值如下：
    • “持有” - 请求者持有锁
    • “等待” - 请求者正在等待锁
    • “终止” - 锁已超时，但还没有被清理
  • 如果这个锁处于“等待”状态，则锁定该锁的ID。
  • 锁的类型，其可以是：
    • “独占” - 没有人能同时持有锁（主要由DDL操作获得，如DROP表）
    • “shared_read” - 可以在同一时间任意数量的shared_read锁定相同的资源（通过读取获得；令人困惑的是，插入操作也获得shared_read锁）
    • “shared_write” - 可同时任意数量shared_read锁定同一资源，但不允许其他shared_write（通过更新而获得和删除）
  • 与此锁相关联的事务ID，如果有的话
  • 上一次持有该锁的所有者发送心跳时间，可表明它仍然活着
  • 获取锁的时间，如果它已被获取
  • 请求锁的Hive用户
  • 用户运行程序的主机
  • 代理信息——帮助识别发出锁请求的实体的字符串。对于SQL客户端来说，这是查询ID，对于流客户端来说，它可能是Storm Blot ID（Storm）。

10.7、    显示CONF

SHOW CONF <configuration_name>;

SHOW CONF返回指定配置属性的描述。

默认值

需要的类型

描述

需要注意的是，SHOW CONF不显示配置属性的当前值。对于当前属性的设置，在CLI或HiveQL脚本、在Beeline中使用“set”命令（请参阅命令）（见Beeline Hive命令）。

10.8、    显示事务

SHOW TRANSACTIONS;

SHOW TRANSACTIONS供管理员在启用Hive事务时使用。它返回系统中所有当前打开和中止的事务列表，包括以下信息：

  • 事务ID
  • 事务状态
  • 启动事务的用户
  • 事务开始的服务器
  • 当事务开始的时间戳（自Hive2.2.0开始）
  • 最后一次心跳检测的时间戳（自Hive2.2.0开始 ）

10.9、    显示Compactions

SHOW COMPACTIONS;

当启用Hive事务时，SHOW COMPACTIONS返回当前正在紧缩或计划紧缩的表和分区的列表，包括以下信息：

  • “CompactionId” – 唯一标识的内部ID（从Hive3.0开始）
  • “Database” - Hive数据库名称
  • “Table” - 表名
  • “Partition” - 分区名称（如果表是分区表）
  • “Type” - 无论是最大或最小的紧缩
  • “State” – 紧缩任务所处的状态，可以是：
    • “初始化” - 在队列中等待紧缩
    • “工作中” – 正在紧缩
    • “准备清理” - 紧缩已经完成，旧文件正安排清洁
    • “失败” - 任务失败。metastore日志中将有更多的细节。
    • “成功” - OK
    • “尝试中” - 试图安排紧缩，但失败了。metastore日志中将有更多的信息。
  • “Worker” - 做紧缩工作者线程（只有在工作状态）的线程ID
  • “开始时间” - 在该紧缩的开始时间（仅当在工作或准备清洗的状态）
  • “持续时间（毫秒）” –紧缩所用的时间（从Hive2.2 ）
  • “HadoopJobId” -提交的Hadoop的工作标识（从Hive2.2）

紧缩都是自动启动的，但也可以用ALTER TABLE COMPACT语句手动启动。

十一、    描述

11.1、    描述数据库

DESCRIBE DATABASE [EXTENDED] db_name;

DESCRIBE SCHEMA [EXTENDED] db_name;

DESCRIBE DATABASE显示数据库的名称，它的注释（如果已设置）和在文件系统的根目录位置。

SCHEMA和DATABASE的用途是可以互换的,他们的含义相同。

DESCRIBE SCHEMA在Hive1.1.0加入（HIVE-8803）。

EXTENDED也显示了数据库性属性DBPROPERTIES。

11.2、    Hive2.0+：语法变化

Hive2.0+：新语法

在Hive2.0发布以后，描述表的命令语法有变化，并不向后兼容。详见HIVE-12184。

DESCRIBE [EXTENDED | FORMATTED]

    [db_name.]table_name [PARTITION partition_spec] [col_name ( [.field_name] | [.'$elem$'] | [.'$key$'] | [.'$value$'] )* ];

警告：新语法可能影响到当前的脚本。

不再接受表名和列名之间用点分隔，他们必须要空格分开。DB和TABLENAME用点分隔。复杂类型数据的列名仍然可以包含点。

可选partition_spec必须出现在表名之后、可选的列之前加入。而在之前的语法中，列名出现在表名和partition_spec之间。

例子：

DESCRIBE FORMATTED default.src_table PARTITION (part_col = 100) columnA;

DESCRIBE default.src_thrift lintString.$elem$.myint;

11.3、    描述表/视图/列

DESCRIBE [EXTENDED|FORMATTED]

  [db_name.]table_name[ col_name ( [.field_name] | [.'$elem$'] | [.'$key$'] | [.'$value$'] )* ];

DESCRIBE语句显示给定表包括分区列在内的所有列，如果使用了Extended关键字，则以Thrift序列化形式显示表的元数据，这通常只对调试有用，而不适用于一般用途。如果使用Formatted关键字，则以表格形式显示元数据。

注意：DESCRIBE EXTENDED仅显示在数据加载时收集统计信息(请参阅新创建的表)，以及使用HiveCLI而不是Thrift客户端或Beeline时统计的行数。HIVE-6285将解决这个问题。尽管Analysis Table可以在加载数据后收集统计信息(请参阅现有表)，但目前DESCRIBE EXTENDED没有提供有关行数的信息。

如果表拥有复合类型的列，可以通过使用表名 复合列名（field_name用于结构，'$elem$'用于数组，'$key$'用于map的键，'$value$'用于map的键值）查看该列的属性。您可以递归地指定这一语法，以剖析更复杂的列类型。

对于视图，可以使用DESCRIBE EXTENDED或FORMATTED来获取视图的定义。提供了两个相关属性：由用户指定的原始视图定义和Hive内部使用的扩展定义。

版本信息 - 分区和非分区列

在Hive 0.10.0和更早版本中，在显示描述表的列时，不区分分区列和非分区列，从Hive 0.12.0开始，它们分别显示。

在Hive 0.13.0及更高版本中，配置参数hive.display.partion.cols.separately允许您使用旧的行为，详见Hive6689。有关示例，请参见用于Hive-6689修补程序中的测试用例。

修复bug于Hive0.10.0 - 数据库限定符

在Hive0.7.0中引入了表名的数据库限定符，但是在Hive0.10.0(hive-1977)中的bug修复之前，表名的数据库限定符会破环DESCRIBE。

修复bug于Hive0.13.0 - 带引号的标识符

在Hive 0.13.0之前，DESCRIBE没有接受反引号（'）包围的表标识符，因此不能将DESCRIBE用于与保留关键字(hive-2949和hive-6187)匹配的表名。

从0.13.0开始，当配置参数hive.support.quoted.identifiers的默认值为“column”时(hive-6013),反引号包围内的所有标识符被字面处理。唯一的例外是双反引号 (``)表示单个反引号字符。

11.4、    描述分区

DESCRIBE [EXTENDED | FORMATTED]

[db_name.]table_name [PARTITION partition_spec]

此语句列出给定分区的元数据。输出与Describe TABLE_NAME的输出类似。目前，在准备计划时不使用与特定分区相关联的列信息。从Hive1.2(hive-10307)开始，当hive.typecheck.on.insert设置为true（默认），partition_spec中指定的分区列的值将被验证、转换并归一化为其列类型。这些值可以是数字字符串。

例：

hive> show partitions part_table;

OK

d=abc

hive> DESCRIBE extended part_table partition (d='abc');

OK

i                       int

d                       string                                     

# Partition Information

# col_name              data_type               comment            

d                       string                                     

Detailed Partition Information  Partition(values:[abc], dbName:default, tableName:part_table, createTime:1459382234, lastAccessTime:0, sd:StorageDescriptor(cols:[FieldSchema(name:i, type:int, comment:null), FieldSchema(name:d, type:string, comment:null)], location:file:/tmp/warehouse/part_table/d=abc, inputFormat:org.apache.hadoop.mapred.TextInputFormat, outputFormat:org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat, compressed:false, numBuckets:-1, serdeInfo:SerDeInfo(name:null, serializationLib:org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe, parameters:{serialization.format=1}), bucketCols:[], sortCols:[], parameters:{}, skewedInfo:SkewedInfo(skewedColNames:[], skewedColValues:[], skewedColValueLocationMaps:{}), storedAsSubDirectories:false), parameters:{numFiles=1, COLUMN_STATS_ACCURATE=true, transient_lastDdlTime=1459382234, numRows=1, totalSize=2, rawDataSize=1})

Time taken: 0.325 seconds, Fetched: 9 row(s)

hive> DESCRIBE formatted part_table partition (d='abc');

OK

# col_name              data_type               comment            

i                       int                                        

# Partition Information

# col_name              data_type               comment            

d                       string                                     

# Detailed Partition Information

Partition Value:        [abc]

Database:               default

Table:                  part_table

CreateTime:             Wed Mar 30 16:57:14 PDT 2016

LastAccessTime:         UNKNOWN

Protect Mode:           None

Location:               file:/tmp/warehouse/part_table/d=abc

Partition Parameters:

        COLUMN_STATS_ACCURATE   true

        numFiles                1

        numRows                 1

        rawDataSize             1

        totalSize               2

        transient_lastDdlTime   1459382234         

# Storage Information

SerDe Library:          org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe

InputFormat:            org.apache.hadoop.mapred.TextInputFormat

OutputFormat:           org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat

Compressed:             No

Num Buckets:            -1

Bucket Columns:         []

Sort Columns:           []

Storage Desc Params:

        serialization.format    1

Time taken: 0.334 seconds, Fetched: 35 row(s)

11.5、    显示列统计信息

版本信息

截止到Hive0.14.0，见HIVE-7050和HIVE-7051。（ANALYZE TABLE的选项FOR COLUMNS 从Hive0.10.0开始）。

ANALYZE TABLE table_name COMPUTE STATISTICS FOR COLUMNS将计算指定表中所有列的列统计信息(如果对表进行分区，则计算所有分区)。要查看收集的列统计信息，可以使用以下语句：

DESCRIBE FORMATTED [db_name.]table_name column_name;                             

DESCRIBE FORMATTED [db_name.]table_name PARTITION (partition_spec) column_name;

见Hive统计：可以对现有表执行ANALYZE TABLE命令取得有关的详细信息。

十二、    终止事务

版本信息

从Hive1.3.0到2.1.0 （见Hive事务）。

ABORT TRANSACTIONS transactionID [ transactionID ...];

ABORT TRANSACTIONS是从Hive Metastore中清除指定事务的ID，这样用户就不需要直接与Metastore交互就可以删除悬空或失败的事务。中止事务在Hive1.3.0和2.1.0中加入（HIVE-12634）。

例：

ABORT TRANSACTIONS 0000007 0000008 0000010 0000015;

这个命令可以与使用SHOW TRANSACTIONS配合使用。后者可以帮助找出被清理的候选事务ID。

翻译说明

1、版本信息

除非版本是1.2以上，过滤诸如 “Version Information As of Hive 0.9”之类的信息

2、个别术语保持英文，如：

Schema

相关文章