BULKLOAD= Table Option

Loads rows of data as one unit.

Category: Bulk Loading
Interaction: Used in conjunction with BULKOPTS= Table Option.
Data source: DB2 under UNIX and PC, Impala, MDS, Oracle, Teradata

Syntax

BULKLOAD= YES | NO

Arguments

YES

calls a DBMS-specific bulk load facility in order to insert or append rows to a DBMS table.

NO

does not call the DBMS-specific bulk load facility. This is the default value.

Details

Overview

Using BULKLOAD=YES is the fastest way to insert rows into a DBMS table.
You can specify data source-specific options in the BULKOPTS= table option (BL_BULKOPTS= for Oracle). BULKOPTS= functions as a container for the data source-specific options. For more information, see BULKOPTS= Table Option.
When the BULKLOAD= table option is not set, a simple multi-row insert SQL scheme is used to insert data rows.

Usage Notes

Impala

Bulk loading to the Impala server can be accomplished in two ways: you can use the WebHDFS interface to Hadoop to push data to HDFS, or you can configure a required set of Hadoop JAR files. Both approaches require Hadoop configuration files needed by SAS to be in one location and available to the client machine. To use WebHDFS, you must additionally set the SAS_HADOOP_RESTFUL= environment variable to 1. To use Java, you must make the Hadoop JAR location known to the client machine and ensure that the SAS_HADOOP_RESTFUL= environment variable is not set to 1 (or TRUE or YES).
Specifying BULKLOAD=YES causes two CREATE TABLE statements to be issued to the Impala server. One creates the target Impala table. The other creates a temporary table. SAS uses WebHDFS to upload table data to the HDFS /tmp directory. The resulting file is a UTF-8 delimited text file. SAS issues a LOAD DATA statement to move the data file from the /tmp directory to the temporary table, then issues an INSERT INTO statement that copies and transforms the text data from the temporary table to the target table. The temporary table is then deleted from HDFS.

MDS

BULKLOAD= is available for insert operations only. When the BULKLOAD= table option is set, newly inserted rows are committed immediately and become visible to existing transactions. When the BULKLOAD= table option is not set, newly inserted rows are not visible until the existing transactions are committed or rolled back.

Teradata

Specifying BULKLOAD=YES invokes the Teradata Parallel Transporter (TPT) API protocol driver. The default TPT operator is the Stream operator. Use the BULKOPTS= table option to specify a different TPT operator.
Last updated: February 23, 2017