Loading data into Azure Synapse

This guide explains how to load data into Azure Synapse for further analysis.

Prerequisites

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

• Create a datastream whose data you want to load into Azure Synapse. For more information on creating a datastream, see Collecting data in Adverity.

• Apply the correct Data Mapping to your datastream.

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

• If this is the first time you are loading data into Azure Synapse, you will need to create a login profile with the username and password that you will use when adding Azure Synapse as a destination. You will also need to grant this login profile the required permissions. To do this, see the Advanced Microsoft SQL Server tips.

• 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 instance, contact Adverity Customer Support.

Procedure

To load data from a datastream into 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.

   You can assign as many destinations to a datastream as you want.

   Some destinations require specific Data Mapping, such as Hubspot and Facebook Offline Conversions. If these Data Mapping requirements conflict, the destinations cannot be assigned to the same datastream.

3. Configure load settings.

Adding Azure Synapse as a destination

To add Azure Synapse as a destination to a workspace, follow these steps:

1. Go to the Destinations page.

2. Click + Create destination.

3. Search for and click Azure Synapse.

4. Choose how to authorize Adverity to access Azure Synapse:

   • To use your details, click Access Azure Synapse using your credentials.

   • To ask someone else to use their details, click Access Azure Synapse using someone else’s credentials.

     If you choose this option, the person you ask to create the authorization will need to go through the following steps.

5. Click Next.

6. 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 into which you want to load the data.

   Username

   The username of the Azure Synapsedatabase user.

   Password

   The password of the Azure Synapsedatabase user.

7. Click Authorize.

8. 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 Advanced Azure Synapse tips.

9. Click Create.

Assigning Azure Synapse as a destination

To assign the Azure Synapse destination to a datastream, follow these steps:

1. Go to the Datastreams page.

2. Open the chosen datastream by clicking on its name.

3. In the Load section, click + Add destination.

4. Select the Azure Synapse checkbox in the list.

5. Click Save.

6. For the automatically enabled destinations, in the pop-up window, click Yes, load data if you want to automatically load your previously collected data into the new destination. The following data extracts will be loaded:

   • All data extracts with the status collected if no other destinations are enabled for the datastream

   • All data extracts with the status loaded if the data extracts have already been sent to Adverity Data Storage or external destinations

   Alternatively, click Skip to continue configuring the destination settings or re-load the data extracts manually. For more information, see Re-loading a data extract.

Configuring settings for loading data into Azure Synapse

To configure the settings for loading data into Azure Synapse, follow these steps:

1. Go to the Datastreams page.

2. Open the chosen datastream by clicking on its name.

3. In the Load section, find the Azure Synapse destination in the list, and click Actions on the right.

4. Click Destination settings.

5. Fill in the following fields:

   Table name

   Specify the target table in the destination into which to load 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.

   • To create a new Azure Synapse spreadsheet containing the data you load into Azure Synapse, enter a name for the new spreadsheet into this field.

   You can use the following placeholders when creating new table names in the destination:

   Placeholder	Description
   {datastream_id}	The datastream ID.
   {datastream_type}	The data source.
   {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 when the data fetch was scheduled to run.
   {scheduled_month}	The month when the data fetch was scheduled to run.
   {scheduled_year}	The year when the data fetch was scheduled to run.

   Truncate

   Select this checkbox to delete all rows from the relevant table in the destination before loading the latest data extract.

   If the Truncate checkbox is selected, the following overwrite conditions are not applied and all data will be deleted.

   If no overwrite options are selected, the loaded data is appended to the destination table.

   Datastream

   Select this checkbox to overwrite all data previously loaded into the destination from this datastream.

   Data loaded from other datastreams is not overwritten.

   Date Range

   Select a date column from your data extract to overwrite all data in the destination with a date that matches a date in this column.

   If the Datastream checkbox is selected, only the data loaded from the current datastream with matching dates will be overwritten.

   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 Data 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.

6. Click Save.

Advanced Azure Synapse tips

Configuration

To configure the Azure Synapse destination, follow these steps:

1. Go to the Destinations page.

2. Click the Azure Synapse destination in the list.

3. In the secondary menu, click Configuration.

4. Fill in the following fields:

   Load state table

   Select the checkbox to create a load state table. The load state table is a table in the destination which contains information about each data load. The load state table contains the following fields:

   Fieldname	Description
   datastream_id	datastream ID.
   datastream_name	datastream name.
   datastream_url	datastream URL.
   extract_filename	Filename of the data extract.
   range_start	Start date and time of the fetch.
   range_end	End date and time of the fetch.
   uuid	Unique ID of the data transfer to the target table.
   load_state	State of the data transfer. The possible states are failed, finished, and running.
   load_start	Date and time when the data transfer started.
   load_end	Date and time when the data transfer finished.
   target_schema	Data schema used in the target table.
   target_table	Name of the target table.
   nrows	Number of rows in the target table.
   ncols	Number of columns in the target table.

   Name of load state table

   Enter a name for the load state table. The default name of a load state table is dt_loadstate.

5. Click Save.

Technical details of loading data into Azure Synapse

When loading a data extract into Azure Synapse, Adverity creates the following additional columns in the target table:

dt_created

The date the data extract was created.

dt_updated

The date the data extract was last updated.

dt_filename

The automatically generated filename of the data extract.

In addition, Adverity creates a temporary table in Azure Synapse with the name _stage. This ensures that no partial updates are made to the database (guarantee of atomicity). Adverity deletes this temporary table after the data load is complete or has been aborted.

Troubleshooting: Loading data into Azure Synapse fails after changing the table structure of the data extract

Data may fail to load into Azure Synapse because of a change in the table structure of the data extract. To ensure the frictionless operation of production systems, Adverity only modifies existing tables in Azure Synapse if the changes are considered neutral, such as adding a new field to the table. Changes to the table structure of the data extract are not considered neutral. As a consequence, Adverity does not update the existing table in Azure Synapse resulting in failure when loading data.

The following actions change the table structure of the data extract:

• You change the key columns in the datastream’s Data Mapping. For more information on key columns, see Setting key columns.

• You change the data type of a column in the datastream’s Data Mapping. For more information on the data types used in Adverity, see Data types used in data harmonization.

• You change the maximum character length of values in a column which contains String values.

To resolve this problem, follow these steps:

1. In Azure Synapse, modify or drop the table that corresponds to the datastream.

2. In Adverity, load data from the datastream into Azure Synapse.

Troubleshooting: Adverity cannot access Azure Synapse destination

If Adverity cannot access the Azure Synapse destination, the destination may restrict access based on IP address.

To resolve this issue, follow these steps:

1. Find the IP address of your Adverity instance. To find the IP address of your Adverity instance, contact Adverity Customer Support.

2. Add Adverity’s IP address to the whitelist of your destination.