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.
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)
Only managed tables can be transactional - not external.
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.