HiveSQL学习

Hive 是基于Hadoop构建的一套数据仓库分析系统，他以SQL查询的方式来分析存储在Hadoop分布式文件系统中的数据，将结构化的数据文件映射成一张表，并且提供了完整的SQL查询功能，可以将SQL语句转化成为MapReduce任务运行。这一套SQL就是HiveSQL。对于不熟悉MapReduce的人来说，使用类似SQL的语句就可以很方便地通过查询语句来分析数据。而MapReduce的开发人员，也可以把自己编写的mapper和reducer作为插件支持hive进行更加复杂的数据分析工作。

HiveSQL与关系数据库中的SQL略有不同：HiveSQL支持绝大多数的DDL，DML以及连接查询、条件查询和聚合函数。但是HIVE不支持OLTP，也不支持实时查询。

一、 DDL操作(Data Definition Language)

DDL

•建表

•删除表

•修改表结构

•创建／删除视图

•创建数据库

•显示命令

1. 创建表

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]

•CREATE TABLE 创建一个指定名字的表。如果相同名字的表已经存在，则抛出异常；用户可以用 IF NOT EXIST 选项来忽略这个异常

•EXTERNAL 关键字可以让用户创建一个外部表，在建表的同时指定一个指向实际数据的路径（LOCATION）

•LIKE 允许用户复制现有的表结构，但是不复制数据

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

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

• STORED AS

            SEQUENCEFILE

            | TEXTFILE

            | RCFILE    

            | INPUTFORMAT input_format_classname OUTPUTFORMAT             output_format_classname

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

1）. External： 外部表和内部表

Hive的数据表分为两种： 外部表和内部表 。内部表是Hive创建并通过load data inpath加载到数据库中的表。这种表可以理解为数据和表结构都保存在一起的表。 当你通过DROP TABLE table_name 删除元数据中表结构的同时，表中的数据也同样会从hdfs中被删除。

外部表指在表结构创建以前，数据已经保存在hdfs中了，通过创建表结构，将数据格式化到表的结构里。当DROP TABLE table_name 的时候，hive仅仅会删除元数据的表结构，而不会删除hdfs上的文件，所以，相比内部表，外部表可以更放心大胆的使用。

内部表的创建：

CREATE TABLE database.table1 
(
column1 STRING COMMENT 'comment1',
column2 INT COMMENT 'comment2'
);

外部表创建，下面是hdfs中文件不用LZO压缩，纯文本保存时，如何创建外部表：

CREATE EXTERNAL TABLE IF NOT EXISTS database.table1 
(
column1 STRING COMMENT 'comment1',
column2 STRING COMMENT 'comment2'
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t"
LOCATION 'hdfs:///data/dw/asf/20120201';

下面是，当hdfs中的文件用LZO压缩后，如何创建外部表，当然你需要hadoop-gpl的支持才能以文本形式读取lzo文件。

CREATE EXTERNAL TABLE IF NOT EXISTS database.table1
(
column1 STRING COMMENT 'comment1',
column2 STRING COMMENT 'comment2'
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t"
STORED AS INPUTFORMAT "com.hadoop.mapred.DeprecatedLzoTextInputFormat" OUTPUTFORMAT "org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat"
LOCATION 'hdfs:///data/dw/asf/20120201';

2）创建分区表：

在Hive Select查询中一般会扫描整个表内容，会消耗很多时间做没必要的工作。有时候只需要扫描表中关心的一部分数据，因此建表时引入了partition概念。分区表指的是在创建表时指定的partition的分区空间。如果需要创建有分区的表，需要在create表的时候调用可选参数partitioned by，详见表创建的语法结构。

技术细节：

a、一个表可以拥有一个或者多个分区，每个分区以文件夹的形式单独存在表文件夹的目录下。

b、表和列名不区分大小写。

c、 分区是以字段的形式在表结构中存在，通过describe table命令可以查看到字段存在，但是该字段不存放实际的数据内容，仅仅是分区的表示。

d、 分区建表分为2种，一种是单分区，也就是说在表文件夹目录下只有一级文件夹目录。另外一种是多分区，表文件夹下出现多文件夹嵌套模式。

单分区表：

create table day_table (id int, content string) partitioned by (dt string);

多分区表：

create table day_hour_table (id int, content string) partitioned by (dt string, hour string);

双分区表，按天和小时分区，在表结构中新增加了dt和hour两列。表文件夹目录示意图（多分区表）：

e、 添加分区表语法（表已创建，在此基础上添加分区）

ALTER TABLE table_name ADD
  partition_spec [ LOCATION 'location1' ]
  partition_spec [ LOCATION 'location2' ] ...

partition_spec:
  : PARTITION (partition_col = partition_col_value,
       partition_col = partiton_col_value, ...)

ALTER TABLE day_table ADD
    PARTITION (dt='2008-08-08', hour='08')
      location '/path/pv1.txt'
    PARTITION (dt='2008-08-08', hour='09')
      location '/path/pv2.txt';

f、删除分区：

ALTER TABLE table_name DROP
    partition_spec, partition_spec,...

用户可以用 ALTER TABLE DROP PARTITION 来删除分区。分区的元数据和数据将被一并删除。例：

ALTER TABLE day_hour_table
    DROP PARTITION (dt=