feat: Add support for creation of streams on external tables

[adamantike]

Allow the creation of STREAMS on EXTERNAL TABLEs.

Test Plan

• acceptance tests
• unit tests

References

• Initial version of this PR taken as-is from feat: Add option to create streams which reference EXTERNAL TABLEs #661
• docs.snowflake.com/en/sql-reference/sql/create-stream.html#creating-an-insert-only-stream-on-an-external-table

[adamantike]

@alldoami, gentle ping, considering you reviewed #661

[alldoami]

oops sorry! Thanks for the reminder!

[alldoami]

/ok-to-test sha=c4214d2

[github-actions]

Integration tests failure for c4214d2

[alldoami]

/ok-to-test sha=c1a2d10

[github-actions]

Integration tests failure for c1a2d10

[alldoami]

/ok-to-test sha=ce964ea

[github-actions]

Integration tests failure for ce964ea

[alldoami]

/ok-to-test sha=b1fc73b

[github-actions]

Integration tests failure for b1fc73b

[sfc-gh-swinkler]

@adamantike I really like this, but request a change:

When you do 'show tables like 'TABLE_NAME'; there is an is_external column which indicates if it is external or not. So instead of introducing a new boolean variable, just read the table and determine whether it is external or no.

[adamantike]

@sfc-gh-swinkler, I really liked your recommendation, the implementation is cleaner and simpler in comparison!

I have refactored the PR to go with that approach, and would appreciate a review to know if the table information retrieval is using the function you expected.

[sfc-gh-swinkler]

/ok-to-test sha=9cc539d

[github-actions]

Integration tests failure for 9cc539d

[sfc-gh-swinkler]

=== CONT  TestAcc_Stream
    stream_acceptance_test.go:16: Step 3/4 error: Error running apply: exit status 1
        Error: error creating externalTable STREAM_ON_EXTERNAL_TABLE: 001003 (42000): SQL compilation error:
        syntax error line 1 at position 219 unexpected ''CreatedDate''.
          on terraform_plugin_test.tf line 25, in resource "snowflake_external_table" "test_external_stream_table":
          25: resource "snowflake_external_table" "test_external_stream_table" {

Hi @adamantike can you please fix? One of acceptance tests is failing

[adamantike]

I have added a fix for that syntax error, but it seems there's no way to test external tables within acceptance tests at the moment. Fixing that, and disabling auto_refresh, makes the test reach a point where it requires a valid S3 bucket:

=== CONT  TestAcc_Stream
    stream_acceptance_test.go:15: Step 1/1 error: Error running apply: exit status 1

        Error: error creating externalTable STREAM_ON_EXTERNAL_TABLE: 091017 (22000): S3 bucket 'com.example.bucket' does not exist or not authorized.

          on terraform_plugin_test.tf line 25, in resource "snowflake_external_table" "test_external_stream_table":
          25: resource "snowflake_external_table" "test_external_stream_table" {

I found that the other acceptance tests for External tables are behind a flag, so I duplicated that same behavior here. (reference: #426)

[sfc-gh-swinkler]

/ok-to-test sha=b6a786b

[github-actions]

Integration tests success for b6a786b

[adamantike]

Now that acceptance tests are also fixed, I'm open to any feedback on this PR, so it can be merged!

[sfc-gh-swinkler]

LGTM

[sfc-gh-swinkler]

@adamantike thanks for your contribution! This is great 👍