SQL Snippets

References

Debugging and exploration

Show execution plans

EXPLAIN          SELECT /*+ COALESCE(1) */ * FROM t;
EXPLAIN EXTENDED SELECT * FROM t;
EXPLAIN CODEGEN  SELECT * FROM t;

Describe a table.

DESC t;
DESC EXTENDED t;
DESC FORMATTED t;

Performance tweaks, SQL hints, environment variables

Select and coalesce to one partition.
```
SELECT /*+ COALESCE(1) */ * FROM t
```

Select and repartition based on a column.

SELECT /*+ REPARTITION(t.id) */ * FROM t

Specify which table to broadcast inside a JOIN (the table must fit into the worker-memory).
```
SELECT /*+ BROADCAST(t1) */ * FROM t1 LEFT JOIN t2 ON t1.id = t2.id
```
Enable AQE.
```
SET spark.sql.adaptive.enabled=true
```
Maintain impala compatibility when saving DECIMAL columns inside Parquet
```
SET spark.sql.parquet.writeLegacyFormat=true
```

Simple versioning

Implement a simple versioning scheme (add an insertion_date column for timestamp, and auto-incremented version for every run within the same day).

SELECT
  *,
  DATE_FORMAT(CURRENT_DATE(), 'yyyyMMdd') AS insertion_date,
  COALESCE(
    (SELECT MAX(version) FROM t WHERE insertion_date = DATE_FORMAT(CURRENT_DATE(), 'yyyyMMdd')),
    0
  ) + 1 AS version
FROM t

Choose the latest version (based on the versioning scheme above).

WITH
  versioned_latest_date AS (
    SELECT MAX(insertion_date) FROM t
  ),
  versioned_latest_version AS (
    SELECT MAX(version) FROM t WHERE data = (SELECT * FROM versioned_latest_date)
  )

SELECT *
  FROM t
  WHERE insertion_date = (SELECT * FROM versioned_latest_date)
  AND   version        = (SELECT * FROM versioned_latest_version)

Read lines written in the past 10 days.

SELECT *
  FROM t
  WHERE insertion_date >= DATE_FORMAT(DATE_SUB(CURRENT_DATE(), 10), 'yyyyMMdd')

Misc

Add a temporary column with unique IDs (similar to a primary-key).

SELECT
  *,
  MONOTONICALLY_INCREASING_ID() AS id
FROM t

Create an inline translation table.

WITH
  capitalCities AS (
    SELECT *
    FROM VALUES
      ("France" , "Paris" ),
      ("Germany", "Berlin"),
      ("Italy"  , "Rome"  )
    AS data(country, capital)
  )

SELECT *
  FROM stats
  INNER JOIN capitalCities
  ON stats.city = capitalCities.capital

Match against an interval uing EquiJoin.

WITH
  possible_values AS (SELECT EXPLODE(SEQUENCE(200, 300)) AS e)

SELECT t.*
  FROM t
  INNER JOIN possible_values
  ON t.val = possible_values.e