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 SpaceClosed 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 JDBCClosed 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.

BIClosed 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 ODBCClosed 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.

Click on Deploy Data Gateway Service in Ops Manager to deploy the service:


  • Service name — the name of the new service that will be introduced to the grid
  • Number of instances — the number of instances for the service
  • Bind Port — the port which the gateway server will bind to in the host. The user should allocated a port, typically 5432, and bind it to a routable IP address, in case there are several IPs for a specific host.
  • Advanced — Context Properties — Name and value pairs for additional properties. Click on + to add a property and value, delete to remove.
    The defined properties and their values are used as part of the deploy process for various configuration purposes.

Command example:

gs data-gateway deploy --instances=3 --properties=file://config/myfile.properties -p kl=v1 k2=v2 myService


For the service myService, whose properties file is located at //config/myfile.properties, deploy the service with 3 instances. Use the context properties of k1=v1 and k2=v2.


For the service myService, whose properties file is located at //config/myfile.properties, deploy the service with 3 instances. Use the context properties of k1=v1 and k2=v2.

Example Request

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'


Option Description Sample Data Required
name The name of the data gateway service that you wish to deploy myService Yes
instances The number of instances to deploy 3 No
contextProperties The location of the properties file, and optional additional context properties file://config/myfile.properties kl=v1 k2=v2 No

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.

It is recommended to use embedded QP true. This will mean that the cache for the execution plan will be saved in the client side and required joins will be performed there. It is important to allocate sufficient resources to enable this.

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.

For additional configuration details, refer to the CalciteDefaults class in Javadocs.

Change Inequality Operator

Change the default <> inequality operator to !=


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.


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.


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

Support for EXPLAIN



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);