gustavodemorais commented on code in PR #29967:
URL: https://github.com/apache/flink/pull/29967#discussion_r4627297575
##########
docs/content/docs/sql/built-in-functions/process-table-functions.md:
##########
@@ -31,6 +31,7 @@ The built-in process table functions (PTFs) take a table as
input and return a t
|---------------------------------------------------------|-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
| `FROM_CHANGELOG(input => TABLE t [, ...])` | Converts an
append-only table with an explicit operation column into a dynamic table. See
[Changelog Conversion]({{< ref "docs/sql/reference/queries/changelog"
>}}#from_changelog) for the full list of arguments, semantics, and usage.
|
| `TO_CHANGELOG(input => TABLE t [, ...])` | Converts a dynamic
table into an append-only table with an explicit operation column. See
[Changelog Conversion]({{< ref "docs/sql/reference/queries/changelog"
>}}#to_changelog) for the full list of arguments, semantics, and usage.
|
+| `DEDUPLICATE(input => TABLE t PARTITION BY k [, ...])` | Keeps the first or
the last row per key of an append-only table. See [Deduplication]({{< ref
"docs/sql/reference/queries/deduplication" >}}#deduplicate) for the full list
of arguments, semantics, and usage. |
Review Comment:
Fine for this PR. Let's just remember to drop "of an append-only table" here
once updating input is supported.
##########
docs/content/docs/sql/reference/queries/deduplication.md:
##########
@@ -69,4 +69,114 @@ FROM (
WHERE row_num = 1
```
+## DEDUPLICATE
+
+{{< label Streaming >}}
+
+The `DEDUPLICATE` process table function (PTF) keeps the first or the last row
per key of an append-only table, as long as the state of the key is retained.
Unlike the `ROW_NUMBER()` pattern above, it needs no time attribute. Rows are
ordered by arrival or by `sequence_by` columns.
Review Comment:
I'd drop "Unlike the `ROW_NUMBER()` pattern above, it needs no time
attribute." to keep the intro short:
```suggestion
The `DEDUPLICATE` process table function (PTF) keeps the first or the last
row per key of an append-only table, as long as the state of the key is
retained. Rows are ordered by arrival or by `sequence_by` columns.
```
##########
docs/content/docs/sql/reference/queries/deduplication.md:
##########
@@ -69,4 +69,114 @@ FROM (
WHERE row_num = 1
```
+## DEDUPLICATE
+
+{{< label Streaming >}}
+
+The `DEDUPLICATE` process table function (PTF) keeps the first or the last row
per key of an append-only table, as long as the state of the key is retained.
Unlike the `ROW_NUMBER()` pattern above, it needs no time attribute. Rows are
ordered by arrival or by `sequence_by` columns.
+
+### Syntax
+
+```sql
+SELECT * FROM DEDUPLICATE(
+ input => TABLE source_table PARTITION BY key_col[, ...],
+ [keep => 'first' | 'last',]
+ [sequence_by => DESCRIPTOR(col[, ...]),]
+ [state_ttl => INTERVAL '1' HOUR,]
+ [reset_ttl_on_duplicate => true | false,]
+ [uid => 'my-id']
+)
+```
+
+### Parameters
+
+| Parameter | Required | Description |
+|:----------|:---------|:------------|
+| `input` | Yes | The append-only input table. `PARTITION BY` is required and
its columns are the key of the output. `ORDER BY` is not supported, use
`sequence_by` instead. |
Review Comment:
Let's keep the parameter descriptions lean. Users get a clear error if they
use `ORDER BY`, so we don't need to mention it here.
##########
docs/content/docs/sql/reference/queries/deduplication.md:
##########
@@ -69,4 +69,114 @@ FROM (
WHERE row_num = 1
```
+## DEDUPLICATE
+
+{{< label Streaming >}}
+
+The `DEDUPLICATE` process table function (PTF) keeps the first or the last row
per key of an append-only table, as long as the state of the key is retained.
Unlike the `ROW_NUMBER()` pattern above, it needs no time attribute. Rows are
ordered by arrival or by `sequence_by` columns.
+
+### Syntax
+
+```sql
+SELECT * FROM DEDUPLICATE(
+ input => TABLE source_table PARTITION BY key_col[, ...],
+ [keep => 'first' | 'last',]
+ [sequence_by => DESCRIPTOR(col[, ...]),]
+ [state_ttl => INTERVAL '1' HOUR,]
+ [reset_ttl_on_duplicate => true | false,]
+ [uid => 'my-id']
+)
+```
+
+### Parameters
+
+| Parameter | Required | Description |
+|:----------|:---------|:------------|
+| `input` | Yes | The append-only input table. `PARTITION BY` is required and
its columns are the key of the output. `ORDER BY` is not supported, use
`sequence_by` instead. |
+| `keep` | No | `'first'` (default) keeps the earliest arrival or the smallest
`sequence_by` value, `'last'` keeps the latest arrival or the greatest. Must be
a literal. Arrival order is not deterministic across reruns, use `sequence_by`
for a repeatable result. |
+| `sequence_by` | No | One or more columns that order the records of a key
instead of their arrival. The first column decides, later columns break ties,
and `NULL` orders below every other value. On a tie, the later arrival wins for
`'last'` and the earlier one for `'first'`. Columns must be numeric, string,
binary, boolean or date-time, and not part of `PARTITION BY`. |
+| `state_ttl` | No | The minimum time the state of an idle key is retained, in
processing time, as a non-negative `INTERVAL` literal. If omitted,
`table.exec.state.ttl` applies, including later changes to it. `INTERVAL '0'`
retains state indefinitely; the TTL is then left out of the state, so one
cannot be added later without losing the state. When the state of a key
expires, its next record is treated as the first one and emits `+I` again, so a
sink without an upsert key can hold several rows for that key. |
Review Comment:
This is a lot of detail for one parameter. Could we keep only what a user
needs to choose a value, e.g. what it does, the default, and that an expired
key is emitted again?
##########
docs/content/docs/sql/reference/queries/deduplication.md:
##########
@@ -69,4 +69,114 @@ FROM (
WHERE row_num = 1
```
+## DEDUPLICATE
+
+{{< label Streaming >}}
+
+The `DEDUPLICATE` process table function (PTF) keeps the first or the last row
per key of an append-only table, as long as the state of the key is retained.
Unlike the `ROW_NUMBER()` pattern above, it needs no time attribute. Rows are
ordered by arrival or by `sequence_by` columns.
+
+### Syntax
+
+```sql
+SELECT * FROM DEDUPLICATE(
+ input => TABLE source_table PARTITION BY key_col[, ...],
+ [keep => 'first' | 'last',]
+ [sequence_by => DESCRIPTOR(col[, ...]),]
+ [state_ttl => INTERVAL '1' HOUR,]
+ [reset_ttl_on_duplicate => true | false,]
+ [uid => 'my-id']
+)
+```
+
+### Parameters
+
+| Parameter | Required | Description |
+|:----------|:---------|:------------|
+| `input` | Yes | The append-only input table. `PARTITION BY` is required and
its columns are the key of the output. `ORDER BY` is not supported, use
`sequence_by` instead. |
+| `keep` | No | `'first'` (default) keeps the earliest arrival or the smallest
`sequence_by` value, `'last'` keeps the latest arrival or the greatest. Must be
a literal. Arrival order is not deterministic across reruns, use `sequence_by`
for a repeatable result. |
+| `sequence_by` | No | One or more columns that order the records of a key
instead of their arrival. The first column decides, later columns break ties,
and `NULL` orders below every other value. On a tie, the later arrival wins for
`'last'` and the earlier one for `'first'`. Columns must be numeric, string,
binary, boolean or date-time, and not part of `PARTITION BY`. |
+| `state_ttl` | No | The minimum time the state of an idle key is retained, in
processing time, as a non-negative `INTERVAL` literal. If omitted,
`table.exec.state.ttl` applies, including later changes to it. `INTERVAL '0'`
retains state indefinitely; the TTL is then left out of the state, so one
cannot be added later without losing the state. When the state of a key
expires, its next record is treated as the first one and emits `+I` again, so a
sink without an upsert key can hold several rows for that key. |
+| `reset_ttl_on_duplicate` | No | Whether a record that is dropped refreshes
the TTL of its key. Defaults to `true`. Has no effect without a TTL. |
+| `uid` | No | A unique identifier for the PTF invocation. Defaults to
`DEDUPLICATE`. See [Multiple Invocations]({{< ref
"docs/sql/reference/queries/changelog" >}}#multiple-invocations). |
+
+System arguments such as `uid` come last.
Review Comment:
Can we drop this? It's a general PTF rule, not specific to `DEDUPLICATE`.
##########
docs/content/docs/sql/reference/queries/deduplication.md:
##########
@@ -69,4 +69,114 @@ FROM (
WHERE row_num = 1
```
+## DEDUPLICATE
+
+{{< label Streaming >}}
+
+The `DEDUPLICATE` process table function (PTF) keeps the first or the last row
per key of an append-only table, as long as the state of the key is retained.
Unlike the `ROW_NUMBER()` pattern above, it needs no time attribute. Rows are
ordered by arrival or by `sequence_by` columns.
+
+### Syntax
+
+```sql
+SELECT * FROM DEDUPLICATE(
+ input => TABLE source_table PARTITION BY key_col[, ...],
+ [keep => 'first' | 'last',]
+ [sequence_by => DESCRIPTOR(col[, ...]),]
+ [state_ttl => INTERVAL '1' HOUR,]
+ [reset_ttl_on_duplicate => true | false,]
+ [uid => 'my-id']
+)
+```
+
+### Parameters
+
+| Parameter | Required | Description |
+|:----------|:---------|:------------|
+| `input` | Yes | The append-only input table. `PARTITION BY` is required and
its columns are the key of the output. `ORDER BY` is not supported, use
`sequence_by` instead. |
+| `keep` | No | `'first'` (default) keeps the earliest arrival or the smallest
`sequence_by` value, `'last'` keeps the latest arrival or the greatest. Must be
a literal. Arrival order is not deterministic across reruns, use `sequence_by`
for a repeatable result. |
+| `sequence_by` | No | One or more columns that order the records of a key
instead of their arrival. The first column decides, later columns break ties,
and `NULL` orders below every other value. On a tie, the later arrival wins for
`'last'` and the earlier one for `'first'`. Columns must be numeric, string,
binary, boolean or date-time, and not part of `PARTITION BY`. |
+| `state_ttl` | No | The minimum time the state of an idle key is retained, in
processing time, as a non-negative `INTERVAL` literal. If omitted,
`table.exec.state.ttl` applies, including later changes to it. `INTERVAL '0'`
retains state indefinitely; the TTL is then left out of the state, so one
cannot be added later without losing the state. When the state of a key
expires, its next record is treated as the first one and emits `+I` again, so a
sink without an upsert key can hold several rows for that key. |
+| `reset_ttl_on_duplicate` | No | Whether a record that is dropped refreshes
the TTL of its key. Defaults to `true`. Has no effect without a TTL. |
+| `uid` | No | A unique identifier for the PTF invocation. Defaults to
`DEDUPLICATE`. See [Multiple Invocations]({{< ref
"docs/sql/reference/queries/changelog" >}}#multiple-invocations). |
+
+System arguments such as `uid` come last.
+
+### Output
+
+The output columns are the `PARTITION BY` columns, in the order listed,
followed by the remaining input columns. Time attributes are emitted as regular
timestamps.
+
+The result is an updating table whose key is the `PARTITION BY` columns. The
first record of a key emits `+I`. A record that replaces the kept one emits
`+U`, preceded by `-U` if the consumer needs retractions. A record that equals
the kept one, or that does not replace it, emits nothing. With `keep =>
'first'` and no `sequence_by`, only `+I` is emitted. The output is currently
declared as updating in every case, so a sink that cannot consume updates is
rejected at planning time. See [Updates and Changelogs]({{< ref
"docs/dev/table/functions/ptfs" >}}#updates-and-changelogs) for the meaning of
the change flags.
+
+### Examples
+
+#### Keep the first or the last record
+
+```sql
+CREATE TABLE user_events (user_id STRING, event STRING) WITH (...);
+-- (u1, login), (u2, login), (u1, click)
+
+SELECT * FROM DEDUPLICATE(
+ input => TABLE user_events PARTITION BY user_id);
+
+-- +I[u1, login]
+-- +I[u2, login]
+
+SELECT * FROM DEDUPLICATE(
+ input => TABLE user_events PARTITION BY user_id,
+ keep => 'last');
+
+-- +I[u1, login]
+-- +I[u2, login]
+-- +U[u1, click] (an upsert consumer; a retract consumer also gets -U[u1,
login] before it)
Review Comment:
Same idea for the examples: showing the resulting table (or the final rows)
is easier to follow than the change flags, and we can drop the upsert/retract
consumer note.
##########
docs/content/docs/sql/reference/queries/deduplication.md:
##########
@@ -69,4 +69,114 @@ FROM (
WHERE row_num = 1
```
+## DEDUPLICATE
+
+{{< label Streaming >}}
+
+The `DEDUPLICATE` process table function (PTF) keeps the first or the last row
per key of an append-only table, as long as the state of the key is retained.
Unlike the `ROW_NUMBER()` pattern above, it needs no time attribute. Rows are
ordered by arrival or by `sequence_by` columns.
+
+### Syntax
+
+```sql
+SELECT * FROM DEDUPLICATE(
+ input => TABLE source_table PARTITION BY key_col[, ...],
+ [keep => 'first' | 'last',]
+ [sequence_by => DESCRIPTOR(col[, ...]),]
+ [state_ttl => INTERVAL '1' HOUR,]
+ [reset_ttl_on_duplicate => true | false,]
+ [uid => 'my-id']
+)
+```
+
+### Parameters
+
+| Parameter | Required | Description |
+|:----------|:---------|:------------|
+| `input` | Yes | The append-only input table. `PARTITION BY` is required and
its columns are the key of the output. `ORDER BY` is not supported, use
`sequence_by` instead. |
+| `keep` | No | `'first'` (default) keeps the earliest arrival or the smallest
`sequence_by` value, `'last'` keeps the latest arrival or the greatest. Must be
a literal. Arrival order is not deterministic across reruns, use `sequence_by`
for a repeatable result. |
+| `sequence_by` | No | One or more columns that order the records of a key
instead of their arrival. The first column decides, later columns break ties,
and `NULL` orders below every other value. On a tie, the later arrival wins for
`'last'` and the earlier one for `'first'`. Columns must be numeric, string,
binary, boolean or date-time, and not part of `PARTITION BY`. |
+| `state_ttl` | No | The minimum time the state of an idle key is retained, in
processing time, as a non-negative `INTERVAL` literal. If omitted,
`table.exec.state.ttl` applies, including later changes to it. `INTERVAL '0'`
retains state indefinitely; the TTL is then left out of the state, so one
cannot be added later without losing the state. When the state of a key
expires, its next record is treated as the first one and emits `+I` again, so a
sink without an upsert key can hold several rows for that key. |
+| `reset_ttl_on_duplicate` | No | Whether a record that is dropped refreshes
the TTL of its key. Defaults to `true`. Has no effect without a TTL. |
+| `uid` | No | A unique identifier for the PTF invocation. Defaults to
`DEDUPLICATE`. See [Multiple Invocations]({{< ref
"docs/sql/reference/queries/changelog" >}}#multiple-invocations). |
+
+System arguments such as `uid` come last.
+
+### Output
+
+The output columns are the `PARTITION BY` columns, in the order listed,
followed by the remaining input columns. Time attributes are emitted as regular
timestamps.
+
+The result is an updating table whose key is the `PARTITION BY` columns. The
first record of a key emits `+I`. A record that replaces the kept one emits
`+U`, preceded by `-U` if the consumer needs retractions. A record that equals
the kept one, or that does not replace it, emits nothing. With `keep =>
'first'` and no `sequence_by`, only `+I` is emitted. The output is currently
declared as updating in every case, so a sink that cannot consume updates is
rejected at planning time. See [Updates and Changelogs]({{< ref
"docs/dev/table/functions/ptfs" >}}#updates-and-changelogs) for the meaning of
the change flags.
Review Comment:
I'd like to propose an idea: I'd describe the result in table terms and keep
the change flags out of the main text, like in the TO_APPEND docs (#30016). For
example: "The result has one row per key. When a later row replaces the kept
one, the row of that key is updated, so the result must be written to a table
that accepts updates, e.g. one with a primary key on the `PARTITION BY`
columns." The `+I`/`+U` details could go into a short hint box, if at all.
##########
docs/content/docs/sql/reference/queries/deduplication.md:
##########
@@ -69,4 +69,114 @@ FROM (
WHERE row_num = 1
```
+## DEDUPLICATE
+
+{{< label Streaming >}}
+
+The `DEDUPLICATE` process table function (PTF) keeps the first or the last row
per key of an append-only table, as long as the state of the key is retained.
Unlike the `ROW_NUMBER()` pattern above, it needs no time attribute. Rows are
ordered by arrival or by `sequence_by` columns.
+
+### Syntax
+
+```sql
+SELECT * FROM DEDUPLICATE(
+ input => TABLE source_table PARTITION BY key_col[, ...],
+ [keep => 'first' | 'last',]
+ [sequence_by => DESCRIPTOR(col[, ...]),]
+ [state_ttl => INTERVAL '1' HOUR,]
+ [reset_ttl_on_duplicate => true | false,]
+ [uid => 'my-id']
+)
+```
+
+### Parameters
+
+| Parameter | Required | Description |
+|:----------|:---------|:------------|
+| `input` | Yes | The append-only input table. `PARTITION BY` is required and
its columns are the key of the output. `ORDER BY` is not supported, use
`sequence_by` instead. |
+| `keep` | No | `'first'` (default) keeps the earliest arrival or the smallest
`sequence_by` value, `'last'` keeps the latest arrival or the greatest. Must be
a literal. Arrival order is not deterministic across reruns, use `sequence_by`
for a repeatable result. |
+| `sequence_by` | No | One or more columns that order the records of a key
instead of their arrival. The first column decides, later columns break ties,
and `NULL` orders below every other value. On a tie, the later arrival wins for
`'last'` and the earlier one for `'first'`. Columns must be numeric, string,
binary, boolean or date-time, and not part of `PARTITION BY`. |
+| `state_ttl` | No | The minimum time the state of an idle key is retained, in
processing time, as a non-negative `INTERVAL` literal. If omitted,
`table.exec.state.ttl` applies, including later changes to it. `INTERVAL '0'`
retains state indefinitely; the TTL is then left out of the state, so one
cannot be added later without losing the state. When the state of a key
expires, its next record is treated as the first one and emits `+I` again, so a
sink without an upsert key can hold several rows for that key. |
+| `reset_ttl_on_duplicate` | No | Whether a record that is dropped refreshes
the TTL of its key. Defaults to `true`. Has no effect without a TTL. |
+| `uid` | No | A unique identifier for the PTF invocation. Defaults to
`DEDUPLICATE`. See [Multiple Invocations]({{< ref
"docs/sql/reference/queries/changelog" >}}#multiple-invocations). |
+
+System arguments such as `uid` come last.
+
+### Output
+
+The output columns are the `PARTITION BY` columns, in the order listed,
followed by the remaining input columns. Time attributes are emitted as regular
timestamps.
+
+The result is an updating table whose key is the `PARTITION BY` columns. The
first record of a key emits `+I`. A record that replaces the kept one emits
`+U`, preceded by `-U` if the consumer needs retractions. A record that equals
the kept one, or that does not replace it, emits nothing. With `keep =>
'first'` and no `sequence_by`, only `+I` is emitted. The output is currently
declared as updating in every case, so a sink that cannot consume updates is
rejected at planning time. See [Updates and Changelogs]({{< ref
"docs/dev/table/functions/ptfs" >}}#updates-and-changelogs) for the meaning of
the change flags.
+
+### Examples
+
+#### Keep the first or the last record
+
+```sql
+CREATE TABLE user_events (user_id STRING, event STRING) WITH (...);
+-- (u1, login), (u2, login), (u1, click)
+
+SELECT * FROM DEDUPLICATE(
+ input => TABLE user_events PARTITION BY user_id);
+
+-- +I[u1, login]
+-- +I[u2, login]
+
+SELECT * FROM DEDUPLICATE(
+ input => TABLE user_events PARTITION BY user_id,
+ keep => 'last');
+
+-- +I[u1, login]
+-- +I[u2, login]
+-- +U[u1, click] (an upsert consumer; a retract consumer also gets -U[u1,
login] before it)
+```
+
+#### Order by a column
+
+```sql
+CREATE TABLE customer_changes (customer_id STRING, city STRING, lsn BIGINT)
WITH (...);
+-- (c1, Berlin, 100), (c1, Munich, 120), (c1, Hamburg, 110)
+
+SELECT * FROM DEDUPLICATE(
+ input => TABLE customer_changes PARTITION BY customer_id,
+ keep => 'last',
+ sequence_by => DESCRIPTOR(lsn),
+ state_ttl => INTERVAL '1' DAY);
+
+-- +I[c1, Berlin, 100]
+-- +U[c1, Munich, 120] (The entry (c1, Hamburg, 110) is older than the
kept, row -> dropped)
Review Comment:
Small wording fix:
```suggestion
-- +U[c1, Munich, 120] (Hamburg has a smaller lsn than the kept row, so
it is dropped)
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]