Hello meltano < CMN8HELB0|> friends slightly smiling face we Meltano #getting-started

Hello meltano <#CMN8HELB0|> friends :slightly_smil...

jozef_reginac

10/13/2021, 9:10 AM

Hello meltano #CMN8HELB0 friends 🙂 we are setting our project and we have trouble with target-snowflake. There’s a

batch_size

parameter with default value of 100k (within meltano it’s 5k), but when we run it with 150k we get CRITICAL error:

Copy code

target-snowflake-test | CRITICAL maximum number of expressions in a list exceeded, expected at most 16,384, got 27,501

This seems to be a limit of snowflake. Are we missing some setting in our target or how do you overcome this limit in your projects?

dave_lyons

10/13/2021, 3:36 PM

Which target-snowflake are you using? You linked to the

transferwise

variant. The default is the

datamill-co

variant but I found that one had a bug involving unconstrained large integers so I switched to the

Meltano

variant. I haven't changed the

batch_size

yet but was planning to.

dave_lyons

10/13/2021, 7:40 PM

I can confirm I get the same basic error using the <!subteam^S02A7AJFXQC>

target-snowflake

variant if I set

batch_size

to 150K, currently running at 15K. This seems to be because the loader is using an INSERT or a MERGE ... SELECT ... WHERE ... IN(a list with length of

batch_size

jozef_reginac

10/14/2021, 7:41 AM

Thanks for checking, I think so as well. I would assume that it takes the batch and splits it into chunks of 16k selects.

dave_lyons

10/14/2021, 11:08 AM

The issue, AFAICT, is that

target-snowflake

uses the

snowflake-connector-python

package to do UPSERTs, which in turn uses a list and Snowflake has a hard limit of 16384. I don't see anything in the loader that checks if the UPSERT list is greater than 16384 and chunks it - meaning it just sends a list longer than the limit to Snowflake and Snowflake rejects it.

jozef_reginac

10/18/2021, 12:05 PM

Thanks again, I see the same and can’t think of why we are first to realize this. My guess is that many meltano users are using

target-snowflake

but no issue is there. Do you know if this is not usual workflow and they use something else to load data?

aaronsteers

10/18/2021, 11:12 PM

@jozef_reginac and @dave_lyons - Aside from pushing for improved performance, there are reason we would need batch sizes larger than 16K? Ideally, would this justbe a config-time error, and better documented? Or do you see it as a flaw in the implementation? (Just trying to make sure I understand.)

aaronsteers

10/18/2021, 11:35 PM

cc @julian_knight who's trying his hand at an SDK-based target-snowflake

jozef_reginac

10/19/2021, 6:55 AM

I would say that documentation saying max batsh size to be 16k would be enough. Otherwise we set it to 200k and it failed. But preferably, the implementation would take this into account and thus we could gain performance.

dave_lyons

10/19/2021, 1:18 PM

@aaronsteers performance is my main concern as this means our larger tables will take weeks to do an initial sync and we can't throw resources at it to make it go faster. Has there been any exploration of using a COPY instead of an UPSERT strategy for

target-snowflake

julian_knight

10/19/2021, 2:08 PM

Huh, this is good to know about. I didn’t realize there was a

meltano

variant. I have a hunch the 16K limit doesn’t apply when loading from a file using stages? Both the datamill and transferwise variants do this (and the one I’m working on will too)

dave_lyons

10/19/2021, 2:28 PM

@julian_knight I believe that’s correct, using COPY and stage does not have this issue because it does not involve a list, which is the object with the 16384 item limit.

julian_knight

10/19/2021, 5:05 PM

The Meltano variants of the standard targets are deprecated and not recommended for use, they predate the SDK and don’t have a lot of users. I’m sure the team would accept a PR if you want to try fixing this but you’re probably best off using one of the other variants that use stages IMO

dave_lyons

10/19/2021, 5:16 PM

@julian_knight cheers, I’m switching over to the transferwise variant

dave_lyons

10/19/2021, 6:04 PM

Can confirm that

transferwise

variant batches at 100K but for some reason its not doing the last batch of each table, which invariably is less than 100K. i.e. a table has 1,195,000 rows, the target ends up with 1,100,000. I am using

FULL_TABLE

from my Postgres source and I don’t seen anything in the logs that looks like an error… [UPDATE} It looks like the pipeline went through all four tables I had selected, then went back and did the last batch for each one?

5 Views

Open in Slack

Previous Next