https://pinot.apache.org/ logo
Join Slack
Powered by
if i use a reserve keyword like timestamp as a col...
# troubleshooting
l
if i use a reserve keyword like timestamp as a column name can I still query stuff? if so how would i do it?
m
SQL style escape should work.
l
Copy code
select * from query_metrics_dev 
where `timestamp`=0 limit 10
like that?
cause that doesn’t seem to work
m
Are you using back-tick or single quote?
l
back-tick
m
Can you try single quote?
l
that doesn’t work
Copy code
select timestamp from query_metrics_dev limit 10
showing you the original error
Copy code
ProcessingException(errorCode:150, message:PQLParsingError:
org.apache.pinot.sql.parsers.SqlCompilationException: Caught exception while parsing query: select timestamp from query_metrics_dev limit 10
	at org.apache.pinot.sql.parsers.CalciteSqlParser.compileCalciteSqlToPinotQuery(CalciteSqlParser.java:334)
	at org.apache.pinot.sql.parsers.CalciteSqlParser.compileToPinotQuery(CalciteSqlParser.java:133)
	at org.apache.pinot.sql.parsers.CalciteSqlCompiler.compileToBrokerRequest(CalciteSqlCompiler.java:35)
	at org.apache.pinot.controller.api.resources.PinotQueryResource.getQueryResponse(PinotQueryResource.java:166)
...
Caused by: org.apache.calcite.sql.parser.SqlParseException: Encountered "timestamp from" at line 1, column 8.
Was expecting one of:
    "/*+" ...
    "/*+" "UNION" ...
    "/*+" "INTERSECT" ...
...
Caused by: org.apache.calcite.sql.parser.babel.ParseException: Encountered "timestamp from" at line 1, column 8.
Was expecting one of:
    "/*+" ...
    "/*+" "UNION" ...
    "/*+" "INTERSECT" ...)
m
Ok, single quote is literal, try double quote
l
that works for selection
as in in the select
Copy code
select * from query_metrics_dev 
where "timestamp"=0 limit 10
but if i do this i don’t see anything
m
Does that give compilation error, or empty result? If empty result, try setting it to a value that selection returned.
l
Copy code
select "timestamp" as a, user_id from query_metrics_dev
where a=0 limit 10
returns empty
m
No I am saying change the predicate
“timestamp = x” where. is a value that 
select "timestamp"
returns
l
sorry i don’t think i’m understanding like a subselect after the = sign?
0 is a value that the query returns that’s why i’m doing =0
Copy code
timestamp	user_id
0	32086606
0	16806149
m
hmm
try 
where "timestamp" != 0
cc: @Jackie
l
oh wow
that worked
tf
but like
what
is it cause is null or something like that but not 0?
m
Have you enabled default null value?
l
no, i was just testing cause timestamp is not supposed to be null, but we are still developing this app
but that’s why then
j
Do you use 
timestamp
column as time column?
l
yes
j
Which version of pinot are you running?
l
0.10.0
j
Is 
timestamp
a 
LONG
field?
l
that is right
j
FYI, we shouldn't allow 
0
as the time value because it is not within the valid time range (Pinot allows time from 1971 so that we can catch wrong time config)
Which query returns the 0 timestamp?
l
ooooo
i see
yeah yeah that makes sense to me, in reality this shouldn’t be a thing timestamp should be a real timestamp and always populated
i guess this is more for my own education now
j
Oh, so do you explicitly ingest timestamp value 0? Or you got it from querying the table?
l
i think that at the moment we are not setting the timestamp but the table is showing it as 0 i guess cause it’s a long value
so yea we are getting it from querying the table
j
Hmm, it should not be filled with 0 if it is configured as a time column
Can you share the table config you are using to set up the table?
Table config and schema
l
schema:
Copy code
{
  "schemaName": "query_metrics_dev",
  "dimensionFieldSpecs": [
    {
      "name": "user_id",
      "dataType": "LONG"
    },
    {
      "name": "product_id",
      "dataType": "LONG"
    },
    {
      "name": "title_phrases",
      "dataType": "STRING",
      "singleValueField": false
    },
    {
      "name": "tags",
      "dataType": "STRING",
      "singleValueField": false
    },
    {
      "name": "stemmed_query",
      "dataType": "STRING"
    }
  ],
  "metricFieldSpecs": [
    {
      "name": "impression_count",
      "dataType": "INT"
    },
    {
      "name": "click_count",
      "dataType": "INT"
    },
    {
      "name": "order_count",
      "dataType": "INT"
    }
  ],
  "dateTimeFieldSpecs": [
    {
      "name": "timestamp",
      "dataType": "LONG",
      "format": "1:SECONDS:EPOCH",
      "granularity": "1:HOURS"
    }
  ]
}
table:
Copy code
{
  "REALTIME": {
    "tableName": "query_metrics_dev_REALTIME",
    "tableType": "REALTIME",
    "segmentsConfig": {
      "schemaName": "query_metrics_dev",
      "retentionTimeUnit": "DAYS",
      "retentionTimeValue": "2",
      "replication": "1",
      "timeColumnName": "timestamp",
      "allowNullTimeValue": false,
      "replicasPerPartition": "1"
    },
    "tenants": {
      "broker": "DefaultTenant",
      "server": "DefaultTenant",
      "tagOverrideConfig": {
        "realtimeCompleted": "DefaultTenant_OFFLINE"
      }
    },
    "tableIndexConfig": {
      "invertedIndexColumns": [],
      "noDictionaryColumns": [
        "click_count",
        "order_count",
        "impression_count",
        "tags",
        "title_phrases",
        "stemmed_query"
      ],
      "streamConfigs": {
        "streamType": "kafka",
        "stream.kafka.topic.name": "topic",
        "stream.kafka.broker.list": "kafka-broker",
        "stream.kafka.consumer.type": "lowlevel",
        "stream.kafka.consumer.prop.auto.offset.reset": "largest",
        "stream.kafka.consumer.factory.class.name": "org.apache.pinot.plugin.stream.kafka20.KafkaConsumerFactory",
        "stream.kafka.decoder.class.name": "org.apache.pinot.plugin.stream.kafka.KafkaJSONMessageDecoder",
        "realtime.segment.flush.threshold.rows": "0",
        "realtime.segment.flush.threshold.time": "24h",
        "realtime.segment.flush.segment.size": "250M"
      },
      "rangeIndexColumns": [
        "timestamp"
      ],
      "rangeIndexVersion": 2,
      "autoGeneratedInvertedIndex": false,
      "createInvertedIndexDuringSegmentGeneration": false,
      "sortedColumn": [
        "user_id"
      ],
      "bloomFilterColumns": [
        "user_id",
        "product_id"
      ],
      "loadMode": "MMAP",
      "onHeapDictionaryColumns": [],
      "varLengthDictionaryColumns": [],
      "enableDefaultStarTree": false,
      "enableDynamicStarTreeCreation": false,
      "aggregateMetrics": true,
      "nullHandlingEnabled": false
    },
    "metadata": {},
    "quota": {},
    "task": {
      "taskTypeConfigsMap": {
        "RealtimeToOfflineSegmentsTask": {
          "bucketTimePeriod": "1d",
          "bufferTimePeriod": "2d",
          "roundBucketTimePeriod": "1h",
          "mergeType": "concat",
          "maxNumRecordsPerSegment": "5000000"
        }
      }
    },
    "query": {},
    "fieldConfigList": [],
    "ingestionConfig": {},
    "isDimTable": false
  }
}
j
Copy code
"timeColumnName": "timestamp",
      "allowNullTimeValue": false,
I don't know how the 
0
timestamp is ingested, it should throw exception
l
we are not setting it at the moment so nothing is really sending it
like the data being produced has not timestamp
that later pinot consumes
j
Yeah, data without 
timestamp
column is the same as using 
null
as the value
In the current master (will be released as 
0.11.0
), if timestamp is not provided, we will fill it with the ingestion timestamp
l
oh nice thank you for the info
Open in Slack
PreviousNext
Powered by