Loading data into SQL Database

This guide explains how to load data into SQL Database for further analysis.

Introduction

Use the SQL Database destination to load data into an SQL Database. Adverity supports the following engine types:

  • Amazon Redshift

  • Microsoft SQL Server

  • MySQL

  • Oracle

  • Postgres

  • SAP HANA

  • Snowflake

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 SQL Database. For more information on creating a datastream, see Creating a datastream.

Procedure

To load data from a datastream into SQL Database, follow these steps:

  1. Add SQL Database as a destination to the workspace which contains the datastream or to one of its parent workspaces.

  2. Assign the SQL Database 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 SQL Database as a destination

To add SQL Database as a destination to a workspace, follow these steps:

  1. Select the workspace you work with in Adverity and then, in the platform navigation menu, click Destinations.

  2. Click + Add Destination.

  3. Click SQL Database.

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

    Name

    (Optional) Rename the destination.

    Engine type

    Select the SQL engine type from the drop-down list.

    Hostname

    Specify the URL of the SQL Database. If you use a port other than the default setting for this SQL Database, specify the port in the format hostname:port.

    Database

    Specify the name of the database into which you want to load the data.

    Username

    Enter the username for SQL Database destination.

    Password

    Enter the password for SQL Database destination.

    For more information on advanced configuration settings, see Advanced SQL Database tips.

  1. Click Create.

Assigning SQL Database as a destination

To assign the SQL Database destination to a datastream, follow these steps:

  1. Select the workspace you work with in Adverity and then, in the platform navigation menu, click Datastreams.

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

  1. In the External destinations section, click + Assign destination.

  2. Click Assign existing destination.

  1. Select the SQL Database checkbox in the list.

  2. Click Save.

Configuring settings for loading data into SQL Database

To configure the settings for loading data into SQL Database, follow these steps:

  1. Select the workspace you work with in Adverity and then, in the platform navigation menu, click Datastreams.

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

  1. In the Destinations section, find the SQL Database 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 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.

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

  2. Click Save.

Advanced SQL Database tips

Configuration

To configure the SQL Database destination, follow these steps:

  1. Select the workspace you work with in Adverity and then, in the platform navigation menu, click Destinations.

  2. Click the SQL Database destination in the list.

  1. In the secondary menu, click Configuration.

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

    Options

    Specify the options for the database in JSON format.

    Default schema

    Specify the default schema used in the database.

  3. Click Save.

Technical details of loading data into SQL Database

When loading a data extract into SQL Database, 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 SQL Database 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 SQL Database fails after changing the table structure of the data extract

Data may fail to load into SQL Database 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 SQL Database 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 SQL Database 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 SQL Database, modify or drop the table that corresponds to the datastream.

  2. In Adverity, load data from the datastream into SQL Database.

Troubleshooting: Adverity cannot access SQL Database destination

If Adverity cannot access the SQL Database 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.