Defining the query for routable queries using the VerticaRoutableExecutor class

Use the VerticaRoutableExecutor class to access table data directly from a single node. VerticaRoutableExecutor directly queries Vertica only on the node that has all the data needed for the query, avoiding the distributed planning and execution costs associated with Vertica query execution. You can use VerticaRoutableExecutor to join tables or use a GROUP BY clause, as these operations are not possible using VGet.

When using the VerticaRoutableExecutor class, the following rules apply:

• If joining tables, all tables being joined must be segmented (by hash) on the same set of columns referenced in the join predicate, unless the table to join is unsegmented.
• Multiple conditions in a join WHERE clause must be AND'd together. Using OR in the WHERE clause causes the query to degenerate to a multi-node plan. You can specify OR, IN list, or range conditions on columns outside the join condition if the data exists on the same node.
• You can only execute a single statement per request. Chained SQL statements are not permitted.
• Your query can be used in a driver-generated subquery to help determine whether the query can execute on a single node. Therefore, you cannot include the semi-colon at the end of the statement and you cannot include SQL comments using double-dashes (--), as these cause the driver-generated query to fail.

You create a VerticaRoutableExecutor by calling the createRoutableExecutor method on a connection object:

createRoutableExecutor( schema-name, table-name )

For example:

VerticaRoutableConnection conn;
        Map<String, Object> map = new HashMap<String, Object>();
        map.put("customer_key", 1);
try {
    conn = (VerticaRoutableConnection)
        jdbcSettings.getConnection();
     String table = "customers";
     VerticaRoutableExecutor q = conn.createRoutableExecutor(null, table);
     ...
}...

If schema-name is set to null, then the search path is used to find the table.

VerticaRoutableExecutor methods

VerticaRoutableExecutor has the following methods:

• execute

• close

• getWarnings

For details on this class, see the JDBC documentation.

Execute

execute( query-string, { column, value | map } )

Runs the query.

String column = "customer_key";
Integer value = 1;
ResultSet rs = q.execute(query, column, value)		

 Map<String, Object> map = new HashMap<String, Object>();
      map.put("customer_key", 1);
      map.put("another_key", 42);
      ResultSet rs = q.execute(query, map);

The following requirements apply:

• The query to execute must use regular SQL that complies with the rules of the VerticaRoutableExecutor class. For example, you can add limits and sorts, or use aggregate functions, provided the data exists on a single node.

• The JDBC client uses the column/value or map arguments to determine on which node to execute the query. The content of the query must use the same values that you provide in the column/value or map arguments.

• The following data types cannot be used as column values: * INTERVAL * TIMETZ * TIMESTAMPTZ

  Also, if a table is segmented on any columns with the following data types then the table cannot be queried with the routable query API:

The driver does not verify the syntax of the query before it sends the query to the server. If your expression is incorrect, then the query fails.

Close

close()

Closes this VerticaRoutableExecutor by releasing resources used by this VerticaRoutableExecutor. It does not close the parent JDBC connection to Vertica.

getWarnings

getWarnings()

Retrieves the first warning reported by calls on this VerticaRoutableExecutor. Additional warnings are chained and can be accessed with the JDBC method getNextWarning().

Example

The following example shows how to use VerticaRoutableExecutor to execute a query using both a JOIN clause and an aggregate function with a GROUP BY clause. The example also shows how to create a customer and sales table, and segment the tables so they can be joined using the VerticaRoutableExecutor class. This example uses the date_dimension table in the VMart schema to show how to join data on unsegmented tables.

1. Create the customers table to store customer details, and then create projections that are segmented on the table's customer_key column:

   
   => CREATE TABLE customers (customer_key INT, customer_name VARCHAR(128), customer_email VARCHAR(128));
   => CREATE PROJECTION cust_proj_b0 AS SELECT * FROM customers SEGMENTED BY HASH (customer_key) ALL NODES;
   => CREATE PROJECTION cust_proj_b1 AS SELECT * FROM customers SEGMENTED BY HASH (customer_key) ALL NODES OFFSET 1;
   => CREATE PROJECTION cust_proj_b2 AS SELECT * FROM customers SEGMENTED BY HASH (customer_key) ALL NODES OFFSET 2;
   => SELECT start_refresh();
   

2. Create the sales table, then create projections that are segmented on its customer_key column. Because the customer and sales tables are segmented on the same key, you can join them later with the VerticaRoutableExecutor routable query lookup.

   
   => CREATE TABLE sales (sale_key INT, customer_key INT, date_key INT, sales_amount FLOAT);
   => CREATE PROJECTION sales_proj_b0 AS SELECT * FROM sales SEGMENTED BY HASH (customer_key) ALL NODES;
   => CREATE PROJECTION sales_proj_b1 AS SELECT * FROM sales SEGMENTED BY HASH (customer_key) ALL NODES OFFSET 1;
   => CREATE PROJECTION sales_proj_b2 AS SELECT * FROM sales SEGMENTED BY HASH (customer_key) ALL NODES OFFSET 2;
   => SELECT start_refresh();
   

3. Add some sample data:

   => INSERT INTO customers VALUES (1, 'Fred', 'fred@example.com');
   => INSERT INTO customers VALUES (2, 'Sue', 'Sue@example.com');
   => INSERT INTO customers VALUES (3, 'Dave', 'Dave@example.com');
   => INSERT INTO customers VALUES (4, 'Ann', 'Ann@example.com');
   => INSERT INTO customers VALUES (5, 'Jamie', 'Jamie@example.com');
   => COMMIT;
   
   => INSERT INTO sales VALUES(1, 1, 1, '100.00');
   => INSERT INTO sales VALUES(2, 2, 2, '200.00');
   => INSERT INTO sales VALUES(3, 3, 3, '300.00');
   => INSERT INTO sales VALUES(4, 4, 4, '400.00');
   => INSERT INTO sales VALUES(5, 5, 5, '400.00');
   => INSERT INTO sales VALUES(6, 1, 15, '500.00');
   => INSERT INTO sales VALUES(7, 1, 15, '400.00');
   => INSERT INTO sales VALUES(8, 1, 35, '300.00');
   => INSERT INTO sales VALUES(9, 1, 35, '200.00');
   => COMMIT;
   

4. Create an unsegmented projection of the VMart date_dimension table for use in this example. Call the meta-function START_REFRESH to unsegment the existing data:

   => CREATE PROJECTION date_dim AS SELECT * FROM date_dimension UNSEGMENTED ALL NODES;
   => SELECT start_refresh();
   

Using the customer, sales, and date_dimension data, you can now create a routable query lookup that uses joins and a group by to query the customers table and return the total number of purchases per day for a given customer:

import java.sql.*;
import java.util.HashMap;
import java.util.Map;
import com.vertica.jdbc.kv.*;

public class verticaKV_doc {
    public static void main(String[] args) {
        com.vertica.jdbc.DataSource jdbcSettings
            = new com.vertica.jdbc.DataSource();
        jdbcSettings.setDatabase("VMart");
        jdbcSettings.setHost("vertica.example.com");
        jdbcSettings.setUserID("dbadmin");
        jdbcSettings.setPassword("password");
        jdbcSettings.setEnableRoutableQueries(true);
        jdbcSettings.setFailOnMultiNodePlans(true);
        jdbcSettings.setPort((short) 5433);
        VerticaRoutableConnection conn;
                Map<String, Object> map = new HashMap<String, Object>();
                map.put("customer_key", 1);
        try {
            conn = (VerticaRoutableConnection)
                jdbcSettings.getConnection();
            String table = "customers";
            VerticaRoutableExecutor q = conn.createRoutableExecutor(null, table);
            String query = "select d.date, SUM(s.sales_amount) as Total ";
                query += " from customers as c";
                query += " join sales as s ";
                query += " on s.customer_key = c.customer_key ";
                query += " join date_dimension as d ";
                query += " on d.date_key = s.date_key ";
                query += " where c.customer_key = " + map.get("customer_key");
                query += " group by (d.date) order by Total DESC";
            ResultSet rs = q.execute(query, map);
            while(rs.next()) {
                System.out.print("Date: " + rs.getString("date") + ":  ");
                System.out.println("Amount: " + rs.getString("Total"));
            }
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

The example code produces output like this:

Date: 2012-01-15:  Amount: 900.0
Date: 2012-02-04:  Amount: 500.0
Date: 2012-01-01:  Amount: 100.0