Support Questions

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

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

avatar

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? 

1 ACCEPTED SOLUTION

avatar
Super Guru

@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 solution in original post

7 REPLIES 7

avatar
Expert Contributor

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

avatar
Expert Contributor

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

avatar
Expert Contributor

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

avatar

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.

avatar
Super Guru

@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.

avatar

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

avatar
Super Guru

@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.
--
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.