Hive表操作及管理

转载请注明出处：http://blog.csdn.net/u012842205/article/details/72765667

Hive是基于Hadoop的一个数据仓库工具，可以将结构化的数据文件映射为一张数据库表，并提供简单的sql查询功能，可以将sql语句转换为MapReduce任务进行运行。 其优点是学习成本低，可以通过类SQL语句快速实现简单的MapReduce统计，不必开发专门的MapReduce应用，十分适合数据仓库的统计分析。但缺点也非常明显。因为Hive运算引擎来自MapReduce，MapReduce中间结果都存储在磁盘，IO导致速度很慢，这也是Hive最显著的缺点。

作为对元数据SQL化操作的引擎，Hive同样使用RDBMS的数据表对象管理数据。

一、Hive表

Hive中能存在两种表：

• 内部表（managed table），数据文件、统计文件、元数据都由Hive自己管理，换句话说，这个表数据存储在哪里我们不用关心，也不用提供，Hive默认存储在HDFS。Hive能管理原始数据的整个生命周期。Hive表删除后，数据也随之删除。

• 外部表（external table），数据文件存储在其他系统中，可能是HDFS，也可能是HBase、ASV等，HIve只保留映射关系，但Hive表删除后，数据不会丢失，仍然存在于其他系统中。
  

二、创建表

由于Hive支持标准的SQL（包括SQL:2003和SQL:2011标准的一些特性），创建表与后面提到的CURD都比较好理解。以下是一些简单的创建表语句例子：

简单的内部表

create table tb (id int, name String, age int);  

分区的内部表

create table ez_part_test (  
    id int,   
    col1 string,   
    col2 string) partitioned by   
  (type int) stored as orc  

外部表（HDFS）

create external table example_csv_tb (  
    id int,  
    col1 string,  
    col2 string) ROW FORMAT   
  delimited fields terminated by ','   
  lines terminated by '\n'  
  stored as textfile location "/ez/example"  

HiveSQL不仅仅定义字段及相应的类型，也提供定义字段属性、表存储格式等的语义。

1、外部表

例如，在HDFS上有个在 hdfs://myhost:8020/ez/example/lang.csv 的数据文件，内容如下：

1,java,20  
2,c,40  
3,Golang,8  
4,Perl,35  
5,PHP,20  

这时候需要一个Hive表，映射到这个文件，方便用SQL查询，这个表既是外部表。同理映射到HBase表也是外部表。于是我们使用如下语句：

create external table lang (  
    id int,  
    name string,  
    field1 int) ROW FORMAT  
  delimited   
    fields terminated by ','  
    lines terminated by '\n'  
  stored as textfile   
  location "/ez/example/lang.csv"  

文件第一列是数字，我们定义为id列，第二列定义为name，第三列随便指定个名字为field。列与列间用逗号隔开。一行记录结束用换行符 ‘\n’结尾。于是后面的ROW FORMAT row_format子句定义整个格式行为，如下：

row_format  
  : DELIMITED [FIELDS TERMINATED BY char [ESCAPED BY char]]  
    [COLLECTION ITEMS TERMINATED BY char]  
    [MAP KEYS TERMINATED BY char]  
    [LINES TERMINATED BY char]  
    [NULL DEFINED AS char]   -- (Note: Available in Hive 0.13 and later)  
  | SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, ...)]  

必须定义的两个属性之一，其一是DELIMITED，其二是SERDE。第一个用于描述文本文件，第二个用于描述二进制文件（序列化与反序列化）。我们只涉及到描述文本文件。于是描述字段间的分割符号为','，行结束符为'\n'。

stored as file_format声明存储的文件为什么格式。


file_format:  
  : SEQUENCEFILE  
  | TEXTFILE    -- (Default, depending on hive.default.fileformat configuration)  
  | RCFILE      -- (Note: Available in Hive 0.6.0 and later)  
  | ORC         -- (Note: Available in Hive 0.11.0 and later)  
  | PARQUET     -- (Note: Available in Hive 0.13.0 and later)  
  | AVRO        -- (Note: Available in Hive 0.14.0 and later)  
  | INPUTFORMAT input_format_classname OUTPUTFORMAT output_format_classname  

这里我们配置TEXTFILE（文本文件），也是默认的Hive外部表存储格式。

location hdfs_path子句意思很明显了，数据文件存储的位置，注：必须是HDFS文件路径。
至此一个最简单的外部表就创建完成了。可以直接使用如下语句查询试试：

select * from lang;  

注意：这里我们没有指定数据库，所以这个表将创建在default数据库中，Hive默认内置的一个数据库。

2、内部表
例如，我们没有数据文件，只是想创建一个Hive表，这个Hive表以后用于存储数据用，或从其他数据源导入，或程序写入等。内部表看起来就像RDBMS的数据表，Hive有足够大的权限可以操纵表里的数据，包括删除。
创建外部表时，使用的语句是create external table。而默认情况下，不使用external则是默认创建内部表。

以下是创建表语法：

    CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name    -- (Note: TEMPORARY available in Hive 0.14.0 and later)  
      [(col_name data_type [COMMENT col_comment], ... [constraint_specification])]  
      [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]  
      [SKEWED BY (col_name, col_name, ...)                  -- (Note: Available in Hive 0.10.0 and later)]  
         ON ((col_value, col_value, ...), (col_value, col_value, ...), ...)  
         [STORED AS DIRECTORIES]  
      [  
       [ROW FORMAT row_format]   
       [STORED AS file_format]  
         | STORED BY 'storage.handler.class.name' [WITH SERDEPROPERTIES (...)]  -- (Note: Available in Hive 0.6.0 and later)  
      ]  
      [LOCATION hdfs_path]  
      [TBLPROPERTIES (property_name=property_value, ...)]   -- (Note: Available in Hive 0.6.0 and later)  
      [AS select_statement];   -- (Note: Available in Hive 0.5.0 and later; not supported for external tables)  
       
    CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name  
      LIKE existing_table_or_view_name  
      [LOCATION hdfs_path];  
       
    data_type  
      : primitive_type  
      | array_type  
      | map_type  
      | struct_type  
      | union_type  -- (Note: Available in Hive 0.7.0 and later)  
       
    primitive_type  
      : TINYINT  
      | SMALLINT  
      | INT  
      | BIGINT  
      | BOOLEAN  
      | FLOAT  
      | DOUBLE  
      | DOUBLE PRECISION -- (Note: Available in Hive 2.2.0 and later)  
      | STRING  
      | BINARY      -- (Note: Available in Hive 0.8.0 and later)  
      | TIMESTAMP   -- (Note: Available in Hive 0.8.0 and later)  
      | DECIMAL     -- (Note: Available in Hive 0.11.0 and later)  
      | DECIMAL(precision, scale)  -- (Note: Available in Hive 0.13.0 and later)  
      | DATE        -- (Note: Available in Hive 0.12.0 and later)  
      | VARCHAR     -- (Note: Available in Hive 0.12.0 and later)  
      | CHAR        -- (Note: Available in Hive 0.13.0 and later)  
       
    array_type  
      : ARRAY < data_type >  
       
    map_type  
      : MAP < primitive_type, data_type >  
       
    struct_type  
      : STRUCT < col_name : data_type [COMMENT col_comment], ...>  
       
    union_type  
       : UNIONTYPE < data_type, data_type, ... >  -- (Note: Available in Hive 0.7.0 and later)  
       
    row_format  
      : DELIMITED [FIELDS TERMINATED BY char [ESCAPED BY char]] [COLLECTION ITEMS TERMINATED BY char]  
            [MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char]  
            [NULL DEFINED AS char]   -- (Note: Available in Hive 0.13 and later)  
      | SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, ...)]  
       
    file_format:  
      : SEQUENCEFILE  
      | TEXTFILE    -- (Default, depending on hive.default.fileformat configuration)  
      | RCFILE      -- (Note: Available in Hive 0.6.0 and later)  
      | ORC         -- (Note: Available in Hive 0.11.0 and later)  
      | PARQUET     -- (Note: Available in Hive 0.13.0 and later)  
      | AVRO        -- (Note: Available in Hive 0.14.0 and later)  
      | INPUTFORMAT input_format_classname OUTPUTFORMAT output_format_classname  
       
    constraint_specification:  
      : [, PRIMARY KEY (col_name, ...) DISABLE NOVALIDATE ]  
        [, CONSTRAINT constraint_name FOREIGN KEY (col_name, ...) REFERENCES table_name(col_name, ...) DISABLE NOVALIDATE  

三、创建分区表

目前我只尝试过在内部表上分区，没有尝试过外部表分区。分区是将HDFS中不同分区的数据文件存放于不同的目录下。例如一个Hive内部表数据保存于HDFS中/user/hive/warehouse/mytest.db/下，这个目录下全是数据文件（可能是文本文件格式，也可能是其他格式，根据file_format指定）。这时候若引入一个字段用于分区，则将会在这个目录下产生新的目录，每个目录对应一个分区。每个分区数据分别存在自己对应的目录下。这样查询效率也会更高。

创建分区表将使用到partitioned by子句。如下分区表：

create table partition_tb (  
id int, col1 string, col2 string)  
partitioned by (type int)  
stored as ORC  

这里需要注意，当指定分区列时，分区列不能是数据列。上面type列就没有出现在数据列（id，col1，col2）中。而在查询和写入语句中，type列完全可以像普通数据列一样使用，放在where子句，放在select列中。

当表建立好后，只是声明了有一个叫type的列可以用于分隔数据，但没有创建分区出来，也就是说，现在HDFS目录下还没有分区目录，分区信息的元数据也没有形成。那我们是等数据写入时再检测是否分区存在，并创建与否，还是事先建立好所有的分区，静等数据进入呢？这个也是Hive静态分区与动态分区的问题。我们完全可以配置使Hive允许动态分区。这里我只记录了非动态分区的做法（后续学习了动态分区留待补充）。配置参数hive.exec.dynamic.partition.mode=nonstrict。

ALTER TABLE table_name ADD [IF NOT EXISTS] PARTITION partition_spec   
  [LOCATION 'location1'] partition_spec [LOCATION 'location2'] ...;  
   
partition_spec:  
  : (partition_column = partition_col_value, partition_column = partition_col_value, ...)  

结合上面的partition_tb表，我们创建一个分区：

alter table partition_tb add partition (type = 1)  

注意：这里也可以指定LOCATION子句，这样分区目录将创建到LOCATION下。但之前的数据将还存在这个表中。

为表做分区主要考虑的是效率问题，重点需要考虑分区的列的基数（也就是该列包含唯一值的个数）。选择基数很高的列来做分区会导致数据严重的碎片化。不要对数据过分的分区。如果有太多的小分区，那么对这么多的分区目录进行扫描代价也是比较高的，甚至可能比全表扫描还高。

四、删除表

上面已经提到，对于内部表，删除后hive也会删除HDFS上存储的数据。对于外部表，只是表的元数据（metastore）会删除，真实数据并不会删除。

DROP TABLE [IF EXISTS] table_name [PURGE];     -- (Note: PURGE available in Hive 0.14.0 and later)  

下一篇将记录表操作CURD及笔者倒腾Hive遇到的一些坑。