Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

External table error with TBLPROPERTIES("transactional"="true"

Highlighted

External table error with TBLPROPERTIES("transactional"="true"

When i try to create external table i get below below error. Can anybody help to resolve this issue.

CREATE EXTERNAL TABLE DIM_ROOM1 ( ROOM_CATEGORY_ID INT, UPD_BATCH_ID INT) CLUSTERED BY (ROOM_CATEGORY_ID) INTO 3 BUCKETS ROW FORMAT DELIMITED STORED AS ORC location '/proj/arcadia/edw/opera/dim_room' TBLPROPERTIES("transactional"="true");

This is same syntax posted by hottom works at below link.

https://community.hortonworks.com/articles/52856/stream-data-into-hive-like-a-king-using-nifi.html.

Below is the error message . FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. MetaException(message:default.DIM_ROOM1 cannot be declared transactional because it' s an external table)

2 REPLIES 2
Highlighted

Re: External table error with TBLPROPERTIES("transactional"="true"

Expert Contributor

Only managed tables can be transactional - not external.

Re: External table error with TBLPROPERTIES("transactional"="true"

Contributor
@Sateesh Mandumula

Obviously You cannot have transactional Properties on top of external table as Hive just reads the file present in HDFS. Its not suppose to change the content of the External files.

If you really want to use Transactional Property, Create an internal table select * from external table and use transaction properly on this internal table.

Don't have an account?
Coming from Hortonworks? Activate your account here