SQL Server -> Parquet -> Pandas -> Postgres data type issue

[Vigenary]

Using odbc2parquet to produce parquet files from SQL Server tables.
There is a table with a tinyint column (range 0-255).

Odbc2Parquet imports it into Postgres properly (the column there is defined as int4).
However Pandas/pyarrow wraps the numbers during import: they are imported as range from -128 to 126.

Pyarrow sees the parquet file schema like this:

<pyarrow._parquet.ParquetSchema object at 0x000000CA428F7F00>
required group field_id=-1 schema {
required int32 field_id=-1 DetailID (Int(bitWidth=8, isSigned=true));
}

Is this a bug in odbc2parquet or pandas/pyarrow ?
Any idea how to work around this, besides redefining the data type at SQL Server side?

[pacman82]

Hello @Vigenary ,

odbc2parquet might be at fault here, because it does not query the schema for whether or not the column in question is signed or not, and always writes out the logical type as signed. However the "physical" type to store it in parquet is a sign 32Bit integer. Which can hold the correct values. I would need to look some more into this, but my current assumption is, that odbc2parquet should query more information about the column and write out this column as unsigned. Would you mind telling me the precise CREATE TABLE statement used to create the column in the Postgres Database?

Best, Markus

[pacman82]

Or wait, you did start out with SQL Server? In that case please this CREATE TABLE statement containing the relational type also.

[pacman82]

Never mind, could reproduce it on my own. odbc2parquet 2.0.3 is released. It contains a fix, which will query the ODBC driver for the sign of a TINYINT column. The sign will be reflected appropriately in the logical type of the column.

Best, Markus

[Vigenary]

Markus, thank you very much! Very impressively fast fix, you made it before I even got to reply to your questions! Thank you!

[pacman82]

You are welcome