https://linen.dev logo
Join Slack
Powered by
Hi Team, We are trying to explore meltano as EL so...
# troubleshooting
h
Hi Team, We are trying to explore meltano as EL solution, As our first EL we are trying to dump data from MSSQL to S3-Parquet replication, tried both FULL_TALBLE and INCREMENTAL but getting same error in both scenarios as below. Log level is DEBUG and we are unable to identity the root cause. Can you please help and let us know if we are doing anything wrong. 1. 
--variant buzzcutnorman
2022-12-06T074031.155867Z [error ] Loader failed 
"""╭─────────────────────────────── Traceback (most recent call last) ────────────────────────────────╮
│ /Users/harshit.soni/opt/anaconda3/envs/py39/lib/python3.9/site-packages/meltano/core/logging/out │
│ put_logger.py:201 in redirect_logging                                                            │
│                                                                                                  │
│   198 │   │   │   *ignore_errors,                                                                │
│   199 │   │   )                                                                                  │
│   200 │   │   try:                                                                               │
│ ❱ 201 │   │   │   yield                                                                          │
│   202 │   │   except ignored_errors:  # noqa: WPS329                                             │
│   203 │   │   │   raise                                                                          │
│   204 │   │   except Exception as err:                                                           │
│                                                                                                  │
│ ╭────────────────────────────────────────── locals ───────────────────────────────────────────╮  │
│ │            err = RunnerError('Loader failed')                                               │  │
│ │  ignore_errors = ()                                                                         │  │
│ │ ignored_errors = (<class 'KeyboardInterrupt'>, <class 'asyncio.exceptions.CancelledError'>) │  │
│ │         logger = <RootLogger root (DEBUG)>                                                  │  │
│ │           self = <meltano.core.logging.output_logger.Out object at 0x7ff2201999a0>          │  │
│ ╰─────────────────────────────────────────────────────────────────────────────────────────────╯  │
│                                                                                                  │
│ /Users/harshit.soni/opt/anaconda3/envs/py39/lib/python3.9/site-packages/meltano/core/block/extra │
│ ct_load.py:461 in run                                                                            │
│                                                                                                  │
`│ 458 │ │ │ # TODO: legacy 
meltano elt
style logging should be deprecated │` 
│   459 │   │   │   legacy_log_handler = self.output_logger.out("meltano", logger)                 │
│   460 │   │   │   with legacy_log_handler.redirect_logging():                                    │
│ ❱ 461 │   │   │   │   await self.run_with_job()                                                  │
`│ 462 │ │ │ │ return …
c
That big Traceback box is actually not the interesting part. All the interesting logs are all the lines that occured before / above that Traceback box
h
Thanks for a quick response @christoph. Provided below is log trail 
022-12-06T07:40:31.042315Z [info     ] jsonschema.exceptions.ValidationError: 5000.0 is not of type 'string', 'null' cmd_type=elb consumer=True name=target-s3-parquet producer=False stdio=stderr string_id=target-s3-parquet
2022-12-06T07:40:31.042432Z [info     ]                                cmd_type=elb consumer=True name=target-s3-parquet producer=False stdio=stderr string_id=target-s3-parquet
2022-12-06T07:40:31.042547Z [info     ] Failed validating 'type' in schema['properties']['Relamt']: cmd_type=elb consumer=True name=target-s3-parquet producer=False stdio=stderr string_id=target-s3-parquet
2022-12-06T07:40:31.042658Z [info     ]     {'type': ['string', 'null']} cmd_type=elb consumer=True name=target-s3-parquet producer=False stdio=stderr string_id=target-s3-parquet
2022-12-06T07:40:31.042775Z [info     ]                                cmd_type=elb consumer=True name=target-s3-parquet producer=False stdio=stderr string_id=target-s3-parquet
2022-12-06T07:40:31.042885Z [info     ] On instance['Relamt']:         cmd_type=elb consumer=True name=target-s3-parquet producer=False stdio=stderr string_id=target-s3-parquet
2022-12-06T07:40:31.042994Z [info     ]     5000.0                     cmd_type=elb consumer=True name=target-s3-parquet producer=False stdio=stderr string_id=target-s3-parquet
2022-12-06T07:40:31.043123Z [info     ] {"type": "RECORD", "stream": "dbo-Ledger1", "record": {"Bnkname": "dsfg", "Brnname": "", "dfg": "C", "Ddno": "", "Dddt": "2016-12-23T14:00:15.273000+00:00", "Reldt": "2016-12-23T00:00:00+00:00", "Relamt": 5000.0, "Refno": "            ", "Receiptno": 0, "Vtyp": 9, "Vno": "2345", "Lno": 2, "Drcr": "C", "Booktype": "01", "Micrno": 0, "Slipno": 0, "Slipdate": "1900-01-01T00:00:00+00:00", "Chequeinname": "Test Check", "Chqprinted": 0, "Clear_mode": " ", "L1_SNo": 108}, "time_extracted": "2022-12-06T07:40:31.002550+00:00"} cmd_type=elb consumer=False name=tap-mssql producer=True stdio=stdout string_id=tap-mssql
c
jsonschema.exceptions.ValidationError: 5000.0 is not of type 'string', 'null'
There's the issue
So, 
tap-mssql
is saying that the "Relamt" column is expected to be of type 'string' ... but what is actually being emitted is a float instead.
h
its a float column as its amount, How can we disable schema checks?
c
The Meltano SDK is supposed to correctly provide the data type when the Singer SCHEMA message gets emitted by 
tap-mssql
Under the hood, the Meltano SDK uses SQLAlchemy Core to do all the hard work of abstracting away the different SQL database implementations.
So, my first guess is that something is not working right with your particular MSSQL source database in combination with the Meltano-SDK based 
tap-mssql
I actually only have very limited use of the SDK based 
tap-mssql
myself. In production, I still rely on the the default variant https://hub.meltano.com/extractors/tap-mssql--wintersrd/ As a quick workaround, you could give that variant a try if you need to put something together quickly and if you don't need any of the features of the Meltano SDK.
h
We tried that variant as well and got same sort of error with it. It stated something like 0.015 is not a multiple of .0001.
c
Hmmm. That's very strange. In that case, it's probably better to stick with the Meltano SDK based tap-mssql and troubleshoot your problem by using that variant
I can't provide that much advise on this tap, apart from the fact, that the relevant code is this bit: https://github.com/meltano/sdk/blob/80daef23cb5ef5bad4c73b1ef0df7cab06483cea/singer_sdk/connectors/sql.py#L369-L384
One thing that will be helpful to know is what the information_schema in your source database is reporting for this column. i.e., running a
Copy code
select column_name,data_type from information_schema.columns where table_name = 'Ledger1' and column_name = 'Relamt';
to see if it the data_type is indeed numeric or decimal
h
column type is money in Db
c
Ah, interesting. That's rarely used. I think that's where the 
tap-mssql
is getting stuck.
I think that the "MONEY" and "SMALLMONEY" SQL types need to be added to the 
tap-mssql
to_jsonschema_type()
override method and be mapped to the correct JSON type (i.e. a numeric JSON type, since "MONEY" and "SMALLMONEY" are just decimal in SQL Server) https://github.com/BuzzCutNorman/tap-mssql/blob/9bdceb5bcc0d22c921c17e9207975c003fc66e40/tap_mssql/client.py#L91-L95
cc @BuzzCutNorman Does that sound right to you?
b
@christoph I believe you are correct. I will take a look this morning and update the thread with what I find.
@christoph and @harshit_soni I just put a fix in place. You might need to run 
meltano install --clean
in existing projects to get the fix. https://github.com/BuzzCutNorman/tap-mssql/issues/8#issuecomment-1340060294
h
Thanks @BuzzCutNorman for quick fix, will do it.
Open in Slack
PreviousNext
Powered by