Metatron Discovery’s connection extension for Microsoft SQL Server

Created with Sketch.

Metatron Discovery’s connection extension for Microsoft SQL Server


Notice: Undefined offset: 1 in /data/httpd/www/html/wp-includes/media.php on line 764

Notice: Undefined offset: 1 in /data/httpd/www/html/wp-includes/media.php on line 766

Notice: Undefined offset: 1 in /data/httpd/www/html/wp-includes/media.php on line 769

Notice: Undefined offset: 1 in /data/httpd/www/html/wp-includes/media.php on line 70
0
(0)

Currently, there are five types of Supported DB in Metatron Discovery.

  • MySQL
  • Hive
  • PostgreSQL
  • Presto
  • Druid

But you may need to connect to a DB other than those listed above.
For example, Microsoft SQL Server.
It is not so difficult if the DB you want to connect to provides a JDBC driver.
Add the connection extension for Microsoft SQL Server through the following guide.

Connection extension basic

The connection extension consists of three main interfaces: JdbcDialect, JdbcAcceseor, and JdbcConnector.

The functions of each interface are summarized as follows.

  • JdbcDialect: Dialect for a specific DB. Defines the JDBC connection url format, Query, Format, and so on.
  • JdbcConnector: manage connection with DB
  • JdbcAcceseor: Uses JdbcConnector and JdbcDialect to query the data of actual DB.

JdbcConnector and JdbcAccessor are abstract implementations, so you do not have to add anything else, just define JdbcDialect.
Then, when building the module, all of the Library Dependencies including the JDBC Driver are packaged and made into a zip file.
After distributing the generated extension binary to Metatron Discovery’s extensions path and restarting Metatron Discovery, that extension becomes available in Discovery.
Let’s create a Microsoft SQL Server connection extension step by step.

Step 1. create an extension module

You can create a simple module project using the maven archetype.

1. Navigate to the directory of the discovery-extensions module.
metatron-discovery $ cd discovery-extensions
2. Create an extension module using archetype.
discovery-extensions $ mvn archetype:generate -DarchetypeGroupId=app.metatron.discovery -DarchetypeArtifactId=discovery-extension-connection-archetype -DarchetypeVersion=1.0.0
3. In interactive mode, enter the desired value.
$ mvn archetype:generate -DarchetypeGroupId=app.metatron.discovery -DarchetypeArtifactId=discovery-extension-connection-archetype -DarchetypeVersion=1.0.0

After entering the above command, enter the following in interactive mode to simplify the basic structure of the Extension Module.

  • groupId : app.metatron.discovery
  • artifactId : mssql-connection
  • version : 7.3.0
  • package :
[INFO] Scanning for projects...
 [WARNING]
 [WARNING] Some problems were encountered while building the effective model for app.metatron.discovery:discovery-extensions:pom:3.2.0
 [WARNING] 'build.plugins.plugin.version' for org.apache.maven.plugins:maven-compiler-plugin is missing. @ app.metatron.discovery:metatron-discovery:3.2.0, /../metatron-discovery/pom.xml, line 105, column 21
 [WARNING]
 [WARNING] It is highly recommended to fix these problems because they threaten the stability of your build.
 [WARNING]
 [WARNING] For this reason, future Maven versions might no longer support building such malformed projects.
 [WARNING]
 [INFO]
 [INFO] ------------------------------------------------------------------------
 [INFO] Building discovery-extensions 3.3.0-rc1
 [INFO] ------------------------------------------------------------------------
 [INFO]
 [INFO] >>> maven-archetype-plugin:3.0.1:generate (default-cli) > generate-sources @ discovery-extensions >>>
 [INFO]
 [INFO] <<< maven-archetype-plugin:3.0.1:generate (default-cli) < generate-sources @ discovery-extensions <<<
 [INFO]
 [INFO]
 [INFO] --- maven-archetype-plugin:3.0.1:generate (default-cli) @ discovery-extensions ---
 [INFO] Generating project in Interactive mode
 [INFO] Archetype repository not defined. Using the one from [app.metatron.discovery:discovery-extension-connection-archetype:1.0.0] found in catalog local
 Define value for property 'groupId': app.metatron.discovery
 Define value for property 'artifactId': custom-connection
 Define value for property 'version' 1.0-SNAPSHOT: : 7.3.0
 Define value for property 'package' app.metatron.discovery: :
 Confirm properties configuration:
 groupId: app.metatron.discovery
 artifactId: mssql-connection
 version: 7.3.0
 package: app.metatron.discovery
 Y: : Y
 [INFO] ----------------------------------------------------------------------------
 [INFO] Using following parameters for creating project from Archetype: discovery-extension-connection-archetype:1.0.0
 [INFO] ----------------------------------------------------------------------------
 [INFO] Parameter: groupId, Value: app.metatron.discovery
 [INFO] Parameter: artifactId, Value: mssql-connection
 [INFO] Parameter: version, Value: 7.3.0
 [INFO] Parameter: package, Value: app.metatron.discovery
 [INFO] Parameter: packageInPathFormat, Value: app/metatron/discovery
 [INFO] Parameter: package, Value: app.metatron.discovery
 [INFO] Parameter: version, Value: 7.3.0
 [INFO] Parameter: groupId, Value: app.metatron.discovery
 [INFO] Parameter: artifactId, Value: mssql-connection
 [INFO] Parent element not overwritten in /../metatron-discovery/discovery-extensions/mssql-connection/pom.xml
 [INFO] Project created from Archetype in dir: /../metatron-discovery/discovery-extensions/mssql-connection
 [INFO] ------------------------------------------------------------------------
 [INFO] BUILD SUCCESS
 [INFO] ------------------------------------------------------------------------
5. Created module structure

The files generated by Maven archetype are simply a class. (WelcomeConnectionExtension.java)

discovery-extensions $ tree mssql-connection
mssql-connection
 ├── pom.xml
 └── src
     └── main
         ├── assembly
         │   └── assembly.xml
         ├── java
         │   └── app
         │       └── metatron
         │           └── discovery
         │               └── WelcomeConnectionExtension.java
         └── resources

 

Step 2. Customize extension

Let’s modify the extension project to support Microsoft SQL Server.

1. Rename Extension Class

The first thing to do is to rename the Welcome classes.

  • Modify the name of the WelcomeConnectionExtension class to MssqlConnectionExtension. (modify the file name too)
  • Modify the name of the WelcomeDataAccessor class to MssqlDataAccessor.
  • Modify the name of the WelcomeDialect class to MssqlDialect.
2. Open and modify the created pom.xml.

– In the parent.version property, type metatron-discovery version (ex: 3.3.0-rc1)

<parent>
  <artifactId>discovery-extensions</artifactId>
  <groupId>app.metatron.discovery</groupId>
  <version>3.3.0-rc1</version>
</parent>

– Change the properties as desired. The rest of the properties need not be modified and only the explicit name of the plugin class needs to be modified.
Just change the plugin.class property to the explicit name of the class you rename above. Please refer to the sample below.

<properties>
  <project.distribution.path>${basedir}/../../discovery-distribution</project.distribution.path>

  <!-- extension id-->
  <plugin.id>${project.artifactId}-extension</plugin.id>

  <!-- extension Class (needs to be modified) -->
  <plugin.class>app.metatron.discovery.MssqlConnectionExtension</plugin.class>

  <!-- extension version -->
  <plugin.version>${project.version}-${project.parent.version}</plugin.version>

  <plugin.provider></plugin.provider>
  <plugin.dependency></plugin.dependency>
</properties>
3. Add the Microsoft SQL Server JDBC Driver as a dependency

The version of mssql-jdbc that corresponds to the version of SQL Server you want to connect to can be found here:
https://docs.microsoft.com/en-us/sql/connect/jdbc/system-requirements-for-the-jdbc-driver.

<dependencies>
  <dependency>
    <groupId>com.microsoft.sqlserver</groupId>
    <artifactId>mssql-jdbc</artifactId>
    <version>7.3.0.jre8-preview</version>
  </dependency>
</dependencies>
4. Modify MssqlConnectionExtension.java

Let’s look at the Extension Class.
The class only serves to link the application with the @Extension annotated class, and the actual implementation is JdbcDialect, as described above.
Although the JdbcDialect interface consists of many functions, implementing the essential parts below is sufficient for functional operation.

  • Connection Name : This is the name of Connetion to show to the user. Simply enter MS-SQL.
@Override
public String getName() {
  return "MS-SQL";
}

 

  • Connetion Implementor : You can input the unique delimiter of the connection.
@Override
public String getImplementor() {
  return "MSSQL";
}

 

  • InputSpec : This is a spec that the user must enter in order to establish a connection to the DB. Host and Port are default values. In the case of SQL Server, the three values of Username, Password, and Database are required.
@Override
public InputSpec getInputSpec() {
  return (new InputSpec())
      .setAuthenticationType(InputMandatory.MANDATORY)
      .setUsername(InputMandatory.MANDATORY)
      .setPassword(InputMandatory.MANDATORY)
      .setCatalog(InputMandatory.NONE)
      .setSid(InputMandatory.NONE)
      .setDatabase(InputMandatory.MANDATORY);
}

 

  • DriverClass : This is the explicit name of the DriverClass to create the Connection. For the mssql-jdbc dependency added above, use a driver named “com.microsoft.sqlserver.jdbc.SQLServerDriver”.
@Override
public String getDriverClass(JdbcConnectInformation connectInfo) {
  return "com.microsoft.sqlserver.jdbc.SQLServerDriver";
}

 

  • DataAccessorClass : The explicit name of the DataAccessorClass to use. Create an instance of the class and use it as a DataAccessor class to query the actual DB data.
@Override
public String getDataAccessorClass(JdbcConnectInformation connectInfo) {
  return "app.metatron.discovery.MssqlConnectionExtension$MssqlDataAccessor";
}

 

jdbc:sqlserver://<hostname>:<port>;database=<database>
@Override
public String makeConnectUrl(JdbcConnectInformation connectInfo, String database, boolean includeDatabase) {
  if(StringUtils.isNotEmpty(connectInfo.getUrl())) {
    return connectInfo.getUrl();
  }

  StringBuilder builder = new StringBuilder();
  builder.append("jdbc:sqlserver:/");

  // Set HostName
  builder.append(URL_SEP);
  builder.append(connectInfo.getHostname());

  // Set Port
  if(connectInfo.getPort() != null) {
    builder.append(":").append(connectInfo.getPort());
  }

  // Set DataBase
  if(StringUtils.isNotEmpty(connectInfo.getDatabase()) && includeDatabase) {
    builder.append(";");
    builder.append("database=");
    builder.append(connectInfo.getDatabase());
  }
  return builder.toString();
}

You are now ready to connect to Microsoft SQL Server through Metatron Discovery.

 

Step 3. Build & Test Extension

1. Build Metatron Discovery
metatron-discovery $ mvn clean install -DskipTests
2. Start Metatron Discovery

Untar the archive binary file of Metatron Discovery.

$ tar zxf metatron-discovery-{VERSION}-{TIMESTAMP}-bin.tar.gz
$ bin/metatron.sh
3. Create Connection to Microsoft SQL Server

Log in as the admin account admin and go to Management -> Data Storage -> Data Connection. In the Data Connection list, select the “Create new Data Connection” button to create a Data Connection.

Create data connection

Connectable DB Type You can see that “MS-SQL” type is added to the right side.
Enter the Microsoft SQL Server information to test and create a Data Connection.

4. Create Workbench

Now create a workbench using the newly created Data Connection. Go to Admin’s workspace and select the Workbench button in the bottom right corner.

 

Create workbench

Select the Microsoft SQL Server Connection you just created and create a workbench.

5. Execute SQL

Execute simple query

You can see the result successfully by executing a simple query.

 

An additional feature for Microsoft SQL Server only

So far we have been talking about the usual DB Connection Extension, and let’s add functionality for Microsoft SQL Server only.

Schema Browser

The Workbench’s Schema Browser has an Information Tab that allows you to view table details.

 

Information Tab before modification

Currently, if you look up the Information tab, it says No Data.

 

Let’s implement additional extensions to query the sys schema in Microsoft SQL Server for additional information and display the results in the Information tab.

/**
 * Gets table desc query.
 *
 * @param connectInfo the connect info
 * @param catalog the catalog
 * @param schema the schema
 * @param table the table
 * @return the table desc query
 */
 String getTableDescQuery(JdbcConnectInformation connectInfo, String catalog, String schema, String table);

 

Below is a simple query sample that can be used to query the creation date and time of the table and the modification date.

SELECT
  t.name as TableName,
  t.create_date as CreateDate,
  t.modify_date as ModifyDate
FROM
  sys.tables t
WHERE t.NAME = 'some_table_name';

 

Implement the getTableDescQuery Method using this query.

MssqlConnectionExtenstion.java
@Override
public String getTableDescQuery(JdbcConnectInformation connectInfo, String catalog, String schema, String table) {
  return "SELECT \n" +
      "    t.name as TableName,\n" +
      "    t.create_date as CreateDate,\n " +
      "    t.modify_date as ModifyDate\n" +
      "FROM \n" +
      "    sys.tables t\n" +
      "WHERE t.NAME = '" + table + "';";
}

 

Once you have modified the code above, you can build and deploy it again and restart Metatron Discovery.

 

Information Tab after modification

If you go to the Workbench’s Schema Browser, you can see that additional information appears on the information tab.

 

Conclusion

We have created a connection extension for Microsoft SQL Server.
There are various database services in Microsoft Azure. Of course, there are some databases not yet supported by Metatron Discovery.
However, you can easily create additional connection extensions like above.

How useful was this post?

Click on a star to rate it!

Average rating 0 / 5. Vote count: 0

No votes so far! Be the first to rate this post.

As you found this post useful...

Share this post on your social media!

We are sorry that this post was not useful for you!

Let us improve this post!

Tell us how we can improve this post?

Leave a Reply

Your email address will not be published. Required fields are marked *