Connect to Snowflake
The sections below explain how to connect to Snowflake, via Cyral, using the driver and client of your choice:
- Connect to Snowflake through SnowSQL (CLI)
- Connect to Snowflake in Java (JDBC)
- Connect to Snowflake in R (JDBC)
- Connect to Snowflake in R (ODBC)
- Connect to Snowflake using the Python connector
- Connect to Snowflake using Tableau
Connect to Snowflake through SnowSQL (CLI)
Installation
The SnowSQL files can be downloaded from snowsql/bootstrap downloads. After downloading the installer, follow Snowflake's installation steps, which are OS specific: Installing SnowSQL.
Check the installation was successful:
snowsql -v
Usage
Then, to start a session:
snowsql -a <account> -u <user>
Running this command, you’ll be prompted for your password. After logging in, you have access to the SnowSQL shell and can start running queries:
$ snowsql -a fva95771 -u hsousa
Password:
* SnowSQL * v1.2.9
Type SQL statements or !help
hsousa#(no warehouse)@(no database).(no schema)>
Connecting to a Cyral sidecar
It's also possible to specify the Snowflake host and port, with the -h and -p flags, respectively. So if you want to connect to a Cyral sidecar, you can do it like this:
snowsql -a fva95771 -u hsousa -h <sidecar_host> -p <sidecar_port>
tip
With Cyral in place, you need two additional parameters to log in. For connecting to a Snowflake server directly, you usually provide only the account name. When you connect through a Cyral sidecar, you must also provide the host and port of the server.
Using SSO
To use SSO with Snowflake, whether it’s with SnowSQL or with the usual browser interface, the same steps must be followed. For details on this configuration, see Cyral's Snowflake configuration instructions.
With everything set up, you can use SSO with SnowSQL by specifying
the --authenticator flag, as shown here:
$ snowsql -a JSA01108 -u nancy.drew@hhiu.us --authenticator externalbrowser
Initiating login request with your identity provider. A browser window
should have opened for you to complete the login. If you can't see it,
check existing browser windows, or your OS settings. Press CTRL+C to
abort and try again...
Opening in existing browser session.
* SnowSQL * v1.2.14
Type SQL statements or !help
nancy.drew@hhiu.us#(no warehouse)@(no database).(no schema)>
Connect to Snowflake in Java (JDBC)
In this section we’ll explore how to connect to a Snowflake server using Java. This will cover driver installation, setup, establishing connections and running simple queries.
Prerequisites
This tutorial will be focused on the Java programming language, hence it’s expected that you already have a Java environment set up. If you don’t, please refer to the Java SE downloads page for download links and instructions.
In Ubuntu, the following commands will install the components you need:
sudo apt-get update
sudo apt install default-jre
sudo apt install default-jdk
To verify you can compile and run Java programs, the commands below should have a similar output to what’s shown here.
$ java --version
openjdk 11.0.11 2021-04-20
OpenJDK Runtime Environment (build 11.0.11+9-post-Debian-1deb10u1)
OpenJDK 64-Bit Server VM (build 11.0.11+9-post-Debian-1deb10u1, mixed mode, sharing)
$ javac --version
javac 11.0.11
Setup
To set up Snowflake to be used with Java, you first need to download the Snowflake JDBC driver, and then to install it.
# To download the latest version (at the time of writing)
wget https://repo1.maven.org/maven2/net/snowflake/snowflake-jdbc/3.13.3/snowflake-jdbc-3.13.3.jar
Then, to use the driver when running the Java program that connects to Snowflake, the downloaded file must be passed to the CLASSPATH environment variable.
CLASSPATH="${CLASSPATH}:./snowflake-jdbc-3.13.3.jar" java Snowflake
Connecting to Snowflake
Here’s an example Java file that can be used to connect to Snowflake and run a simple query:
import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.DriverManager;
import java.util.Properties;
public class Snowflake {
public static void main(String[] args) throws Exception {
String url = "jdbc:snowflake://<snowflake_endpoint>";
Properties prop = new Properties();
prop.put("user", "<user>");
prop.put("password", "<password>");
prop.put("db", "<database>");
prop.put("schema", "<schema>");
prop.put("warehouse", "<warehouse>");
prop.put("role", "<role>");
Connection conn = DriverManager.getConnection(url, prop);
Statement stat = conn.createStatement();
ResultSet res = stat.executeQuery("SELECT 42");
res.next();
System.out.println(res.getString(1));
conn.close();
}
}
Save this file as Snowflake.java, and let’s walk through it:
- In line 9, you must pass the Snowflake endpoint to the connection string.
- Lines 11-17 set the database credentials and parameters for the connecting user.
- Line 19 connects to the server.
- Lines 21-22 execute a query.
- And lines 24-25 retrieve the result for this query.
To compile the program, run:
javac Snowflake.java
Connecting to a Cyral sidecar
You can use the same Java program above to connect to a Cyral sidecar
with the Snowflake stack. You just need to do one change: the
snowflake_endpoint should specify the location of the Cyral sidecar
endpoint, in the form <host>[:<port>].
tip
With Cyral in place, you need two additional parameters to log in. For connecting to a Snowflake server directly, you usually provide only the account name. When you connect through a Cyral sidecar, you must also provide the host and port of the server.
Using Single Sign-On
In order to connect using SSO, the authenticator parameter must be set
to externalbrowser added to the connection string. So it would look
like this:
String url = "jdbc:snowflake://<snowflake_endpoint>/?authenticator=externalbrowser";
Alternatively, you can also set this parameter using the Properties object:
Properties prop = new Properties();
prop.put("authenticator", "externalbrowser");
Connect to Snowflake in R (JDBC)
In this section we’ll explore how to connect to a Snowflake server using R (with the JDBC driver). This will cover driver installation, setup, establishing connections and running simple queries. If you’d like instructions on R x ODBC, please refer to Connect to Snowflake in R (ODBC).
Prerequisites
This tutorial will be focused on the R programming language, hence it’s expected that you already have an R environment set up. If you don’t, please refer to this page for download links and instructions.
In Ubuntu 20.04, the following commands will install all you need:
sudo apt-key adv --keyserver keyserver.ubuntu.com --recv-keys E298A3A825C0D65DFD57CBB651716619E084DAB9
sudo add-apt-repository 'deb https://cloud.r-project.org/bin/linux/ubuntu focal-cran40/'
sudo apt update
sudo apt install r-base
To verify you can run R code, the command below should have a similar output to what’s shown here.
$ R
R version 4.1.0 (2021-05-18) -- "Camp Pontanezen"
Copyright (C) 2021 The R Foundation for Statistical Computing
Platform: x86_64-pc-linux-gnu (64-bit)
R is free software and comes with ABSOLUTELY NO WARRANTY.
You are welcome to redistribute it under certain conditions.
...
Setup
To set up Snowflake to be used with R and JDBC, you first need to download the Snowflake JDBC driver from this page. To download the latest version (at the time of writing)
wget https://repo1.maven.org/maven2/net/snowflake/snowflake-jdbc/3.13.3/snowflake-jdbc-3.13.3.jar
Then, to install the necessary dependencies in R, you’ll use the following commands:
install.packages(c("rJava"))
install.packages(c("RJDBC", "DBI", "dplyr"))
install.packages("devtools")
devtools::install_github("snowflakedb/dplyr-snowflakedb")
These can be run directly into an R interactive session (using the R
command), or saved in a file and executed using the Rscript command.
Rscript snowflake_install.r
caution
This installation step may fail if any of the required dependencies are missing. If that happens, check the installation logs to see which library is missing, and install it before retrying.
Connecting to Snowflake
Here’s an example R file that can be used to connect to Snowflake using JDBC and run a simple query:
library(RJDBC)
library(dplyr)
library(dplyr.snowflakedb)
options(dplyr.jdbc.classpath = "/path/to/jdbc/driver/snowflake-jdbc-3.13.3.jar")
my_db <- src_snowflakedb(
user = "<user>",
password = "<password>",
account = "<account>",
opts = list(
warehouse = "<warehouse>",
db = "<database>",
schema = "<schema>",
role = "<role>"
)
)
tbl(my_db, sql("SELECT 42"))
Save this file as snowflake.r, and let’s walk through it.
- Lines 1-3 import the necessary libraries.
- Line 5 sets the absolute path of the JDBC driver you downloaded previously.
- Lines 7-17 set the connection parameters and connect to Snowflake.
- Line 19 executes a simple query in the connected database.
To run the program, execute:
Rscript snowflake.r
Despite this tutorial focusing on using the Rscript utility to run R programs, all code showed here can also be used in Rstudio.
Connecting to a Cyral sidecar
You can use the same R program above to connect to a Cyral sidecar with the Snowflake stack. For that, in addition to the other connection parameters, you must also specify the host and port parameters, pointing to the sidecar endpoint. Note that you don’t need to provide the account name in this case. It would look like the following:
my_db <- src_snowflakedb(
user = "<user>",
password = "<password>",
host = "<host>",
port = "<port>",
opts = list(
warehouse = "<warehouse>",
db = "<database>",
schema = "<schema>",
role = "<role>"
)
)
tip
With Cyral in place, you need two additional parameters to log in. For connecting to a Snowflake server directly, you usually provide only the account name. When you connect through a Cyral sidecar, you must also provide the host and port of the server.
Using Single Sign-On
In order to connect using SSO, the authenticator parameter must be set
to externalbrowser in the connection parameters. So it would look like
this:
my_db <- src_snowflakedb(
user = "<user>",
account = "<account>",
opts = list(
warehouse = "<warehouse>",
db = "<database>",
schema = "<schema>",
role = "<role>",
authenticator = "externalbrowser"
)
)
tip
In this case, you don’t need to specify a password, since the credentials will be provided in a browser window.
Connect to Snowflake in R (ODBC)
In this section we’ll explore how to connect to a Snowflake server using R (with the ODBC driver). This will cover driver installation, setup, establishing connections and running simple queries. If you’d like instructions on R x JDBC, please refer to this page.
Prerequisites
This tutorial will be focused on the R programming language, hence it’s expected that you already have an R environment set up. If you don’t, please refer to this page for download links and instructions.
In Ubuntu 20.04, the following commands will install all you need:
sudo apt-key adv --keyserver keyserver.ubuntu.com --recv-keys E298A3A825C0D65DFD57CBB651716619E084DAB9
sudo add-apt-repository 'deb https://cloud.r-project.org/bin/linux/ubuntu focal-cran40/'
sudo apt update
sudo apt install r-base
To verify you can run R code, the command below should have a similar output to what’s shown here.
$ R
R version 4.1.0 (2021-05-18) -- "Camp Pontanezen"
Copyright (C) 2021 The R Foundation for Statistical Computing
Platform: x86_64-pc-linux-gnu (64-bit)
...
Setup
To setup Snowflake to be used with R and ODBC, you first need to
install a driver manager. In this tutorial we’ll use unixODBC, but
Snowflake also supports iODBC, as mentioned
here.
sudo apt-get install unixodbc
Then, you need to download the ODBC driver. This page contains download links for multiple platforms. The command below downloads version 2.23.2 of the driver, in .deb format, for Linux.
wget https://sfc-repo.snowflakecomputing.com/odbc/linux/2.23.2/snowflake-odbc-2.23.2.x86_64.deb
And then to install the driver:
sudo dpkg -i snowflake-odbc-2.23.2.x86_64.deb
caution
This installation step may fail if any of the required dependencies
for the package manager are missing. If this step fails, install
the missing dependencies with sudo apt-get install -f.
Now that the driver is installed, you must configure it. For that, you
need to edit three files, as described below. Note that here we’ll
assume that the driver was installed in the default path, which is
/usr/lib/snowflake/odbc/.
First, edit the /usr/lib/snowflake/odbc/lib/simba.snowflake.ini
file, adding the following lines to it:
ErrorMessagesPath=/usr/lib/snowflake/odbc/ErrorMessages/
LogPath=/tmp/
ODBCInstLib=<driver_manager_path>
CABundleFile=/usr/lib/snowflake/odbc/lib/cacert.pem
ANSIENCODING=UTF-8
For ODBCInstLib, use libiodbcinst.so.2 if you are using iODBC, and libodbcinst.so if you are using unixODBC. If your driver manager directory is not included in the LD_LIBRARY_PATH environment variable, then you need to specify the complete path to these files. In my case, using unixODBC, this was /usr/lib/x86_64-linux-gnu/libodbcinst.so.
Edit the /etc/odbcinst.ini file adding these lines:
[ODBC Drivers]
SnowflakeDSIIDriver=Installed
[SnowflakeDSIIDriver]
APILevel=1
ConnectFunctions=YYY
Description=Snowflake DSII
Driver=/usr/lib/snowflake/odbc/lib/libSnowflake.so
DriverODBCVer=03.52
SQLLevel=1
And finally, edit the /etc/odbc.ini file, adding all DSNs you’d like
to use in your R code. Below we show an example with a single DSN
set up.
[ODBC Data Sources]
testodbc1 = SnowflakeDSIIDriver
[testodbc1]
Driver = /usr/lib/snowflake/odbc/lib/libSnowflake.so
Description = Snowflake
server = xy12345.snowflakecomputing.com
role = sysadmin
role = analyst
database = sales
warehouse = analysis
To test the DSN above created in this file, using the isql utility
(shipped with unixODBC), you could run the following:
isql -v testodbc1 <user> <password>
If successful, this command will return something like:
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| ... |
| |
+---------------------------------------+
SQL>
Now there’s a last step before you can connect to a Snowflake server using R and ODBC. You must install the following modules in R:
install.packages(c("DBI", "dplyr","dbplyr","odbc"))
Connecting to Snowflake
Here’s an example R file that can be used to connect to Snowflake using ODBC and run a simple query:
library(DBI)
library(dplyr)
library(dbplyr)
library(odbc)
myconn <- DBI::dbConnect(odbc::odbc(), "testodbc1", uid="<user>", pwd='<password>')
mydata <- DBI::dbGetQuery(myconn, "SELECT 42")
mydata
Save this file as snowflake.r, and let’s walk through it.
- Lines 1-4 import the necessary libraries.
- Line 6 passes user credentials and connects to the DSN named testodbc1.
- Line 7 executes a simple query in the connected database.
- Line 8 prints the results for this query.
To run the program, execute:
Rscript snowflake.r
Despite this tutorial focusing on using the Rscript utility to run R programs, all code showed here can also be used in Rstudio.
Connecting to a Cyral sidecar
You can use the same R program above to connect to a Cyral sidecar with the Snowflake stack. For that, you need to modify the /etc/odbc.ini file, passing the server and port parameters in the DSN entry, pointing to the sidecar endpoint.
[ODBC Data Sources]
cyral = SnowflakeDSIIDriver
[cyral]
Driver = /usr/lib/snowflake/odbc/lib/libSnowflake.so
Description = Cyral
server = snowflake.cyral.com
port = 443
role = accountadmin
database = cyral_copy
warehouse = compute_wh
schema = finance
tip
With Cyral in place, you need two additional parameters to log in. For connecting to a Snowflake server directly, you usually provide only the account name. When you connect through a Cyral sidecar, you must also provide the host and port of the server.
Using Single Sign-On
In order to connect using SSO, the authenticator parameter must be set
to externalbrowser in the connection parameters in the /etc/odbc.ini
file. For example:
[ODBC Data Sources]
cyral = SnowflakeDSIIDriver
[cyral]
Driver = /usr/lib/snowflake/odbc/lib/libSnowflake.so
Description = Cyral
server = snowflake.cyral.com
port = 443
authenticator = externalbrowser
account = fva95771
role = accountadmin
database = cyral_copy
warehouse = compute_wh
schema = finance
Connect to Snowflake using the Python connector
In this section we’ll explore how to connect to a Snowflake server using the Snowflake Python connector. This will cover driver installation, setup, establishing connections and running simple queries.
Python connector prerequisites
This tutorial will be focused on the Python programming language, hence it’s expected that you already have a Python environment set up. If you don’t, please refer to this page for download links and instructions. In Ubuntu, the following commands will install all you need:
sudo apt-get update
sudo apt-get install python3.8
sudo ln -s /usr/bin/python3.8 /usr/bin/python
info
For this connector, Python 3.6+ is required.
To verify you can run Python code, the command below should have a similar output to what’s shown here.
$ python
Python 3.8.5 (default, Jan 27 2021, 15:41:15)
[GCC 9.3.0] on linux
Type "help", "copyright", "credits" or "license" for more information.
In addition, you’ll also install pip, to manage Python module installations.
sudo apt-get install python3-pip
Python connector setup
To set up Snowflake to be used with the Python connector, you first need to install the dependencies. For this, use this page to grab the appropriate requirements file (for your Python version) in the appropriate tag (for the desired connector version). For example, if you'd like to install the requirements for connector v2.4.3, using Python 3.8, then you'd run the following command:
python -m pip install -r https://raw.githubusercontent.com/snowflakedb/snowflake-connector-python/v2.4.3/tested_requirements/requirements_38.reqs
Then, to install the Python connector, run the following command (in
my example, <version> would be 2.4.3).
python -m pip install snowflake-connector-python==<version>
Connecting to Snowflake with the Python connector
Here’s an example Python file that can be used to connect to Snowflake and run a simple query:
#!/usr/bin/env python3
import snowflake.connector
ctx = snowflake.connector.connect(
user='<user>',
password='<password>',
account='<account>',
database='<database>',
schema='<schema>',
warehouse='<warehouse>',
)
cs = ctx.cursor()
try:
cs.execute("SELECT 1")
one_row = cs.fetchone()
print(one_row[0])
finally:
cs.close()
ctx.close()
Save this file as snowflake.py, and let’s walk through it.
- In line 2 you import the connector code.
- Lines 4-11 set the database credentials and parameters for the connecting user, and establish connection with the server.
- Lines 15 executes a query.
- And lines 16-17 retrieve and print the result for this query.
To run the program:
python snowflake.py
Or, alternatively, set execution permissions for the file and run it:
chmod u+x snowflake.py # One time operation
./snowflake.py
Use the Python connector to connect through the Cyral sidecar
You can use the same Python program above to connect to a Cyral sidecar with the Snowflake stack. You just need to include the host and port parameters in the connect method, to point to the sidecar.
ctx = snowflake.connector.connect(
user='<user>',
password='<password>',
account='<account>',
database='<database>',
schema='<schema>',
warehouse='<warehouse>',
host='<host>',
port='<port>',
)
tip
With Cyral in place, you need two additional parameters to log in. For connecting to a Snowflake server directly, you usually provide only the account name. When you connect through a Cyral sidecar, you must also provide the host and port of the server.
Use the Python connector for SSO
In order to connect using SSO, the authenticator parameter must be set
to externalbrowser in the connect method. Here's an example:
ctx = snowflake.connector.connect(
user='<user>',
authenticator='externalbrowser',
account='<account>',
database='<database>',
schema='<schema>',
warehouse='<warehouse>',
)
tip
You don’t need to specify the password in this case, as a browser window will open for the user to specify their SSO credentials. In this example we are not specifying host and port to connect, but if you wanted to use SSO with a Cyral sidecar, you would need to do that.
Connect to Snowflake using Tableau
Follow the steps below to use Tableau to connect to a Cyral-protected Snowflake database. Here we assume you're using the standard Tableau Online interface, but these steps apply to other Tableau versions as well.
caution
Be aware of the following limitations:
When accessing data through Tableau Prep, Cyral does not log the endUser name.
Tableau Flows are not supported. Please use a Tableau Workbook to connect to your data.
Follow the instructions for your type of credentials:
- Create a connection in Tableau with native authentication
- Create a connection in Tableau with SSO authentication
Create a connection in Tableau with native authentication
This procedure assumes you have not set up Cyral SSO for your Snowflake instance and will log in with Snowflake credentials.
To create a new connection to a server in Tableau, you need a connector to store the database connection details. In the Connect to Data window, click on the Connectors tab, then specify that you want to connect to a Snowflake server.
Provide the connection details:
- for server name, provide the Cyral sidecar address;
- if desired, add an optional role to use when connecting; and
- for authentication method, choose Username and Password, and provide the Snowflake username and password.
Click the Initial SQL tab and add the following query. This provides identity attribution, allowing Cyral to log the identity of the logged-in user responsible for the query.
SET TABLEAUENDUSER = [TableauServerUser]Click Sign In.
Once you've connected to a Snowflake server, you can run queries. Type your query in the Custom SQL tool and click Update Now to load the results.
Create a connection in Tableau with SSO authentication
This procedure assumes you have set up Cyral SSO for your Snowflake instance. Cyral supports using SSO with Snowflake and Tableau through OAuth. For that, an OAuth integration must be created in the Snowflake account. See the Snowflake documentation for the steps to set this up.
If you’re using Tableau Online, the following command statement will create an integration called ts_oauth_int1.
create security integration ts_oauth_int1
type = oauth
enabled = true
oauth_client = tableau_server;
After you've run the above statement in Snowflake, you can add a new connection in Tableau using OAuth.
Create or open the Tableau workbook where you'll add the database connection details.
Select Connect to Data. (If you're creating a new workbook, you'll see the Connect to Data screen automatically. If you're adding a data source in an existing workbook, click New Data Source in the workbook screen.)
In the Connect to Data screen, specify that you want to connect to a Snowflake server.
Provide the connection details:
- for server name, provide the Cyral sidecar address;
- if desired, add an optional role to use when connecting; and
- for authentication method, choose Sign in using OAuth. When you do this, a new browser window will pop up, and you can enter SSO credentials.
Click the Initial SQL tab and add the following query:
SET TABLEAUENDUSER = [TableauServerUser]Click Sign In.
Once you've connected to a Snowflake server, you can run queries. Type your query in the Custom SQL tool and click Update Now to load the results.
Log Snowflake queries that were initiated in Tableau
The Cyral data activity log shows
endUser: the user's Tableau identity. If you've set up Cyral SSO for Snowflake, then this will be the user's SSO user name as it appears in your identity provider.repoUser: Snowflake database user identity; this might be a native Snowflake account or, if you've set up SSO directly in your Snowflake instance, then it will be the SSO user name.dbRole: Snowflake database role
Here's a sample segment of the Cyral data activity log showing the identity information about a person who has connected to a Cyral-protected Snowflake repository using Tableau:
"identity": {
"endUser": "nancy.drew@hhiu.us",
"repoUser": "gsamsa",
"dbRole": "accountadmin"
}
caution
When using Tableau Flows or Tableau Prep, Cyral logs do not capture the endUser name.