Pinot offers standard JDBC interface to query the database. This makes it easier to integrate Pinot with other applications such as Tableau.
You can include the JDBC dependency in your code as follows -
You can also compile the JDBC code into a JAR and place the JAR in the Drivers directory of your application.
There is no need to register the driver manually as it will automatically register itself at the startup of the application.
Here's an example of how to use the pinot-jdbc-client
for querying. The client only requires the controller URL.
You can also use PreparedStatements. The placeholder parameters are represented using ?
** (question mark) symbol.
The JDBC client doesn't support INSERT
, DELETE
or UPDATE
statements due to the database limitations. You can only use the client to query the database.
The driver is also not completely ANSI SQL 92 compliant.
If you want to use JDBC driver to integrate Pinot with other applications, do make sure to check JDBC ConnectionMetadata in your code. This will help in determining which features cannot be supported by Pinot since it is an OLAP database.
Pinot Client for Golang
Pinot also provides a native go client to query database directly from go application.
Please follow this Pinot Quickstart link to install and start Pinot batch QuickStart locally.
Check out Client library Github Repo
Build and run the example application to query from Pinot Batch Quickstart
Pinot client could be initialized through:
Please see this example for your reference.
Code snippet:
Query Response is defined as the struct of following:
Note that AggregationResults
and SelectionResults
are holders for PQL queries.
Meanwhile ResultTable
is the holder for SQL queries. ResultTable
is defined as:
RespSchema
is defined as:
There are multiple functions defined for ResultTable
, like:
Sample Usage is here
A lot of times the user wants to query data from an external application instead of using the inbuilt query explorer. Pinot provides external query client for this purpose. All of the clients have pretty standard interfaces so that the learning curve is minimum.
Currently Pinot provides the following clients
Pinot provides a native java client to execute queries on the cluster. The client makes it easier for user to query data. The client is also tenant-aware and thus is able to redirect the queries to the correct broker.
You can use the client by including the following dependency -
You can also build locally and use it.
Here's an example of how to use the pinot-java-client
to query Pinot.
The client provides a ConnectionFactory
class to create connections to a Pinot cluster. The factory supports the following methods to create a connection -
Zookeeper (Recommended) - Comma seperated list of zookeeper of the cluster. This is the recommended method which can redirect queries to appropriate brokers based on tenant/table.
Broker list - Comma seperated list of the brokers in the cluster. This should only be used in standalone setups or for POC, unless you have a load balancer setup for brokers.
Properties file - You can also put the broker list as brokerList
in a properties file and provide the path to that file to the factory. This should only be used in standalone setups or for POC, unless you have a load balancer setup for brokers.
Here's an example demonstrating all methods of Connection factory -
You can run the query in both blocking as well as async manner. Use
Connection.execute(org.apache.pinot.client.Request)
for blocking queries
Connection.executeAsync(org.apache.pinot.client.Request)
for asynchronous queries that return a future object.
You can also use PreparedStatement
to escape query parameters. We don't store the Prepared Statement in the database and hence it won't increase the subsequent query performance.
Results can be obtained with the various get methods in the first ResultSet, obtained through the getResultSet(int)
method:
If queryFormat pql
is used in the Request
, there are some differences in how the results can be accessed, depending on the query.
In the case of aggregation, each aggregation function is within its own ResultSet. A query with multiple aggregation function will return one result set per aggregation function, as they are computed in parallel.
In case of aggregation with GROUP BY
, there will be as many ResultSets as the number of aggregations, each of which will contain multiple results grouped by a grouping key.
This section is only applicable for PQL endpoint, which is deprecated and will be deleted soon. For more information about the endpoints, visit .
Applications can use this python client library to query Apache Pinot.
Pypi Repo: https://pypi.org/project/pinotdb/
Source Code Repo: https://github.com/python-pinot-dbapi/pinot-dbapi
Please note:
pinotdb version >= 0.3.2 is using Pinot SQL API (added in Pinot >= 0.3.0) and drops support for PQL API. So this client requires Pinot server version >= 0.3.0 in order to access Pinot.
pinotdb version in 0.2.x is using Pinot PQL API, which works with pinot version <= 0.3.0, but may miss some new SQL query features added in newer Pinot version.
The db engine connection string is format as: pinot://:?controller=://:/
Run below command to start Pinot Batch Quickstart in docker and expose Pinot controller port 9000 and Pinot broker port 8000.
Once pinot batch quickstart is up, you can run below sample code snippet to query Pinot:
Sample Output:
Run below command to start Pinot Hybrid Quickstart in docker and expose Pinot controller port 9000 and Pinot broker port 8000.
Below is an example to query against Pinot Quickstart Hybrid: