This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
Has anyone been able to INSERT into a partitioned Hive table via Alteryx?
Been struggling with this for a while. Hive query syntax requires that you specify the name(s) of the partitioned column(s) when you insert into the partitioned table, so "Write Data In-DB" obviously fails.
Therefore, I've been trying to run an INSERT query via the "Dynamic Input In-DB" tool. However, while my INSERT runs properly in Squirrel, the same query fails when I run it in using the Dynamic Input In-DB tool. For example:
INSERT INTO myschema.test_p PARTITION (date_col = '2018-01-01') VALUES (201);
This INSERT works in Squirrel, but in Alteryx it fails with: "Dynamic Input In-DB (24) Error SQLPrepare: [Simba][SQLEngine] (31740) Table or view not found: HIVE.INSERT INTO myschema.test_p PARTITION (date_col = '2018-01-01') VALUES (201);"
I am trying to insert into a partitioned table in HIVE using the Output Data tool using an ODBC connection. I have used both the Cloudera Hive ODBC and the Simba Hive ODBC drivers.
I am getting the following error for both drivers:
DataWrap2ODBC::SendBatch: [Cloudera][Hardy] (80) Syntax or semantic analysis error thrown in server while executing query. Error message from server: Error while compiling statement: FAILED: SemanticException 1:18 Need to specify partition columns because the destination table is partitioned. Error encountered near token 'mm2_claim_dataload_vl_test' Insert into mm2_claim_dataload_vl_test(intrnl_clm_nbr,inv_prd,ndc,rx_nbr,submtd_util_qty,test1,test2,test3,test4,test5,load_id) Values (?,?,?,?,?,?,?,?,?,?,?)
So that means, if I am reading 1000 records from another database and want to insert them into my partitioned HIVE table I will need to create a process that will create 1000 individual insert statements speicifying specific partition values and data values?