[
https://issues.apache.org/jira/browse/SPARK-61276?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Dimitrije Zdravkovic updated SPARK-61276:
-----------------------------------------
Description:
h2. Summary
Add a new SQL relation operator {{GAP FILL (...)}} that puts each partition of
a time series on a regular grid of bins. It adds a row at every bin start that
has no input row at exactly that time, and it can fill chosen columns in the
added rows. Input rows are kept unchanged. Target use case: IoT, telemetry and
generally time series data with missing or uneven readings, where users today
build the grid by hand with {{explode(sequence(...))}} and a join.
h2. Syntax
{code:sql}
relation GAP FILL (
[ PARTITION BY partition_column [, ...] ]
ORDER BY time_column
BIN WIDTH interval_expr
[ { ALIGN TO origin_expr |
FROM range_start TO range_end |
FILL ( fill_column fill_method [, ...] ) |
MAX GAP max_gap_expr |
BIN_START [ AS bin_start_alias ] |
IS_GENERATED [ AS is_generated_alias ] } [...] ]
) [ [ AS ] table_alias ]
fill_method: { LINEAR | FFILL | FORWARD FILL | BFILL | BACKWARD FILL | CONSTANT
constant_expr | NULL }
{code}
h2. Clauses
* {{{}PARTITION BY partition_column, ...{}}}: optional. Rows with the same
values in all partition columns form one partition, and each partition is
filled on its own. Rows whose partition columns are NULL form one partition.
Without PARTITION BY, all rows form one partition.
* {{{}ORDER BY time_column{}}}: the TIMESTAMP, TIMESTAMP_NTZ or DATE column
that holds the time of each row. Rows with a NULL time are kept unchanged and
add no rows.
* {{{}BIN WIDTH interval_expr{}}}: a positive, constant day-time interval. For
a DATE column it must be a whole number of days.
* {{{}ALIGN TO origin_expr{}}}: optional constant of the time column's type
that sets where bins start. The default is {{{}1970-01-01 00:00:00{}}}, which
gives the same grid as {{{}time_bucket{}}}.
* {{{}FROM range_start TO range_end{}}}: optional constants. Every partition
gets bins from range_start (inclusive) to range_end (exclusive). Without them,
each partition covers the bins from its first row to its last row. Rows outside
the range are kept and can be used to fill gaps inside it.
* {{{}FILL (fill_column fill_method, ...){}}}: optional fill method per
column. Columns not listed are NULL in added rows.
* {{{}MAX GAP max_gap_expr{}}}: optional positive day-time interval. Gaps
longer than this get no added rows. A gap equal to it is filled.
* {{BIN_START [AS alias]}} / {{{}IS_GENERATED [AS alias]{}}}: optional
appended columns.
* {{{}AS table_alias{}}}: optional alias for the result, like {{PIVOT}} and
{{{}UNPIVOT{}}}.
h2. Output schema
All input columns with their types, followed by the optional appended columns:
* {{bin_start}} (same type as the time column): the start of the bin the row
falls in.
* {{is_generated}} (BOOLEAN): true for added rows, false for input rows.
h2. Behavior
* An added row has the bin start as its time and the partition values in its
partition columns. Listed columns get their fill value, and all other columns
are NULL.
* A bin includes its start and excludes its end, like {{{}time_bucket{}}}.
* Fill methods skip NULLs when they look for the value before or after a gap.
At the edges, FFILL gives NULL before the first value, BFILL gives NULL after
the last value, and LINEAR gives NULL at both.
* LINEAR on integer and DECIMAL columns computes the exact value and rounds it
once, half away from zero.
* GAP FILL does not aggregate and does not guarantee an output order. An empty
input gives an empty result, also with FROM and TO.
h2. Examples
{code:sql}
/* 15-minute grid per device, with three fill methods */
SELECT *
FROM sensor_readings GAP FILL (
PARTITION BY device_id
ORDER BY ts
BIN WIDTH INTERVAL 15 MINUTES
FILL (temperature LINEAR, status FFILL, alarm_count CONSTANT 0)
IS_GENERATED AS is_gen
)
/* The same range for every device, no added rows in gaps longer than one hour
*/
SELECT *
FROM sensor_readings GAP FILL (
PARTITION BY device_id
ORDER BY ts
BIN WIDTH INTERVAL 15 MINUTES
FROM TIMESTAMP '2026-01-01 00:00:00' TO TIMESTAMP '2026-01-02 00:00:00'
FILL (temperature FFILL)
MAX GAP INTERVAL 1 HOUR
BIN_START AS bucket_ts
)
/* Aggregate into buckets first, then fill the empty buckets */
SELECT *
FROM (
SELECT device_id, time_bucket(INTERVAL 5 MINUTES, ts) AS bucket, avg(value)
AS avg_value
FROM metrics
GROUP BY ALL
) GAP FILL (
PARTITION BY device_id
ORDER BY bucket
BIN WIDTH INTERVAL 5 MINUTES
FILL (avg_value LINEAR)
)
{code}
was:
h2. Summary
Add a new SQL relation operator {{GAP FILL (...)}} that puts each partition of
a time series on a regular grid of bins. It adds a row at every bin start that
has no input row at exactly that time, and it can fill chosen columns in the
added rows. Input rows are kept unchanged. Target use case: IoT, telemetry and
generally time series data with missing or uneven readings, where users today
build the grid by hand with {{explode(sequence(...))}} and a join.
h2. Syntax
{code:sql}
relation GAP FILL (
[ PARTITION BY partition_column [, ...] ]
ORDER BY time_column
BIN WIDTH interval_expr
[ { ALIGN TO origin_expr |
FROM range_start TO range_end |
FILL ( fill_column fill_method [, ...] ) |
MAX GAP max_gap_expr |
BIN_START [ AS bin_start_alias ] |
IS_GENERATED [ AS is_generated_alias ] } [...] ]
) [ [ AS ] table_alias ]
fill_method: \{ LINEAR | FFILL | FORWARD FILL | BFILL | BACKWARD FILL |
CONSTANT constant_expr | NULL }
{code}
h2. Clauses
* {{{}PARTITION BY partition_column, ...{}}}: optional. Rows with the same
values in all partition columns form one partition, and each partition is
filled on its own. Rows whose partition columns are NULL form one partition.
Without PARTITION BY, all rows form one partition.
* {{{}ORDER BY time_column{}}}: the TIMESTAMP, TIMESTAMP_NTZ or DATE column
that holds the time of each row. Rows with a NULL time are kept unchanged and
add no rows.
* {{{}BIN WIDTH interval_expr{}}}: a positive, constant day-time interval. For
a DATE column it must be a whole number of days.
* {{{}ALIGN TO origin_expr{}}}: optional constant of the time column's type
that sets where bins start. The default is {{{}1970-01-01 00:00:00{}}}, which
gives the same grid as {{{}time_bucket{}}}.
* {{{}FROM range_start TO range_end{}}}: optional constants. Every partition
gets bins from range_start (inclusive) to range_end (exclusive). Without them,
each partition covers the bins from its first row to its last row. Rows outside
the range are kept and can be used to fill gaps inside it.
* {{{}FILL (fill_column fill_method, ...){}}}: optional fill method per
column. Columns not listed are NULL in added rows.
* {{{}MAX GAP max_gap_expr{}}}: optional positive day-time interval. Gaps
longer than this get no added rows. A gap equal to it is filled.
* {{BIN_START [AS alias]}} / {{{}IS_GENERATED [AS alias]{}}}: optional
appended columns.
* {{{}AS table_alias{}}}: optional alias for the result, like {{PIVOT}} and
{{{}UNPIVOT{}}}.
h2. Output schema
All input columns with their types, followed by the optional appended columns:
* {{bin_start}} (same type as the time column): the start of the bin the row
falls in.
* {{is_generated}} (BOOLEAN): true for added rows, false for input rows.
h2. Behavior
* An added row has the bin start as its time and the partition values in its
partition columns. Listed columns get their fill value, and all other columns
are NULL.
* A bin includes its start and excludes its end, like {{{}time_bucket{}}}.
* Fill methods skip NULLs when they look for the value before or after a gap.
At the edges, FFILL gives NULL before the first value, BFILL gives NULL after
the last value, and LINEAR gives NULL at both.
* LINEAR on integer and DECIMAL columns computes the exact value and rounds it
once, half away from zero.
* GAP FILL does not aggregate and does not guarantee an output order. An empty
input gives an empty result, also with FROM and TO.
h2. Examples
{code:sql}
/* 15-minute grid per device, with three fill methods */
SELECT *
FROM sensor_readings GAP FILL (
PARTITION BY device_id
ORDER BY ts
BIN WIDTH INTERVAL 15 MINUTES
FILL (temperature LINEAR, status FFILL, alarm_count CONSTANT 0)
IS_GENERATED AS is_gen
)
/* The same range for every device, no added rows in gaps longer than one hour
*/
SELECT *
FROM sensor_readings GAP FILL (
PARTITION BY device_id
ORDER BY ts
BIN WIDTH INTERVAL 15 MINUTES
FROM TIMESTAMP '2026-01-01 00:00:00' TO TIMESTAMP '2026-01-02 00:00:00'
FILL (temperature FFILL)
MAX GAP INTERVAL 1 HOUR
BIN_START AS bucket_ts
)
/* Aggregate into buckets first, then fill the empty buckets */
SELECT *
FROM (
SELECT device_id, time_bucket(INTERVAL 5 MINUTES, ts) AS bucket, avg(value)
AS avg_value
FROM metrics
GROUP BY ALL
) GAP FILL (
PARTITION BY device_id
ORDER BY bucket
BIN WIDTH INTERVAL 5 MINUTES
FILL (avg_value LINEAR)
)
{code}
> Add GAP FILL relation operator
> ------------------------------
>
> Key: SPARK-61276
> URL: https://issues.apache.org/jira/browse/SPARK-61276
> Project: Spark
> Issue Type: Umbrella
> Components: SQL
> Affects Versions: 5.0.0
> Reporter: Dimitrije Zdravkovic
> Priority: Major
>
> h2. Summary
> Add a new SQL relation operator {{GAP FILL (...)}} that puts each partition
> of a time series on a regular grid of bins. It adds a row at every bin start
> that has no input row at exactly that time, and it can fill chosen columns in
> the added rows. Input rows are kept unchanged. Target use case: IoT,
> telemetry and generally time series data with missing or uneven readings,
> where users today build the grid by hand with {{explode(sequence(...))}} and
> a join.
> h2. Syntax
> {code:sql}
> relation GAP FILL (
> [ PARTITION BY partition_column [, ...] ]
> ORDER BY time_column
> BIN WIDTH interval_expr
> [ { ALIGN TO origin_expr |
> FROM range_start TO range_end |
> FILL ( fill_column fill_method [, ...] ) |
> MAX GAP max_gap_expr |
> BIN_START [ AS bin_start_alias ] |
> IS_GENERATED [ AS is_generated_alias ] } [...] ]
> ) [ [ AS ] table_alias ]
> fill_method: { LINEAR | FFILL | FORWARD FILL | BFILL | BACKWARD FILL |
> CONSTANT constant_expr | NULL }
> {code}
> h2. Clauses
> * {{{}PARTITION BY partition_column, ...{}}}: optional. Rows with the same
> values in all partition columns form one partition, and each partition is
> filled on its own. Rows whose partition columns are NULL form one partition.
> Without PARTITION BY, all rows form one partition.
> * {{{}ORDER BY time_column{}}}: the TIMESTAMP, TIMESTAMP_NTZ or DATE column
> that holds the time of each row. Rows with a NULL time are kept unchanged and
> add no rows.
> * {{{}BIN WIDTH interval_expr{}}}: a positive, constant day-time interval.
> For a DATE column it must be a whole number of days.
> * {{{}ALIGN TO origin_expr{}}}: optional constant of the time column's type
> that sets where bins start. The default is {{{}1970-01-01 00:00:00{}}}, which
> gives the same grid as {{{}time_bucket{}}}.
> * {{{}FROM range_start TO range_end{}}}: optional constants. Every partition
> gets bins from range_start (inclusive) to range_end (exclusive). Without
> them, each partition covers the bins from its first row to its last row. Rows
> outside the range are kept and can be used to fill gaps inside it.
> * {{{}FILL (fill_column fill_method, ...){}}}: optional fill method per
> column. Columns not listed are NULL in added rows.
> * {{{}MAX GAP max_gap_expr{}}}: optional positive day-time interval. Gaps
> longer than this get no added rows. A gap equal to it is filled.
> * {{BIN_START [AS alias]}} / {{{}IS_GENERATED [AS alias]{}}}: optional
> appended columns.
> * {{{}AS table_alias{}}}: optional alias for the result, like {{PIVOT}} and
> {{{}UNPIVOT{}}}.
> h2. Output schema
> All input columns with their types, followed by the optional appended columns:
> * {{bin_start}} (same type as the time column): the start of the bin the row
> falls in.
> * {{is_generated}} (BOOLEAN): true for added rows, false for input rows.
> h2. Behavior
> * An added row has the bin start as its time and the partition values in its
> partition columns. Listed columns get their fill value, and all other columns
> are NULL.
> * A bin includes its start and excludes its end, like {{{}time_bucket{}}}.
> * Fill methods skip NULLs when they look for the value before or after a
> gap. At the edges, FFILL gives NULL before the first value, BFILL gives NULL
> after the last value, and LINEAR gives NULL at both.
> * LINEAR on integer and DECIMAL columns computes the exact value and rounds
> it once, half away from zero.
> * GAP FILL does not aggregate and does not guarantee an output order. An
> empty input gives an empty result, also with FROM and TO.
> h2. Examples
> {code:sql}
> /* 15-minute grid per device, with three fill methods */
> SELECT *
> FROM sensor_readings GAP FILL (
> PARTITION BY device_id
> ORDER BY ts
> BIN WIDTH INTERVAL 15 MINUTES
> FILL (temperature LINEAR, status FFILL, alarm_count CONSTANT 0)
> IS_GENERATED AS is_gen
> )
> /* The same range for every device, no added rows in gaps longer than one
> hour */
> SELECT *
> FROM sensor_readings GAP FILL (
> PARTITION BY device_id
> ORDER BY ts
> BIN WIDTH INTERVAL 15 MINUTES
> FROM TIMESTAMP '2026-01-01 00:00:00' TO TIMESTAMP '2026-01-02 00:00:00'
> FILL (temperature FFILL)
> MAX GAP INTERVAL 1 HOUR
> BIN_START AS bucket_ts
> )
> /* Aggregate into buckets first, then fill the empty buckets */
> SELECT *
> FROM (
> SELECT device_id, time_bucket(INTERVAL 5 MINUTES, ts) AS bucket, avg(value)
> AS avg_value
> FROM metrics
> GROUP BY ALL
> ) GAP FILL (
> PARTITION BY device_id
> ORDER BY bucket
> BIN WIDTH INTERVAL 5 MINUTES
> FILL (avg_value LINEAR)
> )
> {code}
--
This message was sent by Atlassian Jira
(v10.3.26#10030026)
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
