Azure Synapse Analytics

How to set up the Gravity Azure Synapse Analytics Destination

This Destination is currently in beta

To load data into your Azure Synapse SQL pool in the best (fast, and in the background) way possible, you will need a few things.

What you'll need

• An Azure Storage account and Storage account URL with:

  • (Recommended) a separate container for Gravity to use

  • A shared access signature with List, Read, Write and Create permissions

Gravity does not currently support Azure Data Lake Storage Gen2 due to known limitations

• An Azure Dedicated SQL pool and JDBC URL

Gravity does not currently support Serverless SQL pools because it requires tables which serverless pools don't support

• To add Gravity's IPs to your Azure Dedicated SQL pool IP firewall rules

• A SQL database user and password with:

  • (Recommended) a separate database for Gravity to use

  • Permissions to ADMINISTER DATABASE BULK OPERATIONS and either OWNERSHIP of the database or permissions to CREATE TABLE, CREATE SCHEMA, DROP TABLE, SELECT, INSERT, UPDATE, DELETE on the database

Steps

If you don't have an Azure Storage account, container or shared access signature, please follow our Azure Storage guide

1. Log in to the database with an admin user through SQL Server Management Studio (SSMS) or Azure Data Studio

2. In the following T-SQL, replace any placeholders surrounded by curly braces { ... }

3. Execute the script

The T-SQL script assumes you haven't already created these database objects for Gravity to use. If you have, amend the script as required.

USE DATABASE {database};

-- Create a database user and grant permissions
CREATE LOGIN {login} WITH PASSWORD '{password1}';
CREATE USER {user} FROM LOGIN {login};

ALTER AUTHORIZATION DATABASE::{database} TO {user};
GRANT ADMINISTER DATABASE BULK OPERATIONS TO {user};

Complete Gravity setup

Enter the required settings:

1. The Storage account URL pointing to your Storage account and container. It looks like this: https://{storageaccount}.blob.core.windows.net/{container}

2. A Shared Access Signature key with the necessary permissions. It may start with a question mark ?, if so, don't remove it

3. Under Connection parameters, the JDBC URL of the Azure SQL Database. If you're not sure what it is, see How do I find the JDBC URL?
4. The username and password of the database user Gravity can use. If you create a user and password using the SQL script from earlier, it's the {user} and {password1}

5. Click Save -> Test to validate your setup

   • Test checks connectivity to Azure Synapse and permission to create schemas and tables on your Azure Synapse database.

Frequently asked questions

Why is an Azure Storage account required?

Before data can be loaded into your Azure Synapse SQL pool by a third party - such as Gravity - it has to be stored somewhere that the database can access. Azure Storage is a convenient and secure storage option for that purpose. It also means your data does not reside on our systems after your Jobs have completed.

Why is a separate container recommended?

Because it logically separates the data (blobs) your Jobs in Gravity create from the rest of your data you have stored in the same account. It also reduces the chance of data being overwritten accidentally and makes it easier to monitor Gravity's usage of your storage.

How do I find the JDBC URL?

One of the easiest ways is by logging in to your Synapse workspace https://web.azuresynapse.net, go to Manage from the left menu. Under SQL pools find your Dedicated SQL pool and click on its name. From the right menu, select JDBC (SQL connection) from the Connection strings. The URL should start with jdbc:sqlserver//.

Why do you need bulk operations permissions?

Gravity produces data files which are loaded into your Azure Synapse SQL pool. It does that using the T-SQL command COPY INTO and it's this that requires bulk operations permissions.

This is different to the T-SQL INSERT command you may be more familiar with. That's because the data's in a file. Compared to an INSERT statement, we can load much larger volumes of data faster and more efficiently this way.