https://pinot.apache.org/ logo
Channels
Powered by
#troubleshooting
Title
# troubleshooting
f

francoisa

03/31/2022, 12:54 PM
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

Mark Needham

03/31/2022, 3:00 PM
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

francoisa

03/31/2022, 3:02 PM
yes. My only goal is to rename data.type to type. Working like a charm if no unnest. Crash null values if unnest
m

Mark Needham

03/31/2022, 3:02 PM
oh I see
f

francoisa

03/31/2022, 3:04 PM
Plus some other modification on transformConfig to parseDateToTimestamp etc ... but all return null 😕 Like if the field does not exist
m

Mark Needham

03/31/2022, 3:04 PM
Copy code
"ingestionConfig": {
      "transformConfigs": [
        {
          "columnName": "type",
          "transformFunction": "\"data.type\""
        }
      ],
      "complexTypeConfig": {
        "fieldsToUnnest": [
          "data.attributes.actualExpenses"
        ],
        "delimiter": "."
      }
    },
give this a try
f

francoisa

03/31/2022, 3:08 PM
This is working 🙂
So I have a “” that I need to proctect 🙂
m

Mark Needham

03/31/2022, 3:09 PM
you might not need the inside quotes. I put them there just in case
f

francoisa

03/31/2022, 3:10 PM
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

Mark Needham

03/31/2022, 3:52 PM
that column definitely exists?
f

francoisa

03/31/2022, 3:52 PM
yes
message has been deleted
m

Mark Needham

03/31/2022, 3:56 PM
hmmm, not sure
f

francoisa

03/31/2022, 4:00 PM
not sure of ? The existence of the column ?
m

Mark Needham

03/31/2022, 4:00 PM
not sure why it's not working!
f

francoisa

03/31/2022, 4:01 PM
ah 🙂
m

Mark Needham

03/31/2022, 4:01 PM
@User might know
m

Mayank

03/31/2022, 4:53 PM
Are these two separate columns or is this a nested JSON column? If latter, you probably want to use JSON functions.
f

francoisa

03/31/2022, 6:21 PM
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

Mayank

03/31/2022, 7:07 PM
Wait, are you trying to rename a column of an existing table? That would be a backward incompatible change, and is not allowed.
f

francoisa

03/31/2022, 7:31 PM
Nope brand new table 😉
m

Mayank

03/31/2022, 9:37 PM
@User can we chain flatten and then rename of column today?
j

Jackie

03/31/2022, 10:11 PM
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

francoisa

04/01/2022, 6:57 AM
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

Mayank

04/01/2022, 6:22 PM
Hmm, is this expected @User?
j

Jackie

04/01/2022, 6:30 PM
No, this is unexpected
The column name should match schema though
f

francoisa

04/01/2022, 8:09 PM
Other thing found null date in Fromdatetime throw null pointer exception ( fix in master 10days ago I will try to upgrade to this branch )
Powered by