You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@beam.apache.org by Ahmed Abualsaud via dev <de...@beam.apache.org> on 2026/10/07 20:30:31 UTC
[PROPOSAL] Add CHANGES and WITH clauses to Beam SQL
Hey everyone,
I'd like to propose two additions to how Beam SQL refers to a table:
1. *CHANGES*, to read a table's row-level changes instead of its current
contents:
SELECT * FROM cat.db.orders CHANGES FROM VERSION 1 TO VERSION 5
2. *WITH*, to set connector options for one query:
INSERT INTO cat.db.events WITH ('distribution_mode' = 'hash') SELECT ...
Both follow Spark SQL's syntax (SPARK-55948
<https://issues.apache.org/jira/browse/SPARK-55948> and SPARK-36680
<https://issues.apache.org/jira/browse/SPARK-36680>) and would improve ease
of use.
More details and example usage in the doc. Comments and suggestions are
welcome!
https://docs.google.com/document/d/1k_RBQmSvf0H5ZayRf2E_oUK1HpcBwryiJBH5BJv2mwA/edit
Thanks,
Ahmed
Re: [PROPOSAL] Add CHANGES and WITH clauses to Beam SQL
Posted by Ahmed Abualsaud via dev <de...@beam.apache.org>.
Looks like a function is the way to go. I like having named parameters too.
If there are no objections, I'll aim to build something that lets us do the
following (also reflected in the doc
<https://docs.google.com/document/d/1k_RBQmSvf0H5ZayRf2E_oUK1HpcBwryiJBH5BJv2mwA/edit?tab=t.0#heading=h.7flwjj504oo9>
):
SELECT * FROM CHANGES(cat.db.orders) -- all
changes
SELECT * FROM CHANGES(cat.db.orders, 123) -- start
version, open end
SELECT * FROM CHANGES(cat.db.orders, '2026-10-01 10:05:30', '2026-10-05') --
start and end timestamps
SELECT * FROM CHANGES(cat.db.orders, end_timestamp => '2026-10-05') --
named end timestamp, open start (or NULL positionally)
SELECT * FROM CHANGES(cat.db.orders, start_version => 123) -- named start
version, open end
On Tue, Oct 13, 2026 at 7:25 AM Kenneth Knowles <ke...@apache.org> wrote:
> Top priority is to "be like everyone else" since the whole point of SQL
> is that a lot of people are familiar with it. If everyone were doing the
> syntax, I would go with it even though adding syntax is a needless burden.
> But since major players are going with the function style, I prefer we do
> that.
>
> BTW both BigQuery and Databricks support named parameters for functions.
> If those can be used with CHANGES, it greatly improves readability:
>
> CHANGES(cat.db.table, start_version => 123, end_version => 456)
>
> CHANGES(cat.db.table, start_timestamp => '2026-10-01', end_timestamp =>
> '2026-10-22')
>
>
> Kenn
>
> On Mon, Oct 12, 2026 at 5:35 PM Reuven Lax via dev <de...@beam.apache.org>
> wrote:
>
>> In terms of readability, they are similar. I prefer the table-function as
>> it minimizes the addition of new, non-standard SQL syntax. It's also
>> implicitly compatible with any future SQL syntax that is standardized for
>> changestreams, as we can rewrite the table function to expand to this new
>> syntax.
>>
>> On Mon, Oct 12, 2026 at 2:13 PM Ahmed Abualsaud via dev <
>> dev@beam.apache.org> wrote:
>>
>>> Hey everyone,
>>>
>>> Surfacing a question raised from the doc regarding how users should ask
>>> for a table's changelog. I'd like more input before building it.
>>>
>>> *Option 1*: a clause after the table name (similar to Spark, Snowflake)
>>>
>>> SELECT * FROM cat.db.table *CHANGES* FROM VERSION 123 TO VERSION 456;
>>> SELECT * FROM cat.db.table *CHANGES* FROM TIMESTAMP '2026-10-01' TO TIMESTAMP
>>> '2026-10-12';
>>>
>>> *Option 2*: a table function (similar to BigQuery, Databricks)
>>>
>>> SELECT * FROM *CHANGES(*cat.db.table, 123, 456*);*
>>> SELECT * FROM *CHANGES(*cat.db.table, '2026-10-01', '2026-10-12'*);*
>>>
>>> Which syntax would you prefer, and why? More examples are in the doc
>>> <https://docs.google.com/document/d/1k_RBQmSvf0H5ZayRf2E_oUK1HpcBwryiJBH5BJv2mwA/edit?tab=t.0> if
>>> that helps.
>>>
>>>
>>> On Wed, Oct 7, 2026 at 1:30 PM Ahmed Abualsaud <
>>> ahmedabualsaud@google.com> wrote:
>>>
>>>> Hey everyone,
>>>>
>>>>
>>>> I'd like to propose two additions to how Beam SQL refers to a table:
>>>>
>>>> 1. *CHANGES*, to read a table's row-level changes instead of its
>>>> current contents:
>>>>
>>>> SELECT * FROM cat.db.orders CHANGES FROM VERSION 1 TO VERSION 5
>>>>
>>>> 2. *WITH*, to set connector options for one query:
>>>>
>>>> INSERT INTO cat.db.events WITH ('distribution_mode' = 'hash') SELECT
>>>> ...
>>>>
>>>> Both follow Spark SQL's syntax (SPARK-55948
>>>> <https://issues.apache.org/jira/browse/SPARK-55948> and SPARK-36680
>>>> <https://issues.apache.org/jira/browse/SPARK-36680>) and would improve
>>>> ease of use.
>>>>
>>>> More details and example usage in the doc. Comments and suggestions are
>>>> welcome!
>>>>
>>>>
>>>>
>>>> https://docs.google.com/document/d/1k_RBQmSvf0H5ZayRf2E_oUK1HpcBwryiJBH5BJv2mwA/edit
>>>>
>>>> Thanks,
>>>>
>>>> Ahmed
>>>>
>>>>
Re: [PROPOSAL] Add CHANGES and WITH clauses to Beam SQL
Posted by Kenneth Knowles <ke...@apache.org>.
Top priority is to "be like everyone else" since the whole point of SQL is
that a lot of people are familiar with it. If everyone were doing the
syntax, I would go with it even though adding syntax is a needless burden.
But since major players are going with the function style, I prefer we do
that.
BTW both BigQuery and Databricks support named parameters for functions. If
those can be used with CHANGES, it greatly improves readability:
CHANGES(cat.db.table, start_version => 123, end_version => 456)
CHANGES(cat.db.table, start_timestamp => '2026-10-01', end_timestamp =>
'2026-10-22')
Kenn
On Mon, Oct 12, 2026 at 5:35 PM Reuven Lax via dev <de...@beam.apache.org>
wrote:
> In terms of readability, they are similar. I prefer the table-function as
> it minimizes the addition of new, non-standard SQL syntax. It's also
> implicitly compatible with any future SQL syntax that is standardized for
> changestreams, as we can rewrite the table function to expand to this new
> syntax.
>
> On Mon, Oct 12, 2026 at 2:13 PM Ahmed Abualsaud via dev <
> dev@beam.apache.org> wrote:
>
>> Hey everyone,
>>
>> Surfacing a question raised from the doc regarding how users should ask
>> for a table's changelog. I'd like more input before building it.
>>
>> *Option 1*: a clause after the table name (similar to Spark, Snowflake)
>>
>> SELECT * FROM cat.db.table *CHANGES* FROM VERSION 123 TO VERSION 456;
>> SELECT * FROM cat.db.table *CHANGES* FROM TIMESTAMP '2026-10-01' TO TIMESTAMP
>> '2026-10-12';
>>
>> *Option 2*: a table function (similar to BigQuery, Databricks)
>>
>> SELECT * FROM *CHANGES(*cat.db.table, 123, 456*);*
>> SELECT * FROM *CHANGES(*cat.db.table, '2026-10-01', '2026-10-12'*);*
>>
>> Which syntax would you prefer, and why? More examples are in the doc
>> <https://docs.google.com/document/d/1k_RBQmSvf0H5ZayRf2E_oUK1HpcBwryiJBH5BJv2mwA/edit?tab=t.0> if
>> that helps.
>>
>>
>> On Wed, Oct 7, 2026 at 1:30 PM Ahmed Abualsaud <ah...@google.com>
>> wrote:
>>
>>> Hey everyone,
>>>
>>>
>>> I'd like to propose two additions to how Beam SQL refers to a table:
>>>
>>> 1. *CHANGES*, to read a table's row-level changes instead of its
>>> current contents:
>>>
>>> SELECT * FROM cat.db.orders CHANGES FROM VERSION 1 TO VERSION 5
>>>
>>> 2. *WITH*, to set connector options for one query:
>>>
>>> INSERT INTO cat.db.events WITH ('distribution_mode' = 'hash') SELECT
>>> ...
>>>
>>> Both follow Spark SQL's syntax (SPARK-55948
>>> <https://issues.apache.org/jira/browse/SPARK-55948> and SPARK-36680
>>> <https://issues.apache.org/jira/browse/SPARK-36680>) and would improve
>>> ease of use.
>>>
>>> More details and example usage in the doc. Comments and suggestions are
>>> welcome!
>>>
>>>
>>>
>>> https://docs.google.com/document/d/1k_RBQmSvf0H5ZayRf2E_oUK1HpcBwryiJBH5BJv2mwA/edit
>>>
>>> Thanks,
>>>
>>> Ahmed
>>>
>>>
Re: [PROPOSAL] Add CHANGES and WITH clauses to Beam SQL
Posted by Reuven Lax via dev <de...@beam.apache.org>.
In terms of readability, they are similar. I prefer the table-function as
it minimizes the addition of new, non-standard SQL syntax. It's also
implicitly compatible with any future SQL syntax that is standardized for
changestreams, as we can rewrite the table function to expand to this new
syntax.
On Mon, Oct 12, 2026 at 2:13 PM Ahmed Abualsaud via dev <de...@beam.apache.org>
wrote:
> Hey everyone,
>
> Surfacing a question raised from the doc regarding how users should ask
> for a table's changelog. I'd like more input before building it.
>
> *Option 1*: a clause after the table name (similar to Spark, Snowflake)
>
> SELECT * FROM cat.db.table *CHANGES* FROM VERSION 123 TO VERSION 456;
> SELECT * FROM cat.db.table *CHANGES* FROM TIMESTAMP '2026-10-01' TO TIMESTAMP
> '2026-10-12';
>
> *Option 2*: a table function (similar to BigQuery, Databricks)
>
> SELECT * FROM *CHANGES(*cat.db.table, 123, 456*);*
> SELECT * FROM *CHANGES(*cat.db.table, '2026-10-01', '2026-10-12'*);*
>
> Which syntax would you prefer, and why? More examples are in the doc
> <https://docs.google.com/document/d/1k_RBQmSvf0H5ZayRf2E_oUK1HpcBwryiJBH5BJv2mwA/edit?tab=t.0> if
> that helps.
>
>
> On Wed, Oct 7, 2026 at 1:30 PM Ahmed Abualsaud <ah...@google.com>
> wrote:
>
>> Hey everyone,
>>
>>
>> I'd like to propose two additions to how Beam SQL refers to a table:
>>
>> 1. *CHANGES*, to read a table's row-level changes instead of its current
>> contents:
>>
>> SELECT * FROM cat.db.orders CHANGES FROM VERSION 1 TO VERSION 5
>>
>> 2. *WITH*, to set connector options for one query:
>>
>> INSERT INTO cat.db.events WITH ('distribution_mode' = 'hash') SELECT
>> ...
>>
>> Both follow Spark SQL's syntax (SPARK-55948
>> <https://issues.apache.org/jira/browse/SPARK-55948> and SPARK-36680
>> <https://issues.apache.org/jira/browse/SPARK-36680>) and would improve
>> ease of use.
>>
>> More details and example usage in the doc. Comments and suggestions are
>> welcome!
>>
>>
>>
>> https://docs.google.com/document/d/1k_RBQmSvf0H5ZayRf2E_oUK1HpcBwryiJBH5BJv2mwA/edit
>>
>> Thanks,
>>
>> Ahmed
>>
>>
Re: [PROPOSAL] Add CHANGES and WITH clauses to Beam SQL
Posted by Ahmed Abualsaud via dev <de...@beam.apache.org>.
Hey everyone,
Surfacing a question raised from the doc regarding how users should ask for
a table's changelog. I'd like more input before building it.
*Option 1*: a clause after the table name (similar to Spark, Snowflake)
SELECT * FROM cat.db.table *CHANGES* FROM VERSION 123 TO VERSION 456;
SELECT * FROM cat.db.table *CHANGES* FROM TIMESTAMP '2026-10-01' TO
TIMESTAMP
'2026-10-12';
*Option 2*: a table function (similar to BigQuery, Databricks)
SELECT * FROM *CHANGES(*cat.db.table, 123, 456*);*
SELECT * FROM *CHANGES(*cat.db.table, '2026-10-01', '2026-10-12'*);*
Which syntax would you prefer, and why? More examples are in the doc
<https://docs.google.com/document/d/1k_RBQmSvf0H5ZayRf2E_oUK1HpcBwryiJBH5BJv2mwA/edit?tab=t.0>
if
that helps.
On Wed, Oct 7, 2026 at 1:30 PM Ahmed Abualsaud <ah...@google.com>
wrote:
> Hey everyone,
>
>
> I'd like to propose two additions to how Beam SQL refers to a table:
>
> 1. *CHANGES*, to read a table's row-level changes instead of its current
> contents:
>
> SELECT * FROM cat.db.orders CHANGES FROM VERSION 1 TO VERSION 5
>
> 2. *WITH*, to set connector options for one query:
>
> INSERT INTO cat.db.events WITH ('distribution_mode' = 'hash') SELECT ...
>
> Both follow Spark SQL's syntax (SPARK-55948
> <https://issues.apache.org/jira/browse/SPARK-55948> and SPARK-36680
> <https://issues.apache.org/jira/browse/SPARK-36680>) and would improve
> ease of use.
>
> More details and example usage in the doc. Comments and suggestions are
> welcome!
>
>
>
> https://docs.google.com/document/d/1k_RBQmSvf0H5ZayRf2E_oUK1HpcBwryiJBH5BJv2mwA/edit
>
> Thanks,
>
> Ahmed
>
>