/*
Iceberg v3 Variant Performance Benchmark — Databricks all queries
=================================================================

This file contains the table definitions, synthetic data generation scripts,
and the 11 benchmark queries used in the Iceberg v3 Variant Performance blog
post on the Snowflake engineering blog (https://www.snowflake.com/en/engineering-blog).

The companion file `iceberg-variant-snowflake-all-queries.sql` reproduces
the same benchmark on Snowflake-managed Iceberg tables so the comparison can
be re-run end-to-end on either engine.

Prerequisites
-------------
  * Databricks SQL warehouse with Iceberg + VARIANT support enabled.
  * An XXS Serverless cluster (or larger) for representative latencies — the
    benchmark in the blog post uses XXS Serverless.

Methodology
-----------
  * Each query is run with 2 warmup runs followed by 5 measured runs.
  * The reported metric is the median end-to-end latency in milliseconds.
  * Result caching is disabled on the SQL Warehouse before the measurement runs.

Sections
--------
  1. Table definitions (DDL)
  2. Synthetic data generation
  3. Benchmark queries (Q1–Q11) across the four workload categories:
       - Workload 1: accessing specific fields from a Variant object
       - Workload 2: accessing the entire Variant object
       - Workload 3: accessing specific elements from a Variant array
       - Workload 4: accessing entire Variant arrays
*/

-- Disable result caching on the SQL Warehouse before measurements.
SET use_cached_result = false;


/* =========================================================
   Section 1 — Table definitions (DDL)
   ========================================================= */

-- events_variant: 500M-row event log with a single VARIANT payload column.
-- Used for Workload 1 (field extraction) and Workload 2 (full-object retrieval).
CREATE OR REPLACE TABLE events_variant (
    event_id BIGINT NOT NULL,
    payload  VARIANT
) USING ICEBERG;

-- arrays_variant: 10M-row table with three array-shaped VARIANT columns
-- (256-element string array, 256-element integer array, 64×64 2D integer matrix).
-- Used for Workload 3 (array element access) and Workload 4 (full-array retrieval).
CREATE TABLE arrays_variant (
    id         BIGINT NOT NULL,
    arr_text   VARIANT,
    arr_number VARIANT,
    arr_graph  VARIANT
) USING ICEBERG;


/* =========================================================
   Section 2 — Synthetic data generation
   ========================================================= */

-- ---------------------------------------------------------
-- 2.1  Populate events_variant (500M rows)
-- ---------------------------------------------------------
INSERT INTO events_variant
WITH event_types AS (
    SELECT ARRAY(
        'page_view','click','scroll','purchase','add_to_cart',
        'remove_from_cart','search','login','logout','signup',
        'share','comment','like','bookmark','download',
        'upload','print','export','import','settings_change'
    ) AS v
),
countries AS (
    SELECT ARRAY(
        'US','CN','IN','BR','JP','RU','DE','GB','FR','MX',
        'ID','NG','PK','BD','VN','KR','TR','IT','ES','TH',
        'PH','EG','AR','PL','CA','MY','AU','NL','SA','PE',
        'CO','ZA','UA','RO','CL','CZ','BE','SE','PT','AT',
        'CH','IL','HK','SG','NZ','IE','DK','FI','NO','GR'
    ) AS v
),
raw AS (
    SELECT
        id                                                       AS event_id,
        RAND()                                                   AS p_skew,
        CAST(FLOOR(RAND() * 4)       AS INT)                    AS et_top4_idx,
        CAST(FLOOR(RAND() * 20)      AS INT)                    AS et_all_idx,
        CAST(FLOOR(RAND() * 50)      AS INT)                    AS country_idx,
        CAST(FLOOR(RAND() * 300000)  AS INT)                    AS duration,
        CAST(FLOOR(RAND() * 10000000)AS BIGINT)                 AS user_id,
        RAND()                                                   AS p_premium,
        LOWER(SUBSTRING(MD5(CAST(RAND() AS STRING)), 1, 16))    AS session_str,
        CAST(FLOOR(RAND() * 5000)    AS INT)                    AS load_ms,
        CAST(FLOOR(RAND() * 10000)   AS INT)                    AS render_ms,
        RAND()                                                   AS p_is_bot,
        CAST(FLOOR(RAND() * 50)      AS INT)                    AS tag1,
        CAST(FLOOR(RAND() * 50)      AS INT)                    AS tag2
    FROM RANGE(500000000)
)
SELECT
    r.event_id,
    PARSE_JSON(CONCAT(
        '{"event_type":"',
        IF(r.p_skew < 0.8, et.v[r.et_top4_idx], et.v[r.et_all_idx]),
        '","country":"',  c.v[r.country_idx],
        '","duration":',  r.duration,
        ',"user":{"id":', r.user_id,
        ',"is_premium":', IF(r.p_premium < 0.15, 'true', 'false'),
        ',"session":"',   r.session_str,
        '"},"metrics":{"timing":{"load_ms":', r.load_ms,
        ',"render_ms":',  r.render_ms,
        '},"flags":{"is_bot":', IF(r.p_is_bot < 0.05, 'true', 'false'),
        '}},"tags":["tag_', r.tag1, '","tag_', r.tag2,
        '"],"label":"item_', MOD(r.event_id, 10000), '"}'
    )) AS payload
FROM raw r, event_types et, countries c;


-- ---------------------------------------------------------
-- 2.2  Populate arrays_variant (10M rows)
-- ---------------------------------------------------------
INSERT INTO arrays_variant
SELECT
    id,
    PARSE_JSON(
        CONCAT('[', ARRAY_JOIN(
            TRANSFORM(SEQUENCE(0, 255), x -> CONCAT('"', LOWER(SUBSTRING(MD5(CONCAT(CAST(id AS STRING), '_', CAST(x AS STRING), '_', CAST(RAND() AS STRING))), 1, 16)), '"'))
        , ','), ']')
    ) AS arr_text,
    PARSE_JSON(
        CONCAT('[', ARRAY_JOIN(
            TRANSFORM(SEQUENCE(0, 255), x -> CAST(CAST(FLOOR(RAND() * 1000000) AS INT) AS STRING))
        , ','), ']')
    ) AS arr_number,
    PARSE_JSON(
        CONCAT('[', ARRAY_JOIN(
            TRANSFORM(SEQUENCE(0, 63), i ->
                CONCAT('[', ARRAY_JOIN(
                    TRANSFORM(SEQUENCE(0, 63), j -> CAST(CAST(FLOOR(RAND() * 1000000) AS INT) AS STRING))
                , ','), ']')
            )
        , ','), ']')
    ) AS arr_graph
FROM RANGE(10000000);


/* =========================================================
   Section 3 — Benchmark queries (Q1–Q11)
   ========================================================= */

-- ---------------------------------------------------------
-- Workload 1 — Accessing specific fields from a Variant object
-- ---------------------------------------------------------

-- Q1: Top-level field aggregation
SELECT SUM(try_variant_get(payload, '$.duration', 'int')) FROM events_variant;

-- Q2: Deeply nested path traversal
SELECT SUM(try_variant_get(payload, '$.metrics.timing.load_ms', 'int')) FROM events_variant;

-- Q3: Filtered aggregation with predicates on variant fields
SELECT COUNT(event_id), SUM(try_variant_get(payload, '$.duration', 'int'))
FROM events_variant
WHERE try_variant_get(payload, '$.event_type', 'string') = 'purchase'
  AND try_variant_get(payload, '$.country', 'string') = 'US';

-- Q4: Grouped aggregation across variant fields
SELECT try_variant_get(payload, '$.event_type', 'string') AS event_type,
       COUNT(event_id) AS cnt,
       SUM(try_variant_get(payload, '$.duration', 'int')) AS total_duration
FROM events_variant
GROUP BY try_variant_get(payload, '$.event_type', 'string');


-- ---------------------------------------------------------
-- Workload 2 — Accessing the entire Variant object
-- ---------------------------------------------------------

-- Q5: Full object retrieval with sort
SELECT payload FROM events_variant ORDER BY payload:duration::INT LIMIT 10;


-- ---------------------------------------------------------
-- Workload 3 — Accessing specific elements from a Variant array
-- ---------------------------------------------------------

-- Q6: Numeric element access
SELECT SUM(try_variant_get(arr_number, '$[0]', 'int')) FROM arrays_variant;

-- Q7: String element access
SELECT MIN(try_variant_get(arr_text, '$[0]', 'string')) FROM arrays_variant;

-- Q8: Nested 2D array element access
SELECT SUM(try_variant_get(arr_graph, '$[0][3]', 'int')) FROM arrays_variant;


-- ---------------------------------------------------------
-- Workload 4 — Accessing entire Variant arrays
-- ---------------------------------------------------------

-- Q9: Full numeric array retrieval
SELECT arr_number FROM arrays_variant ORDER BY try_variant_get(arr_number, '$[0]', 'int') LIMIT 10;

-- Q10: Full string array retrieval
SELECT arr_text FROM arrays_variant ORDER BY try_variant_get(arr_text, '$[0]', 'string') LIMIT 10;

-- Q11: Full 2D array retrieval
SELECT arr_graph FROM arrays_variant ORDER BY try_variant_get(arr_graph, '$[0][3]', 'int') LIMIT 10;
