Hive数据定义操作

数据库操作

Hive中的数据按照如下粒度从粗到细、从顶层到底层的方式组织：

数据库（database）：命名空间，为避免表、视图、分区和列等存在命名冲突，同时可以用作权限控制的单位；
数据表（table）：结构相同数据的存储单元。例如，对于用户网页浏览数据表，每一行数据可以包含以下列：
网页浏览的UNIX时间戳，为整数型；
用户标识符，为整数型；
网页URL地址，为字符串；
来源，即浏览的前一个网页URL地址，为字符串；
用户IP地址，为字符串；
分区（partition）：每张表可以有一个或多个分区键，用于决定数据的存储方式。分区还能更高效的根据条件做行的筛选。例如，用户网页浏览数据表以国家和日期作为分区键，各分区键不同值的组合唯一确定一个分区，所有用户国家为“中国”、日期为“2018-12-23”的数据组成了该表的一个分区。因此，如果仅需要在2018年12月23日的中国用户数据上做查询，则可以直接定位到该分区，大大加快查询效率；
桶（bucket）或簇（cluster）：分区中的数据可以根据某些列的哈希值进一步分成桶，可以用于高效的做数据抽样。例如，用户网页浏览数据表可以按用户标识符分桶。
需要注意的是，数据表并不一定要做分区或分桶，但是分区和分桶可以在数据查询时快速定位到感兴趣的数据，提高执行效率。

本章所有例子基于一个数据集。该数据集是2014年前10个月由纽约出发航班的准点情况数据，由美国运输局收集整理，包含253，316个样本，每个样本包含17个属性，如下表所示

属性 定义
year 年份
month 月份
day 日期
dep_time 出发时间
dep_delay 出发延误分钟数
arr_time 到达时间
arr_delay 到达延误分钟数
carrier 航空公司代码
tailnum 尾翼编号
flight 航班编号
origin 出发地
dest 目的地
air_time 飞行分钟数
distance 距离
hour 小时
min 分钟
该数据集的路径为/opt/data/flights/flights14.csv，其中第一行为列名。同时也已经存储在Hive的表flights.flights14中。

创建数据库

使用beeline -u <JDBC连接URL> -n <登陆用户名> -p <登陆密码>命令启动Beeline。

beeline -u ‘jdbc:hive2://localhost:10000’ -n root -p 123456
SLF4J: Class path contains multiple SLF4J bindings.
SLF4J: Found binding in [jar:file:/usr/lib/hive/lib/log4j-slf4j-impl-2.8.2.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: Found binding in [jar:file:/usr/lib/zookeeper/lib/slf4j-log4j12-1.7.25.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.
SLF4J: Actual binding is of type [org.apache.logging.slf4j.Log4jLoggerFactory]
Connecting to jdbc:hive2://localhost:10000
Connected to: Apache Hive (version 2.1.1-cdh6.1.0)
Driver: Hive JDBC (version 2.1.1-cdh6.1.0)
Transaction isolation: TRANSACTION_REPEATABLE_READ
Beeline version 2.1.1-cdh6.1.0 by Apache Hive
0: jdbc:hive2://localhost:10000>

使用CREATE DATABASE [IF NOT EXISTS] <数据库名>语句创建数据库test1，其中

IF NOT EXISTS关键词表示仅在该数据库不存在时才创建。

CREATE DATABASE IF NOT EXISTS test1;

使用LOCATION语句指定数据库在HDFS上的对应路径，默认为/user/hive/warehouse/<数据库名>.db，该默认路径可以在hive-site.xml配置文件中修改。这里创建数据库test2，指定对应HDFS上的/user/hive/test2文件夹。

CREATE DATABASE IF NOT EXISTS test2
LOCATION ‘/user/root/test2’;

描述数据库

使用DESCRIBE DATABASE <数据库名>语句描述数据库test1，显示的信息包括数据库名称（db_name）、注释（comment）、存储位置（location）、所有者（owner_name）等。

DESCRIBE DATABASE test1;
±---------±---------±---------------------------------------------------±------------±------------±------------±-+
| db_name | comment | location | owner_name | owner_type | parameters |
±---------±---------±---------------------------------------------------±------------±------------±------------±-+
| test1 | | hdfs://localhost:8020/user/hive/warehouse/test1.db | root | USER | |
±---------±---------±---------------------------------------------------±------------±------------±------------±-+

列出数据库

使用SHOW DATABASES [LIKE ‘<正则表达式>’]语句列出所有（满足正则表达式的）数据库。正则表达式中，星号*指代一个或多个字母，竖线|表示或。其中，default数据库是Hive默认的数据库。

SHOW DATABASES;
±---------------±-+
| database_name |
±---------------±-+
| default |
| flights |
| test1 |
| test2 |
±---------------±-+

SHOW DATABASES LIKE ‘test*’;
±---------------±-+
| database_name |
±---------------±-+
| test1 |
| test2 |
±---------------±-+

当前数据库

使用USE <数据库名>设置当前数据库，这样在指定数据表名时，不需要再以<数据库名>.<表名>的形式，而可以直接使用表名。

USE test1;

使用SELECT current_database()查看当前数据库。

SELECT current_database();
±-------±-+
| _c0 |
±-------±-+
| test1 |
±-------±-+

删除数据库

使用DROP DATABASE [IF EXISTS] <数据库名> [CASCADE]语句删除数据库test2，其中

IF EXISTS关键词表示仅在该数据库存在时才删除；
CASCADE关键词表示删除数据库的同时也删除数据库中的所有表，如果没有该关键词且数据库中还有表，则会删除失败。

DROP DATABASE IF EXISTS test1 CASCADE;
SHOW DATABASES;
±---------------±-+
| database_name |
±---------------±-+
| default |
| flights |
| test2 |
±---------------±-+

数据表基本操作

创建数据表

创建数据表的语句功能强大，形式多样，基本语法和常用选项如下：

CREATE [EXTERNAL] TABLE [IF NOT EXISTS] [<数据库名>.]<表名>
[(<列名1> <数据类型1>, <列名2> <数据类型2>, …)]
[PARTITIONED BY (<分区列名1> <数据类型1>, <分区列名2> <数据类型2>, …)]
[ROW FORMAT <行格式>]
[STORED AS <文件存储格式>]
[LOCATION <HDFS存储路径>]
[AS <查询语句>];

这些关键词和语句都会在后续章节详细介绍。

使用CREATE TABLE [IF NOT EXISTS] <表名>语句创建数据表flights用于存储航班数据集，其中

IF NOT EXISTS关键词表示仅在该数据表不存在时才创建；
列carrier、tailnum、origin和dest为字符串类型（string），其他列都是整数类型（int），数据类型会在后续章节详细介绍。

USE default;
CREATE TABLE IF NOT EXISTS flights
(year int, month int, day int, dep_time int ,dep_delay int,
arr_time int, arr_delay int, cancelled int, carrier string,
tailnum string, flight int, origin string, dest string,
air_time int, distance int, hour int, min int);

描述数据表

使用DESCRIBE [FORMATTED] <表名>语句描述数据表flights。

DESCRIBE flights;
±-----------±-----------±---------±-+
| col_name | data_type | comment |
±-----------±-----------±---------±-+
| year | int | |
| month | int | |
| day | int | |
| dep_time | int | |
| dep_delay | int | |
| arr_time | int | |
| arr_delay | int | |
| cancelled | int | |
| carrier | string | |
| tailnum | string | |
| flight | int | |
| origin | string | |
| dest | string | |
| air_time | int | |
| distance | int | |
| hour | int | |
| min | int | |
±-----------±-----------±---------±-+

使用FORMATTED关键词以表格形式显示数据表的额外信息。

DESCRIBE FORMATTED flights;
±------------------------------±---------------------------------------------------±----------------------±-+
| col_name | data_type | comment |
±------------------------------±---------------------------------------------------±----------------------±-+
| # col_name | data_type | comment |
| | NULL | NULL |
| year | int | |
| month | int | |
| day | int | |
| dep_time | int | |
| dep_delay | int | |
| arr_time | int | |
| arr_delay | int | |
| cancelled | int | |
| carrier | string | |
| tailnum | string | |
| flight | int | |
| origin | string | |
| dest | string | |
| air_time | int | |
| distance | int | |
| hour | int | |
| min | int | |
| | NULL | NULL |
| # Detailed Table Information | NULL | NULL |
| Database: | default | NULL |
| Owner: | root | NULL |
| CreateTime: | Tue Oct 02 08:34:32 UTC 2018 | NULL |
| LastAccessTime: | UNKNOWN | NULL |
| Protect Mode: | None | NULL |
| Retention: | 0 | NULL |
| Location: | hdfs://localhost:8020/user/hive/warehouse/flights | NULL |
| Table Type: | MANAGED_TABLE | NULL |
| Table Parameters: | NULL | NULL |
| | transient_lastDdlTime | 1538469272 |
| | NULL | NULL |
| # Storage Information | NULL | NULL |
| SerDe Library: | org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe | NULL |
| InputFormat: | org.apache.hadoop.mapred.TextInputFormat | NULL |
| OutputFormat: | org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat | NULL |
| Compressed: | No | NULL |
| Num Buckets: | -1 | NULL |
| Bucket Columns: | [] | NULL |
| Sort Columns: | [] | NULL |
| Storage Desc Params: | NULL | NULL |
| | serialization.format | 1 |
±------------------------------±---------------------------------------------------±----------------------±-+

列出数据表

使用SHOW TABLES [LIKE ‘<正则表达式>’]语句列出所有（满足正则表达式的）数据表。正则表达式中，星号*指代一个或多个字母，竖线|表示或。

SHOW TABLES;
±----------±-+
| tab_name |
±----------±-+
| flights |
±----------±-+
SHOW TABLES LIKE ‘f*’;
±----------±-+
| tab_name |
±----------±-+
| flights |
±----------±-+

删除数据表

使用DROP TABLE [IF EXISTS] <表名>语句删除表flights，其中

IF EXISTS关键词表示仅在该表存在时才删除。
删除表时会同时删除表的元数据和数据。事实上，数据会移动到HDFS上的.Trash/Current文件夹中。

DROP TABLE IF EXISTS flights;

从查询结果创建数据表

使用CREATE TABLE [IF NOT EXISTS] <表名> AS <查询语句>语句从查询结果创建数据表flights，查询结果为简单选取flights.flights14表中的所有数据。

CREATE TABLE IF NOT EXISTS flights_top5 AS
SELECT * FROM flights.flights14 limit 5;

拷贝表结构

使用CREATE TABLE [IF NOT EXISTS] <新表名> LIKE <原表名>语句拷贝flights.flights14表结构创建数据表flights，并不拷贝数据。

CREATE TABLE IF NOT EXISTS flights LIKE flights.flights14;

存储格式和行格式

创建表中STORED AS语句用于指定表中数据的存储格式，常用的选项有TEXTFILE、SEQUENCEFILE、ORC和PARQUET等。 其中，TEXTFILE表示文本文件，也是表中数据的默认存储格式，以可读的明文存储数据，但所占空间较大，性能较差。其他存储格式都是二进制格式，进行了压缩和性能优化。

文本存储格式

在创建表flights时，使用STORED AS TEXFILE语句表示存储为文本文件，该语句也可以省略，因为文本文件是默认存储格式。

DROP TABLE IF EXISTS flights_top5;
CREATE TABLE IF NOT EXISTS flights_top5
STORED AS TEXTFILE AS
SELECT * FROM flights.flights14 limit 5;

文本文件都是可读的明文，使用dfs -cat命令显示文件内容。

dfs -cat /user/hive/warehouse/flights_top5/000000_0;
±---------------------------------------------------±-+
| DFS Output |
±---------------------------------------------------±-+
| 20141113472170610AAN319AA117JFKLAX35024751347 |
| 201411722-81014-260AAN3EHAA29LGAPBI1571035722 |
| 20141119022222490AAN327AA21JFKLAX3512475192 |
| 2014111157-31523130AAN335AA3JFKLAX36324751157 |
| 201411914141238130AAN338AA1JFKLAX3592475914 |
±---------------------------------------------------±-+

可以看出，每一列的值看似直接连接在了一起。事实上，每一行的格式是以不可见字符’\001’分隔的列。

行格式

对于文本存储格式，行的默认格式是以指定字符分隔的列。

使用ROW FORMAT语句设置行格式，这里

DELIMITED关键词表示行格式是以指定字符分隔的列；
FIELDS TERMINATED BY语句表示分隔符，这里设为逗号,。

DROP TABLE IF EXISTS flights_top5;
CREATE TABLE IF NOT EXISTS flights_top5
ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘,’
STORED AS TEXTFILE
AS
SELECT * FROM flights.flights14 limit 5;

使用dfs -cat命令显示文件内容。

dfs -cat /user/hive/warehouse/flights_top5/000000_0;
±---------------------------------------------------±-+
| DFS Output |
±---------------------------------------------------±-+
| 2014,1,1,1347,2,1706,1,0,AA,N319AA,117,JFK,LAX,350,2475,13,47 |
| 2014,1,1,722,-8,1014,-26,0,AA,N3EHAA,29,LGA,PBI,157,1035,7,22 |
| 2014,1,1,1902,2,2224,9,0,AA,N327AA,21,JFK,LAX,351,2475,19,2 |
| 2014,1,1,1157,-3,1523,13,0,AA,N335AA,3,JFK,LAX,363,2475,11,57 |
| 2014,1,1,914,14,1238,13,0,AA,N338AA,1,JFK,LAX,359,2475,9,14 |
±---------------------------------------------------±-+

可以看出，每一行的格式是以逗号,分隔的列。

分隔符为逗号,的文本文件也称为CSV（comma-seperated values）文件，分隔符为制表符的文件也称为TSV（tab-seperated values）文件。

Hive还提供多个功能强大的序列化和反序列化器用于指定行格式，具体语法为

ROW FORMAT SERDE ‘<序列化和反序列化器类名>’
[WITH SERDEPROPERTIES (
“<属性名1>” = “<属性值1>”,
“<属性名2>” = “<属性值2>”,
…)]

使用org.apache.hadoop.hive.serde2.OpenCSVSerde序列化和反序列化器，设置行格式为以指定字符分隔的列，达到使用DELIMITED关键词相同的效果，其中可以进一步设置以下表属性：

separatorChar表示列分隔符，默认为逗号,；
quoteChar表示引号字符，默认为双引号"；
escapeChar表示转义字符，默认为反斜杠\。
以下例子将列分隔符设为制表符，引号字符设为单引号’。

DROP TABLE IF EXISTS flights_top5;
CREATE TABLE IF NOT EXISTS flights_top5
ROW FORMAT SERDE ‘org.apache.hadoop.hive.serde2.OpenCSVSerde’
WITH SERDEPROPERTIES (
“separatorChar” = “\t”,
“quoteChar” = “'”
)
STORED AS TEXTFILE
AS
SELECT * FROM flights.flights14 limit 5;

使用dfs -cat命令显示文件内容。

dfs -cat /user/hive/warehouse/flights_top5/000000_0;
±---------------------------------------------------±-+
| DFS Output |
±---------------------------------------------------±-+
| ‘2014’ ‘1’ ‘1’ ‘1347’ ‘2’ ‘1706’ ‘1’ ‘0’ ‘AA’ ‘N319AA’ ‘117’ ‘JFK’ ‘LAX’ ‘350’ ‘2475’ ‘13’ ‘47’ |
| ‘2014’ ‘1’ ‘1’ ‘722’ ‘-8’ ‘1014’ ‘-26’ ‘0’ ‘AA’ ‘N3EHAA’ ‘29’ ‘LGA’ ‘PBI’ ‘157’ ‘1035’ ‘7’ ‘22’ |
| ‘2014’ ‘1’ ‘1’ ‘1902’ ‘2’ ‘2224’ ‘9’ ‘0’ ‘AA’ ‘N327AA’ ‘21’ ‘JFK’ ‘LAX’ ‘351’ ‘2475’ ‘19’ ‘2’ |
| ‘2014’ ‘1’ ‘1’ ‘1157’ ‘-3’ ‘1523’ ‘13’ ‘0’ ‘AA’ ‘N335AA’ ‘3’ ‘JFK’ ‘LAX’ ‘363’ ‘2475’ ‘11’ ‘57’ |
| ‘2014’ ‘1’ ‘1’ ‘914’ ‘14’ ‘1238’ ‘13’ ‘0’ ‘AA’ ‘N338AA’ ‘1’ ‘JFK’ ‘LAX’ ‘359’ ‘2475’ ‘9’ ‘14’ |
±---------------------------------------------------±-+

可以看出，每一行的格式是以制表符分隔且包含在单引号’内的列。

二进制存储格式

除了文本文件以外的存储格式都称为二进制存储格式，包括SEQUENCEFILE、ORC和PARQUET等。

使用STORED AS SEQUENCEFILE语句表示存储为压缩的序列文件。

DROP TABLE IF EXISTS flights_top5;
CREATE TABLE IF NOT EXISTS flights_top5
STORED AS SEQUENCEFILE
AS
SELECT * FROM flights.flights14 limit 5;

使用dfs -cat命令显示文件内容。

dfs -cat /user/hive/warehouse/flights_top5/000000_0;
dfs -cat /user/hive/warehouse/flights_top5/000000_0;
±---------------------------------------------------±-+
| DFS Output |
±---------------------------------------------------±-+
| SEQ"org.apache.hadoop.io.BytesWritableorg.apache.hadoop.io.Text�b
����G-%"6Rf�B=20141113472170610AAN319AA117JFKLAX35024751347B=201411722-81014-260AAN3EHAA29LGAPBI1571035722@;20141119022222490AAN327AA21JFKLAX3512475192B=2014111157-31523130AAN335AA3JFKLAX36324751157@;201411914141238130AAN338AA1JFKLAX3592475914 |
±---------------------------------------------------±-+

可以看出，文件内容不可读。

数据类型

Hive中常用的数据类型主要有以下几种：

数值类型
整数（INT）：占用4个字节，范围从-2,147,483,648到2,147,483,647；
浮点数（DOUBLE）：占用8个字节；
日期时间类型
时间戳（TIMESTAMP）；
日期（DATE）；
字符串类型
字符串（STRING）；
其他类型
布尔（BOOLEAN）；
复杂类型
数组（ARRAY<<数据类型>>）；
字典（MAP<<键的数据类型>, <值的数据类型>>）；
结构体（STRUCT<<列名> : <数据类型>, …>）。
函数cast(<列名或数值> as <新数据类型>)用于类型转换。

字符串

输入字符串时，可以使用单引号’或双引号"。

SELECT ‘single’, “double”;
±--------±--------±-+
| _c0 | _c1 |
±--------±--------±-+
| single | double |
±--------±--------±-+

如果输入的字符串中包含引号，则需要通过反斜杠\转义。

SELECT ‘‘single’’, ““double””;
±----------±----------±-+
| _c0 | _c1 |
±----------±----------±-+
| ‘single’ | “double” |
±----------±----------±-+

日期时间

时间戳（TIMESTAMP）
时间戳（TIMESTAMP）的形式为<年（4位）>-<月（2位）>-<日（2位）> <小时（2位）>:<分钟（2位）>:<秒（2位）>。

输入时间戳时，需要在前面加上timestamp关键词。

SELECT timestamp’2019-01-01 19:18:18’;
±-----------------------±-+
| _c0 |
±-----------------------±-+
| 2019-01-01 19:18:18.0 |
±-----------------------±-+

时间戳支持从UNIX时间戳的数值型转换得到。

SELECT cast(1237532400000 as timestamp);
±-----------------------±-+
| _c0 |
±-----------------------±-+
| 2009-03-20 03:00:00.0 |
±-----------------------±-+

日期（DATE）

日期（DATE）的形式为<年（4位）>-<月（2位）>-<日（2位）>，而不包含时间部分。

输入日期时，需要在前面加上date关键词。

select date’2019-01-01’;
±------------±-+
| _c0 |
±------------±-+
| 2019-01-01 |
±------------±-+

复杂类型

复杂类型主要包括：

数组（ARRAY<<数据类型>>）由不定个数且类型相同的数据元素组成；
字典（MAP<<键的数据类型>, <值的数据类型>>）由不定个数且类型相同的键值对组成；
结构体（STRUCT<<列名1> : <数据类型1>, <列名2> : <数据类型2>, …>）由个数相同且类型可以不同的数据元素组成。
创建表complex包含3列，分别为

第1列为由字符串组成的数组；
第2列为由键为整数、值为字符串组成的字典；
第3列为由3个子列组成的结构体，分别为整数a、浮点数b和字符串c组成。
对于包含复杂数据类型且行格式是以指定字符分隔的形式的表，还需要进一步在建表语句中指定复杂数据类型中元素的分隔符以及字典中键值对的分隔符。以下例子中，

使用COLLECTION ITEMS TERMINATED BY语句将复杂数据类型中元素的分隔符设为竖线|；
使用MAP KEYS TERMINATED BY语句将字典中键值对的分隔符设为冒号:。

CREATE TABLE IF NOT EXISTS complex (
array_col array,
map_col map<int, string>,
struct_col struct<a : int, b : double, c : string>)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘,’
COLLECTION ITEMS TERMINATED BY ‘|’
MAP KEYS TERMINATED BY ‘:’;
DESCRIBE complex;
±------------±---------------------------------±---------±-+
| col_name | data_type | comment |
±------------±---------------------------------±---------±-+
| array_col | array | |
| map_col | map<int,string> | |
| struct_col | struct<a:int,b:double,c:string> | |
±------------±---------------------------------±---------±-+

复杂类型不支持直接输入，这里创建一个文本文件，并将其导入新创建的表，导入的语法会在后续章节具体介绍。

新启动一个终端，使用echo命令写入若干行测试数据到文件complex.csv。

echo “1a|1b|1c|1d,101:1a|102:1b,1|1.0|1a
2a|2b,201:2a|202:2b|203:2c,2|2.0|2a
3a,301:3a,3|3.0|3a” > /opt/complex.csv

回到beeline所在的终端，使用LOAD DATA语句导入该文件。

LOAD DATA LOCAL INPATH ‘/opt/complex.csv’
OVERWRITE INTO TABLE complex;
SELECT * FROM complex;
±-----------------------±------------------------------±--------------------------±-+
| complex.array_col | complex.map_col | complex.struct_col |
±-----------------------±------------------------------±--------------------------±-+
| [“1a”,“1b”,“1c”,“1d”] | {101:“1a”,102:“1b”} | {“a”:1,“b”:1.0,“c”:“1a”} |
| [“2a”,“2b”] | {201:“2a”,202:“2b”,203:“2c”} | {“a”:2,“b”:2.0,“c”:“2a”} |
| [“3a”] | {301:“3a”} | {“a”:3,“b”:3.0,“c”:“3a”} |
±-----------------------±------------------------------±--------------------------±-+

外部表

Hive默认创建的都是内部表，即表的数据文件、元数据和统计信息都由Hive直接管理。Hive的数据文件默认存储在HDFS的/user/hive/warehouse/<数据库名>.db/<表名>/文件夹，可以通过Hive配置文件中的hive.metastore.warehouse.dir配置项修改。如果在Hive中删除内部表，则与表关联的数据文件和元数据都会被删除，即Hive管理表的整个生命周期。

Hive的外部表仅管理表的元数据，而并不负责管理数据文件。如果在Hive中删除外部表，则仅会删除元数据，而不会删除数据文件。

使用EXTERNAL关键词创建外部表flights，并使用LOCATION语句指定表的数据文件路径为/user/root/flights14文件夹。

CREATE EXTERNAL TABLE IF NOT EXISTS flights
(year int, month int, day int, dep_time int ,dep_delay int,
arr_time int, arr_delay int, cancelled int, carrier string,
tailnum string, flight int, origin string, dest string,
air_time int, distance int, hour int, min int)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ‘,’
LOCATION ‘/user/root/flights14’;

此时由于表flights对应的数据文件路径并没有任何数据，因此无法选取出任何数据。

SELECT * FROM flights LIMIT 5;
±--------------±---------------±-------------±------------------±-------------------±------------------±-------------------±-------------------±-----------------±-----------------±----------------±----------------±--------------±------------------±------------------±--------------±-------------±-+
| flights.year | flights.month | flights.day | flights.dep_time | flights.dep_delay | flights.arr_time | flights.arr_delay | flights.cancelled | flights.carrier | flights.tailnum | flights.flight | flights.origin | flights.dest | flights.air_time | flights.distance | flights.hour | flights.min |
±--------------±---------------±-------------±------------------±-------------------±------------------±-------------------±-------------------±-----------------±-----------------±----------------±----------------±--------------±------------------±------------------±--------------±-------------±-+
±--------------±---------------±-------------±------------------±-------------------±------------------±-------------------±-------------------±-----------------±-----------------±----------------±----------------±--------------±------------------±------------------±--------------±-------------±-+

使用dfs -cp命令将数据文件复制到外部表flights对应的数据文件路径。

dfs -cp /user/hive/warehouse/flights.db/flights14/ /user/root/;

此时则可以查询到数据。

SELECT * FROM flights LIMIT 5;
±--------------±---------------±-------------±------------------±-------------------±------------------±-------------------±-------------------±-----------------±-----------------±----------------±----------------±--------------±------------------±------------------±--------------±-------------±-+
| flights.year | flights.month | flights.day | flights.dep_time | flights.dep_delay | flights.arr_time | flights.arr_delay | flights.cancelled | flights.carrier | flights.tailnum | flights.flight | flights.origin | flights.dest | flights.air_time | flights.distance | flights.hour | flights.min |
±--------------±---------------±-------------±------------------±-------------------±------------------±-------------------±-------------------±-----------------±-----------------±----------------±----------------±--------------±------------------±------------------±--------------±-------------±-+
| 2014 | 1 | 1 | 914 | 14 | 1238 | 13 | 0 | AA | N338AA | 1 | JFK | LAX | 359 | 2475 | 9 | 14 |
| 2014 | 1 | 1 | 1157 | -3 | 1523 | 13 | 0 | AA | N335AA | 3 | JFK | LAX | 363 | 2475 | 11 | 57 |
| 2014 | 1 | 1 | 1902 | 2 | 2224 | 9 | 0 | AA | N327AA | 21 | JFK | LAX | 351 | 2475 | 19 | 2 |
| 2014 | 1 | 1 | 722 | -8 | 1014 | -26 | 0 | AA | N3EHAA | 29 | LGA | PBI | 157 | 1035 | 7 | 22 |
| 2014 | 1 | 1 | 1347 | 2 | 1706 | 1 | 0 | AA | N319AA | 117 | JFK | LAX | 350 | 2475 | 13 | 47 |
±--------------±---------------±-------------±------------------±-------------------±------------------±-------------------±-------------------±-----------------±-----------------±----------------±----------------±--------------±------------------±------------------±--------------±-------------±-+

使用DROP TABLE语句删除外部表flights。

DROP TABLE flights;

使用dfs -ls查看原来的表flights对应的数据文件路径/user/root/flights14。

dfs -ls /user/root/flights14;
±---------------------------------------------------±-+
| DFS Output |
±---------------------------------------------------±-+
| Found 1 items |
| -rw-r–r-- 1 root hadoop 16150340 2018-10-03 02:43 /user/root/flights14/000000_0 |
±---------------------------------------------------±-+
可以看出，数据文件仍然存在，并没有因为外部表的删除而被删除。

分区表

分区表可以有一个或多个分区列，用于决定数据的存储方式。分区还能更高效的根据条件做行的筛选。

例如，分区表t有2个分区列k1和k2，其中

k1有2种不同取值：v11和v12；
k2有3种不同取值：v21、v22和v23。
则分区表对应的数据文件夹结构如下：

‘t’
–‘k1=v11’
----‘k2=v21’
----‘k2=v22’
----‘k2=v23’
–‘k1=v12’
----‘k2=v21’
----‘k2=v22’
----‘k2=v23’

当查询条件中包含分区列时，如仅需要查询k1=v11和k2=v23时，则可以直接定位到相应的数据文件夹t/k1=v11/k2=v23，因此更高效。

使用PARTITIONED BY (<分区列名1> <数据类型1>, <分区列名2> <数据类型2>, …)语句指定表flights的分区，这里的分区列为year和month。

DROP TABLE IF EXISTS flights;
CREATE TABLE IF NOT EXISTS flights
(day int, dep_time int ,dep_delay int,
arr_time int, arr_delay int, cancelled int, carrier string,
tailnum string, flight int, origin string, dest string,
air_time int, distance int, hour int, min int)
PARTITIONED BY (year int, month int);

使用DESCRIBE语句描述表flights。

DESCRIBE flights;
±-------------------------±----------------------±----------------------±-+
| col_name | data_type | comment |
±-------------------------±----------------------±----------------------±-+
| day | int | |
| dep_time | int | |
| dep_delay | int | |
| arr_time | int | |
| arr_delay | int | |
| cancelled | int | |
| carrier | string | |
| tailnum | string | |
| flight | int | |
| origin | string | |
| dest | string | |
| air_time | int | |
| distance | int | |
| hour | int | |
| min | int | |
| year | int | |
| month | int | |
| | NULL | NULL |
| # Partition Information | NULL | NULL |
| # col_name | data_type | comment |
| | NULL | NULL |
| year | int | |
| month | int | |
±-------------------------±----------------------±----------------------±-+

可以看出，最下面的Partition Information区域显示了表的分区列。

使用2条INSERT语句插入2014年1月和2月的数据到分区表flights中，插入时需要指定分区。插入数据的语法会在后续章节具体介绍。

INSERT OVERWRITE TABLE flights PARTITION (year=2014, month=1)
SELECT day, dep_time, dep_delay, arr_time, arr_delay, cancelled, carrier,
tailnum, flight, origin, dest, air_time, distance, hour, min
FROM flights.flights14
WHERE year=2014 and month=1;

INSERT OVERWRITE TABLE flights PARTITION (year=2014, month=2)
SELECT day, dep_time, dep_delay, arr_time, arr_delay, cancelled, carrier,
tailnum, flight, origin, dest, air_time, distance, hour, min
FROM flights.flights14
WHERE year=2014 and month=2;

使用dfs -ls查看分区表flights对应的数据文件路径/user/hive/warehouse/flights的具体结构。

dfs -ls /user/hive/warehouse/flights/*;
±---------------------------------------------------±-+
| DFS Output |
±---------------------------------------------------±-+
| Found 2 items |
| drwxrwxrwt - root hadoop 0 2018-10-03 05:59 /user/hive/warehouse/flights/year=2014/month=1 |
| drwxrwxrwt - root hadoop 0 2018-10-03 05:59 /user/hive/warehouse/flights/year=2014/month=2 |
±---------------------------------------------------±-+

使用SHOW PARTITIONS语句显示表flights的分区。

SHOW PARTITIONS flights;
±-------------------±-+
| partition |
±-------------------±-+
| year=2014/month=1 |
| year=2014/month=2 |
±-------------------±-+