• Cannot create upsert data flow, don't know how to set.

Cannot create upsert data flow, don't know how to set.

  • 84Views
  • Last Post 01 June 2022
0
votes
Rafael Delgado posted this 24 May 2022

Hello.

I'm exploring Skyvia features due to a necessity i have. I need to extract and load data from crm (Hubspot) to a postgres table.

I'm considering using skyvia's data flow (even though it's not included on my current plan) to do this job, but i can't set the upsert operation. I read the Skyvia documentation but it didn't help me to solve where i can set properly this.

PS: The "primary key" i'm using on postgres is the Hubspot Company ID (it has not the pk constraint btw), and i expect Skyvia to insert records that doesn't match any of the existing company ids on the table and update the columns of records that matches company id with the existing ones in the table.

Could you please help me with it?

Also, if i succeed on this task, could i have a test time to see if the functionality satisfy my need properly?

Order By: Standard | Newest | Votes
0
votes
Rafael Delgado posted this 26 May 2022

No idea here?

1
votes
Mariia Zaharova posted this 27 May 2022

Hello Rafael,

 

Sorry for the delay with this case.

We are working on it and will answer you as soon as possible.

 

Best regards,

Mariia

  • Supported by
  • Rafael Delgado
1
votes
Olena Romanchuk posted this 30 May 2022

Hello Rafael,

Could you please share the problem package Id?

To perform the Upsert operation the object should have the primary key to uniquely identify the records. 

You should add a new autogenerated column for the primary key in your PostgreSQL database.
Then you can implement your scenario using the Upsert operation in the import package. 

Or you can still use Data Flow and perform the upsert operation with this  tutorial as an example:

https://docs.skyvia.com/data-integration/tutorials/data-flow-tutorials/updating-existing-records-and-inserting-new-ones.html

Should you have any questions please feel free to contact us.

Best regards,

Olena

 

Technical Support Engineer

  • Supported by
  • Rafael Delgado
0
votes
Rafael Delgado posted this 30 May 2022

Thanks for the answer.

I want to perform an upsert operation Hubspot (Companies) -> Postgres where the 'Company ID' is a field from Hubspot that identifies uniquely each register. My idea is to use this field as a "primary key" on the PostgreSQL table, even it's not explicitly declared as a PK (i mean, it has not the PK constraint on the table).

My question here is: I can't perform the upsert operation without the primary key constraint explicitly declared on the postgreSQL table?

 

I created two packages trying to achieve this goal:

1- Package https://app.skyvia.com/#/103855/packages/166088 but the 'company id' hubspot field didn't appear on the returning tab (where i guess it should be mapped the pk - fk).

2- I've tried using Data Flow (https://app.skyvia.com/#/103855/packages/166107) but didn't understand the upsert logic to be implemented.

0
votes
Olena Romanchuk posted this 01 June 2022

Hello Rafael, 

UPSERT operation requires the primary key to be auto-generated. In Skyvia, UPSERT determines what action to perform in the following way: if a Null value is specified for the ID or primary key, UPSERT operation inserts the record, and if a non-null value is specified, UPSERT operation tries to update the record with the specified ID or primary key. Skyvia does not actually check if such a record exists, and if you provide invalid ID/PK values, it will result in failed records.

Thus the upsert operation without the primary key constraint explicitly declared on the PostgreSQL table won’t work.


Best regards,

Olena

 

Technical Support Engineer

New Ticket
Categories
Popular Tags
Close