Hey everyone how to set primary key properties in a stream t Meltano #troubleshooting

Hey everyone, how to set primary key properties in...

revanth_peddi

07/15/2021, 6:40 AM

Hey everyone, how to set primary key properties in a stream (transferwise-tap-postgres to transferwise-target-postgres). I am facing the following issue

Primary key is set to mandatory but not defined in the XXXX stream. Exception: key_properties field is required

charley_guillaume

07/15/2021, 11:03 AM

You can set it in the catalog.json file but also when defining the schema of your stream: https://hub.meltano.com/singer/spec#schemas

douwe_maan

07/15/2021, 3:34 PM

@revanth_peddi tap-postgres should detect primary keys automatically. Does the table you’re pulling from have a primary key set?

revanth_peddi

07/15/2021, 3:52 PM

The primary_key isn’t

id

, it’s some custom column name

douwe_maan

07/15/2021, 3:57 PM

So the source tables all have primary keys? They don’t have to be

id

. What is the type of the primary key?

revanth_peddi

07/15/2021, 4:12 PM

it something like schemaid and it’s data type is int

revanth_peddi

07/15/2021, 4:14 PM

What if I want to use combination of two columns as my primary key?

revanth_peddi

07/15/2021, 4:15 PM

I was trying

meltano config tap-postgres-XXXX set _metadata public-XXXX table-key-properties '["XXXX", "XXXX"]'

douwe_maan

07/15/2021, 4:16 PM

I was just looking into that as well 🙂

douwe_maan

07/15/2021, 4:17 PM

Judging by https://github.com/transferwise/pipelinewise-tap-postgres/blob/ec7d5f08d0fc8070914ca5331e5d2834e447472e/tap_postgres/discovery_utils.py#L149 and https://github.com/transferwise/pipelinewise-tap-postgres/blob/8c4732ee398f6124a616f929c6f85c5499366149/tap_postgres/sync_strategies/common.py#L26, that should work

douwe_maan

07/15/2021, 4:17 PM

Can you share what the result of setting that looks like in

meltano.yml

, so that I can verify it looks correct?

revanth_peddi

07/15/2021, 4:33 PM

Copy code

- name: tap-postgres-xxxxx
    inherit_from: tap-postgres
    model_name:
    config:
      port:
      user:
      dbname:
      default_replication_method: LOG_BASED
      host:
      ssl:
      streams:
      - name: stream_xxxxx
        table: public-xxxxx
        datetime_key: updatedat
    select:
    - public-xxxxx
    metadata:
      public-xxxxx:
        replication-key: updatedat
        table-key-properties:
        - schemaid

douwe_maan

07/15/2021, 4:36 PM

Ok that looks like it should work

douwe_maan

07/15/2021, 4:40 PM

Let me know if it does 🙂

revanth_peddi

07/15/2021, 4:40 PM

It isn’t working

douwe_maan

07/15/2021, 4:43 PM

Still the same error? Do the

table-key-properties

show up in

meltano elt … --dump=catalog

as expected?

revanth_peddi

07/15/2021, 4:48 PM

revanth_peddi

07/15/2021, 4:48 PM

Copy code

"breadcrumb": [],
          "metadata": {
            "table-key-properties": [],

douwe_maan

07/15/2021, 4:49 PM

Weird. You’re not providing an explicit

catalog: path

meltano.yml

, are you?

douwe_maan

07/15/2021, 4:50 PM

I see that

tap-postgres-xxxxx

inherits from

tap-postgres

, is that defined separately?

revanth_peddi

07/15/2021, 4:51 PM

No, I am not providing any catalog path

revanth_peddi

07/15/2021, 4:51 PM

yes, it’s defined seperately

douwe_maan

07/15/2021, 4:51 PM

And

public-xxxxx

matches the

tap_stream_id

for the stream in question?

revanth_peddi

07/15/2021, 4:53 PM

yes tap_stream_id and

public-xxxxx

matches

douwe_maan

07/15/2021, 4:55 PM

Is the

select: [public-xxxxx]

resulting in the

selected:true

metadata being set correctly on that stream, but not any others?

douwe_maan

07/15/2021, 4:55 PM

I don’t know why

select

would work but

metadata

wouldn’t 😕

revanth_peddi

07/15/2021, 5:03 PM

Now, i see the table-key-properties in catalog file but the error is persistant

revanth_peddi

07/15/2021, 5:04 PM

Copy code

[2021-07-15 17:02:25,034] {subprocess.py:79} INFO - target-postgres-staging | time=2021-07-15 17:02:25 name=target_postgres level=CRITICAL message=Primary key is set to mandatory but not defined in the [public-XXXXX] stream
[2021-07-15 17:02:25,036] {subprocess.py:79} INFO - target-postgres-staging | Traceback (most recent call last):
[2021-07-15 17:02:25,037] {subprocess.py:79} INFO - target-postgres-staging |   File "/project/.meltano/loaders/target-postgres-staging/venv/bin/target-postgres", line 8, in <module>
[2021-07-15 17:02:25,037] {subprocess.py:79} INFO - target-postgres-staging |     sys.exit(main())
[2021-07-15 17:02:25,038] {subprocess.py:79} INFO - target-postgres-staging |   File "/project/.meltano/loaders/target-postgres-staging/venv/lib/python3.6/site-packages/target_postgres/__init__.py", line 373, in main
[2021-07-15 17:02:25,039] {subprocess.py:79} INFO - target-postgres-staging |     persist_lines(config, singer_messages)
[2021-07-15 17:02:25,039] {subprocess.py:79} INFO - target-postgres-staging |   File "/project/.meltano/loaders/target-postgres-staging/venv/lib/python3.6/site-packages/target_postgres/__init__.py", line 209, in persist_lines
[2021-07-15 17:02:25,039] {subprocess.py:79} INFO - target-postgres-staging |     raise Exception("key_properties field is required")
[2021-07-15 17:02:25,039] {subprocess.py:79} INFO - target-postgres-staging | Exception: key_properties field is required
[2021-07-15 17:02:25,102] {subprocess.py:79} INFO - meltano                 | Loading failed (1): Exception: key_properties field is required

douwe_maan

07/15/2021, 5:21 PM

That’s very weird. Can you run with

meltano --log-level=debug elt …

and look for the line that starts with

tap-postgres-xxxxx (out)

that writes the

SCHEMA

message that triggers that error in the target? I want to see if

key_properties

is actually missing/wrong

revanth_peddi

07/15/2021, 5:40 PM

tap-postgres-XXXXX (out) | {“type”: “STATE”, “value”: {“currently_syncing”: “fund-msfund”, “bookmarks”: {“public-XXXXXX”: {“last_replication_method”: “LOG_BASED”, “lsn”: 5688452019168, “version”: 1626261638498, “xmin”: null}, “public-XXXXXX”: {“last_replication_method”: “LOG_BASED”, “lsn”: 5688452019168, “version”: 1626261649757, “xmin”: null}, “public-XXXXXX”: {“last_replication_method”: “FULL_TABLE”, “version”: 1626346077584, “xmin”: null}, “public-XXXXXX”: {“last_replication_method”: “LOG_BASED”, “lsn”: 5688533226336, “version”: 1626370711148}}}}

douwe_maan

07/15/2021, 5:43 PM

Do you see a

SCHEMA

revanth_peddi

07/15/2021, 5:46 PM

tap-postgres-XXXXX (out) | {“type”: “SCHEMA”, ...... ,“key_properties”: [], “bookmark_properties”: []}

revanth_peddi

07/15/2021, 5:47 PM

here it’s blank

revanth_peddi

07/15/2021, 5:48 PM

but in catalog I see it as

Copy code

{
      "table_name": "XXXXX",
      "stream": "XXXXX",
      "metadata": [
        {
          "breadcrumb": [],
          "metadata": {
            "table-key-properties": [
              "schemaid"
            ],

douwe_maan

07/15/2021, 5:57 PM

That’s so weird. It suggests that https://github.com/transferwise/pipelinewise-tap-postgres/blob/8c4732ee398f6124a616f929c6f85c5499366149/tap_postgres/sync_strategies/common.py#L26 isn’t working as we expect

douwe_maan

07/15/2021, 5:58 PM

I think I know what’s going on, @edward_smith ran into the same thing recently: https://github.com/transferwise/pipelinewise-tap-postgres/issues/101

douwe_maan

07/15/2021, 5:58 PM

If you point your

pip_url

for

tap-postgres

at his PR branch, it should work as expected: https://github.com/transferwise/pipelinewise-tap-postgres/pull/102

douwe_maan

07/15/2021, 5:58 PM

I feel a bit dumb for not having made the connection until now, since he and I were talking about the exact same issue a few weeks ago!

revanth_peddi

07/16/2021, 5:25 AM

I have pointed it to PR branch, but stream is skipping the table

Copy code

There are no columns selected for stream public-XXXXX, , skipping it

douwe_maan

07/16/2021, 2:50 PM

That’s odd. @edward_smith Any idea what may be going on here?

edward_smith

07/16/2021, 3:04 PM

Hi @revanth_peddi, I've been experiencing this same issue, and actually plan to spend today working on it. The branch @douwe_maan pointed you two actually fixes a separate problem I was facing that deals with whether or not fields are marked as

selected

. So, two things I'd like to work through: (1) The problem of overriding the primary key of a postgres table to that the target understands how to eliminate duplicates when inserting. Is this your use case? (2) Why are you getting no selected columns when using my branch? The default behavior of pipelinewise-tap-postgres seems to be select all tables, regardless of the

selected

property in the catalog. My branch ensures that non-discoverable metadata is retained in the catalog after discovery. Can you double check your meltano.yml for me? Above you have:

Copy code

select:
    - public-xxxxx

But I think you need this to be:

Copy code

select:
    - public-xxxxx.*

sumukh_ghodke

07/22/2021, 12:59 PM

hey @douwe_maan @edward_smith thanks for all the help! I'm working with @revanth_peddi on this issue and have a failing test that might help debug the issue. I think the problem is with

key-properties

metadata attribute containing a list, rather than a literal value. What do you think?

test_stream_metadata_update.py

edward_smith

07/22/2021, 3:01 PM

This is good work! I have thought that the combination key may also be a problem. When this fails, what is the value of

table-key-properties

sumukh_ghodke

07/22/2021, 3:26 PM

It is empty

[]

, because its picking it up from the discovery metadata.

edward_smith

07/22/2021, 3:31 PM

Are you setting an override value in

meltano.yml

sumukh_ghodke

07/22/2021, 3:36 PM

I'm setting the override value in catalog.json and passing the catalog using the

--catalog

argument like this

meltano invoke tap-postgres --catalog catalog.json

3 Views

Open in Slack

Previous Next