最近在使用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功能。