# Oracle Eloqua Import Integration This data connector for Oracle Eloqua enables you to import contact and activity data into Treasure Data. ## Prerequisites Basic knowledge of Treasure Data. ## Requirements and Limitations - Treasure Data account - Oracle Eloqua account ## Static IP Address of Treasure Data Integration If your security policy requires IP whitelisting, you must add Treasure Data's IP addresses to your allowlist to ensure a successful connection. Please find the complete list of static IP addresses, organized by region, at the following link: [https://api-docs.treasuredata.com/en/overview/ip-addresses-integrations-result-workers/](https://api-docs.treasuredata.com/en/overview/ip-addresses-integrations-result-workers/) ## Import from Oracle Eloqua via TD Console ### Create Authentication Your first step is to create a new authentication with a set of credentials. 1. Select **Integrations Hub**. 2. Select **Catalog**. ![](/assets/image2021-9-30_14-38-2.ca92fa4ab9277dca95973c6bd413fc662a3f0d04b57d58f7a8c952a29f28bbec.c68a7c88.png) 1. Search for **Oracle Eloqua** in the Catalog. Hover your mouse over the icon and select **Create Authentication**. ![](/assets/oracleeloqua.c70b013008f28b4d52b898e49719694ae28dbb3b69b3c2f77f1d588a51ab62fd.c68a7c88.png) 1. Ensure that the **Credentials** tab is selected and enter credential information for the integration. **Authentication Fields** | Parameter | Description | | --- | --- | | Authentication Method | Basic (Default) | | Eloqua Site Name | URL for the Oracle Eloqua site | | Username | The username of your Oracle Eloqua account | | Password | The password for your account | 1. Select **Continue**. 2. Enter a name for your authentication, and select **Done**. ### Create a Source 1. Open TD Console. 2. Navigate to **Integrations Hub** > **Authentications**. 3. Locate your new authentication and select **New Source**. ### Create a Connection In the connection tab, enter the following information and then select **Next**. | Parameter | Description | | --- | --- | | Data Transfer Name | You can define the name of your transfer. | | Authentication | The authentication name that is used for a transfer. | ![](/assets/eloqua_createsource.157580f09145e469ddf19828f7b155751aac1c430452ede5c9ba67349722a285.c68a7c88.png) ### Identify a Source Table Enter the following information in the Source Table tab and then select **Next**. | Parameter | Description | | --- | --- | | Data Type | Select either Contact or Activity | | Import Fields | Choose the contact fields that you would like to import. Only support for Data Type Contact. Field names must be separated by comma. | | Start Time | Import data since this time | | End Time | Import data until this time | | Incremental Loading | When the Incremental loading option is checked, it imports new and updated contacts incrementally. | | Update End Time | When the end time is larger than the incremental job's running time and the Update End Time option is checked, the end time will be set to the job's running time. | ![](/assets/screenshot-2024-06-18-at-16.09.31.c5f0e0d578807e1d343de018e629c5376751fea0d31e23c77c0b2e76594bd3dd.c68a7c88.png) ### Define Data Settings Enter the following information in the Data Setting tab and then select **Next**. | Parameter | Description | | --- | --- | | Max retry count per API call | Maximum retry count per API call | | Initial retry interval per API call | Initial retry interval per API call in milliseconds | | Max retry interval per API call | Maximum retry interval per API call in milliseconds | | HTTP Connect Timeout | In milliseconds, default: 3 minutes | | HTTP Read Timeout | In milliseconds, default: 3 minutes | ![](/assets/eloqua_datasettings.638804e3b0446e2f310105dd4b6e0aead2780db40d814c4c9543c854f2ac8760.c68a7c88.png) ### Preview Your Data You can see a [preview](https://docs.treasuredata.com/smart/project-product-documentation/previewing-your-source-data) of your data before running the import by selecting **Generate Preview**. ![](/assets/eloqua_preview.0d058174a44456ec606152d8807dbe774aa6697620a26f01a3db1aa91f2bd7d1.c68a7c88.png) ### Define Your Data Placement For data placement, select the target database and table where you want your data placed and indicate how often the import should run. 1. Select a **Database** > **Select** **an existing** or **Create New Database**. 2. Optionally, type a database name. 3. Select a **Table**> **Select an existing** or **Create New Table**. 4. Optionally, type a table name. 5. Choose the method for importing the data. - **Append** (default)-Data import results are appended to the table. If the table does not exist, it will be created. - **Always Replace**-Replaces the entire content of an existing table with the result output of the query. If the table does not exist, a new table is created. - **Replace on New Data**: When new data is present, only replace the entire content of an existing table with the result output. 6. Select the **Timestamp-based Partition Key** column. If you want to set a partition key seed different from the default key, you can specify the long or timestamp column as the partitioning time. As a default time column, it uses upload_time with the add_time filter. 7. Select the **Data Storage Timezone** for your data storage. 8. Under **Schedule**, you can choose when and how often to run this query. - Run once: 1. Select **Off**. 2. Select **Scheduling Timezone**. 3. Select **Create & Run Now**. - Repeat the query: 1. Select **On**. 2. Select the **Schedule**. The UI provides these four options: *@hourly*, *@daily* and *@monthly* or custom *cron*. 3. You can also select **Delay Transfer** and add a delay of execution time. 4. Select **Scheduling Timezone**. 9. Select **Create & Run Now**. After your transfer has run, you can see the results of your transfer in **Data Workbench** > **Databases**. ![](/assets/eloqua_dataplacement.ae863c8687a19d99313193ab6ad1a74f2bf0faf9f03e8d6b591a3e41f63bd599.c68a7c88.png) ## Import from Oracle Eloqua via CLI (Toolbelt) Before setting up the connector, install the most current [TD Toolbelt](https://toolbelt.treasuredata.com/). You will need version 0.11.9 or later. To determine your version: ``` $ td --version 0.16.10 ``` ### Create Seed Configuration File (seed.yml) ``` 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 ``` | **Option name** | **Description** | **Type** | **Required?** | **Default value** | | --- | --- | --- | --- | --- | | type | | | | | | 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,WebVisit, PageView, FormSubmit, External Activity` | | yes, for activity target | EmailOpen | | import_fields | The field name will be imported for the data type contact. If this value does not set and contact data type have more than 250 fields then first 250 fields will be imported. Other fields will be skipped. | string | no | | | start_time | Specify the date and time from which to fetch the records. 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 | | incremental_field | Incremental base on this field. Support values include: ActivityDate, LinkedToContactDate, CampaignResponseCreatedAt | string | yes, for activity data type WebVisit, PageView | ActivityDate | | update_end_time | Update the query's end time when the end time is larger than the job's running time. Support for incremental job only. | bool | no | false | | mode | Either append or replace records in the target table. Any manual schema changes made to the target table remain intact for replace mode. | string | yes | Append | ### Get a list of contact field names Because we cannot read or get list fields on the Eloqua console, they are only supported via API. So, in the import job, we log out all contact field names to support selection and reuse for the import_fields config. Ex: You will see in the Eloqua import job console ``` 2024-06-14 11:29:21.271 +0000 [INFO] (0001:transaction): All contact fields: C_EmailAddress,C_FirstName,C_LastName,C_Company............ ``` If the contact object has more than 250 fields and the Import Fields are empty, then we will see this log. ``` Import contact fields: C_EmailAddress,C_FirstName,C_LastName,C_Company...........Skipped contact fields: C_Country1,C_Province1,C_City1 ``` If you want to specify import fields, we can copy field names from all contact field logs and input them into Import Fields. ### Preview the Data (Optional) To preview the data, use the *td connector:preview* command. ``` $ 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. Treasure Data recommends that you the -time-columnoption, because Treasure Data’s storage is partitioned by time. (See [data partitioning](https://docs.treasuredata.com/smart/project-product-documentation/data-partitioning-in-treasure-data).) If the option is not given, the data connector selects the first `long` or `timestamp`column as the partitioning time. The type of the column specified by -time-column must be either type `long or` type `timestamp`. If your data doesn’t have a time column, you can add it using `add_time` filter option. For more details, see [add_time filter function](https://docs.treasuredata.com/smart/project-product-documentation/add_time-filter-function). 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. You can create the database and table manually by using the -auto-create-table option with the `td connector:issue` command: ``` $ td connector:issue load.yml --database td_sample_db --table td_sample_table --time-column activity_date --auto-create-table ``` ### Schedule Execution You can schedule periodic data connector execution for the Eloqua import integration. By using this feature, you no longer need to use a `cron` daemon. However, Treasure Data recommends configuring your scheduler carefully to ensure high availability. A new schedule can be created using the `td connector:create` command. You must specify the following: - name of the schedule - a cron-style schedule - the database and table where the data will be stored - 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 the following three options: `@hourly`, `@daily`, and `@monthly`. By default, the schedule is set in the UTC timezone. You can set the schedule in a different timezone using the —t or—-timezone option. The `--timezone` option only supports extended timezone formats like 'Asia/Tokyo', 'America/Los_Angeles', etc. Timezone abbreviations like PST and 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` displays 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`displays the execution history of a schedule entry. To investigate the results of each 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 ```