i have a ksqldb table without a defined time field how can i Apache Pinot #getting-started

i have a ksqldb table without a defined time field...

Larry Meadors

11/03/2022, 12:53 PM

i have a ksqldb table without a defined time field, how can i create a table in pinot that represents that? i tried defining the schema like this, but when i tried to create the table, it fails (message below):

Copy code

{
  "schemaName": "ecommerce_customer",
  "dimensionFieldSpecs": [
    {
      "name": "NAME",
      "dataType": "STRING"
    },
    {
      "name": "SAPID",
      "dataType": "STRING"
    },
    {
      "name": "COUNTRYID",
      "dataType": "LONG"
    },
    {
      "name": "ENABLED",
      "dataType": "BOOLEAN"
    }
  ]
}

creating a table for this causes this error:

Copy code

{
  "_code": 400,
  "_error": "Cannot find valid fieldSpec for timeColumn: rowtime from the table config: ecommerce_customer_REALTIME, in the schema: ecommerce_customer"
}

which kinda makes sense, i think - i just don't know how to define that time column (yet) - any tips?

Larry Meadors

11/03/2022, 1:05 PM

i tried adding this to the schema:

Copy code

"dateTimeFieldSpecs": [
        {
            "name": "rowtime",
            "dataType": "TIMESTAMP",
            "format": "1:MILLISECONDS:EPOCH",
            "granularity": "1:MILLISECONDS"
        }
    ]

that didn't work - i get a pile of NPEs reading the data because i'm getting back

Copy code

{
  "fieldToValueMap": {
    "rowtime": null,
    "COUNTRYID": 26,
    "ENABLED": 0,
    "SAPID": "100000",
    "NAME": "your name here"
  },
  "nullValueFields": [
    "ENABLED"
  ]
}

Larry Meadors

11/03/2022, 1:09 PM

tried

"ROWTIME"

to see if it was just a case issue...nope 😕

Larry Meadors

11/03/2022, 1:40 PM

i think i got this, and maybe it's one of those "well, of course, you dope" kinda things...but i added a timestamp to the source data

Kishore G

11/03/2022, 2:20 PM

You can use now() transform function for this column - see transform configs .. this will set the current ingestion time as the value

Larry Meadors

11/03/2022, 2:22 PM

oh, ok - i will look at that later today, i have not messed with transforms yet (but did read some about them yesterday)

Larry Meadors

11/03/2022, 2:22 PM

thanks!

Kishore G

11/03/2022, 2:24 PM

👍

Mark Needham

11/07/2022, 12:59 PM

https://dev.startree.ai/docs/pinot/recipes/datetime-string-to-timestamp#add-schema-and-table If you follow this guide and set:

Copy code

"transformConfigs":[
      {"columnName":"rowtime", "transformFunction":"now()"}
    ]

👍 1

Open in Slack

Previous Next