Failed to connect to Kafka topic - No rows returned from Flink SQL "select * from user_behavior;" about flink-sql-demo HOT 2 CLOSED

I did a local install of flink 1.12.4, downloaded the correct connectors, and removed flink/datagen from docker-compose.yml. After running SourceGenerator.java locally, Kafka topic is populated and I got the following error from Flink SQL:

Flink SQL> select * from user_behavior;
[ERROR] Could not execute SQL statement. Reason:
java.time.format.DateTimeParseException: Text '2017-11-27 00:00:00' could not be parsed at index 10

At least this shows the connector is working :-).
I think it's a datetime format issue which needs to be addressed in SourceGenerator.

from flink-sql-demo.

OK, finally got all the Kibana scenarios working. Tweaks made:
1). For docker-compose, only keep kafka, zookeeper, elasticsearch, kibana. Note versions - kafka_2.12-2.2.1, elasticsearch 7.6.0.
2). Install mysql 8.0.19 locally. Run mysql/category.sql to create flink database and category table.
3). Install Flink 1.12.4 locally. Download to lib the following jars if they are not there - this is the most tricky part as versioning match is hell:

flink-connector-jdbc_2.11-1.12.4.jar #NEED THIS ONE IN ADDITION TO mysql-connector-java TO AVOID org.apache.flink.table.api.NoMatchingTableFactoryException.

flink-csv-1.12.4.jar
flink-dist_2.12-1.12.4.jar
flink-jdbc_2.11-1.9.0.jar
flink-json-1.12.4.jar
flink-shaded-zookeeper-3.4.14.jar

flink-sql-connector-elasticsearch7_2.11-1.12.4.jar #WORKS WITH elasticsearch 7.6.0.
flink-sql-connector-kafka_2.11-1.12.4.jar #WORKS WITH kafka_2.12-2.2.1

flink-table-blink_2.12-1.12.4.jar
flink-table_2.12-1.12.4.jar
log4j-1.2-api-2.12.1.jar
log4j-api-2.12.1.jar
log4j-core-2.12.1.jar
log4j-slf4j-impl-2.12.1.jar

mysql-connector-java-8.0.19.jar

4). Download the 3.6GB user_behavior.log. Compile and run SourceGenerator.java with "--input datagen/user_behavior.log --output kafka localhost:9092 --speedup 2000". To adjust the datetimeformat:
static final DateTimeFormatter SQL_TIMESTAMP_FORMAT = new DateTimeFormatterBuilder()
.append(DateTimeFormatter.ISO_LOCAL_DATE)
.appendLiteral('T')
.append(SQL_TIME_FORMAT)
.appendLiteral('Z')
.toFormatter();
5). Start flink: ./bin/start.cluster.sh. Start sql-client: ./bin/sql-client.sh embedded.

6). SQL changes
'connector.type' = 'elasticsearch',
'connector.version' = '7',

'connector.type' = 'jdbc',
'connector.url' = 'jdbc:mysql://localhost:3306/flink?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC',

Since category.sql schema is different from what the blog states (parent_category_name instead of parent_category_id), category_dim and related need to adjust accordingly.

from flink-sql-demo.