Snowflake copy destinations should execute COPY commands in parallel

[edgao]

blocked on #8820

Tell us about the problem you're trying to solve

Currently, destination-snowflake's copy modes will generate multiple files on S3 or GCS, and then for each of those files, execute a COPY command in serial. We should run those commands in parallel to be more time-efficient.

Additionally, the COPY command can actually accept up to 1,000 files. destination-snowflake should take advantage of that capability.

Describe the solution you’d like

• After Snowflake S3 copy destination should use the updated S3StreamCopier, and expose the purgeStagingData option #8820 is completed, update S3StreamCopier and GcsStreamCopier to run copyS3CsvFileIntoTable / copyGcsCsvFileIntoTable in parallel
• Make SnowflakeS3StreamCopier and GcsStreamCopier override copyStagingFileToTemporaryTable to actually process 1,000 files at a time, and have copyS3CsvFileIntoTable / copyGcsCsvFileIntoTable throw RuntimeException. (see RedshiftStreamCopier, which is similar).
  • Write unit tests to verify this behavior - again, see RedshiftStreamCopierTest's usage of verify(db).execute(...)

Describe the alternative you’ve considered or used

Additional context

Are you willing to submit a PR?

👍 😺

[joshuataylor]

That'd be great, but there should also be a configuration set for max size of tmp directory, in case the download of source is faster than the upload to Snowflake. This way the disk doesn't fill up with tmp files whilst uploading to SF if the outbound connection speed is congested.

[edgao]

ooh, yeah that's a great idea! Could you make a separate issue to track that? I suspect it would be a bit separate from the features in this one

also, to double-check - you're referring to the internal staging mode? The S3 and GCS staging methods shouldn't (?) run into disk size limits