CODEWITHSUNDEEP
pythonpyodbcimpala
Sanchez333
Sanchez333

Reputation: 408

Can't insert to impala via pyodbc due to 'unsupported query' error

I have an insert query which works via hue but when performed via pyodc receives the error:

[CLOUDERAODBC] (140) Unsupported Query.

The query itself is stored in an iql file and read into python for execution

INSERT INTO db_name_tbl_name
SELECT DISTINCT field_name, now() 
FROM db_name.tbl_name 
WHERE field_name not in (SELECT field_name from someothertable)

python:

 with open('file/location.iql') as script:
    statement = script.read()
    print(statement)
    crsr = conn.cursor()
    crsr.execute(statement)

Upvotes: 1

Views: 235

Answers (1)

Sanchez333
Sanchez333

Reputation: 408

The problem is the DISTINCT part.

For some reason the driver does not support performing a distinct query after an insert via pyodbc. This is the case for other query complexities raised during an insert as well. Your query will work in hue but is not supported via the cloudera odbc driver.

As a workaround you can do any complex parts of the query using a CTE and then do the insert, as follows:

WITH result_set AS (
  SELECT DISTINCT field_name
  FROM db_name.tbl_name
  WHERE field_name not in (SELECT field_name FROM someothertable)

INSERT INTO db_name.tbl_name
SELECT field_name, now() FROM result_set
;

I hope this helps somebody!

Upvotes: 1

Related Questions