This feature is in BETA. Contact your Customer Success Representative for more information.

You can use this data connector to import Contact and Activity data into Treasure Data.


Prerequisites

Basic knowledge and access to:

  • a Treasure Data account

  • an Oracle Eloqua account

Use the TD Console to Create Your Connection

Create a New Connection

In Treasure Data, you configure the authentication and then specify the source information.

Go to Integrations Hub -> Catalog and search and select Oracle Eloqua.

Provide the following information:

Then click Continue and give your connection a name:


After entering your authentication name, click Create Source.



Create a New Source

Complete each tab page to define your incoming source data and schedule for import.


Contact or Activity

On the Source Table tab, choose Contact or Activity for the Data Type, and enter a Start Time and End Time, and then click Next.


When Incremental loading option is checked, it incrementally imports new and updated contacts

In Data Settings, you specify import parameters. You can also select to see a Data Preview of your data or you can choose to Skip This Step.


The next step is to select the database and table to where you want to transfer the data, as shown in the following dialog:


If you are creating a new database, check Create new database and give your database a name. Do the same with Create New Table


Select whether to append records to an existing table or replace your existing table.

If you want to set a different partition key seed than the default key, you can specify one from the drop-down menu.

Finally, specify the schedule of the data transfer using the following dialog and click Save or Save & Run Now:


Specify the frequency of your data import, and the timezone the data is stored in

You can choose from the following options:

Option

Description

Repeat: Off

Run the transfer only once.

Repeat: On

Select an automated recurring transfer

  • Schedule: accepts three options: @hourly, @daily and @monthly and custom cron.

  • Delay Transfer: add a delay of execution time.


After your transfer runs, you can see the results of your transfer in Data Workbench > Databases.

Use Command Line

Install ‘td’ Command v0.11.9 or Later

You can install the latest TD Toolbelt.

$ td --version
0.15.8


Create a Configuration File

Prepare configuration file (for example: load.yml) as shown in the following example, with your Eloqua credential and transfer information.

in:
  type: eloqua
  site_name: "<YOUR_ELOQUA_SITE_NAME>"
  username: "<YOUR_USERNAME>"
  password: "<YOURPASSWORD>"
  target: activity
  data_type: EmailOpen
  start_time: "2018-05-07T00:00:00Z"
  end_time: "2018-05-08T00:00:00Z"
out:
  mode: replace

Optionally Preview Data to Import

You can preview data to be imported using the command td connector:preview.

$ td connector:preview load.yml
+-------------------+00--------------------+--------------------------+----
| activity_id:long  | activity_type:string | activity_date:timestamp  | ...
+---------------------+--------------------+--------------------------+----
| 12345678          | "__"                 | "2018-04-17 13:39:06 UTC"| ...
+-------------------+----------------------+--------------------------+----

Execute Load Job

Submit the load job. It may take a couple of hours depending on the data size. 

$ td connector:issue load.yml --database td_sample_db --table td_sample_table --time-column activity_date

You must specify the database and table where the data is stored.

It is recommended to specify 

  • -time-column

 option, because Treasure Data’s storage is partitioned by time (see also data partitioning). If the option is not given, the data connector selects the first long or timestampcolumn as the partitioning time. The type of the column specified by 

  • -time-column

 must be either of longand timestamp type.

If your data doesn’t have a time column you can add it using add_time filter option. More details at add_time filter function.

You can assign a Time Format column to the "Partitioning Key" by "--time-column" option.

The td connector:issue command assumes that you have already created database(td_sample_db) and table(td_sample_table). If the database or the table do not exist in TD, then this command will not succeed, so create the database and table manually or use 

  • -auto-create-table

 option with td connector:issuecommand to auto create the database and table:

$ td connector:issue load.yml --database td_sample_db --table td_sample_table --time-column activity_date --auto-create-table

Scheduled Execution

You can schedule periodic data connector execution for periodic Eloqua import. We configure our scheduler carefully to ensure high availability. By using this feature, you no longer need a cron daemon on your local data center.

A new schedule can be created using the td connector:create command. You must specify the name of the schedule, a cron-style schedule, the database and table where the data will be stored, and the data connector configuration file.

$ td connector:create \
    daily_eloqua_import \
    "9 0 * * *" \
    td_sample_db \
    td_sample_table \
    load.yml



The `cron` parameter also accepts these three options: `@hourly`, `@daily` and `@monthly`.



By default, schedule is setup in UTC timezone. You can set the schedule in a timezone using -t or --timezone option. The `--timezone` option only supports extended timezone formats like 'Asia/Tokyo', 'America/Los_Angeles' etc. Timezone abbreviations like PST, CST are *not* supported and may lead to unexpected schedules.


List the Schedules

You can see the list of scheduled entries by using the command td connector:list.

$ td connector:list
+-------------------------+-------------+----------+-------+--------------+-----------------+------------------------------+
| Name                    | Cron        | Timezone | Delay | Database     | Table           | Config                       |
+-------------------------+-------------+----------+-------+--------------+-----------------+------------------------------+
| daily_eloqua_import | 9 0 * * *   | UTC      | 0     | td_sample_db | td_sample_table | {"type"=>"eloqua", ... } |
+-------------------------+-------------+----------+-------+--------------+-----------------+------------------------------+


Show the Setting and History of Schedules

td connector:show shows the execution setting of a schedule entry.

% td connector:show daily_eloqua_import
Name     : daily_eloqua_import
Cron     : 9 0 * * *
Timezone : UTC
Delay    : 0
Database : td_sample_db
Table    : td_sample_table

td connector:history shows the execution history of a schedule entry. To investigate the results of each individual execution, use td job <jobid>.

% td connector:history daily_eloqua_import
+--------+---------+---------+--------------+-----------------+----------+---------------------------+----------+
| JobID  | Status  | Records | Database     | Table           | Priority | Started                   | Duration |
+--------+---------+---------+--------------+-----------------+----------+---------------------------+----------+
| 678066 | success | 10000   | td_sample_db | td_sample_table | 0        | 2017-07-28 00:09:05 +0000 | 160      |
| 677968 | success | 10000   | td_sample_db | td_sample_table | 0        | 2017-07-27 00:09:07 +0000 | 161      |
| 677914 | success | 10000   | td_sample_db | td_sample_table | 0        | 2017-07-26 00:09:03 +0000 | 152      |
| 677872 | success | 10000   | td_sample_db | td_sample_table | 0        | 2017-07-25 00:09:04 +0000 | 163      |
| 677810 | success | 10000   | td_sample_db | td_sample_table | 0        | 2017-07-24 00:09:04 +0000 | 164      |
| 677766 | success | 10000   | td_sample_db | td_sample_table | 0        | 2017-07-23 00:09:04 +0000 | 155      |
| 677710 | success | 10000   | td_sample_db | td_sample_table | 0        | 2017-07-22 00:09:05 +0000 | 156      |
| 677610 | success | 10000   | td_sample_db | td_sample_table | 0        | 2017-07-21 00:09:04 +0000 | 157      |
+--------+---------+---------+--------------+-----------------+----------+---------------------------+----------+
8 rows in set


Delete the Schedule

td connector:delete removes the schedule.

$ td connector:delete daily_eloqua_import


Configuration

See the following table for more details on available in modes.

Option name

Description

Type

Required?

Default value

site_name

Eloqua Site name




username

Username

string

yes

N/A

password

Password

string

yes

N/A

target

contact or activity

string

yes

contact

data_type

EmailOpen, Bounceback, Unsubscribe, 
EmailSend, Subscribe, EmailClickthrough


yes for activity target


start_time

Specify the date and time to fetch records from. Formatted yyyy-MM-dd'T'hh:mm:ss.SSS'Z' (e.g. ‘2018-05-07T00:00:00Z’). Exclusive

string

yes

N/A

end_time

Specify the allowable duration to fetch records. formatted yyyy-MM-dd'T'hh:mm:ss.SSS'Z' (e.g. ‘2018-05-08T00:00:00Z’). Inclusive

string

yes

N/A

incremental

true for “mode: append”, false for “mode: replace”.

bool

no

true


Appendix

Modes for Out Plugin

You can specify the file import mode in the out section of seed.yml.

Append (Default)

This is the default mode, which appends records to the target table.

in:
  ...
out:
  mode: append

Replace (In td 0.11.10 and Later)

This mode replaces data in the target table. Any manual schema changes made to the target table remains intact with this mode.

in:
  ...
out:
  mode: replace



  • No labels