· kafka ksql

KSQL: Create Stream - extraneous input 'properties'

In my continued playing with the KSQL streaming engine for Kafka, I came across another interesting error while trying to put a stream on top of a topic generated by the Neo4j Streams Library.

We’ll simplify the events being posted on the topic for this blog post, so this is what the events on the topic look like:

{
   "id":"ABCDEFGHI",
   "properties": {
      "name":"Mark",
      "location":"London"
   }
}

We then create a stream on that topic:

CREATE STREAM users_original(
  id varchar,
  properties STRUCT<
    name varchar,
    location varchar
  >
)
WITH(KAFKA_TOPIC='users', value_format='json');

But when we execute that query we’ll get the following exception:

line 3:3: extraneous input 'properties' expecting {'INTEGER', 'DATE', 'TIME', 'TIMESTAMP', 'INTERVAL', 'YEAR', 'MONTH', 'DAY', 'HOUR', 'MINUTE', 'SECOND', 'ZONE', 'PARTITION', 'STRUCT', 'EXPLAIN', 'ANALYZE', 'TYPE', 'SHOW', 'TABLES', 'COLUMNS', 'COLUMN', 'PARTITIONS', 'FUNCTIONS', 'FUNCTION', 'ARRAY', 'MAP', 'SET', 'RESET', 'SESSION', 'IF', IDENTIFIER, DIGIT_IDENTIFIER, QUOTED_IDENTIFIER, BACKQUOTED_IDENTIFIER}
Statement: CREATE STREAM users_original(

I thought I might have a trailing comma or colon - two mistakes I’ve made before - but on this occassion the problem is that properties is a reserved word in KSQL. Luckily it’s an easy problem to fix:

If the name of a column in your source topic is one of the reserved words in KSQL you can use back quotes to define the column. The same applies to the field names in a STRUCT type. For instance, if in the above example we had another field called Properties, which is a reserved word in KSQL, you can use the following statement to declare your stream:

— KSQL Syntax Reference

The fixed syntax is below:

CREATE STREAM users_original(
  id varchar,
  `properties` STRUCT<
    name varchar,
    location varchar
  >
)
WITH(KAFKA_TOPIC='users', value_format='json');

And if we run that the stream will be created:

Message
----------------
Stream created
----------------
  • LinkedIn
  • Tumblr
  • Reddit
  • Google+
  • Pinterest
  • Pocket