Key Differences between INSERT-ONLY and FULL ACID Tables
Before beginning to use FULL ACID v2 tables you must be aware of the key differences between the INSERT-ONLY and FULL-ACID tables.
This table highlights some of the differences between the INSERT-ONLY and FULL ACID tables.
| INSERT-ONLY | FULL ACID | |
|---|---|---|
Schema |
There is no special data schema. They store the data just like plain original files from the non-ACID world. | Data is in special format, i.e. there are synthetic columns with transactional information in addition to actual data. |
Transactional information |
Transactional information is encoded in directory names. | Full ACID tables also use the same directory structure as INSERT-only tables. Transactional information is encoded in the directory names. Directory name and filename are the source of transactional information. |
Table properties |
'transactional'='true', 'transactional_properties'='insert_only' | 'transactional'='true' |
Supported operations |
INSERT-ONLY tables only support insertion of data. UPDATES and DELETES are not supported. These tables also provide CREATE TABLE, DROP TABLE, TRUNCATE, INSERT, SELECT operations. | FULL ACID ORC tables can be READ using IMPALA. These tables also provide UPDATE and DELETE operations at the row level using HIVE. This is achieved using transactions like Insert-Only Tables along with changes in ORC Reader to support deletes. |
WRITE operation |
WRITE operations are atomic and the results of the insert operation are not visible to other query operations until the operation is committed. | WRITE operations are atomic - The operation either succeeds completely or fails; it does not result in partial data. |
INSERT operation |
For every INSERT statement the created files are added to a transactional directory which has transactional information in its name. | INSERT operation is done through HIVE and this statement is executed in a single transaction. This operation creates a delta directory containing information about this transaction and its data. |
DELETE operation |
N/A | DELETE operation is done through HIVE and this event creates a special “delete delta” directory. |
UPDATE operation |
N/A | UPDATE operation is done through HIVE. This operation is split into an INSERT and DELETE operation. This operation creates a delta dir followed by a delete dir. |
READ operation |
READ operations always read a consistent snapshot of the data. | READ operations always read a consistent snapshot of the data. |
Supported file format |
Supports any file formats. | Supports only ORC. |
Compactions |
Minor and major compactions are supported. | Minor compactions can be created, which means several delta and delete directories can be compacted into one delta and delete directory. Major compactions are also supported. |
File structure of FULL ACID transational table
Hive 3 achieves atomicity and isolation of operations on transactional tables by using techniques in write, read, insert, create, delete, and update operations that involve delta files, which can provide query status information and help you troubleshoot query problems. For more information, see Hive 3 ACID transactions
