[Hadoop]Hive r0.9.0中文文档（一）之数据操作语句

文章为原创，转载请标注文章出处，否则保留对版权的追求权力。

一、创建表的语法

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]
  [TBLPROPERTIES (property_name=property_value, ...)]  
  [AS select_statement]  

CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name
  LIKE existing_table_name
  [LOCATION hdfs_path]

data_type
  : primitive_type
  | array_type
  | map_type
  | struct_type

primitive_type
  : TINYINT
  | SMALLINT
  | INT
  | BIGINT
  | BOOLEAN
  | FLOAT
  | DOUBLE
  | STRING

array_type
  : ARRAY < data_type >

map_type
  : MAP < primitive_type, data_type >

struct_type
  : STRUCT < col_name : data_type [COMMENT col_comment], ...>

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, ...)]

file_format:
  : SEQUENCEFILE
  | TEXTFILE
  | INPUTFORMAT input_format_classname OUTPUTFORMAT output_format_classname

用已知表名创建一个表的时候，如果同名表已经存在则会报一个表已存在的异常，不过你可以使用IF NOT EXISTS来防止这个错误。

EXTERNAL 关键字可以让用户创建一个外部表，在建表的同时指定一个指向实际数据的路径（LOCATION），Hive 创建内部表时，会将数据移动到数据仓库指向的路径；若创建外部表，仅记录数据所在的路径，不对数据的位置做任何改变。在删除表的时候，内部表的元数据和数 据会被一起删除，而外部表只删除元数据，不删除数据

用户在建表的时候可以自定义 SerDe 或者使用自带的 SerDe。如果没有指定 ROW FORMAT 或者 ROW FORMAT DELIMITED，将会使用自带的 SerDe。在建表的时候，用户还需要为表指定列，用户在指定表的列的同时也会指定自定义的 SerDe，Hive 通过 SerDe 确定表的具体的列的数据。

如果文件数据是纯文本，可以使用 STORED AS TEXTFILE。如果数据需要压缩，使用 STORED AS SEQUENCE 。使用INPUTFROMAT 和OUTPUTFORMAT将指定输入输出的格式，例如：

'org.apache.hadoop.hive.contrib.fileformat.base64.Base64TextInputFormat'.

代表指定了表的输入格式必须的是Base64.

有分区的表可以在创建的时候使用 PARTITIONED BY 语句。一个表可以拥有一个或者多个分区，每一个分区单独存在一个目录下。而且，表和分区都可以对某个列进行 CLUSTERED BY 操作，将若干个列放入一个桶（bucket）中。也可以利用SORT BY 对数据进行排序。这样可以为特定应用提高性能。

表名和列名不区分大小写，SerDe 和属性名区分大小写。表和列的注释是字符串。

下面是创建一个表的例子:

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;

创建一个page_view表，按照dt和country进行分割并有序排列。

二、使用select来创建表（简称CTAS）
表可以按照查询结果来进行创建，按照CTAS查询的来的表是自动的，这意味着这个表不会被其他人看见值到查询结果结束，其他人只能看见查询的来的完成结果，而不是看到这个表的全部。

CTAS语句分为2个部分，HiveQL支持SELECT部分，创建部分将会使用SELECT查询的结果，并且可以使用其他表的属性例如SerDe和存储格式来创建这个表。CTAS创建表的唯一限制是不能够创建一个分区表，也不能创建一个EXTERNAL的表。

三、使用 SerDes
SerDe 是 Serialize/Deserilize 的简称，目的是用于序列化和反序列化。序列化的格式包括：
• 分隔符（tab、逗号、CTRL-A）
• Thrift 协议
反序列化（内存内）：
• Java Integer/String/ArrayList/HashMap
• Hadoop Writable 类
• 用户自定义类


CTAS创建如果没有指定列名那么查询得到的列名将会被自动分配为 _col0, _col1, and _col2等这样的列名。另外，新的目标表将会使用特殊的SerDe进行创建，并且存储格式与查询语句相独立。

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;

把一个表中的数据查询出来得到另外一个表示Hive一个重要的Feature，在这个查询过程中，你可以让Hive处理将原数据格式转化为另外的一种格式。

四、按BUCKET（桶）存储的表

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 '\001'
   COLLECTION ITEMS TERMINATED BY '\002'
   MAP KEYS TERMINATED BY '\003'
 STORED AS SEQUENCEFILE;

上面的例子中，page_view 表按照userid进行分桶，共32个桶，数据按照viewTime进行排序。
意义在于，这样可以对数据集群按照userid做一个有效的抽样结果。排序属性可以让开发人员更好的利用已知的数据结构进行查询，当某一个列是lists或者maps类型时，还可以使用MAP KEYS 和 COLLECTION TIEMS关键字。

CLUSTERED BY 和 SORTERD BY不影响数据插入方式，只影响读取顺序。这意味着用户必须小心使用这2个命令，看看桶类型表是怎么工作的吧。

五、外部表
External Tables

External Table 指向已经在 HDFS 中存在的数据，可以创建 Partition。它和 Table 在元数据的组织上是相同的，而实际数据的存储则有较大的差异

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 '\054'
 STORED AS TEXTFILE
 LOCATION '<hdfs_location>';
 

六、使用Like来创建表
like仅复制表结构，但是不复制数据

CREATE TABLE empty_key_value_store
LIKE key_value_store;

七、drop
删除一个内部表的同时会同时删除表的元数据和数据。删除一个外部表，只删除元数据而保留数据。