Connectors Plugins
  • 08 May 2022
  • 8 Minutes to read
  • Dark
    Light

Connectors Plugins

  • Dark
    Light

The Connectors Plugins feature allows you to apply custom logic to modify how Sisense connects and works with a data source using Java code. There are four flows that you can modify:

  • Connection Flow: Modify how Sisense connects to a data source, for example, by adding parameters specific to the data source.
  • Discovery Flow: Modify which metadata is discovered by filtering tables or columns.
  • Query Execution Flow: Modify the query before it is executed.
  • Data Extraction Flow: Modify the extracted data during a build or a live connection to a data source.

Plugins are one (or more) Java classes in the connector folder that implement one or more interfaces that they are related to. You develop your plugins for some or all of the flows according to its scope. The scope of your plugin controls the flow in which the plugin is applied. For example, connection plugins can be applied to both Query Execution and Discovery flows. Defining the scope of your plugin is optional. If you do not define a scope, the plugin is automatically applied to all scopes. The scope is defined on the plugin level.

Plugin Lifecycle

There are four stages to the lifecycle of a plugin:

  • Initialization: You prepare a tar.gz file containing all the relevant files for the plugin. To introduce the plugin to the system, you execute a CLI command to install the plugin. This command copies and extracts the tar.gz content into the specified connector's directory, compiles the Java files and uses the classLoader to load the plugin classes. Afterwards, you need to restart the Query service.
  • Creation: The plugin is initiated with the connector it belongs to and the plugin classes are loaded.
  • Execution: The plugin's methods are executed according to the defined scope.
  • Destruction: The plugin is destroyed with the connector it belongs to and the onDestroyPlugin method is executed.

Enabling the Connectors Plugin

By default, the Connectors Plugin feature is disabled, which prevents it from being applied to your builds or Live connections.

To enable the Connectors Plugin feature:

  1. In Sisense, go to Admin > System Management > Configuration.

  2. In the top-left corner, click the Sisense logo five times.

  3. Open the Connectors section and toggle EnableConnectorsPlugins to Enabled.
    OR
    In the Sisense CLI, run the following command:
    si config set -key connectors.enableConnectorsPlugins -new-value true
    Restart the Query service.

To access the Sisense CLI, see Accessing the Sisense CLI in Using Sisense CLI Commands.

Creating Plugins

Sisense supports four Connector plugins types based on the four possible flows you can modify:

  • Connection extension
  • Discovery extension
  • ExecuteQuery extension
  • DataExtraction extension

This section describes how to implement each type and provides examples of how you can use them.

Plugin Packaging
You should package your plugins as a tar.gz file. Inside the tar.gz file you should include the following files:

  • Sisense Connectors SDK: This is the SDK jar file provided by Sisense. Contact your CSM for the latest version.
  • mainfest.yaml file: A manifest file that you create, which contains two key-value pairs for each plugin. The keys are name and version. The value of name is the name of your plugin, which you define. The version is the version of the plugin, which you define. These values are displayed in the Name and Version columns when you run the list plugins command.
  • Java Plugin files: The Java files that contain the logic of your extensions. To implement a plugin, you need to extend one or more extension classes (specified below), and override methods to implement your custom logic. The Java classes should not reside in a package.

Below is an example of a typical project's structure:
image.png

Example manifest.yaml:

image.png

Example tar.gz content:

image.png

Connection Extensions
You can implement Connection extensions to modify how Sisense connects to your data source by overriding the following methods:

  • ConnectionParameters beforeConnectionCreated(ConnectionParameters connectionParameters, ConnectionUiParameters connectionUiParameters, ConnectorConfig connnectorConfig)

    • ConnectionParameters contains:

      • String connectionString
      • Map<String, Object> connectionArguments
    • Allows you to modify connection parameters, for example adding a connection string parameter.

  • void afterConnectionCreated(Connection connection): Allows you to configure additional connection parameters.

@ConnectorPlugin(extensionScopes={PluginExtensionScope.LIVE, PluginExtensionScope.DISCOVERY})
class MyConnectionPlugin extends ConnectionExtension {
    ....
    @Override
    public void afterConnectionCreated(Connection connection) {
        doSomethingWithConnection(connection);
    }
}

Discovery Extensions
You can use the Discovery extension to modify data found in the data source.

Discovery extensions can override the following methods:

  • List< String > getDatabases(List< String > databases): Allows you to modify the database list.
  • List< Table > getTables(List< Table > tables): Allows you to modify the list of returned tables.
  • Table getTableDetails(Table table): Allows you to modify the list of returned table details.
interface DiscoveryPlugin extends ConnectorPlugin {
    default List<String> getDatabases(List<String> databases) {
		return databases;
	}
    default List<Table> getTables(List<Table> tables) {
		return tables;
	}
    default Table getTableDetails(Table table) {
		return table;
	}

}

The example below filters tables that start with the letter “A” on the data source level.

@ConnectorPlugin
class FilterTablesPlugin extends DiscoveryExtension {
	....
	@Override
    public List<Table> getTables(List<Table> tables) {
		return tables
				.stream()
				.filter(table -> table.getName().startWith("A"))
				.collect(Collectors.toList());
	}
}

ExecuteQuery Extension

Execute query extensions can be used to modify execute query behavior.

  • String beforeExecuteQuery(QueryContext queryContext, String query): Allows you to modify a query before its executed.
@ConnectorPlugin
class OrderByQueryPlugin extends ExecuteQueryExtension {
	....
	@Override
    public String beforeExecutQuery(QueryContext queryContext, String query) {
		// sort the results by some column
		return query + " ORDER BY " + someColumn;
	}
}

DataExtraction Extension
With the Data extraction extension, you can modify or filter data that has been extracted from your data source.

  • Object getObject(int columnIndex): Allows you to modify the returned value for a given column.
  • boolean moveToNextRow(): Allows you to skip/filter certain rows.
  • int getColumnCount(): Allows you to change the number of columns in the response (e.g. add a column).
@ConnectorPlugin(extensionScopes=PluginExtensionScope.BUILD)
class PasswordRemover extends DataExtractionPlugin {
	....
	@Override
    public Object getObject(int columnIndex) {
		if (columnIndex == PASSWORD_COLUMN) {
			return "";
		}
		return super.getObject(columnIndex);
	}
}

The example below adds a new column that is a sum of the other two columns.

@ConnectorPlugin(name="AddCalculatedColumnPlugin", scope=PluginExtensionScope.BUILD)
class AddCalculatedColumnPlugin extends DataExtractionExtension {
	private int calculatedColumn;
	....
	
	@Override
	public int getColumnCount() {
		calculatedColumn = resultSet.getMetadata().getColumnCount() + 1;
		return calculatedColumn;
	}
	
	@Override
    public Object getObject(int columnIndex) {
		if (columnIndex == calculatedColumn) {
			return resultSet.getInt(3) + resultSet.getInt(5);
		}
		return super.getObject(columnIndex)
	}
}

Managing Connector Plugins

This section provides a list of commands you can run to install and manage your connector plugins. For information accessing the Sisense CLI, see Using Sisense CLI Commands.

Installing Plugins

si connectors plugins install -connector-id<connectorID> -file<full path to plugin [.tar.gz]>

This command extracts the tar.gz file into the "plugins" folder under the connectors folder. Sisense compiles the plugin's Java files, and if it succeeds, Sisense activates the plugin according to the extensions it implements and its scope.

The tar.gz file must be located on a shared partition /opt/sisense/storage

>   si connectors plugins install -connector-id sql -file /opt/sisense/storage/myPlugins/myConnectionPlugin.tar.gz
Extract myConnectionPlugin.tar.gz and copy its content into /opt/sisense/storage/connectors/framework/sql/plugins and compile it

Viewing a List of Installed Plugins

si connectors plugins list

This command lists all plugins for all connectors and displays a table with the following columns: Connector ID | Name | Type | Scope | Plugin Version | SDK Version

  • Connector ID: The connector ID the plugin belongs to.
  • Name: Plugin name is taken from the name annotation.
  • Type: The interface plugin implements.
  • Scope: Query, build which are taken from the scope annotation. Defined as a combination of a plugin scope and its methods scopes.
  • Plugin Version: The version of the plugin that is defined in the manifest.
  • SDK Version: The version of the SDK the plugin is compatible with that is included in plugin’s tar.gz file.

Viewing a List of Plugins for a Connector

si connectors plugins list -connector-id <connectorID> -

This command lists all plugins for a specific connector and displays a table with the following columns: Name | Type | Scope | Plugin Version | SDK Version

>   si connectors plugins list -id sql
Connector ID	|   Name               | Type       | Scope | Plugin Version | SDK Version
sql         	|   myConnectionPlugin | Connection | query | 1.0.0             | 1.0.3

Uninstalling a Plugin

si connectors plugins uninstall -connector-id <connectorID>

This command uninstalls all the plugins in the "plugins" folder and removes all the files from the "plugins" folder.

Adding a Plugin and Uninstalling It

>   si connectors plugins install -id sql -file /opt/sisense/storage/myPlugins/myConnectionPlugin.tar.gz
Extract myConnectionPlugin.tar.gz and copy its content into /opt/sisense/storage/connectors/framework/sql/plugins and compile it
 
>   si connectors plugins list -connector-id sql
Connector ID	|   Name               | Type       | Scope | Plugin Version | SDK Version
sql         	|   myConnectionPlugin | Connection | query | 1.0.0        | 1.0.3
 
>   si connectors plugins uninstall -connector-id sql
unload myConnectionPlugin class and delete the files from the "plugins" folder
 
>   si connectors plugins list -connector-id sql
Connector ID	|   Name     | Type  | Scope | Plugin Version | SDK Version

Replacing a Plugin

>   si connectors plugins install -connector-id sql -file /opt/sisense/storage/myPlugins/myConnectionPlugin.tar.gz
Extract myConnectionPlugin.tar.gz and copy its content into /opt/sisense/storage/connectors/framework/sql/plugins and compile it
 
>   si connectors plugins uninstall -connector-id sql
unload myConnectionPlugin class and delete the files from the "plugins" folder
 
>   si connectors plugins install -connector-id sql -file /opt/sisense/storage/myPlugins/myNewConnectionPlugin.tar.gz
Extract myNewConnectionPlugin.tar.gz and copy its content into /opt/sisense/storage/connectors/framework/sql/plugins and compile it
 
>   si connectors plugins list -connector-id sql
Connector ID	|   Name	             | Type       | Scope | Plugin Version | SDK Version
sql         	|   myNewConnectionPlugin | Connection | query | 1.0.0    | 1.0.3

Installing Plugins for Multiple Connectors

>   si connectors plugins install -connector-id sql -file /opt/sisense/storage/myPlugins/mySqlPlugin.tar.gz
Extract mySqlPlugin.tar.gz and copy its content into /opt/sisense/storage/connectors/framework/sql/plugins and compile it
 
>   si connectors plugins install -connector-id oracle -file /opt/sisense/storage/myPlugins/myOraclePlugin.tar.gz
Extract myOraclePlugin.tar.gz and copy its content into /opt/sisense/storage/connectors/framework/oracle/plugins and compile it
 
>   si connectors plugins list
Connector ID	|   Name                 | Type           | Scope 
sql         	| mySqlPlugin       	| Connection    	| query
sql         	| mySqlPlugin       	| Discovery     	| query
oracle      	| myOraclePlugin    	| DataExtraction	| build
oracle      	| myOraclePlugin    	| ExecuteQuery  	| query

Was this article helpful?