github.com/snowflakedb/gosnowflake@v1.9.0/cmd/variant/insertvariantobject.go (about)

     1  package main
     2  
     3  import (
     4  	"context"
     5  	"database/sql"
     6  	"encoding/json"
     7  	"flag"
     8  	"fmt"
     9  	"log"
    10  	"strconv"
    11  	"time"
    12  
    13  	sf "github.com/snowflakedb/gosnowflake"
    14  )
    15  
    16  func main() {
    17  	if !flag.Parsed() {
    18  		flag.Parse()
    19  	}
    20  
    21  	cfg, err := sf.GetConfigFromEnv([]*sf.ConfigParam{
    22  		{Name: "Account", EnvName: "SNOWFLAKE_TEST_ACCOUNT", FailOnMissing: true},
    23  		{Name: "User", EnvName: "SNOWFLAKE_TEST_USER", FailOnMissing: true},
    24  		{Name: "Password", EnvName: "SNOWFLAKE_TEST_PASSWORD", FailOnMissing: true},
    25  		{Name: "Warehouse", EnvName: "SNOWFLAKE_TEST_WAREHOUSE", FailOnMissing: true},
    26  		{Name: "Database", EnvName: "SNOWFLAKE_TEST_DATABASE", FailOnMissing: true},
    27  		{Name: "Schema", EnvName: "SNOWFLAKE_TEST_SCHEMA", FailOnMissing: true},
    28  		{Name: "Host", EnvName: "SNOWFLAKE_TEST_HOST", FailOnMissing: false},
    29  		{Name: "Port", EnvName: "SNOWFLAKE_TEST_PORT", FailOnMissing: false},
    30  		{Name: "Protocol", EnvName: "SNOWFLAKE_TEST_PROTOCOL", FailOnMissing: false},
    31  	})
    32  	if err != nil {
    33  		log.Fatalf("failed to create Config, err: %v", err)
    34  	}
    35  	dsn, err := sf.DSN(cfg)
    36  	if err != nil {
    37  		log.Fatalf("failed to create DSN from Config: %v, err: %v", cfg, err)
    38  	}
    39  
    40  	db, err := sql.Open("snowflake", dsn)
    41  	if err != nil {
    42  		log.Fatalf("failed to connect. %v, err: %v", dsn, err)
    43  	}
    44  	defer db.Close()
    45  
    46  	ctx := context.Background()
    47  	conn, err := db.Conn(ctx)
    48  	if err != nil {
    49  		log.Fatalf("Failed to acquire connection. err: %v", err)
    50  	}
    51  	defer conn.Close()
    52  
    53  	tablename := "insert_variant_object_" + strconv.FormatInt(time.Now().UnixNano(), 10)
    54  	param := map[string]string{"key": "value"}
    55  	jsonStr, err := json.Marshal(param)
    56  	if err != nil {
    57  		log.Fatalf("failed to marshal json. err: %v", err)
    58  	}
    59  
    60  	createTableQuery := "CREATE TABLE " + tablename + " (c1 VARIANT, c2 OBJECT)"
    61  
    62  	// https://docs.snowflake.com/en/sql-reference/functions/parse_json
    63  	// can do with TO_VARIANT(PARSE_JSON(..)) as well, but PARSE_JSON already produces VARIANT
    64  	insertQuery := "INSERT INTO " + tablename + " (c1, c2) SELECT PARSE_JSON(?), TO_OBJECT(PARSE_JSON(?))"
    65  	// https://docs.snowflake.com/en/sql-reference/data-types-semistructured#object
    66  	insertOnlyObject := "INSERT INTO " + tablename + " (c2) SELECT OBJECT_CONSTRUCT('name', 'Jones'::VARIANT, 'age',  42::VARIANT)"
    67  
    68  	selectQuery := "SELECT c1, c2 FROM " + tablename
    69  
    70  	dropQuery := "DROP TABLE " + tablename
    71  
    72  	fmt.Printf("Creating table: %v\n", createTableQuery)
    73  	_, err = conn.ExecContext(ctx, createTableQuery)
    74  	if err != nil {
    75  		log.Fatalf("failed to run the query. %v, err: %v", createTableQuery, err)
    76  	}
    77  	defer func() {
    78  		fmt.Printf("Dropping the table: %v\n", dropQuery)
    79  		_, err = conn.ExecContext(ctx, dropQuery)
    80  		if err != nil {
    81  			log.Fatalf("failed to run the query. %v, err: %v", dropQuery, err)
    82  		}
    83  	}()
    84  	fmt.Printf("Inserting VARIANT and OBJECT data into table: %v\n", insertQuery)
    85  	_, err = conn.ExecContext(ctx, insertQuery,
    86  		string(jsonStr),
    87  		string(jsonStr),
    88  	)
    89  	if err != nil {
    90  		log.Fatalf("failed to run the query. %v, err: %v", insertQuery, err)
    91  	}
    92  	fmt.Printf("Now for another approach: %v\n", insertOnlyObject)
    93  	_, err = conn.ExecContext(ctx, insertOnlyObject)
    94  	if err != nil {
    95  		log.Fatalf("failed to run the query. %v, err: %v", insertOnlyObject, err)
    96  	}
    97  
    98  	fmt.Printf("Querying the table into which we just inserted the data: %v\n", selectQuery)
    99  	rows, err := conn.QueryContext(ctx, selectQuery)
   100  	if err != nil {
   101  		log.Fatalf("failed to run the query. %v, err: %v", selectQuery, err)
   102  	}
   103  	defer rows.Close()
   104  	var c1, c2 any
   105  	for rows.Next() {
   106  		err := rows.Scan(&c1, &c2)
   107  		if err != nil {
   108  			log.Fatalf("failed to get result. err: %v", err)
   109  		}
   110  		fmt.Printf("%v (type: %T), %v (type: %T)\n", c1, c1, c2, c2)
   111  	}
   112  	if rows.Err() != nil {
   113  		fmt.Printf("ERROR: %v\n", rows.Err())
   114  		return
   115  	}
   116  
   117  }