[FLINK-29219][table] Fix CREATE TABLE AS statement blocks SQL client's execution

[lsyldliu]

What is the purpose of the change

Fix CTAS statement blocks SQL client's execution

Brief change log

• Fix CTAS statement blocks SQL client's execution
• BEGIN STATEMENT SET syntax also support CTAS

Verifying this change

This change added tests and can be verified as follows:

• Added e2e tests in CreateTableAsITCase
• Added related integration test

Does this pull request potentially affect one of the following parts:

• Dependencies (does it add or upgrade a dependency): (no)
• The public API, i.e., is any changed class annotated with @Public(Evolving): (no)
• The serializers: (no)
• The runtime per-record code paths (performance sensitive): (no)
• Anything that affects deployment or recovery: JobManager (and its components), Checkpointing, Kubernetes/Yarn, ZooKeeper: (no)
• The S3 file system connector: (no)

Documentation

• Does this pull request introduce a new feature? (no)
• If yes, how is the feature documented? (not documented)

[flinkbot]

CI report:

• 584285c053f552440ebdabdefdd527c9bb49bd9a Azure: SUCCESS

Bot commands

The @flinkbot bot supports the following commands:

• @flinkbot run azure re-run the last Azure build

[wuchong]

@flinkbot run azure

[lsyldliu]

@flinkbot run azure

[HuangXingBo]

For resolving https://issues.apache.org/jira/browse/FLINK-29315, we run tests in AlibabaCI006-agent01, which caused this ci failure.

[HuangXingBo]

@flinkbot run azure

[wuchong]

@flinkbot run azure

[wuchong]

The test is failed

2022-09-26T07:22:05.4150143Z Sep 26 07:22:05   # test "ctas" only supported in Hive Dialect	
2022-09-26T07:22:05.4150791Z Sep 26 07:22:05   CREATE TABLE foo as select 1;	
2022-09-26T07:22:05.4151625Z Sep 26 07:22:05   +-------------------------+	
2022-09-26T07:22:05.4152211Z Sep 26 07:22:05   | hivecatalog.default.foo |	
2022-09-26T07:22:05.4152992Z Sep 26 07:22:05   +-------------------------+	
2022-09-26T07:22:05.4153680Z Sep 26 07:22:05   |                      -1 |	
2022-09-26T07:22:05.4154432Z Sep 26 07:22:05   +-------------------------+	
2022-09-26T07:22:05.4154970Z Sep 26 07:22:05   1 row in set	
2022-09-26T07:22:05.4155425Z Sep 26 07:22:05   !ok

but was:

2022-09-26T07:22:05.4307377Z Sep 26 07:22:05   # test "ctas" only supported in Hive Dialect
2022-09-26T07:22:05.4307772Z Sep 26 07:22:05   CREATE TABLE foo as select 1;
2022-09-26T07:22:05.4308202Z Sep 26 07:22:05   [INFO] Submitting SQL update statement to the cluster...
2022-09-26T07:22:05.4308846Z Sep 26 07:22:05   [INFO] SQL update statement has been successfully submitted to the cluster:
2022-09-26T07:22:05.4309393Z Sep 26 07:22:05   Job ID:
2022-09-26T07:22:05.4309694Z Sep 26 07:22:05   
2022-09-26T07:22:05.4309985Z Sep 26 07:22:05   !info

[lsyldliu]

The test is failed

2022-09-26T07:22:05.4150143Z Sep 26 07:22:05   # test "ctas" only supported in Hive Dialect	
2022-09-26T07:22:05.4150791Z Sep 26 07:22:05   CREATE TABLE foo as select 1;	
2022-09-26T07:22:05.4151625Z Sep 26 07:22:05   +-------------------------+	
2022-09-26T07:22:05.4152211Z Sep 26 07:22:05   | hivecatalog.default.foo |	
2022-09-26T07:22:05.4152992Z Sep 26 07:22:05   +-------------------------+	
2022-09-26T07:22:05.4153680Z Sep 26 07:22:05   |                      -1 |	
2022-09-26T07:22:05.4154432Z Sep 26 07:22:05   +-------------------------+	
2022-09-26T07:22:05.4154970Z Sep 26 07:22:05   1 row in set	
2022-09-26T07:22:05.4155425Z Sep 26 07:22:05   !ok

but was:

2022-09-26T07:22:05.4307377Z Sep 26 07:22:05   # test "ctas" only supported in Hive Dialect
2022-09-26T07:22:05.4307772Z Sep 26 07:22:05   CREATE TABLE foo as select 1;
2022-09-26T07:22:05.4308202Z Sep 26 07:22:05   [INFO] Submitting SQL update statement to the cluster...
2022-09-26T07:22:05.4308846Z Sep 26 07:22:05   [INFO] SQL update statement has been successfully submitted to the cluster:
2022-09-26T07:22:05.4309393Z Sep 26 07:22:05   Job ID:
2022-09-26T07:22:05.4309694Z Sep 26 07:22:05   
2022-09-26T07:22:05.4309985Z Sep 26 07:22:05   !info

I've fixed it.