createtableifnotexists test_orc(
advertiser_id string,
ad_plan_id string,
cnt BIGINT
) partitioned by (day string, type TINYINT COMMENT '0 as bid, 1 as win, 2 as ck', hour TINYINT)
STORED AS ORC;altertable test_orc set serdeproperties('serialization.null.format' = '');
查看结果
hive> showcreatetable test_orc;CREATETABLE`test_orc`(
`advertiser_id` string,
`ad_plan_id` string,
`cnt` bigint)
PARTITIONED BY (
`day` string,
`type` tinyint COMMENT '0 as bid, 1 as win, 2 as ck',
`hour` tinyint)
ROW FORMAT DELIMITED
NULL DEFINED AS''
STORED AS INPUTFORMAT
'org.apache.hadoop.hive.ql.io.orc.OrcInputFormat'
OUTPUTFORMAT
'org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat'
LOCATION
'hdfs://namenode/hivedata/warehouse/pmp.db/test_orc'
TBLPROPERTIES (
'last_modified_by'='pmp_bi',
'last_modified_time'='1465992624',
'transient_lastDdlTime'='1465992624')
方式二
droptable test_orc;createtableifnotexists test_orc(
advertiser_id string,
ad_plan_id string,
cnt BIGINT
) partitioned by (day string, type TINYINT COMMENT '0 as bid, 1 as win, 2 as ck', hour TINYINT)
ROW FORMAT SERDE
'org.apache.hadoop.hive.ql.io.orc.OrcSerde'with serdeproperties('serialization.null.format' = '')
STORED AS ORC;
查看结果
hive> showcreatetable test_orc;CREATETABLE`test_orc`(
`advertiser_id` string,
`ad_plan_id` string,
`cnt` bigint)
PARTITIONED BY (
`day` string,
`type` tinyint COMMENT '0 as bid, 1 as win, 2 as ck',
`hour` tinyint)
ROW FORMAT DELIMITED
NULL DEFINED AS''
STORED AS INPUTFORMAT
'org.apache.hadoop.hive.ql.io.orc.OrcInputFormat'
OUTPUTFORMAT
'org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat'
LOCATION
'hdfs://namenode/hivedata/warehouse/pmp.db/test_orc'
TBLPROPERTIES (
'transient_lastDdlTime'='1465992726')
方式三
drop table test_orc;
create table ifnot exists test_orc(
advertiser_id string,
ad_plan_id string,
cnt BIGINT
) partitioned by (day string, type TINYINT COMMENT '0 as bid, 1 as win, 2 as ck', hour TINYINT)
ROW FORMAT DELIMITED
NULL DEFINED AS ''
STORED AS ORC;
查看结果
hive> show create table test_orc;
CREATE TABLE `test_orc`(
`advertiser_id`string,
`ad_plan_id`string,
`cnt` bigint)
PARTITIONED BY (
`day`string,
`type` tinyint COMMENT '0 as bid, 1 as win, 2 as ck',
`hour` tinyint)
ROW FORMAT DELIMITED
NULL DEFINED AS ''
STORED AS INPUTFORMAT
'org.apache.hadoop.hive.ql.io.orc.OrcInputFormat'
OUTPUTFORMAT
'org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat'
LOCATION
'hdfs://namenode/hivedata/warehouse/pmp.db/test_orc'
TBLPROPERTIES (
'transient_lastDdlTime'='1465992916')