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
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.
2. Create an extension module using archetype.
3. In interactive mode, enter the desired value.
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 :
5. Created module structure
The files generated by Maven archetype are simply a class. (WelcomeConnectionExtension.java)
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)
– 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.
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.
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.
- Connetion Implementor : You can input the unique delimiter of the connection.
- 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.
- 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”.
- 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.
- makeConnectUrl : Creates a JDBC Connection Url String using the DB Connection information entered by the user.
Please refer to the following page for general form of Connection URL to access SQL Server.
https://docs.microsoft.com/en-us/sql/connect/jdbc/building-the-connection-url
We will implement it to create a connection URL using the most general form.
You are now ready to connect to Microsoft SQL Server through Metatron Discovery.
Step 3. Build & Test Extension
1. Build Metatron Discovery
2. Start Metatron Discovery
Untar the archive binary file of Metatron Discovery.
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.
Below is a simple query sample that can be used to query the creation date and time of the table and the modification date.
Implement the getTableDescQuery Method using this query.
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.