summary
Help Page for ETL Configuration of Data Setting to Azure Synapse Analytics.
constraints
- none in particular
Setting items
STEP1 Basic settings
| item name | indispensable | default value | Contents |
|---|---|---|---|
| Azure Synapse Analytics Connection Configuration | Yes | - | Select the preregistered Azure Synapse Analytics Connection Configuration that has the necessary permissions for this ETL Configuration. |
| database | Yes | - | Select the Database Name for Data Destination. |
| schema | Yes | - | Select the name of the Data Destination schema. |
| table | Yes | - | Select the name of the Data Destination table. If the target table does not exist in the Data Destination database schema, it will be created automatically. |
| transfer mode | Yes | Append (INSERT) |
Select the transfer mode. For more information, see About Transfer Mode below. |
| merge key | No | - | Can be entered when UPSERT (MERGE) is selected in transfer mode.Enter the name of the column that will be treated as a merge key (primary key) if the primary key does not exist in the Data Destination table. The merge key should be populated with columns that have no duplicate values and no null values. |
| Table Structure | Yes | Heap (HEAP ) |
Select the table structure. For more information, see Table Structure below. |
| Index Key Row | Yes | - | Enter when CLUSTERED COLUMNSTORE INDEX is selected for the table structure.Specify the input and order of key column names for the index. |
| Table Distribution | Yes | Round Robin Variance (ROUND_ROBIN ) |
Select table variance. For more information, see Table Distribution below. |
| distribution column | Yes | - | Enter when hashdistribution(HASH ) is selected for table distribution.Specify the name of the distribution column that will be used as the hash key. |
About transfer mode
| transfer mode | Contents |
|---|---|
Append (INSERT) |
First, a temporary table is created and data is transferred. After all temporary tables have been created, insert data into the target tables. |
Postscript (INSERT DIRECT) |
Inserts rows directly into the target table. If the transfer fails midway, some data may have been inserted into the target table. |
TRUNCATE INSERT |
First, a temporary table is created and data is transferred to the temporary table. After all temporary tables have been created, delete the contents of the target table and then insert data into the target table. |
All cases washed (REPLACE) |
First, a temporary table is created and data is transferred. Once the temporary table has been created, delete the target table and rename the temporary table to the target name. If the transfer fails during the process, the target table may be deleted. |
UPSERT (MERGE) |
First, a temporary table is created and data is transferred. Once all temporary tables have been created For the target table, rows that match the merge key and value in the temporary table are updated, and rows that do not match are inserted. |
About Table Structure
For more information on table structures, please refer to the official Azure Synapse Analytics documentation - Table Structure Options.
| Table Structure | Contents |
|---|---|
Heap (HEAP ) |
Stores tables as a heap. |
CLUSTERED COLUMNSTORE INDEX |
Store the table as a clustered column store index. |
CLUSTERED INDEX |
Store tables as clustered indexes containing one or more key columns. |
About Table Distributions
For more information on table distribution, see Azure Synapse Analytics Official Documentation - Table Distribution Options.
| Table Distribution | Contents |
|---|---|
Hash Dispersion (HASH ) |
Distribute rows based on hash values of up to 8 columns. |
Round Robin Variance (ROUND_ROBIN ) |
Distribute rows evenly across all distributions in a round-robin fashion. |
REPLICATE |
One copy of the table is stored on each computing node. |
STEP1 Detailed settings
| item name | default value | Contents |
|---|---|---|
| Maximum number of retries | 12 | Specifies the maximum number of retries. |
| Retry wait time (ms) | 1000 | Specifies the waiting time for retries. |
| Maximum retry wait time (ms) | 1800000 | Specifies the maximum time to wait for retries. |
| Batch size (MB) | 16 | Specify batch size. |
| Default time zone | - | Specify the default time zone. |
| Connection timeout (sec) | 300 | Specify the timeout in seconds before the driver connects. If 0 is specified, the driver's default value of 30 seconds is adopted. |
| Socket timeout (seconds) | 1800 | Specify the timeout in seconds before executing the query. If 0 is specified, there is no timeout. |