SAP
How to read LOB data from HANA tables using SQLAlchemy?
We are using sqlalchemy-hana to migrate data from one source hana system to another. We are not creating tables in target as it already exists. Data is retrieved from source as below,
_from sqlalchemy import create_engine, MetaData engine = create_engine('hana://{0}:{1}@{2}:{3}'.format(username,password,host,port)) Metadata = MetaData(engine, schema=schema) Metadata.reflect(bind=engine)
tableORM = Metadata.tables table = tableORM[schema + '.'+tableName] data = engine.execute(table.select()).fetchall()_
With the above code, data can be fetched from tables which doesn't contain LOB data types. But for tables with LOB, this read is getting stucked. The module hdbcli has LOB class and it can be accessed via table cursor. How the same can be achieved in SQLAlchemy? Please give some suggestions.
With the above code, data can be fetched from tables which doesn't contain LOB data types. But for tables with LOB, this read is getting stucked.
By default SQLAlchemy reads every LOB into memory while feting the result from the database. Is you result maybe too big? Could you please try a smaller result through usage of LIMIT? Does it work if you perform the statement with a raw database client like hdbcli?
Hi Jarus, Thanks for the response. I tried to read tables with LOB data types where length of the field is less. ie less than 200 characters, But even that was getting stuck with fetchall() where as fetchmany() was reading first 32 rows. And with fetchone() I was able to read all raws iteratively. When I tried the same with hdbcli, I was able to read the same table. But with hdbcli also, issue exist if the field length is more than 1024 characters. It works with pyhdb. But as we need encrypted connection we are not able to use pyhdb as driver. As I already mentioned we are doing migration from source to target and tried to read similar table from target and it was working with sqlalchemy and also with hdbcli. The difference between the source and target is, a ssh connection is established with source and as 'localhost' sqlalchemy/hdbcli connections are made. Do you see any reason why with ssh this issue is happening? Or any suggestions?
@Nileena SSH errors are not part of sqlalchemy-hana but refer to your setup. Regarding the read errors, I hope that a more modern hdbcli/sqlalchemy/sqlalchemy-hana setup will resolve the issue by it's own. Please wait until 1.0 (https://github.com/SAP/sqlalchemy-hana/issues/102) is released and also update sqlalchemy/hdbcli and of course your HANA version.
If the issue then still occurs, please reopen the issue.
