are the statistics up to date on the tables? It should be able to do a semi join reduction. Updating stats may help: https://cwiki.apache.org/confluence/display/Hive/Column%2BStatistics%2Bin%2BHive ... View more

From the directory listing, your table must have "transactional=true" property, i.e. it's an ACID table. That means that Insert Overwrite will create a base_x directory where it will put the result of the insert (new data) there. Any data that existed before, will remain in the table but will not be visible to readers that start after Insert Overwrite finished. Old data will be physically removed once Compaction runs over this table/partition. ... View more

The contents of a managed table should be manage by Hive, you cannot just write files there. To do that you have to use External table. To add data to a managed table you should use Insert or Load Data or some other Hive command, but not just drop files there. ... View more

Is this a managed or external table? If managed, do you have any files in the table that don't match 0000_0, 0000_0_copy_1 or bucket_0 pattern? ... View more

Have you considered SQL Merge statement? ... View more

Hive metastore is a service. That may run in embedded mode and in stand alone mode. You can have several instances running in your cluster, all instances must share the same backend RDMBS. This should help: https://cwiki.apache.org/confluence/display/Hive/AdminManual+MetastoreAdmin#AdminManualMetastoreAdmin-RemoteMetastoreServer ... View more

Do you have the standalone metastore running? that is where compaction jobs are actually generated and submitted. ... View more

https://cwiki.apache.org/confluence/display/hive/languagemanual+dml#LanguageManualDML-Merge is designed for this ... View more

hive.merge.cardinality.check=false is a bad idea. The logic controlled by this property checks if the ON clause of your Merge statement is such that more than 1 row from source side matches the same row from target side (which only happens in WHEN MATCHED clause). Logically what this means is that the query is asking the system to update 1 existing row in target in 2 (or more) different ways. This check is actually part of SQL standard definition of how Merge should work. You either need examine your data or the ON clause but disabling this check, when it throws a cardinality_violation error, may lead to data corruption later. ... View more

Have you considered Hive? It can certainly do joins. Since you are trying to do CDC and need to handle updates/deletes, you can create transactional tables in Hive which support SQL Merge which is designed for exactly this. ... View more

hive.support.concurrency property enables locking. When a queries is shutdown its locks should be released immediately. When dies abruptly it may leave locks behind. These will be cleaned up by a background process running from a standalone Hive metastore process. This process will consider locks abandoned if they have not heartbeated for (by default) 5 minutes. Metastore logfile should have entries from AcidHouseKeeperService - that is the clean up process. ... View more

Hive does not support this directly but you can use SQL Merge statement to achieve this: https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DML#LanguageManualDML-Merge ... View more

I would need more context. What exceptions are you getting? It seems unusual that you have modify pom files in an whole platform installation. Are you calling Streaming API from your own app? ... View more

Check metastore log from standalone metastore. It may have more information. ... View more

If your competing read/insert target a single partition this should be safe since Hive uses 'rename' file system operation at the end of insert to make new files visible. Rename is atomic on HDFS. If your insert is a dynamic partition insert then you are writing multiple partitions and the data for each partition is using the 'rename' operation. This means that some read operation could see a set of files that reflects only part of the insert. Insert overwrite actually deletes existing files so this can conflict with a concurrent read. ... View more

could you also post the plan with "hive.explain.user=true" please ... View more

I think for partition pruning to work you have to either something like WHERE license_name=X somewhere for static pruning or src.license_name=target.license_name for dynamic partition pruning. Otherwise there is nothing to to infer a smaller partition set form. ... View more

you can stage your data somewhere and use "Insert into AcidTable Select * from ..." If the data originates in some streaming fashion then Streaming Ingest may be appropriate - this has been integrated with Stork, Flume and NiFi. Load Data statement is not supported in 2.x. ... View more

This may be due to not having https://issues.apache.org/jira/browse/HIVE-10632 in your build. There is some data in internal tables that was not cleaned when tables were dropped which is causing Initiator to try to schedule compactions. ... View more

Have you considered using Merge statement? For example, https://community.hortonworks.com/articles/97113/hive-acid-merge-by-example.html ... View more