Discover how a bimodal integration strategy can address the major data management challenges facing your organization today.
Get the Report →Load JDBC-ODBC Bridge Data to a Database Using Embulk
Use CData JDBC drivers with the open source ETL/ELT tool Embulk to load JDBC-ODBC Bridge data to a database.
Embulk is an open source bulk data loader. When paired with the CData JDBC-ODBC Bridge Driver, Embulk easily loads data from JDBC-ODBC Bridge to any supported destination. In this article, we explain how to use the CData JDBC-ODBC Bridge Driver in Embulk to load JDBC-ODBC Bridge data to a MySQL dtabase.
With built-in optimized data processing, the CData JDBC Driver offers unmatched performance for interacting with live JDBC-ODBC Bridge data. When you issue complex SQL queries to JDBC-ODBC Bridge, the driver pushes supported SQL operations, like filters and aggregations, directly to JDBC-ODBC Bridge and utilizes the embedded SQL engine to process unsupported operations client-side (often SQL functions and JOIN operations).
Configure a JDBC Connection to JDBC-ODBC Bridge Data
Before creating a bulk load job in Embulk, note the installation location for the JAR file for the JDBC Driver (typically C:\Program Files\CData\CData JDBC-ODBC Bridge Driver\lib).
Embulk supports JDBC connectivity, so you can easily connect to JDBC-ODBC Bridge and execute SQL queries. Before creating a bulk load job, create a JDBC URL for authenticating with JDBC-ODBC Bridge.
To connect to an ODBC data source, specify either the DSN (data source name) or specify an ODBC connection string: Set Driver and the connection properties for your ODBC driver.Built-in Connection String Designer
For assistance in constructing the JDBC URL, use the connection string designer built into the JDBC-ODBC Bridge JDBC Driver. Either double-click the JAR file or execute the jar file from the command-line.
java -jar cdata.jdbc.jdbcodbc.jar
Fill in the connection properties and copy the connection string to the clipboard.
Below is a typical JDBC connection string for JDBC-ODBC Bridge:
jdbc:jdbcodbc:Driver={ODBC_Driver_Name};Driver_Property1=Driver_Value1;Driver_Property2=Driver_Value2;...
Load JDBC-ODBC Bridge Data in Embulk
After installing the CData JDBC Driver and creating a JDBC connection string, install the required Embulk plugins.
Install Embulk Input & Output Plugins
- Install the JDBC Input Plugin in Embulk.
https://github.com/embulk/embulk-input-jdbc/tree/master/embulk-input-jdbc - In this article, we use MySQL as the destination database. You can also choose SQL Server, PostgreSQL, or Google BigQuery as the destination using the output Plugins.
https://github.com/embulk/embulk-output-jdbc/tree/master/embulk-output-mysqlembulk gem install embulk-output-mysql
embulk gem install embulk-input-jdbc
With the input and output plugins installed, we are ready to load JDBC-ODBC Bridge data into MySQL using Embulk.
Create a Job to Load JDBC-ODBC Bridge Data
Start by creating a config file in Embulk, using a name like jdbcodbc-mysql.yml.
- For the input plugin options, use the CData JDBC-ODBC Bridge Driver, including the path to the driver JAR file, the driver class (e.g. cdata.jdbc.jdbcodbc.JDBCODBCDriver), and the JDBC URL from above
- For the output plugin options, use the values and credentials for the MySQL database
Sample Config File (jdbcodbc-mysql.yml)
in:
type: jdbc
driver_path: C:\Program Files\CData[product_name] 20xx\lib\cdata.jdbc.jdbcodbc.jar
driver_class: cdata.jdbc.jdbcodbc.JDBCODBCDriver
url: jdbc:jdbcodbc:Driver={ODBC_Driver_Name};Driver_Property1=Driver_Value1;Driver_Property2=Driver_Value2;...
table: "Account"
out:
type: mysql
host: localhost
database: DatabaseName
user: UserId
password: UserPassword
table: "Account"
mode: insert
After creating the file, run the Embulk job.
embulk run jdbcodbc-mysql.yml
After running the the Embulk job, find the Salesforce data in the MySQL table.
Load Filtered JDBC-ODBC Bridge Data
In addition to loading data directly from a table, you can use a custom SQL query to have more granular control of the data loaded. You can also perform increment loads by setting a last updated column in a SQL WHERE clause in the query field.
in:
type: jdbc
driver_path: C:\Program Files\CData[product_name] 20xx\lib\cdata.jdbc.jdbcodbc.jar
driver_class: cdata.jdbc.jdbcodbc.JDBCODBCDriver
url: jdbc:jdbcodbc:Driver={ODBC_Driver_Name};Driver_Property1=Driver_Value1;Driver_Property2=Driver_Value2;...
query: "SELECT Id, Name FROM Account WHERE [RecordId] = 1"
out:
type: mysql
host: localhost
database: DatabaseName
user: UserId
password: UserPassword
table: "Account"
mode: insert
More Information & Free Trial
By using CData JDBC-ODBC Bridge Driver as a connector, Embulk can integrate JDBC-ODBC Bridge data into your data load jobs. And with drivers for more than 200+ other enterprise sources, you can integrate any enterprise SaaS, big data, or NoSQL source as well. Download a 30-day free trial and get started today.