Unexpected token "@"

[xmlking]

ksql> CREATE STREAM my_logs_stream  ( "@timestamp" varchar, "@version" bigint, message 
varchar, logger_name varchar, thread_name varchar, level varchar, level_value bigint, HOSTNAME 
varchar, app varchar) WITH (kafka_topic='applogs', value_format='JSON');

SELECT app,  HOSTNAME, "@version" FROM my_logs_stream LIMIT 3;

Error:

Line 1, Column 17: Unexpected token "@"

unfortunately my JSON data looks like this

{  
   "@timestamp":"2017-08-30T06:48:05.149+00:00",
   "@version":1,
   "message":"cancel()",
   "logger_name":"ss-QuoteGenerator",
   "thread_name":"reactor-http-nio-4",
   "level":"INFO",
   "level_value":20000,
   "HOSTNAME":"weqeqe",
   "app":"stream-service"
}

[hjafarpour]

@xmlking The columns names cannot start with '@'. Can you change the field names in your JSON so they won't start with '@'? I would suggest '_timestamp' and '_version'.

[xmlking]

I can certainly change it. KSQL is amazing component in Kafka ecosystem . Thanks.

As logstash by default use this JSON format, and it is valid JSON, wonder if we can remove this limitation in KSQL.
https://github.com/logstash/logstash-logback-encoder#standard-fields

[miguno]

@xmlking Thanks for reporting this. I agree that it would be great to support @... named columns in KSQL in order to support logstash with default settings, but @ is not commonly a valid character in SQL identifiers. Hence we are hesitant at the moment to deviate from that.

[henridf]

Fwiw this is the first thing that I ran into when using KSQL to do ETL on Elasticsearch-destined data recently... (it's broader than logstash, which I don't use... @timestamp seems to have become a pretty general convention in ES-land).

I do understand the quandary with deviating from SQL. My 2c would be that "loosening" the syntax (e.g. making things that aren't valid SQL be valid kSQL) is ok. But your call of course.

[miguno]

Thanks for the feedback @henridf -- we're still thinking about this, please stay tuned.

Have you been able to come up with a workaround in the meantime?

[henridf]

I since ran into some other (more significant) issues with ksql and as a result have put this aside for the time being. So, I haven't needed to find a workaround for this.

I'll file the other issues as soon as I have time to do a simple repro. In any case, what little I was able to do was great, and I look forward to seeing this project grow. Thanks!

[miguno]

Thanks for the update @henridf. Appreciate the kind words! And please keep the feedback coming! :-)

[bluemonk3y]

@henridf - the clickstream demo uses a dynamic-template to configure the timestamp fields, it should provide you with a workaround, then you can use a _timestamp field as the demo does.

Take a look at https://github.com/confluentinc/ksql/blob/0.1.x/ksql-clickstream-demo/demo/elastic-dynamic-template.sh

[apurvam]

Fixed by #581