Connecting to JBDC
Sisense provides a generic Java Database Connectivity (JDBC) connector that you can configure to import data from JDBC sources such as Postgres or Hive. JDBC is one of the most widely supported Java APIs for connecting to a variety of relational databases.
To import data from a JDBC source, you need a connection string that defines the location of the database and the necessary properties required for accessing the database. See Creating a Connection String for more information.
Keep in mind that each data source has its own requirements for connecting to it via JDBC. You must refer to their documentation for this information.
In addition, you need to extract your JDBC driver into a folder you create in the directory:
For example, if your using JDBC to connect to a Postgres database:
The folder in this example is called, postgresqldriver, and it should contain your JAR files.
This topic describes how to configure a JDBC connector in Sisense and then import data from the source to Sisense.
To import data from a JDBC data source:
- In the Data page, open an ElastiCube or create a new ElastiCube.
- In the Model Editor, click . The Add Data dialog box is displayed.
- Click JDBC to open the JDBC settings.
- In Connection String, enter the URL of your JDBC data source. See Creating a Connection String for more information.
- In JDBC JARs Folder, enter the name of the directory where the JDBC’s JAR files are located. For example, if you called the directory postgresqldriver, the value of this field would be postgresqldriver. This directory must be located in ...\ProgramData\Sisense\DataConnectors\jdbcdrivers\.
- In Driver's Class Name, enter the location of your JDBC driver. This is the name of the class that provides a basic service for managing your JDBC drivers. It should be provided by the provider of your JDBC's driver.
In User Name and Password, enter your credentials to the JDBC data source. These fields are not required if the user name and password were provided in the connection string.
- 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.
- From the Tables list, select the relevant table or view you want to work with. You can click next to the relevant table or view to see a preview of the data inside it.
- (Optional) Click + to customize the data you want to import with SQL. See Importing Data with Custom Queries for more information.
- After you have selected all the relevant tables, click Done. The tables are added to your schema.
With JDBC, a database is represented by a connection string, or URL, that defines its location and additional properties.
The connection string required to connect to a JDBC data source varies according to the requirements of that particular provider. You should check the provider’s documentation for details regarding their requirements.
For example, a JDBC connection string to Hive has the form described below:
For Hive, your connection string should include the following:
- Subprotocol: The value hive if you are connecting to a Hive Server 1 system. If you are connecting to a Hive Server 2 system, use the value hive2.
- Host: The DNS or IP address of the server hosting the Hive data warehouse.
- Port: The port to connect to on Host.
- Property: Additional properties that you can define. See Hive’s JDBC documentation for more information.
After you have created a connection string, it should look something like this:
Another example is Postgres, who also supports connections through JDBC.
A connection string to a Postgres data source has the following form:
For Postgres, your connection string should include the following:
- Host: The host name of the server. Defaults to localhost. To specify an IPv6 address your must enclose the host parameter with square brackets, for example:
- Port: The port number the server is listening on. Defaults to the PostgreSQL™ standard port number (5432).
- Database: The database name.
See Postgres’ JDBC documentation for more information.