Hi team we currently have 60 columns in our Pinot ...
# generala
Abhijeet Kushe
07/26/2023, 11:49 AMHi team we currently have 60 columns in our Pinot table.Since Pinot does not support joins we decided to store each event enriched with joined data in the same so our table support multiple types of events but has led to column expansion.We are prepared to redesign if need.Had a few questions
1. Is there a limit on the Number of columns we can have in pinot for a realtime upsert table
2. Also is there a recommended way to do this for eg using Json object instead of multiple column which is better ?
k
Kartik Khare
07/26/2023, 12:02 PM@Rong R Hi abhijeet, joins are supported now in Pinot after 0.12.0. If you can mention you queries, we can guide with the schema design
a
Abhijeet Kushe
07/26/2023, 12:04 PMThanks @Kartik Khare @Rong R I will post my high level domain design here shortly..but just curious is there limit on the number of columns or are there disadvantages due to it ? Many of our columns have null values as well
m
Mayank
07/26/2023, 1:31 PM
There is no limit in the code for number of columns. 60 doesn’t sound like out of the charts. If most of them are sparse attributes, you can also explore json column, or something like attr-name + attr-type.
r
robert zych
07/26/2023, 2:16 PMIn a recent Uber talk, it was mentioned that with 100+ columns and partial upsert, memory issues can arise.
a
Abhijeet Kushe
07/26/2023, 2:19 PMThanks @robert zych can you share the talk Incase you have the link handy
Thanks @Mayank I will take a look at Json column
r
robert zych
07/26/2023, 2:55 PMThe talk was only a week ago and hasn't been posted yet. The talk I'm referring to is "Pinot Joins at Uber Scale" by Ankit Sultana
a
Abhijeet Kushe
07/26/2023, 2:58 PMthanks i will lookout
👍 1
Abhijeet Kushe
08/31/2023, 11:36 AM@Mayank when you mentioned using json columns u mean that I will create a field like properties as string which will have different json objects and create a json complex type .https://docs.pinot.apache.org/basics/data-import/complex-type .The problem with this approach is that we do have metrics fields inside the json properties which we want to do operations like sum, min max etc which we cannot define https://docs.pinot.apache.org/configuration-reference/schema#metricfieldspec .So in this situation we will probably have to extract them out and only add string lookup in json ? Also for attr-name + attr-type due mean storing data in a denormalized unnested format ?