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

[#1]

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]

any help?

[#3]

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