This section describes the LIBNAME statement options that SAS/ACCESS Interface to Snowflake supports. For general information about this feature, see LIBNAME Statement for Relational Databases.
Here is the LIBNAME statement syntax for accessing Snowflake.
For general information about the LIBNAME statement that is not specific to SAS/ACCESS, see LIBNAME Statement in SAS Global Statements: Reference.
specifies any SAS name that serves as an alias to associate SAS with a database, schema, server, or group of tables and views.
snowspecifies the SAS/ACCESS engine name for the Snowflake interface.
provide connection information and control how SAS manages the timing and concurrence of the connection to the DBMS. When you use the LIBNAME statement, you can connect to the Snowflake database in several ways. Specify only one of these required methods for each connection because they are mutually exclusive.
You can also specify any of these optional connection options.
The ODBC driver supports some additional connection options. To add these to the connection string for the SAS/ACCESS engine, specify them on the CONOPTS= LIBNAME option.
Here is how these options are defined.
specifies additional connection options for the Snowflake database. Separate multiple options with a semicolon. See your Snowflake ODBC driver documentation for a list of ODBC connection options that your ODBC driver supports.
Alias: CONNECT_OPTIONS=
specifies the default Snowflake database to use for sessions that the driver initiates and that contains the tables and views that you want to access. If the database name contains spaces or nonalphanumeric characters, you must enclose it in quotation marks.
Alias: DB=
Requirement: If you specify this option, you must also set SCHEMA=.
specifies the ODBC driver.
Default: SnowflakeDSIDriver
Requirement: On UNIX, this must be configured in odbcinst.ini.
specifies the configured Snowflake ODBC data source to which you want to connect. Use this option if you have existing Snowflake ODBC data sources that are configured on your client. This method requires additional setup through the odbc.ini file on UNIX platforms. So it is recommended that you use this connection method only if you have existing, functioning data sources that have been specified.
Alias: DATASRC=, DS=
Requirement: If you use this method, you just specify the user ID and password in the LIBNAME statement, even if these are already specified in the ODBC data source.
Alias: PASS=, PW=, PWD=, USING=
specifies the password that is associated with your Snowflake user ID. If the password contains spaces or nonalphanumeric characters, you must enclose it in quotation marks.
specifies the port number that is used to connect to the specified Snowflake database or server.
Alias: SERVICE=, SERVICE_NAME=
Default: 443
specifies the default Snowflake role to use for sessions that the driver initiated. The specified role should be one that has been assigned to the specified user. If the specified role does not match any roles that are assigned to the user, sessions that the driver initiated have no role initially. However, a role can always be specified from within the session.
specifies the default Snowflake schema to use for sessions that the driver initiated.
Alias: OWNER=
Default: PUBLIC
Requirement: The SCHEMA= option is required in LIBNAME connections to Snowflake.
specifies the host name or IP address where the Snowflake database is running. If the server name contains spaces or nonalphanumeric characters, you must enclose it in quotation marks.
Alias: HOST=, HOSTNAME=
specifies the Snowflake user name (also called the user ID) that you use to connect to your database. If the user name contains spaces or nonalphanumeric characters, you must enclose it in quotation marks.
Alias: UID=
specifies the default Snowflake warehouse to use for sessions that the driver initiated.
specify how SAS processes DBMS objects. Some LIBNAME options can enhance performance, and others determine locking or naming behavior. The following table describes the LIBNAME options for SAS/ACCESS Interface to Snowflake with the applicable default values. This table also identifies LIBNAME options that are valid in the CONNECT statement in the SQL procedure. For details, see LIBNAME Options for Relational Databases.
|
Option |
Default Value |
Valid in CONNECT |
|---|---|---|
|
none |
|
|
|
none |
|
|
|
YES |
● |
|
|
none |
||
|
none |
||
|
~/.aws/config (UNIX) C:\Users\<userid>\.aws\config (Windows) |
||
|
none |
||
|
none |
||
|
none |
||
|
NO |
||
|
YES |
||
|
dfs.core.windows.net |
||
|
none |
||
|
none |
|
|
|
2 for an X-Small warehouse; 4 for a Small warehouse; 8 for Medium and larger warehouses |
|
|
|
none |
|
|
|
60 |
||
|
none |
|
|
|
NO |
|
|
|
SHAREDREAD |
● |
|
|
none |
● |
|
|
none |
● |
|
|
0 |
● |
|
|
none |
|
|
|
none |
● |
|
|
none |
● |
|
|
none |
|
|
|
DBMS |
● |
|
|
none |
|
|
|
none |
|
|
|
1024 |
● |
|
|
NO |
|
|
|
YES |
|
|
|
NO |
● |
|
|
COMPAT |
|
|
|
1 |
|
|
|
NO |
● |
|
|
NO |
|
|
|
YES |
|
|
|
automatically calculated based on row length |
|
|
|
0 |
● |
|
|
NONE |
|
|
|
none |
|
|
|
YES (see Naming Conventions for Snowflake) |
|
|
|
YES (see Naming Conventions for Snowflake) |
|
|
|
none |
|
|
|
0 |
● |
|
|
automatically calculated based on row length |
● |
|
|
NO |
● |
|
|
NO |
|
|
|
PUBLIC |
|
|
|
YES |
|
|
|
none |
|
|
|
none |
|
|
|
none |
|
|
|
NO |
|
|
|
NO |
|
|
|
none |
● |
|
|
NO |
|
|
|
NO |
|
In the example below, SERVER=, DATABASE=, USER=, and PASSWORD= are the connection options.
LIBNAME mydblib snow server=mysrv1 database=test
user=myusr1 password=mypwd1;
proc print data=mydblib.customers;
where state='CA';
run;In this next example, DSN=, USER=, and PASSWORD= are the connection options. The Snowflake database is configured in the odbc.ini file or a similarly named configuration file on UNIX platforms.
LIBNAME mydblib snow dsn=snow user=myusr1 password=mypwd1
schema=sasuser;
proc print data=mydblib.customers;
where state='CA';
run;In the example below, SERVER=, USER=, PASSWORD=, and SCHEMA= are the connection options.
libname a snow server="saspartner.snowflakecomputing.com"
user=myusr1 password=mypwd1 schema=sasuser;