Repository: chanzuckerberg/terraform-provider-snowflake
Pull request #655 (2021-08-20 21:15:44)

feat: Add INSERT_ONLY option to streams
<!-- Feel free to delete comments as you fill this in -->

<!-- summary of changes -->

Add `INSERT_ONLY` as a DDL option to `streams` resource in support of [insert only streams on external tables](https://docs.snowflake.com/en/sql-reference/sql/create-stream.html#creating-an-insert-only-stream-on-an-external-table).

## Test Plan
<!-- detail ways in which this PR has been tested or needs to be tested -->
* [x] unit tests
<!-- add more below if you think they are relevant -->
  - Note: I didn't see any validation to help avoid conflicting settings in other modules, (*e.g.* `APPEND_ONLY` and `INSERT_ONLY`) so didn't add any here as I imagine that is a purposeful decision. 

## References
<!-- issues documentation links, etc  -->

* https://docs.snowflake.com/en/sql-reference/sql/create-stream.html#creating-an-insert-only-stream-on-an-external-table

alldoami: /ok-to-test sha=5df4f89
github-actions[bot]: Integration tests failure for [5df4f89](https://github.com/chanzuckerberg/terraform-provider-snowflake/actions/runs/1152133718)
momer: Sorry about that - didn't realize there were other changes needed to be made in `resources` patching now.
momer: @alldoami these tests should be ready trigger again:

```sh
➜  terraform-provider-snowflake git:(feature/add-insert-only-option-snowflake-streams) SKIP_MANAGED_ACCOUNT_TEST=1 TF_ACC=1 go test -run '.*?Stream.*' -v -coverprofile=coverage.txt -covermode=atomic ./...
?       github.com/chanzuckerberg/terraform-provider-snowflake  [no test files]
=== RUN   TestAccStreams
=== PAUSE TestAccStreams
=== CONT  TestAccStreams
    streams_acceptance_test.go:17: Step 1/1 error: Error running pre-apply refresh: exit status 1
        
        Error: Missing required argument
        
        The argument "username" is required, but was not set.
        
        Error: Missing required argument
        
        The argument "account" is required, but was not set.
--- FAIL: TestAccStreams (0.50s)
FAIL
coverage: 4.2% of statements
FAIL    github.com/chanzuckerberg/terraform-provider-snowflake/pkg/datasources  0.883s
?       github.com/chanzuckerberg/terraform-provider-snowflake/pkg/db   [no test files]
testing: warning: no tests to run
PASS
coverage: 0.0% of statements
ok      github.com/chanzuckerberg/terraform-provider-snowflake/pkg/provider     0.252s  coverage: 0.0% of statements [no tests to run]
=== RUN   TestStreamIDFromString
--- PASS: TestStreamIDFromString (0.00s)
=== RUN   TestStreamStruct
--- PASS: TestStreamStruct (0.00s)
=== RUN   TestStreamOnTableIDFromString
--- PASS: TestStreamOnTableIDFromString (0.00s)
=== RUN   TestAcc_Stream
=== PAUSE TestAcc_Stream
=== RUN   TestAccStreamGrant_basic
    stream_grant_acceptance_test.go:21: Step 1/1 error: Error running pre-apply refresh: exit status 1
        
        Error: Missing required argument
        
        The argument "username" is required, but was not set.
        
        Error: Missing required argument
        
        The argument "account" is required, but was not set.
--- FAIL: TestAccStreamGrant_basic (0.46s)
=== RUN   TestAccStreamGrante_future
    stream_grant_acceptance_test.go:44: Step 1/1 error: Error running pre-apply refresh: exit status 1
        
        Error: Missing required argument
        
        The argument "account" is required, but was not set.
        
        Error: Missing required argument
        
        The argument "username" is required, but was not set.
--- FAIL: TestAccStreamGrante_future (0.46s)
=== RUN   TestStreamGrant
--- PASS: TestStreamGrant (0.00s)
=== RUN   TestStreamGrantCreate
--- PASS: TestStreamGrantCreate (0.00s)
=== RUN   TestStreamGrantRead
--- PASS: TestStreamGrantRead (0.00s)
=== RUN   TestFutureStreamGrantCreate
--- PASS: TestFutureStreamGrantCreate (0.00s)
=== RUN   TestStream
--- PASS: TestStream (0.00s)
=== RUN   TestStreamCreate
--- PASS: TestStreamCreate (0.00s)
=== RUN   TestStreamRead
--- PASS: TestStreamRead (0.00s)
=== RUN   TestStreamDelete
--- PASS: TestStreamDelete (0.00s)
=== RUN   TestStreamUpdate
--- PASS: TestStreamUpdate (0.00s)
=== CONT  TestAcc_Stream
    stream_acceptance_test.go:15: Step 1/1 error: Error running pre-apply refresh: exit status 1
        
        Error: Missing required argument
        
        The argument "username" is required, but was not set.
        
        Error: Missing required argument
        
        The argument "account" is required, but was not set.
--- FAIL: TestAcc_Stream (0.44s)
FAIL
coverage: 5.5% of statements
FAIL    github.com/chanzuckerberg/terraform-provider-snowflake/pkg/resources    1.726s
=== RUN   TestStreamCreate
--- PASS: TestStreamCreate (0.00s)
=== RUN   TestStreamChangeComment
--- PASS: TestStreamChangeComment (0.00s)
=== RUN   TestStreamRemoveComment
--- PASS: TestStreamRemoveComment (0.00s)
=== RUN   TestStreamDrop
--- PASS: TestStreamDrop (0.00s)
=== RUN   TestStreamShow
--- PASS: TestStreamShow (0.00s)
PASS
coverage: 1.7% of statements
ok      github.com/chanzuckerberg/terraform-provider-snowflake/pkg/snowflake    0.298s  coverage: 1.7% of statements
?       github.com/chanzuckerberg/terraform-provider-snowflake/pkg/testhelpers  [no test files]
testing: warning: no tests to run
PASS
coverage: 0.0% of statements
ok      github.com/chanzuckerberg/terraform-provider-snowflake/pkg/validation   0.174s  coverage: 0.0% of statements [no tests to run]
?       github.com/chanzuckerberg/terraform-provider-snowflake/pkg/version      [no test files]
FAIL
```
alldoami: /ok-to-test sha=dd06679
alldoami: Can you run `make docs` :)
github-actions[bot]: Integration tests success for [dd06679](https://github.com/chanzuckerberg/terraform-provider-snowflake/actions/runs/1160089925)
momer: Hey @alldoami ran `make docs`! Thanks again for bearing with me on this
alldoami: /ok-to-test sha=84a6234
github-actions[bot]: Integration tests success for [84a6234](https://github.com/chanzuckerberg/terraform-provider-snowflake/actions/runs/1160355144)

Patch:
diff --git a/docs/data-sources/functions.md b/docs/data-sources/functions.md
new file mode 100644
index 00000000..e8fbeb93
--- /dev/null
+++ b/docs/data-sources/functions.md
@@ -0,0 +1,50 @@
+---
+# generated by https://github.com/hashicorp/terraform-plugin-docs
+page_title: "snowflake_functions Data Source - terraform-provider-snowflake"
+subcategory: ""
+description: |-
+  
+---
+
+# snowflake_functions (Data Source)
+
+
+
+## Example Usage
+
+```terraform
+data "snowflake_functions" "current" {
+    database = "MYDB"
+    schema   = "MYSCHEMA"
+}
+```
+
+<!-- schema generated by tfplugindocs -->
+## Schema
+
+### Required
+
+- **database** (String) The database from which to return the schemas from.
+- **schema** (String) The schema from which to return the functions from.
+
+### Optional
+
+- **id** (String) The ID of this resource.
+
+### Read-Only
+
+- **functions** (List of Object) The functions in the schema (see [below for nested schema](#nestedatt--functions))
+
+<a id="nestedatt--functions"></a>
+### Nested Schema for `functions`
+
+Read-Only:
+
+- **argument_types** (List of String)
+- **comment** (String)
+- **database** (String)
+- **name** (String)
+- **return_type** (String)
+- **schema** (String)
+
+
diff --git a/docs/data-sources/procedures.md b/docs/data-sources/procedures.md
new file mode 100644
index 00000000..403d28b6
--- /dev/null
+++ b/docs/data-sources/procedures.md
@@ -0,0 +1,50 @@
+---
+# generated by https://github.com/hashicorp/terraform-plugin-docs
+page_title: "snowflake_procedures Data Source - terraform-provider-snowflake"
+subcategory: ""
+description: |-
+  
+---
+
+# snowflake_procedures (Data Source)
+
+
+
+## Example Usage
+
+```terraform
+data "snowflake_procedures" "current" {
+    database = "MYDB"
+    schema   = "MYSCHEMA"
+}
+```
+
+<!-- schema generated by tfplugindocs -->
+## Schema
+
+### Required
+
+- **database** (String) The database from which to return the schemas from.
+- **schema** (String) The schema from which to return the procedures from.
+
+### Optional
+
+- **id** (String) The ID of this resource.
+
+### Read-Only
+
+- **procedures** (List of Object) The procedures in the schema (see [below for nested schema](#nestedatt--procedures))
+
+<a id="nestedatt--procedures"></a>
+### Nested Schema for `procedures`
+
+Read-Only:
+
+- **argument_types** (List of String)
+- **comment** (String)
+- **database** (String)
+- **name** (String)
+- **return_type** (String)
+- **schema** (String)
+
+
diff --git a/docs/resources/stream.md b/docs/resources/stream.md
index e303aa78..88440de2 100644
--- a/docs/resources/stream.md
+++ b/docs/resources/stream.md
@@ -22,6 +22,7 @@ resource snowflake_stream stream {
 
   on_table    = "table"
   append_only = false
+  insert_only = false
 
   owner = "role1"
 }
@@ -41,6 +42,7 @@ resource snowflake_stream stream {
 - **append_only** (Boolean) Type of the stream that will be created.
 - **comment** (String) Specifies a comment for the stream.
 - **id** (String) The ID of this resource.
+- **insert_only** (Boolean) Create an insert only stream type.
 - **on_table** (String) Name of the table the stream will monitor.
 - **show_initial_rows** (Boolean) Specifies whether to return all existing rows in the source table as row inserts the first time the stream is consumed.
 
diff --git a/examples/resources/snowflake_stream/resource.tf b/examples/resources/snowflake_stream/resource.tf
index 3668d37d..d490d343 100644
--- a/examples/resources/snowflake_stream/resource.tf
+++ b/examples/resources/snowflake_stream/resource.tf
@@ -7,6 +7,7 @@ resource snowflake_stream stream {
 
   on_table    = "table"
   append_only = false
+  insert_only = false
 
   owner = "role1"
 }
diff --git a/pkg/resources/stream.go b/pkg/resources/stream.go
index f97acd6a..f8bc7aaf 100644
--- a/pkg/resources/stream.go
+++ b/pkg/resources/stream.go
@@ -55,6 +55,13 @@ var streamSchema = map[string]*schema.Schema{
 		Default:     false,
 		Description: "Type of the stream that will be created.",
 	},
+	"insert_only": {
+		Type:        schema.TypeBool,
+		Optional:    true,
+		ForceNew:    true,
+		Default:     false,
+		Description: "Create an insert only stream type.",
+	},
 	"show_initial_rows": {
 		Type:        schema.TypeBool,
 		Optional:    true,
@@ -169,6 +176,7 @@ func CreateStream(d *schema.ResourceData, meta interface{}) error {
 	name := d.Get("name").(string)
 	onTable := d.Get("on_table").(string)
 	appendOnly := d.Get("append_only").(bool)
+	insertOnly := d.Get("insert_only").(bool)
 	showInitialRows := d.Get("show_initial_rows").(bool)
 
 	builder := snowflake.Stream(name, database, schema)
@@ -180,6 +188,7 @@ func CreateStream(d *schema.ResourceData, meta interface{}) error {
 
 	builder.WithOnTable(resultOnTable.DatabaseName, resultOnTable.SchemaName, resultOnTable.OnTableName)
 	builder.WithAppendOnly(appendOnly)
+	builder.WithInsertOnly(insertOnly)
 	builder.WithShowInitialRows(showInitialRows)
 
 	// Set optionals
@@ -247,6 +256,11 @@ func ReadStream(d *schema.ResourceData, meta interface{}) error {
 		return err
 	}
 
+	err = d.Set("insert_only", stream.InsertOnly)
+	if err != nil {
+		return err
+	}
+
 	err = d.Set("show_initial_rows", stream.ShowInitialRows)
 	if err != nil {
 		return err
diff --git a/pkg/snowflake/stream.go b/pkg/snowflake/stream.go
index 7e51e6ac..429d8de4 100644
--- a/pkg/snowflake/stream.go
+++ b/pkg/snowflake/stream.go
@@ -17,6 +17,7 @@ type StreamBuilder struct {
 	schema          string
 	onTable         string
 	appendOnly      bool
+	insertOnly      bool
 	showInitialRows bool
 	comment         string
 }
@@ -57,6 +58,11 @@ func (sb *StreamBuilder) WithAppendOnly(b bool) *StreamBuilder {
 	return sb
 }
 
+func (sb *StreamBuilder) WithInsertOnly(b bool) *StreamBuilder {
+	sb.insertOnly = b
+	return sb
+}
+
 func (sb *StreamBuilder) WithShowInitialRows(b bool) *StreamBuilder {
 	sb.showInitialRows = b
 	return sb
@@ -92,6 +98,8 @@ func (sb *StreamBuilder) Create() string {
 
 	q.WriteString(fmt.Sprintf(` APPEND_ONLY = %v`, sb.appendOnly))
 
+	q.WriteString(fmt.Sprintf(` INSERT_ONLY = %v`, sb.insertOnly))
+
 	q.WriteString(fmt.Sprintf(` SHOW_INITIAL_ROWS = %v`, sb.showInitialRows))
 
 	return q.String()
@@ -125,6 +133,7 @@ type descStreamRow struct {
 	Owner           sql.NullString `db:"owner"`
 	Comment         sql.NullString `db:"comment"`
 	AppendOnly      bool           `db:"append_only"`
+	InsertOnly      bool           `db:"insert_only"`
 	ShowInitialRows bool           `db:"show_initial_rows"`
 	TableName       sql.NullString `db:"table_name"`
 	Type            sql.NullString `db:"type"`


Test patch:
diff --git a/pkg/resources/stream_acceptance_test.go b/pkg/resources/stream_acceptance_test.go
index 883463a4..d271c377 100644
--- a/pkg/resources/stream_acceptance_test.go
+++ b/pkg/resources/stream_acceptance_test.go
@@ -24,6 +24,7 @@ func TestAcc_Stream(t *testing.T) {
 					resource.TestCheckResourceAttr("snowflake_stream.test_stream", "on_table", fmt.Sprintf("%s.%s.%s", accName, accName, "STREAM_ON_TABLE")),
 					resource.TestCheckResourceAttr("snowflake_stream.test_stream", "comment", "Terraform acceptance test"),
 					checkBool("snowflake_stream.test_stream", "append_only", false),
+					checkBool("snowflake_stream.test_stream", "insert_only", false),
 					checkBool("snowflake_stream.test_stream", "show_initial_rows", false),
 				),
 			},
diff --git a/pkg/resources/stream_test.go b/pkg/resources/stream_test.go
index 48ffd4fb..7a532049 100644
--- a/pkg/resources/stream_test.go
+++ b/pkg/resources/stream_test.go
@@ -28,12 +28,13 @@ func TestStreamCreate(t *testing.T) {
 		"comment":           "great comment",
 		"on_table":          "target_db.target_schema.target_table",
 		"append_only":       true,
+		"insert_only":       false,
 		"show_initial_rows": true,
 	}
 	d := stream(t, "database_name|schema_name|stream_name", in)
 
 	WithMockDb(t, func(db *sql.DB, mock sqlmock.Sqlmock) {
-		mock.ExpectExec(`CREATE STREAM "database_name"."schema_name"."stream_name" ON TABLE "target_db"."target_schema"."target_table" COMMENT = 'great comment' APPEND_ONLY = true SHOW_INITIAL_ROWS = true`).WillReturnResult(sqlmock.NewResult(1, 1))
+		mock.ExpectExec(`CREATE STREAM "database_name"."schema_name"."stream_name" ON TABLE "target_db"."target_schema"."target_table" COMMENT = 'great comment' APPEND_ONLY = true INSERT_ONLY = false SHOW_INITIAL_ROWS = true`).WillReturnResult(sqlmock.NewResult(1, 1))
 		expectStreamRead(mock)
 		err := resources.CreateStream(d, db)
 		r.NoError(err)
@@ -118,6 +119,7 @@ func TestStreamUpdate(t *testing.T) {
 		"comment":     "new stream comment",
 		"on_table":    "target_table",
 		"append_only": true,
+		"insert_only": false,
 	}
 
 	d := stream(t, "database_name|schema_name|stream_name", in)
diff --git a/pkg/snowflake/stream_test.go b/pkg/snowflake/stream_test.go
index 9ebadf66..b8a5ad86 100644
--- a/pkg/snowflake/stream_test.go
+++ b/pkg/snowflake/stream_test.go
@@ -11,16 +11,19 @@ func TestStreamCreate(t *testing.T) {
 	s := Stream("test_stream", "test_db", "test_schema")
 
 	s.WithOnTable("test_db", "test_schema", "test_target_table")
-	r.Equal(s.Create(), `CREATE STREAM "test_db"."test_schema"."test_stream" ON TABLE "test_db"."test_schema"."test_target_table" APPEND_ONLY = false SHOW_INITIAL_ROWS = false`)
+	r.Equal(s.Create(), `CREATE STREAM "test_db"."test_schema"."test_stream" ON TABLE "test_db"."test_schema"."test_target_table" APPEND_ONLY = false INSERT_ONLY = false SHOW_INITIAL_ROWS = false`)
 
 	s.WithComment("Test Comment")
-	r.Equal(s.Create(), `CREATE STREAM "test_db"."test_schema"."test_stream" ON TABLE "test_db"."test_schema"."test_target_table" COMMENT = 'Test Comment' APPEND_ONLY = false SHOW_INITIAL_ROWS = false`)
+	r.Equal(s.Create(), `CREATE STREAM "test_db"."test_schema"."test_stream" ON TABLE "test_db"."test_schema"."test_target_table" COMMENT = 'Test Comment' APPEND_ONLY = false INSERT_ONLY = false SHOW_INITIAL_ROWS = false`)
 
 	s.WithShowInitialRows(true)
-	r.Equal(s.Create(), `CREATE STREAM "test_db"."test_schema"."test_stream" ON TABLE "test_db"."test_schema"."test_target_table" COMMENT = 'Test Comment' APPEND_ONLY = false SHOW_INITIAL_ROWS = true`)
+	r.Equal(s.Create(), `CREATE STREAM "test_db"."test_schema"."test_stream" ON TABLE "test_db"."test_schema"."test_target_table" COMMENT = 'Test Comment' APPEND_ONLY = false INSERT_ONLY = false SHOW_INITIAL_ROWS = true`)
 
 	s.WithAppendOnly(true)
-	r.Equal(s.Create(), `CREATE STREAM "test_db"."test_schema"."test_stream" ON TABLE "test_db"."test_schema"."test_target_table" COMMENT = 'Test Comment' APPEND_ONLY = true SHOW_INITIAL_ROWS = true`)
+	r.Equal(s.Create(), `CREATE STREAM "test_db"."test_schema"."test_stream" ON TABLE "test_db"."test_schema"."test_target_table" COMMENT = 'Test Comment' APPEND_ONLY = true INSERT_ONLY = false SHOW_INITIAL_ROWS = true`)
+
+	s.WithInsertOnly(true)
+	r.Equal(s.Create(), `CREATE STREAM "test_db"."test_schema"."test_stream" ON TABLE "test_db"."test_schema"."test_target_table" COMMENT = 'Test Comment' APPEND_ONLY = true INSERT_ONLY = true SHOW_INITIAL_ROWS = true`)
 }
 
 func TestStreamChangeComment(t *testing.T) {