最近在使用ADM系统的时候遇到一个问题，ADM在自动将HIVE QL包装成CTAS之后，由于HIVE内部缺省使用’\N’来存储NULL，这样就会产生一个问题，因为我们处理的很多结果数据是需要导出附件来给下游客户使用的，而导出数据时很少会使用这样一个特殊的字符串来代表NULL值。

这种情况下，HIVE为我们提供了重新定义NULL值存储格式的方法，使用serialization.null.format参数。

一、CTAS功能探究

对于已经创建成功的hive表，如果希望修改NULL值的存储格式，可以使用以下语句修改空字串定义：

alter table table_name set serdeproperties(‘serialization.null.format’ = ”);

目前，HIVE新版本（0.6.0）已经开始支持CTAS功能，我们希望能够在CTAS动态建表的同时完成缺省NULL串的转化。

首先，来看一下CTAS的语法规则：

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]

   | STORED BY ‘storage.handler.class.name’ [ WITH SERDEPROPERTIES (...) ]  (Note:  only available starting with 0.6.0)

  ]

  [LOCATION hdfs_path]

  [TBLPROPERTIES (property_name=property_value, ...)]  (Note:  only available starting with 0.6.0)

  [AS select_statement]  (Note: this feature is only available starting with 0.5.0.)

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是Serialize/Deserilize的简称，目的是用于序列化和反序列化。用户在建表时可以用自定义的SerDe或使用Hive自带的SerDe，SerDe能为表指定列，且对列指定相应的数据。在脚本中为SerDe设置相应的键值对就可以达到目的，以下两个SerDe是比较常用的：

field.delim是分隔符参数

serialization.null.format 用于null值的存储格式设置

注意：上面两个是表的全局参数，这意味着一旦对单个表指定了这一参数，它将对表的所有分区有效。其中属性名和属性值都必须用单引号括起来。

接下来我们测试一下这个新功能：

1、CATS并设置null的存储格式为空

create table gv_test_tmp

ROW FORMAT SERDE ‘org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe’

WITH SERDEPROPERTIES(‘serialization.null.format’=”)

stored as textfile

as

select col1,col2 from fact_tmp_d where dt=’20110427′ limit 5;

2、查看表gv_test_tmp对应的hadoop文件内容，如下：

        1^A/N

        2^A/N

       3^A3                                                                                                                                                           

        4^A4

        5^A5

可以看出，NULL的存储格式并没有变成我们预想的’’，还是缺省的’\N’。难道我们设置的SerDe参数没有起作用？

二、追根溯源

1、  Desc来描述表的属性

hive> desc extended gv_test_tmp ;

OK

col1  bigint

col2  bigint

Detailed Table Information      Table(tableName:gv_test_tmp, dbName:default, owner:gvora, createTime:1303893563, lastAccessTime:0, retention:0, sd:StorageDescriptor(cols:[FieldSchema(name:col1, type:bigint, comment:null), FieldSchema(name:col2, type:bigint, comment:null)], location:hdfs://hdpnn:****/gv_test_tmp, inputFormat:org.apache.hadoop.mapred.TextInputFormat, outputFormat:org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat, compressed:false, numBuckets:-1, serdeInfo:SerDeInfo(name:null, serializationLib:org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe, parameters:{serialization.null.format=, serialization.format=1}), bucketCols:[], sortCols:[], parameters:{}), partitionKeys:[], parameters:{transient_lastDdlTime=1303893563}, viewOriginalText:null, viewExpandedText:null, tableType:MANAGED_TABLE)

Time taken: 4.505 seconds

可以看出“serialization.null.format=”已经改成’’，不是缺省的’\N’了，这说明选项设置起到了作用，实际的数据存储却没有变。

2、接下来查看该CTAS语句的执行计划：

hive> explain create table gv_test_tmp

    > ROW FORMAT SERDE ‘org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe’

    > WITH SERDEPROPERTIES(‘serialization.null.format’=”)

    > stored as textfile

    > as

    > select col1,col2 from fact_tmp_d where dt=‘20110427’ limit 10;

OK

……

STAGE DEPENDENCIES:

  Stage-1 is a root stage

  Stage-0 depends on stages: Stage-1

  Stage-2 depends on stages: Stage-0

STAGE PLANS:

  Stage: Stage-1

    Map Reduce

      Alias -> Map Operator Tree:

…… （此处省略500字）

      Reduce Operator Tree:

        Extract

          Limit

            File Output Operator

              compressed: true

              GlobalTableId: 0

              table:

                  input format: org.apache.hadoop.mapred.TextInputFormat

                  output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat

  Stage: Stage-0

    Move Operator

      files:

          hdfs directory: true

          destination: /group/tbdev/gvora/hive/gv_test_tmp

  Stage: Stage-2

      Create Table Operator:

        Create Table

          columns: col1 bigint, col2 bigint

          if not exists: false

          input format: org.apache.hadoop.mapred.TextInputFormat

          # buckets: -1

          output format: org.apache.hadoop.hive.ql.io.IgnoreKeyTextOutputFormat

          serde name: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe

          serde properties:

            serialization.null.format

          name: gv_test_tmp

          isExternal: false

Time taken: 0.683 seconds

从执行计划不难看出，整个CTAS脚本的执行过程主要分为三个阶段：

Stage-1：执行select语句

Stage-0：move查询结果至缺省hadoop目录

Stage-2：创建表指向对应的查询结果目录

很明显，创建表对象的过程被放到了select语句完成之后，这就不难推出为啥表定义中显示的存储格式改变了，而实际的hadoop文件结果却没变的原因。

三、可能的替代方案

针对这个问题，为了能够实现ADM系统对于select语句的动态包装，并应用新的null存储格式设定，可以采用如下的替代方案来打破CTAS默认的执行步骤：

1、实现基于动态查询SQL的表结构及待修正的NULL存储格式

hive> create table gv_test_tmp                                                    

    > ROW FORMAT SERDE ‘org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe’

    > WITH SERDEPROPERTIES (

    > ‘serialization.null.format’=”

    > )

    > as

    > select col1,col2 from fact_tmp_d where dt=‘20110427’ limit 5;

2、新建一张表gv_test_tmp2，表结构及列定义等copy 表gv_test_tmp。

hive> create table gv_test_tmp2 like gv_test_tmp;

3、接下来实现表数据的insert

hive> insert overwrite table gv_test_tmp2 select * from gv_test_tmp;

通过查看生成的hadoop结果文件，NULL的存储格式已改变：

        1^A

        2^A

        3^A3

        4^A4

        5^A5

总结，本文主要介绍了HIVE的CTAS功能，通过测试发现问题，最终采用一种折中的方式解决问题。文中的方案多了一次重新insert结果数据至新表的过程，性能上会有折扣，有待HIVE继续完善CTAS功能。

建议继续学习：

1. 如何获取hive建表语句    (阅读：6703)
2. Hive源码解析-之-词法分析器 parser    (阅读：5860)
3. HIVE中UDTF编写和使用    (阅读：5285)
4. Hive的入口 -- Hive源码解析    (阅读：4849)
5. Hive源码解析-之-语法解析器    (阅读：4346)
6. 用hadoop hive协同scribe log用户行为分析方案    (阅读：4153)
7. 几个HIVE的streaming    (阅读：3423)
8. 快速复制一张大表讨论    (阅读：3207)
9. 写好Hive 程序的五个提示    (阅读：3185)
10. Impala与Hive的比较    (阅读：3016)