search

trinodb / trino-python-client

Python client for Trino
Apache License 2.0
328 stars 163 forks source link

getting 502 bad gateway responses #303

Closed suresh7277 closed 1 year ago

suresh7277 commented 1 year ago

Expected behavior

Able to write dataframe

Actual behavior

I am reading a csv file in dataframe n writing it in bulk with chunk_size and method= multi, While doing so trino is throwing 502 bad gateway for some of the csv files. but when i try to write dataframes which failed without chunck_size and method=None its working fine.

Steps To Reproduce

Attaching csv file here 01055.csv

Code

from trino.dbapi import connect from trino.auth import BasicAuthentication import pandas as pd from sqlalchemy import create_engine import datetime

trino_connection = connect( host=host, port=port, verify=False, auth=BasicAuthentication(user_id, pswd), http_scheme="https", catalog="iceberg", schema=schema, )

engine = create_engine(trino_uri), connect_args={'verify': False}) sqlalchemy_connection = engine.connect()

monthly_col_name = ['year', 'month', 'tavg', 'tmin', 'tmax', 'prcp', 'wspd', 'pres', 'tsun'] data = pd.read_csv('01055.csv', parse_dates={'Date': ['year', 'month']}, names=monthly_col_name)

current_time = datetime.datetime.now() current_time = [current_time for x in range(0, input_table.shape[0])] rec_counts = [x for x in range(0, input_table.shape[0])]

data.insert(loc=0, column='pull_datetime.datetime'.upper(), value=current_time) data.insert(loc=1, column='row_unique_id'.upper(), value=rec_counts) data.insert(loc=2, column='station_id', value=['01055' for x in range(0, data.shape[0])])

data.to_sql(name=tablename, con=sqlalchemy_connection, schema=schema, if_exists="append", method='multi', chunksize=500, index=False)

Log output

Error closing cursor Traceback (most recent call last): File "/home/suresh/Workspace/.pythonProjects/my-project/venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1901, in _execute_context self.dialect.do_execute( File "/home/suresh/Workspace/.pythonProjects/my-project/venv/lib/python3.9/site-packages/trino/sqlalchemy/dialect.py", line 365, in do_execute cursor.execute(statement, parameters) File "/home/suresh/Workspace/.pythonProjects/my-project/venv/lib/python3.9/site-packages/trino/dbapi.py", line 415, in execute self._prepare_statement(operation, statement_name) File "/home/suresh/Workspace/.pythonProjects/my-project/venv/lib/python3.9/site-packages/trino/dbapi.py", line 315, in _prepare_statement query.execute() File "/home/suresh/Workspace/.pythonProjects/my-project/venv/lib/python3.9/site-packages/trino/client.py", line 759, in execute self._result.rows += self.fetch() File "/home/suresh/Workspace/.pythonProjects/my-project/venv/lib/python3.9/site-packages/trino/client.py", line 774, in fetch status = self._request.process(response) File "/home/suresh/Workspace/.pythonProjects/my-project/venv/lib/python3.9/site-packages/trino/client.py", line 568, in process self.raise_response_error(http_response) File "/home/suresh/Workspace/.pythonProjects/my-project/venv/lib/python3.9/site-packages/trino/client.py", line 551, in raise_response_error raise exceptions.Http502Error("error 502: bad gateway") trino.exceptions.Http502Error: error 502: bad gateway

During handling of the above exception, another exception occurred:

Traceback (most recent call last): File "/home/suresh/Workspace/.pythonProjects/my-project/venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1996, in _safe_close_cursor cursor.close() File "/home/suresh/Workspace/.pythonProjects/my-project/venv/lib/python3.9/site-packages/trino/dbapi.py", line 529, in close self.cancel() File "/home/suresh/Workspace/.pythonProjects/my-project/venv/lib/python3.9/site-packages/trino/dbapi.py", line 523, in cancel raise trino.exceptions.OperationalError( trino.exceptions.OperationalError: Cancel query failed; no running query Traceback (most recent call last): File "/home/suresh/Workspace/my-project/connectors/connector/app.py", line 362, in connector.main() File "/home/suresh/Workspace/my-project/libs/ConnectorHelper.py", line 277, in main raise err File "/home/suresh/Workspace/my-project/libs/ConnectorHelper.py", line 266, in main self.run_batch() File "/home/suresh/Workspace/my-project/connectors/connector/app.py", line 358, in run_batch self.run_connector() File "/home/suresh/Workspace/my-project/connectors/connector/app.py", line 352, in run_connector raise e File "/home/suresh/Workspace/my-project/connectors/connector/app.py", line 341, in run_connector self.get_monthly_weather_data() File "/home/suresh/Workspace/my-project/connectors/connector/app.py", line 168, in get_monthly_weather_data raise e File "/home/suresh/Workspace/my-project/connectors/connector/app.py", line 161, in get_monthly_weather_data raise e File "/home/suresh/Workspace/my-project/connectors/connector/app.py", line 148, in get_monthly_weather_data self.write_to_lakehouse(mode='INSERT', data=weather_monthly_table, File "/home/suresh/Workspace/my-project/libs/ConnectorHelper.py", line 97, in write_to_lakehouse self.lakehouse_writer.write_table(table_name=self.dataset_properties['title'], File "/home/suresh/Workspace/my-project/libs/writer.py", line 384, in write_table data.to_sql(name=table_name, File "/home/suresh/Workspace/.pythonProjects/my-project/venv/lib/python3.9/site-packages/pandas/core/generic.py", line 2987, in to_sql return sql.to_sql( File "/home/suresh/Workspace/.pythonProjects/my-project/venv/lib/python3.9/site-packages/pandas/io/sql.py", line 695, in to_sql return pandas_sql.to_sql( File "/home/suresh/Workspace/.pythonProjects/my-project/venv/lib/python3.9/site-packages/pandas/io/sql.py", line 1740, in to_sql total_inserted = sql_engine.insert_records( File "/home/suresh/Workspace/.pythonProjects/my-project/venv/lib/python3.9/site-packages/pandas/io/sql.py", line 1327, in insert_records return table.insert(chunksize=chunksize, method=method) File "/home/suresh/Workspace/.pythonProjects/my-project/venv/lib/python3.9/site-packages/pandas/io/sql.py", line 948, in insert num_inserted = exec_insert(conn, keys, chunk_iter) File "/home/suresh/Workspace/.pythonProjects/my-project/venv/lib/python3.9/site-packages/pandas/io/sql.py", line 871, in _execute_insert_multi result = conn.execute(stmt) File "/home/suresh/Workspace/.pythonProjects/my-project/venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1380, in execute return meth(self, multiparams, params, _EMPTY_EXECUTION_OPTS) File "/home/suresh/Workspace/.pythonProjects/my-project/venv/lib/python3.9/site-packages/sqlalchemy/sql/elements.py", line 334, in _execute_on_connection return connection._execute_clauseelement( File "/home/suresh/Workspace/.pythonProjects/my-project/venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1572, in _execute_clauseelement ret = self._execute_context( File "/home/suresh/Workspace/.pythonProjects/my-project/venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1944, in _execute_context self._handle_dbapi_exception( File "/home/suresh/Workspace/.pythonProjects/my-project/venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 2129, in _handle_dbapiexception util.raise(exc_info[1], with_traceback=excinfo[2]) File "/home/suresh/Workspace/.pythonProjects/my-project/venv/lib/python3.9/site-packages/sqlalchemy/util/compat.py", line 210, in raise raise exception File "/home/suresh/Workspace/.pythonProjects/my-project/venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1901, in _execute_context self.dialect.do_execute( File "/home/suresh/Workspace/.pythonProjects/my-project/venv/lib/python3.9/site-packages/trino/sqlalchemy/dialect.py", line 365, in do_execute cursor.execute(statement, parameters) File "/home/suresh/Workspace/.pythonProjects/my-project/venv/lib/python3.9/site-packages/trino/dbapi.py", line 415, in execute self._prepare_statement(operation, statement_name) File "/home/suresh/Workspace/.pythonProjects/my-project/venv/lib/python3.9/site-packages/trino/dbapi.py", line 315, in _prepare_statement query.execute() File "/home/suresh/Workspace/.pythonProjects/my-project/venv/lib/python3.9/site-packages/trino/client.py", line 759, in execute self._result.rows += self.fetch() File "/home/suresh/Workspace/.pythonProjects/my-project/venv/lib/python3.9/site-packages/trino/client.py", line 774, in fetch status = self._request.process(response) File "/home/suresh/Workspace/.pythonProjects/my-project/venv/lib/python3.9/site-packages/trino/client.py", line 568, in process self.raise_response_error(http_response) File "/home/suresh/Workspace/.pythonProjects/my-project/venv/lib/python3.9/site-packages/trino/client.py", line 551, in raise_response_error raise exceptions.Http502Error("error 502: bad gateway") trino.exceptions.Http502Error: error 502: bad gateway

Operating System

NAME="Pop!_OS" VERSION="22.04 LTS"

Trino Python client version

0.319.0

Trino Server version

403

Python version

python 3.7

Are you willing to submit PR?

mdesmet commented 1 year ago

This is not a client issue. You should investigate why you're getting a 502 error.

hashhar commented 1 year ago

@suresh7277 Feel free to re-open if this is still a issue. If you have been to solve it it'd be helpful for future visitors if you can leave a comment about what the solution was.