Best practice when inserting data to existing table via impyla

[IamGianluca]

Hi,

I'm using impyla for my project. Specifically I'm using it to read data using the Impala engine and writing data using HIve. The only difference (aside from minor syntax) is the port I have to provide when connecting to the cluster.

I need to write new rows into an existing partitioned table using Hive. My first thought was to use the executemany command and pass to it a string containing the query template and a tuple of tuples with all the data I want to add. Since I was struggling with implementing such method, I did a research and bumped into #96 . There people say that using the executemany is not the best practice for adding new rows into an existing table. They suggest instead to use ibis or writing the data into HDFS and then register the tables with a CREATE statements. I'm not sure the second suggestion would apply for adding data into a tables that already exist, anyway I was wondering if using impyla plus Hive engine for the INSERT INTO table is still discouraged.

What is the suggested way for inserting data into an existing Hive table?

Also what's wrong with my code below?

# prepare query 
yesterday = datetime.date.today() - datetime.timedelta(days=1)
query = """
             SET hive.exec.dynamic.partition.mode=nonstrict
             INSERT INTO db_name.table_name
             PARTITION (year, month, day)
             VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s);
             """
log_datetime = datetime.datetime.now()
rows = tuple([(str(yesterday), "NULL",
                      str(i.campaign), str(i.adgroup),
                      str(i.id), '1', '1', str(yesterday.year),
                      str(yesterday.month), str(yesterday.day))
                     for i in target.values()])

conn = connect(host=hostname, port=port, auth_mechanism="PLAIN")
cursor = conn.cursor()
cursor.executemany(query, rows)

The program runs without returning any error message but I cannot see the new rows added to the table in Hive.

[chandlervan]

i meet the same problem......it kills me ...

[timarmstrong]

@chandlervan can you provide info about the hive and Impyla version you're using. And ideally a python script that reproduces the problem.

I don't think we intended Impyla to work as a Hive client, but it seems like people have been using it that way! Would be good to understand what is happening - it's entirely possible that the issue also affects Impala connections in a different way.

[chandlervan]

@timarmstrong hi,it seemd to be my ignorance that i didn't notice there is a configuration in the cursor().execute() method, when i finish my config correctly,my sql worked. my workmate check the log and told me that it is about the queue setting that makes my sql failed..so it had nothing to do with the wonderful pakage:impyla~~~~thx for your reply!!

[timarmstrong]

@chandlervan no problem, glad to hear you got unblocked :)

[jeff303]

@timarmstrong hi,it seemd to be my ignorance that i didn't notice there is a configuration in the cursor().execute() method, when i finish my config correctly,my sql worked. my workmate check the log and told me that it is about the queue setting that makes my sql failed..so it had nothing to do with the wonderful pakage:impyla~~~~thx for your reply!!

Would you mind sharing more details about this configuration that you needed to change?