https://pinot.apache.org/ logo
Join Slack
Powered by
Hi :slightly_smiling_face: I’ve a misunderstanding...
# troubleshooting
f
Hi 🙂 I’ve a misunderstanding on something that give me a lot of trouble. I’ve a neasted array on my JSON. I’ve managed to flaten it using the complexTypeConfig. Sound all good and generate me all row with correct values. But I’m tring to rename the genarated cols using eiter a groovy function or a JSON path string but none of them are working 😞 Ingestion.
Copy code
"ingestionConfig": {
      "transformConfigs": [
        {
          "columnName": "type",
          "transformFunction": "JSONPATHSTRING(data,'$.type')"
        }
      ],
      "complexTypeConfig": {
        "fieldsToUnnest": [
          "data.attributes.actualExpenses"
        ],
        "delimiter": "."
      }
    },
Schema is already defined with type STRING
Output
I’ve also tried with groovy function same result 😞
I’ve the strange feeling of an ordering of the transformConfig based on someting like transformConfigs -> complexTypeConfig. So all my transformConfig are useless and return null values. I’m I right ?
m
Do you have a field called 'data' in the schema?
I think that's what the transform fn is trying to do - read 
.type
from 
data
f
yes. My only goal is to rename data.type to type. Working like a charm if no unnest. Crash null values if unnest
m
oh I see
f
Plus some other modification on transformConfig to parseDateToTimestamp etc ... but all return null 😕 Like if the field does not exist
m
Copy code
"ingestionConfig": {
      "transformConfigs": [
        {
          "columnName": "type",
          "transformFunction": "\"data.type\""
        }
      ],
      "complexTypeConfig": {
        "fieldsToUnnest": [
          "data.attributes.actualExpenses"
        ],
        "delimiter": "."
      }
    },
give this a try
f
This is working 🙂
So I have a “” that I need to proctect 🙂
m
you might not need the inside quotes. I put them there just in case
f
ok thank you for your help 🙂
This works like a charm but lead to another issue maybe linked.
Copy code
Select * from mytable
Perfect it works
Copy code
Select "mycol.otherName" from my table
Throws
Copy code
[
  {
    "message": "UnknownColumnError:\norg.apache.pinot.spi.exception.BadQueryRequestException: Unknown columnName 'actualExpenses.activityType' found in the query\n\tat org.apache.pinot.broker.requesthandler.BaseBrokerRequestHandler.getActualColumnName(BaseBrokerRequestHandler.java:1604)\n\tat org.apache.pinot.broker.requesthandler.BaseBrokerRequestHandler.fixColumnName(BaseBrokerRequestHandler.java:1538)\n\tat org.apache.pinot.broker.requesthandler.BaseBrokerRequestHandler.updateColumnNames(BaseBrokerRequestHandler.java:1421)\n\tat org.apache.pinot.broker.requesthandler.BaseBrokerRequestHandler.handleSQLRequest(BaseBrokerRequestHandler.java:254)",
    "errorCode": 710
  }
]
m
that column definitely exists?
f
yes
image.png,image.png
m
hmmm, not sure
f
not sure of ? The existence of the column ?
m
not sure why it's not working!
f
ah 🙂
m
@User might know
m
Are these two separate columns or is this a nested JSON column? If latter, you probably want to use JSON functions.
f
Two separates columns but all on the same neasted structure inside data.attributes.actualExpenses. By json function you mean ? I need to aggregate a few meyrics stored in this structure ( 30 row per paylod)
m
Wait, are you trying to rename a column of an existing table? That would be a backward incompatible change, and is not allowed.
f
Nope brand new table 😉
m
@User can we chain flatten and then rename of column today?
j
Yes, flatten happens first before other transforms
@User Can you show the raw json response for the 
select *
query? Want to make sure the column name matches
f
Here it is. I will try to flaten to raw JSON and then try transformations to see if it works.
Same transformation output into a column without ‘.’ works like a charm
SOLUTION : pinot does not like mixedCase. I have to put everything in lowercase “actualexpenses.project.reference” -> OK // “actualExpenses.project.reference” -> KO
m
Hmm, is this expected @User?
j
No, this is unexpected
The column name should match schema though
f
Other thing found null date in Fromdatetime throw null pointer exception ( fix in master 10days ago I will try to upgrade to this branch )
Open in Slack
PreviousNext
Powered by