gosnowflake

README

********************************************************************************
Go Snowflake Driver
********************************************************************************

.. image:: https://travis-ci.org/snowflakedb/gosnowflake.svg?branch=master
    :target: https://travis-ci.org/snowflakedb/gosnowflake

.. image:: https://codecov.io/gh/snowflakedb/gosnowflake/branch/master/graph/badge.svg
    :target: https://codecov.io/gh/snowflakedb/gosnowflake

.. image:: http://img.shields.io/:license-Apache%202-brightgreen.svg
    :target: http://www.apache.org/licenses/LICENSE-2.0.txt

.. image:: https://goreportcard.com/badge/github.com/snowflakedb/gosnowflake
    :target: https://goreportcard.com/report/github.com/snowflakedb/gosnowflake

This topic provides instructions for installing, running, and modifying the Go Snowflake Driver. The driver supports Go's `database/sql <https://golang.org/pkg/database/sql/>`_ package.

Prerequisites
================================================================================

The following software packages are required to use the Go Snowflake Driver.

Go
----------------------------------------------------------------------

The driver requires the `Go language <https://golang.org/>`_ 1.8 or higher. The supported operating systems are Linux, Mac OS, and Windows, but you may run the driver on other platforms if the Go language works correctly on those platforms.


Installation
================================================================================

Get Gosnowflake source code and `dep <https://github.com/golang/dep>`_ (dependency managment tool), if not installed, and ensure the dependent libraries are installed.

.. code-block:: bash

    go get -u github.com/snowflakedb/gosnowflake
    go get -u github.com/golang/dep/cmd/dep
    cd $GOPATH/src/github.com/snowflakedb/gosnowflake/
    dep ensure

Docs
====

For detailed documentation and basic usage examples, please see the documentation at
`godoc.org <https://godoc.org/github.com/snowflakedb/gosnowflake/>`_.

Sample Programs
================================================================================

Snowflake provides a set of sample programs to test with. Set the environment variable ``$GOPATH`` to the top directory of your workspace, e.g., ``~/go`` and make certain to 
include ``$GOPATH/bin`` in the environment variable ``$PATH``. Run the ``make`` command to build all sample programs.

.. code-block:: go

    make install

In the following example, the program ``select1.go`` is built and installed in ``$GOPATH/bin`` and can be run from the command line:

.. code-block:: bash

    SNOWFLAKE_TEST_ACCOUNT=<your_account> \
    SNOWFLAKE_TEST_USER=<your_user> \
    SNOWFLAKE_TEST_PASSWORD=<your_password> \
    select1
    Congrats! You have successfully run SELECT 1 with Snowflake DB!

Development
================================================================================

The developer notes are hosted with the source code on `GitHub <https://github.com/snowflakedb/gosnowflake>`_.

Testing Code
----------------------------------------------------------------------

Set the Snowflake connection info in ``parameters.json``:

.. code-block:: json

    {
        "testconnection": {
            "SNOWFLAKE_TEST_USER":      "<your_user>",
            "SNOWFLAKE_TEST_PASSWORD":  "<your_password>",
            "SNOWFLAKE_TEST_ACCOUNT":   "<your_account>",
            "SNOWFLAKE_TEST_WAREHOUSE": "<your_warehouse>",
            "SNOWFLAKE_TEST_DATABASE":  "<your_database>",
            "SNOWFLAKE_TEST_SCHEMA":    "<your_schema>",
            "SNOWFLAKE_TEST_ROLE":      "<your_role>"
        }
    }

Install `jq <https://stedolan.github.io/jq/>`_ so that the parameters can get parsed correctly, and run ``make test`` in your Go development environment:

.. code-block:: bash

    make test

Submitting Pull Requests
----------------------------------------------------------------------

You may use your preferred editor to edit the driver code. Make certain to run ``make fmt lint`` before submitting any pull request to Snowflake. This command formats your source code according to the standard Go style and detects any coding style issues.

Support
----------------------------------------------------------------------

For official support, contact Snowflake support at:
https://support.snowflake.net/s/snowflake-support

Documentation

Overview

Package gosnowflake is a pure Go Snowflake driver for the database/sql package.

Clients can use the database/sql package directly. For example:

import (
	"database/sql"

	_ "github.com/snowflakedb/gosnowflake"
)

func main() {
	db, err := sql.Open("snowflake", "user:password@myaccount/mydb")
	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()
	...
}

Connection String

Use Open to create a database handle with connection parameters:

db, err := sql.Open("snowflake", "<connection string>")

The Go Snowflake Driver supports the following connection syntaxes (or data source name formats):

• username[:password]@accountname/dbname/schemaname[?param1=value&...&paramN=valueN
• username[:password]@accountname/dbname[?param1=value&...&paramN=valueN
• username[:password]@hostname:port/dbname/schemaname?account=<your_account>[&param1=value&...&paramN=valueN]

where all parameters must be escaped or use `Config` and `DSN` to construct a DSN string.

The following example opens a database handle with the Snowflake account myaccount where the username is jsmith, password is mypassword, database is mydb, schema is testschema, and warehouse is mywh:

db, err := sql.Open("snowflake", "jsmith:mypassword@myaccount/mydb/testschema?warehouse=mywh")

Connection Parameters

The following connection parameters are supported:

• region <string>: Specifies the Snowflake region. By default, the US West region is used. US East region, specify us-east-1. EU (Frankfurt) region, specify eu-central-1. AU (Australia) region, specify ap-southeast-2.

• account <string>: Specifies the name of your Snowflake account, where string is the name assigned to your account by Snowflake. In the URL you received from Snowflake, your account name is the first segment in the domain (e.g. abc123 in https://abc123.snowflakecomputing.com). This parameter is optional if your account is specified after the @ character.

• database: Specifies the database to use by default in the client session (can be changed after login).

• schema: Specifies the database schema to use by default in the client session (can be changed after login).

• warehouse: Specifies the virtual warehouse to use by default for queries, loading, etc. in the client session (can be changed after login).

• role: Specifies the role to use by default for accessing Snowflake objects in the client session (can be changed after login).

• passcode: Specifies the passcode provided by Duo when using MFA for login.

• passcodeInPassword: false by default. Set to true if the MFA passcode is embedded in the login password. Appends the MFA passcode to the end of the password.

• loginTimeout: Specifies the timeout, in seconds, for login. The default is 60 seconds. The login request gives up after the timeout length if the HTTP response is success.

• authenticator: Specifies the authenticator to use for authenticating user credentials:

• To use the internal Snowflake authenticator, specify snowflake (Default).

• To authenticate through Okta, specify https://<okta_account_name>.okta.com (URL prefix for Okta).

• To authenticate using your IDP via a browser, specify externalbrowser.

• To authenticate via OAuth, specify oauth and provide an OAuth Access Token (see the token parameter below).

• application: Identifies your application to Snowflake Support.

• insecureMode false by default. Set to true to bypass the Online Certificate Status Protocol (OCSP) certificate revocation check. IMPORTANT: Change the default value for testing or emergency situations only.

• token: a token that can be used to authenticate. Should be used in conjunction with the "oauth" authenticator.

• client_session_keep_alive: Set to true have a heartbeat in the background every hour to keep the connection alive such that the connection session will never expire. Care should be taken in using this option as it opens up the access forever as long as the process is alive.

All other parameters are taken as session parameters. For example, TIMESTAMP_OUTPUT_FORMAT session parameter can be set by adding:

...&TIMESTAMP_OUTPUT_FORMAT=MM-DD-YYYY...

Proxy

The Go Snowflake Driver honors the environment variables HTTP_PROXY, HTTPS_PROXY and NO_PROXY for the forward proxy setting.

Logging

By default, the driver's builtin logger is NOP; no output is generated. This is intentional for those applications that use the same set of logger parameters not to conflict with glog, which is incorporated in the driver logging framework.

In order to enable debug logging for the driver, add a build tag sfdebug to the go tool command lines, for example:

go build -tags=sfdebug

For tests, run the test command with the tag along with glog parameters. For example, the following command will generate all acitivty logs in the standard error.

go test -tags=sfdebug -v . -vmodule=*=2 -stderrthreshold=INFO

Likewise, if you build your application with the tag, you may specify the same set of glog parameters.

your_go_program -vmodule=*=2 -stderrthreshold=INFO

To get the logs for a specific module, use the -vmodule option. For example, to retrieve the driver.go and connection.go module logs:

your_go_program -vmodule=driver=2,connection=2 -stderrthreshold=INFO

Note: If your request retrieves no logs, call db.Close() or glog.flush() to flush the glog buffer.

Note: The logger may be changed in the future for better logging. Currently if the applications use the same parameters as glog, you cannot collect both application and driver logs at the same time.

Canceling Query by CtrlC

From 0.5.0, a signal handling responsibility has moved to the applications. If you want to cancel a query/command by Ctrl+C, add a os.Interrupt trap in context to execute methods that can take the context parameter, e.g., QueryContext, ExecContext.

// handle interrupt signal
ctx, cancel := context.WithCancel(context.Background())
c := make(chan os.Signal, 1)
signal.Notify(c, os.Interrupt)
defer func() {
	signal.Stop(c)
}()
go func() {
	<-c
	log.Println("Caught signal, canceling...")
	cancel()
}()
... (connection)
// execute a query
rows, err := db.QueryContext(ctx, query)
... (Ctrl+C to cancel the query)

See cmd/selectmany.go for the full example.

Supported Data Types

Queries return SQL column type information in the ColumnType type. The DatabaseTypeName method returns the following strings representing Snowflake data types:

String Representation	Snowflake Data Type
FIXED	                NUMBER/INT
REAL	                REAL
TEXT	                VARCHAR/STRING
DATE	                DATE
TIME	                TIME
TIMESTAMP_LTZ	        TIMESTAMP_LTZ
TIMESTAMP_NTZ	        TIMESTAMP_NTZ
TIMESTAMP_TZ	        TIMESTAMP_TZ
VARIANT	                VARIANT
OBJECT	                OBJECT
ARRAY	                ARRAY
BINARY	                BINARY
BOOLEAN	                BOOLEAN

Binding Time Type

Go's database/sql package limits Go's data types to the following for binding and fetching:

int64
float64
bool
[]byte
string
time.Time

Fetching data isn't an issue since the database data type is provided along with the data so the Go Snowflake Driver can translate Snowflake data types to Go native data types.

When the client binds data to send to the server, however, the driver cannot determine the date/timestamp data types to associate with binding parameters. For example:

dbt.mustExec("CREATE OR REPLACE TABLE tztest (id int, ntz, timestamp_ntz, ltz timestamp_ltz)")
// ...
stmt, err :=dbt.db.Prepare("INSERT INTO tztest(id,ntz,ltz) VALUES(1, ?, ?)")
// ...
tmValue time.Now()
// ... Is tmValue a TIMESTAMP_NTZ or TIMESTAMP_LTZ?
_, err = stmt.Exec(tmValue, tmValue)

To resolve this issue, a binding parameter flag is introduced that associates any subsequent time.Time type to the DATE, TIME, TIMESTAMP_LTZ, TIMESTAMP_NTZ or BINARY data type. The above example could be rewritten as follows:

import (
	sf "github.com/snowflakedb/gosnowflake"
)
dbt.mustExec("CREATE OR REPLACE TABLE tztest (id int, ntz, timestamp_ntz, ltz timestamp_ltz)")
// ...
stmt, err :=dbt.db.Prepare("INSERT INTO tztest(id,ntz,ltz) VALUES(1, ?, ?)")
// ...
tmValue time.Now()
// ...
_, err = stmt.Exec(sf.DataTypeTimestampNtz, tmValue, sf.DataTypeTimestampLtz, tmValue)

Timestamps with Time Zones

The driver fetches TIMESTAMP_TZ (timestamp with time zone) data using the offset-based Location types, which represent a collection of time offsets in use in a geographical area, such as CET (Central European Time) or UTC (Coordinated Universal Time). The offset-based Location data is generated and cached when a Go Snowflake Driver application starts, and if the given offset is not in the cache, it is generated dynamically.

Currently, Snowflake doesn't support the name-based Location types, e.g., America/Los_Angeles.

For more information about Location types, see the Go documentation for https://golang.org/pkg/time/#Location.

Binary Data

Internally, this feature leverages the []byte data type. As a result, BINARY data cannot be bound without the binding parameter flag. In the following example, sf is an alias for the gosnowflake package:

var b = []byte{0x01, 0x02, 0x03}
_, err = stmt.Exec(sf.DataTypeBinary, b)

Limitations

Currently, GET and PUT operations are unsupported.

Index

• Constants
• Variables
• func DSN(cfg *Config) (dsn string, err error)
• func Location(offset int) *time.Location
• func LocationWithOffsetString(offsets string) (loc *time.Location, err error)
• type Config
  • func ParseDSN(dsn string) (cfg *Config, err error)
• type SnowflakeDriver
  • func (d SnowflakeDriver) Open(dsn string) (driver.Conn, error)
• type SnowflakeError
  • func (se *SnowflakeError) Error() string
• type SnowflakeParameter
  • func ScanSnowflakeParameter(rows *sql.Rows) (*SnowflakeParameter, error)

Constants

const (

	// ErrCodeEmptyAccountCode is an error code for the case where a DNS doesn't include account parameter
	ErrCodeEmptyAccountCode = 260000
	// ErrCodeEmptyUsernameCode is an error code for the case where a DNS doesn't include user parameter
	ErrCodeEmptyUsernameCode = 260001
	// ErrCodeEmptyPasswordCode is an error code for the case where a DNS doesn't include password parameter
	ErrCodeEmptyPasswordCode = 260002
	// ErrCodeFailedToParseHost is an error code for the case where a DNS includes an invalid host name
	ErrCodeFailedToParseHost = 260003
	// ErrCodeFailedToParsePort is an error code for the case where a DNS includes an invalid port number
	ErrCodeFailedToParsePort = 260004
	// ErrCodeIdpConnectionError is an error code for the case where a IDP connection failed
	ErrCodeIdpConnectionError = 260005
	// ErrCodeSSOURLNotMatch is an error code for the case where a SSO URL doesn't match
	ErrCodeSSOURLNotMatch = 260006
	// ErrCodeServiceUnavailable is an error code for the case where service is unavailable.
	ErrCodeServiceUnavailable = 260007
	// ErrCodeFailedToConnect is an error code for the case where a DB connection failed due to wrong account name
	ErrCodeFailedToConnect = 260008
	// ErrCodeObjectNotExists is an error code for the case where the specified database object doesn't exist
	ErrCodeObjectNotExists = 260009

	// ErrFailedToPostQuery is an error code for the case where HTTP POST failed.
	ErrFailedToPostQuery = 261000
	// ErrFailedToRenewSession is an error code for the case where session renewal failed.
	ErrFailedToRenewSession = 261001
	// ErrFailedToCancelQuery is an error code for the case where cancel query failed.
	ErrFailedToCancelQuery = 261002
	// ErrFailedToCloseSession is an error code for the case where close session failed.
	ErrFailedToCloseSession = 261003
	// ErrFailedToAuth is an error code for the case where authentication failed for unknown reason.
	ErrFailedToAuth = 261004
	// ErrFailedToAuthSAML is an error code for the case where authentication via SAML failed for unknown reason.
	ErrFailedToAuthSAML = 261005
	// ErrFailedToAuthOKTA is an error code for the case where authentication via OKTA failed for unknown reason.
	ErrFailedToAuthOKTA = 261006
	// ErrFailedToGetSSO is an error code for the case where authentication via OKTA failed for unknown reason.
	ErrFailedToGetSSO = 261007
	// ErrFailedToParseResponse is an error code for when we cannot parse an external browser response from Snowflake.
	ErrFailedToParseResponse = 261008
	// ErrFailedToGetExternalBrowserResponse is an error code for when there's an error reading from the open socket.
	ErrFailedToGetExternalBrowserResponse = 261009
	// ErrFailedToHeartbeat is an error code when a heartbeat fails.
	ErrFailedToHeartbeat = 261010

	// ErrFailedToGetChunk is an error code for the case where it failed to get chunk of result set
	ErrFailedToGetChunk = 262000

	// ErrNoReadOnlyTransaction is an error code for the case where readonly mode is specified.
	ErrNoReadOnlyTransaction = 263000
	// ErrNoDefaultTransactionIsolationLevel is an error code for the case where non default isolation level is specified.
	ErrNoDefaultTransactionIsolationLevel = 263001

	// ErrInvalidTimestampTz is an error code for the case where a returned TIMESTAMP_TZ internal value is invalid
	ErrInvalidTimestampTz = 268000
	// ErrInvalidOffsetStr is an error code for the case where a offset string is invalid. The input string must
	// consist of sHHMI where one sign character '+'/'-' followed by zero filled hours and minutes
	ErrInvalidOffsetStr = 268001
	// ErrInvalidBinaryHexForm is an error code for the case where a binary data in hex form is invalid.
	ErrInvalidBinaryHexForm = 268002
)

const (
	// SQLStateNumericValueOutOfRange is a SQL State code indicating Numeric value is out of range.
	SQLStateNumericValueOutOfRange = "22003"
	// SQLStateInvalidDataTimeFormat is a SQL State code indicating DataTime format is invalid.
	SQLStateInvalidDataTimeFormat = "22007"
	// SQLStateConnectionWasNotEstablished is a SQL State code indicating connection was not established.
	SQLStateConnectionWasNotEstablished = "08001"
	// SQLStateConnectionRejected is a SQL State code indicating connection was rejected.
	SQLStateConnectionRejected = "08004"
	// SQLStateConnectionFailure is a SQL State code indicating connection failed.
	SQLStateConnectionFailure = "08006"
	// SQLStateFeatureNotSupported is a SQL State code indicating the feature is not enabled.
	SQLStateFeatureNotSupported = "0A000"
)

const SnowflakeGoDriverVersion = "1.1.9"

SnowflakeGoDriverVersion is the version of Go Snowflake Driver.

Variables

var (
	// DataTypeFixed is a FIXED datatype.
	DataTypeFixed = []byte{fixedType}
	// DataTypeReal is a REAL datatype.
	DataTypeReal = []byte{realType}
	// DataTypeText is a TEXT datatype.
	DataTypeText = []byte{textType}
	// DataTypeDate is a Date datatype.
	DataTypeDate = []byte{dateType}
	// DataTypeVariant is a TEXT datatype.
	DataTypeVariant = []byte{variantType}
	// DataTypeTimestampLtz is a TIMESTAMP_LTZ datatype.
	DataTypeTimestampLtz = []byte{timestampLtzType}
	// DataTypeTimestampNtz is a TIMESTAMP_NTZ datatype.
	DataTypeTimestampNtz = []byte{timestampNtzType}
	// DataTypeTimestampTz is a TIMESTAMP_TZ datatype.
	DataTypeTimestampTz = []byte{timestampTzType}
	// DataTypeObject is a OBJECT datatype.
	DataTypeObject = []byte{objectType}
	// DataTypeArray is a ARRAY datatype.
	DataTypeArray = []byte{arrayType}
	// DataTypeBinary is a BINARY datatype.
	DataTypeBinary = []byte{binaryType}
	// DataTypeTime is a TIME datatype.
	DataTypeTime = []byte{timeType}
	// DataTypeBoolean is a BOOLEAN datatype.
	DataTypeBoolean = []byte{booleanType}
)

var (
	// ErrEmptyAccount is returned if a DNS doesn't include account parameter.
	ErrEmptyAccount = &SnowflakeError{
		Number:  ErrCodeEmptyAccountCode,
		Message: "account is empty",
	}
	// ErrEmptyUsername is returned if a DNS doesn't include user parameter.
	ErrEmptyUsername = &SnowflakeError{
		Number:  ErrCodeEmptyUsernameCode,
		Message: "user is empty",
	}
	// ErrEmptyPassword is returned if a DNS doesn't include password parameter.
	ErrEmptyPassword = &SnowflakeError{
		Number:  ErrCodeEmptyPasswordCode,
		Message: "password is empty"}
)

var SnowflakeTransport = &http.Transport{
	TLSClientConfig: &tls.Config{
		RootCAs:               certPool,
		VerifyPeerCertificate: verifyPeerCertificateParallel,
	},
	MaxIdleConns:    10,
	IdleConnTimeout: 30 * time.Minute,
	Proxy:           http.ProxyFromEnvironment,
}

SnowflakeTransport includes the certificate revocation check with OCSP in parallel. By default, the driver uses this transport object.

var SnowflakeTransportSerial = &http.Transport{
	TLSClientConfig: &tls.Config{
		RootCAs:               certPool,
		VerifyPeerCertificate: verifyPeerCertificateSerial,
	},
	MaxIdleConns:    10,
	IdleConnTimeout: 30 * time.Minute,
	Proxy:           http.ProxyFromEnvironment,
}

SnowflakeTransportSerial includes the certificate revocation check with OCSP in serial.

var SnowflakeTransportTest = SnowflakeTransport

SnowflakeTransportTest includes the certificate revocation check in parallel

Functions

func DSN

func DSN(cfg *Config) (dsn string, err error)

DSN constructs a DSN for Snowflake db.

func Location

func Location(offset int) *time.Location

Location returns an offset (minutes) based Location object for Snowflake database.

func LocationWithOffsetString

func LocationWithOffsetString(offsets string) (loc *time.Location, err error)

LocationWithOffsetString returns an offset based Location object. The offset string must consist of sHHMI where one sign character '+'/'-' followed by zero filled hours and minutes.

Types

type Config

type Config struct {
	Account   string             // Account name
	User      string             // Username
	Password  string             // Password (requires User)
	Database  string             // Database name
	Schema    string             // Schema
	Warehouse string             // Warehouse
	Role      string             // Role
	Region    string             // Region
	Params    map[string]*string // other connection parameters

	Protocol string // http or https (optional)
	Host     string // hostname (optional)
	Port     int    // port (optional)

	Authenticator      string // snowflake, okta URL, oauth or externalbrowser
	Passcode           string
	PasscodeInPassword bool

	LoginTimeout   time.Duration // Login timeout
	RequestTimeout time.Duration // request timeout

	Application  string // application name.
	InsecureMode bool   // driver doesn't check certificate revocation status

	Token string // Token to use for OAuth / JWT / other forms of token based auth
}

Config is a set of configuration parameters

func ParseDSN

func ParseDSN(dsn string) (cfg *Config, err error)

ParseDSN parses the DSN string to a Config.

type SnowflakeDriver

type SnowflakeDriver struct{}

SnowflakeDriver is a context of Go Driver

func (SnowflakeDriver) Open

func (d SnowflakeDriver) Open(dsn string) (driver.Conn, error)

Open creates a new connection.

type SnowflakeError

type SnowflakeError struct {
	Number         int
	SQLState       string
	QueryID        string
	Message        string
	MessageArgs    []interface{}
	IncludeQueryID bool // TODO: populate this in connection
}

SnowflakeError is a error type including various Snowflake specific information.

func (*SnowflakeError) Error

func (se *SnowflakeError) Error() string

type SnowflakeParameter

type SnowflakeParameter struct {
	Key              string
	Value            string
	Default          string
	Level            string
	Description      string
	SetByUser        string
	SetInJob         string
	SetOn            string
	SetByThreadID    string
	SetByThreadName  string
	SetByClass       string
	ParameterComment string
}

SnowflakeParameter includes the columns output from SHOW PARAMETER command.

func ScanSnowflakeParameter

func ScanSnowflakeParameter(rows *sql.Rows) (*SnowflakeParameter, error)

ScanSnowflakeParameter binds SnowflakeParameter variable with an array of column buffer.