Azure Synapse SQL - Bulk Load

In this article

Overview

You can use this Snap to perform a bulk load operation from an external location to the target table. The input data is first written to a temporary data file and is loaded from the data file into the target table.

 

Snap Type

The Azure Synapse SQL Bulk Load Snap is a Write-type Snap that performs a bulk load operation.

Prerequisites

The SQL Server JDBC driver is required in the Azure SQL database account.

Support for Ultra Pipelines

Does not support Ultra Pipelines

Limitation

When the table name contains single quote characters, the No table found error is displayed. This is because of the JDBC driver limitation.

Known Issues

None.

Snap Views

Type

Format

Number of Views

Examples of Upstream and Downstream Snaps

Description

Type

Format

Number of Views

Examples of Upstream and Downstream Snaps

Description

Input 

Document

 

  • Min: 0

  • Max: 2

Mapper

Input document must not contain any data other than data to be bulk-loaded. If the number of keys is smaller than the number of columns in the target table, the Snap fills missing keys with null values. If key names in the input document is different from column names, Mapper Snap can be used to map key names to column names.

Output

Document

  • Min: 0

  • Max: 1

JSON Formatter

This Snap has at the most one document output view. If an output view is available, it produces the number of records loaded.Reports the bulk-load result in a key-value pair, e.g. {"status" : "34687 records loaded"}
If no record is loaded (e.g. due to errors), no output document is produced.

Error

Error handling is a generic way to handle errors without losing data or failing the Snap execution. You can handle the errors that the Snap might encounter while running the Pipeline by choosing one of the following options from the When errors occur list under the Views tab. The available options are:

  • Stop Pipeline Execution: Stops the current pipeline execution when the Snap encounters an error.

  • Discard Error Data and Continue: Ignores the error, discards that record, and continues with the rest of the records.

  • Route Error Data to Error View: Routes the error data to an error view without stopping the Snap execution.

Learn more about Error handling in Pipelines.

Snap Settings

  • Asterisk (*): Indicates a mandatory field.

  • Suggestion icon (): Indicates a list that is dynamically populated based on the configuration.

  • Expression icon (): Indicates whether the value is an expression (if enabled) or a static value (if disabled). Learn more about Using Expressions in SnapLogic.

  • Add icon (): Indicates that you can add fields in the field set.

  • Remove icon (): Indicates that you can remove fields from the field set.

Field Name

Field Type

Field Dependency

Description

Field Name

Field Type

Field Dependency

Description

Label*

Default ValueAzure Synapse SQL - Bulk Load
ExampleBulk_Load

String

N/A

Specify a unique name for the Snap.

 

Schema Name

Default value: None
Example: SYS

String/Expression

N/A

Specify the database schema name. In case it is not defined, then the suggestion for the table name retrieves all tables names of all schemas. The property is suggestible and retrieves available database schemas during suggest values.

Table Name*

Default Value: None
Example: users

String/Expression

N/A

Specify the table name or select the table from the suggestion list to load the incoming data into.

Data Source

Default Value: Input View
Example: External Source

Dropdown list

N/A

Select either of the following sources from where the data must load:

  • Input view: The data is loaded from the upstream Snap.

  • External storage: The data is loaded from an external source.

When the Data Source is Input View, the incoming data is written to a temporary file in the Snaplex and then uploaded to the staged location (Azure storage) using the Azure API. After uploading the files to Azure storage, the Snap runs the COPY INTO command to load the data from files to table. The temporary files are deleted after the execution (failed or successful) is completed. Hence, if you are running huge data Pipelines, we recommend you to configure your Snaplex instance disk space accordingly.

Create table if not present

Default Value: Deselected

Checkbox

Appears only when you select Input view for Data Source.

Select this checkbox to create the target table if it does not exist. Otherwise, the system displays the "table not found" error.

If a target table does not exist when the Snap tries to do the bulk load, and if you select this checkbox, the Snap creates the table with the columns and data types. If you want a table to be created with the same schema as a source table, you can include a second input view for this Snap. This view can be used to pass metadata about the table, effectively allowing you to replicate a table from one database to another.

The table metadata document that is read in by the second input view contains a dump of the JDBC DatabaseMetaData class. The document can be manipulated to affect the CREATE TABLE statement that is generated by this Snap. For example, to rename the name column to full_name, you can use a Mapper Snap that sets the path $.columns. Name.COLUMN_NAME to full_name.

The Snap does not automatically fix the errors encountered during table creation, because it may require user intervention to resolve correctly. For example, if the source table contains a column with a type that does not have a direct mapping in the target database, Snap fails to execute. In such a case, add a Mapper Snap to change the metadata document to explicitly set the values required to produce a valid CREATE TABLE statement.

Purge files

Default Value: Deselected
Example: Selected

Checkbox

Appears only when you select External storage for Data Source.

Select this checkbox if you want to purge the data files automatically from the external storage after the data is loaded successfully.

Table Column Settings

Use this field set to define columns to map the source data to the columns in the target table. The source data can be either from the input view or external storage.

Column Name

Default Vaue: N/A
Example: $first_name

String/Expression/Suggestion

N/A

Specify the column name in the target table.

Default Value

Default Vaue: N/A
Example: new

String/Expression

N/A

Specify the default value that replaces null value if any in the input file.

Source Column Position

Default Value: None
Example: Second

String/Expression

Appears only when you select Exteranl storage for Data Source.

Specify the position of the column for the source file within a row.

Add Quotes

Default Vaue: Deselected

Checkbox

N/A

Select this checkbox to enclose default value in quotes.

File List

Appears only when you select External source for Data Source.

Use this fieldset to specify the list of files that must be loaded to the target table.

File

String/Expression

Appears only when you select External source for Data Source.

Specify the file to be loaded to the target table.

File Name Pattern

String/Expression

Appears only when you select External source for Data Source.

Specify an expression or string that indicates the absolute path of the file names to match the files in the external location.

File Format Type

Dropdown list

Appears only when you select External source for Data Source.

Choose one of the following file format types to load data into the target table or unload data from the target table:

  • CSV

  • PARQUET

  • ORC

Copy Arguments

 

Use this fieldset to configure the list of arguments that you want the Snap to generate for the copy command.

With

Default Value: None
Example: ENCODING={'UTF8'|'UTF18'}

String/Expression

N/A

Specify the list of arguments to be used when loading the data. For example, the argument With MAXERRORS=1000 enables the Snap to ignore 1000 record errors and continues with the execution of the Snap and terminates the operation after exceeding the 1000 errors.

Parallel Transfer Options

This property enables in faster loading of data.

Max Concurrency*

Default Value: 5
Example: 10

Integer

N/A

Specify the maximum number of parallel requests that are to be issued at a given time as part of a single parallel transfer.

Block Size (MB)*

Default Value: 2
Example: 6

Integer

N/A

Specify the size of data that must be chunked to transfter at a time.

Snap Execution

Default ValueExecute only
Example: Validate & Execute

Dropdown list

N/A

Select one of the three modes in which the Snap executes. Available options are:

  • Validate & Execute: Performs limited execution of the Snap, and generates a data preview during Pipeline validation. Subsequently, performs full execution of the Snap (unlimited records) during Pipeline runtime.

  • Execute only: Performs full execution of the Snap during Pipeline execution without generating preview data.

  • Disabled: Disables the Snap and all Snaps that are downstream from it.

Examples

Loading Data in Bulk to Azure Synapse SQL

This example Pipeline demonstrates how to load data in bulk using the Azure Synapse SQL Bulk Load Snap.

First, configure the Snowflake Select Snap to select the data that you want to load into the Azure Synapse SQL Database.

The selected data to be loaded is displayed in the output.

Next, configure the Azure Synapse SQL - Bulk Load Snap to load the data into the BigDataTest7612 table.

On validation, the success message of the Bulk Load is displayed.

Download this Pipeline

Downloads

  File Modified

File Example_Azure_Synapse_BulkLoad.slp

Aug 10, 2022 by Sandhya Sriram

Snap Pack History

Release

Snap Pack Version

Date

Type

Updates

November 2024

main29029

Stable

Updated and certified against the current SnapLogic Platform release.

August 2024

main27765

Stable

Upgraded the org.json.json library from v20090211 to v20240303, which is fully backward compatible.

May 2024

437patches26597

Latest

Fixed an issue with the Azure Synapse SQL Select Snap that displayed an error and did not retry the connection when the serverless SQL pool was warming up.

May 2024

main26341

Stable

February 2024

436patches25597

-

Upgraded the jOOQ library of the Azure Synapse SQL Snap Pack from v3.9.1 to v3.17.x.

This upgrade is currently NOT available with the latest distribution. However, you can still consume this patch through 436patches25597. This update is scheduled to be included in the latest distribution release on April 10, 2024, and will be a part of the stable distribution with the GA release on May 8. 2024.

Behavior change:

The jOOQ library upgrade for Azure Synapse SQL Snap Pack, done as part of the 436patches25597, resulted in the following behavior change:

Previously, when “null”(string) was passed from the upstream Mapper Snap for the Bigint datatype, the /wiki/spaces/DRWIP/pages/3011674578Snap updated it as null without displaying any error. It displayed the status as 0 and the output message as Success. Now, the Snap displays the following error in this scenario:

Error converting value(expecting a number but you are sending String(“null”))

If you pass null from Mapper Snap with the expression enabled, the Snap updates the null value for the BigInt datatype correctly, as earlier.

February 2024

main25112

Stable

Updated and certified against the current SnapLogic Platform release.

November 2023

main23721

Stable

Updated and certified against the current SnapLogic Platform release.

August 2023

main22460

Stable

The Azure Synapse SQL - Execute Snap now includes a new Query type field. When Auto is selected, the Snap tries to determine the query type automatically.

May 2023

433patches21718

Latest

Fixed an issue with the Azure Synapse SQL Bulkload Snap that caused a 404 error when the External Location was set to ADLS Gen2 and the Azure Folder field was not specified in the account settings.

May 2023

main21015

Stable

Upgraded with the latest SnapLogic Platform release.

February 2023

432patches20389

Latest

The Azure Synapse SQL Account and Azure Synapse SQL Active Directory Account now support the latest JDBC Driver 12.2.0.jre11 by default. You do not have to upload the JAR file in the JDBC Driver field manually.

February 2023

main19844

 

Stable

Upgraded with the latest SnapLogic Platform release.

November 2022

431patches19493

Latest

The Azure Synapse SQL - Bulk Load Snap no longer fails when loading the JDBC driver from SLDB as it now uses the uploaded JAR driver when performing all bulk load operations.

November 2022

431patches19410

Stable

Fixed the Azure SQL - Execute Snap using the Azure SQL Active Directory Account that failed with SQL operation failed errors in environments using federated authentication

November 2022

431patches19410

Latest

Fixed the Azure SQL - Execute Snap using the Azure SQL Active Directory Account that failed with SQL operation failed errors in environments using federated authentication

November 2022

431patches19354