Reading from RDBMS in Workflow

Fire has JDBC Processors for reading from JDBC sources or writing to JDBC sinks.

In order to connect to a JDBC source like MySQL/Oracle/DB2 etc. the JDBC driver needs to be installed in Fire Insights.

Use the steps here for installing the corresponding JDBC driver for your RDBMS:

Workflow for reading from MySQL

Below is a workflow which reads data from MySQL and saves to a CSV file. It reads in the data from the dm_product table in MySQL and saves it to a CSV file.

JDBC Workflowt

JDBC Processor Configuration

Below are the configuration details of the JDBC Processor. It uses the provided user for reading from the MySQL database. On clicking on Refresh Schema, Fire gets the schema of the table in MySQL and populates the entries.

JDBC Processor Dialog

Results of reading from MySQL table

The below screenshot displays some of the records read from the MySQL table by Fire.

JDBC Read Results

Specifying a sub-query

In the configuration of the JDBC node, for db_table anything that is valid in a FROM clause of a SQL query can be used. For example, instead of a full table we could also use a subquery in parentheses.

More details are available on the Spark Guide : https://spark.apache.org/docs/1.6.0/sql-programming-guide.html#jdbc-to-other-databases

JDBC Sub-Query

Above we have specified a subquery which selects only the ‘first_name’ from the employees table.

JDBC Sub-Query Output

JDBC Drivers

Below are the JDBC URL’s for some databases:

  • MySQL : com.mysql.jdbc.Driver
  • PostgreSQL : org.postgresql.Driver
  • Oracle : oracle.jdbc.driver.OracleDriver

Example JDBC URL

Below are some examples of JDBC URL for reading from Relational sources: