Azure SQL - Bulk Extract
On this Page
| Snap type: | Read | ||||||
|---|---|---|---|---|---|---|---|
| Description: | This Snap reads the data from the SQL server table on the Azure. ETL Transformations & Data FlowThis Snap enables the following ETL operations/flows: 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. Input & Output
Modes
| ||||||
| Prerequisites: | Bulk Extract requires a minimum of SQL Server 2016 to work properly. Must install the BCP utility. | ||||||
Limitations and Known Issues: | None | ||||||
| Configurations: | Account & AccessThis Snap uses account references created on the Accounts page of SnapLogic Manager to handle access to this endpoint. See Configuring Azure SQL Accounts for information on setting up this type of account. Active Directory-based authentication is not supported in Bulk Extract. Views
| ||||||
Settings | |||||||
Label | Required. The name for the Snap. You can modify this to be more specific, especially if you have more than one of the same Snap in your pipeline. | ||||||
Schema Name | The database schema name. In case it is not defined, then the suggestion for the Table Name will retrieve all tables names from all the schemas. The property is suggestible and will retrieve available database schemas during suggest values. The values can be passed using the pipeline parameters but not the upstream parameter. Example: SYS | ||||||
Table Name | Required. Table on which to execute the bulk load operation. The values can be passed using the pipeline parameters but not the upstream parameter. Example: people Default value: [None] Currently, the BCP utility in the Linux environment has a limitation while processing the table names. When loading the 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-a-versa as'%$' and '$!'. Examples: Not supported by BCP: "dbo"."sqldemo#^&%$" | ||||||
BCP absolute path | 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. Default value: [None] The path to the BCP executable should include the ".exe" extension to ensure the executable is actually referenced. Currently, the BCP utility in the Linux environment has a limitation while processing the table names. When loading the 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-a-versa as'%$' and '$!'. Examples: | ||||||
Maximum error count | Required. The maximum number of rows which can fail before the bulk load operation is stopped. Default value: 10 | ||||||
| Enable UTF-8 encoding | 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 | Select one of the three modes in which the Snap executes. Available options are:
| ||||||
Examples
Basic Use Case
In this pipeline, the Bulk Extract Snap retrieves the data from a table on the Azure Database. The output view in parsed in a JSON format.
Extract: BulkExtract reads the data from the Azure Sql Database.
Transform: JSON Formatter parses the data and converts it into JSON.
.png?version=1&modificationDate=1501874648747&cacheVersion=1&api=v2&width=452&height=250)
The execution displays the following output:
|
Typical Snap Configurations
The key configurations for the Snap are:
- Without Expression: Directly passing the table name from which the data is extracted.
With Expressions
- Query from an upstream Snap: The Mapper Snap passing the required Schema and Table name to the Snap.
- Pipeline Parameter: Pipeline parameter set to pass the required Schema and Table name to the Snap.
Advanced Use Case
The following describes a pipeline, with a broader business logic involving multiple ETL transformations, that shows how typically in an enterprise environment, Bulk import/read functionality is used.
Pipeline download link is available below.
This pipeline reads and moves data from the Azure SQL database to the Redshift using the Redshift BulkLoad Snap. The data is migrated to Redshift, and the Snap helps in achieving this with high performance and ease of use.
Extract: BulkExtract reads the data from the Azure SQL Database.
Transform: JSON Formatter parses the data and converts it into JSON.
Load: Redshift BulkLoad will loads the data being formatted using the JSON Parser.
Downloads
Important steps to successfully reuse Pipelines
- Download and import the Pipeline into SnapLogic.
- Configure Snap accounts as applicable.
- Provide Pipeline parameters as applicable.