LIBNAME Statement for the Google BigQuery Engine

Overview

This section describes the LIBNAME statement options that SAS/ACCESS Interface to Google BigQuery supports. For general information about this feature, see LIBNAME Statement for Relational Databases.

Here is the LIBNAME statement syntax for accessing Google BigQuery.

For general information about the LIBNAME statement that is not specific to SAS/ACCESS, see LIBNAME Statement in SAS Global Statements: Reference.

Arguments

libref

specifies any SAS name that serves as an alias to associate SAS with a database, schema, server, or group of tables and views.

bigquery

specifies the SAS/ACCESS engine name for the Google BigQuery interface.

connection-options

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 Google BigQuery database in several ways.

Here is how these options are defined.

Note: All of the following connection options are also valid in the CONNECT statement when you use the SQL pass-through facility (SQL procedure) to connect to your DBMS.

CRED_PATH=’path-and-filename’

specifies the location of a credential file that enables authentication to Google Cloud Platform.

This value is masked in the SAS log. This option accepts values that have been encoded using PROC PWENCODE. SAS/ACCESS recognizes encoded values as those that begin with a SAS encoding tag. For more information, see PWENCODE Procedure in Base SAS Procedures Guide.

For more information about the credential file, see the getting started information in your Google Cloud documentation.

Valid in: SAS/ACCESS LIBNAME statement

Aliases: CREDPATH=, CRED_FILE=, CREDFILE=

Default: none

Requirement: You must use single quotation marks around the value for CRED_PATH=.

Example: cred_path='/u/authfiles/BigQuery/xxx-yyy-8e99c10a22537.json'

PROJECT='project-ID'

specifies the project ID for a Google Cloud Platform project.

Valid in: SAS/ACCESS LIBNAME statement

Default: none

Requirement: This value is required to access Google BigQuery in a LIBNAME statement.

Example: project='project1'

LIBNAME options

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 Google BigQuery 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.

SAS/ACCESS LIBNAME Options for Google BigQuery

Option	Default Value	Valid in CONNECT
ACCESS=	none
ALLOW_LARGE_RESULTS=	OFF
BL_BUCKET=	none	●
BL_DEFAULT_DIR=	temporary file directory that is specified by the UTILLOC= system option	●
BL_DELETE_DATAFILE=	YES	●
BL_DELIMITER=	bell character (ASCII 0x07)	●
BL_NUM_READ_THREADS=	4	●
BULKLOAD=	NO	●
BULKUNLOAD=	NO
CLIENT_ID=	none
CLIENT_SECRET=	none
DBCLIENT_MAX_BYTES=	Maximum number of bytes per character for the current session encoding	●
DBCREATE_TABLE_OPTS=	none
DBGEN_NAME=	DBMS	●
DBSASLABEL=	COMPAT
DBSLICEPARM=	none
DEFER=	NO	●
DIRECT_SQL=	YES
DRIVER_TRACE=	none
DRIVER_TRACEFILE=	none
DRIVER_TRACEOPTIONS=	Trace file is overwritten and contains no time stamps or thread identification.
FETCH_NUMERIC_TYPE=	FLOAT64
GO_ENABLE_LOGGING=	NO
GO_LOG_FILE=	none
GO_LOG_FORMAT=	JSON
GO_LOG_LEVEL=	INFO
IGNORE_FEDSQL_OBJECTS=	NO
INSERTBUFF=	automatically calculated based on row length
LARGE_RESULTS_DATASET=	_sasbq_temp_tables
LARGE_RESULTS_EXPIRATION_TIME=	86400000
LARGE_RESULTS_PROJECT=	none
MAX_BINARY_LEN=	2000
MAX_CHAR_LEN=	2000
MODE=	STANDARD
MULTI_DATASRC_OPT=	NONE
PROJECT_LIST=	none
PROXY=	none
QUALIFIER=	none
READ_MODE=	STANDARD
READBUFF=	automatically calculated based on row length	●
REFRESH_TOKEN=	none
SCANSTRINGCOLUMNS=	NO
SCHEMA=	none
SPOOL=	YES
SQL_FUNCTIONS=	none
SQL_FUNCTIONS_COPY=	none
SQLGENERATION=	none
STRINGDATES=	NO	●
TRACE=	NO	●
TRACEFILE=	none	●
TRACEFLAGS=	none
USE_INFORMATION_SCHEMA=	YES

Troubleshooting LIBNAME Connections: Set a GOMEMLIMIT= Value

If you experience an exception while connecting to Google BigQuery with a LIBNAME statement, then you might need to specify a value for the GOMEMLIMIT= environment variable. This variable must be set from the command line prior to starting your SAS session. Here is an example that shows how to set this variable:

export GOMEMLIMIT=250MiB

For more information about setting this environment variable during configuration, see Configure SAS/ACCESS Interface to Google BigQuery.

Google BigQuery LIBNAME Examples

Use the following example to establish a connection between SAS and Google BigQuery. This example connects to project Project1 in schema Meddata.

libname mydb bigquery project='Project1' schema='Meddata';

If you require a credentials file, you might specify a LIBNAME statement like this one:

libname mydb bigquery project='Project1' schema='Meddata' 
                  cred_path='/u/fedadmin/BigQuery/xxx-yyy-9e99c10a8888.json';