Azure Synapse

Azure Synapse, formerly known as Azure SQL Data Warehouse, is an integrated analytics service provided by Microsoft Azure. It combines big data and data warehousing capabilities, allowing users to analyze and visualize large volumes of data from various sources, perform complex data transformations, and gain insights through business intelligence tools, all in a unified and scalable platform.

Prerequisites

• You have a running instance of Azure Synapse.
• You have configured firewall settings to accept incoming connection from Dataddo IPs.
• You have created a database user with CREATE, SELECT, UPDATE, INSERT, DELETE permissions.

Authorize Connection to Azure Synapse

In Azure Portal

Create an Instance of Synapse

1. In the Azure portal, select Create a resource from the menu on the left.
2. Search for Azure Synapse Analytics and click on Create.
3. Select Subscription, Resource Group and enter your Database name.
4. Select your Server (if you already have another SQL Database running) or create a new one.
5. In the Networking section, make sure that Allow connections from all IP addresses is checked.
6. Review the remaining settings, adjust as necessary, and click on Review + create.

Configure Firewall

1. In the Azure portal, click on Azure Synapse Analytics and select the workspace you created in the previous step.
2. In the workspace menu, select the Firewall tab and add each Dataddo IP.

In Azure Synapse SQL Pool

Create an SQL Pool

Azure Synapse uses SQL pools (previously SQL Data Warehouses) to enable querying over large amounts of data. Please create a new SQL pool under your Synapse workspace.

1. In the Azure portal select Azure Synapse Analytics and select your workspace.
2. In the workspace menu navigate to the New dedicated SQL pool section.
3. Enter a Pool name and click on Review + create.

Create a Database User

After setting up your Azure Synapse SQL pool, you need to create a user who can interact with the database on behalf of Dataddo. This can be done using the SQL Server Management Studio or Azure Data Studio.

Remember, it is important to regularly review security settings and follow best practices to ensure your data is protected. Always take your organization's security guidelines into account when when setting up users and permissions.

1. Connect to your database with the admin account.
2. Run the command below to create a new user:

CREATE USER dataddo WITH PASSWORD = '***';

Set Permissions

1. Connect to your database with the admin account.
2. Run the commands below to grant CREATE, SELECT, INSERT, UPDATE, DELETE permissions.

GRANT CONNECT SQL TO dataddo;
GRANT SELECT, INSERT, UPDATE, DELETE TO dataddo;
GRANT CREATE TABLE TO dataddo;

In Dataddo

1. In the Authorizer tab, click on Authorize New Service and select Azure Synapse.
2. You will be asked to fill the following fields:
   1. Server IP or Hostname: Public hostname of the SQL Pool of the Azure Synapse. You can find this by navigating to SQL Pool details and looking for Connection String.
   2. Database (Catalog): The database you want to connect to. Sometimes this might be referred to as Catalog.
   3. Username: Username to your database.
   4. Password: Password to your database.
   5. Port: Port to connect to database. The default is 1433.
3. Save the authorization details.

Create a New Azure Synapse Database Destination

Create a Flow to Azure Synapse

Table Naming Convention

When naming your table, please make sure the table name:

• Starts and ends with a letter or a number
• Contains only
  • Letters
  • Numbers
  • Underscores
• Does NOT contain whitespaces.

---