destination sqlalchemy with Exasol and its sqlachemy-exasol backend does not work in Dlt

[JZ-poc]

dlt version

1.5.0

Describe the problem

I am experiencing multiple issues when using dlt with the SQLAlchemy destination for Exasol. My expectation was that dlt would successfully generate a table for the CSV file (test_data.csv) in Exasol. However, the process encounters multiple failures, including:

• Missing dlt metadata tables, causing dlt to issue a SELECT query on non-existent tables.

• Incorrect table creation SQL, leading to an error due to an unsupported UNIQUE constraint on the _dlt_id column.

• Serialization error (TypeError: Object of type DateTime is not JSON serializable), preventing further execution.

A similar issue has been reported for SAP HANA (Issue #2110), and a fix has been proposed in PR #2213, which might also resolve the problem for Exasol.

Errors Encountered

1. Missing dlt Metadata Tables

• When _dlt_pipeline_state, _dlt_loads, or _dlt_version are missing in Exasol, dlt fails with:

pyexasol.exceptions.ExaQueryError: object POST_COVID."_dlt_pipeline_state" not found

2. Incorrect Table Creation with UNIQUE Constraint

• dlt attempts to create the target table in Exasol with a UNIQUE constraint on _dlt_id, which Exasol does not support:

CREATE TABLE post_covid.test_data (
    id DECIMAL(19), 
    payload TEXT, 
    "_dlt_load_id" TEXT NOT NULL, 
    "_dlt_id" VARCHAR(128) NOT NULL, 
    UNIQUE ("_dlt_id")
)

• This leads to a DatabaseTransientException.

3. TypeError: Object of type DateTime is not JSON serializable

• After working around the missing tables and the UNIQUE constraint, the process fails with:

TypeError: Object of type DateTime is not JSON serializable

Expected behavior

My expectation was that dlt would properly generate a table for the CSV file (test_data.csv) in Exasol. However, the process fails due to multiple errors.
Expected behavior:

• dlt should automatically create required metadata tables (_dlt_pipeline_state, _dlt_loads, _dlt_version) in Exasol.
• The _dlt_id column should be handled without an unsupported UNIQUE constraint.
• dlt should correctly handle DateTime serialization without errors.

Steps to reproduce

1. Configure dlt to use Exasol as the SQLAlchemy destination.

python script test_pipeline.py for executing the pipeline

import dlt
import sqlalchemy as sa
from dlt.sources.filesystem import filesystem, read_csv

filesystem_resource = filesystem(
    bucket_url = "file:///app/test_csv_to_exasol",
    file_glob= "test_data.csv"
)

filesystem_pipe = filesystem_resource | read_csv()

filesystem_pipe = filesystem_pipe.with_name("test_data") #name of the resource will be the table name in the target

pipeline = dlt.pipeline(pipeline_name="my_pipeline", destination = "sqlalchemy", dataset_name="POST_COVID") # dataset_name is the target schema

load_info = pipeline.run(filesystem_pipe)
print(load_info)

The following secrets.toml is used for setting up the sqlalchemy destination

[destination.sqlalchemy]
credentials = "exa+websocket://user:password@host:8563/POST_COVID"

2. Load a simple CSV file (test_data.csv) into Exasol using dlt.

The csv file test_data.csv is used:

ID,PAYLOAD
1,car
2,bike

3. Observe the errors mentioned above.
   just run the pipeline using the command
   python test_pipeline.py

Operating system

Linux

Runtime environment

Docker, Docker Compose

Python version

3.10

dlt data source

verified CSV source:
The csv file test_data.csv is used:

ID,PAYLOAD
1,car
2,bike

dlt destination

No response

Other deployment details

I ran everything in a docker container.

Additional information

A similar issue has been reported for SAP HANA (Issue #2110), and a fix has been proposed in PR #2213, which might also resolve the problem for Exasol.

The following workarounds were executed:
Creating the tables _dlt_loads, _dlt_pipeline_state, _dlt_version, target table test_data:

CREATE OR REPLACE TABLE POST_COVID."_dlt_loads" (
    LOAD_ID             VARCHAR(5000) NOT NULL,
    SCHEMA_NAME         VARCHAR(5000),
    STATUS              INTEGER NOT NULL,
    INSERTED_AT         TIMESTAMP NOT NULL,
    SCHEMA_VERSION_HASH VARCHAR(5000)
);

CREATE OR REPLACE TABLE POST_COVID."_dlt_pipeline_state" (
    VERSION        INTEGER NOT NULL,
    ENGINE_VERSION INTEGER NOT NULL,
    PIPELINE_NAME  VARCHAR(5000) NOT NULL,
    "state"          VARCHAR(5000) NOT NULL,
    CREATED_AT     TIMESTAMP NOT NULL,
    VERSION_HASH   VARCHAR(5000),
    "_dlt_load_id" VARCHAR(5000) NOT NULL,
    "_dlt_id"      VARCHAR(128) NOT NULL
);

CREATE OR REPLACE TABLE POST_COVID."_dlt_version" (
    VERSION        INTEGER NOT NULL,
    ENGINE_VERSION INTEGER NOT NULL,
    INSERTED_AT    TIMESTAMP NOT NULL,
    SCHEMA_NAME    VARCHAR(5000) NOT NULL,
    VERSION_HASH   VARCHAR(5000) NOT NULL,
    "schema"         VARCHAR(2000000) NOT NULL
);

CREATE OR REPLACE TABLE post_covid.test_data (
    id DECIMAL(19),
    payload VARCHAR(2000000),
    "_dlt_load_id" VARCHAR(2000000) NOT NULL,
    "_dlt_id" VARCHAR(128) NOT NULL,
    PRIMARY KEY ("_dlt_id")
);

[rudolfix]

@JZ-poc thx for this report, it was very helpful! regarding the issues

1. hopefully this branch will detect non existing entities correctly: #2249 sqlalchemy indexes off by default #2253
2. destinations should not create any constraints by default. I disabled that on sqlalchemy and made optional (see branch above)
3. I need a full stack trace... this error happens if there's ie. csv parser that generates date times that are not compatible with Python date times and are considered unknown objects. This happened a few times in the past... are you using our stock csv parser? (it uses pandas internally, you could use duckdb csv parser instead and see if problem persists)

[JZ-poc]

@rudolfix : Thank you. Regarding point 3, I exchanged the read_csv with read_csv_duckdb. Same error. Note that the csv file does not contain any dates at all, so dlt internals actually created the error while trying to write data to either _dlt_version, _dlt_pipeline_state, _dlt_loads. Any idea?

Sorry to ask: I can see that your commit was merged to devel branch. How do I see when it is in master/main?

Here the log in DEBUG mode:
`test_csv_to_exasol# python test_pipeline.py
{"written_at":"2025-02-04T08:05:11.459Z","written_ts":1738656311459832000,"component_name":"my_pipeline","process":8,"msg":"The state was not found in the destination sqlalchemy(dlt.destinations.sqlalchemy):POST_COVID","type":"log","logger":"dlt","thread":"MainThread","level":"INFO","module":"pipeline","line_no":1546,"version":{"dlt_version":"1.5.0","pipeline_name":"my_pipeline"}}
{"written_at":"2025-02-04T08:05:11.546Z","written_ts":1738656311546810000,"component_name":"my_pipeline","process":8,"msg":"Closing writer for 1738656311.4801705.my.test_data with file None and actual name /var/dlt/pipelines/my_pipeline/normalize/8ed3c8b3a70102b2/1738656311.4801705/new_jobs/test_data.03a2523a0a.0.typed-jsonl","type":"log","logger":"dlt","thread":"MainThread","level":"DEBUG","module":"data_item_storage","line_no":81,"version":{"dlt_version":"1.5.0","pipeline_name":"my_pipeline"}}
{"written_at":"2025-02-04T08:05:11.568Z","written_ts":1738656311568339000,"component_name":"my_pipeline","process":8,"msg":"Closing writer for 1738656311.4801705.my._dlt_pipeline_state with file None and actual name /var/dlt/pipelines/my_pipeline/normalize/8ed3c8b3a70102b2/1738656311.4801705/new_jobs/_dlt_pipeline_state.364a7ac03b.0.typed-jsonl","type":"log","logger":"dlt","thread":"MainThread","level":"DEBUG","module":"data_item_storage","line_no":81,"version":{"dlt_version":"1.5.0","pipeline_name":"my_pipeline"}}
{"written_at":"2025-02-04T08:05:11.610Z","written_ts":1738656311610537000,"component_name":"my_pipeline","process":8,"msg":"Created none pool with 1 workers","type":"log","logger":"dlt","thread":"MainThread","level":"INFO","module":"pool_runner","line_no":77,"version":{"dlt_version":"1.5.0","pipeline_name":"my_pipeline"}}
{"written_at":"2025-02-04T08:05:11.610Z","written_ts":1738656311610676000,"component_name":"my_pipeline","process":8,"msg":"Running pool","type":"log","logger":"dlt","thread":"MainThread","level":"DEBUG","module":"pool_runner","line_no":90,"version":{"dlt_version":"1.5.0","pipeline_name":"my_pipeline"}}
{"written_at":"2025-02-04T08:05:11.610Z","written_ts":1738656311610790000,"component_name":"my_pipeline","process":8,"msg":"Running file normalizing","type":"log","logger":"dlt","thread":"MainThread","level":"INFO","module":"normalize","line_no":241,"version":{"dlt_version":"1.5.0","pipeline_name":"my_pipeline"}}
{"written_at":"2025-02-04T08:05:11.610Z","written_ts":1738656311610941000,"component_name":"my_pipeline","process":8,"msg":"Found 1 load packages","type":"log","logger":"dlt","thread":"MainThread","level":"INFO","module":"normalize","line_no":244,"version":{"dlt_version":"1.5.0","pipeline_name":"my_pipeline"}}
{"written_at":"2025-02-04T08:05:11.626Z","written_ts":1738656311626564000,"component_name":"my_pipeline","process":8,"msg":"Found 2 files in schema my load_id 1738656311.4801705","type":"log","logger":"dlt","thread":"MainThread","level":"INFO","module":"normalize","line_no":267,"version":{"dlt_version":"1.5.0","pipeline_name":"my_pipeline"}}
{"written_at":"2025-02-04T08:05:11.627Z","written_ts":1738656311627501000,"component_name":"my_pipeline","process":8,"msg":"Created new load package 1738656311.4801705 on loading volume with ","type":"log","logger":"dlt","thread":"MainThread","level":"INFO","module":"normalize","line_no":230,"version":{"dlt_version":"1.5.0","pipeline_name":"my_pipeline"}}
{"written_at":"2025-02-04T08:05:11.632Z","written_ts":1738656311632075000,"component_name":"my_pipeline","process":8,"msg":"Created items normalizer JsonLItemsNormalizer with writer TypedJsonlListWriter for item format object and file format typed-jsonl","type":"log","logger":"dlt","thread":"MainThread","level":"INFO","module":"worker","line_no":170,"version":{"dlt_version":"1.5.0","pipeline_name":"my_pipeline"}}
{"written_at":"2025-02-04T08:05:11.632Z","written_ts":1738656311632159000,"component_name":"my_pipeline","process":8,"msg":"Processing extracted items in 1738656311.4801705/new_jobs/test_data.03a2523a0a.0.typed-jsonl in load_id 1738656311.4801705 with table name test_data and schema my","type":"log","logger":"dlt","thread":"MainThread","level":"DEBUG","module":"worker","line_no":231,"version":{"dlt_version":"1.5.0","pipeline_name":"my_pipeline"}}
{"written_at":"2025-02-04T08:05:11.633Z","written_ts":1738656311633778000,"component_name":"my_pipeline","process":8,"msg":"Processed 1 lines from file 1738656311.4801705/new_jobs/test_data.03a2523a0a.0.typed-jsonl","type":"log","logger":"dlt","thread":"MainThread","level":"DEBUG","module":"items_normalizers","line_no":208,"version":{"dlt_version":"1.5.0","pipeline_name":"my_pipeline"}}
{"written_at":"2025-02-04T08:05:11.633Z","written_ts":1738656311633886000,"component_name":"my_pipeline","process":8,"msg":"Processed file 1738656311.4801705/new_jobs/test_data.03a2523a0a.0.typed-jsonl","type":"log","logger":"dlt","thread":"MainThread","level":"DEBUG","module":"worker","line_no":237,"version":{"dlt_version":"1.5.0","pipeline_name":"my_pipeline"}}
{"written_at":"2025-02-04T08:05:11.633Z","written_ts":1738656311633979000,"component_name":"my_pipeline","process":8,"msg":"A file format for table _dlt_pipeline_state was specified to preferred in the resource so typed-jsonl format being used.","type":"log","logger":"dlt","thread":"MainThread","level":"INFO","module":"worker","line_no":124,"version":{"dlt_version":"1.5.0","pipeline_name":"my_pipeline"}}
{"written_at":"2025-02-04T08:05:11.634Z","written_ts":1738656311634054000,"component_name":"my_pipeline","process":8,"msg":"Created items normalizer JsonLItemsNormalizer with writer TypedJsonlListWriter for item format object and file format typed-jsonl","type":"log","logger":"dlt","thread":"MainThread","level":"INFO","module":"worker","line_no":170,"version":{"dlt_version":"1.5.0","pipeline_name":"my_pipeline"}}
{"written_at":"2025-02-04T08:05:11.634Z","written_ts":1738656311634119000,"component_name":"my_pipeline","process":8,"msg":"Processing extracted items in 1738656311.4801705/new_jobs/_dlt_pipeline_state.364a7ac03b.0.typed-jsonl in load_id 1738656311.4801705 with table name _dlt_pipeline_state and schema my","type":"log","logger":"dlt","thread":"MainThread","level":"DEBUG","module":"worker","line_no":231,"version":{"dlt_version":"1.5.0","pipeline_name":"my_pipeline"}}
{"written_at":"2025-02-04T08:05:11.635Z","written_ts":1738656311635475000,"component_name":"my_pipeline","process":8,"msg":"Processed 1 lines from file 1738656311.4801705/new_jobs/_dlt_pipeline_state.364a7ac03b.0.typed-jsonl","type":"log","logger":"dlt","thread":"MainThread","level":"DEBUG","module":"items_normalizers","line_no":208,"version":{"dlt_version":"1.5.0","pipeline_name":"my_pipeline"}}
{"written_at":"2025-02-04T08:05:11.635Z","written_ts":1738656311635580000,"component_name":"my_pipeline","process":8,"msg":"Processed file 1738656311.4801705/new_jobs/_dlt_pipeline_state.364a7ac03b.0.typed-jsonl","type":"log","logger":"dlt","thread":"MainThread","level":"DEBUG","module":"worker","line_no":237,"version":{"dlt_version":"1.5.0","pipeline_name":"my_pipeline"}}
{"written_at":"2025-02-04T08:05:11.635Z","written_ts":1738656311635650000,"component_name":"my_pipeline","process":8,"msg":"Closing writer for 1738656311.4801705.my.test_data with file None and actual name /var/dlt/pipelines/my_pipeline/load/new/1738656311.4801705/new_jobs/test_data.b309e78704.0.typed-jsonl","type":"log","logger":"dlt","thread":"MainThread","level":"DEBUG","module":"data_item_storage","line_no":81,"version":{"dlt_version":"1.5.0","pipeline_name":"my_pipeline"}}
{"written_at":"2025-02-04T08:05:11.636Z","written_ts":1738656311636380000,"component_name":"my_pipeline","process":8,"msg":"Closing writer for 1738656311.4801705.my._dlt_pipeline_state with file None and actual name /var/dlt/pipelines/my_pipeline/load/new/1738656311.4801705/new_jobs/_dlt_pipeline_state.d487670e10.0.typed-jsonl","type":"log","logger":"dlt","thread":"MainThread","level":"DEBUG","module":"data_item_storage","line_no":81,"version":{"dlt_version":"1.5.0","pipeline_name":"my_pipeline"}}
{"written_at":"2025-02-04T08:05:11.636Z","written_ts":1738656311636619000,"component_name":"my_pipeline","process":8,"msg":"Processed all items in 2 files","type":"log","logger":"dlt","thread":"MainThread","level":"INFO","module":"worker","line_no":245,"version":{"dlt_version":"1.5.0","pipeline_name":"my_pipeline"}}
{"written_at":"2025-02-04T08:05:11.636Z","written_ts":1738656311636731000,"component_name":"my_pipeline","process":8,"msg":"Updating schema for table test_data with 1 deltas","type":"log","logger":"dlt","thread":"MainThread","level":"INFO","module":"validate","line_no":20,"version":{"dlt_version":"1.5.0","pipeline_name":"my_pipeline"}}
{"written_at":"2025-02-04T08:05:11.636Z","written_ts":1738656311636840000,"component_name":"my_pipeline","process":8,"msg":"Updating schema for table _dlt_pipeline_state with 1 deltas","type":"log","logger":"dlt","thread":"MainThread","level":"INFO","module":"validate","line_no":20,"version":{"dlt_version":"1.5.0","pipeline_name":"my_pipeline"}}
{"written_at":"2025-02-04T08:05:11.636Z","written_ts":1738656311636984000,"component_name":"my_pipeline","process":8,"msg":"Table test_data has seen data for a first time with load id 1738656311.4801705","type":"log","logger":"dlt","thread":"MainThread","level":"INFO","module":"normalize","line_no":184,"version":{"dlt_version":"1.5.0","pipeline_name":"my_pipeline"}}
{"written_at":"2025-02-04T08:05:11.637Z","written_ts":1738656311637055000,"component_name":"my_pipeline","process":8,"msg":"Table _dlt_pipeline_state has seen data for a first time with load id 1738656311.4801705","type":"log","logger":"dlt","thread":"MainThread","level":"INFO","module":"normalize","line_no":184,"version":{"dlt_version":"1.5.0","pipeline_name":"my_pipeline"}}
{"written_at":"2025-02-04T08:05:11.637Z","written_ts":1738656311637250000,"component_name":"my_pipeline","process":8,"msg":"Saving schema my with version 1:2","type":"log","logger":"dlt","thread":"MainThread","level":"INFO","module":"normalize","line_no":190,"version":{"dlt_version":"1.5.0","pipeline_name":"my_pipeline"}}
{"written_at":"2025-02-04T08:05:11.638Z","written_ts":1738656311638639000,"component_name":"my_pipeline","process":8,"msg":"Committing storage, do not kill this process","type":"log","logger":"dlt","thread":"MainThread","level":"INFO","module":"normalize","line_no":206,"version":{"dlt_version":"1.5.0","pipeline_name":"my_pipeline"}}
{"written_at":"2025-02-04T08:05:11.639Z","written_ts":1738656311639187000,"component_name":"my_pipeline","process":8,"msg":"Extracted package 1738656311.4801705 processed","type":"log","logger":"dlt","thread":"MainThread","level":"INFO","module":"normalize","line_no":212,"version":{"dlt_version":"1.5.0","pipeline_name":"my_pipeline"}}
{"written_at":"2025-02-04T08:05:11.639Z","written_ts":1738656311639366000,"component_name":"my_pipeline","process":8,"msg":"Closing processing pool","type":"log","logger":"dlt","thread":"MainThread","level":"INFO","module":"pool_runner","line_no":103,"version":{"dlt_version":"1.5.0","pipeline_name":"my_pipeline"}}
{"written_at":"2025-02-04T08:05:11.639Z","written_ts":1738656311639432000,"component_name":"my_pipeline","process":8,"msg":"Processing pool closed","type":"log","logger":"dlt","thread":"MainThread","level":"INFO","module":"pool_runner","line_no":106,"version":{"dlt_version":"1.5.0","pipeline_name":"my_pipeline"}}
{"written_at":"2025-02-04T08:05:11.667Z","written_ts":1738656311667203000,"component_name":"my_pipeline","process":8,"msg":"Created thread pool with 20 workers","type":"log","logger":"dlt","thread":"MainThread","level":"INFO","module":"pool_runner","line_no":77,"version":{"dlt_version":"1.5.0","pipeline_name":"my_pipeline"}}
{"written_at":"2025-02-04T08:05:11.667Z","written_ts":1738656311667279000,"component_name":"my_pipeline","process":8,"msg":"Running pool","type":"log","logger":"dlt","thread":"MainThread","level":"DEBUG","module":"pool_runner","line_no":90,"version":{"dlt_version":"1.5.0","pipeline_name":"my_pipeline"}}
{"written_at":"2025-02-04T08:05:11.667Z","written_ts":1738656311667346000,"component_name":"my_pipeline","process":8,"msg":"Running file loading","type":"log","logger":"dlt","thread":"MainThread","level":"INFO","module":"load","line_no":614,"version":{"dlt_version":"1.5.0","pipeline_name":"my_pipeline"}}
{"written_at":"2025-02-04T08:05:11.667Z","written_ts":1738656311667447000,"component_name":"my_pipeline","process":8,"msg":"Found 1 load packages","type":"log","logger":"dlt","thread":"MainThread","level":"INFO","module":"load","line_no":617,"version":{"dlt_version":"1.5.0","pipeline_name":"my_pipeline"}}
{"written_at":"2025-02-04T08:05:11.667Z","written_ts":1738656311667510000,"component_name":"my_pipeline","process":8,"msg":"Loading schema from load package in 1738656311.4801705","type":"log","logger":"dlt","thread":"MainThread","level":"INFO","module":"load","line_no":623,"version":{"dlt_version":"1.5.0","pipeline_name":"my_pipeline"}}
{"written_at":"2025-02-04T08:05:11.675Z","written_ts":1738656311675172000,"component_name":"my_pipeline","process":8,"msg":"Loaded schema name my and version 2","type":"log","logger":"dlt","thread":"MainThread","level":"INFO","module":"load","line_no":625,"version":{"dlt_version":"1.5.0","pipeline_name":"my_pipeline"}}
{"written_at":"2025-02-04T08:05:11.899Z","written_ts":1738656311899293000,"component_name":"my_pipeline","process":8,"msg":"Client for sqlalchemy will start initialize storage ","type":"log","logger":"dlt","thread":"MainThread","level":"INFO","module":"utils","line_no":157,"version":{"dlt_version":"1.5.0","pipeline_name":"my_pipeline"}}
{"written_at":"2025-02-04T08:05:11.952Z","written_ts":1738656311952903000,"component_name":"my_pipeline","process":8,"msg":"Client for sqlalchemy will update schema to package schema ","type":"log","logger":"dlt","thread":"MainThread","level":"INFO","module":"utils","line_no":176,"version":{"dlt_version":"1.5.0","pipeline_name":"my_pipeline"}}
{"written_at":"2025-02-04T08:05:11.981Z","written_ts":1738656311981530000,"component_name":"my_pipeline","msg":"Schema with hash uToJPR/fLECCq1wMoH1hky8KVuqPVuZWT2Ym61i49CI= not found in storage, upgrading","process":8,"type":"log","logger":"dlt","thread":"MainThread","level":"INFO","module":"sqlalchemy_job_client","line_no":179,"version":{"dlt_version":"1.5.0","pipeline_name":"my_pipeline"}}
{"written_at":"2025-02-04T08:05:13.344Z","written_ts":1738656313344868000,"component_name":"my_pipeline","process":8,"msg":"Closing processing pool","type":"log","logger":"dlt","thread":"MainThread","level":"INFO","module":"pool_runner","line_no":103,"version":{"dlt_version":"1.5.0","pipeline_name":"my_pipeline"}}
{"written_at":"2025-02-04T08:05:13.345Z","written_ts":1738656313345003000,"component_name":"my_pipeline","process":8,"msg":"Processing pool closed","type":"log","logger":"dlt","thread":"MainThread","level":"INFO","module":"pool_runner","line_no":106,"version":{"dlt_version":"1.5.0","pipeline_name":"my_pipeline"}}
Traceback (most recent call last):
File "/usr/local/lib/python3.10/site-packages/dlt/pipeline/pipeline.py", line 608, in load
runner.run_pool(load_step.config, load_step)
File "/usr/local/lib/python3.10/site-packages/dlt/common/runners/pool_runner.py", line 91, in run_pool
while _run_func():
File "/usr/local/lib/python3.10/site-packages/dlt/common/runners/pool_runner.py", line 84, in _run_func
run_metrics = run_f.run(cast(TExecutor, pool))
File "/usr/local/lib/python3.10/site-packages/dlt/load/load.py", line 639, in run
self.load_single_package(load_id, schema)
File "/usr/local/lib/python3.10/site-packages/dlt/load/load.py", line 528, in load_single_package
applied_update = init_client(
File "/usr/local/lib/python3.10/site-packages/dlt/load/utils.py", line 117, in init_client
applied_update = _init_dataset_and_update_schema(
File "/usr/local/lib/python3.10/site-packages/dlt/load/utils.py", line 180, in _init_dataset_and_update_schema
applied_update = job_client.update_stored_schema(
File "/usr/local/lib/python3.10/site-packages/dlt/destinations/impl/sqlalchemy/sqlalchemy_job_client.py", line 214, in update_stored_schema
self._update_schema_in_storage(self.schema)
File "/usr/local/lib/python3.10/site-packages/dlt/destinations/impl/sqlalchemy/sqlalchemy_job_client.py", line 240, in _update_schema_in_storage
self.sql_client.execute_sql(table_obj.insert().values(schema_mapping))
File "/usr/local/lib/python3.10/site-packages/dlt/destinations/impl/sqlalchemy/db_api_client.py", line 316, in execute_sql
with self.execute_query(sql, *args, **kwargs) as cursor:
File "/usr/local/lib/python3.10/contextlib.py", line 135, in enter
return next(self.gen)
File "/usr/local/lib/python3.10/site-packages/dlt/destinations/impl/sqlalchemy/db_api_client.py", line 337, in execute_query
yield SqlaDbApiCursor(self._current_connection.execute(query, *args)) # type: ignore[call-overload, abstract]
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1385, in execute
return meth(self, multiparams, params, _EMPTY_EXECUTION_OPTS)
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/sql/elements.py", line 334, in _execute_on_connection
return connection._execute_clauseelement(
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1577, in _execute_clauseelement
ret = self._execute_context(
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1953, in _execute_context
self.handle_dbapi_exception(
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 2138, in handle_dbapi_exception
util.raise(exc_info[1], with_traceback=exc_info[2])
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/util/compat.py", line 211, in raise
raise exception
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1910, in _execute_context
self.dialect.do_execute(
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 736, in do_execute
cursor.execute(statement, parameters)
File "/usr/local/lib/python3.10/site-packages/exasol/driver/websocket/_cursor.py", line 70, in wrapper
return method(self, *args, **kwargs)
File "/usr/local/lib/python3.10/site-packages/exasol/driver/websocket/_cursor.py", line 224, in execute
self.executemany(operation, [parameters])
File "/usr/local/lib/python3.10/site-packages/exasol/driver/websocket/_cursor.py", line 70, in wrapper
return method(self, *args, **kwargs)
File "/usr/local/lib/python3.10/site-packages/exasol/driver/websocket/_cursor.py", line 301, in executemany
self._cursor.execute_prepared(parameters)
File "/usr/local/lib/python3.10/site-packages/pyexasol/statement.py", line 196, in execute_prepared
ret = self.connection.req({
File "/usr/local/lib/python3.10/site-packages/pyexasol/connection.py", line 511, in req
send_data = self.json_encode(req)
File "/usr/local/lib/python3.10/site-packages/pyexasol/connection.py", line 846, in
self.json_encode = lambda x, indent=False: json.dumps(x, indent=2 if indent else None, ensure_ascii=False)
File "/usr/local/lib/python3.10/json/init.py", line 238, in dumps
**kw).encode(obj)
File "/usr/local/lib/python3.10/json/encoder.py", line 199, in encode
chunks = self.iterencode(o, _one_shot=True)
File "/usr/local/lib/python3.10/json/encoder.py", line 257, in iterencode
return _iterencode(o, 0)
File "/usr/local/lib/python3.10/json/encoder.py", line 179, in default
raise TypeError(f'Object of type {o.class.name} '
TypeError: Object of type DateTime is not JSON serializable

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
File "/app/test_csv_to_exasol/test_pipeline.py", line 22, in
load_info = pipeline.run(filesystem_pipe)
File "/usr/local/lib/python3.10/site-packages/dlt/pipeline/pipeline.py", line 226, in _wrap
step_info = f(self, *args, **kwargs)
File "/usr/local/lib/python3.10/site-packages/dlt/pipeline/pipeline.py", line 275, in _wrap
return f(self, *args, **kwargs)
File "/usr/local/lib/python3.10/site-packages/dlt/pipeline/pipeline.py", line 747, in run
return self.load(destination, dataset_name, credentials=credentials)
File "/usr/local/lib/python3.10/site-packages/dlt/pipeline/pipeline.py", line 226, in _wrap
step_info = f(self, *args, **kwargs)
File "/usr/local/lib/python3.10/site-packages/dlt/pipeline/pipeline.py", line 166, in _wrap
return f(self, *args, **kwargs)
File "/usr/local/lib/python3.10/site-packages/dlt/pipeline/pipeline.py", line 275, in _wrap
return f(self, *args, **kwargs)
File "/usr/local/lib/python3.10/site-packages/dlt/pipeline/pipeline.py", line 615, in load
raise PipelineStepFailed(
dlt.pipeline.exceptions.PipelineStepFailed: Pipeline execution failed at stage load when processing package 1738656311.4801705 with exception:

<class 'TypeError'>
Object of type DateTime is not JSON serializable`