sqlload 0.69.1

SQL data loading tooling.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
use crate::auth::models::{Authenticator, Secret};
use crate::batching::models::{Batch, Partition};
use crate::bigquery_api::main::run_query::run_query;
use crate::bigquery_api::models::BigQueryClient;
use crate::config::models::{ColumnConfig, Config, LoadErrorPolicy, OversizedPolicy};
use crate::databricks_api::main::execute_statement::execute_statement;
use crate::databricks_api::models::DatabricksClient;
use crate::delivery::_helpers::stage_paths::s3_target;
use crate::delivery::constants::{STAGE_CONFIRM_POLL, STREAMING_CONFIRM_POLL};
use crate::delivery::errors::DeliveryError;
use crate::http::errors::ApiError;
use crate::http::models::InFlight;
use crate::naming::models::Stream;
use crate::planning::models::LoadPath;
use crate::sql_api::main::execute_sql::execute_sql;
use crate::sql_api::models::{SqlClient, SqlRows};
use crate::stage_api::models::{DriverSession, ObjectTarget, StageInfo};
use crate::streaming_api::models::StreamingClient;
use std::collections::{BTreeMap, VecDeque};
use std::sync::Arc;

/// What one delivery sent.
#[derive(Debug, Clone, Default, PartialEq, Eq)]
pub struct DeliveryReport {
    pub rows: u64,
    pub uncompressed_bytes: u64,
    pub sent_bytes: u64,
    pub requests: u64,
    pub skipped_rows: u64,
    pub confirmed: BTreeMap<Partition, i64>,
}

/// Offsets Snowflake has confirmed since the last poll, newly rejected rows, and files skipped after a failed load.
#[derive(Debug, Clone, Default, PartialEq, Eq)]
pub struct Confirmation {
    pub confirmed: BTreeMap<Partition, i64>,
    pub error_rows: u64,
    pub skipped_files: u64,
    /// A retryable report or check failure from a poll that still confirmed offsets.
    pub deferred_error: Option<DeliveryError>,
}

/// The shared clients a stage delivery uses.
#[derive(Debug, Clone)]
pub struct StageClients {
    pub http: reqwest::Client,
    pub auth: Arc<Authenticator>,
    pub sql: SqlClient,
    pub driver: Arc<DriverSession>,
    pub account_url: String,
    pub in_flight: InFlight,
}

/// The destination's clients.
#[derive(Debug, Clone)]
pub enum Warehouse {
    Snowflake(SnowflakeClients),
    Databricks(DatabricksClients),
    BigQuery(BigQueryClient),
}

/// The Snowflake clients, on one authenticator.
#[derive(Debug, Clone)]
pub struct SnowflakeClients {
    pub auth: Arc<Authenticator>,
    pub sql: SqlClient,
    pub streaming: Arc<StreamingClient>,
    pub driver: Arc<DriverSession>,
    pub account_url: String,
}

/// The Databricks statement and Files API client, and the settings Zerobus Ingest streams are opened with.
#[derive(Debug, Clone)]
pub struct DatabricksClients {
    pub api: DatabricksClient,
    pub zerobus: Option<ZerobusSettings>,
}

/// Where and as whom Zerobus Ingest streams are opened.
#[derive(Debug, Clone)]
pub struct ZerobusSettings {
    pub endpoint: String,
    pub workspace_url: String,
    pub client_id: String,
    pub client_secret: Secret,
}

impl Warehouse {
    /// Run one SQL statement on the destination.
    pub async fn execute(&self, statement: &str) -> Result<SqlRows, ApiError> {
        match self {
            Self::Snowflake(snowflake) => execute_sql(&snowflake.sql, statement).await,
            Self::Databricks(databricks) => execute_statement(&databricks.api, statement).await,
            Self::BigQuery(bigquery) => run_query(bigquery, statement).await,
        }
    }

    /// True when Snowflake signs in with a key pair, which Snowpipe's REST API needs.
    pub fn uses_key_pair(&self) -> bool {
        match self {
            Self::Snowflake(snowflake) => snowflake.auth.uses_key_pair(),
            Self::Databricks(_) | Self::BigQuery(_) => false,
        }
    }
}

/// Channel state for one stream; the committed offset token in Snowflake is the source of truth.
#[derive(Debug)]
pub struct StreamingDelivery {
    pub(crate) client: Arc<StreamingClient>,
    pub(crate) stream: Stream,
    pub(crate) max_request_bytes: u64,
    pub(crate) oversized: OversizedPolicy,
    pub(crate) continuations: BTreeMap<Partition, String>,
    pub(crate) pending: BTreeMap<Partition, i64>,
    pub(crate) error_rows: BTreeMap<String, i64>,
}

/// File uploads and loads for one stream.
#[derive(Debug)]
pub struct StageDelivery {
    pub(crate) clients: StageClients,
    pub(crate) config: Arc<Config>,
    pub(crate) stream: Stream,
    pub(crate) path: LoadPath,
    pub(crate) stage_info: Option<StageInfo>,
    pub(crate) s3: Option<ObjectTarget>,
    /// A random id for this delivery, in every staged file's name: the warehouse skips a file whose name it loaded before, so names from offsets alone could match an earlier run's file and commit offsets nothing was loaded for.
    pub(crate) run: String,
    pub(crate) staged: Option<StagedBatch>,
    pub(crate) pending: PendingLoads,
    pub(crate) begin_mark: Option<String>,
    /// Set while the stream stops: auto-ingest checks COPY_HISTORY on every poll, so the files of the last minutes are committed before it exits.
    pub(crate) final_checks: bool,
}

/// Batches handed to Snowpipe whose loads are not all reported yet, oldest first.
#[derive(Debug, Clone, Default, PartialEq, Eq)]
pub struct PendingLoads {
    pub batches: VecDeque<PendingBatch>,
    pub policy: LoadErrorPolicy,
    pub timing: CheckTiming,
}

/// When unreported files are checked: first after `check_after`, then every `recheck_every`.
#[derive(Debug, Clone, Copy, Default, PartialEq, Eq)]
pub struct CheckTiming {
    pub check_after: std::time::Duration,
    pub recheck_every: std::time::Duration,
}

/// One delivered batch: its files and the offsets to commit once every file is loaded.
#[derive(Debug, Clone, PartialEq, Eq)]
pub struct PendingBatch {
    pub files: Vec<PendingFile>,
    pub offsets: BTreeMap<Partition, i64>,
}

/// One file sent to `insertFiles`, by its path relative to the pipe's location.
#[derive(Debug, Clone, PartialEq, Eq)]
pub struct PendingFile {
    pub key: String,
    /// When the file was first sent, for the oldest-pending age and the history lookback.
    pub first_sent_ms: i64,
    pub resent: bool,
    /// The pipe said it had received the file before, so its load may be older than this run.
    pub received_before: bool,
    /// When the file's load history and stage object are next checked, if it is still unreported.
    pub next_check_ms: i64,
    pub last_check_ms: Option<i64>,
    /// Checks in a row that could not run, for their backoff.
    pub failed_checks: u32,
    /// A received-before file with no load history was already warned about.
    pub warned_no_history: bool,
    pub state: FileState,
}

/// Where one pending file's load stands.
#[derive(Debug, Clone, PartialEq, Eq)]
pub enum FileState {
    /// Not reported yet.
    Waiting,
    /// Reported as loading.
    InProgress,
    Loaded,
    /// Failed, with the reason; held until the policy is applied.
    Failed(String),
    /// Failed and skipped by `on_load_error = "skip"`.
    Skipped,
}

/// Pending loads settled by a poll.
#[derive(Debug, Clone, Default, PartialEq, Eq)]
pub struct Settled {
    pub confirmed: BTreeMap<Partition, i64>,
    /// Files newly skipped under `on_load_error = "skip"`, with why they failed.
    pub skipped: Vec<(String, String)>,
    /// The first failure under `on_load_error = "halt"`.
    pub halted: Option<String>,
}

/// A batch's files, fixed on the first attempt so retries reuse the same paths and skip finished uploads.
#[derive(Debug, Clone, PartialEq, Eq)]
pub struct StagedBatch {
    pub digest: String,
    pub files: Vec<StagedFile>,
}

/// One file of a batch, relative to the internal stage or to the bucket prefix.
#[derive(Debug, Clone, PartialEq, Eq)]
pub struct StagedFile {
    pub key: String,
    pub uploaded: bool,
}

/// Rows streamed to one Databricks table through Zerobus Ingest; a batch counts as delivered once the stream's flush returns.
#[derive(Debug)]
pub struct ZerobusDelivery {
    pub(crate) settings: ZerobusSettings,
    /// `catalog.schema.table`.
    pub(crate) table: String,
    pub(crate) columns: Vec<ColumnConfig>,
    pub(crate) max_request_bytes: u64,
    pub(crate) oversized: OversizedPolicy,
    pub(crate) link: ZerobusLink,
}

/// The open Zerobus Ingest SDK and stream, if any; dropped after a failure so the next batch opens a new stream.
#[derive(Default)]
pub struct ZerobusLink {
    pub(crate) sdk: Option<databricks_zerobus_ingest_sdk::ZerobusSdk>,
    pub(crate) stream: Option<databricks_zerobus_ingest_sdk::ZerobusStream>,
}

impl std::fmt::Debug for ZerobusLink {
    fn fmt(&self, formatter: &mut std::fmt::Formatter<'_>) -> std::fmt::Result {
        formatter
            .debug_struct("ZerobusLink")
            .field("open", &self.stream.is_some())
            .finish()
    }
}

/// Parquet files uploaded to a Unity Catalog volume or an S3 bucket and loaded into one Databricks table with `COPY INTO`, which skips files it loaded before.
#[derive(Debug)]
pub struct CopyDelivery {
    pub(crate) api: DatabricksClient,
    pub(crate) http: reqwest::Client,
    pub(crate) in_flight: InFlight,
    pub(crate) config: Arc<Config>,
    pub(crate) stream: Stream,
    /// The bucket, for `staging.kind = "s3"`; otherwise files go to the stream's volume.
    pub(crate) s3: Option<ObjectTarget>,
    /// A random id for this delivery, in every staged file's name: the warehouse skips a file whose name it loaded before, so names from offsets alone could match an earlier run's file and commit offsets nothing was loaded for.
    pub(crate) run: String,
    pub(crate) staged: Option<StagedBatch>,
}

/// Rows written to one BigQuery table: appended to its default stream, or loaded by load jobs, at most one a minute.
#[derive(Debug)]
pub struct BigQueryDelivery {
    pub(crate) api: BigQueryClient,
    pub(crate) config: Arc<Config>,
    pub(crate) stream: Stream,
    pub(crate) path: LoadPath,
    /// A random id for this delivery, in every load job's name: job names are unique across the project for months, so offsets alone would match another table's or another run's job.
    pub(crate) run: String,
    /// The load of a batch whose job failed or never answered, kept so its retry reuses the job's name and the uploaded files.
    pub(crate) staged: Option<StagedLoad>,
    /// When the table's last load job was started.
    pub(crate) last_load: Option<tokio::time::Instant>,
}

/// A batch's load job: its rows' digest, which attempt it is, and the files in the bucket it reads.
#[derive(Debug, Clone, PartialEq, Eq)]
pub struct StagedLoad {
    pub digest: String,
    pub attempt: u32,
    pub uris: Vec<String>,
}

/// The configured delivery mode for one stream.
#[derive(Debug)]
pub enum Delivery {
    Streaming(Box<StreamingDelivery>),
    Stage(Box<StageDelivery>),
    Zerobus(Box<ZerobusDelivery>),
    Copy(Box<CopyDelivery>),
    BigQuery(Box<BigQueryDelivery>),
}

impl Delivery {
    /// Snowpipe Streaming delivery for one stream.
    pub fn streaming(
        client: Arc<StreamingClient>,
        stream: Stream,
        max_request_bytes: u64,
        oversized: OversizedPolicy,
    ) -> Self {
        Self::Streaming(Box::new(StreamingDelivery::new(
            client,
            stream,
            max_request_bytes,
            oversized,
        )))
    }

    /// File delivery for one stream on `path`.
    pub fn stage(
        clients: StageClients,
        config: Arc<Config>,
        stream: Stream,
        path: LoadPath,
    ) -> Result<Self, DeliveryError> {
        StageDelivery::new(clients, config, stream, path).map(|stage| Self::Stage(Box::new(stage)))
    }

    /// Zerobus Ingest delivery for one Databricks table.
    pub fn zerobus(
        settings: ZerobusSettings,
        config: &Config,
        stream: &Stream,
        oversized: OversizedPolicy,
    ) -> Self {
        Self::Zerobus(Box::new(ZerobusDelivery {
            settings,
            table: format!(
                "{}.{}.{}",
                config.destination.database(),
                config.destination.schema(),
                stream.table
            ),
            columns: config.envelope.columns.clone(),
            max_request_bytes: config.batching.max_request_bytes.0,
            oversized,
            link: ZerobusLink::default(),
        }))
    }

    /// `COPY INTO` delivery for one Databricks table, from its volume or the `s3` bucket.
    pub fn copy(
        inputs: &DeliveryInputs,
        api: &DatabricksClient,
        stream: &Stream,
    ) -> Result<Self, DeliveryError> {
        let s3 = match inputs.path {
            LoadPath::S3Copy => Some(s3_target(&inputs.config).map_err(DeliveryError::Fatal)?),
            _ => None,
        };
        Ok(Self::Copy(Box::new(CopyDelivery {
            api: api.clone(),
            http: inputs.http.clone(),
            in_flight: inputs.in_flight.clone(),
            config: inputs.config.clone(),
            stream: stream.clone(),
            s3,
            run: uuid::Uuid::new_v4().simple().to_string(),
            staged: None,
        })))
    }

    /// BigQuery delivery for one table on `path`.
    pub fn bigquery(inputs: &DeliveryInputs, api: &BigQueryClient, stream: &Stream) -> Self {
        Self::BigQuery(Box::new(BigQueryDelivery {
            api: api.clone(),
            config: inputs.config.clone(),
            stream: stream.clone(),
            path: inputs.path,
            run: uuid::Uuid::new_v4().simple().to_string(),
            staged: None,
            last_load: None,
        }))
    }

    /// True when Snowflake, not Kafka, holds the resume position.
    pub fn resumes_from_warehouse(&self) -> bool {
        matches!(self, Self::Streaming(_))
    }

    /// Prepare a partition; returns the warehouse's committed offset when the mode tracks one.
    pub async fn open(&mut self, partition: &Partition) -> Result<Option<i64>, DeliveryError> {
        match self {
            Self::Streaming(streaming) => streaming.open(partition).await,
            Self::Stage(_) | Self::Zerobus(_) | Self::Copy(_) | Self::BigQuery(_) => Ok(None),
        }
    }

    pub fn is_open(&self, partition: &Partition) -> bool {
        match self {
            Self::Streaming(streaming) => streaming.is_open(partition),
            Self::Stage(_) | Self::Zerobus(_) | Self::Copy(_) | Self::BigQuery(_) => true,
        }
    }

    pub async fn deliver(&mut self, batch: &Batch) -> Result<DeliveryReport, DeliveryError> {
        match self {
            Self::Streaming(streaming) => streaming.deliver(batch).await,
            Self::Stage(stage) => stage.deliver(batch).await,
            Self::Zerobus(zerobus) => zerobus.deliver(batch).await,
            Self::Copy(copy) => copy.deliver(batch).await,
            Self::BigQuery(bigquery) => bigquery.deliver(batch).await,
        }
    }

    pub fn has_pending(&self) -> bool {
        match self {
            Self::Streaming(streaming) => streaming.has_pending(),
            Self::Stage(stage) => !stage.pending.is_empty(),
            Self::Zerobus(_) | Self::Copy(_) | Self::BigQuery(_) => false,
        }
    }

    /// How often [`Self::poll`] runs while confirmations are pending.
    pub fn confirm_interval(&self) -> std::time::Duration {
        match self {
            Self::Streaming(_) => STREAMING_CONFIRM_POLL,
            Self::Stage(_) | Self::Zerobus(_) | Self::Copy(_) | Self::BigQuery(_) => {
                STAGE_CONFIRM_POLL
            }
        }
    }

    /// True when the stream's files sent but not committed fill `staging.max_pending_files`.
    pub fn is_full(&self) -> bool {
        match self {
            Self::Streaming(_) | Self::Zerobus(_) | Self::Copy(_) | Self::BigQuery(_) => false,
            Self::Stage(stage) => stage.is_full(),
        }
    }

    /// Files sent to Snowpipe whose offsets are not committed yet.
    pub fn pending_files(&self) -> usize {
        match self {
            Self::Streaming(_) | Self::Zerobus(_) | Self::Copy(_) | Self::BigQuery(_) => 0,
            Self::Stage(stage) => stage.pending.file_count(),
        }
    }

    /// When the oldest uncommitted file was first sent, in Unix milliseconds.
    pub fn oldest_pending_ms(&self) -> Option<i64> {
        match self {
            Self::Streaming(_) | Self::Zerobus(_) | Self::Copy(_) | Self::BigQuery(_) => None,
            Self::Stage(stage) => stage.pending.oldest_sent_ms(),
        }
    }

    pub async fn poll(&mut self) -> Result<Confirmation, DeliveryError> {
        match self {
            Self::Streaming(streaming) => streaming.poll().await,
            Self::Stage(stage) => stage.poll().await,
            Self::Zerobus(_) | Self::Copy(_) | Self::BigQuery(_) => Ok(Confirmation::default()),
        }
    }

    /// The stream is stopping: auto-ingest checks every waiting file on each poll from now on, instead of once per `pending_check_after`; other routes poll as before.
    pub fn check_until_stopped(&mut self) {
        if let Self::Stage(stage) = self {
            stage.final_checks = true;
        }
    }

    pub fn reset(&mut self) {
        match self {
            Self::Streaming(streaming) => streaming.reset(),
            Self::Stage(stage) => stage.pending.clear(),
            Self::Zerobus(zerobus) => zerobus.link = ZerobusLink::default(),
            Self::Copy(copy) => copy.staged = None,
            Self::BigQuery(bigquery) => bigquery.staged = None,
        }
    }

    pub fn forget(&mut self, partitions: &[Partition]) {
        match self {
            Self::Streaming(streaming) => streaming.forget(partitions),
            Self::Stage(stage) => stage.pending.forget(partitions),
            Self::Zerobus(_) | Self::Copy(_) | Self::BigQuery(_) => {}
        }
    }

    /// Remove expired staged files; returns how many were removed.
    pub async fn prune(&self) -> Result<u64, DeliveryError> {
        match self {
            Self::Streaming(_) | Self::Zerobus(_) | Self::BigQuery(_) => Ok(0),
            Self::Stage(stage) => stage.prune().await,
            Self::Copy(copy) => copy.prune().await,
        }
    }
}

/// The clients and settings a stream's delivery needs.
#[derive(Debug, Clone)]
pub struct DeliveryInputs {
    pub http: reqwest::Client,
    pub in_flight: InFlight,
    pub warehouse: Warehouse,
    pub config: Arc<Config>,
    pub path: LoadPath,
    pub oversized: OversizedPolicy,
}