MS Azure SQL Data Warehouse V2 Source Properties
In a mapping, you can configure a Source transformation to represent a single Microsoft Azure SQL Data Warehouse V2 object. When you read data from Microsoft Azure SQL Data Warehouse, you can configure key range partitioning to optimize the mapping performance at run time.
The following table describes the Microsoft Azure SQL Data Warehouse V2 properties that you can configure in a Source transformation:
Property | Description |
|---|
Connection | Name of the source connection. |
Source Type | Source type. Select Single Object or Parameter. |
Object | Name of the source object. |
Sort | Sort is not supported for Microsoft Azure SQL Data Warehouse V2 Connector. |
The following table describes the Microsoft Azure SQL Data Warehouse V2 source advanced properties that you can configure in a Source transformation:
Property | Description |
|---|
Azure Blob Container Name | Microsoft Azure Storage container name. |
Field Delimiter | Character used to separate fields in the file. Default is 0x1e. You can select the following field delimiters from the list: ~ ` | . TAB 0x1e |
Number of Concurrent Connections to Blob Store | Number of concurrent connections to extract data from the Microsoft Azure Blob Storage. Default is 4. |
SQL Override | When you read data from a Microsoft Azure SQL Data Warehouse object, you can configure SQL overrides and define constraints. |
Quote Character | The Secure Agent Skips the specified character when you read data from Microsoft Azure SQL Data Warehouse. |
Pre-SQL | Pre-SQL command that must be run before reading data from the source. |
Post- SQL | Post-SQL command that must be run after writing data to the target. |
Tracing Level | Amount of detail displayed in the session log for the transformation. You can choose Normal, Verbose Initialization, or Verbose Data. Default is Normal. |
Key Range Partitioning
You can configure key range partitioning when you use a Mapping Configuration task to read data from Microsoft Azure SQL Data Warehouse sources. With key range partitioning, the Secure Agent distributes rows of source data based on the fields that you define as partition keys. The Secure Agent compares the field value to the range values for each partition and sends rows to the appropriate partitions.
Use key range partitioning for columns that have an even distribution of data values. Otherwise, the partitions might have unequal size. For example, a column might have 10 rows between key values 1 and 1000 and the column might have 999 rows between key values 1001 and 2000. If the mapping includes multiple sources, use the same number of key ranges for each source.
When you define key range partitioning for a column, the Secure Agent reads the rows that are within the specified partition range. For example, if you configure two partitions for a column with the ranges as 10 through 20 and 30 through 40, the Secure Agent does not read the rows 20 through 30 because these rows are not within the specified partition range.
You can configure a partition key for fields of the following data types:
- •Any type of number data type. However, you cannot use decimals in key range values.
You cannot use key range partitions when a mapping includes any of the following transformations:
- •Web Services
- •XML to Relational
Configuring Key Range Partitioning
Perform the following steps to configure key range partitioning for Microsoft Azure SQL Data Warehouse V2 sources:
1. In the Source Properties, click the Partitions tab.
2. Select the required partition key from the list.
3. Click Add New Key Range to define the number of partitions and the key ranges based on which the Secure Agent must partition data.
Use a blank value for the start range to indicate the minimum value. Use a blank value for the end range to indicate the maximum value.
The following image displays the Partitions tab:
