- BEGIN, COMMIT, and ROLLBACK are not yet supported. All language operations are auto-commit. The plan is to support these in a future release.
- Only ORC file format is supported in this first release. The feature has been built such that transactions can be used by any storage format that can determine how updates or deletes apply to base records (basically, that has an explicit or implicit row id), but so far the integration work has only been done for ORC.
- By default transactions are configured to be off. See the Configuration 82706483 section below for a discussion of which values need to be set to configure it.
- Tables must be bucketed to make use of these features. Tables in the same system not using transactions and ACID do not need to be bucketed. External tables cannot be made ACID tables since the changes on external tables are beyond the control of the compactor (HIVE-13175).
- Reading/writing to an ACID table from a non-ACID session is not allowed. In other words, the Hive transaction manager must be set to org.apache.hadoop.hive.ql.lockmgr.DbTxnManager in order to work with ACID tables.
- At this time only snapshot level isolation is supported. When a given query starts it will be provided with a consistent snapshot of the data. There is no support for dirty read, read committed, repeatable read, or serializable. With the introduction of BEGIN the intention is to support snapshot isolation for the duration of transaction rather than just a single query. Other isolation levels may be added depending on user requests.
- The existing ZooKeeper and in-memory lock managers are not compatible with transactions. There is no intention to address this issue. See Basic Design 82706483 below for a discussion of how locks are stored for transactions.
Schema changes using ALTER TABLE is NOT supported for ACID tables. HIVE-11421 is tracking it.Fixed in 1.3.0/2.0.0.
- Using Oracle as the Metastore DB and "datanucleus.connectionPoolingType=BONECP" may generate intermittent "No such lock.." and "No such transaction..." errors. Setting "datanucleus.connectionPoolingType=DBCP" is recommended in this case.
- LOAD DATA... statement is not supported with transactional tables. (This was not properly enforced until HIVE-16732)
A new option has been added to ALTER TABLE to request a compaction of a table or partition. In general users do not need to request compactions, as the system will detect the need for them and initiate the compaction. However, if compaction is turned off for a table or a user wants to compact the table at a time the system would not choose to, ALTER TABLE can be used to initiate the compaction. See Alter Table/Partition Compact for details. This will enqueue a request for compaction and return. To watch the progress of the compaction the user can use SHOW COMPACTIONS.
Also see LanguageManual DDL#ShowCompactions for more information on the output of this command and NewConfigurationParametersforTransactions82706483/Compaction History for configuration properties affecting the output of this command. The system retains the last N entries of each type: failed, succeeded, attempted (where N is configurable for each type).
A new logical entity called "transaction manager" was added which incorporated previous notion of "database/table/partition lock manager" (hive.lock.manager with default of org.apache.hadoop.hive.ql.lockmgr.zookeeper.ZooKeeperHiveLockManager). The transaction manager is now additionally responsible for managing of transactions locks. The default DummyTxnManager emulates behavior of old Hive versions: has no transactions and uses hive.lock.manager property to create lock manager for tables, partitions and databases. A newly added DbTxnManager manages all locks/transactions in Hive metastore with DbLockManager (transactions and locks are durable in the face of server failure). This means that previous behavior of locking in ZooKeeper is not present anymore when transactions are enabled. To avoid clients dying and leaving transaction or locks dangling, a heartbeat is sent from lock holders and transaction initiators to the metastore on a regular basis. If a heartbeat is not received in the configured amount of time, the lock or transaction will be aborted.
New Configuration Parameters for Transactions
A number of new configuration parameters have been added to the system to support transactions.
Value required for transactions: org.apache.hadoop.hive.ql.lockmgr.DbTxnManager
DummyTxnManager replicates pre Hive-0.13 behavior and provides no transactions.
|In strict mode non-ACID resources use standard R/W lock semantics, e.g. INSERT will acquire exclusive lock. In non-strict mode, for non-ACID resources, INSERT will only acquire shared lock, which allows two concurrent writes to the same partition but still lets lock manager prevent DROP TABLE etc. when the table is being written to (as of Hive 2.2.0).|
Time after which transactions are declared aborted if the client has not sent a heartbeat, in seconds. It's critical that this property has the same value for all components/services.5
|The number of threads to use for heartbeating (as of Hive 1.3.0 and 2.0.0).|
|hive.timedout.txn.reaper.start||Default: 100s||Metastore||Time delay of first reaper (the process which aborts timed-out transactions) run after the metastore starts (as of Hive 1.3.0). Controls AcidHouseKeeperServcie above.|
Time interval describing how often the reaper (the process which aborts timed-out transactions) runs (as of Hive 1.3.0). Controls AcidHouseKeeperServcie above.
Maximum number of transactions that can be fetched in one call to open_txns().1
|Maximum number of open transactions. If current open transactions reach this limit, future open transaction requests will be rejected, until the number goes below the limit. (As of Hive 1.3.0 and 2.1.0.)|
|Time in seconds between checks to count open transactions (as of Hive 1.3.0 and 2.1.0).|
|hive.txn.retryable.sqlex.regex||Default: "" (empty string)|
Comma separated list of regular expression patterns for SQL state, error code, and error message of retryable SQLExceptions, that's suitable for the Hive metastore database (as of Hive 1.3.0 and 2.1.0).
For an example, see Configuration Properties.
Value required for transactions: true (for exactly one instance of the Thrift metastore service)
Whether to run the initiator and cleaner threads on this metastore instance. Prior to Hive 1.3.0 it's critical that this is enabled on exactly one standalone metastore service instance (not enforced yet).
As of Hive 1.3.0 this property may be enabled on any number of standalone metastore instances.
Value required for transactions: > 0 on at least one instance of the Thrift metastore service
How many compactor worker threads to run on this metastore instance.2
Time in seconds after which a compaction job will be declared failed and the compaction re-queued.
|hive.compactor.cleaner.run.interval||Default: 5000||Metastore||Time in milliseconds between runs of the cleaner thread. (Hive 0.14.0 and later.)|
Time in seconds between checks to see if any tables or partitions need to be compacted.3
Number of delta directories in a table or partition that will trigger a minor compaction.
Percentage (fractional) size of the delta files relative to the base that will trigger a major compaction. 1 = 100%, so the default 0.1 = 10%.
Number of aborted transactions involving a given table or partition that will trigger a major compaction.
|hive.compactor.aborted.txn.time.threshold||Default: 12h||Metastore||Age of table/partition's oldest aborted transaction when compaction will be triggered. Default time unit is: hours. Set to a negative number to disable.|
|Default: 500||Metastore||Maximum number of delta files that the compactor will attempt to handle in a single job (as of Hive 1.3.0).4|
|Default: "" (empty string)||Metastore||Used to specify name of Hadoop queue to which Compaction jobs will be submitted. Set to empty string to let Hadoop choose the queue (as of Hive 1.3.0).|
|Default: 3||Metastore||Number of successful compaction entries to retain in history (per partition).|
|Default: 3||Metastore||Number of failed compaction entries to retain in history (per partition).|
|Default: 2||Metastore||Number of attempted compaction entries to retain in history (per partition).|
|Default: 2||Metastore||Number of of consecutive failed compactions for a given partition after which the Initiator will stop attempting to schedule compactions automatically. It is still possible to use ALTER TABLE to initiate compaction. Once a manually initiated compaction succeeds auto initiated compactions will resume. Note that this must be less than hive.compactor.history.retention.failed.|
|Default: 2m||Metastore||Controls how often the process to purge historical record of compactions runs.|
1hive.txn.max.open.batch controls how many transactions streaming agents such as Flume or Storm open simultaneously. The streaming agent then writes that number of entries into a single file (per Flume agent or Storm bolt). Thus increasing this value decreases the number of delta files created by streaming agents. But it also increases the number of open transactions that Hive has to track at any given time, which may negatively affect read performance.
In addition to the new parameters listed above, some existing parameters need to be set to support INSERT ... VALUES, UPDATE, and DELETE.
|Configuration key||Must be set to|
|hive.support.concurrency||true (default is false)|
|hive.enforce.bucketing||true (default is false) (Not required as of Hive 2.0)|
|hive.exec.dynamic.partition.mode||nonstrict (default is strict)|
Configuration Values to Set for Compaction
DataWorks Summit 2018, San Jose, CA, USA - Covers Hive 3 and ACID V2 features