Defining the Query for Routable Queries Using the VGet Class

The VGet class (details on the class are available in the JDBC Documentation) is used to access table data directly from a single node when you do not need to join the data or use a group by clause. Like VerticaRoutableExecutor, VGet directly queries Vertica nodes that have the data needed for the query, avoiding the distributed planning and execution costs associated with a normal Vertica execution. However, VGet does not use SQL. Instead, you define predicates and values to perform Key/Value type lookups on a single table. VGet is especially suited to doing key/value-type lookups on single tables.

You create a VGet by calling prepareGet(schema, table/proj) on a connection object. prepareGet() takes the name of the schema and the name of a table or projection as arguments.

VGet Methods

VGet has the following methods:

You call the execute() method to run query. By default, the VGet fetches all the columns of all the rows that satisfy the logical AND of all the predicates passed via the addPredicate() method. To further customize the get operation use the addOutputColumn(), addOutputExpression(), addPredicateExpression(), addSortColumn() and setLimit() methods.

Note: VGet operations span multiple JDBC connections (and multiple Vertica sessions) and do not honor the parent connection's transaction semantics. If consistency is required across multiple executions, the parent VerticaRoutableConnection's consistent read API can be used to guarantee all operations occur at the same epoch.

VGet is thread safe, but all methods are synchronized, so threads that share a VGet instance are never run in parallel. For better parallelism, each thread should have its own VGet instance. Different VGet instances that operate on the same table share pooled connections and metadata in a manner that enables a high degree of parallelism.

Example

You can query the table defined in Creating Tables and Projections for use with the Routable Query API with the following example code. The table defines an id column that is segmented by hash.

import java.sql.*;
import com.vertica.jdbc.kv.*;

public class verticaKV2 {
	public static void main(String[] args) {
		com.vertica.jdbc.DataSource jdbcSettings 
			= new com.vertica.jdbc.DataSource();
		jdbcSettings.setDatabase("exampleDB");
		jdbcSettings.setHost("v_vmart_node0001.example.com");
		jdbcSettings.setUserID("dbadmin");
		jdbcSettings.setPassword("password");
		jdbcSettings.setEnableRoutableQueries(true);
		jdbcSettings.setPort((short) 5433);
		
		VerticaRoutableConnection conn;
		try {
			conn = (VerticaRoutableConnection) 
				jdbcSettings.getConnection();
		    System.out.println("Connected.");
			VGet get = conn.prepareGet("public", "users");
			get.addPredicate("id", 5);
			ResultSet rs = get.execute();
			rs.next();
			System.out.println("ID: " + 
				rs.getString("id"));
			System.out.println("Username: " 
				+ rs.getString("username"));
			System.out.println("Email: " 
				+ rs.getString("email"));
			System.out.println("Closing Connection.");
		    conn.close();
		} catch (SQLException e) {
			System.out.println("Error! Stacktrace:");
		    e.printStackTrace();
		}
	}
}

The output:

Connected.
ID: 5
Username: userE
Email: usere@example.com
Closing Connection.