Morning folks .. Been trying to export to snowflake from mysql db using

target-snowflake

meltanolabs variant .. but having an issue when validating values like this

jsonschema.exceptions.ValidationError: '[{"amount": -50, "bucketId": "66cd03a6"}]' is not of type 'null', 'object'
Failed validating 'type' in schema['properties']['transaction_buckets']

I did enable the flattening option ..

flattening_enabled: true
flattening_max_depth: 1

not sure if also this one a know issue or not .. I did a research but did not land on anything yet. The column's data-type is

json

in the Db though. Not sure if I can change the schema or define it manually to tell meltano what is the type of it .. somehting like this

variant

instead of `object`:

- name: transaction_buckets
        datatype: variant

@bassel what tap are you using? It seems like the tap might be incorrectly defining the data type for that field.

You can use stream maps to cast it to another type https://docs.meltano.com/guide/mappers as a short term test or fix and if the tap does have the incorrect data type then you can open an issue in the repo to let the maintainer know

@pat_nadolny

tap-mysql

I generated the catalog

meltano --environment=dev invoke --dump=catalog tap-mysql > catalog.json

and verified the field

"transaction_buckets": {
            "inclusion": "available",
            "type": [
              "null",
              "object"
            ]
          },

but the type in the Db is

json

and its an array of json objects

'[{"amount": -50, "bucketId": "66cd03a6"}]'

====================== Also, its complaining about another column with type

longtext

in the db

Invalid character length: 4,294,967,295. Must be between 1 and 16,777,216

with the maxLength value:

"data": {
            "inclusion": "available",
            "maxLength": 4294967295,
            "type": [
              "null",
              "string"
            ]
          },

@bassel thanks for the info, I'll try to replicate it in one of our tests really quick

I opened a few issues for this https://github.com/MeltanoLabs/target-snowflake/issues/67 and https://github.com/meltano/sdk/issues/1812. I have a PR to fix the max length bug in the target https://github.com/MeltanoLabs/target-snowflake/pull/68

I forked the tap-mysql repo and tried to change the way it constructs the schema for each table .. testing it 🤞

I think since array and object are different types they wouldnt be interchangeable like this

@pat_nadolny That's correct.

@bassel theres a new release with the max length fix https://github.com/MeltanoLabs/target-snowflake/releases/tag/v0.3.1. If you dont have pip_url version pinned in your meltano.yml then just run

meltano install loader target-snowflake --clean

otherwise bump the pinned version to 0.3.1 then run that install command

@bassel Can you try explicitly overriding the type of that property to

['array', 'null']

using this feature? https://docs.meltano.com/concepts/plugins#schema-extra