https://pinot.apache.org/ logo
Join Slack
Powered by
Hello :slightly_smiling_face: Quick question regar...
# troubleshooting
j
Hello 🙂 Quick question regarding 
ingestionConfig
on REALTIME tables Is there any way to 
jsonPathString
+ further process the result with 
Groovy
in 
transformConfig
?
Say we have
Copy code
{"data": {
  "someKey": <needs_processing>
  }
}
I'd like to process 
data.someKey
with 
Groovy
It's possible from the query side with :
Copy code
groovy('{"returnType":"LONG","isSingleValue":true}', 'Long.valueOf((arg0.substring(0, 8) + arg0.substring(18)), 16)', userId)
Basically need to do this ahead of time in the 
transformConfig
of a REALTIME table
Actually, this limitation doesn't seem to exist anymore And I managed to use an already transformed field ! 🎉
m
Thanks @Jonathan Meyer, could you paste an example that worked for you? We will fix the doc, cc @Mark Needham @Dunith Dhanushka
j
Sure, here it is:
Copy code
"ingestionConfig": {
    "transformConfigs": [
      {
        "columnName": "userOid",
        "transformFunction": "jsonPathString(data, '$.userId')"
      },
      {
        "columnName": "userId",
        "transformFunction": "Groovy({Long.valueOf((userOid.substring(0, 8) + userOid.substring(18)), 16)}, userOid)"
      }
   ]
}
As we can see, to populate 
userId
we use column 
userOid
which is itself the output of a transformation
Note that we are using 
0.7.1-afa4b252ab1c424ddd6c859bb305b2aa342b66ed
And I haven't tested any other combinations of 
transformConfigs
, so maybe some limitations still apply (e.g. order in which they are listed, maybe ?)
n
No limitations should apply.. we added support for chaining, early this year. Thanks for pointing out!
🙌 1
j
Fantastic news, thanks for that feature, saved my day today 😄
m
Hey @Jonathan Meyer, docs are now updated based on your example - https://docs.pinot.apache.org/developers/advanced/ingestion-level-transformations#chaining-transformations Thanks 😄
j
Nice ! 😄
m
@Jonathan Meyer quick question - in your example did you have to map 
data
as a field in the schema as well? Or is it only a field in the source data?
j
data is an object in the Kafka message Basically looks like this
Copy code
{
  "some_field": ...,
  "data": {
     "userId": ...
  }
}
m
got it. the reason I ask is that I'm trying to do a CSV import where the headers contain spaces
and I'm wondering could I use a transform function to map to schema field names that don't have any spaces
j
Not sure I get it, you're trying to ingest a CSV file with headers like "column name with space" and map that to other columns in Pinot ?
m
you can't have spaces in columns in Pinot
so 'field with space' --> 'fieldwithspace'
is what I want to do
j
I see Yeah, sounds possible to do by listing every column manually If you wanted it to be automatized, I'm not sure it's possible but I'm far from being an expert on the topic Check the section on "Renaming column" in Pinot Ingestion transform docs (on phone, not practical to check sorry ^^)
m
manual would be fine
Copy code
"ingestionConfig": {
    "transformConfigs": [{
      "columnName": "userId",
      "transformFunction": "Groovy({user_id}, user_id)" 
    }
}
how would I pass through the column name with spaces in groovy land?
j
Oh, right I see Is it not possible to pass a column with single quotes or escaped double quotes ?
m
I think I tried that and the column was ending up as null. I need to read a bit more how the transform stuff works
maybe I can figure it out
j
Couldn't try today, have you found a way ?
m
j
Cool ! Your example makes me wonder if the above one can't be simplified and not use Groovy then https://docs.pinot.apache.org/developers/advanced/ingestion-level-transformations#column-name-change
Like so ?
Copy code
"ingestionConfig": {
    "transformConfigs": [{
      "columnName": "userId",
      "transformFunction": "user_id" 
    }]
}
m
yeh I think you're right, that would be way simpler. I wrote up what I was trying to do with the columns with spaces and the steps along the way -- https://www.markhneedham.com/blog/2021/11/25/apache-pinot-csv-columns-spaces/
👍 1
j
Nice article @Mark Needham 🙂
➕ 1
Hello @Mark Needham @Mayank @Neha Pawar (sorry for pinging you explicitly, thought it was better to continue this thread) Was there any change related to 
transformConfigs
+ 
Groovy
expressions in the recent releases ? We've bumped from 0.7.1 to 0.9.0 with the following configuration on REALTIME table
Copy code
"ingestionConfig": {
   "transformConfigs": [
    {
        "columnName": "userOid",
        "transformFunction": "jsonPathString(data, '$.userId')"
      },
      {
        "columnName": "userId",
        "transformFunction": "Groovy({Long.valueOf((userOid.substring(0, 8) + userOid.substring(18)), 16)}, userOid)"
      },
   ]
   }
}
This used to work, now raises 
Caught exception while transforming the record
Caused by: java.lang.NumberFormatException: For input string: "6049f2c188d36401807cf526"
java.lang.RuntimeException: Caught exception while transforming data type for column: userId
It looks like the Groovy expression is not evaluated, as 
userOid
is passed straight to 
userId
without transformation
(& @Neha Pawar maybe ?)
m
so the chaining seems to not be working basically?
j
I think it is, since it is saying the type isn't valid (STRING in place of LONG) and printing the value from the "dependable" column - the chaining seems ok then
Nevermind, looks like some other stuff made the table break right before the Pinot migration, sorry for the disturbance
m
No worries
Open in Slack
PreviousNext
Powered by