Using Apache HiveQL
Also available as:
PDF

Alter a table from flat to transactional

Using the ALTER TABLE statement, you can convert a flat table to a transactional table.

In this task you create a flat table, which is a managed table that resides in the Hive warehouse, but is not transactional. In a real-world situation, you might already have a flat table present from earlier releases. In addition to showing you how to create a flat table, this task shows you how to use an ALTER TABLE statement to change the flat table to transactional. Upon completion of the task, you can immediately run update and delete operations on the table.

  1. Start the Hive shell:
    [vagrant@c7401]# hive
  2. Enter your user name and password.
    The Hive 3 connection message appears, followed by the Hive prompt for entering HiveQL queries on the command line:
    Connected to: Apache Hive (version 3.0.0.3.0.0.0-1361)
    Driver: Hive JDBC (version 3.0.0.3.0.0.0-1361)
    Transaction isolation: TRANSACTION_REPEATABLE_READ
    Beeline version 3.0.0.3.0.0.0-1361 by Apache Hive
                            
    0: jdbc:hive2://c7402.ambari.apache.org:2181,>
  3. Create a flat table and populate it with values.
    CREATE TABLE T3(a int, b int) 
    TBLPROPERTIES ('transactional'='false');
    INSERT INTO TABLE T3 VALUES (10, 20), (30, 40);
  4. Alter the table to make it transactional.
    ALTER TABLE T3 SET TBLPROPERTIES ('transactional'='true');