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:
-
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 "WHERE" clause to the query. MultipleaddPredicate()
method calls are joined by AND modifiers. Note that the VGet retains this value after each call to execute. To remove it, useClearPredicates()
.Note: The following data types cannot be used as predicates. Additionally, if a table is segmented on any columns with the following data types then the table cannot be queried using the Routable Query API:
- interval
- timetz
- timsestamptz
-
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 withaddPredicate()
. Note that the VGet retains this value after each call to execute. To remove it, useClearPredicates()
.Note: 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(string)
- Adds a column to be included in the output. By default the query runs asSELECT *
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 you want returned. Note that the VGet retains this value after each call to execute. To remove it, useClearOutputs()
.-
addOutputExpression(string)
- Accepts arbitrary SQL expressions that operate on the table's columns as output. Note that the VGet retains this value after each call to execute. To remove it, useClearOutputs()
.Note: 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.
Note:
addOutputExpression()
is not supported when querying Flex Tables. If you attempt to useaddOutputExpression()
on a Flex Table query, then a SQLFeatureNotSupportedException is thrown. 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 addOutputColumn or addOutputExpress. You can defined multiple sort columns.setLimit(int)
- Sets a limit on the number of results returned. A limit of 0 is unlimited.clearPredicates()
- Removes predicates that were added byaddPredicate()
andaddPredicateExpression()
.clearOutputs()
- Removes outputs added byaddOutput()
andaddOutputExpression()
.clearSortColumns()
- Removes sort columns previously added byaddSortColumn()
.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()
- Closes this VGet by releasing resources used by this VGet. It does not close the parent JDBC connection to Vertica.getWarnings()
- Retrieves the first warning reported by calls on this VGet. Additional warnings are chained and can be accessed with the JDBCgetNextWarning()
method.
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.