Bulk Loading and Unloading for Snowflake

Overview

Bulk loading is the fastest way to insert large numbers of rows into a Snowflake table. To use the bulk-load facility, set the BULKLOAD= LIBNAME option to YES. You can also perform bulk unloading (data retrieval) from Snowflake. To enable bulk unloading of data, set the BULKUNLOAD= LIBNAME option to YES.

The SAS/ACCESS engine handles bulk loading and bulk unloading through one of these methods.

• Snowflake internal stage: The engine uses PUT and GET commands to upload files to the internal stage. Bulk loading and unloading use the Snowflake COPY command to load staged files into or from a table.
• Amazon S3 bucket: The bulk-load facility uses the Amazon Simple Storage Service (S3) tool to move data to and from the client to the Snowflake database. Files are copied using S3 utilities. You can find more information about managing data on Snowflake on the Amazon Web Services documentation site.
• Azure ADLS storage: The Snowflake engine can bulk load data to and bulk unload data from files between an ADLS storage location and SAS.

Snowflake supports different types of stages.

• user stages: @~ is the name of the stage of the current user.
• table stages: @[<namespace>.]%<tablename> is the name of the stage for a table.
• internally named stages: @[<namespace>.]<internal_stage_names> is the name of the internal stage.
• external stages: These are supported only through the Amazon S3 bucket or through an Azure external stage.
  Note: Bulk loading and bulk unloading use CSV files, which can be compressed.

  For bulk loading and bulk unloading with Amazon S3, use these LIBNAME or data set options.

  BL_AWS_CONFIG_FILE=	BL_KEY=
  BL_AWS_PROFILE_NAME=	BL_REGION=
  BL_BUCKET=	BL_SECRET=
  BL_CONFIG_FILE=	BL_TOKEN=
  BL_ENCKEY=	BL_USE_SSL=

  For bulk loading and bulk unloading with Azure, see these topics:

  • Authentication for Bulk Loading to and Bulk Unloading from Azure
  • Bulk Loading and Bulk Unloading Data Files in Azure ADLS Locations

User and table stages are created automatically. You can create internally named stages with the Snowflake CREATE STAGE command. For details, see your Snowflake documentation.

Use the BL_INTERNAL_STAGE= LIBNAME option or data set option to specify the stage to use for bulk loading or bulk unloading. USER and TABLE are used as placeholders for the respective stages. You can add a path to the location. The internal stage is used by the SAS/ACCESS engine. The engine does not create the stage.

Options for Bulk Loading and Bulk Unloading

Here are the Snowflake bulk-load and bulk-unload options. For detailed information about these options, see Overview.

Except for BULKLOAD= and BULKUNLOAD=, all of these options are supported for bulk loading and bulk unloading.

Authentication for Bulk Loading to and Bulk Unloading from Azure

For bulk loading and bulk unloading data files in Azure, additional authentication is required. This authentication uses a Shared Access Signature token that you provide via the BL_AZURE_SAS= LIBNAME or data set option.

Use device-code authentication to access your Azure ADLS location:

• Use the following system options:

  AZURETENANTID= is required.

  AZURECACHELOC= is optional.

• The following options are all required, either as LIBNAME or data set options:

  BL_ACCOUNTNAME=

  BL_APPLICATIONID=

  BL_AZURE_SAS=

  BL_FILESYSTEM=

• On a user's first attempt to access data by using device-code authentication, an error message is printed in the SAS log. The user must authenticate with Microsoft. That process is explained in the documentation for the required AZURETENANTID= option.

Bulk Loading and Bulk Unloading Data Files in Azure ADLS Locations

You can bulk load or bulk unload data files that are stored in an Azure ADLS location. Before you can do this, ensure that you have the necessary values for authentication to Azure ADLS. For more information, see Authentication for Bulk Loading to and Bulk Unloading from Azure.

You can use these options when you bulk load or bulk unload data files in Azure ADLS:

• BL_ACCOUNTNAME= LIBNAME option and data set options (the stage account)
• BL_APPLICATIONID= LIBNAME option and data set options
• BL_AZURE_SAS= LIBNAME option and data set options (the stage application signature)
• BL_DNSSUFFIX= LIBNAME option and data set options
• BL_FILESYSTEM= LIBNAME option and data set options (the BLOB container)
• BL_FOLDER= LIBNAME option
• BL_TIMEOUT= LIBNAME option and data set options

Bulk Loading for PROC FEDSQL and PROC DS2

When you specify BULKLOAD=YES and associated LIBNAME options for bulk loading in the Snowflake LIBNAME statement, these options are automatically included in the connection string that is passed from the engine to PROC FEDSQL and PROC DS2. The BULKLOAD= options and associated data source-specific bulk load options are also available as FedSQL and DS2 table options. For more information, see FedSQL Statement Table Options by Data Source in SAS FedSQL Language Reference or DS2 Statement Table Options by Data Source in SAS DS2 Language Reference.

Examples

Here are some examples for various types of stages using BULKLOAD= and BULKUNLOAD=.

/* Uses the current user stage, @~/test1/ */
libname a snow <connection-options>
   bulkload=yes bulkunload=yes bl_internal_stage="user/test1";

/* Uses the table stage, @%"TABLE_NAME"/test1/, */
/* where TABLE_NAME is replaced with the table to load/unload */
libname a snow <connection-options>
   bulkload=yes bulkunload=yes bl_internal_stage="table/test1";

/* Uses the internal named stage, @MY_INTERNAL_STAGE/test1/ */
libname a snow <connection-options>
   bulkload=yes bulkunload=yes bl_internal_stage="MY_INTERNAL_STAGE/test1";