Support Questions

Find answers, ask questions, and share your expertise
Announcements
Celebrating as our community reaches 100,000 members! Thank you!

How to disable hive managed tables from becoming transactional/insert-only tables in HDP 3.x. Blocking migration to HDP 3.x

avatar
New Contributor

a 'create table ..' command in HDP 3.x results in the table becoming transactional/insert-only table.

+----------------------------------------------------+

| createtab_stmt |

+----------------------------------------------------+

| CREATE TABLE `ytemp.emps`( |

| `empid` int, |


| ROW FORMAT SERDE |

| 'org.apache.hadoop.hive.ql.io.orc.OrcSerde' |

| STORED AS INPUTFORMAT |

| 'org.apache.hadoop.hive.ql.io.orc.OrcInputFormat' |

| OUTPUTFORMAT |

| 'org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat' |

| LOCATION |

| 'hdfs://xxxx/ytemp.db/emps' |

| TBLPROPERTIES ( |

| 'bucketing_version'='2', |

| 'transactional'='true', |

| 'transactional_properties'='insert-only', |

| 'transient_lastDdlTime'='1554244939') |

+----------------------------------------------------+

tables created by 'create table .' command did not have the 'transactional'='true', 'transactional_properties'='insert-only', in HDP 2.x.

These types of tables (transactional) are not readable by Spark or Presto.

I understand, that this is the default behavior in HDP 3.x. But this breaks backward compatibility with HDP 2.x (where Hive managed tables were not transactional tables by default).

Can someone please recommend a way to disable hive managed tables becoming transactional?, as this is a big impediment for migrating to HDP 3.x from HDP 2.x

Note: I don't want to create 'external tables'

2 REPLIES 2

avatar
New Contributor

any help?

avatar
Contributor

Hi, 
you can get the legacy behavior of "create table" by executing

 

 

SET hive.create.as.external.legacy=true;

 

 

https://docs.cloudera.com/cdp-private-cloud-base/7.1.6/configuring-apache-hive/topics/hive_create_ta...

 

This gives you the old behavior of hive 1.x / 2.x for your tables