Azure Synapse Analytics is a limitless analytics service enabling insights across relational and non-relational big-data. At it's core is the SQL Pool (Previously Azure SQL Data Warehouse), a massively parallel processing database. You can learn more about this service here.

TimeXtender began supporting Azure Synapse as a target database in version 19.11.2. This functionality enables the use of Azure Synapse Analytics as a target Data Warehouse or Staging Database. When also connected to Azure Data Lake via the ODX Server, users can simply drag and drop data from Data Lake to a Synapse SQL Pool.

In this article you will learn how to:

 

Create and Access an Azure Synapse SQL Pool in the Azure Portal

  1. You can view this Quickstart Guide to create the Synapse SQL Pool from the Azure Portal
    1. The resource can be created in an existing Azure SQL Server or you can create a new one. 
    2. Create a Server Level Firewall Rule
      1. In the Azure SQL Server, click Firewalls and virtual networks, click Add client IP, click save. 

Using the Server Admin, login to the server using SQL Server Management Studio to run the following queries. 

Create your own SQL Server Login (More Info here). Sample statement:

--Create SQL Server Login in the Master Database
USE MASTER
CREATE LOGIN USERNAME WITH PASSWORD = 'STRONGPASSWORD';

--Create User in the desired Data Warehouse
USE --***Insert Database Name Here***
CREATE USER USERNAME FROM LOGIN USERNAME;

--Assign User to the desired role in the Data Warehouse
EXEC sp_addrolemember 'db_owner', 'USERNAME'

Assign your user to the desire resource class (more about Resource Classes here). Sample Statement:

EXEC sp_addrolemember 'xlargerc', 'USERNAME'

Create a Master Key (more about Master Keys here). Sample Statement:

CREATE MASTER KEY ENCRYPTION BY PASSWORD ='STRONGPASSWORD';

Connect to Azure Synapse SQL Pool from TimeXtender

  1. Add a Data Warehouse in TimeXtender
    1. Right Click on Data Warehouses in TimeXtender Solution Explorer > Add Data Warehouse. 
    2. Type the Azure SQL Server name from the Azure portal
    3. Type the User Name and Password created earlier.
    4. Type the Data Warehouse name from the Azure portal.
    5. Go to Advanced Settings > Deployment Target > SQL Data Warehouse
  2. Right Click Data Warehouse > Create required objects for SQL Data Warehouse
  3. If you have not already, run a query to create a Master key in the SQL Data Warehouse. This can be done using the TimeXtender Query Tool or Management Studio. 
  4. Add Tables, ideally from ODX Server with Azure Data Lake storage. 
  5. Right click a Table > Table Settings > SQL Data Warehouse Tab > Select desired Distribution method.
  6. Deploy & Execute! 

Troubleshooting

Error: "An error occurred during executing this statement \r\n DELETE FROM [dbo].[TimeXtenderExtendedProperties]…"

Error: "Please create a master key in the database or open the master key in the session before performing this operation."

Error: "Unexpected error encountered filling record reader buffer: HadoopExecutionException: Could not find a delimiter after string delimiter."

  • This is a known issue with Polybase when copy data from Data Lake and there are values in the source file that contain double quotes ["].  To circumvent this issue you can instruct TimeXtender to switch from using Polybase to use the new syntax for the COPY command.
  • To Enable the COPY command, Right-click on the Data Warehouse > Enable SQL Data Warehouse Preview features.