> CommCell Console > Backup and Restore Agents > Backup Agents > Hadoop (HDFS) > Apps > Hive Overview > Hive Restores

Hive Transactional Table Restores

Hive transactional tables are restored as transactional tables only if the tables are in Optimized Row Columnar (ORC) format. If the transactional tables are in any other format, they are restored as non-transactional, external tables.

Note

  • For Hive 3.x, if the backup contains managed ORC tables and if the plan has an associated schedule, disable the schedule or use a different plan with no associated schedule. Subclient backups that are triggered manually or by a schedule mark the managed ORC tables as deleted and are not available for restores.

  • You cannot restore views.

For example, if you have a transactional table created by using the following SQL statement:

CREATE TABLE `man_textfile_bucket_serde0`( `id` int, `col0` string, `col1` string, `col2` string) CLUSTERED BY ( id) INTO 4 BUCKETS ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe' STORED AS INPUTFORMAT 'org.apache.hadoop.mapred.TextInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' LOCATION 'hdfs://cdp1.testride.com:8020/warehouse/tablespace/managed/hive/autodb.db/man_textfile_bucket_serde0' TBLPROPERTIES ( 'bucketing_version'='2', 'transactional'='true', 'transactional_properties'='insert_only', 'transient_lastDdlTime'='1624945011')

The table is restored as an external table with the following properties:

CREATE EXTERNAL TABLE `man_textfile_bucket_serde0`( `id` int, `col0` string, `col1` string, `col2` string) CLUSTERED BY ( id) INTO 4 BUCKETS ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe' STORED AS INPUTFORMAT 'org.apache.hadoop.mapred.TextInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' LOCATION 'hdfs://cdp1.testride.com:8020/warehouse/tablespace/external/hive/autodb_restored.db/man_textfile_bucket_serde0' TBLPROPERTIES ( 'bucketing_version'='2', 'last_modified_by'='hive', 'last_modified_time'='1625841462', 'transient_lastDdlTime'='1625841462')

Transactional Tables in Avro Format

You can also restore the transactional tables in Avro format but the link file must be present at the time of restores.

You cannot restore the tables in Avro format to a different cluster.

For example, you can restore a transactional table in Avro format created by using the following SQL statement:

CREATE EXTERNAL TABLE `avro_db.avroserde`(  ROW FORMAT SERDE  'org.apache.hadoop.hive.serde2.avro.AvroSerDe' 
STORED AS INPUTFORMAT 
’org.apache.hadoop.hive.ql.io.avro.AvroContainerInputFormat' 
OUTPUTFORMAT  'org.apache.hadoop.hive.ql.io.avro.AvroContainerOutputFormat' 
LOCATION 
'hdfs://cdp1.testride.com:8020/warehouse/tablespace/external/hive/avro_db.db/avroserde'  TBLPROPERTIES ( 
'avro.schema.url'='/hive_ndp/avro_schm.avsc',  'bucketing_version'='2', 
'transient_lastDdlTime'='1624627779')
×

Loading...