[
https://issues.apache.org/jira/browse/SPARK-61276?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Dimitrije Zdravkovic updated SPARK-61276:
-----------------------------------------
Affects Version/s: 4.4.0
(was: 5.0.0)
> 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: 4.4.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]
