@Kit Menke - Hey Kit , I have requirement where the users need to execute a query using beeline from hdfs.I tried your approach however i have tried several versions of it and the outcome unfortunately contradicts your posts.Can beeline access hdfs uri?? It would be great help if you could share your thoughts on this. beeline -u "jdbc:hive2://namenode2.dc.corp.astro.com:2181,namenode1.dc.corp.astro.com:2181,namenode3.dc.corp.astro.com:2181/;serviceDiscoveryMode=zooKeeper;zooKeeperNameSpace=hiveserver2sockettimeout=600000;tcpKeepAlive=true" -n xxx -p ******* -f "City.sql" --verbose true --hivevar HDFSDIR="hdfs://namenode1.dc.corp.astro.com:8020/user/xxx" ############ OUTPUT ########################## Connected to: Apache Hive (version 1.2.1000.2.6.0.3-8) Driver: Hive JDBC (version 1.2.1000.2.6.0.3-8) Transaction isolation: TRANSACTION_REPEATABLE_READ City.sql (No such file or directory) ############################################## Option 2: beeline -u "jdbc:hive2://namenode2.dc.corp.astro.com:2181,namenode1.dc.corp.astro.com:2181,namenode3.dc.corp.astro.com:2181/;serviceDiscoveryMode=zooKeeper;zooKeeperNameSpace=hiveserver2sockettimeout=600000;tcpKeepAlive=true" -n xxx -p ******* -f "hdfs://namenode1.dc.corp.astro.com:8020/user/xxx/City.sql" --verbose true ############ OUTPUT ########################## Connected to: Apache Hive (version 1.2.1000.2.6.0.3-8) Driver: Hive JDBC (version 1.2.1000.2.6.0.3-8) Transaction isolation: TRANSACTION_REPEATABLE_READ City.sql (No such file or directory) ############################################## Option 3: beeline -u "jdbc:hive2://namenode2.dc.corp.astro.com:2181,namenode1.dc.corp.astro.com:2181,namenode3.dc.corp.astro.com:2181/;serviceDiscoveryMode=zooKeeper;zooKeeperNameSpace=hiveserver2sockettimeout=600000;tcpKeepAlive=true" -n xxx -p ******* -f "hdfs://user/xxx/City.sql" --verbose true ############ OUTPUT ########################## Connected to: Apache Hive (version 1.2.1000.2.6.0.3-8) Driver: Hive JDBC (version 1.2.1000.2.6.0.3-8) Transaction isolation: TRANSACTION_REPEATABLE_READ City.sql (No such file or directory) ############################################## ... View more

@Simran Kaur Hive CSV serde is built on Open CSV which support all basic csv type operations. Basically, to design the serde based on the type of data within the csv file. Refer to below link for details: http://opencsv.sourceforge.net/ ... View more

OK. That's good. I have not seen any specific issue with NOT IN. Without seeing the data it stands to be asked again - You have confirmed that (a) count(*) on the inner query returns results, (b) that it is not failing silently, and (c) that there are 1+ email addresses in June do not exist prior to June... Here are 2 things to check to see if it is indeed a Hive issue... (1) Try running the query once with MapReduce as the execution engine and then with Tez as the execution engine and see if you get differing results. set hive.execution.engine=mr; set hive.execution.engine=tez; (2) Change the query to use a temporary table and evaluate your results that way (double-check my syntax but it should be close to correct... create temporary table temp_table as select customer_email FROM Table1 WHERE TO_DATE(created_at) < '2016-05-31'; select DISTINCT SF.customer_email FROM Magento.sales_flat_order SF WHERE YEAR(TO_DATE(SF.created_at)) = '2016' AND MONTH(TO_DATE(SF.created_at)) = '6' AND SF.customer_email NOT IN (select customer_email from temp_table); select DISTINCT SF.customer_email FROM Magento.sales_flat_order SF left join temp_table on SF.customer_email = temp_table.customer_email WHERE YEAR(TO_DATE(SF.created_at)) = '2016' AND MONTH(TO_DATE(SF.created_at)) = '6' AND temp_table.customer_email is NULL ... View more

@Simran Kaur If you have a Hive metastore associated with your HDFS cluster, --hive-import and -hive-overwrite always writes to Hive warehouse directory. Arguments like --warehouse-dir <dir>, --as-avrodatafile, --as-sequencefile, --target-dir etc. are not honoured. Thanks and Regards, Sindhu ... View more

Yes, use UpdateAttribute and the expression language to add missing values as appropriate. ... View more