https://linen.dev logo
Join Slack
Powered by
Is there a way to get pipelinewise-target-snowflak...
# troubleshooting
e
Is there a way to get pipelinewise-target-snowflake to treat 2 fields as the primary key even though those 2 fields are not a primary key in postgres?
v
e
Thank you for the suggestion, it does seem like this should work. Will report back!
Mixed results here. Here is the change I made (turns out to be metadata, not schema), last 2 lines are the new ones:
extractors:
- name: cb_stats-0
inherit_from: tap-postgres-cb_stats
metadata:
public-stats_broadcasterstats:
replication-method: TIME_BASED
replication-key: time
replication-time-interval: 1 DAY
table-key-properties:
- user_id, time
This had the desired effect of setting the 
table_key_properties
:
"table_name": "stats_broadcasterstats",
"stream": "stats_broadcasterstats",
"metadata": [
{
"breadcrumb": [],
"metadata": {
"table-key-properties": [
"user_id, time"
],
However, it doesn't seem that pipelinewise-target-snowflake is respecting the keys and avoiding inserting duplicated like I had hoped.
k
I was wondering about this recently too šŸ¤” We have a few tables without PK's that we have resorted to doing FULL_TABLE replication on. We also use ppw-target-snowflake, so are subject to the same upsert.
@aaronsteers I wonder if there is a feature-request in the SDK somewhere here šŸ¤” My first thought was to generate a record hash to use as a PK in the target. Correct me if I'm wrong but this can now be done with stream_maps. Something like:
Copy code
{
    "stream_maps": {
        "my_stream_without_pk": { // Apply these transforms to the stream called 'my_stream_without_pk'
            "surrogate_pk": "md5(config['hash_seed'] + my_column_1 + my_column_2)", // for uniqueness checks
            "__key_properties__": ["surrogate_pk"]
        }
    },
    "stream_map_config": {
        // hash outputs are not able to be replicated without the original seed:
        "hash_seed": "01AWZh7A6DzGm6iJZZ2T"
    }
}
My understanding is this i) creates a new filed as the MD5 of the seed + the two selected columns and ii) sets the 
key_properties
to that new surrogate key?
a
Yes! This would work now. And the SDK handles the schema transformation as well.
k
Can you ommit the salt, as this isn't for obfuscation?
a
You can absolutely ommit the salt.
k
This is awsome šŸ™Œ
a
Oh... I think I'm just now understanding your use case. Is the intended solution to actually fabricate a uniqueness key where one doesn't already exit?? Holy cow, yeah, that's cool.
I hadn't thought of that, but I've seen use cases where it could have been handy.
k
The original question was "can I tell the target to use 2 columns as primary keys to avoid duplication" šŸ™‚
I think its the same problem - no one PK to ensure uniqueness in the destination tables šŸ˜…
a
Ah, I see. (Reads all the way up in the thread.) šŸ™‚ Yeah, definitely this could be applied to that scenario. We didn't intentionally set out to duplicate functionality that users can already do in catalog manipulation, but it was necessary in order to meet obfuscation/removal requirements that might alter the primary key columns.
This was a late-added section in the docs which explain that capability: https://sdk.meltano.com/en/latest/stream_maps.html#unset-or-modify-the-stream-s-primary-key-behavior
k
It sounds like catalog manipulation hasn't worked in @edward_smithā€™s case because the target isn't respecting the multiple replication keys šŸ¤”
a
I see... yeah, if that's the root issue, this would be a good solution. Since with traditional methods you could never get to a single composite column.
k
ā¤ļø this. I guess if md5 was expensive or overkill, you could just string concat the field values right? Like a regular surrogate key šŸ˜…
a
Indeed. šŸ‘
Something like this would work: 
'-'.join([column_a, column_b])
Or any number of other concatenation tricks.
k
@niall_woodward šŸ‘†šŸ˜…
a
I guess, bringing this back though, we still need to publish the standalone plugin, as mentioned in the blog post. That will also allow us to add the meltano.yml-based config that automatically injects the transformer between any two taps. (Whereas currently it requires either a tap or target built on the SDK.)
v
Fun stuff!
k
There is probably a case for raising a MR with the target to implement multiple key properties too šŸ˜… Stream maps will be useful if you want a new surrogate key anyway but @visch s solution of modifying the catalog should work in an ideal world, and is definitely more lightweight from a performance perspective.
v
I don't need credit! I think you both dove in deeper than me, thank you! If I happen to be perusing slack I'll share my 2 cents if it'll help someone, some good some bad šŸ˜„
e
Just catching up on this after the weekend. Coupla things: ā€¢ My issue is actually that I can't manage to get 
key_properties
to be set in the SCHEMA message from tap-postgres. I've tried various forms of configuration of 
schema
and 
metadata
in 
meltano.yml
, but I always get an empty 
key_properties
emitted. ā€¢ Fabricating a unique key inline seems like a fun approach, I'll see if I can get that working.
4 Views
Open in Slack
PreviousNext
Powered by