SQLAlchemy support for Impala on Python 3.11 - incorrect SQL code : concatenation of database, table and column names

[radurogojanumai]

Hi,

I'm encountering issues when using the impala protocol with impyla and SQLAlchemy to read from a table in Hive. It seems the SQL query generated by SQLAlchemy includes incorrect concatenation of database, table, and column names, resulting in an invalid SQL statement

Server Details:
Operating System: Ubuntu 24.04 LTS
Hive Version: 3.1.3 r4df4d75bf1e16fe0af75aad0b4179c34c07fc975
Kerberos Version: 1.20.1-6ubuntu2
SSL/TLS: OpenSSL 3.0.13 (30 Jan 2024)

Client Details:
Operating System: Red Hat Enterprise Linux 9.4 (Plow)
Python Version: 3.11.7
Impyla Version: 0.19.0 (22nd Nov 2023) and 0.20a1 (pre-release from 13th of March)
SQLAlchemy Version: 1.4.52

Related impyla commit on 15th of April: d6582ad

Error encountered in our tests, eg when using ssl:
FAILED [100%]
tests/integration/mostly_data/core/db/test_hive.py:20 (test_read[True])
self = <sqlalchemy.engine.base.Connection object at 0x17b8685d0>
dialect = <impala.sqlalchemy.ImpalaDialect object at 0x16f38bb50>
constructor = <bound method DefaultExecutionContext._init_compiled of <class 'impala.sqlalchemy.ImpalaExecutionContext'>>
statement = 'SELECT test.customer.customer.id, test.customer.customer.name, test.customer.customer.surname, test.customer.c...tomer_type_id, test.customer.customer.creation_user_id, test.customer.customer.creation_date \nFROM test.customer'
parameters = {}, execution_options = immutabledict({})
args = (<sqlalchemy.sql.compiler.SQLCompiler object at 0x16f431790>, [], <sqlalchemy.sql.selectable.Select object at 0x17d830590>, None)
kw = {'cache_hit': symbol('NO_DIALECT_SUPPORT')}
branched = <sqlalchemy.engine.base.Connection object at 0x17b8685d0>
conn = <sqlalchemy.pool.base._ConnectionFairy object at 0x16f38e7d0>
context = <impala.sqlalchemy.ImpalaExecutionContext object at 0x16f431210>
cursor = <impala.hiveserver2.HiveServer2Cursor object at 0x16f389090>
evt_handled = False

def _execute_context(
self,
dialect,
constructor,
statement,
parameters,
execution_options,
*args,
**kw
):
...

---

resp = TExecuteStatementResp(status=TStatus(statusCode=3, infoMessages=["*org.apache.hive.service.cli.HiveSQLException:Error ...surname, gender_id, district_id, birthdate, customer_type_id, creation_user_id, creation_date)"), operationHandle=None)

def err_if_rpc_not_ok(resp):
if (resp.status.statusCode != TStatusCode.SUCCESS_STATUS and
resp.status.statusCode != TStatusCode.SUCCESS_WITH_INFO_STATUS and
resp.status.statusCode != TStatusCode.STILL_EXECUTING_STATUS):
raise HiveServer2Error(resp.status.errorMessage)
sqlalchemy.exc.DBAPIError: (impala.error.HiveServer2Error) Error while compiling statement: FAILED: SemanticException [Error 10004]: Line 1:7 Invalid table alias or column reference 'test': (possible column names are: id, name, surname, gender_id, district_id, birthdate, customer_type_id, creation_user_id, creation_date)
[SQL: SELECT test.customer.customer.id, test.customer.customer.name, test.customer.customer.surname, test.customer.customer.gender_id, test.customer.customer.district_id, test.customer.customer.birthdate, test.customer.customer.customer_type_id, test.customer.customer.creation_user_id, test.customer.customer.creation_date
FROM test.customer]
(Background on this error at: https://sqlalche.me/e/14/dbapi)

/Users/usename/miniconda3/envs/mostly-core/lib/python3.11/site-packages/impala/hiveserver2.py:847: DBAPIError

Please let me know if further information is needed.
Thank you!