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
|
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 |
|
MYSQL |
jdbc:mysql://<HOST>:<PORT>/<DATABASE_NAME> |
3306 |
jdbc:mysql://jupiter.acme.com:3306/test 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>; |
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
| 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
-
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:
Simba JDBC Driver with SQL Connector for Google BigQuery, Installation and Configuration Guide
- 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.
- 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.
-
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';"