HiveQL-DDL总览

一、概览

HiveQL DDL包括以下类型：

1、create database/schema, table, view, function, index

2、drop database/schema, table, view, index

3、truncate table

4、alter database/schema, table, view

5、msck repair table (or alter table recover partitions)

6、show databases/schemas, tables, tblproperties, views, partitions, functions, index[es], columns, create table

7、describe database/schema, table_name, view_name, materialized_view_name

partition语句通常是table语句的选项，show partition除外。

二、数据库

1、创建数据库

create [remote] (database|schema) [if not exists] database_name
  [comment database_comment]
  [location hdfs_path]
  [managedlocation hdfs_path]
  [with dbproperties (property_name=property_value, ...)];

schema和database的用途是可互换的——它们的意思是相同的。

comment是数据库备注

location为数据在HDFS上的存储路径，默认存储路径为/user/hive/warehouse/xxx.db

managedlocation为内部表存储路径，在Hive4.0.0中添加

dbproperties：加一些数据库的属性

remote是在Hive4.0.0中添加的，以支持数据连接器。

2、删除数据库

drop (database|schema) [if exists] database_name [restrict|cascade];

在Hive0.6，中添加了drop database，默认行为是restrict

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;

alter (database|schema) database_name set managedlocation hdfs_path; (hive 4.0.0)

alter database ... set location 语句不会将数据库当前目录的内容移动到新指定的位置。alter database ... set managedlocation 语句也不会将数据库的托管表目录的内容移动到新指定的位置。它们都不会更改与指定数据库下的任何表/分区关联的位置。只更改将为此数据库添加新表的默认父目录。这种行为类似于更改表目录不会将现有分区移动到其他位置。且不能更改有关数据库的任何其他元数据。

4、使用数据库

use database_name;
use default;

use为所有后续的HiveQL语句设置当前数据库。要恢复到默认数据库，请使用关键字“default”而不是数据库名称。要检查当前使用的数据库，请执行：select current_database();

三、连接器

1、创建连接器

create connector [if not exists] connector_name
  [type datasource_type]
  [url datasource_url]
  [comment connector_comment]
  [with dcproperties (property_name=property_value, ...)];

这时Hive4.0.0后的新功能，初始提交包括基于JDBC的数据源（如MYSQL、POSTGRES、DERBY）的连接器实现。

type：

        此连接器连接到的远程数据源的类型。例如mysql。该类型确定driver类和特定于此数据源的任何其他参数。

url：

        远程数据源的url。如果是jdbc数据源，则为jdbc连接url。对于配置单元类型，则为节俭url。

comment：

        此连接器的简短描述。

dcproperties：

        包含为连接器设置的一组名称/值对。远程数据源的凭据是作为dcproperties的一部分指定的，如jdbc存储处理程序文档中所述。所有以前缀“hive.sql”开头的属性都将添加到此连接器映射的表中。

2、删除连接器

drop connector [if exists] connector_name;

如果存在由该连接器映射的数据库，则删除仍然成功。用户在映射数据库中运行类似“显示表”的DDL时会看到错误

3、修改连接器

alter connector connector_name set dcproperties (property_name=property_value, ...);
alter connector connector_name set url new_url;
alter connector connector_name set owner [user|role] user_or_role;

四、表

1、创建表

语法：

create [temporary] [external] table [if not exists] [db_name.]table_name    -- (提示: Hive0.14.0及更高版本中临时可用)
  [(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, ...)                  -- (提示: Hive0.10.0及更高版本中临时可用)]
     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 (...)]  -- (提示: Hive0.6及更高版本中临时可用)
  ]
  [location hdfs_path]
  [tblproperties (property_name=property_value, ...)]   -- (提示: Hive0.6及更高版本中临时可用)
  [as select_statement];   -- (提示: Hive0.5.0及更高版本中可用，不支持外部表)
 
create [temporary] [external] table [if not exists] [db_name.]table_name
  like existing_table_or_view_name
  [location hdfs_path];

data_type 说明

        primitive_type：

                TINYINT、SMALLINT、INT、BIGINT、BOOLEAN、FLOAT、DOUBLE、DOUBLE PRECISION （Hive2.2及更高版本中可用）、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 （Hive0.7.0及更高版本中可用）：

                UNIONTYPE < data_type, data_type, ... > 

file_format 说明

        默认为TEXTFILE，还可以取以下值：SEQUENCEFILE、RCFILE、ORC、PARQUET、AVRO、JSONFILE、INPUTFORMAT input_format_classname OUTPUTFORMAT output_format_classname

column_constraint_specification 说明（可选）

       DEFAULT [default_value]、PRIMARY KEY、UNIQUE、NOT NULL、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 ]、

        [, 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 ]

存储格式

Hive支持内置和自定义开发的文件格式，下面是内置格式的列举：

STORED AS TEXTFILE

        存储为纯文本文件。TEXTFILE是默认的文件格式，除非配置参数hive.default.fileformat有不同的设置。

STORED AS SEQUENCEFILE

        存储为压缩的序列文件

STORED AS ORC

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

STORED AS PARQUET

        存储为Parquet格式，使用ROW FORMAT SERDE。。。存储为INPUTFORMAT。。。OUTPUTFORMAT语法

STORED AS AVRO

        以Avro格式存储

STORED AS RCFILE

        以记录列文件格式存储

STORED AS JSONFILE

        以Json文件格式存储

STORED BY

        以非本机表格式存储。创建或链接到非本机表，例如由HBase、Druid或Accumulo支持的表。

INPUTFORMAT and OUTPUTFORMAT

        在file_format中，将相应的InputFormat和OutputFormat类的名称指定为字符串文字。

例如，“org.apache.hadop.hive.contrib.fileformat.base64.Base64TextInputFormat”。

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

        'INPUTFORMAT“com.hadoop.mapred.DeprecatedLzoTextInputFormat”

                OUTPUTFORMAT“org.apache.hadop.hive.ql.io.HiveIgnoreKeyTextOutputFormat”'

分区表

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

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;

CTAS

        Create Table As Select 的简称

        从查询语句创建表，CTAS创建的表是原子表，这意味着在填充所有查询结果之前，其他用户不会看到该表。CTAS中有两个部分，SELECT部分可以是HiveQL支持的任何SELECT语句。CTAS的CREATE部分从SELECT部分获取结果模式，并创建具有其他表属性（如SerDe和存储格式）的目标表。

        CTAS有以下限制：

                目标表不能是外部表。

                目标表不能是列表分段表。

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;

使用Like语句创建表

        用like语法精确地复制现有的表定义（而不复制其数据）

CREATE TABLE empty_key_value_store

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

2、删除表

语法：

DROP TABLE [IF EXISTS] table_name [PURGE]; 

DROP TABLE删除此表的元数据和数据。数据实际上已移动到垃圾桶，元数据则完全丢失。

删除EXTERNAL表时，表中的数据不会从文件系统中删除，因为不受Hive托管。

如果有视图引用了该表，不会发出任何警告，视图需要手动删除。

3、truncate表

语法：

TRUNCATE [TABLE] table_name [PARTITION partition_spec];

4、修改表

对表重命名：

ALTER TABLE table_name RENAME TO new_table_name;

五、视图

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

2、删除视图

语法：

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

3、修改视图

语法：

ALTER VIEW [db_name.]view_name AS select_statement;

六、索引

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

2、删除索引

语法：

DROP INDEX [IF EXISTS] index_name ON table_name;

3、修改索引

语法：

ALTER INDEX index_name ON table_name [PARTITION partition_spec] REBUILD;

七、宏

1、创建宏

语法：

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

2、删除宏

语法：

DROP TEMPORARY MACRO [IF EXISTS] macro_name;

八、函数

1、临时函数

1、创建临时函数

语法：

CREATE TEMPORARY FUNCTION function_name AS class_name;

此语句允许您创建一个由class_name实现的函数。只要会话持续，您就可以在配置单元查询中使用此功能，也可以使用Hive的类路径中的任何类，也可以通过执行“add JAR”语句将JAR添加到类路径中，可以注册用户定义函数（UDF）

2、删除临时函数

语法：

DROP TEMPORARY FUNCTION [IF EXISTS] function_name;

2、永久函数

1、创建函数

语法：

CREATE FUNCTION [db_name.]function_name AS class_name

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

创建一个由class_name实现的函数。需要添加到环境中的Jars、文件或归档文件可以使用USING子句指定；当Hive会话第一次引用该函数时，这些资源将被添加到环境中，就像发布了ADD JAR/FILE一样。如果配置单元未处于本地模式，则资源位置必须是非本地URI，例如HDFS位置。

该函数将添加到指定的数据库中，或在创建该函数时添加到当前数据库中。可以通过完全限定函数名称（db_name.function_name）来引用该函数，或者如果函数在当前数据库中，则可以在没有限定的情况下引用该函数。

2、删除函数

语法：

DROP FUNCTION [IF EXISTS] function_name;

3、重新加载函数

语法：

RELOAD (FUNCTIONS|FUNCTION);