Defining the Query for Routable Queries using the VerticaRoutableExecutor Class

The VerticaRoutableExecutor class is used 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 a normal Vertica execution. You can use VerticaRoutableExecutor if you need to join tables or use a group by clause, as these operations are not possible using VGet.

When using the VerticaRoutableExecutor class, you must follow these rules:

You create a VerticaRoutableExecutor by calling createRoutableExecutor(schema, table); on a connection object. If schema is set to null, then the search path is used to find the table.

VerticaRoutableExecutor Methods

VerticaRoutableExecutor has the following methods (more details on the class are available in the JDBC Documentation):

Example Query Using VerticaRoutableExecutor

The following example details 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 details how to create both a customer and a sales table, and how to segment the tables so they can be joined using the VerticaRoutableExecutor class. This example also uses the date_dimension table from the VMart schema to illustrate how you can also join data on unsegmented tables.

  1. Create a table for customer details, and then create the projections which segment on the customer_key.

    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 a sales table, then create the projections which segment on the customer_key. Since both the customer and sales tables are segmented on the same key, you can join them 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. Note you must run SELECT start_refresh(); to unsegment the existing data:

    => CREATE PROJECTION date_dim_unsegment 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 outputs:

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

Note that your dates may be different, because the VMart schema randomly generates the dates in the date_dimension table.