Linen
Join Slack
hi all, i have a postgres tap, in which one of the columns is jsonb, but when it makes it's way to s...
# troubleshooting
m
hi all, i have a postgres tap, in which one of the columns is jsonb, but when it makes it's way to snowflake -- it's reported as varchar boolean; unclear if this is expected behavior? i've done no mapping at all. thanks
✅ 1
oh boy that's really unfortunate.
e
when it makes it's way to snowflake
which target-snowflake variant is this?
it's reported as varchar boolean
I'm not sure if this is a typo 😅. Do you mean it's reported as varchar instead of variant?
m
it's the meltanolabs variant. the attached images are postges tap and postres target for testing; what i mean by varchar boolean. is it's true::text
e
what i mean by varchar boolean. is it's true::text
do you see that in target-postgres too? (looking to confirm before jumping into trying to reproduce the issue)
m
maybe the varchar boolean shouldn't be this issue. the issue is that the postgres tap doesn't know how to deal with a jsonb
calling it in the yaml like this works
Copy code
schema
      public-test_users:
        service_settings:
          type:
          - object
but they fails is it sees a null
e
I think I see what you mean. jsonb is translated to an iffy catch-all json schema type in: https://github.com/MeltanoLabs/tap-postgres/blob/f35ff845c856d4cb9e0e7c8e8f8f72f6a84ae6fc/tap_postgres/client.py#L201-L206
Then the target sees that and creates a TEXT column
m
so how do i take care of nulls here? i don't see that a type here in this code
like if it's an objet or a null?
e
you can try
Copy code
schema
      public-test_users:
        service_settings:
          type: [object, null]
m
it's the target that it's erroring on now, which in this example is now the postgres target from MetlanoLabs
e
Is that for the same 
service_settings
column?
(I ask cause I don't think you'd get that error for the schema override we defined above)
m
yeah, i rewrote one of those entries to have NULL; and that's what happens
in my production setting it's kinda doing the same thing
Copy code
singer_sdk.exceptions.InvalidRecord: Record Message Validation Error: None is not of type 'object'", "level": "info", "timestamp": "2024-03-27T01:33:11.002278Z"}
e
Oh wait! Try
Copy code
schema
       public-test_users:
         service_settings:
           type: [object, "null"]
(note the quotes around 
null
)
m
yeah, i saw that. locally that works. off to prod now. lol. thanks. why does it like the "null" and not null?
well, that works in staging now. so thank you @Edgar Ramírez (Arch.dev)
🙌 1
e
why does it like the "null" and not null?
the latter gets deserialized as 
{"type": ["object", None]}
, but we want 
{"type": ["object", "null"]}
(https://json-schema.org/understanding-json-schema/reference/null)
v
https://github.com/MeltanoLabs/tap-postgres/pull/239 has the "why" here I should probably get that in tap-postgres. Seems like something that would be nice to flip back to an object if you wanted it to be
Open in Slack
PreviousNext
Powered by

Meltano

Meltano community

Powered by