* This blog was last tested on Oracle Analytics Cloud Data Sync (2.6.1) + ADW 18c by Jay Pearson (Oracle A-Team) *
This functionality has been available since Version 2.2 of the Data Sync tool, released September 2016, when the ability to connect to a number of different data sources via JDBC was added to the product.
Setting up these Data Sources in Data Sync varies by the source. Rather than create a single article to cover them all, 3 have been written. Select the appropriate article to learn more about the steps to set up that Data Source:
This article will cover a number of the JDBC drivers that come pre-loaded with the Data Sync tool (Greenplum, Hive, Impala, Mongodb. Postgresql. Redshift, Sybase) as well as how to set up connections to different data sources where for which a JDBC driver is available.
|Data Source||Article With Details|
|Greenplum, Hive, Impala, Mongodb. Postgresql. Redshift, Sybase, and directions for other JDBC data sources where the driver is available||this article|
|Service Cloud / RightNow||link to article|
|SalesForce||link to article|
Be sure to download and install the latest version of the Data Sync Tool from Oracle Analytics Downloads.
For instructions on how to configure ADW as the target, see this article.
For general instructions on configuring Data Sync, see this article.
For further information on the Data Sync Tool, upgrade instructions, OAC Data Sync readme (Data Sync release notes and known issues), and OAC Data Sync Documentation (Data Sync install and user guide) can be found here.
The following JDBC drivers are included with version 2.6 of the Data Sync tool. To connect to any of these data sources, no additional JDBC drivers need to be downloaded, and the connection can be set up immediately.
For Databases not in this list - see the section in this document 'Setting up Connection for a Different JDBC Driver'
1. Create a Connection
Within the 'Connections' section, select 'New'
In the detail box
a, Enter a Name for the connection
b. Select 'Generic JDBC' as the Connection Type
c. If the database requires a Username and Password, enter those in the 'User' and 'Password' boxes
d. In the 'URL' box, enter the connection details for the database. Use the format from the list above, replacing the $host, $port, $database values as appropriate.
e, In the 'JDBC Driver' box, cut and paste the text from the relevant 'Driver' column.
2. Test the connection and confirm it works
3. Define the Data Schema
It is good practice to pick the source Database Schema that contains the data to be imported.
In the details of the connection - in this case a Greenplum connection - select the schema box to bring up a list of available schemas and chose the one that contains the data. If multiple schemas are needed, create a new connection for each one and name appropriately.
4. Enter Source Database specific record separators
This only applies in some cases where some of the field names in the source database contain spaces. This can cause a problem when Data Sync generates the select statement. In these cases database specific separators need to be entered. If this applies:
a. Select the Connection, and the 'Advanced Properties' and enter a value, or values into 'Enclose object names'
If, for instance, a double quote character needs to enclose the start and end of the field name (in the case of SalesForce), then just entering a single " in the value box will work. If the character is different to start and end a field, for instance if a field needs to be surrounded by [ ] (in the case of MS Access), then enter the characters separated by a comma, so [,]
Data Sync has the ability to work with many other generic JDBC drivers. This section describes how to set up such a connection. In this case the example of MS Access is used.
1. Close the Data Sync tool, and 'Exit' out completely from the option in the task bar.
2. Locate the JDBC driver for the data source and download to the environment where Data Sync in installed. In this example, the UCanAccess driver, available here, was downloaded.
3. Create a new folder in the /lib directory within the Data Sync folder and save the JDBC driver file(s) in there. Data Sync will read all files within the /lib directory, so adding folders helps organize the new drivers. In this case a new folder called 'Access' was created, and the UCanAccess driver and support files copied there.
4. Open Data Sync and create a new connection
5. Select the 'Connection Type' to be Generic JDBC
6. Use the documentation for the JDBC driver to figure out the JDBC Driver and URL details. In this case the format for the JDBC driver is
And the JDBC URL:
jdbc:ucanaccess://FULL FILE PATH TO ACCESS DATABASE
7. Work through steps 2-4 from the 'Setting up JDBC Connection' section above.
If issues are encountered with the connection, do due diligence testing to try and resolve whether the issue is with the driver or Data Sync. If possible, locate and try a different driver for the same Database Source. While many generic JDBC drivers should work, there are some that may not. At the current time, the only drivers the tool is certified to work with are the ones that are packaged with it. If it's ascertained that the issue is with the Data Sync tool, and you have access to an oracle employee, have them open a bug against product '10432' and the 'DATASYNC' component, otherwise create a low priority SR. In both cases providing details of the driver and error. The DEV team can then look at the issue, and possibly add support in the next release of the Data Sync tool.
This article walked through the steps to configure the Data Sync tool to be able to connect and extract data via generic JDBC connections to data sources.