s3 compat external stage creation

[y-tee]

Hello, for s3 compat external stage that storage integration cannot be created, where it has to be reliant on secret id and secret key, how do i create the external stage in snowddl?
https://docs.snowflake.com/en/user-guide/data-load-s3-compatible-storage

[littleK0i]

Initially I had no plans to support this feature.

It suffers from numerous problems:

1. You deal with plain text keys, which is bad for security.
2. There is no way to compare "old key" vs "new key" in the config due to lack of this property in metadata. The only "solution" is to force-update all stage keys on every run.
3. You still have to move data from external S3 compatible storage to AWS, since Snowflake compute is hosted in AWS.
4. Performance of external S3 compatible storages is questionable when it comes to large amounts of data.

In theory, parameters ENDPOINT and CREDENTIALS can be added for stages. With CREDENTIALS working the same way as ENCRYPTION. But does it even make sense to do that?

Personally I would simply copy data to traditional AWS S3 bucket first and import data into Snowflake from this bucket. It adds an extra step, but it helps to avoid lots of hassle.

Auditing problem is especially bad. Any serious security audit will likely take you down for plain text keys.

[littleK0i]

Could you run commands to create s3compat stage manually and see if everything works correctly? And share the commands afterwards.

I think we can add these extra properties, but it is unlikely for us to establish more 3rd party accounts for automated testing of this feature. I just want to make sure there are no more hidden issues of caveats before adding it.

Thank you!

[y-tee]

this is the command that i ran and i was able to unload files into the bucket with COPY INTO command

CREATE STAGE <external_stage_name>
  URL = 's3compat://<bucket_name>/<prefix>/'
  ENDPOINT = '<xxx.com>'
  CREDENTIALS = (AWS_KEY_ID = '<key_id>' AWS_SECRET_KEY = '<secret_key>')

Just for your information, custom endpoint needs to be raised support ticket to be whitelisted with snowflake before this can work.

Many thanks for adding this feature!

[littleK0i]

Ok, properties endpoint and credentials are available in 0.66.2+. I cannot fully test it on my side, so no documentation and no tests yet.

There is also new CLI parameter --refresh-stage-credentials. Since credentials are not exposed in metadata, the only way to change it is to enforce full refresh on all stages with this parameter. It works exactly the same way as existing --refresh-stage-encryption.

[y-tee]

hello, we have been using this feature for awhile and it has been working great.

However, i found a small typo bug here that will prevent the --refresh-stage-credentials and raised a pr: #322, let me know if you have any concerns/suggestion

[littleK0i]

@y-tee , thank you! Fixed in 0.66.5+.