Skip to main content Skip to complementary content

JDBC: Overview

JDBC is a method for querying and updating data from an external relational database through drivers and database-specific APIs. The APIs provide the Java classes that enable connections and interchange with relational databases like SQLSERVER, ORACLE, TERADATA, POSTGRESQL, and MYSQL. Data Source also utilizes JDBC to query and extract data directly from HDFS or the File System in place.

For more information on registration and installation of JDBC database driver files for Qlik Catalog, see also Database driver registration.

To connect to an external database via JDBC, users should have the following information:

  • Source Type: SQLSERVER, ORACLE, HIVE, TERADATA, POSTGRESQL, MYSQL
  • JDBC URL: Database connection locator
  • User Name: Client-specified
  • Password: Client-specified
JDBC URL configuration by database

Database

JDBC URL format

Default Port

Example

SQLSERVER

jdbc:sqlserver://<HOST>:<PORT>;databaseName=<DATABASE_NAME>

1433

jdbc:sqlserver://jupiter.acme.com:1433

jdbc:sqlserver://127.0.0.1:1433

ORACLE

jdbc:oracle:thin@<HOST>:<PORT>:<DATABASE_NAME>

1521

jdbc:oracle:thin:@jupiter.acme.com:1521:T10A

jdbc:oracle:thin:@192.168.2.1:1521:X01A

HIVE

jdbc:hive2://<HOST>:<PORT>/<DATABASE_NAME>

10000

jdbc:hive2://sebastian.podiumdata.com:10000/default

TERADATA

jdbc:teradata://<HOST>/dbs_port=<PORT>,database=<DATABASE_NAME>

1025

jdbc:teradata://wonderland/dbs_port=1025,database=ods

POSTGRESQL

 

jdbc:postgresql://<HOST>:<PORT>/<DATABASE_NAME>

5432

jdbc:postgresql://jupiter.acme.com:5432/test 
jdbc:postgresql://127.0.0.1:5432/test

MYSQL

jdbc:mysql://<HOST>:<PORT>/<DATABASE_NAME>

3306

jdbc:mysql://jupiter.acme.com:3306/test 
jdbc:mysql://127.0.0.1:3306/test 
jdbc:mysql://localhost:3306/

mysql?connectTimeout=0&socketTimeout=0&autoReconnect=true 

BIGQUERY

jdbc:bigquery://https://www.googleapis.com/bigquery/v2:443;ProjectId=<project>;OAuthType=0;OAuthServiceAcctEmail=<gserviceaccount_email>;OAuthPvtKeyPath=<path_to_gserviceaccount_json>;

Notes for BigQuery setup

443

jdbc:bigquery://https://www.googleapis.com/bigquery/v2:443;ProjectId=projectone-11;OAuthType=0;OAuthServiceAcctEmail=GBQ@projectone-11;OAuthPvtKeyPath=/usr/local/qdc/projectone-11-45.json;

The URL is dependent upon the relational database and JDBC driver. It is a string that the JDBC driver uses to connect to a database. The URL will always begin with 'jdbc:' and will be a variation on: jdbc:DBMSNAME://host:port/database

URL components
Component Value

host

The name of the server (defaults to localhost)/host name of the computer/server hosting the database (if not specified, default is 127.0.0.1)

port

The port number the server is listening on/port number of the computer/server hosting the database (if not specified, default value is 3306)

database

The name of the database that is being queried

Notes for BigQuery setup

  1. Authentication is configured using a Google Service Account. Details on this are contained in the section "Configuring Authentication / Using a Google Service Account" available here:

    • The private key must be JSON format: The content of the JSON private key should be pasted into the Qlik Catalog Source Connection "Password" field.
  2. The property "OAuthPvtKeyPath" value in the JDBC URL string must be specified and be writeable by the service account running Qlik Catalog. The text within the brackets (<path_to_gserviceaccount_json>) within the URL must be replaced with the full path to where the JSON file will be written. The private key will be temporarily written to this path while in use (for example, when Test Connection is selected). The person setting up this source connection should ensure that the path exists and is writeable.
  3. The Simba Google BigQuery JDBC driver jar files should be deployed as instructed in the Installation Guides. The step is copied here:

    If a JDBC driver is particularly complicated and consists of multiple jars (e.g., the Simba Google Big Query driver has dozens of jars), it can be further isolated into its own sub-directory. Copy the jars to a dedicated directory (e.g., /usr/local/qdc/jdbcDrivers/simbaBigQuery) and then run a SQL update statement as follows (default password is “nvs2014!”; alter path and name if needed): psql podium_md -U podium_md -c "update podium_core.pd_jdbc_source_info set alt_classpath = '/usr/local/qdc/jdbcDrivers/simbaBigQuery' where sname = 'BIGQUERY';"

Did this page help you?

If you find any issues with this page or its content – a typo, a missing step, or a technical error – let us know how we can improve!