创建，显示，修改，和删除表
创建表
以下例子创建表
page_view
：
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;
在这个例子中，表的列被指定相应的类型。备注（Comments)可以基于列级别，也可以是表级别。另外，使用PARTITIONED关键词定义的分区列与数据列是不同的，分区列实际上不存储数据。当使用这种方式创建表的时候，我们假设数据文件的内容，字段之间以ASCII 001（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 '1'
STORED AS SEQUENCEFILE;
目前，行分隔符不能指定，因为它不是由Hive决定，而是由Hadoop分隔符。
对表的指定列进行分桶，是一个好的方法，它可以有效地对数据集进行抽样查询。如果没有分桶，则会进行随机抽样，由于在查询的时候，需要扫描所有数据，因此，效率不高。以下例子描述了，在表page_view的userid列上进行分桶的例子：
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 '1'
COLLECTION ITEMS TERMINATED BY '2'
MAP KEYS TERMINATED BY '3'
STORED AS SEQUENCEFILE;
以上例子，通过一个
userid
的哈希函数，表被分成32个桶。在每个桶中的数据，是以
viewTime
升序进行存储。这样组织数据允许用户有效地在这n个桶上进行抽样。合适的排序使得内部操作充分利用熟悉的数据结构来进行更加有效的查询。
CREATE TABLE page_view(viewTime INT, userid BIGINT,
page_url STRING, referrer_url STRING,
friends ARRAY<BIGINT>, properties MAP<STRING, 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 '1'
COLLECTION ITEMS TERMINATED BY '2'
MAP KEYS TERMINATED BY '3'
STORED AS SEQUENCEFILE;
在这个例子，CLUSTERED BY指定列进行分桶，以及创建多少个桶。行格式分隔符指定在hive表中，行如何存储。在这种分隔符情况下，指定了字段是如何结束，集合项（数组和map）如何结束，以及map的key是如何结束的。STORED AS SEQUENCEFILE表示这个数据是以二进制格式进行存储数据在hdfs上。对于以上例子的ROW FORMAT的值和STORED AS表示系统默认值。
表名和列名不区分大小写
数据表列信息查看
浏览表和分区
SHOW TABLES;
列出数据库里的所有的表，也可以这么浏览：
SHOW TABLES 'page.*';
这样将会列出以page开头的表，模式遵循Java正则表达式语法。
SHOW PARTITIONS page_view;
列出表的分区。如果表没有分区，则抛出错误。
DESCRIBE page_view;
列出表的列和列的类型。
DESCRIBE EXTENDED page_view;
列出表的列和表的其他属性。这会打印很多信息，且输出的风格不是很友好，通常用于调试。
DESCRIBE EXTENDED page_view PARTITION (ds='2016-08-08');
列出列和分区的所有属性。这也会打印出许多信息，通常也是用于调试。
修改表
对已有的表进行重命名。如果表的新名字存在，则报错：
ALTER TABLE old_table_name RENAME TO new_table_name;
对已有表的列名进行重命名。要确保使用相同的列类型，且要包含对每个已存在列的一个入口(也就是说，就算不修改其他列的列名，也要把此列另上，否则，此列会丢失）。
ALTER TABLE old_table_name REPLACE COLUMNS (col1 TYPE, ...);
对已有表增加列：
ALTER TABLE tab1 ADD COLUMNS (c1 INT COMMENT 'a new int column', c2 STRING DEFAULT 'def val');
注意：
模式的改变（例如增加列），保留了表的老分区，以免它是一个分区表。所有对这些列或老分区的查询都会隐式地返回一个null值或这些列指定的默认值。
删除表和分区
删除表是相当，表的删除会删除已经建立在表上的任意索引。相关命令是：
DROP TABLE pv_users;
要删除分区。修改表删除分区：
ALTER TABLE pv_users DROP PARTITION (ds='2016-08-08')
注意：此表或分区的任意数据都将被删除，而且可能无法恢复。
加载数据
要加载数据到Hive表有许多种方式。用户可以创建一个“外部表”来指向一个特定的HDFS路径。用这种方法，用户可以使用HDFSput或copy命令，复制一个文件到指定的位置，并且附上相应的行格式信息创建一个表指定这个位置。一旦完成，用户就可以转换数据和插入他们到任意其他的Hive表中。例如，如果文件/tmp/pv_2016-06-08.txt包含逗号分隔的页面访问记录。这需要以合适的分区加载到表page_view，以下命令可以完成这个目标：
CREATE EXTERNAL TABLE page_view_stg(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 '44' LINES TERMINATED BY '12'
STORED AS TEXTFILE
LOCATION '/user/data/staging/page_view';
hadoop dfs -put /tmp/pv_2016-06-08.txt /user/data/staging/page_view
FROM page_view_stg pvs
INSERT OVERWRITE TABLE page_view PARTITION(dt='2016-06-08', country='US')
SELECT pvs.viewTime, pvs.userid, pvs.page_url, pvs.referrer_url, null, null, pvs.ip
WHERE pvs.country = 'US';
其中，‘44’是逗号的ASCII码，‘12’是换页符（NP from feed,new page）。null是作为目标表中的数组和map类型插入，如果指定了合适的行格式，这些值也可以来自外部表。
如果在HDFS上有一些历史数据，用户想增加一些元数据，以便于可以使用Hive来查询和操纵这些数据，这个方法是很有用的。
另外，系统也支持直接从本地文件系统上加载数据到Hive表。表的格式与输入文件的格式需要相同。如果文件/tmp/pv_2016-06-08包含了US数据，然后我们不需要像前面例子那样的任何筛选，这种情况的加载可以使用以下语法完成：
LOAD DATA LOCAL INPATH /tmp/pv_2016-06-08_us.txt INTO TABLE page_view PARTITION(date='2016-06-08', country='US')
路径参数可以是一个目录（这种情况下，目录下的所有文件将被加载），一个文件，或一个通配符（这种情况下，所有匹配的文件会上传）。如果参数是目录，它不能包含子目录。同样，通配符只匹配文件名。
在输入文件/tmp/pv_2016-06-08.txt非常大的情况下，用户可以采用并行加载数据的方式（使用Hive的外部工具）。只要文件在HDFS上－以下语法可以用于加载数据到Hive表：
LOAD DATA INPATH '/user/data/pv_2016-06-08_us.txt' INTO TABLE page_view PARTITION(date='2016-06-08', country='US')
查询和插入数据
Simple Query
Partition Based Query
Joins
Aggregations
Multi Table/File Inserts
Dynamic-Partition Insert
Inserting into Local Files
Sampling
Union All
Array Operations
Map (Associative Arrays) Operations
Custom Map/Reduce Scripts
Co-Groups
Hive查询操作在文档Select,插入操作在文档insert data into Hive Tables from queries和writing data into the filesystem from queries。
简单的查询
对于所有的活跃用户，可以使用以下查询格式：
INSERT OVERWRITE TABLE user_active
SELECT user.*
FROM user
WHERE user.active = 1;
注意：不像SQL，我们老是插入结果到表中。随后我们会描述，用户如何检查这些结果，甚至把结果导出到一个本地文件。你也可以在Beeline或HiveCLI执行以下查询：
SELECT user.*
FROM user
WHERE user.active = 1;
这在内部将会重写到一些临时文件，并在Hive客户端显示。
聚合
统计用户每个性别的人数，可以使用以下查询：
INSERT OVERWRITE TABLE pv_gender_sum
SELECT pv_users.gender, count (DISTINCT pv_users.userid)
FROM pv_users
GROUP BY pv_users.gender;
可以同时做多个聚合，然而，两个聚合函数不能同时用DISTINCT作用于不同的列，以下情况是可以的（DISTINCT作用于相同列）：
INSERT OVERWRITE TABLE pv_gender_agg
SELECT pv_users.gender, count(DISTINCT pv_users.userid), count(*), sum(DISTINCT pv_users.userid)
FROM pv_users
GROUP BY pv_users.gender;
然而，以下情况（DISTINCT作用于不同的列）是不允许的：
INSERT OVERWRITE TABLE pv_gender_agg
SELECT pv_users.gender, count(DISTINCT pv_users.userid), count(DISTINCT pv_users.ip)
FROM pv_users
GROUP BY pv_users.gender;
多表/文件插入
聚合或简单查询的输出可以插入到多个表中，或者甚至是HDFS文件（能够使用HDFS工具进行操纵）。例如，如果沿用前面的“性别分类”，例子如下：
FROM pv_users
INSERT OVERWRITE TABLE pv_gender_sum
SELECT pv_users.gender, count_distinct(pv_users.userid)
GROUP BY pv_users.gender
INSERT OVERWRITE DIRECTORY '/user/data/tmp/pv_age_sum'
SELECT pv_users.age, count_distinct(pv_users.userid)
GROUP BY pv_users.age;
第一个插入语句将结果插入到Hive表中，而第二个插入语句是将结果写到HDFS文件。
动太分区插入
在前面的例子中，我们知道，在插入语句中，只能有一个分区。如果我们想加载到多个分区，我们必须像以下描述来使用多条插入语句：
FROM page_view_stg pvs
INSERT OVERWRITE TABLE page_view PARTITION(dt='2008-06-08', country='US')
SELECT pvs.viewTime, pvs.userid, pvs.page_url, pvs.referrer_url, null, null, pvs.ip WHERE pvs.country = 'US'
INSERT OVERWRITE TABLE page_view PARTITION(dt='2008-06-08', country='CA')
SELECT pvs.viewTime, pvs.userid, pvs.page_url, pvs.referrer_url, null, null, pvs.ip WHERE pvs.country = 'CA'
INSERT OVERWRITE TABLE page_view PARTITION(dt='2008-06-08', country='UK')
SELECT pvs.viewTime, pvs.userid, pvs.page_url, pvs.referrer_url, null, null, pvs.ip WHERE pvs.country = 'UK';
为了加载数据到全部的country分区到指定的日期。我们必须在输入数据中为每个country增加一条插入语句。这是非常不方便的，因为我们需要提前创建且知道已存在哪些country分区列表。如果哪天这些country列表变了，我们必须修改我们的插入语句，也应该创建相应的分区。这也是非常低效的，因为每个插入语句可能都是转换成一个MapReduce作业。
动态分区插入（Dynamic-partition insert)(或multi-partition插入)就是为了解决以上问题而设计的，它通过动态地决定在扫描数据的时候，哪些分区应该创建和填充。这个新的特征是在版本0.6.0加入的。在动态分区插入中，输入列被评估，这行应该插入到哪个分区。如果分区没有创建，它将自动创建这个分区。使用这个特征，我们仅仅需要插入语桀犬吠尧来创建和填充所有需要的分区。另外，因为只有一个插入语句，相应的也只有一个MapReduce作业。相比多个插入语句的情况，这将显著地提高性能且降低Hadoop集群负载。
以下是使用一个插入语句，加载数据到所有country分区的例子：
FROM page_view_stg pvs
INSERT OVERWRITE TABLE page_view PARTITION(dt='2008-06-08', country)
SELECT pvs.viewTime, pvs.userid, pvs.page_url, pvs.referrer_url, null, null, pvs.ip, pvs.country
与多条插入语句相比，动态分区插入有一些语法上的不同：
- country出现在PARTITION后面，但是没有具体的值。这种情况，country就是一个动态分区列。另一方面，dt有一个值，这意味着它是一个静态的分区列。如果一个列是动态分区列，它的值将会使用输入列的值。目前，我们仅仅允许在分区条件的最后一列放置动态分区列，因为分区列的顺序，指示了它的层级次序（意味着dt是根分区，country是子分区）。我们不能这样指定分区（dt,country=’US’)，因为这表示，我们需要更新所有的日期的分区且它的country子分区是‘US’。
一个额外的pvs.country列被加入在查询语句中。这对动态分区列来说，相当于输入列。注意：对于静态分区列，我们不需要添加一个输入列，因为在PARTITION语句中，它的值已经知道。注意：动态分区列的值（不是名字）查出来是有序的，且是放在select语句的最后。
动态分区插入的语义：
对于动态分区列，当已经此分区时，（例如，country='CA'已存在dt根分区下面）如果动态分区插入与输入数据中相同的值（’CA’），它将会被重写（overwritten)。
插入到本地文件
在某些场合，我们需要把输出写到一个本地文件，以便于能用excel表格打开。这可以使用以下命令：
INSERT OVERWRITE LOCAL DIRECTORY '/tmp/pv_gender_sum'
SELECT pv_gender_sum.*
FROM pv_gender_sum;
抽样
抽样语句允许用户对数据抽样查询，而不是全表查询。当前，抽样是对那些在CREATE TABLE语句的CLUSTERED BY修饰的列上。以下例子，我们从表pv_gender_sum表中的32个桶中，选择第3个桶。
INSERT OVERWRITE TABLE pv_gender_sum_sample
SELECT pv_gender_sum.*
FROM pv_gender_sum TABLESAMPLE(BUCKET 3 OUT OF 32);
通常，TABLESAMPLE的语法像这样：
TABLESAMPLE(BUCKET x OUT OF y)
这个y必须是桶的数量的因子或倍数，桶的数量是在创建表的时候指定的。抽样所选的桶由桶大小，y和x共同决定。如果y和桶大小相等，则抽样所选的桶是x对y的求模结果。
TABLESAMPLE(BUCKET 3 OUT OF 16)
这将抽样第3个和第19个桶。桶的编号从0开始。
tablesample语句的另一方面：
TABLESAMPLE(BUCKET 3 OUT OF 64 ON userid)
这将抽取第3个桶的一半。
union all
这个语言也支持union all，如果假设我们有两个不同的表，分别用来记录用户发布的视频和用户发布的评论，以下例子是一个union all 的结果与用户表再连接的查询：
INSERT OVERWRITE TABLE actions_users
SELECT u.id, actions.date
FROM (
SELECT av.uid AS uid
FROM action_video av
WHERE av.date = '2008-06-03'
UNION ALL
SELECT ac.uid AS uid
FROM action_comment ac
WHERE ac.date = '2008-06-03'
) actions JOIN users u ON(u.id = actions.uid);
数组操作
表的数组列可以这样：
CREATE TABLE array_table (int_array_column ARRAY<INT>);
假设pv.friends 是类型ARRAY<INT>（也就是一个整型数组），用户可以通过索引号获取数组中特定的元素，如下：
SELECT pv.friends[2]
FROM page_views pv;
这个查询得到的是pv.friends里的第三个元素。
用户也可以使用函数size来获取数组的长度，如下：
SELECT pv.userid, size(pv.friends)
FROM page_view pv;
Map（关联数组）操作
Map提供了类似于关联数组的集合。这样的结构不仅可以由程序创建。我们也将很快可以继承这个。假设pv.properties是类型map<String,String>，如下：
INSERT OVERWRITE page_views_map
SELECT pv.userid, pv.properties['page type']
FROM page_views pv;
这将查询表page_views的‘page_type‘属性。
与数组相似，也可以使用函数size来获取map的大小：
SELECT size(pv.properties)
FROM page_view pv;
定制Map/Reduce脚本
通过使用Hive语言原生支持的特征，用户可以插入他们自己定制的mapper和reducer在数据流中。例如，要运行一个定制的mapper脚本script-map_script和reducer脚本script-reduce_script)，用户可以执行以下命令，使用TRANSFORM来嵌入mapper和reducer脚本。
注意：在执行用户脚本之前，表的列会转换成字符串，且由TAB分隔，用户脚本的标准输出将会被作为以TAB分隔的字符串列。用户脚本可以输出调试信息到标准错误输出，这个信息也将显示hadoop的详细任务页面上。
FROM (
FROM pv_users
MAP pv_users.userid, pv_users.date
USING 'map_script'
AS dt, uid
CLUSTER BY dt) map_output
INSERT OVERWRITE TABLE pv_users_reduced
REDUCE map_output.dt, map_output.uid
USING 'reduce_script'
AS date, count;
map脚本样本（weekday_mapper.py)
import sys
import datetime
for line in sys.stdin:
line = line.strip()
userid, unixtime = line.split('\t')
weekday = datetime.datetime.fromtimestamp(float(unixtime)).isoweekday()
print ','.join([userid, str(weekday)])
当然，对于那些常见的select转换，MAP和REDUCE都是“语法糖”。内部查询也可以写成这样：
SELECT TRANSFORM(pv_users.userid, pv_users.date) USING 'map_script' AS dt, uid CLUSTER BY dt FROM pv_users;
Co-Groups
在使用map/reduce的群体中，cogroup是相当常见的操作，它是将来自多个表的数据发送到一个定制的reducer，使得行由表的指定列的值进行分组。在Hive的查询语言中，可以使用以下方式，通过使用union all和cluster by来实现此功能。假设我们想对来自表actions_video和action_comment的行对uid列进行分组，且需要发送他们到reducer_script定制的reducer，可以使用以下语法：
FROM (
FROM (
FROM action_video av
SELECT av.uid AS uid, av.id AS id, av.date AS date
UNION ALL
FROM action_comment ac
SELECT ac.uid AS uid, ac.id AS id, ac.date AS date
) union_actions
SELECT union_actions.uid, union_actions.id, union_actions.date
CLUSTER BY union_actions.uid) map
INSERT OVERWRITE TABLE actions_reduced
SELECT TRANSFORM(map.uid, map.id, map.date) USING 'reduce_script' AS (uid, id, reduced_val);