Support `CREATE TABLE` via SQL for infinite streams

[alamb]

Is your feature request related to a problem or challenge?

Right now there is no (good) SQL syntax for creating a table via SQL that is marked as an infinite stream. The only way is to either create it programatically or (after #6235 is merged) some non user friendly option syntax (that is also purposely not documented)

I would like a real, user friendly, way to create such a table

Describe the solution you'd like

Instead of

CREATE external table t ...
OPTIONS('infinite_source' 'true')
LOCATION 'tests/data/empty.csv';

I would like some sort of explicit syntax like

CREATE external table t ...
INFINITE
LOCATION 'tests/data/empty.csv';

Describe alternatives you've considered

It would be good to do some research into what other systems (like Flink) that support streaming SQL call this concept / how they declare such tables

I am not sure INFINITE is the right syntax to choose

Additional context

If we can use one of the existing sqlparser-rs keywords (see https://docs.rs/sqlparser/0.33.0/sqlparser/keywords/enum.Keyword.html) that would be great

Otherwise we may need to add a new one

[aprimadi]

@alamb what do you think using UNBOUNDED keyword?

That's the closest thing I can think of.

I can work on this on the weekend.

[aprimadi]

Otherwise I'll just create a pull request to add INFINITE keyword on sqlparser and wait until sqlparser is updated before working on this.

[alamb]

@mustafasrepo or @ozankabak do you have any suggestions on naming? The DataFusion code uses the term infinte - is that the right term to expose to users? Or should we use a different term?

[ozankabak]

What do you think about a syntax like CREATE [UNBOUNDED] EXTERNAL TABLE ... (or the same with INFINITE)? For some reason having a lonely INFINITE or UNBOUNDED keyword bothers me.

To summarize, my order of preference is:

1. CREATE [UNBOUNDED/INFINITE] EXTERNAL TABLE ...
2. CREATE EXTERNAL TABLE ... OPTIONS('UNBOUNDED/INFINITE' 'TRUE')
3. CREATE EXTERNAL TABLE ... UNBOUNDED/INFINITE ... LOCATION 'tests/data/empty.csv'

In terms of the UNBOUNDED vs INFINITE distinction, I think both are fine, and we use them interchangeably in code. However, in the API, we should stick to one and use it uniformly. At SQL API level, my tendency is towards UNBOUNDED.

[alamb]

Those are some great suggestions. Thank you @ozankabak

I agree that

CREATE UNBOUNDED EXTERNAL TABLE ...

Looks very nice 👍