JDBC and other Data Sources
It is also possible to map tables from other databases using JDBC.
Syntax:
CREATE EXTERNAL TABLE ext_jdbc_hello_ingres
(id INTEGER NOT NULL,
txt VARCHAR(20)NOT NULL)
USING SPARK WITH REFERENCE='dummy',
FORMAT='jdbc',
OPTIONS=('url' = 'db_connection_url',
'dbtable' = 'table_name',
'user' = 'username',
'password' = 'password')
Notes:
• The JDBC URL, the table name to access, and the user name and password are specified in the OPTIONS clause. The value specified WITH REFERENCE (dummy in this example) is not used, but the syntax requires a REFERENCE clause.
• Using the Database driver requires that the class path for the driver be added to the class path of the Spark Provider. To do this, add the following line to $II_SYSTEM/ingres/files/spark-provider/spark_provider.conf:
spark.jars /opt/user_mount/<<jdbc_driver jar>>
Note: The driver JAR files have to be in the folder that is mounted into the container.
Example for Oracle, Vector, Ingres, and Zen databases:
spark.jars= /opt/user_mount/ojdbc8.jar,/opt/user_mount/pv
jdbc2.jar,/opt/user_mount/pvjdbc2x.jar,/opt/user_mount/jpscs.jar
After changing the configuration file, you must restart the Spark Provider.
Note: You can automatically download and add libraries or drivers using the spark.jars.packages option. For more information, see https://spark.apache.org/docs/3.5.1/configuration.html.
JDBC Data Source:
1. Create external table for Actian Ingres data source:
CREATE EXTERNAL TABLE employee_ex_jdbc (
name CHAR(25) NOT NULL,
salary INTEGER NOT NULL
) USING SPARK WITH
REFERENCE='dummy',
FORMAT='jdbc',
OPTIONS=('url' = 'jdbc:ingres://usau-hcl-lnx01:V27/sparktest',
'dbtable' = 't1',
'user' = 'user1' ,
'password' = 'pass123');
In this example, we map the table t1 in database sparktest on node V2 on host usau-hcl-lnx01 to the external table employee_ex_jdbc and connect as user user1 using password pass123.
Note: JDBC connections to Actian Ingres should work out of the box. Contact Actian Support if you encounter any issues.
Using the JDBC driver requires to add the class path for the driver to the class path of the Spark Provider. To do this add the following line to $II_SYSTEM/ingres/files/spark-provider/spark_provider.conf:
spark.driver.extraClassPath /your_II_SYSTEM_value/ingres/lib/iijdbc.jar
2. Create native X100 table
CREATE TABLE employee_jdbc(
name CHAR(25) NOT NULL,
salary INTEGER NOT NULL
) WITH STRUCTURE=X100;
3. Load X100 table with INSERT command:
INSERT INTO employee_jdbc SELECT * FROM employee_ex_jdbc;
WARNING! The specified user credentials are visible for all users that can connect to the database.
Last modified date: 01/27/2026