Azure SQL - Bulk Extract
Overview
The Azure SQL - Bulk Extract Snap reads data from a SQL Server table on Azure and extracts the complete table data using the bcp utility.
The Snap extracts the complete table data from the SQL Server DB and writes it to a locally created temporary data file. The data is then processed from the data file and written to the output view. This Snap uses the bcp utility program internally to perform the bulk load action.
- This is a Read-type Snap.
Works in Ultra Tasks
Supported Accounts
Prerequisites
- Bulk Extract requires a minimum of SQL Server 2016 to work properly.
- Must install the BCP utility.
- A valid account with the required permissions.
Limitations
- The Azure SQL - Bulk Extract Snap fails when using the Active Directory Service Principal Certificate Authentication type because the BCP utility does not support it. We recommend that you use Active Directory Password authentication to connect to Azure SQL.
- Active Directory-based authentication is not supported in Bulk Extract.
Snap views
| Type | Description | Examples of upstream and downstream Snaps |
|---|---|---|
| Input |
This Snap has exactly one document input view. This Snap can have an upstream Snap that can pass a document output view, such as Structure or JSON Generator. |
|
| Output |
This Snap has exactly one document output view. The Snap outputs one document specifying the records extracted. |
|
| Learn more about Error handling. | ||
Examples
- Basic Use Case (Azure SQL Bulk Extract): This example demonstrates how to retrieve data from a table on the Azure Database and parse the output view in JSON format.
Snap settings
| Field/Field set | Description |
|---|---|
Label
String |
Required. Specify a unique name for the Snap. Modify this to be more appropriate, especially if more than one of the same Snaps is in the pipeline. Default value: Azure SQL - Bulk Extract |
Schema Name
String/Expression/ Suggestion |
The database schema name. If not defined, then the suggestion for the Table Name will retrieve all table names from all the schemas. The property is suggestible and will retrieve available database schemas during suggest values.
Note: The values can be passed using the pipeline parameters but not the upstream parameter.
Default value: N/A Example: SYS |
Table Name
String/Expression/ Suggestion |
Required. Table on which to execute the bulk load operation.
Note: The values can be passed using the pipeline parameters but not the upstream parameter.
Note: Currently, the BCP utility in the Linux environment has a limitation while processing table names. When loading data into a selected table and if the table name contains the characters '$%' or '!$', the combination works fine, however, BCP does not support if the table name contains the characters vice-versa as '%$' and '$!'.
Supported by BCP: Not supported by BCP: Default value: N/A Example: people |
BCP absolute path
String/Expression |
Absolute path of the BCP utility program in JCC's file system. If empty, the Snap will look for it in JCC's environment variable PATH.
Note: The path to the BCP executable should include the ".exe" extension to ensure the executable is actually referenced.
Default value: N/A Example: /opt/mssql-tools/bin/bcp |
Maximum error count
Integer/Expression |
Required. The maximum number of rows which can fail before the bulk load operation is stopped.
Default value: 10 Example: 50 |
Enable UTF-8 encoding
Checkbox |
Specify whether UTF-8 coding must be enabled or not. If enabled, the Snap updates the BCP command to support UTF-8 encoded characters.
Default value: Selected |
Snap execution
Dropdown list |
Choose one of the three modes in
which the Snap executes. Available options are:
|
Temporary files
During execution, data processing on Snaplex nodes occurs principally in-memory as streaming and is unencrypted. When processing larger datasets that exceed the available compute memory, the Snap writes unencrypted pipeline data to local storage to optimize the performance. These temporary files are deleted when the pipeline execution completes. You can configure the temporary data's location in the Global properties table of the Snaplex node properties, which can also help avoid pipeline errors because of the unavailability of space. Learn more about Temporary Folder in Configuration Options.