Hello! I have question, I found out meltano cannot...
# getting-startedx
xinge_li
09/06/2021, 3:01 PMHello! I have question, I found out meltano cannot read from PostgreSQL view as it lacks primary key (error:
primary key is set to mandatorye
edgar_ramirez_mondragon
09/07/2021, 3:00 PMWise's
tap-postgresx
xinge_li
09/08/2021, 7:01 AMaha, thank you!
xinge_li
09/08/2021, 7:56 AMHi @edgar_ramirez_mondragon it might be the tap we are using and it does not work on views
e
edgar_ramirez_mondragon
09/08/2021, 1:54 PMI saw you already asked in their repo 🙌
x
xinge_li
09/08/2021, 3:03 PMhaha, waiting for their suggestions
a
aaronsteers
09/08/2021, 3:17 PMHi, @xinge_li. Certainly some database taps support views because I've used them in the past to work through odd table schema issues. If the tap-postgres you are using just completely ignores views (which seems a little odd to me), it may be worth trying another variant.
aaronsteers
09/08/2021, 3:21 PMIn terms of views not having primary keys, you have a few options I can think of: you can inject a the key properties declaration into the catalog if it is true for the view (meaning if it really does have a logical unique key), you can disable the primary-key-required setting on the target (if supported) or you can switch to a target which does not require primary keys.
aaronsteers
09/08/2021, 3:21 PMDoes this help?
x
xinge_li
09/09/2021, 6:09 AMYes @aaronsteers thanks for the suggestion! The answer I got from tap-postgres (pipelinewise) is that this tap does not use a setting for primary key required. I will try with your advice on inject key properties.
s
steve_clarke
08/03/2022, 2:30 AMI have a tap tap-sybase and I want to upsert the data based on data from a view. As stated above there is no primary_key defined for a view. I was however able to succeed by defining a primary_key and tricking the tap that it was seeing a table in the catalog rather than a view. This defined for me a primary key in the catalog and I am also seeing the primary key in the schema messages.
Here is what I did using Meltano Environment variables. You can do the same thing in the meltano.yml file.
export TAP_SYBASE__METADATA='{"dwh_extract-dwh_admission_source_tab": {"is-view":false, "replication-method": "FULL_TABLE", "table-key-properties": ["admission_source_code"]}}'