Connecting to Vendor JDBC Connectors

Applicable to Sisense on Linux and Microsoft Windows

JDBC is one of the most widely supported Java APIs for connecting to a variety of relational databases. With Sisense's generic JDBC driver you can leverage native JDBC connectors to connect to additional data sources.

For a complete list of supported connectors, see Introduction to Data Sources.

Downloading Vendor JDBC Connectors

The connectors are provided by the following data source vendors:

Data Source Driver Class JDBC URL Example
Azure Data Explorer com.microsoft.sqlserver.jdbc.SQLServerDriver jdbc:presto://10.50.12.7:8080/mysql
ClickHouse ru.yandex.clickhouse.ClickHouseDriver jdbc:clickhouse://10.50.29.128:8123/flights
Denodo com.denodo.vdp.jdbc.Driver jdbc:vdb://<hostname>:<port>/<database>
Dremio com.dremio.jdbc.Driver jdbc:dremio:direct=10.50.29.135:31010
IBM DB2 (Connecting to IBM DB2) com.ibm.db2.jcc.DB2Driver jdbc:db2://db2w-vqplkwx.us-south.db2w.cloud.ibm.com:50001/BLUDB:sslConnection=true;
Impala com.cloudera.impala.jdbc4.Driver jdbc:impala://10.50.29.125:21050/tpch_200mb
PrestoDB io.prestosql.jdbc.PrestoDriver jdbc:presto://10.50.12.7:8080/mysql
Vertica com.vertica.jdbc.Driver jdbc:vertica://10.50.29.98:5433/tpch
Yellowbrick org.postgresql.Driver jdbc:postgresql://ecosystem1.yellowbrick.io:5432

Extracting the JDBC Connector

Extract your JDBC driver into a folder you create, as follows:

Important: Each data source has its own requirements for connecting to it via JDBC. Refer to the vendor documentation for this information.

Importing the Data Source Table into Your ElastiCube

  1. Open Sisense.

    For a non-local installation, open Sisense on the hosted cloud environment.

  2. In the Data page, open an ElastiCube or create a new ElastiCube.

  3. In the Model Editor, click . The Add Data dialog box is displayed.

  4. Click Generic JDBC to open the JDBC settings.
  5. In Connection String, paste the string you obtained above.
    jdbc:<your driver>:Initiate OAuth="OFF";User=XXXX;Password=********;
  6. In JDBC JARs Folder, enter the name of the directory where the JDBC’s JAR file islocated.
  7. In Driver's Class Name, enter the class (see above).
  8. In the User Name and Passwordfields:
    • If the User Name and Password are part of the connection string, leave these fields blank

    • If they are not part of the connection string, enter the credentials to your application here

  9. Click Next. A list of tables in the database are displayed. All tables and views associated with the database will appear in a new window.
  10. From the Tables list, select the relevant table or view you want to work with. You can click next to the relevant table or click Preview to see a preview of the data inside it.
  11. (Optional) Click + to customize the data you want to import with SQL. See Importing Data with Custom Queries for more information.
  12. After you have selected all the relevant tables, click Done. The tables are added to your data model.

Changing the Default Separator

For some data sources, you must change the default separator to the separator required by your data source.

You can change the default separator by modifying the value of tableNamePattern in the following configuration file:

Windows:

C:\ProgramData\Sisense\DataConnectors\JVMContainer\Connectors\jdbc_configs

Linux:

/opt/sisense/storage/connectors/jdbc_configs/

Limitations

The connector samples the first X rows and determines the column type. When Sisense finds rows within the data that does not match the column type, the Sisense driver returns an error.