Creating a new database plugin

In this article we demonstrate how to implement support for a new database type by creating a new OBS plugin (Example with Apache Drill)

Creating a new database plugin

The database plugins are individual projects. You can find them on Open Bouquet github page at https://github.com/openbouquet.

The easiest way to start developing your own plugin is probably to fork the PostgreSQL plugin and modifying it to support your database vendor (you can find it here: https://github.com/openbouquet/bouquet-plugin-postgresql)

Registring new database plugin

We need to tell to the jdbc manager how to handle the database connections.

We use the ServiceLoaderto detect whether a jar is a plugin for a database.

Inside your plugin project, you need to create a folder =src/main/resources/META-INF/services/= and put two text files named com.squid.core.jdbc.vendor.IVendorSupport and com.squid.core.sql.db.templates.ISkinProvider inside that indicates that you provide classes that will implements the interfaces.

Inside each text file, you need to provide with the full name of the class that implements that interface. For example, in com.squid.core.jdbc.vendor.IVendorSupport, you find com.squid.core.jdbc.vendor.apachedrill.ApacheDrillVendorSupportand in com.squid.core.sql.db.templates.ISkinProvidercom.squid.core.jdbc.vendor.apachedrill.render.ApacheDrillSkinProvider

You also need to add the plugin to the classpath of OBS plugin (to have the plugin's jar packed inside the OBS plugin jar).

Defining a new database vendor
  • Extends DefaultVendorSupport to call the new JDBCDataFormatter

    JDBCDataFormatter createFormatter(DataFormatter formatter, 
        Connection connection){
            return new ApacheDrillJDBCDataFormatter(formatter,
                                            connection);
        }
  • You need to indicate the new support. 

    • Specify a Vendor ID corresponding to the new driver "(Apache Drill" in our case). You need to use JDBC getMetadata() or populateDatabase(Database database) to know which ID you should put.
    • String getVendorId() and boolean isSupported(DatabaseProduct product) needs to be overriden to return the new id and to indicate the new support.
C DataFormatter

It is only needed if your types are specific to the database. the two methods to overide are unboxJDBCObject and formatJDBCObject

Drill return an hadoop specific type for the text (org.apache.hadoop.io.Textinstead of VARCHAR)

We need to override unboxJDBCObject to catch the VARCHAR and use the method from the Text class to make it a String.

public Object unboxJDBCObject(final Object jdbcObject, final int colType) throws SQLException {

    switch (colType) {

    // VARCHAR are actually hadoop text
    case Types.VARCHAR:
        org.apache.hadoop.io.Text hadoopText = (org.apache.hadoop.io.Text)jdbcObject;
        return (hadoopText.toString());

    default:
        return super.unboxJDBCObject(jdbcObject, colType);
    }

}
The Skins and defining operators

The syntax of operators can vary for each database type.

In order to modify the vendor syntax, you need:

  • A new Skin (extending DefaultJDBCSkin). The skin will give us the method renderused to translate queries from Bouquet to the specific SQL language used by the new database. For example, if you want to truncate a date to the month (2015-04-07 return 2015-04-01), specific SQL languages are going to be different:

    • In MySQL you can use CAST(EXTRACT("+ args[0] +" ,'%Y-%m-01') as DATE)
    • In Apache Drill you should something like 

      CAST(CONCAT(EXTRACT(year FROM (CAST("+args[0]+" AS DATE))),'-',EXTRACT(month FROM (CAST("+args[0]+" AS DATE))),'-01') AS DATE)
  • A new SkinProvider extending DefaultSkinProvider

    • override getFeatureSupport() to indicate which (specific) features you are supporting.
    • Define METADATA_SEARCH_SUPPORT to create the methods that will be used to populate the tables and columns metadata. You need to define:
      • createTableSearch()
      • createColumnSearch()

Compiling and testing your plugin

Compile the plugin jar using maven from the local plugin directory:

mvn clean compile assembly:single

Then you'll have to manually copy the jar file into the OBS DRIVER directory, and restart OBS to load the new driver.

 

 

 

 

 

 

 

 

 

 

 

 

 

Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.