Connecting to JDBC
Applicable to Sisense on Linux and Microsoft Windows
Sisense provides a generic Java Database Connectivity (JDBC) connector that you can configure to import data from JDBC sources such as Postgres. 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.
Keep in mind that each data source has its own requirements for connecting to it via JDBC. Refer to the provider documentation for this information.
Note: For the list of supported connectors, see Introduction to Data Sources.
Extracting the JDBC Driver
- Extract your JDBC driver into a folder you create in the directory:
In Windows: ...\ProgramData\Sisense\DataConnectors\jdbcdrivers\
In Linux: /opt/sisense/storage/connectors/
For example, if you are using JDBC to connect to a PostgresSQL database, create a folder called postgresqldriver, and place your JAR file inside it:
- If you are on a Linux deployment, place the JAR file in this location: /opt/sisense/storage/connectors/.
Creating a Connection String
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 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, 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.
Importing Data from a JDBC Data Source
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 Connecting to JDBC 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
- 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 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.
If you are using any browser-based authentication, for example, OAuth, while connecting to your data source with the JDBC connector, you need to update the JDBC configuration file. For information, click Configuring the JDBC Connector to Use OAuth.
Accumulate Builds for JDBC Connector
By default, the data that comes from your JDBC data sources is not accumulated at the time of the build. To enable data accumulation by integer and date in tables originating from JDBC data sources, click Enabling Accumulative Build for Connector.