I'm trying to use quoted identifiers in Flink (mainly because I have some column names that conflict with keywords like year). But I can't make it parse.
I boiled down to a minimal failing example:
EnvironmentSettings settings =
EnvironmentSettings.newInstance().useBlinkPlanner().inBatchMode().build();
TableEnvironment tEnv = TableEnvironment.create(settings);
String sql = "CREATE TABLE table1(\"ts\" TIMESTAMP) WITH ('connector' = 'filesystem', 'path' = 'file:///Users/ecerulm/tmp/csv/', 'format' = 'csv');";
System.out.println(sql);
tEnv.executeSql(sql);
which fails complaining about the "ts" part:
org.apache.flink.table.api.SqlParserException: SQL parse failed. Encountered "\"" at line 1, column 21.
Was expecting one of:
"CONSTRAINT" ...
"PRIMARY" ...
"UNIQUE" ...
"WATERMARK" ...
<BRACKET_QUOTED_IDENTIFIER> ...
<QUOTED_IDENTIFIER> ...
<BACK_QUOTED_IDENTIFIER> ...
<HYPHENATED_IDENTIFIER> ...
<IDENTIFIER> ...
<UNICODE_QUOTED_IDENTIFIER> ...
at org.apache.flink.table.planner.calcite.CalciteParser.parse(CalciteParser.java:56)
at org.apache.flink.table.planner.delegation.ParserImpl.parse(ParserImpl.java:76)
at org.apache.flink.table.api.internal.TableEnvironmentImpl.executeSql(TableEnvironmentImpl.java:658)
at com.rubenlaguna.BatchJobTest.testBatchJob(BatchJobTest.java:22)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)
at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:78)
at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:57)
at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
at org.junit.runner.JUnitCore.run(JUnitCore.java:137)
at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:69)
at com.intellij.rt.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:33)
at com.intellij.rt.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:220)
at com.intellij.rt.junit.JUnitStarter.main(JUnitStarter.java:53)
As I understand from the exception it's not expecting the " character because it does not match any of the parsing alternatives like <QUOTED_IDENTIFIER> but as I understand from Calcite documentation the syntax for quoted identifiers is just "myidentifier".
The Flink documentation for Expanding Table Identifiers says:
Identifiers follow SQL requirements which means that they can be escaped with a backtick character (`).
It does not mention double quoted identifiers.
So although Flink SQL is based on Calcite SQL and although Calcite has double quoted identifiers it seems that Flink SQL only supports the regular sql identifier + backquoted identifiers. So you will need to write the query like this:
String sql = "CREATE TABLE table1(`ts` TIMESTAMP) WITH ('connector' = 'filesystem', 'path' = 'file:///Users/ecerulm/tmp/csv/', 'format' = 'csv')";
tEnv.executeSql(sql);
so instead of "ts" (with double quotes) use `ts` (with backticks).