Using JDBC Routable Query API for High Concurrency and High Throughput

Does your organization use high-volume short requests that return a small number of results on a single node? Do you deal with low latency and high concurrency queries, and want to achieve high throughput? You could benefit by using the Vertica JDBC Routable Query API that uses fewer system resources than distributed queries.

What is the Routable Query API?

The Routable Query API (formerly known as the Key Value API) uses the JDBC driver. The API can route the queries directly to a specific node containing the data for high-volume queries. Vertica with the Routable Query API reduces the overhead of distributed planning.

What is new in this API?

The Routable Query API, effective in Vertica 7.1 SP1, introduces the VerticaRoutableExecutor interface. This interface allows you to use traditional SQL to query a single node. It supports table joins and the GROUP BY clause.

How does the traditional analytical query work?

With the traditional analytic queries, the client sends the query to an arbitrary initiator. The initiator sends the query plan to all the nodes. In this process, all the nodes participate in the execution and send back the results to the initiator.

How does the Routable Query API work?

With the Routable Query API, the client chooses an initiator that has the data for the query. The initiator sends the query to only one node and sends back the data to the client without involving any other node.

Why segment the data?

Segmenting the data helps the JDBC client determine the node where the data resides. You must segment the data by at least one or more columns. This segmentation by hash allows the client to connect directly to a specific node and fulfill requests.

How do I use the Routable Query API?

To be able to use the Routable Query API, you need to do the following:

• Create tables and projections for use with the API
• Create a connection for the API
• Create a query for the API

Create tables and projections

For the routable queries, the client needs to determine the appropriate node to fetch the data. To do so, the client compares all the projections available and determines the best projection to find the single node that contains the data.

Create a connection

To interact with Vertica, the Java application must create a connection. The Routable Query API provides the VerticaRoutableConnection interface with advanced routing capabilities to connect to a cluster.

Create a query

To perform a routable query, the VerticaRoutableConnection interface must provide access to the VerticaRoutableExecutor interface and the VGet interface. Both the interfaces directly query the Vertica nodes with data avoiding the distributed planning and the execution costs associated with a normal Vertica execution.

The VerticaRoutableExecutor interface provides a powerful SQL-based API that adds flexibility to the API. The VGet interface provides a more structured API for programmatic access.

What will I use this for?

That?’s up to you! You can apply the JDBC Routable Query API to an endless amount of organizational processes, all while knowing that you are using fewer system resources than if you were running distributed queries.

For More Information?

• About the Routable Query Best Practices, see JDBC Routable Query API: Best Practices in the Vertica Knowledge Base.
• About Routable Query API, see Routable Query API in the Vertica documentation.