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. Use Data Analytics Studio, or start Hive from the command line using your user name and substituting the name or IP address of your HiveServer host as follows.
    beeline -u jdbc:hive2://myhiveserver.com:10000 -n <your user name> -p
  2. Enter your user name and password.
    The Hive 3 connection message appears, followed by the Hive prompt for entering queries on the command line.
  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.