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.jars /opt/user_mount/<<jdbc_driver jar>>
Note: The driver jar files must be in the folder that is mounted into the container.
Example for Oracle, Vector, Ingres, Zen Database:
spark.jars= /opt/user_mount/ojdbc8.jar,/opt/user_mount/pvjdbc2.jar,/opt/user_mount/pvjdbc2x.jar,/opt/user_mount/jpscs.jar
After changing the configuration file you must restart the Spark-Vector Provider.
Note: You can automatically download and add libraries or drivers via the option spark.jars.packages. For more information, see https://spark.apache.org/docs/3.5.1/configuration.html.
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.
JDBC connections to Ingres/Vector should work out of the box. Please contact Actian support if you encounter any issues.
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: 12/19/2024