HIVE的CTAS用法探究
最近在使用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功能。
建议继续学习:
- 如何获取hive建表语句 (阅读:6686)
- Hive源码解析-之-词法分析器 parser (阅读:5787)
- HIVE中UDTF编写和使用 (阅读:5266)
- Hive的入口 -- Hive源码解析 (阅读:4778)
- Hive源码解析-之-语法解析器 (阅读:4259)
- 用hadoop hive协同scribe log用户行为分析方案 (阅读:4136)
- 几个HIVE的streaming (阅读:3402)
- 写好Hive 程序的五个提示 (阅读:3170)
- 快速复制一张大表讨论 (阅读:3137)
- Impala与Hive的比较 (阅读:2928)
扫一扫订阅我的微信号:IT技术博客大学习
- 作者:lengchuan 来源: 量子数科院
- 标签: CTAS HIVE
- 发布时间:2011-04-29 13:41:11
- [66] Go Reflect 性能
- [66] Oracle MTS模式下 进程地址与会话信
- [65] 如何拿下简短的域名
- [59] IOS安全–浅谈关于IOS加固的几种方法
- [59] android 开发入门
- [59] 图书馆的世界纪录
- [58] 【社会化设计】自我(self)部分――欢迎区
- [53] 视觉调整-设计师 vs. 逻辑
- [47] 界面设计速成
- [47] 读书笔记-壹百度:百度十年千倍的29条法则