You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@pinot.apache.org by GitBox <gi...@apache.org> on 2021/08/10 18:25:02 UTC
[GitHub] [pinot] mbecker opened a new issue #7276: Ingest and query time in RFC3339
mbecker opened a new issue #7276:
URL: https://github.com/apache/pinot/issues/7276
Hi,
I'm having a time format in the Kafka Messages as follows:
```sh
"_time": "2021-08-10T07:08:08.873Z",
```
I'm using the following schema for the date:
```json
"dateTimeFieldSpecs": [
{
"name": "_time",
"dataType": "STRING",
"format": "1:MILLISECONDS:SIMPLE_DATE_FORMAT:yyyy-MM-dd'T'HH:mm:ss.SSS'Z'",
"granularity": "1:MINUTES"
}
],
```
(I'm using a Java format here)
How can I check that the ingest in Pinot is working with the specified date time field?
Then in a Presto SQL the DATETIMECONVERT doesn't work as expected and returns an error:
```sql
SELECT DATETIMECONVERT("_time", '1:MILLISECONDS:SIMPLE_DATE_FORMAT:yyyy-mm-ddTHH:MM:SS.%fZ', '1:DAYS:SIMPLE_DATE_FORMAT:yyyyMMdd', '1:DAYS'),
max(value_number) AS max_1
FROM abc.xyz
LIMIT 1000;
```
(Here I'm using a python format? as described in superset)
The returned error is as follows:
```sh
pinot error: must be real number, not dict
```
How can I use the column "_time" with the RFC 3339 format as the date time field in Presto / Superset?
Thanks for your held and cheers!
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For additional commands, e-mail: commits-help@pinot.apache.org
[GitHub] [pinot] Jackie-Jiang commented on issue #7276: Ingest and query time in RFC3339
Posted by GitBox <gi...@apache.org>.
Jackie-Jiang commented on issue #7276:
URL: https://github.com/apache/pinot/issues/7276#issuecomment-898766174
The `TIMESTAMP` data type always use `yyyy-MM-dd HH:mm:ss.SSS`, but this won't avoid the time format conversion if we need the result to be in another format.
@mbecker From the Presto SQL query, can you try setting the input format (second argument) to match the field spec format?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For additional commands, e-mail: commits-help@pinot.apache.org
[GitHub] [pinot] mayankshriv commented on issue #7276: Ingest and query time in RFC3339
Posted by GitBox <gi...@apache.org>.
mayankshriv commented on issue #7276:
URL: https://github.com/apache/pinot/issues/7276#issuecomment-899900064
@mbecker If you are just trying to specify if the event is ingested in Pinot correctly and the time format is store correctly in Pinot, then you can simply run a pinot query to do so:
```
select _time from <tableName>
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For additional commands, e-mail: commits-help@pinot.apache.org
[GitHub] [pinot] mayankshriv edited a comment on issue #7276: Ingest and query time in RFC3339
Posted by GitBox <gi...@apache.org>.
mayankshriv edited a comment on issue #7276:
URL: https://github.com/apache/pinot/issues/7276#issuecomment-899900064
@mbecker If you are just trying to validate if the event is ingested in Pinot correctly and the time format is store correctly in Pinot, then you can simply run a pinot query to do so:
```
select _time from <tableName>
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For additional commands, e-mail: commits-help@pinot.apache.org
[GitHub] [pinot] mbecker commented on issue #7276: Ingest and query time in RFC3339
Posted by GitBox <gi...@apache.org>.
mbecker commented on issue #7276:
URL: https://github.com/apache/pinot/issues/7276#issuecomment-899009091
Hi,
thanks for your help!
Sorry, in my original post I mixed some tools like using for example "Presto" (vs. only "Superset").
So, my original use case is that the Kafka Message has this attribute "time" with the RFC 3339 format. I'm ingesting it as a string type and creating the "Pinot" table as described above. In "Superset" I'm defining the dataset and setting the attribute "time" as a time-field with the DATETIME format as described above and with the new format `yyyy-MM-dd HH:mm:ss.SSS` as commented by @Jackie-Jiang .
<img width="574" alt="Screenshot 2021-08-15 at 09 26 15" src="https://user-images.githubusercontent.com/26771/129470575-161807a6-4b74-4fc3-93c2-55f487331f36.png">
Because this is not the repo for "Superset", I'm just asking how to check that the ingestion and the table-format with a STRING-field for the `dateTimeFieldSpecs` in Pinot is correct :-)
Of course, any other help would be great too ;-)
Thank you very much.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For additional commands, e-mail: commits-help@pinot.apache.org
[GitHub] [pinot] xiangfu0 commented on issue #7276: Ingest and query time in RFC3339
Posted by GitBox <gi...@apache.org>.
xiangfu0 commented on issue #7276:
URL: https://github.com/apache/pinot/issues/7276#issuecomment-898665254
@Jackie-Jiang @npawar shall we consider providing built-in formats to avoid those date format conversion difficulties?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For additional commands, e-mail: commits-help@pinot.apache.org