Azure SQL Data Warehouse-Specific Features and Limitations
Skyvia has the following limitations for Azure SQL Data Warehouse:
Azure SQL Data Warehouse Connections
To connect to Azure SQL Data Warehouse, you need to specify the database server host name or IP address, the user name and password to login, and the database name. If you need to import data to Azure SQL Data Warehouse or perform replication to it, you will also need to specify some advanced parameters, described below.
You need to specify the following parameters for Azure SQL Data Warehouse connection:
|•||Name - connection name that will be used to identify the connection on the Connections page and when selecting a connection for a package.|
|•||Server - the name or IP address of the Azure SQL Data Warehouse server to which to connect. Leaving this field empty means using localhost.|
|•||User Id - the user name to login with.|
|•||Password - the password to login with.|
|•||Database - the name of the database you want to connect to.|
If you want to execute Import packages, inserting data to Azure SQL Data Warehouse, or Replication packages via this connection, you need to click the Advanced button and set parameters for connecting to Azure Blob storage service. This is necessary, because for these operations Skyvia uses PolyBase to ensure fastest data loading to Azure SQL Data Warehouse. It loads data to Azure Blob Storage as CSV files and uses PolyBase to import data from these files to Azure SQL Data Warehouse, and then deletes the CSV files after the import. Thus, you need to specify the Storage Account and Storage Account Key to use. You may also optionally change the protocol to use from default https to http, but this is not recommended. Here are the descriptions of these parameters:
|•||Storage Account - the Azure storage account name. |
|•||Storage Account Key - your 512-bit storage access key.|
|•||Storage Endpoints Protocol - determines the protocol to use - HTTPS or HTTP.|
You can also optionally set the following parameters on the Advanced tab:
|•||Command Timeout - specifies the wait time before terminating an attempt to execute a command and generating an error. Note that it is the time to wait for any server reply since the command was sent to a server, and it doesn't include the time necessary to fetch data if the command selects some data. |
|•||Connection Timeout - determines the time (in seconds) to wait while trying to establish a connection before terminating the attempt and reporting an error. Generally you don't need to modify it, but in some specific cases when a connection to the database server is not good you may try increasing its value. |
|•||Use Bulk Import - this setting affects import packages with the INSERT operation and with Azure SQL Data Warehouse as a target. |
By default, such packages import data using PolyBase to quickly load large volumes of data to Azure SQL Data Warehouse. Skyvia writes data into multiple temporary CSV files, upload them to Azure Blob Storage and then tells Redshift to import data from these CSV files. These actions are performed simultaneously, providing the best performance. After the CSV files are imported, they are deleted. However, when data are imported this way, it's not possible to obtain a per-record error log.
If you disable bulk import, Skyvia will use INSERT statements for importing data. This allows you to obtain a per-record error log, but provides far less performance. Thus, disabling bulk import is not recommended, and should only be considered if you need to import not that many records and need to have a per-record error log or don't want to use Azure Blob Storage for temporary files.