SQL Queries

We have an embedded SQL engine for parsing, validating, optimizing and executing SQL queries on the data stored in a Space. 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 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 tools like Tableau, PowerBI, MicroStrategy and others can integrate with GigaSpaces just by using their built-in Postgres JDBC or ODBC 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
  • Image repository and Image tag — Identifying information to access the external image file
  • 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.
  • Load Balancer and Load Balancer Port (Kubernetes only) — Externally available IP address for the service
  • 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

Description:

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.

Description

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'

Options:

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.

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/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.

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