Connecting to Azure SQL Data Warehouse
Azure SQL Data Warehouse-Specific Features and Limitations
Skyvia has the following limitations for Azure SQL Data Warehouse:
- Synchronization is not supported 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, user name and password to log in with, and database name. If you need to import data to Azure SQL Data Warehouse or perform replication, 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 in the objects list and when selecting a connection for a package.
- Server — name or IP address of the Azure SQL Data Warehouse server to connect to. Leaving this field empty means using localhost.
- User Id — user name to log in with.
- Password — password to log in with.
- Database — 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 Advanced Settings 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 — Azure storage account name.
- Storage Account Key — your 512-bit storage access key.
- Storage Endpoints Protocol — this parameter determines the protocol to use (HTTPS or HTTP).
You can also optionally set the following parameters in Advanced Settings:
- Command Timeout — this parameter 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 — this parameter 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 SQL Data Warehouse 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 you do not want to use Azure Blob Storage for temporary files.