Determines whether SAS uses a DBMS facility to insert data into a DBMS table.
| Valid in: | SAS/ACCESS LIBNAME statement |
|---|---|
| Categories: | Bulk Loading |
| Data Set Control | |
| Aliases: | BCP= [ODBC] |
| FASTLOAD= [Teradata] | |
| Default: | YES [Hadoop, Impala, JDBC, Spark] |
| NO [Aster, Google BigQuery, Greenplum, HAWQ, Informix, Microsoft SQL Server, MySQL, ODBC, OLE DB, Teradata, Yellowbrick] | |
| none [Snowflake] | |
| Data source: | Aster, Google BigQuery, Greenplum, Hadoop, HAWQ, Impala, Informix, JDBC, Microsoft SQL Server, MySQL, ODBC, OLE DB, Snowflake, Spark, Teradata, Yellowbrick |
| Notes: | Support for Microsoft SQL Server, Spark in HDFS, and Yellowbrick was added in SAS 9.4M7. |
| Support for Informix and for Spark in Databricks was added in SAS 9.4M9. | |
| See: | BULKUNLOAD= LIBNAME option, BULKLOAD= data set option, BULKUNLOAD= data set option, Bulk Loading for Impala |
Table of Contents
calls a DBMS-specific bulk-load facility to insert or append rows to a DBMS table.
does not call the DBMS bulk-load facility.
Hadoop and Spark: When BULKLOAD=YES, the engine uses HDFS to load data into Hive. HDFS connectivity is therefore required, except for operations that occur when DBIDIRECTEXEC is enabled. When BULKLOAD=NO, you can write to Hive without HDFS connectivity.
Hadoop and Impala: Set your environment variables appropriately before you specify BULKLOAD=YES. If you use SAS and Hadoop JAR files to interact directly with your HDFS system, then specify the SAS_HADOOP_CONFIG_PATH and SAS_HADOOP_JAR_PATH environment variables. If you use the WebHDFS interface to push data to HDFS, then specify SAS_HADOOP_CONFIG_PATH and specify SAS_HADOOP_RESTFUL=1. Spark does not require SAS_HADOOP_RESTFUL.
For Impala, specifying BULKLOAD=NO does not change the loading behavior since all data is loaded in bulk. However, specifying BULKLOAD=YES is required before you can specify other options that are related to bulk loading.
For more information, see SAS Hadoop Configuration Guide for Base SAS and SAS/ACCESS.
Informix: When BULKLOAD=YES, the DBACCESS utility is used to load data to Informix.
JDBC: When BULKLOAD=YES, JDBC uses BatchUpdate capabilities to load data. If BULKLOAD=NO or if the JDBC driver does not support BatchUpdate, then SAS/ACCESS Interface to JDBC uses SQL to load data.
Microsoft SQL Server: The BULKLOAD= LIBNAME option enables Microsoft SQL Server to load data to Azure Synapse Analytics (SQL DW) with a Microsoft Azure Data Lake Storage Gen2 storage account. For more information, see Bulk Loading to Azure Synapse Analytics.
Spark: The Spark engine supports bulk loading to Spark using HDFS. Starting in SAS 9.4M9, it also supports bulk loading to Databricks in Microsoft Azure and in Amazon Web Services. For more information, see Bulk Loading and Bulk Unloading with the Spark Engine.
Teradata: This option calls the FastLoad utility for the TPT API. For more information, see TPT API FastLoad Supported Features and Restrictions.
Yellowbrick: This option calls the YBLOAD utility.