#general
@lochanie1987: @lochanie1987 has joined the channel
@patidar.rahul8392: Is there any way to handle null values in Pinot. I.e. I have one column in Kafka in some messages it contains some value and sometime it's coming null. I have added this column in transformation function so in this case it only showing those row in which we have value for this column. And I I don't add this column in transformation function in that case it's showing all the rows but for this column taking null in all the rows.
@patidar.rahul8392: In config file I have added like so i.am.only able to see the rows whose value is not null.
@fx19880617: if you make this field a column in pinot, then you can set default null value, during query time, you can try to filter based on the null value.
@patidar.rahul8392: @fx19880617 means here no need to remove this transformation, but I need to add one more default value column here for this field as need to assign default value as null, so in this case will it pick both null and not null values because how it's only taking the rows where this columns value is not null?
@npawar: Take a look at the documentation for jsonPathString with default value:
@patidar.rahul8392: @npawar Thanks alot .It Worked.
@xiacongling: @xiacongling has joined the channel
@irakov: Hi, I have a question about StarTree indices: are there any recommendations about ordering in ```tableIndexConfig.starTreeIndexConfigs.dimensionsSplitOrder``` Thanks in advance :)
@npawar: Typically you go from highest cardinality dimension to lowest, followed by time columns
@npawar: You could also just enable default star tree
@irakov: Thank you, @npawar
@nadeemsadim: @nadeemsadim has joined the channel
@lochanie1987: Hi All, I am a complete beginner to apache pinot. Today I have installed apache pinot in my windows 10 machine using below link.
@kautsshukla: Hi All, My server is crashing every time due of OOM, My servers are r5.4xlarge and heap space i have provided is 110GB. I have 2 servers.
@dlavoie: Reduce your heap size. Server requires at least 50% of head room for non heap memory
@kautsshukla: okz
@kautsshukla: @dlavoie thanks
@mayanks: It will also help to get the stack trace. If you are using MMAP mode (recommended), then you should not run into OOM.
@mayanks: @kautsshukla ^^. The one place server uses heap is query execution, but there also it sill try to bail out (unless you increased max num groups), to avoid OOM. So I am really curious about the stack trace.
@mayanks: @kautsshukla let’s continue here
@mayanks: Seems you have 3500 segments on single server
@kautsshukla: Yes
@kautsshukla: Each server with 3500 segments and I have 2 servers
@kautsshukla: Total 7000 segments
@mayanks: The metadata is also stored in memory, but I can’t see how it uses several GB. Would it be possible for you to share the stack
@kautsshukla: I’ll check as I’m out of system as of now
@mayanks: And this is as soon as you bring the server up?
@kautsshukla: No after sometime almost loading half 1700 segments in each server abd than dient
@kautsshukla: Dieing
@mayanks: These are real-time segments that are already written to disk?
@mayanks: I think real-time nodes allocate direct buffer for consuming segments. If you allocated entire memory for heap, then it could run out of direct memory. Unless your queries are doing heavy computation, you should use limited amount of heap (we typically use 16GB for our heavy production loads).
@kautsshukla: @mayanks I have also did the same bring down the Heap and its running fine from last 2 hours
@kautsshukla: @mayanks I was just cehcking what to give as heap size
@kautsshukla: beacuse on max it was coming down
@mayanks: What’s your read qps for queries? And are queries going to process too much data in memory? We have used just 16GB for most production use case and it has worked fine
@kautsshukla: as of now query load is not there…
@mayanks: Ok assuming you will have decent query load that requires processing large amounts of data in memory (say 100's of thousands of groups etc), you can still do away with 16GB heap.
@kautsshukla: @mayanks ok
@aiyer: Hello -- I created a default real time table . After consuming some 300k events i wanted to add a sorted inverted so i edited the table to add the sorted col. How can I check if my query is using the index and whether the index is successfully created or not.?
@mayanks: For segments flushed to disk you can check metadata.properties file. If the column is marked as sorted, you can assume sorted index will be used.
@aiyer: Hi Mayank -- this is the metadata from one of the latest segments, but i dont see any sorted column here.. ```{ "segment.realtime.endOffset": "1209967", "segment.start.time": "1621347175827", "segment.time.unit": "MILLISECONDS", "segment.flush.threshold.size": "50000", "segment.realtime.startOffset": "1159967", "segment.end.time": "1621347449397", "segment.total.docs": "50000", "segment.table.name": "schd_1", "segment.realtime.numReplicas": "1", "segment.creation.time": "1621347177149", "segment.realtime.download.url": "
@aiyer: how do I ensure the sorted index is created and put to use ?
@aiyer: ```{ "REALTIME": { "tableName": "schd_1_REALTIME", "tableType": "REALTIME", "segmentsConfig": { "timeType": "MILLISECONDS", "schemaName": "schd", "timeColumnName": "upd_ts", "segmentAssignmentStrategy": "BalanceNumSegmentAssignmentStrategy", "segmentPushType": "APPEND", "replicasPerPartition": "1" }, "tenants": { "broker": "DefaultTenant", "server": "DefaultTenant" }, "tableIndexConfig": { "loadMode": "MMAP", "streamConfigs": { "streamType": "kafka", "stream.kafka.consumer.type": "lowLevel", "stream.kafka.topic.name": "schd", "stream.kafka.consumer.prop.auto.offset.reset": "smallest", "stream.kafka.decoder.class.name": "org.apache.pinot.plugin.stream.kafka.KafkaJSONMessageDecoder", "stream.kafka.hlc.zk.connect.string": "localhost:2191/kafka", "stream.kafka.consumer.factory.class.name": "org.apache.pinot.plugin.stream.kafka20.KafkaConsumerFactory", "stream.kafka.zk.broker.url": "localhost:2191/kafka", "stream.kafka.broker.list": "localhost:19092", "realtime.segment.flush.threshold.rows": "50000", "realtime.segment.flush.threshold.time": "10m" }, "enableDefaultStarTree": false, "autoGeneratedInvertedIndex": false, "createInvertedIndexDuringSegmentGeneration": false, "sortedColumn": [ "post_prd_id" ], "enableDynamicStarTreeCreation": false, "aggregateMetrics": false, "nullHandlingEnabled": false }, "metadata": { "customConfigs": {} }, "routing": { "instanceSelectorType": "strictReplicaGroup" }, "upsertConfig": { "mode": "FULL" }, "isDimTable": false } }```
@aiyer: this is my table config
@npawar: This metadata is from zookeeper. You need to check the metadata.properties file, which you will find on the server, inside each segment dir
@mayanks: Yes ^^
@npawar: And for older completed segments, any indexing change in table config will only reflect after a segment reload API invocation. However, I think sorted index cannot be applied this way to old segments
@aiyer: where can i find the location of these files ? I am using the docker image.
@mayanks: The data dir of the server
@aiyer: got it ```column.post_prd_id.columnType = DIMENSION column.post_prd_id.isSorted = true```
@mayanks: Yeah, if column is sorted then You can assume it is being used
@aiyer: got it, i assume there is no way to get the query plan since its dynamic for every segment, right?
@mayanks: Yeah, right now there isn’t a way to get query plan, mostly because pinot doesn’t support complex joins or nested queries
@aiyer: got it.. makes sense..
@aiyer: ```timeUsedMs numDocsScanned totalDocs numServersQueried numServersResponded numSegmentsQueried numSegmentsProcessed numSegmentsMatched numConsumingSegmentsQueried numEntriesScannedInFilter numEntriesScannedPostFilter numGroupsLimitReached partialResponse minConsumingFreshnessTimeMs offlineThreadCpuTimeNs realtimeThreadCpuTimeNs 40 31741 1584379 1 1 43 43 34 1 928729 95223 false - 1621350158012 0 0```
@aiyer: Where can I read about what each of these mean ?
@mayanks: one sec
@mayanks:
@mayanks: The search in
@aiyer: got it.. sorry about that.. i was wondering what is the diff between Docs and Entries ?
@aiyer: docs is the actual record, but what does numEntries mean?
@mayanks: Doc represents a record
@mayanks: Entry represents a value for a column in the record.
@aiyer: ok.. so if i see that numEntriesScannedInFilter is really high for a low cardinality col filter, would that mean it's better to have an inverted index on that?
@mayanks: If the cardinality is very low (say gender - M/F/U), then adding inv index only prunes out 2/3 or the data. Depending on your case and query latency requirement, it might still be a good idea.
@aiyer: right.. understood..
@aiyer: is there any way to get the explain plan ?
@g.kishore: We removed that feature.. we have better ways to analyze the plan by looking at the response stats
@miguel0alves: @miguel0alves has joined the channel
@hamza.senoussi: @hamza.senoussi has joined the channel
@mags.carlin: @mags.carlin has joined the channel
@baetensmatthias: @baetensmatthias has joined the channel
@yupeng: Any idea why we convert division of long/long to double by default? for example, in presto `select (1618900560000 - 1618437600000)/604800000*604800000` returns 0, while `SELECT (1618900560000 - 1618437600000)/604800000` returns 0.76547 ?
@amrish.k.lal: It is in large part due to `ArithmeticFunctions.java` which outputs a `double` for any arithmetic computation.
@yupeng: yeha, my question is that shall we consider the behavior with other SQL engines like Hive/Presto/Mysql
@amrish.k.lal: I would definitely second making it more standardized :slightly_smiling_face: I have been trying to do some of that with my PRs on numerical type conversion (#6811, #6927).
@amrish.k.lal: @steotia wanted to change ArithmeticFunctions.java to use templates so that we can return the right output type for given input types.
@yupeng: yeah, that will be awesome
@yupeng: in the meanwhile, any workaround that you suggest to make the query above to output the same as presto?
@steotia: Use cast function in the meantime?
@jackie.jxt: What is the standard sql behavior? Are all numbers without decimal point treated as integer/long?
@amrish.k.lal: yes, the output of simple arithmetic computation on integer/long values should be integer/long.
@steotia: This needs to be fixed across Pinot. Like sum function over long returns double.
@yupeng: +1
@yupeng: also, why ```SELECT cast((1618900560000 - 1618437600000) / 604800000 as integer ) * 604800000``` returns 462959999.99999994
@yupeng: but ```SELECT cast((1618900560000 - 1618437600000) / 604800000 as integer ) ``` returns 0 ?
@fx19880617: I guess this is a bug in the literal evaluation
@yupeng: more than literal..
@yupeng: i am debugging a query
@fx19880617: Ideally this query should directly return
@fx19880617: Not hitting servers
@yupeng: ```cast((column - 1618437600000) / 604800000 as integer ) * 604800000``` returns incorrect data
@fx19880617: let’s create a github issue to track this?
@yupeng: yeah, we should
@yupeng: btw, is there a workaround to achieve bucketing?
@fx19880617:
@fx19880617: you can pick the function
@fx19880617: ```ToEpoch<TIME_UNIT>Bucket(timeInMillis, bucketSize)```
@yupeng: i see
@yupeng: so in my case, i group by week
@fx19880617: date_trunc? then?
@yupeng: i can use `ToEpochDayBucket(time, 7)` ?
@fx19880617: then you need to multiply 7 * 86400000 to get the millis
@fx19880617: or try `DATETIMECONVERT(ts, '1:MILLISECONDS:EPOCH', '1:MILLISECONDS:EPOCH', '7:DAYS')`
@yupeng: yup, get it works
@fx19880617: also `DATETRUNC('week', ts, 'MILLISECONDS')`
@fx19880617: so really up to you
@fx19880617: you can try multiple functions and find the most efficient one
@fx19880617: they are all different implementations
@yupeng: i see
@karinwolok1: :wine_glass: Welcome new Pinot community members! :wave: We're so happy that you've joined us! If you haven't already, please take a moment and introduce yourself, let us know how you found Pinot and what you're working on! :smiley: @miguel0alves @hamza.senoussi @baetensmatthias @nadeemsadim @xiacongling @machhindra.nale @zlchen.ken @cyril @lochanie1987 @prince2015999 @hrsripad @mohamedkashifuddin @benjamin.walker @aritra55 @oneandwholly @amnerkarnitish9 @vishnureddyb1999 @mbracke @brijdesai6 @laurachen @kr.vikas @kelvin @riteshrathi @xiong.juliette @mohitdubey95 @mkrishna.parimi @keweishang
@machhindra.nale: Hello Community, This is Machhindra. I am an architect, working on an analytics solution in Broadcom. While reading about Superset/Druid in a blog, came to know about Pinot. Curiosity led me here. Basically we are collecting a lot of high dimensional operational data (metrics). About 400M/day. Looking to provide use facing analytics.
@lochanie1987: Hello, This is Lochanie working as a data engineer. When I am finding proper solution for OLAP I came across this tool and got to know about the slack community. I am a complete beginner to apache pinot. I think this slack community will help me lot. And I am willing to share my knowledge as well.
@guido.schmutz: Hi all, my name is Guido, I work as a Platform Architect for an IT service provider in Switzerland. I have worked a lot with Kafka based solutions as well as Data Lakes and I'm generally interested in all things data and seeing a potential use in a modern data architecture. Pinot looks very promising and I'm eager to give it a try in the near future. So far only have watched a couple of youtube videos. And I also like good wine, especially red wine :wink: Thanks a lot for the nice welcome :-)
@jiasheng55: @jiasheng55 has joined the channel
#random
@lochanie1987: @lochanie1987 has joined the channel
@xiacongling: @xiacongling has joined the channel
@nadeemsadim: @nadeemsadim has joined the channel
@miguel0alves: @miguel0alves has joined the channel
@hamza.senoussi: @hamza.senoussi has joined the channel
@mags.carlin: @mags.carlin has joined the channel
@baetensmatthias: @baetensmatthias has joined the channel
@jiasheng55: @jiasheng55 has joined the channel
#troubleshooting
@lochanie1987: @lochanie1987 has joined the channel
@xiacongling: @xiacongling has joined the channel
@nadeemsadim: @nadeemsadim has joined the channel
@nadeemsadim: Hi @mohamedkashifuddin @mohamed.sultan @shaileshjha061 @pugal.selvan
@lochanie1987: Hi All, I am a complete beginner to apache pinot. Today I have installed apache pinot in my windows 10 machine using below link.
@nadeemsadim: how can we increase pinot ingestion throughput if we are consuming messages from kafka topic . .say the output topic have only 1 partition .. will increasing partition of the kafka topic from where pinot is consuming automatically increase pinot throughput ... means pinot is smart enough to increase the no of consumers consuming from that topic and storing into tables/segments ..
@mohamed.sultan: Hi @mayanks Glad to say, He is also my team mate who is working on data engineering stuffs.
@fx19880617: pinot will handle Kafka topic expansion
@fx19880617: when you use low-level kafka consumer, pinot will create one segment per kafka topic partition
@fx19880617: so you can scale your ingestion throughput accordingly
@fx19880617: I think current pinot only handles scale up not scale down , meaning you can only increase kafka topic partitions
@nadeemsadim: @mayanks regarding the discussion u had with @mohamed.sultan.. why we need to create a new pinot cluster and restore the backup there.. so the use case is like .. suppose my gcp service account is changed and i need to migrate to a new gcp vpc .. then how can we restore the pinot backup from one cluster to another pinot cluster @mohamed.sultan @pugal.selvan @mohamedkashifuddin @shaileshjha061 please post what is the blocker we faced in restore
@nadeemsadim: thanks a lot @fx19880617.. that helps ..
@nadeemsadim: cc: @hussain
@mayanks: @nadeemsadim if you can copy data from old to new vpc then you can simply point the new cluster to the new vpc.
@mayanks: Note though, please don’t have two clusters point to same vpc and shot same tables
@nadeemsadim: sure mayank .. ofcourse pointing two clusters to same tables will cause issue .. but restoring should not be a blocker @mohamed.sultan
@mayanks: Yes, restore works.
@miguel0alves: @miguel0alves has joined the channel
@hamza.senoussi: @hamza.senoussi has joined the channel
@mags.carlin: @mags.carlin has joined the channel
@baetensmatthias: @baetensmatthias has joined the channel
@jiasheng55: @jiasheng55 has joined the channel
#presto-pinot-connector
@patidar.rahul8392: @patidar.rahul8392 has joined the channel
#getting-started
@hamza.senoussi: @hamza.senoussi has joined the channel
#complex-type-support
@hamza.senoussi: @hamza.senoussi has joined the channel
--------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
