ODBC/JDBC V3 Data Gateway

We have an embedded SQL engine for parsing, validating, optimizing and executing SQL queries on the data stored in a Space Where GigaSpaces data is stored. It is the logical cache that holds data objects in memory and might also hold them in layered in tiering. Data is hosted from multiple SoRs, consolidated as a unified data model.. Our SQL engine is SQL-99 compatible and supports all read operations.

Executing an SQL query can be done either by using our Data Gateway using the PostgreSQL wire protocol or by using our JDBC Java DataBase Connectivity. This is an application programming interface (API) for the Java programming language, which defines how a client may access a database. v3 package.

Connecting through Data Gateway

Our Data Gateway is a GigaSpaces Service that supports the PostgreSQL wire protocol. This allows existing applications and tools built on PostgreSQL to access GigaSpaces Data Gateway seamlessly and without any code change.

BI Business intelligence (BI) is software that ingests business data and presents it in user-friendly views such as reports, dashboards, charts and graphs. tools like Tableau, PowerBI, MicroStrategy and others can integrate with GigaSpaces just by using their built-in Postgres JDBC or ODBC Microsoft Open DataBase Connectivity. This interface is a C programming language interface that makes it possible for applications to access data from a variety of database management systems (DBMSs). ODBC is a low-level, high-performance interface that is designed specifically for relational data stores. driver.

Connection String

The connections string has the same structure as Postgres (including the default port of 5432).

Data Gateway Activation

Activating the Data Gateway can be done in Ops Manager, using the command line, and via the REST-API.

curl -X POST --header 'Accept: application/json' 'http://localhost:8090/v2/data-gateway?name=myService&instances=3&contextProperties=file://config/myfile.properties%20kl=v1%20k2=v2'

Working with GigaSpaces JDBC Driver

As part of our code base we provide a JDBC driver for accessing GS data grid through a standard JDBC interface. Using GS JDBC v3 provides its client with industry-standard SQL querying capabilities, validations and query optimizations. Connecting the data grid through JDBC is done using a connection string as shown below.

Acquire a Connection Using V3:

Connection conn = DriverManager.getConnection("jdbc:gigaspaces:v3:localhost:4174/mySpace", props);

Acquire a Connection Using V3 with Multiple Locators:

Multiple locators (hosts) can be provided in the connection string separated by semicolons as shown below. The lookup port can be omitted to indicate using the default (4174).

Connection conn = DriverManager.getConnection("jdbc:gigaspaces:v3:host1;host2;host3/mySpace", props);

Embedding the Query Processor Within the Application

Query processing can either be done at the server side or at the client side. By default it is done on the server. To override the default behavior and run embedded within the application process (client side), set the following property when acquiring the connection. It is also possible to set the same property as a System property.

Acquire a Connection Using V3 and Enabling Embedded Query processing:

Properties props = new Properties();

props.put("com.gs.embeddedQP.enabled", "true");

Connection conn = DriverManager.getConnection("jdbc:gigaspaces:v3://localhost:4174/mySpace", props);

JDBC v3 Configuration Extensions

For enhanced flexibility of query parsing and to allow quick migration from different SQL dialect, we provide the following extensions that need to be set as part of the context properties of the Space, or as System properties at the client side when running an embedded query processor. Note that using those extensions may have some performance impact, that can be avoided by modifying the original query to meet our native SQL dialect.

Change Inequality Operator

Change the default <> inequality operator to !=

Property:

com.gs.jdbc.v3.support.inequality = true

Support for semicolon delimiter

Remove ending semicolons (;) from queries. Note that multiple queries separated by a semicolon are not supported.

Property:

com.gs.jdbc.v3.support.semicolon_separator = true

Support for ROWNUM

Support the ROWNUM element to limit the number of rows returned by a query.

Property:

com.gs.jdbc.v3.support.rownum = true

Support for EXPLAIN

Change syntax of EXPLAIN SELECT to EXPLAIN PLAN FOR SELECT.

Property:

com.gs.jdbc.v3.support.explain_plan = true

Table Names

Table names are equivalent to the full class names of the data model in the Space. The class name should also be enclosed by quotation marks like in the following example:

SELECT * FROM “com.mycompany.model.MyData”

Query Analysis

Use EXPLAIN PLAN FOR SELECT … to give us a better understanding of why a specific query fails. The physical plan of the query is written to the log under the name com.gigaspaces.jdbc.v3 and will assist us in troubleshooting failures.

Explain Analyze For

It is recommended to use prepared statements and to check the analyzed output in order to verify the expected execution time.  When multiple joins are used, the optimization phase might take a long time. This is mitigated by using a prepared statement, which will ensure that the plan is only calculated for the first run and then cached.

com.gs.jdbc.v3.queryCache.expiration_time_minutes - How long to keep in cache

com.gs.queryCache.cacheSize - how many are cached

Simple Query Example

Properties props = new Properties();
props.put("com.gs.embeddedQP.enabled", "true");
try (Connection connection =
DriverManager.getConnection("jdbc:gigaspaces:v3:localhost:4174/mySpace", props)
{
  Statement statement = connection.createStatement();
  String query = "SELECT * FROM \"com.company.model.Coffee\" WHERE price < 100";
  ResultSet resultSet = statement.executeQuery(query);
  while (resultSet.next()) {
    String brand = resultSet.getString("brand");
    float price = resultSet.getFloat("price");
    System.out.println("brand: " + brand + " price: $" + price);
   }
}