https://pinot.apache.org/ logo
Join Slack
Powered by
Hey team, looking for some suggestions. We have a ...
# general
d
Hey team, looking for some suggestions. We have a use case with both offline (around 3 years) and realtime data amounting to a total of around 8 billion records. The queries we are looking to handle will include CTE's, joins, counts, avg and sum grouped on timestamps (day granularity for offline) and/or product type. • do we pre-compute counts/sum before ingesting or configure in table config/schema to do that by building startree indexes ? Also for different combinations of queries (counts/sums and different group by's) do we need multiple configs defining each combination or all of them in one config is enough to address different combinations used in query ? • understand that moving the realtime data to offline is a good • also understand it would need v2 engine to handle CTE's and joins. Currently, the following query itself is timing out with startree index configured -
Copy code
select name, count(name) from table group by 1
we have around 2 million distinct names. Thanks
Copy code
{
  "OFFLINE": {
    "tableName": "table_OFFLINE",
    "tableType": "OFFLINE",
    "segmentsConfig": {
      "schemaName": "table",
      "replication": "1",
      "replicasPerPartition": "1",
      "minimizeDataMovement": false,
      "segmentPushType": "APPEND",
      "timeColumnName": "timestamp_ms",
      "segmentPushFrequency": "HOURLY"
    },
    "tenants": {
      "broker": "DefaultTenant",
      "server": "DefaultTenant"
    },
    "tableIndexConfig": {
      "invertedIndexColumns": [],
      "noDictionaryColumns": [],
      "onHeapDictionaryColumns": [],
      "varLengthDictionaryColumns": [],
      "enableDefaultStarTree": false,
      "starTreeIndexConfigs": [
        {
          "dimensionsSplitOrder": [
            "name"
          ],
          "skipStarNodeCreationForDimensions": [],
          "functionColumnPairs": [
            "COUNT__name"
          ],
          "maxLeafRecords": 10000
        }
      ],
      "enableDynamicStarTreeCreation": true,
      "aggregateMetrics": true,
      "nullHandlingEnabled": false,
      "optimizeDictionary": true,
      "optimizeDictionaryForMetrics": true,
      "noDictionarySizeRatioThreshold": 0.85,
      "rangeIndexColumns": [],
      "rangeIndexVersion": 2,
      "autoGeneratedInvertedIndex": false,
      "createInvertedIndexDuringSegmentGeneration": false,
      "sortedColumn": [],
      "bloomFilterColumns": [],
      "loadMode": "MMAP"
    },
    "metadata": {},
    "quota": {},
    "routing": {},
    "query": {},
    "fieldConfigList": [
      {
        "name": "name",
        "encodingType": "RAW",
        "indexType": "TEXT",
        "indexTypes": [
          "TEXT"
        ],
        "indexes": null,
        "tierOverwrites": null
      },
      {
        "name": "timestamp_ms",
        "encodingType": "DICTIONARY",
        "indexType": "TIMESTAMP",
        "indexTypes": [
          "TIMESTAMP"
        ],
        "timestampConfig": {
          "granularities": [
            "DAY",
            "WEEK"
          ]
        },
        "indexes": null,
        "tierOverwrites": null
      }
    ],
    "ingestionConfig": {
      "transformConfigs": [
        {
          "columnName": "timestamp_ms",
          "transformFunction": "ToDateTime(\"timestamp_unix\", 'yyyy-MM-dd hh:mm:ss')"
        }
      ],
      "continueOnError": false,
      "rowTimeValueCheck": false,
      "segmentTimeValueCheck": true
    },
    "isDimTable": false
  }
}
m
1. The query you mentioned will be greatly helped if you use StarTree index with HLL approximation. If you cannot us approximation, there’s partitioned version of count distinct (that requires data to be partitioned. If that doesn’t work either, will have to look at the setup (in terms of resources allocated). 2. If you don’t plan to use upserts, you can move data to offline. 3. V2 is needed to handle joins/CTEs cc: @Rong R
Open in Slack
PreviousNext
Powered by