create/drop/alter/use database
create database语法：
create (database|schema) [if not exists] database_name
[comment database_comment]
[location hdfs_path]
[with dbproperties (property_name=property_value, ...)];
如 create database if not exists db1 comment '测试库' with dbproperties ('creator'='kou','createDate'='2018-08-28');
drop database语法：
drop (database|schema) [if exists] database_name [restrict|cascade];
如 drop database if exists db1;
默认是 restrict，当库里有表的时候，会删除失败；当添加 cascade 时，会清空库并删除库。
alter database语法：
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; 注意，2.2.1、2.4.0及之后版本才支持
如 alter database db1 set dbproperties ('createDate'='2018-08-31');
alter database ... set location 不会将数据库当前目录的内容移动到新指定的位置，只是新表的父目录将会是新位置。
use database语法：
use database_name;
如 use db1; use default;
注意，如果想查看当前操作的是哪个数据库的话，可以用 select current_database(); 命令查看。
create/drop/truncate table
create table语法：
语法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]; -- (note: not supported for external tables)
语法2：
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 -- (note: available in hive 2.2.0 and later)
| 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, ... >
constraint_specification支持：
[primary key (col_name, ...) disable novalidate ]
[constraint constraint_name foreign key (col_name, ...) references table_name(col_name, ...) disable novalidate
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]
|serde serde_name [with serdeproperties (property_name=property_value, property_name=property_value, ...)]
stored as 值支持：
sequencefile
| textfile -- (default, depending on hive.default.fileformat 配置值)
| rcfile
| orc
| parquet
| avro
| jsonfile -- (note: available in hive 4.0.0 and later)
| inputformat input_format_classname outputformat output_format_classname
表名和列名不区分大小写，但SerDe和属性名区分大小写。
表注释和列注释须用单引号括起来。
tblproperties子句可以指定表属性，属性名和属性值必须用单引号或双引号括起来。一些常用的表属性有：
tblproperties ("comment"="table_comment")，在更改表注释或给表添加注释时使用，如alter table pokes set tblproperties ('comment' = 'this is pokes table');
tblproperties ("hbase.table.name"="table_name") 与hbase整合时使用
tblproperties ("immutable"="true") or ("immutable"="false")，默认是"false"，具体见自己翻译的Hive DML文档。
tblproperties ("orc.compress"="ZLIB") or ("orc.compress"="SNAPPY") or ("orc.compress"="NONE")，具体查看ORC Files文档。
tblproperties ("transactional"="false") or ("transactional"="true")，默认是"false"，具体查看Hive Transactions文档。
tblproperties ("compactor.mapreduce.map.memory.mb"="mapper_memory")，具体查看Hive Transactions文档。
tblproperties ("compactorthreshold.hive.compactor.delta.num.threshold"="threshold_num") ，具体查看Hive Transactions文档。
tblproperties("compactorthreshold.hive.compactor.delta.pct.threshold"="threshold_pct")，具体查看Hive Transactions文档。
tblproperties("auto.purge"="false") or ("auto.purge"="true")，默认是"false"。purge是清除的意思，默认会在清除之前放到回收站中。
tblproperties("external"="TRUE") or ("external"="FALSE")。hive2.4.0之前，external属性值必须为大写的"TRUE"或者"FALSE"。hive 2.4.0开始，external属性值不区分大小写。值为“TRUE”时，表示change a managed table to an external table，为"FALSE"时，反之。
在create table语句之前执行 use database_name; 命令，或者使用数据库名称限定表名(database_name.table_name)，可为表指定数据库。hive有个默认数据库，名称是default。
Managed vs External Table 托管表和外部表
默认情况下(即没有external关键字的情况下)，hive会创建托管表(managed table)。托管表的文件、元数据和统计信息都由内部hive进程管理。托管表存储在 hive.metastore.warehouse.dir 指定的路径下(该属性默认值是/user/hive/warehouse)，默认情况下存储在/user/hive/warehouse/database_name.db/table_name目录中(这里指的是hdfs的绝对路径，不是hive服务器的绝对路径)。在创建表的时候，可以用location属性显式指定存储位置。如果删除托管表或分区，则与该表或分区关联的数据和元数据也会被删除。如果drop table的时候没有带purge选项，则会将数据移动到回收箱，如果带了purge，则不会移动到回收箱，而是直接删除。
当hive应该管理表的生命周期或者创建临时表时，就应该创建托管表。
外部表(external table)：
An external table describes the metadata / schema on external files. External table files can be accessed and managed by processes outside of Hive. External tables can access data stored in sources such as Azure Storage Volumes or remote HDFS locations. If the structure or partitioning of an external table is changed, an MSCK REPAIR TABLE table_name statement can be used to refresh metadata information.
可以使用desc formatted table_name; 命令来看一个表是托管表还是外部表，返回的表格中的Table Type值就是表类型。
存储格式：create table table_name(.....) stored as XX;
常用的存储格式有：
textfile：存储为纯文本文件，这是默认格式，由 hive.default.fileformat 属性值指定。使用delimited子句读取分隔文件。通过使用escaped by子句(例如escaped by '\')为分隔符字符启用转义。如果要处理包含这些分隔符字符的数据，就需要转义。也可以使用null defined as子句指定自定义null格式(默认为'\N')。
sequenctfile
rcfile
orc
parquet
jsonfile：hive4.0.0版本之后需新增的一个格式
stored by：存储为一个非native表格式，创建或者关联一个非native表，例如hbase、druid、accumulo中的表。 ？？？
inputformat and outputformat：显式指定一个InputFormat和OutputFormat类(全类名)。InputFormat默认是org.apache.hadoop.mapred.TextInputFormat，OutputFormat默认是org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
row format & serde(serializer and deserializer，序列化和反序列化)
row_format默认是delimited，即row format delimited fields terminated by '\001' lines terminated by '\n'。即field-delimited by ctrl-A and row-delimited by newline。
如果表有array<data_type>类型的字段，则可以使用collection items terminated by char 分隔字段值。char可以是','
如果表有map<primitive_type,data_type>类型的子弹，则可以使用map keys terminated by char 分隔字段值。char可以是','
建表时可以用row format serde子句来指定SerDe，否则将使用默认的org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe。
1、regex：stored as plain text file, translated by regular expression
create table table_name(
)
row format serde 'org.apache.hadoop.hive.serde2.RegexSerDe'
with serdeproperties (
"input.regex" = "([^]*) ([^]*) ([^]*) (-|\\[^\\]*\\]) ([^ \"]*|\"[^\"]*\") (-|[0-9]*) (-|[0-9]*)(?: ([^ \"]*|\".*\") ([^ \"]*|\".*\"))?"
)
stored as textfile;
2、json：stored as plain text file in json format
create table table_name(
)
row format serde 'org.apache.hive.hcatalog.data.JsonSerDe'
stored as textfile;
org.apache.hive.hcatalog.data.JsonSerDe在hive-hcatalog-core.jar中。从hive3.0.0开始，org.apache.hadoop.hive.serde2.JsonSerDe 类被添加到hive Serde中，故可以用如下建表语句：
create table table_name(
)
row format serde 'org.apache.hadoop.hive.serde2.JsonSerDe'
stored as textfile;
从hive4.0.0开始，可以直接存储为json文件，用如下建表语句：
create table table_name(a string, b bigint, ...) stored as jsonfile;
3、csv/tsv：stored as plain text file in csv/tsv format
create table table_name(
)
row format serde 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
stored as textfile;
分区表：
可以使用partitioned by 子句创建分区表。表可以包含一个或者多个分区列，分区列中的每个不同值组合都会对应一个单独的数据目录。此外，可以使用clustered by对表进行分区或者对分区进行再分区，形成桶，并且可以通过sort by在该存储桶中对数据进行排序。这可以提高某些类型的查询的性能。
如：
create table invites (foo int, bar string) partitioned by (ds string);
创建一个分区表，分区列是ds。ds列是可以查询的。
如果有多个分区列，则按照分区定义顺序，第一个分区列是一级分区，第二个分区列是二级分区，第三个分区列是三级分区。。。
从数据存储角度看，第一分区目录下有多个二级分区目录，第二分区目录下有多个三级分区目录。。。可见下面讲解分桶表处的具体例子。
外部表：
用external 关键字创建外部表。外部表不使用hive.metastore.warehouse.dir 目录存储表数据，而是在建表时用location 关键字指定表数据目录，可以指定为任意hdfs目录。
不像托管表，外部表在drop后，location目录中的数据不会删除。
如：
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 '\001' lines terminated by '\n'
stored as textfile
location '<hdfs_location>';
假如location指定/user/data/page_view，则表数据将存储在hdfs /user/data/page_view 目录中。
create table as select(CTAS)
还可以在一个create-table-as-select(CTAS)语句中通过查询结果创建和填充表。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;
CTAS方式建表不能指定字段名，新表的字段名是select 的字段名或者别名(如果指定了别名)，如果select 的不是单纯的某列，而是做了函数处理，如count(foo) 或者如上面的key % 1024，并且又没有指定别名的话，则新表字段名将是_c0、_c1、_c2这种格式。而字段数据类型会根据结果集数据自动映射。
create table like
根据某个表的数据结构创建一个新表，注意不会复制原表的数据。
如：
create table empty_pokes2 like pokes tblproperties ("comment"="pokes like 表");
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;
上例中，page_view 表既是分区表，又是分桶表。分区列是dt 和country，分桶列是userid，排序字段是viewTime。
The CLUSTERED BY and SORTED BY creation commands do not affect how data is inserted into a table – only how it is read. This means that users must be careful to insert data correctly by specifying the number of reducers to be equal to the number of buckets, and using CLUSTER BY and SORT BY commands in their query.
分区表、分桶表的数据是怎么存储的呢？
假如插入一条记录，如下：
insert into table page_view partition (dt='2018-09-07', country='usa') values (1, 2, 'http://baidu.com', 'http://baidu.com', '192.168.1.1');
观察hdfs，这条数据会在hdfs中的/user/hive/warehouse/sx_bdp_ela_safe.db/page_view/dt=2018-09-07/country=usa 目录中32个文件中的某一个文件中。
再插入一条记录，如下：
insert into table page_view partition (dt='2018-09-07', country='china') values (1, 2, 'http://baidu.com', 'http://baidu.com', '192.168.1.1');
观察hdfs，这条数据会在hdfs中的/user/hive/warehouse/sx_bdp_ela_safe.db/page_view/dt=2018-09-07/country=china 目录中的32个文件中的某一个文件中。
再插入一条记录，如下：
insert into table page_view partition (dt='2018-09-08', country='usa') values (1, 2, 'http://baidu.com', 'http://baidu.com', '192.168.1.1');
观察hdfs，这条数据会在hdfs中的/user/hive/warehouse/sx_bdp_ela_safe.db/page_view/dt=2018-09-08/country=usa 目录中32个文件中的某一个文件中。
再插入一条记录，如下：
insert into table page_view partition (dt='2018-09-08', country='china') values (1, 2, 'http://baidu.com', 'http://baidu.com', '192.168.1.1');
观察hdfs，这条数据会在hdfs中的/user/hive/warehouse/sx_bdp_ela_safe.db/page_view/dt=2018-09-08/country=china 目录中的32个文件中的某一个文件中。
上面路径的解释：
/user/hive/warehouse是 hive.metastore.warehouse.dir配置项的默认值，是default数据库数据目录，也可以理解为hive数据根目录
/user/hive/warehouse/sx_bdp_ela_safe.db 是sx_bdp_ela_safe数据库数据目录
/user/hive/warehouse/sx_bdp_ela_safe.db/page_view 是sx_bdp_ela_safe数据库，page_view表的数据目录
/user/hive/warehouse/sx_bdp_ela_safe.db/page_view/dt=2018-09-07 是sx_bdp_ela_safe数据库，page_view表，ds=2018-09-07分区的数据目录
/user/hive/warehouse/sx_bdp_ela_safe.db/page_view/dt=2018-09-07/country=china 是sx_bdp_ela_safe数据库，page_view表，ds=2018-09-07分区，country=china分区的数据目录
在/user/hive/warehouse/sx_bdp_ela_safe.db/page_view/dt=XXX/country=YYY 目录中有32个文件，命名分别是000000_0、000000_1、000000_2 ...
为什么会有32个文件呢？因为创建表的时候CLUSTERED BY(userid) SORTED BY(viewTime) INTO 32 BUCKETS，即以userid的值分桶，分为32个桶。可以理解为hive根据hash_function(userid) mod 32 的值把数据存储到对应的文件中。
如果表的数据足够多，则此文件中会有很多很多的hash_function(userid) mod 32 值相同的数据，这些数据以viewTime字段值升序排列。
skewed tables
省略。。。
temporary tables
临时表
临时表只能在当前会话看到。临时表的数据放在hive.exec.scratchdir 指定的目录中(这里是hdfs目录)，会话结束时数据会被删除。
如果创建的临时表与其他某非临时表重名，则在当前会话中访问的都会是这个临时表。如果不删除此临时表或者重命名此临时表，则在当前会话中不能访问那个重名的非临时表。
临时表有限制，不能有分区列，不能创建索引。
可以更改hive.exec.temporary.table.storage 配置来更改临时表数据的存储策略，支持memory, ssd, default ，默认是default。
constraints
约束
hive2.1.0版本之后才支持constraint。
hive支持未经验证的主键primary key 和外键foregin key 约束。当约束存在时，某些查询可能会更有效率。由于约束未经验证，故需要上游系统在将数据加载到hive前校验数据。
如：
create table pk(id1 int, id2 int, primary key(id1, id2) disable novalidate);
create table fk(id1 int, id2 int, constraint c1 foreign key(id1, id2) references pk(id2, id1) disable novalidate);
drop table
drop table [if exists] table_name [purge];
drop table 会删除表的元数据和数据。如果配置了trash，并且未指定purge，则实际会将数据移到.trash/current 目录。元数据完全丢失。如果指定了purge，表数据不会移到.trash/current 目录，而是直接彻底删除了。
删除external表时，表中的数据不会从文件系统中删除。
删除视图引用的表时，不会给出警告。之后视图会无效。
truncate table
truncate table table_name [partition partition_spec];
partition_spec 语法是
(partition_column = partition_col_value, partition_column = partition_col_value, ...)
如：
truncate table invites partition(ds='2018-09-01', ds='2018-09-02');
truncate表或分区，目标表必须是native/managed 表，否则将会抛出异常，报"Cannot truncate non-native table"。
如果分区表在truncate 时没指定分区，则所有分区数据都会被删除。truncate分区之后，分区不会删除。用show partitions table_name; 命令仍能查看该分区。
alter table/partition/column
alter table 语句可以改变现有表的表结构。可以增加列，增加分区，改变SerDe，增加表属性，更改表名。
alter table partition 语句可以表特定分区的属性。
alter table
rename table
alter table table_name rename to new_table_name;
hive0.6版本之后，hive2.2.0版本之前，重命名managed table 会移动其hdfs location。hive2.2.0版本之后，只有在创建表时没有location 子句且在其数据库目录下创建表时，重命名managed table才会移动该表的location。
alter table properties
alter table table_name set tblproperties table_properties;
table_properties 语法是
(property_name = property_value, property_name = property_value, ...)
可以用此语句给表添加或更改自定义元数据。目前，last_modified_time、last_modified_by会被hive自动添加和管理。可以使用desc formatted table_name; 命令来查看表属性。
alter table comment
alter table table_name set tblproperties ('comment' = new_comment);
add SerDe properties
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。
如：
alter table table_name set serdeproperties ('field.delim' = ',');
field.delim属性不能随便改，改了就查不出数据了。
alter table storage properties
alter table table_name clustered by (col_name, col_name, ...) [stored by (col_name, ...)] into num_buckets buckets;
更改表的物理存储属性。
需要注意的是，以上语句不会重新组织或者格式化表现有的数据，而只会更改表的元数据。之后插入的数据，才会以此表定义存储。
alter table constraints
hive2.1.0版本之后才支持此语法。
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 partition
可以使用alter table 语句的partition 子句添加、重命名、移动、删除分区。
add partitions
alter table table_name add [if not exists] partition partition_spec [location 'location'][, partition partition_spec [location 'location'], ...];
可以使用alter table add partition 来给表添加分区。对非分区表添加分区，会报table is not partitioned but partition spec exists 错误。
如：
alter table invites add if not exists partition (ds='2018-09-15');
rename partition
alter table table_name partition partition_spec rename to partition partition_spec;
如：
alter table invites partition (ds='2018-09-11') rename to partition (ds='2018-09-11 00:00:00');
exchange partition
分区及其数据可以在表之间移动。
语法：
-- 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;
目标表在前面，数据源表在后面。仅支持1.2.2、1.3.0及2.0.0+
把分区数据从一个表移动到另一个表，前提是这两个表数据结构一样(列名和分区列名和数据类型都得一样)，否则会报错Tables have incompatible schemas and their partitions cannot be exchanged。
msck repair table
hive在metastore中存储每个表的分区信息。但是，如果将新分区直接添加到hdfs(例如使用hadoop fs -put 命令)，hive不会感知到这些分区，除非使用alter table table_name add partition partition_spec 命令来添加每个分区。但是如果新分区太多的话，就太麻烦。还有一个最简单的方法，执行msck repair table table_name; 命令。该命令会将在hdfs中但是不在metastore中的分区元数据添加到metastore中。
drop partitions
alter table table_name drop [if exists] partition partition_spec[, partition partition_spec, ...] [purge];
上面语句会同时删除表的分区的元数据和数据。
在hive2.0.0以前，如果表被no_drop cascade 保护，则删除语句需要添加ignore protection，即
alter table table_name drop [if exists] partition partition_spec ignore protection;
在hive2.0.0及之后版本，ignore protection不再支持了。
alter either table or partition
alter table/partition file format
语法：
alter table table_name [partition partition_spec] set fileformat file_format;
上面操作仅更改表的元数据。任何现有数据的转换都必须在hive 之外完成。
alter table/partition location
语法：
alter table table_name [partition partition_spec] set location 'new_location';
alter column
列名是大小写不敏感的。
change column name/type/position/comment
语法：
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];
更改列名，必须指定新列名的数据类型。
ALTER TABLE CHANGE COLUMN with CASCADE command changes the columns of a table's metadata, and cascades the same change to all the partition metadata. RESTRICT is the default, limiting column change only to table metadata.
cascade 和restrict 的区别：只有操作分区表时，这两个关键字作用才有区别。
cascade表示当操作分区表本身时， 目标字段的重命名或者数据类型变更会同步到该表所有分区。如果不显式指定cascade 关键字或是显式指定了restrict 关键字，则目标字段的重命名或者数据类型变更不会同步到该表分区。
案例如下：
create table invites (foo int, bar string) partitioned by (ds string) stored as textfile;
alter table invites add partition (ds='2018-09-16');
alter table invites add partition (ds='2018-09-17');
默认的restrict 方式change 字段：
alter table invites change foo foo2 int;
change完之后，用desc invites; 命令查看表信息，发现foo字段名变为了foo2。用desc invites partition (ds='2018-09-16'); 命令查看ds='2018-09-16' 分区，发现foo字段没有改名，ds='2018-09-17' 分区 foo字段也没有改名。可见用restrict 方式对分区表本身操作change 字段没有同步到分区。
alter table invites change foo2 foo3 string cascade;
change完之后，用desc invites; 命令查看表信息，发现foo2字段名变为了foo3，且数据类型也变为了string。用desc invites partition (ds='2018-09-16'); 命令查看ds='2018-09-16' 分区，发现foo字段变成了foo3，且数据类型变为了string。ds='2018-09-17' 分区 foo字段也变成了foo3，且数据类型也变为了string。由此可见用cascade 方式对分区表本身操作change 字段会同步到分区。
注意，操作分区表某分区时，不能用cascade，只能用默认的restrict，否则会报Alter table partition type rename column does not support cascade。