Creating an insert-only transactional table

You can create a transactional table using any storage format if you do not require update and delete capability. This type of table has ACID properties, is a managed table, and accepts insert operations only. The storage format of an insert-only table is not restricted to ORC.

In this task, you create an insert-only transactional table for storing text. In the CREATE TABLE statement, specifying a storage type other than ORC, such as text, CSV, AVRO, or JSON, results in an insert-only ACID table. You can explicitly specify insert-only in the table properties clause.

  1. Start Hive.
  2. Enter your user name and password.
    The Hive 3 connection message, followed by the Hive prompt for entering SQL queries on the command line, appears.
  3. Create a insert-only transactional table named T2 having two integer columns, a and b:
    CREATE TABLE T2(a int, b int) 
      STORED AS ORC
      TBLPROPERTIES ('transactional'='true',
      'transactional_properties'='insert_only');
    The 'transactional_properties'='insert_only' is required; otherwise, a CRUD table results. The STORED AS ORC clause is optional (default = ORC).
  4. Create an insert-only transactional table for text data.
    CREATE TABLE T3(a int, b int) 
    STORED AS TEXTFILE;
    The 'transactional_properties'='insert_only' is not required because the storage format is other than ORC.