7.17. Pinot Connector

    To configure the Pinot connector, create a catalog properties file in named, for example, pinot.properties, to mount the Pinot connector as the pinot catalog. Create the file with the following contents, replacing the connection properties as appropriate for your setup:

    Where the pinot.controller-urls property allows you to specify a comma separated list of the pinot controller host/port pairs.

    You can have as many catalogs as you need, so if you have additional Pinot clusters, simply add another properties file to etc/catalog with a different name (making sure it ends in .properties). For example, if you name the property file sales.properties, Presto will create a catalog named sales using the configured connector.

    In general Pinot schema to Presto schema mapping are pretty straight forward. By default, the data type mapping follows the table below.

    Since Pinot defines each field as dimension, metric or time(date_time) field, it’s possible to infer Presto data type DATE and TIMESTAMP:

    • A Pinot TIME field with timeGranularity { "TimeFormat":"EPOCH", "TimeUnit":"DAYS", "TimeUnitSize": 1 } could be map to a DATE type.
    • A Pinot TIME field with timeGranularity { "TimeFormat":"EPOCH", "TimeUnit":"MILLISECONDS", "TimeUnitSize": 1 } could be map to a TIMESTAMP type.
    • A Pinot DATE_TIME field with format 1:DAYS:EPOCH could be map to a DATE type.
    • A Pinot DATE_TIME field with format 1:MILLISECONDS:EPOCH could be map to a TIMESTAMP type.
    • pinot.infer-date-type-in-schema: This config is false by default. Setting it to true will infer a Pinot TIME/DATE_TIME field to DATE in Presto if possible.
    • pinot.infer-timestamp-type-in-schema: This config is false by default. Setting it to true will infer a Pinot TIME/DATE_TIME field to TIMESTAMP in Presto if possible.

    Below is an example with config: pinot.infer-timestamp-type-in-schema=true.

    Sample Pinot Schema:

    1. {
    3.   "dimensionFieldSpecs": [
    4.     {
    5.       "name": "venue_name",
    6.       "dataType": "STRING"
    7.     },
    8.     {
    9.       "name": "event_name",
    10.       "dataType": "STRING"
    11.     {
    12.       "name": "event_id",
    13.       "dataType": "STRING"
    14.     },
    15.     {
    16.       "name": "event_time",
    17.       "dataType": "LONG"
    18.     },
    19.     {
    20.       "name": "group_city",
    21.       "dataType": "STRING"
    22.     },
    23.     {
    24.       "name": "group_country",
    25.       "dataType": "STRING"
    26.     },
    27.     {
    28.       "name": "group_id",
    29.       "dataType": "LONG"
    30.     },
    31.     {
    33.     }
    34.   ],
    35.   "metricFieldSpecs": [
    36.     {
    37.       "name": "rsvp_count",
    38.       "dataType": "INT"
    39.     }
    40.   ],
    41.   "timeFieldSpec": {
    42.     "incomingGranularitySpec": {
    43.       "name": "mtime",
    44.       "dataType": "LONG",
    45.       "timeType": "MILLISECONDS"
    46.     }
    47.   }
    48. }

    Sample Presto Schema:

    The Pinot catalog exposes all pinot tables inside a flat schema. The schema name is immaterial when querying but running SHOW SCHEMAS, will show just one schema entry of default.

    The name of the pinot catalog is the catalog file you created above without the .properties extension.

    1. SHOW TABLES from mypinotcluster.default

    OR:

    Both of these commands will list all the tables in your pinot cluster. This is because Pinot does not have a notion of schemas.

    Consider you have a table called clicks in the mypinotcluster. You can see a list of the columns in the clicks table using either of the following:

    1. DESCRIBE mypinotcluster.dontcare.clicks;
    2. SHOW COLUMNS FROM mypinotcluster.dontcare.clicks;

    Finally, you can access the clicks table:

    The connector tries to push the maximal subquery inferred from the presto query into pinot. It can push down everything Pinot supports including aggregations, group by, all UDFs etc. It generates the correct Pinot PQL keeping Pinot’s quirks in mind.

    There are a few configurations that control this behavior:

    • pinot.prefer-broker-queries: This config is true by default. Setting it to false will also create parallel plans for aggregation and limit queries.
    • pinot.non-aggregate-limit-for-broker-queries: To prevent overwhelming the broker, the connector only allows querying the pinot broker for short queries. We define a query to be either an aggregation (or group-by) query or a query with a limit less than the value configured for pinot.non-aggregate-limit-for-broker-queries. The default value for this limit is 25K rows.