Transferring data to Azure Synapse

This guide explains how to transfer data to Azure Synapse to store and further process information.

Concept

Azure Synapse is an Active Destination. After you set Azure Synapse as the Destination of a Datastream, data is transferred to Azure Synapse each time data is fetched for the Datastream. For more information, see Destination types.

You can assign multiple Destinations to a Datastream. For more information on possible limitations, see Assigning multiple Destinations to a Datastream.

Prerequisites

Before you complete the procedure in this guide, perform all of the following actions:

  • Create a Datastream whose data you want to transfer to Azure Synapse. For more information on creating a Datastream, see Introduction to collecting data.

  • Create a database user in the Azure Synapse user interface. For more information, see the Microsoft documentation.

  • Create a database master key in Azure Synapse. For more information, see the Microsoft documentation.

  • Set up Azure Blob storage for your Workspace. For more information, see Setting up Storage for Data Extracts.

  • Add Adverity's IP address to the whitelist of Azure Synapse. For more information, see the Microsoft documentation. To determine the IP address of your Adverity stack, contact Adverity Customer Support.

Procedure

To transfer data from a Datastream to Azure Synapse, follow these steps:

  1. Add Azure Synapse as a Destination to the Workspace which contains the Datastream or to one of its parent Workspaces.

  2. Assign the Azure Synapse Destination to the Datastream.

  3. Configure transfer settings.

Adding Azure Synapse as a Destination

To add Azure Synapse as a Destination to a Workspace, follow these steps:

  1. Click the Transfer element and select the Workspace you work with in Connect, Enrich & Transfer.

  1. Click + Add.

  2. Click Azure Synapse.

  1. Click Setup a new Authorization.

  2. Click Next.

  1. In the Authorization page, fill in the following fields:

    Hostname

    The server name of the Destination database. For more information on the Azure Synapse hostname, see the Microsoft documentation.

    Database

    Specify the name of the Azure Synapse database where you want to transfer the data.

    Username

    The username of the Azure Synapse database user.

    Password

    The password of the Azure Synapse database user.

  2. Click Authorize.

  3. In the Configuration page, fill in the following fields:

    Name

    (Optional) Rename the Destination.

    Azure storage

    Select the Azure Blob storage you set up for the Workspace.

    Master key exists

    If Adverity detects a database master key in the Azure Synapse Destination, this field displays .

    For more information on advanced configuration settings, see Azure Synapse Destination reference.

  1. Click Create.

Assigning Azure Synapse as a Destination

To assign the Azure Synapse Destination to a Datastream, follow these steps:

  1. Click the Connect element and select the Workspace you work with in Connect, Enrich & Transfer.

  1. Select the chosen Datastream.

  1. In the Destinations section, click + Add Destination.

  2. Click Assign Existing Destinations.

  1. Select the Azure Synapse checkbox in the list.

  2. Click Save.

Configuring transfer settings

To configure transfer settings, follow these steps:

  1. Click the Connect element and select the Workspace you work with in Connect, Enrich & Transfer.

  1. Select the chosen Datastream.

  1. In the Destinations section, find the Azure Synapse Destination in the list, and click on the right.

  2. Click Destination Settings.

  1. Fill in the following fields:

    Table name

    Specify the target table in the Destination where to transfer data from the Datastream. The name can contain alphanumeric characters and underscores. For example, target_table. To specify a schema, use the syntax schemaName.tableName.

    By default, Adverity saves data from each Datastream in a different table named {datastream_type}_{datastream_id} (for example, mailgun_83).

    You can specify the same target table for several Datastreams. If a column is shared between Datastreams, Adverity performs a full outer join and concatenates values. If a column is not shared between Datastreams, Adverity writes null values in the relevant cells.

    Use placeholders to create unique, dynamic table names in the Destination. Use the following placeholders:

    Placeholder

    Description

    {datastream_id}

    The Datastream ID.

    {datastream_type}

    The Datastream Type.

    {extract_id}

    The Data Extract ID.

    {meta[*]}

    Replace * with a metadata placeholder to use metadata in the table name. For example, {meta[datastream_URI]} uses the Datastream URI as the table name. For more information on metadata and placeholders, see Using placeholders.

    {name}

    The automatically generated filename of the Data Extract.

    {scheduled_day}

    The day from the start date of a date range for a scheduled data fetch.

    {scheduled_month}

    The month from the start date of a date range for a scheduled data fetch.

    {scheduled_year}

    The year from the start date of a date range for a scheduled data fetch.

    Truncate

    Select this checkbox to delete all rows from the relevant table in the Destination before transferring the latest Data Extract.

    Datastream

    Select this checkbox to overwrite data in the target table if both of these conditions are satisfied:

    • The data was previously transferred from this Datastream. Data transferred from other Datastreams is not overwritten.

    • The date ranges of the existing and the new data set overlap. Adverity overwrites existing data in the target table if it refers to the same dates as the new data from the Datastream.

    For example, if the existing data in the target table refers to 10 January 2022 - 14 January 2022, and the data from the Datastream refers to 13 January 2022 - 17 January 2022, then Adverity overwrites data in the target table for 13 January 2022 and 14 January 2022.

    If you select this checkbox, specify the column in your Data Extract that contains the dates in Date Range.

    Date Range

    Select the column in your Data Extract that contains the dates.

    Filename

    Select this checkbox to overwrite the relevant data in the Destination if a Data Extract with the same filename already exists in the Destination.

    Key Columns

    Select this checkbox to overwrite data in the Destination based on the key columns defined in the Schema Mapping of the Datastream. Adverity executes this overwrite option after all the other overwrite options. When you select this checkbox, the configuration to overwrite data based on dates does not have an effect.

  2. Click Save.