Swiss Army Knives All the Way Down
We like to say that “FME is the Swiss army knife for your data”, on account of FME’s ability to solve so many different kinds of problems. Some of this power stems from FME embedding other tools with Swiss army knife qualities: GDAL, ODBC, and my favorite, JDBC.
These frameworks allow FME to expose many formats with minimal effort, including some that we haven’t gotten around to adding formal support for (or haven’t been invented yet!).
This post will explore the power of JDBC, and how FME users can take advantage of it.
Why Was JDBC Created?
There are many databases in the world (FME supports dozens, which is a drop in the bucket) and each one has a custom API for speaking to it.
The pain of having to write custom code for each database eventually led to the invention of the ODBC abstraction layer, which promised a common interface to any database that provided an ODBC driver. In theory that meant a piece of software could be written to speak to any database without any particular knowledge of that database. As often is the case, reality did not live up to the dream.
In practice, every ODBC driver has many strange quirks and Data Source Name (DSN) management involves using arcane GUIs through an obscure tool in Windows. Other platforms didn’t expose DSN management even to that degree; in general plan for a significant effort to get ODBC operational. You can use FME with arbitrary ODBC data sources (but I promise you won’t enjoy it as much as the JDBC experience).
The Java SDK added JDBC in its early days, as an answer to ODBC. JDBC turned out to have a few advantages over ODBC, including:
- Cross-platform compatibility for drivers, due to being written in Java
- Improved driver consistency, due to some combination of a clean programming interface and clearly stated behavior requirements
- DSN configuration replaced with connection strings
Extending Open Source Support
JDBC drivers are being created for every kind of data source imaginable (even when it’s an awkward fit). If there isn’t an official driver (and even then, sometimes) someone will probably create one.
FME users can leverage this community support to access data in many formats that are not officially supported. Some notable examples of Open Source drivers (all bundled with FME 2018.0 and exposed as full formats) include:
Microsoft Access
While a standard and critical format on Windows, it has been historically unusable on other platforms. UCanAccess changed all that with a JDBC driver that is very close to feature complete and is dramatically faster than some native Windows connection methods.
LDAP
LDAP is not a database. Its contents are not easily represented as tables. It has no support for SQL. And yet MyVirtualDirectory offers an LDAP JDBC driver that allows FME to perform basic reading and manipulation through SQL(like) queries.
CloudSpanner
Google provided an official driver for their new database, but it didn’t take long for a programmer to become dissatisfied and create an Open Source CloudSpanner driver that:
- is compatible with the official one
- is faster at reading than the official one
- adds DDL and writing support
- adds OAuth login support
Thanks, Knut, for all the changes you made to improve the FME user experience with CloudSpanner!
How to Leverage JDBC in FME
FME includes a Java Plug-in SDK that allows any developer to create Java plugins for FME. Internally we have leveraged this to rapidly produce a number of JDBC based formats, including:
You may notice that most of those listed are competing with non-JDBC equivalents within FME; they were developed because the non-JDBC versions are only available on Windows and there is a growing demand for these formats on Linux, particularly in the FME Cloud context.
Below, however, we will be focusing on FME’s “generic” JDBC Reader/Writer, which allows users to easily extend FME to support new and obscure formats.
Example: Adding Support for neo4j
Let’s say you want to use FME to interact with neo4j, a “graph” database used for modeling relationships between entities. This will require a few straightforward steps:
1. Acquire a JDBC driver (will have .jar extension, may be multiple files) and place it in the Java CLASSPATH that FME will search.
2. Create a Database Connection within FME
a. Determine the connection string format and fill in your parameters like host and port
b. Enter credentials
c. Optionally specify the Java Driver Class name for this driver. This will prevent badly behaved drivers from intercepting your connection string
d. If the driver supports other parameters you can embed them in the connection string or (preferably) specify them as name/value Connection Properties (FME 2018.0 and above). In the case of neo4j we specify flatten=-1 to ensure all data is returned from variable schema entities (a common problem when mapping a NoSQL type database into JDBC).
3. Now that you have a connection as shown below, can use the Test… button to confirm everything is working
4. Use FME to interact with your datasource. If your datasource has entities exposed as tables and supports SQL then you can add a JDBC Reader or Writer. If not, as in the case of neo4j, which has a custom Cypher query language, you can use a SQLCreator or SQLExecutor transformer to run native queries.
*
With JDBC you can apply FME’s power to all sorts of unsupported formats. Have you found any drivers that don’t work with FME as well as they could? Encountered a driver that is even stranger than LDAP’s? Let us know in the comments below!
You might also want to read:
Integrating Third-Party Tools with FME (No Coding Required)
Raven Kopelman
