SNOW-176486 How to insert a map[string]string in a variant column?

[vgrigoriu]

Issue description

I'm trying to insert a Go map in a Snowflake column of type VARIANT. I get an error saying failed to run query: sql: converting argument $2 type: unsupported type map[string]string, a map.

Example code

result, err := db.Exec("insert into SomeTable (some_column) values ($1);",
	map[string]string{"key": "value"})
if err != nil {
	log.Fatalf("failed to run query: %v", err)
}

Configuration

Driver version (or git SHA): latest

Go version: go1.10.1 darwin/amd64

Server version: 3.12.0

Client OS: macos

[ChTimTsubasa]

Hi @vgrigoriu , the error is thrown from https://golang.org/src/database/sql/driver/types.go line 281. Which means using DB interface does not support map type.

Besides that, we don't support binding VARIANT on the golang driver for now. You can directly send a query containing the variant if that match you needs. See https://docs.snowflake.net/manuals/sql-reference/data-types-semistructured.html for more info on that.

We will keep this thread open and upgrade here once we support binding VARIANT type.

[sfc-gh-dszmolka]

this is with the driver team already so reopened this issue to properly track the progress when it's any

[sfc-gh-dszmolka]

small update: this turns out to be a new feature request, looking at https://github.com/snowflakedb/gosnowflake/blob/master/bind_uploader.go#L292

		// TODO SNOW-176486 variant, object, array

so thank you everyone for bearing with us while this gets put on the roadmap and eventually worked on.

The workaround will be converting map[string]string to a JSON string and inserting VARIANT data directly using "INSERT INTO ... SELECT": https://docs.snowflake.com/en/sql-reference/data-types-semistructured#example-of-inserting-a-variant

Example:

	param := map[string]string{"key": "value"}
	jsonStr, _ := json.Marshal(param)

	db.Exec("create or replace table issue_296(c1 variant)")
	result, err := db.Exec(
		"INSERT INTO issue_296 (c1) SELECT TO_VARIANT(PARSE_JSON(?));",
		string(jsonStr),
	)

[mihaitodor]

@sfc-gh-dszmolka Thanks! Just curious, why SELECT TO_VARIANT(PARSE_JSON(?)) and not just SELECT PARSE_JSON(?)? It looks like the return type of PARSE_JSON is VARIANT

[sfc-gh-dszmolka]

good catch, should be of no difference, but testing should prove or disprove this assumption