com.vertica.jdbc.kv

Interface VGet

public interface VGet

A scalable key-based accessor for table data. The VGet directly queries Vertica nodes that have the data needed for the query, avoiding the distributed planning and execution costs associated with a normal execution. For workloads that access single rows based on a primary key, the VGet can be used to achieve lower latency and higher throughput than a traditional execution.

The VGet uses projection segmentation metadata and a set of equality predicates on table columns to determine an appropriate projection and node to send a query to. The addPredicate(String, Object) method is used to specify equality predicates on table columns. The VGet uses these predicates to evaluate the segmentation expression on the client to determine which node to query. At a minimum, the predicates columns passed must cover the segmentation expression of at least one of the table's underlying projections.

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 equality predicates passed via addPredicate(String, Object). To further customize the get operation, the addOutputColumn(String), addOutputExpression(String) addPredicateExpression(String), addSortColumn(String, SortOrder) and setLimit(int) may be used.

addOutputExpression(String) and addPredicateExpression(String) accept arbitrary SQL expressions that operate on the table's columns as input. Care must be taken to ensure that the columns exist on the table & projection used by the VGet, and that the expressions do not require distributed resources to execute. If an expression is sufficiently complex as to require more than one node to execute, execute() will throw a SQLException if the FailOnMultiNodePlans connection property is true.

Internally, VGet operations span multiple JDBC connections (and multiple Vertica sessions) and therefore 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.

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

Nested Class Summary

Nested Classes

Modifier and Type	Interface and Description
static class	VGet.SortOrder Describes the ordering of result data.

Method Summary

Methods

Modifier and Type	Method and Description
void	addOutputColumn(java.lang.String outputCol) Adds an output column to the ResultSet returned by execute().
void	addOutputExpression(java.lang.String outputExpr) Adds the result of a free-form SQL expression to the ResultSet returned by execute().
void	addPredicate(java.lang.String column, java.lang.Object value) Adds or updates a column equality predicate.
void	addPredicateExpression(java.lang.String expr) Adds a free-form SQL predicate to filter results.
void	addSortColumn(java.lang.String column, VGet.SortOrder order) Adds the specified column to the sort order of the result.
void	clearOutputs() Clears out all outputs added with addOutputColumn(String) and addOutputExpression(String).
void	clearPredicates() Clears out all predicates added with addPredicate(String, Object) and addPredicateExpression(String).
void	clearSortColumns() Clears out all columns added to the sort by addSortColumn(String, SortOrder)
void	close() Releases resources used by this VGet.
java.sql.ResultSet	execute() Fetches the desired results.
java.sql.SQLWarning	getWarnings() Retrieves the first warning reported by calls on this VGet.
void	setLimit(int limit) Imposes a limit on the number of rows returned by execute().

Method Detail

addPredicate

void addPredicate(java.lang.String column,
                java.lang.Object value)
                  throws java.sql.SQLException

Adds or updates a column equality predicate. Before execution, this method must be called for all columns of the segmentation key. Additional predicates on non-key columns are also allowed.

Parameters:

column - The name of a column.

value - The value of the column. Pass null to indicate a SQL NULL.

Throws:

java.sql.SQLException - If the column does not exist or the value is not compatible with the data type of the column.

addPredicateExpression

void addPredicateExpression(java.lang.String expr)
                            throws java.sql.SQLException

Adds a free-form SQL predicate to filter results. Predicates can be on any column in the table. Segmentation key columns may be used, but must also be specified with addPredicate(String, Object).

Currently the VGet does not check columns references in predicate expressions. Because they are not checked, the VGet can not ensure that they will exist on the projection chosen to answer the query. This can happen if a projection's column names do not match the anchor table's column names, or if the projection is not a superprojection. If this occurs, execute() will fail with a syntax error. To avoid this error, applications can override the VGet's projection choice by passing a projection name instead of a table name to VerticaRoutableConnection.prepareGet(String, String)

Parameters:

expr - A valid SQL expression acting on a column in the table. Incorrect syntax will cause execute() to fail.

Throws:

java.sql.SQLException - If this VGet is closed. In the future, may fail if the expression has invalid syntax.

clearPredicates

void clearPredicates()

Clears out all predicates added with addPredicate(String, Object) and addPredicateExpression(String).

addSortColumn

void addSortColumn(java.lang.String column,
                 VGet.SortOrder order)
                   throws java.sql.SQLException

Adds the specified column to the sort order of the result.

Parameters:

column - The name of a column in the table

order -

Throws:

java.sql.SQLException - If this VGet is closed, or if the column does not exist on this table

clearSortColumns

void clearSortColumns()

Clears out all columns added to the sort by addSortColumn(String, SortOrder)

addOutputColumn

void addOutputColumn(java.lang.String outputCol)
                     throws java.sql.SQLException

Adds an output column to the ResultSet returned by execute(). If no output columns or expressions are specified, the entire row is returned.

Parameters:

outputCol - A column from the table.

Throws:

java.sql.SQLException - If the column does not exist on this table.

addOutputExpression

void addOutputExpression(java.lang.String outputExpr)
                         throws java.sql.SQLException

Adds the result of a free-form SQL expression to the ResultSet returned by execute(). The expression can reference and perform computation on any columns in the table. If no output columns or expressions are specified, the entire row is returned.

Currently the VGet does not check columns references in output expressions. Because they are not checked, the VGet can not ensure that they will exist on the projection chosen to answer the query. This can happen if a projection's column names do not match the anchor table's column names, or if the projection is not a superprojection. If this occurs, execute() will fail with a syntax error. To avoid this error, applications can override the VGet's projection choice by passing a projection name instead of a table name to VerticaRoutableConnection.prepareGet(String, String)

Parameters:

outputExpr - A valid SQL expression. Incorrect syntax will cause execute() to fail.

Throws:

java.sql.SQLException - If this VGet is closed. In the future, may fail if the expression has invalid syntax.

clearOutputs

void clearOutputs()

Clears out all outputs added with addOutputColumn(String) and addOutputExpression(String).

setLimit

void setLimit(int limit)

Imposes a limit on the number of rows returned by execute(). The default value of zero indicates no limit.

Parameters:

limit - A maximum number of rows to return, or zero to indicate no limit.

execute

java.sql.ResultSet execute()
                           throws java.sql.SQLException

Fetches the desired results. Routes the request directly to the node containing the data. If the get operation requires a distributed execution (due to complex expressions specified by addOutputExpression(String) or addPredicateExpression(String)) and FailOnMultiNodePlans is set to true, this will throw a SQLException. If FailOnMultiNodePlans is false, a warning is generated and returned by getWarnings().

Returns:

A ResultSet iterator over the rows of data produced by the get operation.

Throws:

java.sql.SQLException - If not enough predicates were provided to determine the routing, or if a database error occurs.

close

void close()
           throws java.sql.SQLException

Releases resources used by this VGet.

Throws:

java.sql.SQLException

getWarnings

java.sql.SQLWarning getWarnings()
                                throws java.sql.SQLException

Retrieves the first warning reported by calls on this VGet. Subsequent warnings will be chained to the SQLWarning returned. The warning chain is cleared with each call to execute().

Returns:

The first SQLWarning, or null if there are no warnings

Throws:

java.sql.SQLException