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.

• Apply the correct Data Mapping to your 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. Go to the Destinations page.

2. Click + Create destination.

3. Search for and click Firebolt.

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

5. Click Authorize.

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

7. Click Create.

Assigning Firebolt as a destination

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

1. Go to the Datastreams page.

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

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

4. Select the Firebolt 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 Firebolt

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

1. Go to the Datastreams page.

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

3. In the Load section, find the Firebolt 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.

   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.

6. Click Save.