You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@pinot.apache.org by Pinot Slack Email Digest <sn...@apache.org> on 2021/05/19 02:00:23 UTC
Apache Pinot Daily Email Digest (2021-05-18)
### _#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: `jsonPathString(after, '$Conveniencefee', 'none')` - for example if
you wanted to set 'none' in rows where the column in null in source. This way
the row won't get skipped during ingestion
**@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. . But
when I type , I cannot see the pinot data explore. It gives the error message
this site cant be reached. Please help me to resolve this issue. my pinot
version is 0.6.0 and JDK version is 8. I ran the bach quick-start command
only. Thank you!
**@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": "", "segment.name":
"schd_1__0__34__20210518T1412Z", "segment.index.version": "v3",
"segment.flush.threshold.time": null, "segment.type": "REALTIME",
"segment.crc": "2855665894", "segment.realtime.status": "DONE" }```
**@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 is pretty good, and should be able to point you
to any docs related to terms you query.
**@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. . But
when I type , I cannot see the pinot data explore. It gives the error message
this site cant be reached. Please help me to resolve this issue. my pinot
version is 0.6.0 and JDK version is 8. I ran the bach quick-start command
only. Thank you!
**@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: dev-unsubscribe@pinot.apache.org For additional commands,
e-mail: dev-help@pinot.apache.org