Document how to use quoted column names and column aliases

[big-andy-coates]

Users may have data where field names contain reserved words, e.g. group. Currently, there is no documentation on how to work with this data with KSQL. Statements such as CREATE STREAM foo (field1 varchar, group varchar ) WITH (KAFKA_TOPIC='foo', VALUE_FORMAT='JSON'); fail with an error due to the group column.

I think the work around is to use the backtick character to escape the keyword:

CREATE STREAM foo (field1 varchar, `group` column) WITH (Blah)

And then the user will need to also escape the column name where ever used, e.g.

SELECT `group` FROM foo LIMIT 5;

However, there is no documentation around column aliasing and how to use with keywords. We should fix this.

[big-andy-coates]

Looking at #1212, there may be bugs in this area!

[rmoff]

Same issue here, for properties: https://stackoverflow.com/questions/50906566/cannot-create-stream-with-ksql/50907284#50907284

ksql> CREATE STREAM fakeData22 (Id VARCHAR, category VARCHAR, timeStamp VARCHAR, deviceID INTEGER, properties MAP<VARCHAR, VARCHAR>) WITH (KAFKA_TOPIC='fake-data-19', VALUE_FORMAT='JSON');

line 1:94: extraneous input 'properties' expecting {'ADD', 'APPROXIMATE', 'AT', 'CONFIDENCE', 'NO', 'SUBSTRING', 'POSITION', 'TINYINT', 'SMALLINT', 'INTEGER', 'DATE', 'TIME', 'TIMESTAMP', 'INTERVAL', 'YEAR', 'MONTH', 'DAY', 'HOUR', 'MINUTE', 'SECOND', 'ZONE', 'OVER', 'PARTITION', 'RANGE', 'ROWS', 'PRECEDING', 'FOLLOWING', 'CURRENT', 'ROW', 'VIEW', 'REPLACE', 'GRANT', 'REVOKE', 'PRIVILEGES', 'PUBLIC', 'OPTION', 'EXPLAIN', 'ANALYZE', 'FORMAT', 'TYPE', 'TEXT', 'GRAPHVIZ', 'LOGICAL', 'DISTRIBUTED', 'TRY', 'SHOW', 'TABLES', 'SCHEMAS', 'CATALOGS', 'COLUMNS', 'COLUMN', 'USE', 'PARTITIONS', 'FUNCTIONS', 'TO', 'SYSTEM', 'BERNOULLI', 'POISSONIZED', 'TABLESAMPLE', 'RESCALED', 'ARRAY', 'MAP', 'SET', 'RESET', 'SESSION', 'DATA', 'START', 'TRANSACTION', 'COMMIT', 'ROLLBACK', 'WORK', 'ISOLATION', 'LEVEL', 'SERIALIZABLE', 'REPEATABLE', 'COMMITTED', 'UNCOMMITTED', 'READ', 'WRITE', 'ONLY', 'CALL', 'NFD', 'NFC', 'NFKD', 'NFKC', 'IF', 'NULLIF', 'COALESCE', IDENTIFIER, DIGIT_IDENTIFIER, QUOTED_IDENTIFIER, BACKQUOTED_IDENTIFIER}
Caused by: line 1:94: extraneous input 'properties' expecting {'ADD', 'APPROXIMATE', 'AT', 'CONFIDENCE', 'NO', 'SUBSTRING', 'POSITION', 'TINYINT', 'SMALLINT', 'INTEGER', 'DATE', 'TIME', 'TIMESTAMP', 'INTERVAL', 'YEAR', 'MONTH', 'DAY', 'HOUR', 'MINUTE', 'SECOND', 'ZONE', 'OVER', 'PARTITION', 'RANGE', 'ROWS', 'PRECEDING', 'FOLLOWING', 'CURRENT', 'ROW', 'VIEW', 'REPLACE', 'GRANT', 'REVOKE', 'PRIVILEGES', 'PUBLIC', 'OPTION', 'EXPLAIN', 'ANALYZE', 'FORMAT', 'TYPE', 'TEXT', 'GRAPHVIZ', 'LOGICAL', 'DISTRIBUTED', 'TRY', 'SHOW', 'TABLES', 'SCHEMAS', 'CATALOGS', 'COLUMNS', 'COLUMN', 'USE', 'PARTITIONS', 'FUNCTIONS', 'TO', 'SYSTEM', 'BERNOULLI', 'POISSONIZED', 'TABLESAMPLE', 'RESCALED', 'ARRAY', 'MAP', 'SET', 'RESET', 'SESSION', 'DATA', 'START', 'TRANSACTION', 'COMMIT', 'ROLLBACK', 'WORK', 'ISOLATION', 'LEVEL', 'SERIALIZABLE', 'REPEATABLE', 'COMMITTED', 'UNCOMMITTED', 'READ', 'WRITE', 'ONLY', 'CALL', 'NFD', 'NFC', 'NFKD', 'NFKC', 'IF', 'NULLIF', 'COALESCE', IDENTIFIER, DIGIT_IDENTIFIER, QUOTED_IDENTIFIER, BACKQUOTED_IDENTIFIER}

[rmoff]

The backtick approach doesn't work for me - whilst KSQL accepts the CREATE STREAM statement, it doesn't actually pick up the desired column.

ksql> print 'reserved_test2' from beginning;
Format:JSON
{"ROWTIME":1529579689725,"ROWKEY":"null","group":"foo","properties":"bar"}
{"ROWTIME":1529579853969,"ROWKEY":"null","group":"foo","groupfoo":"bar"}
^CTopic printing ceased
ksql> CREATE STREAM reservedtest20 ("group" VARCHAR, groupfoo VARCHAR, "groupfoo" varchar) WITH (KAFKA_TOPIC='reserved_test2', VALUE_FORMAT='JSON');

 Message
----------------
 Stream created
----------------
ksql> SELECT * FROM reservedtest20;
1529579689725 | null | null | null | null
1529579853969 | null | null | bar | null
^CQuery terminated
ksql> CREATE STREAM reservedtest201 (GROUP VARCHAR, "group" VARCHAR, groupfoo VARCHAR, "groupfoo" varchar) WITH (KAFKA_TOPIC='reserved_test2', VALUE_FORMAT='JSON');
line 1:32: extraneous input 'GROUP' expecting {'ADD', 'APPROXIMATE', 'AT', 'CONFIDENCE', 'NO', 'SUBSTRING', 'POSITION', 'TINYINT', 'SMALLINT', 'INTEGER', 'DATE', 'TIME', 'TIMESTAMP', 'INTERVAL', 'YEAR', 'MONTH', 'DAY', 'HOUR', 'MINUTE', 'SECOND', 'ZONE', 'OVER', 'PARTITION', 'RANGE', 'ROWS', 'PRECEDING', 'FOLLOWING', 'CURRENT', 'ROW', 'STRUCT', 'VIEW', 'REPLACE', 'GRANT', 'REVOKE', 'PRIVILEGES', 'PUBLIC', 'OPTION', 'EXPLAIN', 'ANALYZE', 'FORMAT', 'TYPE', 'TEXT', 'GRAPHVIZ', 'LOGICAL', 'DISTRIBUTED', 'TRY', 'SHOW', 'TABLES', 'SCHEMAS', 'CATALOGS', 'COLUMNS', 'COLUMN', 'USE', 'PARTITIONS', 'FUNCTIONS', 'TO', 'SYSTEM', 'BERNOULLI', 'POISSONIZED', 'TABLESAMPLE', 'RESCALED', 'ARRAY', 'MAP', 'SET', 'RESET', 'SESSION', 'DATA', 'START', 'TRANSACTION', 'COMMIT', 'ROLLBACK', 'WORK', 'ISOLATION', 'LEVEL', 'SERIALIZABLE', 'REPEATABLE', 'COMMITTED', 'UNCOMMITTED', 'READ', 'WRITE', 'ONLY', 'CALL', 'NFD', 'NFC', 'NFKD', 'NFKC', 'IF', 'NULLIF', 'COALESCE', IDENTIFIER, DIGIT_IDENTIFIER, QUOTED_IDENTIFIER, BACKQUOTED_IDENTIFIER}
ksql>

[rmoff]

Related : #677

[rmoff]

Closing as there's nothing to document yet, until #677 is fixed