community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
#SANTALYTICS

The highly anticipated Alteryx Community tradition is back! We hope you'll join us!

Learn More
We will be upgrading the Gallery to our 2019.4 release this Saturday, December 7th beginning at 9:00am MT. We expect the outage to take last approx. 2.5 hours.

How to Insert Into Partitioned Hive Table?

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);"

 

Any ideas?

 

Alteryx
Alteryx

@sjfontaine How do the values your Connection Name Field and Query/Query Alias List Field look like? Can you attach a sample workflow?

Digan
Alteryx

Hello,

 

I'm a new alteryx user & was wondering if this has been figured out yet.

 

Currently, i'm using a Write Data In-DB to insert data into a partitioned table.

 

Also, is there a way to run an *insert hive query?

 

cheers!

Meteor

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 (?,?,?,?,?,?,?,?,?,?,?)

 

Any ideas how I can get this to work?

Alteryx
Alteryx

@slickshoes you can use the dynamic input -indb tools to do that. Make sure you have the connection name and the query added before adding the dynamic input-indb tool. 

 

@vlecato1 what that means that the table has a partition in it.  Your query should look something like this:

insert into tabname partition (day='30/03/2017') values (1,"abcd","efgh");
if the partition is done by day

This article talks about it as well. Hope that helps. 

Digan
Alteryx
Meteor

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?

Alteryx
Alteryx

@vlecato1 I don't think we have a native way of writing to partitioned tables. However, you can create a batch macro that will do it for you. See Attached.

Digan
Alteryx
Alteryx
Alteryx

@vlecato1 I don't think we have a native way of writing to partitioned tables. However, you can create a batch macro that will do it for you. See Attached.

Digan
Alteryx
Meteor

Thanks for the sample macro.  However, the data I'm trying to insert is actually coming from SQL Server.  Attached is a sample file from the table I'm reading.  

 

I'm trying to read the data from the file and then insert each row into the following HIVE Table:

 

CREATE TABLE mm2_claim_dataload_vl_test
(intrnl_clm_nbr BIGINT ,
inv_prd VARCHAR(7) ,
ndc VARCHAR(20) ,
rx_nbr VARCHAR(20) ,
submtd_util_qty DECIMAL(19,6) ,
test1 VARCHAR(50) ,
test2 VARCHAR(50) ,
test3 VARCHAR(50) ,
test4 VARCHAR(50) ,
test5 VARCHAR(50)
)
PARTITIONED BY ( load_id INT )

STORED AS PARQUET
TBLPROPERTIES('parquet.compression'='SNAPPY')

 

Any additional help is very welcomed.

Highlighted
Meteoroid

Thanks for the Macro, i was wondering however if we wish to insert the data from another table in hive then how would we go about that?

Labels