Connecting to JDBC
1. Overview
The JDBC generic data provider option lets you connect to data sources that have a JDBC driver. Since there are third-party drivers available for a wide range of databases and other data sources, this means you can connect to more data sources out-of-the-box than we can list. You can often use an existing JDBC driver rather than rely on a custom data provider.
Here are some examples of data sources that have a JDBC driver:
- Amazon Athena (Amazon S3)
- Apache Phoenix
- Hive
- Impala
- H2
- Confluence
There are some limitations to be aware of when connecting via JDBC. See Product Notes for more details.
2. Install
You can install additional drivers as needed on computers where the application or gateway is installed. Using JDBC drivers with a gateway requires version 23.3 or higher.
On Windows, JDBC drivers require a 64-bit version of the Java SE Runtime Environment version 8 or above to be installed in order to run.
- When driver files are located on the same machine as a full application instance, indicate this location in the Path To JDBC Drivers configuration setting.
- When using a gateway, find the setting named JdbcDriverPath in the config override file or the corresponding Docker environment variable. Use this to specify the path to the folder containing the driver files.
The application needs read access to the files. On Windows, the application runs as the Network Service identity unless you chose a different option for the application pool when installing. On Linux, the application runs as the dundasbi user/group. The gateway Docker image runs as the user you choose.
3. Connect
With a JDBC driver installed and configured, you can use the driver by creating a new data connector.
In the New Data Connector dialog, set a Name, and then set Data Provider to JDBC.
Next, supply the required credentials:
- Enter the JDBC URL. This will be different for every database and depends on the JDBC driver. For example, the JDBC URL for Amazon Athena is jdbc:awsathena://athena.us-east-1.amazonaws.com:443.
- Enter the User Name for your database account.
- Enter the Password for your database account.
If needed, click to expand the Advanced section to configure settings such as the following.
3.1. Driver properties
Some connection properties cannot be included directly in the JDBC URL and have to be provided separately in the Driver Properties field. Similar to the URL, the list of properties and their format are different for each database and are typically documented by the JDBC driver provider.
For example, as documented by Amazon, the Amazon Athena JDBC driver can take parameters as part of the JDBC URL:
jdbc:awsathena://athena.us-west-1.amazonaws.com:443?max_error_retries=20&connection_timeout=20000
But also requires the s3_staging_dir to indicate a location to which the query output is written:
s3_staging_dir=s3://aws-athena-query-results-154861344432-us-east-1
3.2. Driver class name
Older JDBC drivers may require a Driver Class Name to enable the classes in the JAR file. For example, the main JDBC driver class for Amazon Athena is com.amazonaws.athena.jdbc.AthenaDriver.
3.3. Use PrepareStatement
Certain non-JDBC compliant drivers may produce an error in certain cases, which can be avoided by unchecking the Use PrepareStatement advanced option.
3.4. Fetch size
In some cases, setting a specific fetch size may help if too much data is loaded into memory at once resulting in an out of memory exception from the driver.
In version 10 or higher, uncheck Use Default Fetch Size and set a number (e.g., 10000 rows).
3.5. Date trim expression
Depending on certain conditions, some functionality such as time dimensions normally result in queries sent to the data provider that trim some portions of date/time values, for example to keep only the year portion of the original date. The syntax for this expression varies depending on the particular data source and JDBC driver you are using, so script written in DundasScript can be set for the Date Trim Expression setting. Without this expression, an error may occur for certain operations unless the data is stored in a data cube.
This script can access objects:
- dateGranularity (type: DateTimeGranularity) – The granularity or level to which the date should be trimmed.
- dataTypeName (type: string) – The name of the column's data type, such as datetime or date, which may affect whether or not a value needs trimming.
- stringBuilder (type: StringBuilder) – A string builder object instance that can optionally be used if needed.
The script should return a string representing a valid expression for the data source, including a placeholder {0} that will be replaced by a column expression when included in a query.
The following is a sample date trim expression for a Trino JDBC driver:
if (dataTypeName == null)
{
dataTypeName = "datetime";
}
switch (dateGranularity)
{
case DateTimeGranularity.Minute:
if (dataTypeName.Equals("date", StringComparison.OrdinalIgnoreCase))
{
// No need to trim
return "{0}";
}
else
{
return "date_trunc('minute', {0})";
}
case DateTimeGranularity.Hour:
if (dataTypeName.Equals("date", StringComparison.OrdinalIgnoreCase))
{
// No need to trim
return "{0}";
}
else
{
return "date_trunc('hour', {0})";
}
case DateTimeGranularity.Day:
if (dataTypeName.Equals("datetime", StringComparison.OrdinalIgnoreCase))
{
return "date_trunc('day', {0})";
}
else if (dataTypeName.Equals("date", StringComparison.OrdinalIgnoreCase))
{
// No need to trim
return "{0}";
}
else
{
return "date({0})";
}
case DateTimeGranularity.Month:
return "date_trunc('month', {0})";
case DateTimeGranularity.Year:
return "date_trunc('year', {0})";
default:
return null;
}
return null;
4. Java VM path
Some JDBC drivers require additional binary libraries (examples include the DB2 and SQL Server databases when using Windows Authentication).
To indicate the location of such libraries, access Administration from the main menu when logged in as an administrator, click to expand Setup and select Config to navigate to configuration settings.
Choose the option to show advanced settings, find the setting JVM Library Path and edit it to indicate the folder path.