I created an external table in Hive with partitions pointing to a HDFS location. I am using NiFi to load csv files into that location by using putHDFS. and then i am using SelectHiveQL to execute the ALTER TABLE command. It is successfully able to load the partitions as i can query them from Hive, but its routing to failures as the ALTER TABLE is not producing any output.
Here is the error..
SelectHiveQL[id=7c2b4a39-f772-1ea7-7e5b-48be29a34850] Unable to execute HiveQL select query ALTER TABLE hdf_moat.tbl_moat_display_sai ADD PARTITION (MOATDATE=20150412) LOCATION '/user/putarapasa/MOAT_Daily/20150412/' for StandardFlowFileRecord[uuid=9781e831-a494-4512-8121-7bed28908763,claim=StandardContentClaim [resourceClaim=StandardResourceClaim[id=1485365330956-237, container=default, section=237], offset=0, length=50994078],offset=0,name=GroupM_Nestle_Daily_20150412.csv,size=50994078] due to org.apache.nifi.processor.exception.ProcessException: java.sql.SQLException: The query did not generate a result set!; routing to failure: org.apache.nifi.processor.exception.ProcessException: java.sql.SQLException: The query did not generate a result set!
Correct, SelectHiveQL is for statements that return ResultSets (like SELECT *), those results are converted to Avro records. PutHiveQL is for executing statements (except callable statements like stored procedures) that do not return results, such as your ALTER TABLE example.