I ran across something today which I thought I should share regarding the “CREATE TABLE LIKE…” statement. I found that not all of the tblproperties clauses of the originating table are copied to the new table definition. Below is a quick test….
Note that the second table ends up missing the SKIP.LINE properties, but does keep the NULL FORMAT property.
So, the moral of the story is to double check yourself when using the “CREATE TABLE LIKE” statement
drop table if exists test1;
create external table test1
(val string)
stored as textfile
location ‘/test1’
tblproperties (‘skip.header.line.count’=’1’
,‘skip.footer.line.count’=’1’
,’serialization.null.format’=”);
drop table if exists test2;
create table test2
like jjohns008c.test1
location ‘/test2’;
show create table test2;
CREATE TABLE `test2`(
`val` string)
ROW FORMAT SERDE
‘org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe’
STORED AS INPUTFORMAT
‘org.apache.hadoop.mapred.TextInputFormat’
OUTPUTFORMAT
‘org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat’
LOCATION
‘hdfs://test2’
TBLPROPERTIES (
‘serialization.null.format’=”,
‘transient_lastDdlTime’=’1479502570’);