Re: [DISCUSS] SQL Syntax for Table API StatementSet

[Timo Walther]

Hi Fabian,

thanks for the proposal. I agree that we should have consensus on the 
SQL syntax as well and thus finalize the concepts introduced in FLIP-84.

I would favor Jark's proposal. I would like to propose the following syntax:

BEGIN STATEMENT SET;
  INSERT INTO ...;
  INSERT INTO ...;
END;

1) BEGIN and END are commonly used for blocks in SQL.

2) We should not start mixing START/BEGIN for different kind of blocks. 
Because that can also be confusing for users. There is no additional 
helpful semantic in using START over BEGIN.

3) Instead, we should rather parameterize the block statament with 
`STATEMENT SET` and keep the END of the block simple (also similar to 
CASE ... WHEN ... END).

4) If we look at Jark's example in SQL Server, the BEGIN is also 
parameterized by `BEGIN { TRAN | TRANSACTION }`.

5) Also in Java curly braces are used for both classes, methods, and 
loops for different purposes parameterized by the preceding code.

Regards,
Timo


On 17.06.20 11:36, Fabian Hueske wrote:
Thanks for joining this discussion Jark!

This feature is a bit different from BEGIN TRANSACTION / COMMIT and BEGIN /
END.

The only commonality is that all three group multiple statements.
* BEGIN TRANSACTION / COMMIT creates a transactional context that
guarantees atomicity, consistency, and isolation. Statements and queries
are sequentially executed.
* BEGIN / END defines a block of statements just like curly braces ({ and
}) do in Java. The statements (which can also include variable definitions
and printing) are sequentially executed.
* A statement set defines a group of statements that are optimized together
and jointly executed at the same time, i.e., there is no sequence or order.

A statement set (consisting of multiple INSERT INTO statements) behaves
just like a single INSERT INTO statement.
Everywhere where an INSERT INTO statement can be executed, it should be
possible to execute a statement set consisting of multiple INSERT INTO
statements.
That's also why I think that statement sets are orthogonal to
multi-statement execution.

As I said before, I'm happy to discuss syntax proposals for statement sets.
However, I think a BEGIN / END syntax for statement sets would confuse
users who know this syntax from MySQL, SQL Server, or another DBMS.

Thanks,
Fabian


Am Di., 16. Juni 2020 um 05:07 Uhr schrieb Jark Wu <imj...@gmail.com>:

Hi Fabian,

Thanks for starting this discussion. I think this is a very important
syntax to support file mode and multi-statement for SQL Client.
I'm +1 to introduce a syntax to group SQL statements to execute together.

As a reference, traditional database systems also have similar syntax, such
as "START/BEGIN TRANSACTION ... COMMIT" to group statements as a
transaction [1],
and also "BEGIN ... END" [2] [3] to group a set of SQL statements that
execute together.

Maybe we can also use "BEGIN ... END" syntax which is much simpler?

Regarding where to implement, I also prefer to have it in Flink SQL core,
here are some reasons from my side:
1) I think many downstream projects (e.g Zeppelin) will have the same
requirement. It would be better to have it in core instead of reinventing
the wheel by users.
2) Having it in SQL CLI means it is a standard syntax to support statement
set in Flink. So I think it makes sense to have it in core too, otherwise,
it looks like a broken feature.
     In 1.10, CREATE VIEW is only supported in SQL CLI, not supported in
TableEnvironment, which confuses many users.
3) Currently, we are moving statement parsing to use sql-parser
(FLINK-17728). Calcite has a good support for parsing multi-statements.
     It will be tricky to parse multi-statements only in SQL Client.

Best,
Jark

[1]:

https://docs.microsoft.com/en-us/sql/t-sql/language-elements/begin-transaction-transact-sql?view=sql-server-ver15
[2]:

https://www.sqlservertutorial.net/sql-server-stored-procedures/sql-server-begin-end/
[3]: https://dev.mysql.com/doc/refman/8.0/en/begin-end.html

On Mon, 15 Jun 2020 at 20:50, Fabian Hueske <fhue...@gmail.com> wrote:

Hi everyone,

FLIP-84 [1] added the concept of a "statement set" to group multiple
INSERT
INTO statements (SQL or Table API) together. The statements in a
statement
set are jointly optimized and executed as a single Flink job.

I would like to start a discussion about a SQL syntax to group multiple
INSERT INTO statements in a statement set. The use case would be to
expose
the statement set feature to a solely text based client for Flink SQL
such
as Flink's SQL CLI [1].

During the discussion of FLIP-84, we had briefly talked about such a
syntax
[3].

START STATEMENT SET;
INSERT INTO ... SELECT ...;
INSERT INTO ... SELECT ...;
...
END STATEMENT SET;

We didn't follow up on this proposal, to keep the focus on the FLIP-84
Table API changes and to not dive into a discussion about multiline SQL
query support [4].

While this feature is clearly based on multiple SQL queries, I think it
is
a bit different from what we usually understand as multiline SQL support.
That's because a statement set ends up to be a single Flink job. Hence,
there is no need on the Flink side to coordinate the execution of
multiple
jobs (incl. the discussion about blocking or async execution of queries).
Flink would treat the queries in a STATEMENT SET as a single query.

I would like to start a discussion about supporting the [START|END]
STATEMENT SET syntax (or a different syntax with equivalent semantics) in
Flink.
I don't have a strong preference whether this should be implemented in
Flink's SQL core or be a purely client side implementation in the CLI
client. It would be good though to have parser support in Flink for this.

What do others think?

[1]

https://cwiki.apache.org/confluence/pages/viewpage.action?pageId=134745878
[2]


https://ci.apache.org/projects/flink/flink-docs-stable/dev/table/sqlClient.html
[3]


https://docs.google.com/document/d/1ueLjQWRPdLTFB_TReAyhseAX-1N3j4WYWD0F02Uau0E/edit#heading=h.al86t1h4ecuv
[4]


https://lists.apache.org/thread.html/rf494e227c47010c91583f90eeaf807d3a4c3eb59d105349afd5fdc31%40%3Cdev.flink.apache.org%3E