Skip to content

sql_database MSSQL conversion error with datetime #2486

@AndrewLockton

Description

@AndrewLockton

dlt version

1.9.0

Describe the problem

Trying to export SQL table from MSSQL using pyarrow backend gives a conversion error for datetime columns. I am using PYODBC with SQL Server Driver 2017.

<class 'dlt.extract.exceptions.ResourceExtractionError'>
In processing pipe RISK_HEADERS: extraction of resource RISK_HEADERS in generator table_rows caused an exception: Conversion to arrow failed for field `RNWDATE` with dlt hint `data_type=timestamp` and `inferred_arrow_type=timestamp[ns]` This data type seems currently unsupported by dlt. Please open a GitHub issue

Max datetime in this column is "2918-08-01 00:00:00.000", would this cause issues being so far in the future? I have tried using table_adapter_callback to amend these values but I am either making a mistake or it isnt the solution,

    def _table_adapter_callback(table: sa.Table) -> sa.Table:

        for column in table.columns.values():            
            if isinstance(column.type, sa.DateTime):
                sa.update(table).where(column >= "2050-01-01 00:00:00.000").values(column.name="2050-01-01 00:00:00.000")

        return table

Expected behavior

No response

Steps to reproduce

pipeline = dlt.pipeline(
        pipeline_name="pipeline_dlt_test",
        destination="mssql",
        dataset_name="dlt_test"
    )

    source = sql_database(
        credentials=credentials,
        reflection_level="full_with_precision",
        chunk_size=50000,
        include_views=True,
        schema="dbo",
        backend="pyarrow",
        table_names=["..."]
            
    )

    # Run the resources together
    info = pipeline.extract(source, write_disposition="replace")
    print(info)

Operating system

Windows

Runtime environment

Local

Python version

3.12

dlt data source

Microsoft SQL Server
sql_database

dlt destination

No response

Other deployment details

No response

Additional information

No response

Metadata

Metadata

Assignees

Labels

No labels
No labels

Type

Projects

Status

In Progress

Milestone

No milestone

Relationships

None yet

Development

No branches or pull requests

Issue actions