I’m working on a second iteration on a singer tap ...
# singer-tap-development
j
I’m working on a second iteration on a singer tap that uses the meltano SDK. One of the things I am hoping to do is load json object types as a string into Snowflake. In the target-snowflake README it mentions this limitation of variant or objects types being unavailable in sqlalchemy, and says
Until it is fixed, we are going to store semi-structured data (mainly JSON arrays) as strings and depend on a transformation step to convert those strings to proper snowflake VARIANT or ARRAY types.
The tap fails as I exptected it would if i try to define the object as a string in the object schema with a
Failed validating 'type' in schema
error. I am not sure how to do this conversion from a json object in the tap to a string in the target. Is this a change I would make to the json schema in the tap or somewhere else?
Right now we’re defining the attribute as an
object
in the object schema, which gets ignored in target-snowflake.
Now I am wondering if my version is just out of date, because this looks handled in https://gitlab.com/meltano/target-snowflake/-/blob/master/target_snowflake/utils/singer_target_utils.py#L145
I went ahead and ran
install --clean
for both the tap and target since our meltano.yml is just referencing the repo for target-snowflake, now I’m getting a sqlalchemy error
CRITICAL 'SnowflakeDialect' object has no attribute 'driver'
😤 digging around a bit…
I was able to resolve the last error, but the loader is still ignoring the object type. I noticed that the project has been archived and is now pointing to pipelinewise. I am not sure if there is a solution there either.
oop looks like we recently started using a fork of the meltano snowflake loader here at gitlab. So now I’m wondering if I just need to change something in my schema definition to get the string type to work that looks to be set up in https://gitlab.com/meltano/target-snowflake/-/blob/master/target_snowflake/utils/singer_target_utils.py#L145
Any help here still appreciated
a
Hi @jstark unfortunately I have no experience with the snowflake loader. We push everything to S3, and then we set up external stages in snowflake. If that sounds like an interesting approach, I'd be happy to share more
e
Hi @jstark. Seems like the snowflake loader should be able to handle the object type fine if you remove the
properties
key from the schema: https://gitlab.com/meltano/target-snowflake/-/blob/master/target_snowflake/utils/singer_target_utils.py#L108-114
j
thanks @edgar_ramirez_mondragon when I remove that I get
KeyError: 'properties'
I wondered if i should add
"$ref": "#"
as a property or something equivalent. I am pretty new to json schemas
I’ve been trying to find a good code sample of this being done in another tap, so far no luck
here is the full stack trace
Untitled.cpp
e
oic! @jstark that's a bug in an older version of the SDK. Can you bump your tap's SDK version to the latest?
poetry update singer-sdk
should do the trick
j
that’d be great news., @edgar_ramirez_mondragon I updated signer-sdk and jsonschema unsing
poetry update
and am still getting the same error.
e
Are you using Meltano to test the tap?
j
no, just virtual envs and poetry
do you think meltano would handle this correctly?
Copy code
poetry run tap-zengrc --config .secrets/config.json  | ~/.virtualenvs/target-jsonl/bin/target-jsonl
I can add a meltano.yml to the tap project if you think it’d handle this better
for context I could very easily define the schema in a way that would handle our use case, but that would make the tap idiosyncratic, which I had hoped to contribute to the hub after this iteration.
also, it’s friday, so maybe it’s worth coming back to this next week.
e
do you think meltano would handle this correctly?
not necessarily. was just wondering if the venv was stale
or context I could very easily define the schema in a way that would handle our use case, but that would make the tap idiosyncratic, which I had hoped to contribute to the hub after this iteration.
thanks for that!
if it's only poetry then can you run
poetry run tap-zengrc --about
to confirm the SDK version in 0.3.17?
j
Untitled.txt
e
Copy code
Sdk_Version: 0.3.10
It's not the latest 🤔. What version of singer-sdk do you see in
poetry.lock
?
j
same, that’s what the upgrade ran to
maybe there is a constraint in pyproject.toml
that was it
I’m still learning poetry
woohoo it’s working with meltano and our fork of target-snowflake now. I’m getting the json object as a varchar in snowflake. Thanks @edgar_ramirez_mondragon !
e
Awesome! It's always nice when you fix a bug by updating a dependency 😄