Loading data into Firebolt

This guide explains how to load data into Firebolt 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 Firebolt. For more information on creating a datastream, see Creating a datastream.

  • Find the address of the Firebolt database into which you want to load data.

  • Set up an Amazon S3 bucket as storage for Firebolt. For more information, see Setting up storage for data extracts.

  • Find the region code of your S3 bucket. The region code determines which database to enter when setting up an authorization to Firebolt. For more information on finding your S3 bucket region code, see the AWS documentation.

Procedure

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

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

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

To add Firebolt 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. Search for and click Firebolt.

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

    Database

    Specify the name of the Firebolt database into which you want to load the data. Make sure the database shares the same region code as the S3 bucket that stores the data.

    Username

    The username of the Firebolt account.

    Password

    The password of the Firebolt account.

  2. Click Authorize.

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

    Name

    (Optional) Rename the destination.

    S3 Storage

    Select the S3 storage that stores the data. Leave this field empty to use the default workspace storage settings.

  1. Click Create.

Assigning Firebolt as a destination

To assign the Firebolt 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 Connector Name datastream by clicking on its name.

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

  2. Click Assign existing destination.

  1. Select the Firebolt checkbox in the list.

  2. Click Save.

Configuring settings for loading data into Firebolt

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

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

  2. Open the Connector Name datastream by clicking on its name.

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

    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 Firebolt spreadsheet containing the data you load into Firebolt, 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

    {app_label}

    The data source's short name.

    {datastream_id}

    The datastream ID.

    {datastream_type}

    The data source.

    {extract_id}

    The data extract ID.

    {id}

    The datastream ID.

    {meta[*]}

    Replace * with a metadata placeholder to use metadata in the name. For example, {meta[datastream_URI]} uses the datastream URI as the 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.

    {upload_day}

    The day when the data extract is loaded into the Firebolt destination.

    {upload_hour}

    The hour when the data extract is loaded into the Firebolt destination.

    {upload_minute}

    The minute when the data extract is loaded into the Firebolt destination.

    {upload_month}

    The month when the data extract is loaded into the Firebolt destination.

    {upload_second}

    The second when the data extract is loaded into the Firebolt destination.

    {upload_year}

    The year when the data extract is loaded into the Firebolt destination.

    Mode

    Select what Adverity does when a table already exists in the Firebolt destination. Select one of the following options:

    • Select Delete to delete all data in the exiting table in the destination before populating the table with the most recent data extract.

    • Select Protected to make sure the loaded data is not deleted when you load data into Firebolt in future.

    Primary Index

    Enter the primary fields that tells Firebolt how to sort and index the data. By default, the primary field of dt_created is entered. Enter multiple primary indexes separated by commas. For more information on primary indexes, see the Firebolt documentation.

  2. Click Save.