JDBC Data Source
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-Vector Provider. To do this, add the following line to $II_SYSTEM/ingres/files/spark-provider/spark_provider.conf:
spark.driver.extraClassPath /some_path/<<jdbc_driver jar>>
Example for Oracle, Vector, Ingres, Zen Database:
spark.driver.extraClassPath /your_II_SYSTEM_value/ingres/lib/iijdbc.jar:/some_path/ojdbc8.jar:/some_path/pvjdbc2.jar:/some_path/pvjdbc2x.jar:/some_path/jpscs.jar
After changing the configuration file you must restart the Spark-Vector Provider.
JDBC Data Source:
1. Create external table for Vector 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.
Using the Ingres/Vector 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 Vector table
CREATE TABLE employee_jdbc(
name CHAR(25) NOT NULL,
salary INTEGER NOT NULL
) WITH STRUCTURE=X100;
3. Load Vector 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: 11/09/2022