Snowflake (snowsql) support

[kforeman]

I've added support for Snowflake's snowsql CLI.

execute, show records, desc, desc table, and columns all implemented, but Snowflake doesn't yet have an explain equivalent for snowsql yet.

Included an example configuration with instructions on configuring with either single sign-on or simple Snowflake auth.

[tkopets]

Thank you for making this effort to add the support for the new engine! I will try to look into it in next few days.

[tkopets]

How about adding a SNOWSQL_PWD to env_optional and use the password field in connections so it would be used if it is supplied?

would that work with Snowflake CLI?

...
            "env_optional": {
                "SNOWSQL_PWD": "{password}"
            },
...

I don't have an access to a Snowflake instance, so it would be hard to test this implementation.

[kforeman]

Only limitation I've found with this config is that the column autocomplete ignores schemas.

Snowflake pretty much always requires a schema prefix (i.e. <schema>.<table>.<column>), so if you have overlapping table names across schemas (e.g. PROD.TABLE_A and DEV.TABLE_A), then you'll see columns from DEV.TABLE_A when using autocomplete on PROD.TABLE_A.

They can show up both as duplicates if both tables have the column, or misleading autocomplete if e.g. DEV.TABLE_A has debug columns that don't exist in PROD.TABLE_A.

Not a huge deal, but if you have any suggestions on how to fix I'll look into it! I could do it by modifying Completion.py it looks like, but that'd be a much bigger PR.

[kforeman]

I think that should work, but my company's Snowflake account requires SSO so I can't actually test it either! I thought I had seen a public testing Snowflake server somewhere, but now I can't find it... will look into it on the Snowflake forums later this week if I can find some time.

[tkopets]

Re schemas and column completions. The current implementation does not cover all of the cases that it probably should mostly because the initial completion code to be simpler and more performant.

If you can get the column completion with schemas working that would be awesome. If you will have a chance to do so, please do make a separate PR for that, as this unrelated functionality. There is an additional consideration when implementing the column completions qualified by schema.table.column that some DB engines might not have a notion of schemas. As far as I remember Firebird does not have a schemas in our implementation.

[kforeman]

Just tested env_optional with an account that uses Snowflake password auth and it works. Pushed those changes and I think this is good to go!

Agreed that schema support would be a different and much larger PR. I don't have time for that one personally right now, but I might return to it on a rainy weekend sometime.

Thanks so much for this awesome plugin!

[tkopets]

Thank you for your time to dig and test different config options and making SQLTools usable for a wider Snowflake audience. It was a pleasure merging your PR.

[mtxr]

Awesome guys!