[DISCUSS] Support "create [temporary] table" and "create [temporary] view" statements

[Caizhi Weng]

Hi dear Flink developers,

I'm quite confused by the "create view" statement in SQL client.

From traditional database we expect that a view behaves just like a table
except that it will be computed when used. We also expect that a view
should be created under a database just like a table.

But when I create a view in SQL client and switch to another database by
the "use" statement, the view is still there. It seems that this view is
created in all databases under the same catalog.

I look into the source code and discover that the view I created is
actually a temporary view. In
https://ci.apache.org/projects/flink/flink-docs-stable/dev/table/common.html it
is stated that

> On the other hand, temporary tables are always stored in memory and only
> exist for the duration of the Flink session they are created within. These
> tables are not visible to other sessions. They are not bound to any catalog
> or database but can be created in the namespace of one. Temporary tables
> are not dropped if their corresponding database is removed.

So this seems to be the desired behavior.

What seems really strange is that in SQL client, by using the "create
table" statement I'll get a permanent table, while by using the "create
view" statement I'll get a temporary view, which is sort of inconsistent.

So I would like to discuss that

   1. Shall we make it clear and support create table / create temporary
   table / create view / create temporary view statements?
   2. If yes, shall we need to support them just in SQL client or in SQL
   parser, or in some other places?
   3. Shall catalog manager support creating both permanent and temporary
   views?

Thanks.

[Jingsong Li]

Hi Caizhi, thanks for starting this discussion.

There is a FLIP-71 [1] to describe the whole story of view.

Sql-cli now implements a wrong way, and a separate way, which should be
deprecated and unified to TableEnvironment.

> Shall we make it clear and support create table / create temporary table
/ create view / create temporary view statements?

I think there are clear definitions for "create view" (permanent) and
"create temporary view" (temporary).

> If yes, shall we need to support them just in SQL client or in SQL
parser, or in some other places?

We should support in a unify way for TableEnvironment.sqlUpdate, and
SQL-CLI just pass to this method.

> Shall catalog manager support creating both permanent and temporary views?

Actually, it already supports these things, has done in FLIP-64 [2].

If you are interested, consider moving forward with the design and
implementation of FLIP-71. What do you think?

[1]
https://cwiki.apache.org/confluence/display/FLINK/FLIP-71+-+E2E+View+support+in+FLINK+SQL
[2]
https://cwiki.apache.org/confluence/display/FLINK/FLIP-64%3A+Support+for+Temporary+Objects+in+Table+module
<https://cwiki.apache.org/confluence/display/FLINK/FLIP-64%3A+Support+for+Temporary+Objects+in+Table+module?src=contextnavpagetreemode>

Best,
Jingsong Lee

On Fri, Feb 21, 2020 at 11:21 AM Caizhi Weng <[hidden email]> wrote:

> Hi dear Flink developers,
>
> I'm quite confused by the "create view" statement in SQL client.
>
> From traditional database we expect that a view behaves just like a table
> except that it will be computed when used. We also expect that a view
> should be created under a database just like a table.
>
> But when I create a view in SQL client and switch to another database by
> the "use" statement, the view is still there. It seems that this view is
> created in all databases under the same catalog.
>
> I look into the source code and discover that the view I created is
> actually a temporary view. In
>
> https://ci.apache.org/projects/flink/flink-docs-stable/dev/table/common.html
> it
> is stated that
>
> > On the other hand, temporary tables are always stored in memory and only
> > exist for the duration of the Flink session they are created within.
> These
> > tables are not visible to other sessions. They are not bound to any
> catalog
> > or database but can be created in the namespace of one. Temporary tables
> > are not dropped if their corresponding database is removed.
>
> So this seems to be the desired behavior.
>
> What seems really strange is that in SQL client, by using the "create
> table" statement I'll get a permanent table, while by using the "create
> view" statement I'll get a temporary view, which is sort of inconsistent.
>
> So I would like to discuss that
>
>    1. Shall we make it clear and support create table / create temporary
>    table / create view / create temporary view statements?
>    2. If yes, shall we need to support them just in SQL client or in SQL
>    parser, or in some other places?
>    3. Shall catalog manager support creating both permanent and temporary
>    views?
>
> Thanks.
>

--
Best, Jingsong Lee

[Jark Wu-2]

Hi Caizhi,

Thanks for raising this. I also think it is a bug in SQL CLI that CREATE
VIEW creates a temporary view. We should fix that.
If we want to fix it, we have to support create permanent view on
TableEnvironment first which is not supported yet.

Best,
Jark

On Fri, 21 Feb 2020 at 11:40, Jingsong Li <[hidden email]> wrote:

> Hi Caizhi, thanks for starting this discussion.
>
> There is a FLIP-71 [1] to describe the whole story of view.
>
> Sql-cli now implements a wrong way, and a separate way, which should be
> deprecated and unified to TableEnvironment.
>
> > Shall we make it clear and support create table / create temporary table
> / create view / create temporary view statements?
>
> I think there are clear definitions for "create view" (permanent) and
> "create temporary view" (temporary).
>
> > If yes, shall we need to support them just in SQL client or in SQL
> parser, or in some other places?
>
> We should support in a unify way for TableEnvironment.sqlUpdate, and
> SQL-CLI just pass to this method.
>
> > Shall catalog manager support creating both permanent and temporary
> views?
>
> Actually, it already supports these things, has done in FLIP-64 [2].
>
> If you are interested, consider moving forward with the design and
> implementation of FLIP-71. What do you think?
>
> [1]
>
> https://cwiki.apache.org/confluence/display/FLINK/FLIP-71+-+E2E+View+support+in+FLINK+SQL
> [2]
>
> https://cwiki.apache.org/confluence/display/FLINK/FLIP-64%3A+Support+for+Temporary+Objects+in+Table+module
> <
> https://cwiki.apache.org/confluence/display/FLINK/FLIP-64%3A+Support+for+Temporary+Objects+in+Table+module?src=contextnavpagetreemode
> >
>
> Best,
> Jingsong Lee
>
> On Fri, Feb 21, 2020 at 11:21 AM Caizhi Weng <[hidden email]> wrote:
>
> > Hi dear Flink developers,
> >
> > I'm quite confused by the "create view" statement in SQL client.
> >
> > From traditional database we expect that a view behaves just like a table
> > except that it will be computed when used. We also expect that a view
> > should be created under a database just like a table.
> >
> > But when I create a view in SQL client and switch to another database by
> > the "use" statement, the view is still there. It seems that this view is
> > created in all databases under the same catalog.
> >
> > I look into the source code and discover that the view I created is
> > actually a temporary view. In
> >
> >
> https://ci.apache.org/projects/flink/flink-docs-stable/dev/table/common.html
> > it
> > is stated that
> >
> > > On the other hand, temporary tables are always stored in memory and
> only
> > > exist for the duration of the Flink session they are created within.
> > These
> > > tables are not visible to other sessions. They are not bound to any
> > catalog
> > > or database but can be created in the namespace of one. Temporary
> tables
> > > are not dropped if their corresponding database is removed.
> >
> > So this seems to be the desired behavior.
> >
> > What seems really strange is that in SQL client, by using the "create
> > table" statement I'll get a permanent table, while by using the "create
> > view" statement I'll get a temporary view, which is sort of inconsistent.
> >
> > So I would like to discuss that
> >
> >    1. Shall we make it clear and support create table / create temporary
> >    table / create view / create temporary view statements?
> >    2. If yes, shall we need to support them just in SQL client or in SQL
> >    parser, or in some other places?
> >    3. Shall catalog manager support creating both permanent and temporary
> >    views?
> >
> > Thanks.
> >
>
>
> --
> Best, Jingsong Lee
>