Easysoft Blog

Fri, 19 Aug 2016

Transform ODBC Data in CloverETL

CloverETL as its name suggests is an Extract, Transform, Load (ETL) application. ETL applications are mainly designed to transform and cleanse data from one source database to the format required by the target database.

CloverETL started in 2002 as an open source project that was initially made available on Freshmeat (now known as Freecode) in October of that year. CloverETL's commercial life began in 2005.

CloverETL is written in Java and uses the JDBC API to communicate with databases. If a database has a driver supporting the JDBC API, CloverETL can unload data stored within a database table, or it can populate a database table with data from another source. If a database has a driver supporting the ODBC API, a piece of middleware known as a JDBC-ODBC Bridge is needed before CloverETL can work with that database.

Java used to include a JDBC-ODBC Bridge and previously Java applications such as CloverETL could uses this bridge driver to work with ODBC databases. Now that this JDBC-ODBC Bridge has been withdrawn, a bridge driver from a commercial vendor such as Easysoft is needed.

The Easysoft JDBC-ODBC Bridge is a client server product that enables a Java application such as CloverETL to access an ODBC database such as Microsoft Access. The client part of the Easysoft JDBC-ODBC Bridge is a JDBC driver. On Windows, the sever part is a Windows service that acts as an ODBC application.

The rest of this blog documents how to use the Easysoft JDBC-ODBC Bridge to work with data from an ODBC database in CloverETL.

  1. Configure a System ODBC data source for the database that you want to connect to in CloverETL.

    To do this, use the 32-bit version of ODBC Data Source Administrator on your CloverETL machine. On some versions of Windows, this is located in Control Panel > Administrative Tools. On some version of Windows, you need to search for ODBC in the taskbar search box. The 32-bit version of ODBC Data Source Administrator should be clearly labelled. If in doubt, in the Windows Run dialog box, type:

    %windir%\syswow64\odbcad32.exe
  2. Download the Easysoft JDBC-ODBC Bridge. (Registration required.)
  3. Install and license the Easysoft JDBC-ODBC Bridge on the machine where CloverETL is installed.

    For installation instructions, see the Easysoft JDBC-ODBC Bridge documentation.

  1. Copy the Easysoft JDBC-ODBC Bridge client, EJOB.jar, from the Easysoft distribution to the CloverETL project's $LIBDIR folder.
  2. In CloverETL, in the Navigator pane, right-click conn. From the pop-up menu, choose New > Other.

    The Select a wizard dialog box is displayed.

  3. In the Wizards list, choose Connection > Database Connection. and then choose Next.

    The Database connection dialog box is displayed.

  4. Complete the dialog box fields:
    Field Value
    Connection name Easysoft JDBC-ODBC Bridge
    User If your database requires a user name, supply it here.
    Password The password for User.
    URL jdbc:easysoft://localhost/data_source:logonuser=windows_user:logonpassword=password
    JDBC specific Generic
    Available drivers easysoft.sql.jobDriver
  5. Choose Next, and then enter a name for the configuration file in the space provided. Choose Finish.
  6. In the Navigator pane, right-click graph. From the pop-up menu, choose New > ETL Graph.

    The New ETL Wizard starts.

  7. Enter a name for the graph when prompted and then choose Finish.
  8. Drag a DBInputTable from the Readers Palette to the graph.
  9. Drag your newly created .cfg file from the Navigator palette to the DBInputTable reader.
  10. Double-click the DBInputTable.

    The Edit component DBInputTable dialog box is displayed.

  11. Type an SQL Query in the space provided. Choose OK.
  12. In the $DATATMP_DIR folder, create a new text file.
  13. Drag a FlatFileWriter from the Writers Palette to the graph.
  14. Double-click the FlatFileWriter.

    The Edit component FlatFileWriter dialog box is displayed.

  15. In the File URL box, use the ... button to browse for the newly created text file. Choose OK.
  16. Add a connector from the output of the DBInputTable to the input of the FlatFileWriter.
  17. Right-click the DBInputTable reader. From the pop-up menu, choose Extract Metadata.

    The Metadata Wizard starts.

  18. In the Database schema list, choose a table. Then choose Generate Query. Choose Next.
  19. Choose Finish.
  20. Choose Run > Run to populate the text file with OBDC data.