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.

In this task, you create an insert-only transactional table for storing data in the default storage format of your SQL engine.. In Hive, setting 'transactional'='true','transactional_properties'='insert_only' in table properties when creating the table is required; otherwise, a CRUD table results. In Impala, these table properties are set by default..

In Hive, the default storage format is ORC (Optimized Row Columnar). In Impala, the default storage format is Parquet. Use the STORED AS clause is optional if you want to specify a different format.

  1. Start Hive or Impala.
  2. Enter your user name and password.
  3. Create an insert-only transactional table in the default storage file format having two integer columns, a and b:
    Hive example:
    CREATE TABLE T2(a int, b int) 
      TBLPROPERTIES ('transactional'='true','transactional_properties'='insert_only');
    Impala example:
    CREATE TABLE T3 (a int, b int);  
    By default, Impala creates an insert-only table when you run a CREATE TABLE statement to create a managed table. Specifying the 'transactional'='true' 'transactional_properties'='insert_only' table property is optional.