Skip to main content
na2_koihey11
na2_koihey11
Silver ⭐️⭐️
March 8, 2022
Solved

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

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? 

araujo
araujoAnswer
Platinum ⭐️⭐️⭐️
March 8, 2022

@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.
-- 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.
View in thread

7 replies

vidanimegh
vidanimegh
Gold ⭐️
March 8, 2022

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

Translate
vidanimegh
vidanimegh
Gold ⭐️
March 11, 2022

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

Translate
vidanimegh
vidanimegh
Gold ⭐️
March 11, 2022

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

Translate
araujo
Platinum ⭐️⭐️⭐️
araujoAnswer
Platinum ⭐️⭐️⭐️
March 8, 2022

@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.
Translate
-- 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.
na2_koihey11
na2_koihey11Author
Silver ⭐️⭐️
March 9, 2022

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

Translate
araujo
Platinum ⭐️⭐️⭐️
araujo
Platinum ⭐️⭐️⭐️
March 9, 2022

@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.
Translate
-- 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.
Terms & ConditionsAccessibility statement