Connecting via Python and Java to StarTree Cloud

Python

Applications can use this Python client library to query StarTree Cloud.

Prerequisite

Ensure you have the latest pinotdb library installed. To install the library, run the following command in your terminal:

pip install pinotdb

Usage

Query the broker directly using the REST API

from pinotdb import connect

conn = connect(host='localhost', port=8099, path='/query/sql', scheme='http')
curs = conn.cursor()
curs.execute("""
    SELECT place,
           CAST(REGEXP_EXTRACT(place, '(.*),', 1) AS FLOAT) AS lat,
           CAST(REGEXP_EXTRACT(place, ',(.*)', 1) AS FLOAT) AS lon
      FROM places
     LIMIT 10
""")
for row in curs:
    print(row)

Use SQLAlchemy to query Pinot

The db engine connection string is formatted like this: pinot://:?controller=://:/

from sqlalchemy import *
from sqlalchemy.engine import create_engine
from sqlalchemy.schema import *

engine = create_engine('pinot://localhost:8099/query/sql?controller=http://localhost:9000/')  # uses HTTP by default
# engine = create_engine('pinot+http://localhost:8099/query/sql?controller=http://localhost:9000/')
# engine = create_engine('pinot+https://localhost:8099/query/sql?controller=http://localhost:9000/')

places = Table('places', MetaData(bind=engine), autoload=True)
print(select([func.count('*')], from_obj=places).scalar())

Connect to StarTree Cloud to query Pinot

from pinotdb import connect

conn = connect(host='broker.pinot.dev.[companyname]saas.startree.cloud', port=443, path='/query/sql', username='xxxx', password='xxxx=',scheme='https') 
curs = conn.cursor()
curs.execute("""
    select * from website limit 10
""")
for row in curs:
    print(row)

Clone the Python pinot-dbapi repository

git clone git@github.com:python-pinot-dbapi/pinot-dbapi.git
cd pinot-dbapi

Java

Pinot provides a native java client to execute queries on the cluster. The client is tenant-aware, and able to direct queries to the correct broker.

Prerequisite

You can use the client by installing and including Maven or Gradle.

For information on how to install Maven, visit Installing Apache Maven (opens in a new tab).

Example of how to including the Maven dependency to use the client:

<dependency>
    <groupId>org.apache.pinot</groupId>
    <artifactId>pinot-java-client</artifactId>
    <version>1.0.0</version>
</dependency>

For information on how to install Gradle, visit Gradle Build Tool Installation (opens in a new tab).

Example of how to including the Gradle dependency to use the client:

include 'org.apache.pinot:pinot-java-client:0.5.0'

Usage

Here's an example of how to use the pinot-java-client to query Pinot.

import org.apache.pinot.client.Connection;
import org.apache.pinot.client.ConnectionFactory;
import org.apache.pinot.client.Request;
import org.apache.pinot.client.ResultSetGroup;
import org.apache.pinot.client.ResultSet;

/**
 * Demonstrates the use of the pinot-client to query Pinot from Java
 */
public class PinotClientExample {

  public static void main(String[] args) {

    // pinot connection
    String zkUrl = "localhost:2181";
    String pinotClusterName = "PinotCluster";
    Connection pinotConnection = ConnectionFactory.fromZookeeper(zkUrl + "/" + pinotClusterName);

    String query = "SELECT COUNT(*) FROM myTable GROUP BY foo";

    // set queryType=sql for querying the sql endpoint
    Request pinotClientRequest = new Request("sql", query);
    ResultSetGroup pinotResultSetGroup = pinotConnection.execute(pinotClientRequest);
    ResultSet resultTableResultSet = pinotResultSetGroup.getResultSet(0);

    int numRows = resultTableResultSet.getRowCount();
    int numColumns = resultTableResultSet.getColumnCount();
    String columnValue = resultTableResultSet.getString(0, 1);
    String columnName = resultTableResultSet.getColumnName(1);

    System.out.println("ColumnName: " + columnName + ", ColumnValue: " + columnValue);
  }
}

Connection Factory

The client provides a ConnectionFactory class to create connections to a Pinot cluster. To create a connection, include a comma-separated list of the brokers in the cluster. For example, ("broker-1:1234", "broker-2:1234", ...);

Connection connection = ConnectionFactory.fromZookeeper
  ("some-zookeeper-server:2191/zookeeperPath");
Connection connection = ConnectionFactory.fromProperties("demo.properties");
Connection connection = ConnectionFactory.fromHostList

Query methods

Query using one of the following methods:

For blocking queries, use the following request format: Connection.execute(org.apache.pinot.client.Request)
For asynchronous queries that return a future object, use the following request format: Connection.executeAsync(org.apache.pinot.client.Request)

ResultSetGroup resultSetGroup = 
  connection.execute(new Request("sql", "select * from foo..."));
// OR
Future<ResultSetGroup> futureResultSetGroup = 
  connection.executeAsync(new Request("sql", "select * from foo..."));

To escape query parameters, you can use the PreparedStatement (which isn't stored, so won't decrease the subsequent query performance).

PreparedStatement statement = 
    connection.prepareStatement(new Request("sql", "select * from foo where a = ?"));
statement.setString(1, "bar");
 
ResultSetGroup resultSetGroup = statement.execute();
// OR
Future<ResultSetGroup> futureResultSetGroup = statement.executeAsync();

Result set

Access results using various get methods on the first ResultSet with the getResultSet(int) method.

Request request = new Request("sql", "select foo, bar from baz where quux = 'quuux'");
ResultSetGroup resultSetGroup = connection.execute(request);
ResultSet resultSet = resultSetGroup.getResultSet(0);
 
for (int i = 0; i < resultSet.getRowCount(); ++i) {
  System.out.println("foo: " + resultSet.getString(i, 0));
  System.out.println("bar: " + resultSet.getInt(i, 1));
}

Authentication

Pinot supports basic HTTP authorization (opens in a new tab), which can be enabled for your cluster using configuration. To support basic HTTP authorization in your client-side Java applications, make sure you are using Pinot Java Client 0.10.0+ or building from the latest Pinot snapshot. The following code snippet shows you how to connect to and query a Pinot cluster that has basic HTTP authorization enabled when using the Java client.

final String username = "admin";
final String password = "verysecret";
 
// Concatenate username and password and use base64 to encode the concatenated string
String plainCredentials = username + ":" + password;
String base64Credentials = new String(
    Base64.getEncoder().encode(plainCredentials.getBytes()));
 
String authorizationHeader = "Basic " + base64Credentials;
 
Map<String, String> headers = new HashMap();
headers.put("Authorization", authorizationHeader);
JsonAsyncHttpPinotClientTransportFactory factory = 
    new JsonAsyncHttpPinotClientTransportFactory();
factory.setHeaders(headers);
PinotClientTransport clientTransport = factory
    .buildTransport();
 
Connection connection = ConnectionFactory.fromProperties(
        Collections.singletonList("localhost:8000"), clientTransport);
String query = "select count(*) FROM baseballStats limit 1";
 
ResultSetGroup rs = connection.execute(query);
System.out.println(rs);
connection.close();

Configuring client time-out

The following timeouts can be set:

brokerConnectTimeoutMs (default 2000)
brokerReadTimeoutMs (default 60000)
brokerHandshakeTimeoutMs (default 2000)
controllerConnectTimeoutMs (default 2000)
controllerReadTimeoutMs (default 60000)
controllerHandshakeTimeoutMs (default 2000)

Timeouts for the Java connector can be added as a connection properties. The following example configures a very low timeout of 10ms:

Properties connectionProperties = new Properties();
connectionProperties.setProperty("controllerReadTimeoutMs", "10");
connectionProperties.setProperty("controllerHandshakeTimeoutMs", "10");
connectionProperties.setProperty("controllerConnectTimeoutMs", "10");
connectionProperties.setProperty("brokerReadTimeoutMs", "10");
connectionProperties.setProperty("brokerHandshakeTimeoutMs", "10");
connectionProperties.setProperty("brokerConnectTimeoutMs", "10");
 
// Register new Pinot JDBC driver
DriverManager.registerDriver(new PinotDriver());
 
// Get a client connection and set the connection timeouts
Connection connection = DriverManager.getConnection(DB_URL, connectionProperties);
 
// Test that your query successfully times out
Statement statement = connection.createStatement();
ResultSet rs = statement.executeQuery("SELECT count(*) FROM baseballStats LIMIT 1;");
 
while (rs.next()) {
    String result = rs.getString("count(*)");
    System.out.println(result);
}

Query using the REST API Visualize data