Build JDBC-ODBC Bridge-Connected ETL Processes in Google Data Fusion

Google Data Fusion allows users to perform self-service data integration to consolidate disparate data. Uploading the CData JDBC-ODBC Bridge Driver enables users to access live JDBC-ODBC Bridge data from within their Google Data Fusion pipelines. While the CData JDBC Driver enables piping JDBC-ODBC Bridge data to any data source natively supported in Google Data Fusion, this article explains how to pipe data from JDBC-ODBC Bridge to Google BigQuery,

Upload the CData JDBC-ODBC Bridge Driver to Google Data Fusion

Upload the CData JDBC-ODBC Bridge Driver to your Google Data Fusion instance to work with live JDBC-ODBC Bridge data. Due to the naming restrictions for JDBC drivers in Google Data Fusion, create a copy or rename the JAR file to match the following format driver-version.jar. For example: cdatajdbcodbc-2020.jar

1. Open your Google Data Fusion instance
2. Click the to add an entity and upload a driver 👁 Image
   
3. On the "Upload driver" tab, drag or browse to the renamed JAR file.
4. On the "Driver configuration" tab:
   • Name: Create a name for the driver (cdata.jdbc.jdbcodbc) and make note of the name
   • Class name: Set the JDBC class name: (cdata.jdbc.jdbcodbc.JDBCODBCDriver)
   👁 Configuring the driver (Salesforce is shown.)
   
5. Click "Finish"

Connect to JDBC-ODBC Bridge Data in Google Data Fusion

With the JDBC Driver uploaded, you are ready to work with live JDBC-ODBC Bridge data in Google Data Fusion Pipelines.

1. Navigate to the Pipeline Studio to create a new Pipeline
2. From the "Source" options, click "Database" to add a source for the JDBC Driver 👁 Adding a database source
   
3. Click "Properties" on the Database source to edit the properties

   NOTE: To use the JDBC Driver in Google Data Fusion, you will need a license (full or trial) and a Runtime Key (RTK). For more information on obtaining this license (or a trial), contact our sales team.

   • Set the Label
   • Set Reference Name to a value for any future references (i.e.: cdata-jdbcodbc)
   • Set Plugin Type to "jdbc"
   • Set Connection String to the JDBC URL for JDBC-ODBC Bridge. For example:
     
     jdbc:jdbcodbc:RTK=5246...;Driver={ODBC_Driver_Name};Driver_Property1=Driver_Value1;Driver_Property2=Driver_Value2;... 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.

     👁 Using the built-in connection string designer to generate a JDBC URL (Salesforce is shown.)
     
   • Set Import Query to a SQL query that will extract the data you want from JDBC-ODBC Bridge, i.e.:
     SELECT * FROM Account
   👁 Configuring the database source
   
4. From the "Sink" tab, click to add a destination sink (we use Google BigQuery in this example)
5. Click "Properties" on the BigQuery sink to edit the properties
   • Set the Label
   • Set Reference Name to a value like jdbcodbc-bigquery
   • Set Project ID to a specific Google BigQuery Project ID (or leave as the default, "auto-detect")
   • Set Dataset to a specific Google BigQuery dataset
   • Set Table to the name of the table you wish to insert JDBC-ODBC Bridge data into
   👁 Configuring the BigQuery sink
   

With the Source and Sink configured, you are ready to pipe JDBC-ODBC Bridge data into Google BigQuery. Save and deploy the pipeline. When you run the pipeline, Google Data Fusion will request live data from JDBC-ODBC Bridge and import it into Google BigQuery. 👁 Image

While this is a simple pipeline, you can create more complex JDBC-ODBC Bridge pipelines with transforms, analytics, conditions, and more. Download a free, 30-day trial of the CData JDBC-ODBC Bridge Driver and start working with your live JDBC-ODBC Bridge data in Google Data Fusion today.