Snowflake

Connect to Snowflake Data Warehouse with GO

In this installment, I am going to walk through the process of connecting GO to the Snowflake Data Warehouse Service (DWaaS). This tutorial requires that you have a Snowflake account. You can sign up here for a 30 day/$400.00 trial.

Download and Install the ODBC Driver

Once you have your account set up in Snowflake, follow the directions to download the ODBC Driver. It will the same place you find the snowSQL download:

Warehouse driver download

Installing GO

Installing GO can be accomplished via Homebrew or visiting the GoLang download page. If you chose Homebrew, can find installation instructions on their page, or you can copy and paste:

/usr/bin/ruby -e "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install)"

which will get you going right away. Once you get Homebrew going, you can simply type:

Macbook Pro:~ dbryant$ brew install go
Confirm GO is installed:
Macbook Pro:~ dbryant$ go version
go version go1.10.1 darwin/amd64

After you install GO, this would be a good time to update you .profile to include a GOPATH environment variable. For my configuration, it looks like this:

$ cat .bash_profile
export PS1="$ "
export GOPATH=/Users/dbryant/go

$ echo $GOPATH
/Users/dbryant/go
Install dep

dep is a prototype dependency management tool for Go. It requires Go 1.9 or newer to compile. Since Homebrew is already on your machine, we can easily install dep using brew:

$ brew install dep
Updating Homebrew...
==> Auto-updated Homebrew!
Updated 1 tap (homebrew/core).
==> New Formulae
amtk
==> Updated Formulae
annie                      git-quick-stats            nvc
ansiweather                giter8                     ola
apache-flink               gitlab-runner              overmind
babel                      gnome-latex                packer
bareos-client              grpc                       paket
bazel                      harfbuzz                   proxychains-ng
binaryen                   jpeg-archive               pulseaudio
byteman                    kube-aws                   sane-backends
ccextractor                latex2rtf                  sipp
charm                      lensfun                    snakemake
cmake                      libdvbpsi                  sphinx-doc
cockroach                  libfixbuf                  tepl
collectd                   libtorrent-rasterbar       tiger-vnc
conjure-up                 liquibase                  traefik
fakeroot                   mpop                       util-linux
fastqc                     mutt                       vala
file-roller                neomutt                    webpack
fn                         net-snmp                   webtorrent-cli
folly                      nifi                       weechat
fwup                       npth                       xonsh

==> Installing dependencies for dep: go
==> Installing dep dependency: go
==> Downloading https://homebrew.bintray.com/bottles/go-1.10.3.high_sierra.bottl
######################################################################## 100.0%
==> Pouring go-1.10.3.high_sierra.bottle.tar.gz
==> Caveats
A valid GOPATH is required to use the `go get` command.
If $GOPATH is not specified, $HOME/go will be used by default:
  https://golang.org/doc/code.html#GOPATH

You may wish to add the GOROOT-based install location to your PATH:
  export PATH=$PATH:/usr/local/opt/go/libexec/bin
==> Summary
?  /usr/local/Cellar/go/1.10.3: 8,170 files, 336.7MB
==> Installing dep
==> Downloading https://homebrew.bintray.com/bottles/dep-0.4.1_1.high_sierra.bot
######################################################################## 100.0%
==> Pouring dep-0.4.1_1.high_sierra.bottle.tar.gz
?  /usr/local/Cellar/dep/0.4.1_1: 7 files, 8.8MB
==> Caveats
==> go
A valid GOPATH is required to use the `go get` command.
If $GOPATH is not specified, $HOME/go will be used by default:
  https://golang.org/doc/code.html#GOPATH

You may wish to add the GOROOT-based install location to your PATH:
  export PATH=$PATH:/usr/local/opt/go/libexec/bin
Install the GO Snowflake Driver:

Get Gosnowflake source code and dep (dependency managment tool), if not installed, and ensure the dependent libraries are installed.

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

Now that we have most of the prerequisites for the connection done, we need to build the sample programs

$ make install
for c in $(ls cmd); do \
		(cd cmd/$c;  GOBIN=$GOPATH/bin go install $c.go); \
	done
$

After executing the make install command, I found our test file “select1.go” in the following subdirectory:

/Users/dbryant/go/src/github.com/snowflakedb/gosnowflake/cmd/select1/

If you have all of your path information configured correctly, this should be okay for you test a connection. This test program will simply attempt to connect to the Snowflake data warehouse. There are three parameters required for connecting to Snowflake via GO and the select1.go test file.

Let’s take a look at the snippet from the select1.go file

...

// getDSN constructs a DSN based on the test connection parameters
func getDSN() (string, *sf.Config, error) {
	env := func(k string, failOnMissing bool) string {
		if value := os.Getenv(k); value != "" {
			return value
		}
		if failOnMissing {
			log.Fatalf("%v environment variable is not set.", k)
		}
		return ""
	}

	account := env("SNOWFLAKE_TEST_ACCOUNT", true)
	user := env("SNOWFLAKE_TEST_USER", true)
	password := env("SNOWFLAKE_TEST_PASSWORD", true)
	host := env("SNOWFLAKE_TEST_HOST", false)
	port := env("SNOWFLAKE_TEST_PORT", false)
	protocol := env("SNOWFLAKE_TEST_PROTOCOL", false)

	portStr, _ := strconv.Atoi(port)
	cfg := &sf.Config{
		Account:  account,
		User:     user,
		Password: password,
		Host:     host,
		Port:     portStr,
		Protocol: protocol,
	}

	dsn, err := sf.DSN(cfg)
	return dsn, cfg, err
}
...

The function above comes from the select1.go test file. You should notice that the env function for account, user, and password pass a true parameter. These are values read from your O/S environment variables. I created a shell script to set these values for testing purposes.

$ cat .sfgo.sh
#!/bin/sh
export SNOWFLAKE_TEST_ACCOUNT=my_account_name
export SNOWFLAKE_TEST_USER=user_name
export SNOWFLAKE_TEST_PASSWORD=password
$ 
$ chmod 755 .sfgo.sh
$ . .sfgo.sh

Once you have run the script, check that your environment variables are set:

$ echo $SNOWFLAKE_TEST_USER
user_name
$ echo $SNOWFLAKE_TEST_ACCOUNT
my_account_name
$ echo $SNOWFLAKE_TEST_PASSWORD
password
$

After verifying these environment variables have been set correctly, confirm that you are in the directory with the select1.go file and execute the following:

$ go run select1.go
Congrats! You have successfully run SELECT 1 with Snowflake DB!

If you see the Congrats! message, then congratulations, you have successfully connect to the Snowflake Data Warehouse Cloud Service.

dbaontap warehosue

Enjoy!

Related Posts Plugin for WordPress, Blogger...

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.