Can't set up incremental updates for a Mailchimp Table

  • 26Views
  • Last Post 9 hours ago
  • Topic Is Solved
0
votes
Francois Jacquemart posted this 6 days ago

Hello,

"The source object 'ListMemberActivities' has a composite primary key and cannot be used when the 'Incremental Updates' check box is selected."

This table is 350k+ rows and I need to refresh it incrementally.

I unselected the field "LastUpdate and "Timestamp" but still the same error. Should I drop the table and restart from fresh?

 

Regards,

François

 

Order By: Standard | Newest | Votes
1
votes
Mariia Zaharova posted this 5 days ago

Dear François,

 

This is an expected bahviour. Skyvia works via MailChimp API. The ListMemberActivities table has composite primary key. It is impossible to use Incremental Updates option for tables with composite primary key or without CreatedDate or LastUpdate fields (both fields must be present for this functionality).

Such an objects can be used in Replication packages without Incremental Updates option only.

You can also try using Import package with the Upsert  operation for such tables. 

 

Regards,

Mariia

  • Supported by
  • Francois Jacquemart
0
votes
Francois Jacquemart posted this 5 days ago

HelloMariia,

 

Thank you for you reply. When trying to UPSERT I get this error: 

In order to perform the UPSERT operation, all the primary key columns of the target object 'public."ListMemberActivities"' must be autogenerated.

Regards,

François

0
votes
Dmitriy Muzurov posted this 5 days ago

Dear François,

Thank you for your reply.

The UPSERT is performed in the following way: if a non-null value is provided for your target Id column, the update operation is performed. When a NULL value is provided for the target Id column, the record will be inserted. For more information, please refer to this link.

In the target object it is required to create a new auto-generated ID/PK column. Let's name it id_pk.

To create such a mapping, click the Column button for id_pk and select Target Lookup.

- Click Options and select the "Set null when no match found" check box

- For Lookup Object - select the same PostgreSQL server object to which you import data

- For Result Column - select id_pk

- For Lookup Key Column - select ListId 

- For Column - select ListId

Here is the screenshot 

Should you have any questions, do not hesitate to contact us back.

0
votes
Francois Jacquemart posted this 5 days ago

Hey Maria, is followed your instructions but I get the same error: 

 

Below for the Table ListMembers : 

 

I think I'm missing something with the "In the target object it is required to create a new auto-generated ID/PK column"

 

Thank you for your help,

François

 

0
votes
Dmitriy Muzurov posted this 2 days ago

Hi Francois,

Thank you for the screenshot.

The recommendation to use an import package with UPSERT is related to objects which cannot be used with the incremental updates.

We would advise you use a replication package with the "ListMembers" object.

"ListMembers" object has both fields that are necessary for replication: CreatedDate and LastUpdate. So, it would be more convenient to perform replication with incremental updates rather than UPSERT.

Here is the tutorial for creating a replication package

Should you have any questions, do not hesitate to contact us back.

0
votes
Francois Jacquemart posted this 2 days ago

Hi

I'm having the same issue when trying to UPSERT ListMemberActivities table.

My ListMemberActivities table current status is an initial non incremental replication from Mailchilp to postgresql.

Can someone from Skyvia set up the UPSERT for me?

Thank you,

François

 

 

1
votes
Dmitriy Muzurov posted this yesterday

Dear François,

Thank you for details.

We do not provide end-to-end user package configuration, however can provide general assistance.

EmailHash, ListId, Action and Timestamp are primary key columns.

In order to perform UPSERT, you need to undertake these actions:

1. Remove primary key attribute from these columns (EmailHash, ListId, Action, Timestamp) in your PostgreSQL database.

2. Create a new auto-generated column with type "serial" and primary key attribute.

3. Configure a lookup as shown on the screenshot. Don't forget to select the "Set null when no match found" check box as shown on the step 8 here

Please map four columns - EmailHash, ListId, Action, Timestamp - with a simple column mapping.

We are looking forward to hearing from you, should you require any further assistance.

  • Supported by
  • Francois Jacquemart
0
votes
Francois Jacquemart posted this yesterday

Dear Dmitriy,

Thank you very much, its now running ! 

Best regards,

François

0
votes
Dmitriy Muzurov posted this 12 hours ago

Dear François,

You are very welcome!

We are glad to know the issue is resolved.

Should you have any questions, do not hesitate to contact us back. 

0
votes
Francois Jacquemart posted this 12 hours ago

Hi again,

UPSERT is still running and doesn't seem to work properly

 

As there were originally 4 primery keys (EmailHash, ListId, Action, Timestamp).

Should I use a composite primary key? In this screenshot only Listid is looked up.

Thank you

Regards,

François

1
votes
Dmitriy Muzurov posted this 11 hours ago

Dear François,

Thank you for getting back to us.

There are successful records on the package, however the package produced error message:

"The specified lookup by column 'ListId' = '222e2c8edf' is ambiguous.The 'public."ListMemberActivities"' table must return at most one row for the specified lookup condition."

It seems due to a composite primary key in the source table, there are more than one record for certain ListId values.

Please change your lookup mapping and add lookup key columns for other three fields: Action, EmailHash and Timestamp - 1, 2

We are looking forward to hearing from you, should you require any further assistance.

  • Supported by
  • Francois Jacquemart
0
votes
Francois Jacquemart posted this 9 hours ago

Thank you this make sense I will try that.

I can't cancel the current task. It's running for 16hours now. Is there a way to force the cancellation in order to adjust my package?

 

François

Close