Impala query over odbc canceled (don't know the reason)

[#1]

Hi Team,

I am facing a weird issue, I am connecting to impala from pypyodbc library and not able to execute the insert queries. The moment I execute the "cursor.execute("insert into ........")" it executes successfully with no error/exception but when I checked in coordinator the query is getting cancelled with no reason,

Specifications:

Python = 3.7.3

pypyodbc = 1.3.4

impala = 3.2.0-cdh6.3.3

I0930 01:35:20.687757 552509 coordinator.cc:361] 3f46242d529596a8:c9dd8a3500000000] starting execution on 1 backends for query_id=3f46242d529596a8:c9dd8a3500000000
I0930 01:35:20.688199 105383 impala-internal-service.cc:50] 3f46242d529596a8:c9dd8a3500000000] ExecQueryFInstances(): query_id=3f46242d529596a8:c9dd8a3500000000 coord=some_ip.mastercard.int:port #instances=1
I0930 01:35:20.688211 549896 impala-hs2-server.cc:705] CloseOperation(): query_id=3f46242d529596a8:c9dd8a3500000000
I0930 01:35:20.688225 549896 impala-server.cc:1164] UnregisterQuery(): query_id=3f46242d529596a8:c9dd8a3500000000
I0930 01:35:20.688230 549896 impala-server.cc:1271] Cancel(): query_id=3f46242d529596a8:c9dd8a3500000000
I0930 01:35:20.688603 552509 coordinator.cc:375] 3f46242d529596a8:c9dd8a3500000000] started execution on 1 backends for query_id=3f46242d529596a8:c9dd8a3500000000
I0930 01:35:20.688632 552512 query-state.cc:642] 3f46242d529596a8:c9dd8a3500000000] Executing instance. instance_id=3f46242d529596a8:c9dd8a3500000000 fragment_idx=0 per_fragment_instance_idx=0 coord_state_idx=0 #in-flight=1
I0930 01:35:20.689502 552509 client-request-state.cc:545] 3f46242d529596a8:c9dd8a3500000000] Cancelled right after starting the coordinator query id=3f46242d529596a8:c9dd8a3500000000
I0930 01:35:20.689520 552509 coordinator.cc:488] 3f46242d529596a8:c9dd8a3500000000] ExecState: query id=3f46242d529596a8:c9dd8a3500000000 execution cancelled
I0930 01:35:20.689545 552509 coordinator-backend-state.cc:445] 3f46242d529596a8:c9dd8a3500000000] Sending CancelQueryFInstances rpc for query_id=3f46242d529596a8:c9dd8a3500000000 backend=10.158.176.127:27000
I0930 01:35:20.689965 98671 control-service.cc:169] CancelQueryFInstances(): query_id=3f46242d529596a8:c9dd8a3500000000
I0930 01:35:20.689976 98671 query-exec-mgr.cc:97] QueryState: query_id=3f46242d529596a8:c9dd8a3500000000 refcnt=4
I0930 01:35:20.689981 98671 query-state.cc:667] Cancel: query_id=3f46242d529596a8:c9dd8a3500000000
I0930 01:35:20.689988 98671 krpc-data-stream-mgr.cc:326] cancelling all streams for fragment_instance_id=3f46242d529596a8:c9dd8a3500000000
I0930 01:35:20.690135 552509 coordinator.cc:691] 3f46242d529596a8:c9dd8a3500000000] CancelBackends() query_id=3f46242d529596a8:c9dd8a3500000000, tried to cancel 1 backends
I0930 01:35:20.690142 552509 coordinator.cc:863] 3f46242d529596a8:c9dd8a3500000000] Release admission control resources for query_id=3f46242d529596a8:c9dd8a3500000000
I0930 01:35:20.847793 552512 query-state.cc:650] 3f46242d529596a8:c9dd8a3500000000] Instance completed. instance_id=3f46242d529596a8:c9dd8a3500000000 #in-flight=0 status=OK
I0930 01:35:20.847918 552511 query-state.cc:290] 3f46242d529596a8:c9dd8a3500000000] UpdateBackendExecState(): last report for 3f46242d529596a8:c9dd8a3500000000
I0930 01:35:20.848443 98671 control-service.cc:126] ReportExecStatus(): Received report for unknown query ID (probably closed or cancelled): 3f46242d529596a8:c9dd8a3500000000 remote host=some_ip:port
I0930 01:35:20.848542 552511 query-state.cc:449] 3f46242d529596a8:c9dd8a3500000000] Cancelling fragment instances as directed by the coordinator. Returned status: ReportExecStatus(): Received report for unknown query ID (probably closed or cancelled): 3f46242d529596a8:c9dd8a3500000000 remote host=some_ip:port
I0930 01:35:20.848554 552511 query-state.cc:667] 3f46242d529596a8:c9dd8a3500000000] Cancel: query_id=3f46242d529596a8:c9dd8a3500000000
I0930 01:35:20.848667 552511 query-exec-mgr.cc:184] 3f46242d529596a8:c9dd8a3500000000] ReleaseQueryState(): deleted query_id=3f46242d529596a8:c9dd8a3500000000 

[#2]

The client (i.e. the ODBC driver being used by your pyodbc program) is closing the insert operation before it finishes. I.e. it's starting the insert query, then closing the insert query before it's finished.

I don't know pyodbc well, but I wonder if it's something to do with how it's being used here. The examples I see either commit or fetch rows after execute().

I'd suggest trying either of those things (calling commit() or fetching from the cursor) to see if it forces your program to wait for the insert to succeed..

https://github.com/mkleehammer/pyodbc/wiki/Getting-started

[#3]

Tried executing commit() or setting the timeout but no effect:

import pypyodbc

connection = pypyodbc.connect(DSN="", Schema="dbname", autocommit=True)

cursor = connection.cursor()

query = """INSERT INTO schema.table VALUES ('val1', 'val2')"""

cursor.execute(query)

cursor.commit()

connection.close()