question Re: How to create hive table as non-transactional, by default? in Support Questions

How to create hive table as non-transactional, by default?

na2_koihey11 — Tue, 08 Mar 2022 03:24:04 GMT

I am using HDP hive-3.1.

My configuration as follows:

hive.support.concurrency=true;

hive.txn.manager=org.apache.hadoop.hive.ql.lockmgr.DbTxnManager; hive.exec.dynamic.partition.mode=nonstrict;

hive.compactor.initiator.on=true;

hive.compactor.worker.threads=2;

By default, transactional table is created in hive.

however, i want to create non-transactional table , by default. I tried to change hive.txn.manager to Dummymanager. Though after changing the configuration, hive table is created as transactional.

any suggestion to create non-transactional table, be default?

Re: How to create hive table as non-transactional, by default?

vidanimegh — Tue, 08 Mar 2022 12:49:40 GMT

check hive.default.fileformat and hive.default.fileformat.managed properties

Re: How to create hive table as non-transactional, by default?

araujo — Tue, 08 Mar 2022 21:41:34 GMT

@na2_koihey11 ,

I don't think this is possible. At least, I couldn't find a way to revert the default behaviour.

The default behaviour is:

Managed tables are created as transactional by default
External table are created as non-transactional by default

If you want anything different from the above you have to explicitly specify the transactional property in the TBLPROPERTIES clause.

Cheers,

André

Was your question answered? Please take some time to click on "Accept as Solution" below this post.

If you find a reply useful, say thanks by clicking on the thumbs up button.

Re: How to create hive table as non-transactional, by default?

na2_koihey11 — Wed, 09 Mar 2022 01:54:29 GMT

@araujo Is there any official documentation, which states the statement as mentioned by you. I would like to study more in detail.

Re: How to create hive table as non-transactional, by default?

araujo — Wed, 09 Mar 2022 02:15:34 GMT

@na2_koihey11 ,

Yes, there is! Please take a look at the page below:

https://docs.cloudera.com/runtime/7.2.10/using-hiveql/topics/hive_hive_3_tables.html

Cheers,

André

Was your question answered? Please take some time to click on "Accept as Solution" below this post.

If you find a reply useful, say thanks by clicking on the thumbs up button.

Re: How to create hive table as non-transactional, by default?

vidanimegh — Fri, 11 Mar 2022 11:41:20 GMT

Hi @na2_koihey11 ,

We're also using HDP 3.1 and we are able to create non-transactional tables by default by setting hive.default.fileformat.managed to TextFile.

Thanks,

Megh

Re: How to create hive table as non-transactional, by default?

vidanimegh — Fri, 11 Mar 2022 11:45:21 GMT

We've set this property in advanced hive-site section

Re: How to create hive table as non-transactional, by default?

na2_koihey11 — Mon, 14 Mar 2022 00:45:11 GMT

hive.default.fileformat.managed = ORC

hive.default.fileformat = TextFile

we tried to change hive.default.fileformat.managed to TextFile, still it doesn't work.
Could you please check how it works for you.