Invalid SessionHandle

[Mzekri-madar]

Hello,

I'm using pyhive with sqlAlchemy in my web application to to fetch data from hive, at first my queries seem to work just fine but after I leave it for a while and then try to run a query again I get the following error "Internal server error (pyhive.exc.OperationalError) TExecuteStatementResp(status=TStatus(statusCode=3, infoMessages=['*org.apache.hive.service.cli.HiveSQLException: Invalid SessionHandle: SessionHandle [2ad56f2d-ff50-4266-885f-599341ca56b5]:12:11', 'org.apache.hive.service.cli.session.SessionManager:getSession:SessionManager.java:475',

Here is a sample of my code

from sqlalchemy import create_engine
import backend.settings as S

n_engine = create_engine(
    f"hive://{S.HIVE_SERVER}:{S.HIVE_PORT}/{S.HIVE_DB_NLP}",
    connect_args={"auth": S.HIVE_AUTH},
)

from core.databases import n_engine

with n_engine.connect() as conn:
    query = """
        SELECT 
            *
        FROM most_frequent_words 
        WHERE 1 = 1 """
    result_set = conn.execute(query)
    result = result_set.mappings().all()

Is this some sort of a timeout error or am I doing something wrong here

[superg28-dm]

Same here at the moment, can't get any connection going @Mzekri-madar have you found a solution

[Mzekri-madar]

@superg28-dm I have switched the connector from pyhive to impyla and everything works for the moment.

[superg28-dm]

Was hoping not to have to do that, will test it out

[kotofos]

The problem seems to be that pyhive does not detect stale sessions and does not recreate them. A workaround would be to force connection close/invalidate.

I'm using sqlalchemy sessions, and in my case next helped:

from sqlalchemy.exc import DBAPIError

try:
  session.execute(q)
catch DBAPIError as e:
  # note usually exception has connection_invalidated attribute, but not in pyhive
  session.invalidate()
  session.rollback()
  
# and then retry
session.execute(q)

[kotofos]

Another way is to patch missing .is_disconnect on the dialect

    def is_disconnect(self, e, connection, cursor):
        if not isinstance(e, self.dbapi.DatabaseError):
            return True

        try:
            error_msg = e.args[0].status.errorMessage
        except (AttributeError, IndexError):
            return False

        if 'Invalid SessionHandle: SessionHandle' in error_msg:
            return True

        return False

    DatabricksDialectBase.is_disconnect = is_disconnect