#general
@hui: @hui has joined the channel
@wcxzjtz: just quick question: does pinot support other partition logics other than time based partition?
@jadami: yes,
@wcxzjtz: thanks .
@laxman: quick question: From which pinot version star-tree index GA is available (production ready)?
@g.kishore: star-tree index has been GA since a really long time. is there any doc where you found that its not ready?
@laxman: nope Kishore. We are using 0.7.1 and planning to enable star tree index. So, just checking if this feature is production ready in this version 0.7.1
@laxman: If any major changes, we can upgrade pinot before using this. Have seen some major discussions/jiras in later versions. Hence, just confirming.
@g.kishore: ah, got it. I think there were few enhancements that allowed dynamic creation and dropping of star tree index
@mayanks: BTW, 0.7.1 is quite old, the latest one 0.10.0 was already out a little while ago. You may want to upgrade not for star-tree but for other major features.
@kingkenway16: @kingkenway16 has joined the channel
@rsohlot: @rsohlot has joined the channel
@vishnu: @vishnu has joined the channel
@andre578: Hi folks, is it possible to change the primary time column on existing (offline) tables without reloading segments?
@g.kishore: I don’t think so. the segment metadata contains the min max of the time column.. this is used for retention - to decide when to delete a segment
@mayanks: Yea, this would be considered backward incompatible schema change, and hence not allowed
@andre578: Got it, thanks!
@visar: @visar has joined the channel
@diogo.baeder: Hey folks, I know I said this before, but what a fantastic piece of software Apache Pinot is! I keep finding more and more interesting things about it, smart decisions, great OS support...
#random
@hui: @hui has joined the channel
@kingkenway16: @kingkenway16 has joined the channel
@rsohlot: @rsohlot has joined the channel
@vishnu: @vishnu has joined the channel
@visar: @visar has joined the channel
#troubleshooting
@hui: @hui has joined the channel
@kingkenway16: @kingkenway16 has joined the channel
@rsohlot: @rsohlot has joined the channel
@vishnu: @vishnu has joined the channel
@visar: @visar has joined the channel
@raluca.lazar: hi all, I need to dynamically enable/disable the `peerSegmentDownloadScheme` on a realtime table. If I do this manually by either adding this line or removing it on the table config, it works, but doing it via an environment variable does not (passing an empty env var vs passing this string to be replaced: `, "peerSegmentDownloadScheme": "http"`) . My question is, is there any other way to disable the peer segment download scheme other than removing the setting entirely? I tried this `"peerSegmentDownloadScheme": ""` and it failed with this message: ```{ "code": 400, "error": "Invalid value '' for peerSegmentDownloadScheme. Must be one of http or https" }```
@mark.needham: I think you're right that you'll need to remove that property, you can't specify something other than `http` or `https` otherwise it's gonna throw that errro
@mark.needham: error*
@mayanks: May I ask why you are using the peer download feature? It was primarily built to solve the issue where deepstore had occasional unavailability. Is there the case for you as well?
@raluca.lazar: we're trying to avoid bottlenecks on the controller and following the documentation
@raluca.lazar: so locally we don't want the setting, but we do want it in GKE
@mayanks: Yeah, peer download and eliminating pushing payload to controller should be independent, but somehow tied together. We should decouple these two in the implementation. cc @npawar @yupeng
@raluca.lazar: FYI: I ended up implementing as a JSON comment. Like this for local: ```"__peerSegmentDownloadScheme__": "http"``` and this is for GKE: ```"peerSegmentDownloadScheme": "http"``` The local is simply ignoring the setting and it all works
@luisfernandez: hey friends, coming with a sql optimization question today, we are trying to query pinot with larger timespans now that we are migrating our data, for example, this year, last year filters, for our use case we see that query execution time is way slower now and are trying to figure out ways to gain some performance given this, do you have any recommendations? ```SELECT product_id, SUM(impression_count) as impression_count, SUM(click_count) as click_count, SUM(cost) as spent_total FROM metrics WHERE user_id = xx AND serve_time BETWEEN 1641013200 AND 1654092017 GROUP BY product_id LIMIT 100000``` this is an example of a query we are running ```"numServersQueried": 2, "numServersResponded": 2, "numSegmentsQueried": 1317, "numSegmentsProcessed": 168, "numSegmentsMatched": 117, "numConsumingSegmentsQueried": 0, "numDocsScanned": 69212, "numEntriesScannedInFilter": 1165155303, "numEntriesScannedPostFilter": 415272, "numGroupsLimitReached": false, "totalDocs": 10362679599, "timeUsedMs": 4623,``` this is some of the stats that come back. our data resolution is hourly for this data. Do you all have any idea how to make a query like this perform better? We have an idea of making the records not have hourly resolution after certain period of time but have daily resolution so that the data is compressed even further but wanted to ask if there are any methods we could use for this and if the method we can implement makes sense to you all.
@diogo.baeder: Some things I would look at are if `serve_time` is either the time column or a column determined as a partition for the segments - so that you can prune segments to avoid searching too many of them -, and maybe creating an inverted index on `user_id`. I don't know, however, if segment partitioning works with realtime ingestion (which I'm guessing is your case).
@luisfernandez: `serve_time` is the timecolumn in our config `"timeColumnName": "serve_time",` in this case we are querying an offline table
@diogo.baeder: Ah, cool! Do you perhaps have a column with low cardinality that could be used as a partition marker? E.g. a "region" or "country" one which you could use for partitioning, so that you reduce the amount of segments queried?
@g.kishore: ```"numEntriesScannedInFilter": 1165155303,```
@g.kishore: you are missing indexes
@g.kishore: add range index on the serve_time column
@luisfernandez: we have this in the offline: ```"segmentPartitionConfig": { "columnPartitionMap": { "user_id": { "functionName": "Murmur", "numPartitions": 8 } } },``` ```"sortedColumn": [ "user_id" ], "bloomFilterColumns": [ "user_id", "product_id" ],```
@luisfernandez: oh we def don’t have range indexes ``` "rangeIndexColumns": [], "rangeIndexVersion": 2,```
@luisfernandez: is there not a default idnex on the serve_time column by default or is that only on realtime? @g.kishore
@diogo.baeder: I thought time columns always had a default index too...
@mayanks: Yes, you should add range index. Also is the timestamp in seconds? Do you need to query seconds granularity? If not store it in the granularity you want to query that will reduce cardinality and improve range index perf
@luisfernandez: granularity of this query is hourly
@luisfernandez: so just to confirm there’s no default index on serve_time
@g.kishore: right, pinot does not add default index on any column
@luisfernandez: I ran a query for a month where we had data in our production system and compared with data ingested thru offline and the query in prod is way faster then whatever we ingested in offline which is weird to me
@luisfernandez: I will add this to our realtime config as well as well as offline, (the index on serve_time that is)
@luisfernandez: I will report bak
@g.kishore: the only explanation is when you ingested through offline, the data got mixed up
@mayanks: compare metadata
@mayanks: Also in offline you will need to explicitly partition the data
@luisfernandez: we have a spark job that gets data from bigquery puts it on parquet files somwhere in GCS and then we ingest this data thru the standalone job into pinot
@mayanks: Do you partition it though?
@mayanks: Iirc you partition it in real-time
@luisfernandez: this is the config we have in offline tbh not so much differently from realtime but i guess some of this configs don’t make sense in offline:
@luisfernandez: ```{ "OFFLINE": { "tableName": "metrics_OFFLINE", "tableType": "OFFLINE", "segmentsConfig": { "schemaName": "metrics", "retentionTimeUnit": "DAYS", "retentionTimeValue": "730", "replication": "2", "timeColumnName": "serve_time", "allowNullTimeValue": false, "segmentPushType": "APPEND" }, "tenants": { "broker": "DefaultTenant", "server": "DefaultTenant" }, "tableIndexConfig": { "invertedIndexColumns": [], "noDictionaryColumns": [ "click_count", "order_count", "impression_count", "cost", "revenue" ], "rangeIndexColumns": [], "rangeIndexVersion": 2, "autoGeneratedInvertedIndex": false, "createInvertedIndexDuringSegmentGeneration": false, "sortedColumn": [ "user_id" ], "bloomFilterColumns": [ "user_id", "product_id" ], "loadMode": "MMAP", "onHeapDictionaryColumns": [], "varLengthDictionaryColumns": [], "enableDefaultStarTree": false, "enableDynamicStarTreeCreation": false, "segmentPartitionConfig": { "columnPartitionMap": { "user_id": { "functionName": "Murmur", "numPartitions": 16 } } }, "aggregateMetrics": false, "nullHandlingEnabled": false }, "metadata": {}, "quota": {}, "routing": { "segmentPrunerTypes": [ "partition" ] }, "query": {}, "fieldConfigList": [], "ingestionConfig": {}, "isDimTable": false } }```
@luisfernandez: like the partitioning stuff doesn’t make sense in offline right? only realtime
@luisfernandez: when you say partition is basically separate this data by an id and have stuff cluster together given an id right, and a partitions number like user_id % PARTITION_NUMBER and then have different files like that?
@diogo.baeder: Do you also partition your user_ids when doing the batch ingestion, to make sure you don't end up with segments that would contain basically any user_id? Because if this happens, that partitioning scheme is useless - I made this mistake too some time ago.
@diogo.baeder: It works for offline, yes - I'm using that in a project I'm building.
@luisfernandez: as in the partition config in the json file, that doesn’t matter for offline right? it’s just how you do it yourself
@luisfernandez: ``` "exceptions": [], "numServersQueried": 2, "numServersResponded": 2, "numSegmentsQueried": 1423, "numSegmentsProcessed": 168, "numSegmentsMatched": 117, "numConsumingSegmentsQueried": 0, "numDocsScanned": 69212, "numEntriesScannedInFilter": 1165155303, "numEntriesScannedPostFilter": 415272, "numGroupsLimitReached": false, "totalDocs": 11234231893, "timeUsedMs": 1464, "offlineThreadCpuTimeNs": 0, "realtimeThreadCpuTimeNs": 0, "offlineSystemActivitiesCpuTimeNs": 0, "realtimeSystemActivitiesCpuTimeNs": 0, "offlineResponseSerializationCpuTimeNs": 0, "realtimeResponseSerializationCpuTimeNs": 0, "offlineTotalCpuTimeNs": 0, "realtimeTotalCpuTimeNs": 0, "segmentStatistics": [], "traceInfo": {}, "minConsumingFreshnessTimeMs": 0, "numRowsResultSet": 1```
@luisfernandez: i added the date range index but seems like a lot of things get into `numEntriesScannedInFilter` still
@luisfernandez: also, for some of the queries, i have gotten an error about one of the offline servers not being able to respond with errorCode 427 anyone knows what’s that about
@luisfernandez: ``` "rangeIndexColumns": [ "serve_time" ], "rangeIndexVersion": 2,``` added this
@mayanks: Because your timestand is still in seconds (or ms) and hence really high cardinality? reducing that to hours will help as I mentioned earlier.
@luisfernandez: our timestamp is in seconds but it’s truncated to the hour if that makes sense
@mayanks: I see so for all data in one hour, they all share the same seconds value?
@luisfernandez: yes
@luisfernandez: exactly that
@mayanks: Then I suspect your data is not sorted. `WHERE user_id = xx AND serve_time BETWEEN 1641013200 AND 1654092017 `
@mayanks: The user id predicate should reduce the numDocsScanned to max docs a user can have.
@mayanks: And I doubt you have users with `1165155303` docs
@mayanks: Can you check segment metadata to ensure data is sorted?
@luisfernandez: yea it’s weird to me cause even the stats here ``` "exceptions": [], "numServersQueried": 2, "numServersResponded": 2, "numSegmentsQueried": 1497, "numSegmentsProcessed": 168, "numSegmentsMatched": 117, "numConsumingSegmentsQueried": 0, "numDocsScanned": 69212, "numEntriesScannedInFilter": 1165155303, "numEntriesScannedPostFilter": 415272, "numGroupsLimitReached": false, "totalDocs": 11854535291, "timeUsedMs": 1755, "offlineThreadCpuTimeNs": 0, "realtimeThreadCpuTimeNs": 0, "offlineSystemActivitiesCpuTimeNs": 0, "realtimeSystemActivitiesCpuTimeNs": 0, "offlineResponseSerializationCpuTimeNs": 0, "realtimeResponseSerializationCpuTimeNs": 0, "offlineTotalCpuTimeNs": 0, "realtimeTotalCpuTimeNs": 0, "segmentStatistics": [], "traceInfo": {}, "minConsumingFreshnessTimeMs": 0, "numRowsResultSet": 25305```
@mayanks: Yes check if data sorted
@luisfernandez: it comes back with 25k documents and it’s doing this: ``` "numEntriesScannedInFilter": 1165155303, "numEntriesScannedPostFilter": 415272,```
@mayanks: can you run `select count(*) where userId =xxx`? If numEntiresScanedInFilter is not zero then data is not sorted
@luisfernandez: ``` "exceptions": [], "numServersQueried": 2, "numServersResponded": 2, "numSegmentsQueried": 1497, "numSegmentsProcessed": 204, "numSegmentsMatched": 143, "numConsumingSegmentsQueried": 0, "numDocsScanned": 83660, "numEntriesScannedInFilter": 1435776018, "numEntriesScannedPostFilter": 0, "numGroupsLimitReached": false, "totalDocs": 11854535291, "timeUsedMs": 4000, "offlineThreadCpuTimeNs": 0, "realtimeThreadCpuTimeNs": 0, "offlineSystemActivitiesCpuTimeNs": 0, "realtimeSystemActivitiesCpuTimeNs": 0, "offlineResponseSerializationCpuTimeNs": 0, "realtimeResponseSerializationCpuTimeNs": 0, "offlineTotalCpuTimeNs": 0, "realtimeTotalCpuTimeNs": 0, "segmentStatistics": [], "traceInfo": {}, "minConsumingFreshnessTimeMs": 0, "numRowsResultSet": 1```
@luisfernandez: for that query, yea numEntiresScanedInFilter not zero
@luisfernandez: if you are curious `83660` the `count(*)` is for that user
@luisfernandez: how do I fix that, ``` "sortedColumn": [ "user_id" ],```
@luisfernandez: doesn’t this take care of that?
@mayanks: Is this realtime or offline ingestion? In realtime pinot will sort. In offline, your ingestion job needs to sort.
@luisfernandez: i’m afraid that even our realtime tables do not have this data sorted
@luisfernandez: how can i check?
@mayanks: How did you verify RT does not have it sorted
@luisfernandez: I did the SELECT COUNT(*) for the same user in prod and got umEntiresScanedInFilter not zero
@mayanks: Hmm, check segment metadata as I mentioned earlier
@luisfernandez: can i check via endpoint?
@luisfernandez: `/segments/{tableName}/{segmentName}/metadata` ?
@luisfernandez: it seems like the endpoint doesn’t show that data
@luisfernandez: but i can confirm things are not sorted by `user_id`
@mayanks: I think there is another endpoint for that. Let me find
@luisfernandez: we sorted the data but it seems like it is not doing it for us so far for the segment we are looking at
@luisfernandez:
@luisfernandez: and add this to the offline table config yes?
@luisfernandez: ```{ "tableIndexConfig": { "sortedColumn": [ "column_name" ], ... } }```
@mayanks: For offline, you need to sort the data outside of Pinot (before ingestion).
@luisfernandez: right we did that
@mayanks: For realtime, Pinot should have done it. I can’t think of a reason why it isn’t for your case.
@luisfernandez: and add the tableIndexConfig as well to the offline table?
@luisfernandez: yea yea we checked for realtime and it has indeed done it, for some reason numEntriesScannedInFilter was > than 0 tho when we did the COUNT(*) but then i checked the segments and it said isSorted=true for `user_id`
@luisfernandez: we sorted in spark, and then we used the standalone job to ingest one day data but the segments say they are not sorted
@mayanks: If isSorted = true for all segments of a table (which I expect for RT), numEntriesScannedInFilter should be zero.
@mayanks: Is this a hybrid table?
@luisfernandez: this is hybrid yes
@luisfernandez: ```"sortedColumn": [ "user_id" ], "bloomFilterColumns": [ "user_id", "product_id" ],```
@mayanks: Ok, so that could explain numEntrieScannedInFilter > 0 coming from offline.
@mayanks: If segment says it is not sorted, then for sure the input is not sorted.
@luisfernandez: would that mess things up?
@luisfernandez: if user_id is 2 indexes
@mayanks: It cannot change the sort order in offline. That is immutable as far as Pinot is concnerned.
@mayanks: Order in offline segment == order in what it saw in input file
@luisfernandez: i’m looking at the raw data of the segment (parquet file) and it’s sorted
@luisfernandez: i see it going on ascending order for the `user_id`
@luisfernandez: and just to clarify this is just the data that we are migrating into pinot, not what is already there from realtime + RT to offline, that does seem to be sorted when checking the metadata
@luisfernandez: but the parquet file we are ingesting now does look sorted to me ASC by `user_id`
@luisfernandez: and it doesn’t have to be sorted across segments for the same day
@mayanks: Indexes are in the scope of a segment.
@mayanks: Can user id’s be null? If so, that could explain why Pinot thinks it is not sorted.
@mayanks: Also, is this a number or string in parquet side, and does that match Pinot side (may be number sorted vs string sorted would be different).
@luisfernandez: user_id cannot be null
@luisfernandez: ```############ Column(user_id) ############ name: user_id path: user_id max_definition_level: 1 max_repetition_level: 0 physical_type: INT64 logical_type: None converted_type (legacy): NONE```
@luisfernandez: parquet tools in python showsm e this
@luisfernandez: shows me this
@mayanks: All things matching I am pretty sure that if Pinot sees input is not sorted, then it is not.
@diogo.baeder: Hey guys! Is there any plan to support UNION queries in Pinot, to unite results from two or more queries that yield the same result structure?
@mayanks: Not at the moment.
@diogo.baeder: Got it. No worries, I can deal with multiple queries on the client side then. :slightly_smiling_face:
@brunobrandaovn: Hello!! I’m building an application on Apache Pinot and I need to customize a few features, but some errors are happening. I think the controller can’t associate to the configurations. I’m receiving null results. The following configurations were executed, leading to this problem: The file pinot-controller.conf has this configuration: > pinot.service.role=CONTROLLER > controller.port=9001 > controller.zk.str=localhost:2181 > controller.access.protocols.http.port=9001 > pinot.cluster.name=MyClusterName > controller.vip.host=localhost > controller.vip.port=9001 > controller.data.dir=/tmp/pinot/data/controller > controller.helix.cluster.name=MyClusterName >
#getting-started
@hui: @hui has joined the channel
@kingkenway16: @kingkenway16 has joined the channel
@rsohlot: @rsohlot has joined the channel
@vishnu: @vishnu has joined the channel
@visar: @visar has joined the channel
#introductions
@hui: @hui has joined the channel
@kingkenway16: @kingkenway16 has joined the channel
@rsohlot: @rsohlot has joined the channel
@vishnu: @vishnu has joined the channel
@visar: @visar has joined the channel
--------------------------------------------------------------------- To unsubscribe, e-mail: dev-unsubscr...@pinot.apache.org For additional commands, e-mail: dev-h...@pinot.apache.org
