The ODBC Data Export

The ODBC Data Export feature allows users to export data directly to an ODBC database from the Automated Flow module. Depending on the SELECT permissions on your ODBC server, you either select the schema and table from a dropdown or enter them manually. Afterward, you map your Dengage columns to the columns of the ODBC table.

Before you start, define ODBC as a remote target. See Prerequisite: Defining ODBC as a Remote Target on this page. To export to MySQL, see Exporting Data to MySQL via ODBC in Automated Flows on this page.

Key Terms

• ODBC (Open Database Connectivity): a standard way to connect to a database through a driver. In Dengage, the remote target type is named ODBC (Generic).
• Driver: the connector for one kind of database, for example Teradata or MySQL. You choose it when you define the remote target.
• SELECT permission: the database permission to read tables. Dengage uses it to list the schemas and tables of your server.
• Schema: a named group of tables in your database.
• Automated Flow: a flow in Data Space that combines triggers and actions to run data operations automatically. See Automated Flows.
• Data Export node: the action node of an Automated Flow that sends data to a remote target.
• Data Space Update node: the action node of an Automated Flow that writes data into tables in Data Space. It uses the same kind of column mapping.
• Remote Target: an external destination, defined in Settings, that Dengage exports data to. See Remote Targets.

What You Can Export

The Data Export node offers ODBC (Generic) as a remote target for these data sources:

• Table: an existing table, for example master_contact. After you select the table, Manage Filters lets you add filters and filter groups.
• Segment: an existing segment, from the Segment or Predefined Segments tab.
• SQL Editor: the result of your own SQL query. Write the query on the Source SQL Query screen and click Validate and Preview. You can continue only after the query is validated.
• Incremental Logs: transactional and marketing logs. This source works only with a Recurring trigger and the Daily period. With "Occurs every", the minimum interval is 15 minutes. See Exporting Incremental Logs to Databases via Remote Target.

The Permission Change Logs source does not offer ODBC (Generic) as a remote target.

Note: The maximum export size is 7168 MB.

Accessing the Feature:

1. Navigate to Data Space > Data Flow > Automated Flows > Data Export Node:
   • From the main menu, go to Data Space > Data Flow > Automated Flows. Click New, select Automated Flow, name the flow and click Next.
   • In the flow editor, first add and configure a Recurring Node, then add the Data Export Node and click Configure on it.
   • On the Select data source screen, choose the data source and click Next. Select the table or segment, or enter the query, and click Next.
   • On the Select Remote Target screen, choose ODBC (Generic) and one of your ODBC remote targets listed under it.
2. ODBC Option in Data Export Node:
   • After selecting ODBC (Generic), click Next to proceed to the schema/table selection screen.

Step 1: Select the Schema and Table

The next screen, Export Wizard, displays different options depending on whether Dengage has SELECT permissions on your ODBC server:

Scenario 1: SELECT Permissions Exist

• Select Schema and Table:
  • Choose the schema from the Select Schema dropdown. Then choose the table you want to export data to from the Table Name dropdown.
  • Click Next to proceed to the mapping interface.

Scenario 2: No SELECT Permissions

• Manual Table Entry:
  • If Dengage does not have SELECT permissions, Select Schema and Table Name are text fields. Enter the schema name and the table name manually.
  • Click Next to continue.

Both fields are required. If you leave them empty, each field shows "The field is required" and a "Please select schema" message appears. The table must already exist in your database. If Dengage cannot read the table, a "Something went wrong" message appears and the mapping screen shows no columns.

Step 2: Data Mapping Interface

• Once you select or enter the schema and table, the Column Mapping screen opens.
• This interface mirrors the functionality found in the Data Space Update node, allowing you to map fields between the Dengage platform and the ODBC database.
• Complete the mapping by connecting the relevant Dengage platform fields to the corresponding ODBC table columns.

First, choose the Data Table Action:

• Append: appends all records from the source. This is the default.
• Truncate and Insert: truncates the table and inserts all the records from the source.

Then map the columns. Each row of the Columns table is a column of the ODBC table. In Target Column, choose what Dengage writes into it:

• --Ignore--: the default. The column is not mapped.
• -- Constant Value --: opens an extra field under the dropdown for a fixed value.
• A source column: a column of the data source you selected, for example contact_key.

If the columns of the table do not load, the Columns table shows "At least 1 column must be added". Click Next when the mapping is complete.

Step 3: Review the Summary and Export

The Summary step shows the Source, the Target (Target type and Target Name) and the Columns to be exported. Check the details, and use Back to change a setting. Click Export to confirm your setup, and then save the flow.

Check the Export Runs

Go back to Data Space > Data Flow > Automated Flows to follow your flow:

• The Automated Flows tab lists each flow with its Last Run Time, Next Run Time and Update Date.
• The Running Flows tab lists the runs with Started On, Completed On and a status, for example Completed.
• The menu at the end of a row offers View Details, Move, Copy Public ID and Duplicate. Use Duplicate to create a similar export without starting from scratch.

For older runs, see Automated Flows History.

Prerequisite: Defining ODBC as a Remote Target

Before using features requiring ODBC as a remote target, follow these steps to configure it:

1. Navigate to Remote Targets:
   • Go to Settings > Integrations > Remote Targets.
2. Add a New Remote Target:
   • Click the Add button.
3. Enter Remote Target Details:
   • In the pop-up window:
     • Fill in the Name of your remote target.
     • From the Type dropdown menu, select ODBC (Generic).
     • Driver: Choose the relevant driver for your database. The list contains Amazon Redshift, Dremio, Sap Hana, Vertica, Teradata and MySQL.
     • Provide the necessary server information and user information:
       • Server Information: Server, Port and Db Name.
       • User Information: User Name and Password.
     • Advanced Options: this toggle is off by default. Turn it on to set Command Timeout Sec and to add Additional connection string options. Click Add Parameters to add a Key and Value pair.
4. Save the Remote Target:
   • After entering all the required details, click Save.

Your ODBC remote target is now set up and ready for use in relevant data export features.

Exporting Data to MySQL via ODBC in Automated Flows

The ODBC integration in Dengage allows users to export data from the Automated Flow module to various database systems, including MySQL. You export data directly to your MySQL database by defining an ODBC remote target with the MySQL driver.

Prerequisite: Defining ODBC for MySQL

To export data to MySQL, you must first define it as an ODBC remote target.

1. Navigate to Remote Targets: Settings > Integrations > Remote Targets
2. Add a New Remote Target: Click Add to open the configuration window.
3. Enter Remote Target Details:
   • Name: Enter a recognizable name (e.g., MySQL_Export).
   • Type: Select ODBC (Generic).
   • Driver: Select MySQL.
   • Provide the necessary server information and user information: Server, Port, Db Name, User Name and Password.
4. Save: Click Save to register the connection.

How to Access

Navigation Path:
Data Space > Data Flow > Automated Flows

1. Go to Data Space.
2. Click Data Flow.
3. Open the Automated Flows section.

This is where you create and configure flows that export data to remote systems.

How to Use

1. Create a New Automated Flow

• Click the New button and choose Automated Flow.
• Enter a name, Start Date, and End Date. Start Date can be Now or Scheduled, and End Date can be Never or Scheduled. You can also add a Description and change the Folder.
• Click Next.

2. Add a Trigger

• Drag the trigger node to the canvas.
• The available triggers are Recurring, File Drop and API Trigger. For a scheduled export, use Recurring. Click Configure on the node, set Occurs and the time in Recurring Settings, and click OK.

3. Add a Data Export Node

• Drag the Data Export node under the Actions section.
• Click Configure on the node to begin configuration.

4. Select Data Type

• Choose the desired data type on the Select data source screen: Table, Segment, SQL Editor or Incremental Logs. See What You Can Export on this page.
• Click Next. Then select the table or segment, or enter the query, and click Next.

5. Choose Remote Target: MySQL via ODBC

"MySQL" is not a standalone option on the Select Remote Target screen.
Instead:

• Select ODBC (Generic), then the ODBC remote target previously configured with MySQL credentials.
• Click Next.

6. Enter the Schema and Table

• On the Export Wizard screen, fill in Select Schema and Table Name. Both fields are required.
• Click Next. For the two possible views of this screen, see Step 1: Select the Schema and Table on this page.

7. Map the Columns

• On the Column Mapping screen, choose the Data Table Action and map the columns of the MySQL table. See Step 2: Data Mapping Interface on this page.
• Click Next.

8. Review the Summary and Export

• Check the Summary step, click Export, and then save the flow. See Step 3: Review the Summary and Export on this page.

Use Cases

• Send data to a database in the Driver list: export to Amazon Redshift, Dremio, SAP HANA, Vertica, Teradata or MySQL by choosing the matching driver in an ODBC (Generic) remote target.
• Keep a reporting table up to date: export a Dengage table every night with a Recurring trigger and the Truncate and Insert action, so that reports always read the latest data.
• Share a segment with other systems: export the contacts of a segment to a MySQL table that your own applications read.
• Export a custom dataset: use SQL Editor to select only the columns and rows you need before sending them to your database.
• Collect message logs: export Incremental Logs, such as email sends, to your database for your own analysis.

FAQ

Which databases can I export to with ODBC?

The databases in the Driver list of the ODBC (Generic) type: Amazon Redshift, Dremio, SAP HANA, Vertica, Teradata and MySQL.

Where is MySQL in the remote target list?

MySQL is not a standalone type. Define a remote target with the ODBC (Generic) type and the MySQL driver, then select it under ODBC (Generic) in the Data Export node.

Which data can I export to an ODBC database?

Tables, segments, the result of an SQL Editor query and Incremental Logs. Permission Change Logs do not offer ODBC (Generic) as a target.

Why do I see text fields and not dropdowns for the schema and table?

Dengage could not list the schemas and tables of your server, which happens when it has no SELECT permissions. Type the schema name and the table name.

Does Dengage create the table for me?

No. Enter or select a table that already exists. With a table that Dengage cannot read, a "Something went wrong" message appears and the mapping screen shows no columns.

How often can the export run?

As often as the Recurring trigger allows: Daily, Weekly, Monthly or Yearly, and within a day either once at a set time or repeatedly at an interval. For Incremental Logs the minimum interval is 15 minutes.

Is there a size limit?

Yes. The maximum export size is 7168 MB.

Where do I see whether the export ran?

In Data Space > Data Flow > Automated Flows. The Automated Flows tab shows the last and next run time of each flow, and the Running Flows tab shows the runs with their status.