sqlload 0.61.0

SQL data loading tooling.
//! ClickHouse's writes: no transactions, so each is published by one swap of a whole table or of whole partitions, and a rerun converges.

use crate::adapters::clickhouse::_helpers::dialect::type_names::unwrapped;
use crate::adapters::clickhouse::constants::{ASIDE_SUFFIX, COPY_SUFFIX, PUBLISH_PREFIX};
use crate::adapters::clickhouse::models::{ClickHouseSql, PublishStep};
use crate::adapters::contract::constants::SWAP_SUFFIX;
use crate::adapters::contract::main::_ranked_source::ranked_source;
use crate::adapters::contract::models::{TableName, WriteMode, WriteStatementsRequest};
use crate::adapters::contract::types::SqlAdapter;
use crate::column_types::models::ColumnType;

/// `table` with `suffix` added to its name.
fn with_suffix(table: &TableName, suffix: &str) -> TableName {
    TableName {
        table: format!("{}{suffix}", table.table),
        ..table.clone()
    }
}

/// `step` as the statement the runner carries out itself.
pub(crate) fn publish_statement(step: &PublishStep) -> String {
    format!(
        "{PUBLISH_PREFIX}{}",
        serde_json::to_string(step).unwrap_or_default()
    )
}

/// `build` swapped in for `target` with `EXCHANGE TABLES`, then dropped, holding the old table.
pub(crate) fn swap_statements(
    adapter: &ClickHouseSql,
    target: &TableName,
    build: &TableName,
) -> Vec<String> {
    let build_name = adapter.render_qualified_name(build);
    vec![
        publish_statement(&PublishStep::Exchange {
            target: adapter.render_qualified_name(target),
            build: build_name.clone(),
            aside: adapter.render_qualified_name(&with_suffix(build, ASIDE_SUFFIX)),
        }),
        format!("DROP TABLE IF EXISTS {build_name}"),
    ]
}

/// The statements applying `request.mode` from `staging`. A build table is swapped in, once rows sent to it unpartitioned are partitioned (see [`PublishStep::FillBuild`]). Otherwise the staged rows, converted to the table's types, go into a delta table created `AS` the table, so it has its engine and keys; `replace` swaps that in whole, and the incremental modes publish its partitions, replacing the table's rows that `merge` matches by key (see [`displaced_keys`]) or `delete_insert` covers by its range or holds a value of its `replace_key` the delta holds.
pub(crate) fn write_statements(
    adapter: &ClickHouseSql,
    request: &WriteStatementsRequest<'_>,
    staging: &TableName,
) -> Vec<String> {
    if request.built.is_some() {
        let swap = with_suffix(staging, SWAP_SUFFIX);
        let fill = PublishStep::FillBuild {
            target: (request.table.schema.clone(), request.table.table.clone()),
            build: (staging.schema.clone(), staging.table.clone()),
            swap: swap.table.clone(),
        };
        return [
            publish_statement(&fill),
            format!(
                "DROP TABLE IF EXISTS {}",
                adapter.render_qualified_name(&swap)
            ),
        ]
        .into_iter()
        .chain(swap_statements(adapter, request.table, staging))
        .collect();
    }
    let target = adapter.render_qualified_name(request.table);
    let delta_table = with_suffix(staging, SWAP_SUFFIX);
    let delta = adapter.render_qualified_name(&delta_table);
    let names: Vec<String> = request
        .columns
        .iter()
        .map(|column| adapter.render_identifier(&column.name))
        .collect();
    let values: Vec<String> = request
        .columns
        .iter()
        .map(|column| {
            let value = adapter.render_source_expression_cast(&column.name, &column.column_type);
            match request.hashed.contains(&column.name) {
                true => format!("lower(hex(SHA256({value})))"),
                false => value,
            }
        })
        .collect();
    let staged = adapter.render_qualified_name(staging);
    let (from, values) =
        ranked_source(adapter, request, (&staged, &values), false).unwrap_or((staged, values));
    let values: Vec<String> = values
        .iter()
        .zip(&names)
        .map(|(value, name)| format!("{value} AS {name}"))
        .collect();
    let displaced = match request.mode {
        WriteMode::Replace | WriteMode::Recreate => None,
        WriteMode::Append | WriteMode::MarkMissing(_) => None,
        WriteMode::Merge { .. } => {
            let keys = displaced_keys(adapter, request.mode);
            Some(format!("({keys}) IN (SELECT {keys} FROM {delta})"))
        }
        WriteMode::DeleteInsert {
            cursor_column,
            replace_key,
            ..
        } => {
            let range = cursor_column
                .as_ref()
                .map(|_| range_condition(adapter, request));
            let keyed = (!replace_key.is_empty()).then(|| {
                let keys = displaced_keys(adapter, request.mode);
                format!("({keys}) IN (SELECT {keys} FROM {delta})")
            });
            Some(match (range, keyed) {
                (Some(range), Some(keyed)) => format!("({range}) OR {keyed}"),
                (range, keyed) => range.or(keyed).unwrap_or_else(|| "0".to_owned()),
            })
        }
        WriteMode::Changes(_) => {
            return vec![
                "SELECT throwIf(1, 'captured changes are not applied to ClickHouse tables')"
                    .to_owned(),
            ];
        }
    };
    let publish = match request.mode {
        WriteMode::Replace | WriteMode::Recreate => PublishStep::Exchange {
            target: target.clone(),
            build: delta.clone(),
            aside: adapter.render_qualified_name(&with_suffix(&delta_table, ASIDE_SUFFIX)),
        },
        _ => {
            let copy = with_suffix(&delta_table, COPY_SUFFIX);
            PublishStep::Partitions {
                target: target.clone(),
                delta: delta.clone(),
                copy: adapter.render_qualified_name(&copy),
                aside: adapter.render_qualified_name(&with_suffix(&copy, ASIDE_SUFFIX)),
                displaced,
            }
        }
    };
    vec![
        format!("DROP TABLE IF EXISTS {delta}"),
        format!("CREATE TABLE {delta} AS {target}"),
        publish_statement(&PublishStep::SortedInsert {
            database: delta_table.schema.clone(),
            table: delta_table.table.clone(),
            columns: request
                .columns
                .iter()
                .map(|column| column.name.clone())
                .collect(),
            insert: format!(
                "INSERT INTO {delta} ({}) SELECT {} FROM {from}",
                names.join(", "),
                values.join(", ")
            ),
        }),
        publish_statement(&publish),
        format!("DROP TABLE IF EXISTS {delta}"),
    ]
}

/// The key columns of `mode` (a merge's `unique_key`, a `delete_insert`'s `replace_key`), as the `IN` choosing the rows a delta replaces lists them: a column whose values may be NULL as whether it is NULL and its value, a NULL replaced by its type's default (`assumeNotNull` gives only the type there, as its value for a NULL is undefined), so a NULL matches a NULL (the `IN` also runs in a lightweight `DELETE`, a mutation, which ignores a session's `transform_null_in`), and a default value (`''`, `0`) does not.
fn displaced_keys(adapter: &ClickHouseSql, mode: &WriteMode) -> String {
    let keys: Vec<String> = mode
        .matched_keys()
        .into_iter()
        .map(|(key, nullable)| {
            let name = adapter.render_identifier(key);
            match nullable {
                true => format!(
                    "isNull({name}), ifNull({name}, defaultValueOfArgumentType(assumeNotNull({name})))"
                ),
                false => name,
            }
        })
        .collect();
    keys.join(", ")
}

/// `table`'s rows matching `condition` removed the way a merge publishes: an empty delta created `AS` the table (named after `scratch`) whose partition publish displaces those rows, so a run killed at any point leaves the table whole and a rerun converges.
pub(crate) fn clear_statements(
    adapter: &ClickHouseSql,
    table: &TableName,
    condition: &str,
    scratch: &TableName,
) -> Vec<String> {
    let delta_table = with_suffix(scratch, SWAP_SUFFIX);
    let delta = adapter.render_qualified_name(&delta_table);
    let copy = with_suffix(&delta_table, COPY_SUFFIX);
    vec![
        format!("DROP TABLE IF EXISTS {delta}"),
        format!(
            "CREATE TABLE {delta} AS {}",
            adapter.render_qualified_name(table)
        ),
        publish_statement(&PublishStep::Partitions {
            target: adapter.render_qualified_name(table),
            delta: delta.clone(),
            copy: adapter.render_qualified_name(&copy),
            aside: adapter.render_qualified_name(&with_suffix(&copy, ASIDE_SUFFIX)),
            displaced: Some(condition.to_owned()),
        }),
        format!("DROP TABLE IF EXISTS {delta}"),
    ]
}

/// `text` as a value of `column_type` without `Nullable`, so text ClickHouse cannot read fails: a timestamp parsed with its offset (in UTC when it has none), anything else cast.
pub(crate) fn typed_literal(text: &str, column_type: &ColumnType) -> String {
    let literal = ClickHouseSql.render_loader_value_literal(text);
    match column_type {
        ColumnType::TimestampTz => format!("parseDateTime64BestEffort({literal}, 6, 'UTC')"),
        ColumnType::TimestampNtz => format!("parseDateTime64BestEffort({literal}, 6)"),
        _ => {
            let sql_type = ClickHouseSql.render_loader_logical_type(column_type).sql;
            format!("CAST({literal} AS {})", unwrapped(&sql_type))
        }
    }
}

/// The rows a `delete_insert` replaces by its cursor: those whose cursor is one of its values, or within its range (`1`, every row, when it has no bounds; `0`, none, without a cursor). The bounds are [typed literals](typed_literal), so a bound ClickHouse cannot read fails rather than matching nothing.
pub(crate) fn range_condition(
    adapter: &ClickHouseSql,
    request: &WriteStatementsRequest<'_>,
) -> String {
    let WriteMode::DeleteInsert {
        cursor_column: Some(cursor_column),
        from,
        to,
        values,
        ..
    } = request.mode
    else {
        return "0".to_owned();
    };
    let cursor_type = request
        .columns
        .iter()
        .find(|column| column.name == *cursor_column)
        .map_or(ColumnType::Text, |column| column.column_type.clone());
    let cast = |text: &str| typed_literal(text, &cursor_type);
    let column = adapter.render_identifier(cursor_column);
    if !values.is_empty() {
        let listed: Vec<String> = values.iter().map(|value| cast(value)).collect();
        return format!("{column} IN ({})", listed.join(", "));
    }
    let bounds: Vec<String> = [(from, ">="), (to, "<")]
        .into_iter()
        .filter_map(|(bound, operator)| {
            let bound = bound.as_ref()?;
            Some(format!("{column} {operator} {}", cast(bound)))
        })
        .collect();
    match bounds.is_empty() {
        true => "1".to_owned(),
        false => bounds.join(" AND "),
    }
}