Skip to main content

Minus query in HIVE






It does not appear that HQL supports the MINUS operator

What you want to do can be done with a LEFT JOIN or NOT EXISTS:

SELECT x
FROM abc
LEFT JOIN bcd
ON abc.x = bcd.x
WHERE bcd.x IS NULL
EDIT: Per comments below, NOT EXISTS is not supported.

SELECT x
FROM abc
WHERE NOT EXISTS (SELECT x FROM bcd)

Comments

Post a Comment

Popular posts from this blog

INCREMENTAL UPDATES IN HIVE TABLES

  In this blog I will focus on  Incremental load OR updating the exiting records and inserting new record  and  dynamic partition table loading . In BI world delta load/incremental load is very common activity.       We can implement the same on HIVE table using below steps. In a relation database we will perform updating the exiting records using unique index/primary key, but in Hive we have to do it in different way.  There are many ways to do it, let us see how we can do it using below steps. I am assuming that you have data in HDFS which will be updating hourly/daily from your sqoop/flume/kafka. Design consideration:   I am assuming that good temp space is maintained according to your data volume, so that we not facing temp space issue during the process. I am assuming that you have ingestion logic set to in your code for full load and incremental loads and data is available in HDFS.  Partition column value s...
3 comments
Read more

How to enable SQL Standard-Based Authorization in Hive

How to enable SQL Standard-Based Authorization in Hive Env: Hive 0.13 Reference: SQL Standard Based Hive Authorization 1. Set below parameters in hive-site.xml <property>     <name>hive.server2.enable.doAs</name>     <value>false</value> </property> <property>     <name>hive.users.in.admin.role</name>     <value>mapr</value> </property> <property>     <name>hive.security.authorization.manager</name>     <value>org.apache.hadoop.hive.ql.security.authorization.plugin.sqlstd.SQLStdHiveAuthorizerFactory</value> </property> <property>     <name>hive.security.authorization.enabled</name>     <value>true</value> </property> <property>     <name>hive.security.authenticator.manager</name>     <value>or...
Post a Comment
Read more