#general
@markandrusroberts: @markandrusroberts has joined the channel
@ackermanholden: Why does Pinot exist? It looks like a rip off of Apache Druid and Clickhouse. Does the world really need Pinot too...what are the differences? Or was this just done because LinkedIn and Uber engineers had nothing better to do?
@srini: you’re welcome to not use Pinot :slightly_smiling_face: There’s over a thousand different relational OLTP SQL databases, each with its own quirks, use cases, and vendors. Why is it so surprising to imagine even a few options in the real-time OLAP space?
@sosyalmedya.oguzhan: pinot has lots of flexibility and great indexing techniques, just read comparisons between pinot and others realtime olap systems
@ackermanholden: Don't they all? Scanning the Pinot code, it especially looks like a fork of Druid, and CH seems a bit more of a lightweight OLAP db
@sleepythread: We are using CH is production and currently running POC on Pinot as one of our use cases are not performing well on CH. Each of these system has some advantages and disadvantages.
@sosyalmedya.oguzhan: For example, the biggest different thing is partitioning. Druid forces you to partition data by time, pinot does not. Dont look the code first. Just look the documentations and find limits. Each of them has similar concepts but actually there are very different i think
@sleepythread: Having Mysql doesn’t mean that we should not have SqlLite as RBMS
@sleepythread: each one is built and used for specific reasons and use cases.
@shirshanka: Hi folks, I noticed that there are some old Pinot docker images sitting here:
@fx19880617: no, please refer to
@shirshanka: Are the first one maintained by linkedin?
@mayanks: @shirshanka No, the are 4 years old, I am pretty sure they are not being maintained by anyone
@shirshanka: Thanks @mayanks, would be great to delete them if possible to avoid confusion.
@mayanks: Agree, will followup
@joshhighley: When doing upsert, can the table's time column be a string or does it have to be a long?
@yupeng: it has to be the time column of the table for now
@yupeng: so needs to be long
@joshhighley: ah, so time column can't be a string? "format":"1:MILLISECONDS:SIMPLE_DATE_FORMAT:EE,dd MMM yyyy HH:mm:ss.SSS"
@yupeng: yeah, it can be converted during ingestion
@yupeng:
@joshhighley: I can't use simple_data_format with LONG, though, correct? I believe I tried that and it wouldn't convert
@joshhighley: I'd have to use a transformFunction, correct?
@jackie.jxt: Currently we don't support SDF string as the upsert time values, but that is doable. Can you please submit a github issue for this?
@jackie.jxt: For now, you have to use transform to convert the values to long
@yupeng: is it possible to add the table creation/update time to the cluster management UI (under table)? so we can sort the tables to view the recently added/modified tables?
@fx19880617: Do we have APIs for this?
@fx19880617: Or we should try to use znode timestamp ?
#random
@markandrusroberts: @markandrusroberts has joined the channel
#troubleshooting
@ravikumar.m: *I have a doubt, need your help.* I am using flatten json and created schema config and table config with fattening. My doubt is, Before ingestion to pinot, should I transfer the actual json to flatten json format or Pinot can automatically understand based on schema config.
@jackie.jxt: Hi Ravikumar, can you please share an example schema, table config and json record? Don't completely follow the question
@markandrusroberts: @markandrusroberts has joined the channel
@elon.azoulay: Is there a way to change the kafka cluster (which will change offsets) to realtime table? How will pinot behave if we update the table config, will it handle the different offsets or do we need to do some other config changes?
@mayanks: I suppose offsets are cluster specific? If so, may be re-bootstrap might be safer?
@elon.azoulay: Does rebootstrapping lose data?
@elon.azoulay: Also, what is reboostrapping mean? Drop and recreate the table?
@mayanks: Yeah, drop and recreate.
@mayanks: If this is a hybrid table, you can just consume from offset not in offline data (as opposed to full realtime retention).
@elon.azoulay: Is it possible to do that by setting this config:`"stream.kafka.consumer.prop.auto.offset.reset": "smallest",`?
@elon.azoulay: also, thanks @mayanks!
@elon.azoulay: Would the following work? disable the table, then update the config and then restart the servers, and enable table?
@mayanks: Hmm, I'd recommend clean delete/recreate
@mayanks: Also, before recreate ensure to wait until IS/EV is gone.
@elon.azoulay: What's IS/EV?
@mayanks: IdealState/ExternalView
@elon.azoulay: ah:)
@mayanks: You can temporarily reduce retention to say 2d, to only consume last two days
@mayanks: I think there was a config way of doing that, let me find
@elon.azoulay: On the new table or the old table?
@elon.azoulay: is that as simple as setting retention in the table config?
@mayanks: No no, talking about two different things. Let me summarize:
@mayanks: ```1. If offsets are cluster specific - then better to delete -> recreate. 2. If you have a hybrid table, you don't really want to consume all last n days of data in realtime, since it won't be used anyway. If you want to speed this up, you can play with retention (or there is a config way) to only consume last 2 days.```
@elon.azoulay: ah, thanks! this is a realtime only table
@mayanks: Oh, then 2 doesn't apply
@elon.azoulay: Would recreating it allow for downloading older segments from deepstore?
@elon.azoulay: or can we manually just do upload download segment api call to load older realtime segments?
@mayanks: No. Moreover, if offsets are differnt, those can't be used right
@mayanks: Because if that worked, then you wouldn't even need to delete technically
@elon.azoulay: Oh, so we can't just download the older segments and reupload them to the newer table?
@mayanks: No, it won't work because we won't know what offsets the old segments correspond to, in the new cluster.
@mayanks: btw: ``` public OffsetCriteria withOffsetAsPeriod(String periodString) { Preconditions.checkNotNull(periodString, "Must provide period string eg. 10d, 4h30m etc"); _offsetCriteria.setOffsetType(OffsetType.PERIOD); _offsetCriteria.setOffsetString(periodString); return _offsetCriteria; }```
@elon.azoulay: ah got it
@mayanks: So we do support offset as period.
@elon.azoulay: would there be a way via a minion job I can move realtime segments from the old table to offline segments in the new table?
@ssubrama: you cannot point to a different cluster, even though the schema maybe same. segments inone cluster not equivalent to the other one. so, you need to start afresh. Yes, `smallest'` offset will work, but be careful that you may need to allow some time to consume beforre serving queries at a high rate (cpu is needed for consuming).
@elon.azoulay: Thanks @ssubrama! If anything I would like to contribute to some features to enable moving segments from realtime to offline, or from one table to another - are there any open issues or some example minion jobs you can point me to?
@ssubrama: I believe @npawar already has something on this?
@ssubrama: But then even if you did the move to offline segmegnts, you will need to drop the realtime table and then re-consume. Yes, you can do that for a much shorter time period
@elon.azoulay: sounds good. Would the code for @npawar’s minion job be in the pinot repo somewhere or is it in a different repo?
@elon.azoulay: Ah I see it I think: in pinot-minion-builtin-tasks?
@npawar:
@npawar: this one will move from realtime to offline, for the same table, like an automatic hybrid mode
#pinot-dev
@aiyer: @aiyer has joined the channel
#getting-started
@aiyer: @aiyer has joined the channel
#minion-improvements
@laxman: Thanks Neha. This is really useful.
@laxman: Followed the steps as per the documentation. • Created an OFFLINE table • Updated controller config • Update REALTIME table config with task config (empty - all defaults) • Scheduled REALTIME to OFFLINE task After fixing all the internal issues, I’m able to see tasks are running. However, here is my observation. In the first run, task executor chose several segments (approx 20) as inputs. However, it processed and converted only one of the input segment. Don’t see any errors in logs. Will analyse this tonight? Please let me know if you have any suggestions
@jackie.jxt: Can you please check the document count in the converted one?
@jackie.jxt: The job should be able to merge multiple segments and only produce one
@npawar: also by default, the max num records per converted segment will be 1 million. If your input segments in total had lesser docs than that, then it is expected to end up with only 1
@laxman: Each of my input segment’s docs count is some where around 30K to 50K And doc count in output segment is 42K by which I assumed it processed only one input segment
@laxman: I’m using 0.7.1 release version
@npawar: how many days of data in your RT table? and how many total segments so far?
@npawar: this job will process 1 day’s worth of data at a time.
@jackie.jxt: I believe we only generate segments for one time bucket (1 day by default) in each task execution. The chosen segments are all probably falling into this time range
@npawar: i’m guessing you have several partitions (20?)
@laxman: • We have 8 Kafka partitions • REALTIME retention for the table under test is 5 days • Total number segments: 52
@laxman: Every time I trigger/schedule the task, its converting one segment from REALTIME to OFFLINE
@laxman:
@laxman: I triggered the task 3 times here. So, it converted 3 segments to OFFLINE
@npawar: that is expected, it will only generate 1 segment at a time, which should have all records for 1 day
@npawar: can you run a query like “select $segmentName, min(timeColumn), max(timeColumn) from table group by $segmentName”
@npawar: you should see that each segment has one day worth of data records
@jackie.jxt: Also `count(*)` in the query
@npawar: and then pick one time range from the above output, run the similar query on realtime table. “select count(*) from table where timecolumn >= <min time> and timeColumn <= maxTime
@laxman: ```select $segmentName, min(event_time_millis), max(event_time_millis), count(*) from domainEventView group by $segmentName limit 100``` Output: This include both REALTIME and OFFLINE ```{"columns":["$segmentName","min(event_time_millis)","max(event_time_millis)","count(*)"],"records":[["domainEventView__0__102__20210422T1017Z",1619086645767,1619134068877,6608],["domainEventView__7__28__20210420T1501Z",1618930894484,1619031552782,22500],["domainEventView_1618790405342_1618876799369_0",1618790405342,1618873199296,54766],["domainEventView__2__101__20210421T1859Z",1619031568295,1619045508556,33750],["domainEventView__7__29__20210421T1859Z",1619031552782,1619045933619,33750],["domainEventView__1__27__20210421T2256Z",1619045767111,1619069905634,50625],["domainEventView__4__100__20210421T1858Z",1619031479387,1619046014918,33750],["domainEventView__0__98__20210418T2140Z",1618873202729,1618929892892,7788],["domainEventView__2__99__20210418T2341Z",1618873201337,1618931997639,8261],["domainEventView__3__30__20210422T0547Z",1619066349108,1619134052130,9333],["domainEventView__7__31__20210422T0544Z",1619065559798,1619134072500,10335],["domainEventView__3__28__20210421T1906Z",1619031981781,1619046514907,33750],["domainEventView__1__25__20210420T1357Z",1618927074998,1619031460689,22500],["domainEventView__2__100__20210420T1520Z",1618932001687,1619031568194,22500],["domainEventView_1618618779719_1618703995041_0",1618618779719,1618703995041,48572],["domainEventView__5__26__20210418T2043Z",1618873205983,1618926100709,7406],["domainEventView__5__30__20210422T0537Z",1619064911022,1619134040146,12022],["domainEventView__2__103__20210422T0538Z",1619064938229,1619134039190,11658],["domainEventView__1__28__20210422T0539Z",1619064997429,1619134071340,11355],["domainEventView__0__100__20210421T1858Z",1619031488779,1619046443102,33750],["domainEventView__3__26__20210418T2203Z",1618873200324,1618930813597,7757],["domainEventView__3__29__20210421T2308Z",1619046514907,1619070372298,50625],["domainEventView__5__29__20210421T2252Z",1619045563141,1619069808820,50625],["domainEventView__4__99__20210420T1435Z",1618929363613,1619031479387,22500],["domainEventView__1__24__20210418T2134Z",1618873210153,1618927046816,7454],["domainEventView__6__99__20210420T2235Z",1618958121722,1619033018190,22500],["domainEventView__0__101__20210421T2307Z",1619046443118,1619086645767,50625],["domainEventView__5__27__20210420T1341Z",1618926100731,1619031458684,22500],["domainEventView__1__26__20210421T1857Z",1619031460501,1619045767111,33750],["domainEventView__4__101__20210421T2300Z",1619046014918,1619069994715,50625],["domainEventView__4__98__20210418T2134Z",1618873203878,1618929343192,7419],["domainEventView__5__28__20210421T1857Z",1619031458684,1619045555306,33750],["domainEventView__6__98__20210419T0742Z",1618873228819,1618958121687,10133],["domainEventView_1618704000458_1618790386513_0",1618704000458,1618790386513,49707],["domainEventView__7__30__20210421T2258Z",1619045933635,1619070179512,50625],["domainEventView__0__99__20210420T1444Z",1618929892909,1619031487687,22500],["domainEventView__3__27__20210420T1500Z",1618930814498,1619031981781,22500],["domainEventView__6__102__20210422T1646Z",1619109992033,1619134065944,3554],["domainEventView__4__102__20210422T0540Z",1619065040042,1619134067785,11074],["domainEventView__2__102__20210421T2251Z",1619045508556,1619069844770,50625],["domainEventView__6__100__20210421T1923Z",1619033018190,1619048586918,33750],["domainEventView__6__101__20210421T2343Z",1619048586918,1619109992022,50625],["domainEventView__7__27__20210418T2338Z",1618873213168,1618930894054,8147]]}```
@jackie.jxt: The 3 offline segments sorted with time ```"domainEventView_1618618779719_1618703995041_0",1618618779719,1618703995041,48572 "domainEventView_1618704000458_1618790386513_0",1618704000458,1618790386513,49707 "domainEventView_1618790405342_1618876799369_0",1618790405342,1618873199296,54766```
@jackie.jxt: Actually maybe you can do `select $segmentName, min(event_time_millis), max(event_time_millis), count(*) from domainEventView group by $segmentName order by min(event_time_millis) limit 100`
@jackie.jxt: I think it is working properly
@jackie.jxt: You can try `select count(*) from table_OFFLINE/table_REALTIME where event_time_millis <= 1618876799369` and see if the result matches
@laxman: Jackie, I was expecting this task to convert all existing REALTIME segments to OFFLINE.
@laxman: Even from minion logs I see its picking up multiple REALTIME segments as input. However, after the task I see only one of those segments are converted to OFFLINE.
@laxman: Adding Suraj. We both are working on this now. @suraj
@suraj: @suraj has joined the channel
@jackie.jxt: Each time it will generate segments for one day as I mentioned
@jackie.jxt: The minion task will run every hour, so soon the offline table should be able to catch up with realtime table
@jackie.jxt: The multiple realtime segments are merged into one offline segment for that day
@laxman: > The minion task will run every hour, so soon the offline table should be able to catch up with realtime table Where is this configurated? Currently, task is scheduled only when I invoke the schedule API explicitly.
@laxman: I configured this in controller. Assuming task will be triggered every 15 minutes ```controller.task.frequencyInSeconds=900```
@jackie.jxt: You also need to put `controller.task.scheduler.enabled=true`
--------------------------------------------------------------------- To unsubscribe, e-mail: dev-unsubscr...@pinot.apache.org For additional commands, e-mail: dev-h...@pinot.apache.org
