Hive建表语句和分区表概念及示范

Hive的建表语句

CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name 
   [(col_name data_type [COMMENT col_comment], ...)] 
   [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] 
   [ROW FORMAT row_format] 
   [STORED AS file_format] 
   [LOCATION hdfs_path]

ROW FORMAT

Row Format语法如下：

DELIMITED [FIELDS TERMINATED BY char] [COLLECTION ITEMS TERMINATED BY char] 
        [MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char] 
   | SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, ...)]

有一个COLLECTION ITEMS TERMINATED BY 是指Hive中的字段可以是一个集合，比如Map、数组等。

STORED AS

SEQUENCEFILE|TEXTFILE|RCFILE

如果文件数据是纯文本，可以使用 STORED AS TEXTFILE。如果数据需要压缩，使用 STORED AS SEQUENCEFILE。

LOCATION

如果创建的是一个EXTERNAL表还可以指定它在HDFS上的存储路径，即LOCATION。

创建外部表示例

使用下面的语句来创建一个外部表：

hive> create external table t_ext(id int, name string)
    > row format delimited fields terminated by '\t'
    > stored as textfile
    > location '/hive_ext';

可以使用下面的命令来查看表的详细信息：

desc formated t_ext;

输出结果如下：

# col_name              data_type               comment             

id                      int                                         
name                    string                                      

# Detailed Table Information             
Database:               test_db                  
Owner:                  root                     
CreateTime:             Thu Mar 30 10:50:44 CST 2017     
LastAccessTime:         UNKNOWN                  
Protect Mode:           None                     
Retention:              0                        
Location:               hdfs://amaster:9000/hive_ext     
Table Type:             EXTERNAL_TABLE           
Table Parameters:                
        EXTERNAL                TRUE                
        transient_lastDdlTime   1490842244          

# 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:             
        field.delim             \t                  
        serialization.format    \t                  
Time taken: 0.087 seconds, Fetched: 29 row(s)

外部表与内部表的区别

如果drop外部表，只会删掉表的元数据，数据还在。如果drop内部表则会删除元数据和数据。

Hive数据存储

Hive的所有数据都存储在HDFS中，没有专门的数据存储格式（支持text、sequenceFile、ParquetFile、RCFile等）其中ParquetFile和RCFile有表头，通过读取表头可以知道表的结构。

Hive中上传数据

之前我们是直接将数据文件上传到了Hive表所在的数据目录，其实Hive还提供了一个Load命令供我们将数据进行上传。

语法结构

LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO 
TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)]

说明：

1. Load 操作只是单纯的复制/移动操作，将数据文件移动到 Hive 表对应的位置。
2. filepath：
   相对路径，例如：project/data1
   绝对路径，例如：/user/hive/project/data1
   包含模式的完整 URI，列如：
   hdfs://namenode:9000/user/hive/project/data1
3. LOCAL关键字
   如果指定了 LOCAL， load 命令会去查找本地文件系统中的 filepath。
   如果没有指定 LOCAL 关键字，则根据inpath中的uri查找文件
4. OVERWRITE 关键字
   如果使用了 OVERWRITE 关键字，则目标表（或者分区）中的内容会被删除，然后再将 filepath 指向的文件/目录中的内容添加到表/分区中。
   如果目标表（分区）已经有一个文件，并且文件名和 filepath 中的文件名冲突，那么现有的文件会被新文件所替代。

准备一个names.data文件。

1   zhangsan
2   李四
3   杨凌

使用下面的语句进行导入：

load data local inpath '/root/names.data' into table t_ext;

Hive分区表

使用下面的命令来创建一个带分区的表。

create table t_part(id int,name string)
    > partitioned by (country string)
    > row format delimited
    > fields terminated by ',';

partitioned by里面不能加create table时指定的字段。

此时只能说指定了这个表会分区，但是具体数据有哪些分区则会在导入数据时产生。

使用下面的命令来指定具体导入到哪个分区：

load data local inpath '/root/names.data' into table t_part partition(country='China');
load data local inpath '/root/names.data.jp' into table t_part partition(country='Japan');

此时分区已经变成一个伪字段了。如果要分区查询，可以使用Where或者Group by来进行限定。