Examples of Bulk-Load Options
Use the following examples to configure the bulk-load option for the information map.
For a list of databases that are supported by SAS Customer Intelligence 360, see the Supported Databases topic in SAS 360 Direct System Requirements.
Review the SAS Notes for your Access product and release of SAS for possible errors before using bulk-load options. For example, see Usage Note 65325: SAS/ACCESS® Interface to Microsoft SQL might throw a "Datetime field overflow" error during bulk load.
Where the Bulk-LoadSchema parameter is optional, the default schema of the user who connects is used if it is not specified. A recommended practice is to create a separate schema for temporary bulk-load tables for easy management and monitoring.
|
Database |
<<db_engine>> |
BulkloadOptions_ <<db_engine>> |
BulkloadSchema_<<db_engine>> |
|---|---|---|---|
|
Amazon Aurora |
POSTGRES |
BULKLOAD=YES BL_PSQL_PATH='full-path-of-PSQL' BL_DEFAULT_DIR='full-path-to-bl-temp-file-location' BL_LOGFILE='full-path-and-filename-of-logfile' |
(optional schema-name) |
|
Amazon Redshift |
SASIORST |
bulkload=yes bl_bucket='name-of-temp-storage-bucket' bl_default_dir='full-path-to-bl-temp-file-location' bl_config='full-path-to-bl-config-location' BL_NUM_DATAFILES=1 |
(optional schema-name) |
|
Databricks (AWS) IMPORTANT To use Databricks as a data source, you must use SAS/ACCESS Interface to Spark 9.46 with SAS 9.4M9 and apply the latest Access Interface hot fix. |
SASIOSK |
BULKLOAD=YES BULKUNLOAD=YES BL_BUCKET='sas-databricks-s3-bucket' BL_AWS_CONFIG='path-for-config-file' BL_AWS_CREDENTIALS_FILE='path-for-credentials-file' |
(optional schema-name) |
|
Databricks (Azure) IMPORTANT To use Databricks as a data source, you must use SAS/ACCESS Interface to Spark 9.46 with SAS 9.4M9 and apply the latest Access Interface hot fix. |
SASIOSK |
BULKLOAD=YES BL_APPLICATIONID='application-ID' BL_ACCOUNTNAME='account-name' BL_FILESYSTEM='file-system-name' BL_FOLDER= 'folder-name' |
(optional schema-name) |
|
EMC Greenplum |
GREENPLM |
bulkload=yes bl_format='csv' bl_host='hostserver.mycompany.com' bl_port='port number' bl_protocol='gpfdist' bl_delete_datafile=yes bl_null = '\n' |
(optional schema-name) |
|
Google BigQuery |
BIGQUERY |
BULKLOAD=YES BL_DEFAULT_DIR='full-path-to-bl-temp-file-location' |
(schema-name) |
|
IBM DB2/UDB |
DB2 |
BULKLOAD=YES BL_METHOD=CLILOAD |
(optional schema-name) |
|
Netezza |
NETEZZA |
BULKLOAD=YES BL_OPTIONS="LOGDIR ’full-path-for-log-file’" |
(database-name) |
|
Oracle |
ORACLE |
BL_DIRECT_PATH=YES BULKLOAD=YES BL_DEFAULT_DIR='full-path-to-bl-temp-file-location' |
(optional schema-name) |
|
PostgreSQL |
POSTGRES |
BULKLOAD=YES BL_PSQL_PATH='full-path-of-PSQL' BL_DEFAULT_DIR='full-path-to-bl-temp-file-location' BL_LOGFILE='full-path-and-file-name-for-logfile' |
(optional schema-name) |
|
SAP HANA |
SAPHANA |
INSERTBUFF=32767 table_type=column |
(optional schema-name) |
|
Snowflake |
SASIOSNF |
BULKLOAD=YES |
(optional schema-name) |
|
SQL Server ODBC |
ODBC |
BULKLOAD=YES |
(optional schema-name) |
|
SQL Server OLE DB |
OLEDB |
BULKLOAD=YES |
(optional schema-name) |
|
SQL Server SAS/ACCESS |
SQLSVR |
INSERTBUFF=32767 DBCOMMIT=0 |
(optional schema-name) |
|
Teradata (Fastload) |
TERADATA |
BULKLOAD=YES SLEEP=1 TENACITY=1 |
(optional schema-name) |
|
Teradata (TPT) |
TERADATA |
(optional schema-name) |
- For Databricks (AWS)
Example configuration file content:
databricks_aws_config: [default] region = us-east-1 output = jsonExample credentials file content:
databricks_aws_credentials: [default] aws_access_key_id = <key ID> aws_secret_access_key = <access key> - For Databricks (Azure)
The Azure Databricks setup does not contain SAS server configuration or credentials files, nor any library bulk-load options for files, to allow bulk upload of table data to Azure Databricks. Instead, on your first attempt to upload to Azure Databricks, you will receive an error in the SAS logs that directs you to register access from the device. For more information, see AZUREAUTHCACHELOC System Option in SAS System Options: Reference.