Microsoft's Azure Synapse Analytics product is an enterprise analytics service that brings together enterprise data warehousing and Big Data analytics. QuerySurge customers who use Azure Synapse SQL in their data and analytics architectures can connect QuerySurge to SQL pools within Azure Synapse to test their data. This article explains how to connect QuerySurge with Azure Synapse SQL.
Setting up a Connection to Azure Synapse SQL
Connecting to Azure Synapse SQL is done using the Connection Extensibility feature of the QuerySurge Connection Wizard. The following steps describe what you'll need to set up your QuerySurge Connection to Azure Synapse.
- QuerySurge connects to Azure Synapse using the "standard" SQL Server JDBC driver.
- Download and deploy the SQL Server JDBC driver to your QuerySurge Agent(s). The procedure for deploying a new driver to a QuerySurge Agent is here (for Agents on Windows) and here (for Agents on Linux).
Note: You'll need to deploy a copy of the driver to each of your Agents.
- Log into QuerySurge as a QuerySurge Admin user, and navigate to the Admin view. Launch the Connection Wizard, give your Connection a name, and select the Connection Extensibility option. Click Next.
- Steps for using the Connection Extensibility feature can be found here. To use the Connection Extensibility option in the Connection Wizard with the SQL Server driver, you'll need the following information:
Driver Class: com.microsoft.sqlserver.jdbc.SQLServerDriver
Connection URL: jdbc:sqlserver://<your-SQL-endpoint>:<synapse-server-port>;database=<db-name>;pdw=true
Note that you'll need to provide a URL with the actual values for the endpoint, the port and the database name. (The default Synapse port is 1433.) The endpoint information (dedicated or serverless) can be found under the overview section of you Azure Synapse workspace. When you've entered your information, the Connection Wizard will look similar to this:
Note: You'll need to append pdw=true to the URL so that QuerySurge handles your Azure Synapse Connection properly. (This is a QuerySurge-specific notation.)
If you have a Test Query, you can enter it to help verify that your Connection parameters are correct (recommended). It should be a standard query that returns a small amount of information - one row/column is enough. Click Next.
- If you entered a Test Query, you can use the Test Connection button to test whether your Connection is set up properly. After you have verified your Connection information, click the Save button.
- Once your Connection is set up, you should be able to write SQL queries against Azure Synapse in QuerySurge. Queries use the standard SQL Server syntax. Note that not never construct can be supported in QuerySurge.