0

We have one table A in database. We are loading that table into flink using Flink SQL JdbcCatalog.

Here is how we are loading the data

val catalog = new JdbcCatalog("my_catalog", "database_name", username, password, url)

streamTableEnvironment.registerCatalog("my_catalog", catalog) streamTableEnvironment.useCatalog("my_catalog")

val query = "select timestamp, count from A"

val sourceTable = streamTableEnvironment.sqlQuery(query) streamTableEnvironment.createTemporaryView("innerTable", sourceTable)

val aggregationQuery = select window_end, sum(count) from TABLE(TUMBLE(TABLE innerTable, DESCRIPTOR(timestamp), INTERVAL '10' minutes)) group by window_end

It throws following error Exception in thread "main" org.apache.flink.table.api.ValidationException: SQL validation failed. The window function TUMBLE(TABLE table_name, DESCRIPTOR(timecol), datetime interval[, datetime interval]) requires the timecol is a time attribute type, but is TIMESTAMP(6).

In short we want to apply windowing aggregation on an already existing column. How can we do that Note - This is a batch processing

Kush Rohra
  • 15
  • 5
  • This question is lacking some information like what Flink version are you using and how have you defined and created the table where you want to run the TUMBLE query against. – Martijn Visser Nov 22 '22 at 10:42

2 Answers2

0

Timestamp columns used as time attributes in Flink SQL must be either TIMESTAMP(3) or TIMESTAMP_LTZ(3).

David Anderson
  • 39,434
  • 4
  • 33
  • 60
0

Column should be TIMESTAMP(3) or TIMESTAMP_LTZ(3) but also the column should be marked as ROWTIME.

Type this line in your code

sourceTable.printSchema();

and check the result. The column should be marked as ROWTIME as shown below.

(
  `deviceId` STRING,
  `dataStart` BIGINT,
  `recordCount` INT,
  `time_Insert` BIGINT,
  `time_Insert_ts` TIMESTAMP(3) *ROWTIME*
)

You can find my sample below.

        Table tableCpuDataCalculatedTemp = tableEnv.fromDataStream(streamCPUDataCalculated, Schema.newBuilder()
                        .column("deviceId", DataTypes.STRING())
                        .column("dataStart", DataTypes.BIGINT())
                        .column("recordCount", DataTypes.INT())
                        .column("time_Insert", DataTypes.BIGINT())
                        .column("time_Insert_ts", DataTypes.TIMESTAMP(3))
                        .watermark("time_Insert_ts", "time_Insert_ts")
                        .build());

watermark method makes it ROWTIME

Kenank
  • 321
  • 1
  • 10