Transactions in HIVE

Full ACID  semantics at row level is supported since HIVE  0.13.  Earlier it was only supported at partition level.  

  • Atomicity: Entire operation is single operation. Either entire operation happens or none.
  • Consistency: Once an operation is completed, every subsequent operations sees the same result of it. 
  • Isolation: One user operation does not impact another user. 
  • Durability: Once operation is done ..its result remains thereafter.
At present isolation is only at snapshot level. There are following isolation levels in various DBMS:
  • Snapshot: The snapshot of data at the beginning of  transaction will be visible to through out the transaction. Whatever is happening in other transaction will never be seen by this transaction. 
  • Dirty Read: Uncommitted updates from other transactions can be seen.
  • Read Committed: Only updates which are committed at the time of read will be seen by this transaction.
  • Repeatable Read: Read lock on data being read and write lock on data being created/updated/deleted. 
  • Serializable: Most restrictive. The concurrent transaction appear as if happening in serial. 
Transactions are only supported for ORC format. By default transactions are turned off. Also the transaction manager should be set to DBTxnTransactionManager in order to work with ACID tables. Tables must be bucketed for using ACID feature.  As external tables are beyond the control of compactor, those can not be ACID. You need to set some TBLPROPERTIES to indicate it to be ACID table. You also need to set some other configuration in the Hive ( session ) for enabling ACID transactions for these tables, otherwise depending upon HIVE version and the operation , either will be executed in non-ACID manner or will not be allowed.


Comments

Post a Comment

Popular posts from this blog

SQL

Joins - Not always very obvious ( Following example uses Hive ). create table x_temp ( name string, p_date int  ) insert into x_temp(name,p_date) values ('x1',20181025),('x2', 20181026), ('x3',20181027) create table y_temp ( name string, s_date int  ) insert into y_temp(name,s_date) values ('x1',20181025),('x2', 20181026), ('x3',20181027) create table z_temp ( name string, p_date int, s_date int ) insert into z_temp(name,p_date,s_date) values ('z1','20181025','20181025') select x.*, y.* from x_temp x, y_temp y, z_temp z  where x.p_date != z.p_date and  y.s_date != z.s_date) generates 4 records... x.name,x.p_date,y.name,y.s_date x2, 20181026, x2, 20181026 x3, 20181027, x2, 20181026 x2, 20181026, x3, 20181027 x3, 20181027, x3, 20181027 select x.*, y.* from x_temp x, y_temp y, z_temp z  where x.p_date != z.p_date or  y.s_date != z.s_date) generates 8 records x.name,x.p_date,y.
Read more

Analytics

Types of Analytics ( Notes from AWS Course )  Descriptive - What happened ?  hindsight  Diagnostic - Why did it  happen?  hindsight & insight  Predictive  - What will happen ? insight & foresight  Prescriptive - What should I do ? foresight  Cognitive & AI  - What are recommended actions ? foresight & hypothesis input  Popular BI Tools:  https://www.cio.com/article/3284415/10-bi-tools-for-data-visualization.html
Read more

HIVE

1. When doing multi table joins, except the last ( right most ) table, other tables are buffered in the reducers. So to have less memory footprint on reducers, largest tables should be the last one as it is not buffered but streamed to reducers.   For Impala , it is different. 2.  You can filter the unwanted records within the join itself. SELECT  a.*,   b. * FROM a LEFT OUTER JOIN b ON ( a.key=b.key  AND  b.name= 'Tim'   AND a .name= 'Kim' ) 3. Joins are left associative. So execution happens from left to right and the result of joins is  fed into next join operation. 4. To find the value of variable     set hive.execution.engine 5. To find values of all variables set  6. If you want to pass different hive configuration settings in hive shell, you can use --hiveconf. For example hive --hiveconf hivi.optimize.sort.dynamic.partition=true --hiveconf hive.exec.dynamic.partition.mode=nonstrict 7.  In case you accidentally drop the p
Read more