Apache HIVE

In the previous post, we discussed about HIVE transactional tables; how to create it, properties and configurations required and example of HIVE transactional table DDL with INSERT/UPDATE/DELETE/MERGE statements on HIVE ACID table. In this post, we will talk about limitations with HIVE transactional tables and possible solutions if any. [Error 10302]: Updating values of bucketing […]

We all know HDFS does not support random deletes, updates. With HIVE ACID properties enabled, we can directly run UPDATE/DELETE on HIVE tables. Hive ACID tables support UPDATE, DELETE, INSERT, MERGE query constructs with some limitations and we will talk about that too. I am using HDP 2.6 & Hive 1.2 for examples mentioned below.

BETWEEN operator in HIVE When we have to check if column value lies between a range of value then we use BETWEEN operator in the query. You have to specify MIN and MAX value for the range when using BETWEEN operator. Example: Be very careful when specifying the range in between clause. If by mistake

IN operator is used to compare any column value in the table with multiple possible values. It returns TRUE if value is matching with any value listed inside IN operator. Example: select pres_name,pres_dob,pres_bs from usa_president where pres_bs IN (‘Virginia’,’New York’,’New Jersey’); +————————-+————-+————-+–+ | pres_name | pres_dob | pres_bs | +————————-+————-+————-+–+ | George Washington | 1732-02-22

WHERE clause in HIVE WHERE clause is used to FILTER out records from final result set. When you run any query in HIVE and you want to FILTER out records such that all the rows in output shall abide by some condition then use WHERE clause. You can mention conditions in WHERE which must be