Connecting to a data source using JDBC

A JDBC connection string is used to load the driver and to indicate the settings that are required to establish a connection to the data source. These settings are referred to as connection properties. The connection string is a URL with the following format:

jdbc:rs:dv://host:port;Key=Value;Key=value;...

For example, you can use the following connection string to access virtual tables on your Data Virtualization Manager server, where host is the network hostname or IP address:

jdbc:rs:dv://host:1200;DatabaseType=DVS;user=userid;password=xxxx

If you prefer not to use the //host:port syntax, you can use the following string:

jdbc:rs:dv:Host=host;Port=1200;DatabaseType=DVS;user=userid;password=xxxx

Additional connection properties can be added to influence the behavior of the driver-server communication.

Coding a JDBC application

A JDBC application can establish a connection to the data source using the JDBC DriverManager interface, which is part of the java.sql package. A connection is created by passing the connection string URL to the DriverManager.getConnection method. Alternate forms of this API allow you to specify the user and password as separate parameters, or to specify some or all of the connection properties using the java.util.Properties parameter.

Sample Java code fragment:

final String url = "jdbc:rs:dv://host:1200; DatabaseType=DVS; user=userid; password=xxx";
final String sql = "SELECT * FROM MY_VIRTUAL_TABLE";
try (final Connection conn = DriverManager.getConnection(url)) {
   try (final PreparedStatement statement = conn.prepareStatement(sql)) {
      try (final ResultSet rs = statement.executeQuery()) {
         // process the result set
      }
   }
}

Coding a Spark application

A Spark application can access a data source using the Spark SQL interface, which is defined in the org.apache.spark.sql package namespace. The DataFrameReader interface, obtained via SparkSession.read, is used to load data sets from the data source. Spark SQL uses the JDBC driver to connect to the Data Virtualization Manager server and access the virtualized data.

Sample Scala code fragment:

val url = "jdbc:rs:dv://host:1200; DatabaseType=DVS; user=userid; password=xxx"
val sql = "MY_VIRTUAL_TABLE"
val spark: SparkSession = ...
val df = spark.read
   .format("jdbc")
   .option("url", url)
   .option("dbtable", sql)
   .load()
// perform data analytics on the dataframe