Missing Calcite SQL functions in table API

[françois lacombe]

Hi all,

I'm trying to use CONVERT or CAST functions from Calcite docs to query some table with Table API.

https://calcite.apache.org/docs/reference.html

csv_table.select("col1,CONCAT('field1:',col2,',field2:',CAST(col3 AS string))");

col3 is actually described as int the CSV schema and CONCAT doesn't like it.

An exception is thrown "Undefined function: CAST"

The docs mention that SQL implementation is based on Calcite and is there a list of available functions please?

May I skip some useful dependency?

Thanks in advance

François

[Fabian Hueske-2]

Hi

You are using SQL syntax in a Table API query. You have to stick to Table API syntax or use SQL as

tEnv.sqlQuery("SELECT col1,CONCAT('field1:',col2,',field2:',CAST(col3 AS string)) FROM csvTable")

The Flink documentation lists all supported functions for Table API [1] and SQL [2].

Best, Fabian

[1] https://ci.apache.org/projects/flink/flink-docs-release-1.6/dev/table/tableApi.html

[2] https://ci.apache.org/projects/flink/flink-docs-release-1.6/dev/table/sql.html

2018-09-05 12:22 GMT+02:00 françois lacombe <[hidden email]>:

Hi all,

I'm trying to use CONVERT or CAST functions from Calcite docs to query some table with Table API.

https://calcite.apache.org/docs/reference.html

csv_table.select("col1,CONCAT('field1:',col2,',field2:',CAST(col3 AS string))");

col3 is actually described as int the CSV schema and CONCAT doesn't like it.

An exception is thrown "Undefined function: CAST"

The docs mention that SQL implementation is based on Calcite and is there a list of available functions please?

May I skip some useful dependency?

Thanks in advance

François

[françois lacombe]

Thanks Fabian,

I didn't notice select() wasn't SQL compliant.

sqlQuery works fine, it's all right :)

All the best

François

2018-09-05 12:30 GMT+02:00 Fabian Hueske <[hidden email]>:

Hi

You are using SQL syntax in a Table API query. You have to stick to Table API syntax or use SQL as

tEnv.sqlQuery("SELECT col1,CONCAT('field1:',col2,',field2:',CAST(col3 AS string)) FROM csvTable")

The Flink documentation lists all supported functions for Table API [1] and SQL [2].

Best, Fabian

[1] https://ci.apache.org/projects/flink/flink-docs-release-1.6/dev/table/tableApi.html

[2] https://ci.apache.org/projects/flink/flink-docs-release-1.6/dev/table/sql.html

2018-09-05 12:22 GMT+02:00 françois lacombe <[hidden email]>:

Hi all,

I'm trying to use CONVERT or CAST functions from Calcite docs to query some table with Table API.

https://calcite.apache.org/docs/reference.html

csv_table.select("col1,CONCAT('field1:',col2,',field2:',CAST(col3 AS string))");

col3 is actually described as int the CSV schema and CONCAT doesn't like it.

An exception is thrown "Undefined function: CAST"

The docs mention that SQL implementation is based on Calcite and is there a list of available functions please?

May I skip some useful dependency?

Thanks in advance

François