Azure Synapse
  • 3 Minutes to read
  • Dark
    Light

Azure Synapse

  • Dark
    Light

Article summary

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

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

  1. On the Destinations page, click on the Create Destination button and select the destination from the list.
  2. Select your authorizer from the drop-down menu.
  3. Name your destination and click on Save.
Need to authorize another connection?

Click on Add new Account in drop-down menu during authorizer selection and follow the on-screen prompts. You can also go to the Authorizers tab and click on Add New Service.

Create a Flow to Azure Synapse

  1. Navigate to Flows and click on Create Flow.
  2. Click on Connect Your Data to add your source(s).
  3. Click on Connect Your Data Destination to add the destination.
  4. Choose the write mode and fill in the other required information.
  5. Check the Data Preview to see if your configuration is correct.
  6. Name your flow and click on Create Flow to finish the setup.

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.


Was this article helpful?

What's Next