[Feature]: Default to BIGINT

[visch]

Feature scope

Targets (data type handling, batching, SQL object generation, etc.)

Description

We should add this to the SQL Target milestone

We should probably default to bigint. Implementation with test here MeltanoLabs/target-postgres#27

[BuzzCutNorman]

I just ran into this as well. Below is a simple table that I am able to reproduce the issue with. If I run meltano run tap-mssql target-mssql with just the dbo-TestTable selected. I get the following error message:

('22003', '[22003] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Arithmetic overflow error converting expression to data type int. (8115) (SQLExecDirectW); [22003] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]The statement has been terminated. (3621)')

use [testdata]
go
/*********************************
Create the simple test table
in an MSSQL database
*********************************/
DROP TABLE IF EXISTS [TestTable];
CREATE TABLE TestTable (
    Id int IDENTITY(1,1) PRIMARY KEY,
    TestColumn bigint
);

/*********************************
Insert some test data into the
test table
*********************************/
INSERT INTO [testdata].[dbo].[TestTable]
           ([TestColumn])
     VALUES
           (1234567890123345)
;

[BuzzCutNorman]

@visch @aaronsteers

Playing around I got this to work using JSON Schema. The maximum value is the max size of a bigint. The only issue is you have to use number instead of integer. If we go this route we can have integer and bigint types. WDYT?

Tap side

@staticmethod
def to_jsonschema_type(sql_type: sqlalchemy.types.TypeEngine) -> dict:
        if str(sql_type) == 'BIGINT':
           return {"type": ["number"], "maximum": 9223372036854775807}

Target side

@staticmethod
def to_sql_type(jsonschema_type: dict) -> types.TypeEngine:
        if 'number' in jsonschema_type.get('type'):
            maximum = jsonschema_type.get("maximum")
            if maximum == 9223372036854775807:
                return cast(sqlalchemy.types.TypeEngine, sqlalchemy.types.BIGINT()) 

I attempted to setup a class in typing.py but couldn't get a combination that worked.

[visch]

@visch @aaronsteers

Playing around I got this to work using JSON Schema. The maximum value is the max size of a bigint. The only issue is you have to use number instead of integer. If we go this route we can have integer and bigint types. WDYT?

Tap side

@staticmethod
def to_jsonschema_type(sql_type: sqlalchemy.types.TypeEngine) -> dict:
        if str(sql_type) == 'BIGINT':
           return {"type": ["number"], "maximum": 9223372036854775807}

Target side

@staticmethod
def to_sql_type(jsonschema_type: dict) -> types.TypeEngine:
        if 'number' in jsonschema_type.get('type'):
            maximum = jsonschema_type.get("maximum")
            if maximum == 9223372036854775807:
                return cast(sqlalchemy.types.TypeEngine, sqlalchemy.types.BIGINT()) 

I attempted to setup a class in typing.py but couldn't get a combination that worked.

Did you look at my implementation linked to above? I recall copying typing.py

In regards to your question I don't think a number is an int, but it does depend on the database. I think decimal(32,0) or something similar is an option for mssql and oracle and is probably better than going with bigint as I did above.

@BuzzCutNorman read through this again. And looked at https://www.postgresql.org/docs/current/datatype-numeric.html#DATATYPE-NUMERIC-TABLE , with postgres in the context of MSSQL and Oracle in my mind a NUMERIC might be a better option, but I"m not sure if we want to take the size trade off here. We could do something like NUMERIC(131072,0) for postgres just to allow anything but that seems a bit... intense

[BuzzCutNorman]

@visch Thanks, for those comments. Looking at your examples you are correct the JSON I proposed is off base from what this feature is solving.

[stale[bot]]

This has been marked as stale because it is unassigned, and has not had recent activity. It will be closed after 21 days if no further activity occurs. If this should never go stale, please add the evergreen label, or request that it be added.