Defining the query for routable queries using the VGet class

The VGet class 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 key/value-type lookups on single tables.

You create a VGet by calling the prepareGet method on a connection object:

prepareGet( schema-name, { table-name | projection-name } )

For example:

VerticaRoutableConnection conn;
try {
    conn = (VerticaRoutableConnection)
        jdbcSettings.getConnection();
        System.out.println("Connected.");
        VGet get = conn.prepareGet("public", "users");
              ...
}...

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.

VGet methods

VGet has the following methods:

• VGet methods
  • addPredicate
  • addPredicateExpression
  • addOutputColumn
  • addOutputExpression
  • addSortColumn
  • setLimit
  • clearPredicates
  • clearOutputs
  • clearSortColumns
  • Execute
  • Close
  • getWarnings
• Example

By default, VGet fetches all columns of all rows that satisfy the logical AND of predicates passed via the addPredicate method. You can further customize the get operation with the following methods: addOutputColumn, addOutputExpression, addPredicateExpression, addSortColumn, and setLimit.

addPredicate

addPredicate(string, object)

Adds a predicate column and a constant value to the query. You must include a predicate for each column on which the table is segmented. The predicate acts as the query WHERE clause. Multiple addPredicate method calls are joined by AND modifiers. The VGet retains this value after each call to execute. To remove it, use clearPredicates.

The following data types cannot be used as column values. Also, if a table is segmented on any columns with these data types then the table cannot be queried with the Routable Query API:

• INTERVAL

• TIMETZ

• TIMESTAMPTZ

addPredicateExpression

addPredicateExpression(string)

Accepts arbitrary SQL expressions that operate on the table's columns as input to the query. Predicate expressions and predicates are joined by AND modifiers. You can use segmented columns in predicate expressions, but they must also be specified as a regular predicate with addPredicate. The VGet retains this value after each call to execute. To remove it, use clearPredicates.

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

addOutputColumn

addOutputColumn(string)

Adds a column to be included in the output. By default the query runs as SELECT * and you do not need to define any output columns to return the data. If you add output columns then you must add all the columns to be returned. The VGet retains this value after each call to execute. To remove it, use clearOutputs.

addOutputExpression

addOutputExpression(string)

Accepts arbitrary SQL expressions that operate on the table's columns as output. The VGet retains this value after each call to execute. To remove it, use ClearOutputs.

The following restrictions apply:

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

• addOutputExpression is not supported when querying flex tables. If you use addOutputExpression on a flex table query, then a SQLFeatureNotSupportedException is thrown.

addSortColumn

addSortColumn(string, SortOrder)

Adds a sort order to an output column. The output column can be either the one returned by the default query (SELECT *) or one of the columns defined in addSortColumn or addOutputExpress. You can defined multiple sort columns.

setLimit

setLimit(int)

Sets a limit on the number of results returned. A limit of 0 is unlimited.

clearPredicates

clearPredicates()

Removes predicates that were added by addPredicate and addPredicateExpression.

clearOutputs

clearOutputs()

Removes outputs added by addOutputColumn and addOutputExpression.

clearSortColumns

clearSortColumns()

Removes sort columns previously added by addSortColumn.

Execute

execute()

Runs the query. Care must be taken to ensure that the predicate columns exist on the table and projection used by VGet, and that the expressions do not require multiple nodes to execute. If an expression is sufficiently complex as to require more than one node to execute, execute throws a SQLException if the FailOnMultiNodePlans connection property is true.

Close

close()

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

getWarnings

getWarnings()

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

Example

The following code queries the users table that is defined in Creating tables and projections for use with the routable query API. 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();
        }
    }
}

This code produces the following output:

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