hyprstream_metrics/storage/
duckdb.rs1#![allow(clippy::result_large_err)]
3
4use crate::error::DuckDbErrorWrapper;
5use crate::storage::cache::{CacheEviction, CacheManager};
6use crate::storage::view::{ViewDefinition, ViewMetadata};
7use crate::storage::{Credentials, StorageBackend};
8use async_trait::async_trait;
9use duckdb::arrow::array::{
10 Array, ArrayRef, BinaryArray, Float64Array, Int64Array, RecordBatch, StringArray,
11};
12use duckdb::arrow::datatypes::{DataType, Field, Schema};
13use duckdb::{params, Config, Connection};
14use std::collections::HashMap;
15use std::sync::Arc;
16use std::time::SystemTime;
17use tokio::sync::Mutex;
18use tonic::Status;
19
20#[derive(Clone)]
22pub struct DuckDbBackend {
23 conn: Arc<Mutex<Connection>>,
24 #[allow(dead_code)]
25 connection_string: String,
26 #[allow(dead_code)]
27 options: HashMap<String, String>,
28 #[allow(dead_code)]
29 cache_manager: CacheManager,
30}
31
32impl DuckDbBackend {
33 pub fn new(
34 connection_string: String,
35 options: HashMap<String, String>,
36 ttl: Option<u64>,
37 ) -> Result<Self, Status> {
38 let config = Config::default();
39 let conn = Connection::open_with_flags(&connection_string, config)
40 .map_err(|e| Status::internal(e.to_string()))?;
41
42 conn.execute_batch(
46 r#"
47 CREATE TABLE IF NOT EXISTS view_metadata (
48 view_name VARCHAR PRIMARY KEY,
49 source_table VARCHAR NOT NULL,
50 view_definition VARCHAR NOT NULL,
51 created_at BIGINT NOT NULL
52 );
53 "#,
54 )
55 .map_err(|e| Status::internal(format!("Failed to create tables: {e}")))?;
56
57 Ok(Self {
58 conn: Arc::new(Mutex::new(conn)),
59 connection_string,
60 options,
61 cache_manager: CacheManager::new(ttl),
62 })
63 }
64
65 pub fn new_in_memory() -> Result<Self, Status> {
66 Self::new(":memory:".to_owned(), HashMap::new(), Some(0))
67 }
68
69 async fn execute_statement(&self, sql: &str) -> Result<(), Status> {
70 let conn = self.conn.lock().await;
71 conn.execute_batch(sql)
72 .map_err(|e| Status::internal(format!("Failed to execute statement: {e}")))
73 }
74}
75
76#[async_trait]
77impl StorageBackend for DuckDbBackend {
78 async fn init(&self) -> Result<(), Status> {
79 Ok(())
81 }
82
83 async fn prepare_sql(&self, query: &str) -> Result<Vec<u8>, Status> {
84 Ok(query.as_bytes().to_vec())
85 }
86
87 async fn query_sql(&self, statement_handle: &[u8]) -> Result<RecordBatch, Status> {
88 let sql = std::str::from_utf8(statement_handle)
89 .map_err(|e| Status::internal(format!("Invalid SQL statement: {e}")))?;
90
91 let conn = self.conn.lock().await;
92 let mut stmt = conn
93 .prepare(sql)
94 .map_err(|e| Status::internal(format!("Failed to prepare statement: {e}")))?;
95
96 let batches: Vec<RecordBatch> = stmt
100 .query_arrow(params![])
101 .map_err(|e| Status::internal(format!("Failed to execute query: {e}")))?
102 .collect();
103
104 if let Some(first) = batches.first() {
105 let schema = first.schema();
106 duckdb::arrow::compute::concat_batches(&schema, batches.iter())
107 .map_err(|e| Status::internal(format!("Failed to concatenate result chunks: {e}")))
108 } else {
109 let schema = Arc::new(Schema::new(
111 stmt.column_names()
112 .iter()
113 .map(|col| {
114 Field::new(
115 col,
116 match col.to_uppercase().as_str() {
117 "INTEGER" | "BIGINT" => DataType::Int64,
118 "DOUBLE" | "REAL" | "FLOAT" => DataType::Float64,
119 _ => DataType::Utf8,
121 },
122 true,
123 )
124 })
125 .collect::<Vec<Field>>(),
126 ));
127 let empty_arrays: Vec<ArrayRef> = schema
128 .fields()
129 .iter()
130 .map(|field| match field.data_type() {
131 DataType::Int64 => Arc::new(Int64Array::from(Vec::<i64>::new())) as ArrayRef,
132 DataType::Float64 => {
133 Arc::new(Float64Array::from(Vec::<f64>::new())) as ArrayRef
134 }
135 _ => Arc::new(StringArray::from(Vec::<String>::new())) as ArrayRef,
136 })
137 .collect();
138 RecordBatch::try_new(schema, empty_arrays)
139 .map_err(|e| Status::internal(format!("Failed to create empty batch: {e}")))
140 }
141 }
142
143 fn new_with_options(
144 connection_string: &str,
145 options: &HashMap<String, String>,
146 credentials: Option<&Credentials>,
147 ) -> Result<Self, Status> {
148 let mut all_options = options.clone();
149 if let Some(creds) = credentials {
150 if let Some(ref username) = creds.username {
151 all_options.insert("username".to_owned(), username.clone());
152 }
153 if let Some(ref password) = creds.password {
154 all_options.insert("password".to_owned(), password.clone());
155 }
156 }
157
158 let ttl = all_options
159 .get("ttl")
160 .and_then(|s| s.parse().ok())
161 .map(|ttl| if ttl == 0 { None } else { Some(ttl) })
162 .unwrap_or(None);
163
164 Self::new(connection_string.to_owned(), all_options, ttl)
165 }
166
167 async fn create_table(&self, table_name: &str, schema: &Schema) -> Result<(), Status> {
168 let sql = crate::storage::StorageUtils::generate_create_table_sql(table_name, schema)?;
169 self.execute_statement(&sql).await
170 }
171
172 async fn execute_ddl(&self, sql: &str) -> Result<(), Status> {
173 self.execute_statement(sql).await
174 }
175
176 async fn insert_into_table(&self, table_name: &str, batch: RecordBatch) -> Result<(), Status> {
177 let mut conn = self.conn.lock().await;
178 let tx = conn
179 .transaction()
180 .map_err(|e| Status::internal(format!("Failed to start transaction: {e}")))?;
181
182 let sql =
183 crate::storage::StorageUtils::generate_insert_sql(table_name, batch.num_columns());
184
185 let mut stmt = tx
186 .prepare(&sql)
187 .map_err(|e| Status::internal(format!("Failed to prepare statement: {e}")))?;
188
189 for row_idx in 0..batch.num_rows() {
190 let mut params: Vec<Box<dyn duckdb::ToSql>> = Vec::new();
191 for col_idx in 0..batch.num_columns() {
192 let col = batch.column(col_idx);
193 match col.data_type() {
194 DataType::Int64 => {
195 let array = col
196 .as_any()
197 .downcast_ref::<Int64Array>()
198 .ok_or_else(|| Status::internal("Failed to downcast to Int64Array"))?;
199 if array.is_null(row_idx) {
200 params.push(Box::new(Option::<i64>::None));
201 } else {
202 params.push(Box::new(array.value(row_idx)));
203 }
204 }
205 DataType::Float64 => {
206 let array =
207 col.as_any().downcast_ref::<Float64Array>().ok_or_else(|| {
208 Status::internal("Failed to downcast to Float64Array")
209 })?;
210 if array.is_null(row_idx) {
211 params.push(Box::new(Option::<f64>::None));
212 } else {
213 params.push(Box::new(array.value(row_idx)));
214 }
215 }
216 DataType::Utf8 => {
217 let array = col
218 .as_any()
219 .downcast_ref::<StringArray>()
220 .ok_or_else(|| Status::internal("Failed to downcast to StringArray"))?;
221 if array.is_null(row_idx) {
222 params.push(Box::new(Option::<String>::None));
223 } else {
224 params.push(Box::new(array.value(row_idx).to_owned()));
225 }
226 }
227 DataType::Binary => {
228 let array = col
229 .as_any()
230 .downcast_ref::<BinaryArray>()
231 .ok_or_else(|| Status::internal("Failed to downcast to BinaryArray"))?;
232 if array.is_null(row_idx) {
233 params.push(Box::new(Option::<Vec<u8>>::None));
234 } else {
235 params.push(Box::new(array.value(row_idx).to_vec()));
236 }
237 }
238 _ => return Err(Status::invalid_argument("Unsupported data type")),
239 }
240 }
241
242 let param_refs: Vec<&dyn duckdb::ToSql> =
243 params.iter().map(std::convert::AsRef::as_ref).collect();
244 stmt.execute(param_refs.as_slice())
245 .map_err(|e| Status::internal(format!("Failed to insert row: {e}")))?;
246 }
247
248 tx.commit()
249 .map_err(|e| Status::internal(format!("Failed to commit transaction: {e}")))?;
250
251 Ok(())
252 }
253
254 async fn create_view(&self, name: &str, definition: ViewDefinition) -> Result<(), Status> {
255 let mut conn = self.conn.lock().await;
256 let tx = conn
257 .transaction()
258 .map_err(|e| Status::internal(format!("Failed to start transaction: {e}")))?;
259
260 let create_view_sql = crate::storage::StorageUtils::generate_view_sql(name, &definition);
262 tx.execute(&create_view_sql, params![])
263 .map_err(|e| Status::internal(format!("Failed to create view: {e}")))?;
264
265 let metadata = ViewMetadata {
267 name: name.to_owned(),
268 definition: definition.clone(),
269 created_at: SystemTime::now(),
270 };
271
272 let definition_json = serde_json::to_string(&definition)
273 .map_err(|e| Status::internal(format!("Failed to serialize view definition: {e}")))?;
274
275 let created_at_secs = i64::try_from(
276 metadata
277 .created_at
278 .duration_since(SystemTime::UNIX_EPOCH)
279 .map_err(|e| Status::internal(format!("Failed to get timestamp: {e}")))?
280 .as_secs(),
281 )
282 .unwrap_or(i64::MAX);
283
284 tx.execute(
285 "INSERT INTO view_metadata (view_name, source_table, view_definition, created_at) VALUES (?, ?, ?, ?)",
286 params![
287 name,
288 definition.source_table,
289 definition_json,
290 created_at_secs
291 ],
292 )
293 .map_err(|e| Status::internal(format!("Failed to store view metadata: {e}")))?;
294
295 tx.commit()
296 .map_err(|e| Status::internal(format!("Failed to commit transaction: {e}")))?;
297
298 Ok(())
299 }
300
301 async fn get_view(&self, name: &str) -> Result<ViewMetadata, Status> {
302 let conn = self.conn.lock().await;
303 let mut stmt = conn
304 .prepare("SELECT * FROM view_metadata WHERE view_name = ?")
305 .map_err(|e| Status::internal(format!("Failed to prepare statement: {e}")))?;
306
307 let mut rows = stmt
308 .query_map(params![name], |row| {
309 let definition_json: String = row.get(2)?;
310 let definition: ViewDefinition =
311 serde_json::from_str(&definition_json).map_err(|e| {
312 duckdb::Error::InvalidParameterName(format!("Invalid view definition: {e}"))
313 })?;
314
315 let secs = row.get::<_, i64>(3)?;
316 let created_at = SystemTime::UNIX_EPOCH
317 + std::time::Duration::from_secs(u64::try_from(secs).unwrap_or(0));
318
319 Ok(ViewMetadata {
320 name: row.get(0)?,
321 definition,
322 created_at,
323 })
324 })
325 .map_err(|e| Status::internal(format!("Failed to execute query: {e}")))?;
326
327 if let Some(row) = rows.next() {
328 row.map_err(|e| Status::internal(format!("Failed to read row: {e}")))
329 } else {
330 Err(Status::not_found(format!("View {name} not found")))
331 }
332 }
333
334 async fn list_views(&self) -> Result<Vec<String>, Status> {
335 let conn = self.conn.lock().await;
336 let mut stmt = conn
337 .prepare("SELECT view_name FROM view_metadata")
338 .map_err(|e| Status::internal(format!("Failed to prepare statement: {e}")))?;
339
340 let rows = stmt
341 .query_map(params![], |row| row.get::<_, String>(0))
342 .map_err(|e| Status::internal(format!("Failed to execute query: {e}")))?;
343
344 let mut views = Vec::new();
345 for row in rows {
346 views.push(row.map_err(|e| Status::internal(format!("Failed to read row: {e}")))?);
347 }
348
349 Ok(views)
350 }
351
352 async fn drop_view(&self, name: &str) -> Result<(), Status> {
353 let mut conn = self.conn.lock().await;
354 let tx = conn
355 .transaction()
356 .map_err(|e| Status::internal(format!("Failed to start transaction: {e}")))?;
357
358 tx.execute(&format!("DROP VIEW IF EXISTS {name}"), params![])
360 .map_err(|e| Status::internal(format!("Failed to drop view: {e}")))?;
361
362 tx.execute(
364 "DELETE FROM view_metadata WHERE view_name = ?",
365 params![name],
366 )
367 .map_err(|e| Status::internal(format!("Failed to remove view metadata: {e}")))?;
368
369 tx.commit()
370 .map_err(|e| Status::internal(format!("Failed to commit transaction: {e}")))?;
371
372 Ok(())
373 }
374
375 async fn drop_table(&self, table_name: &str) -> Result<(), Status> {
376 let sql = format!("DROP TABLE IF EXISTS {table_name}");
377 self.execute_statement(&sql).await
378 }
379
380 async fn list_tables(&self) -> Result<Vec<String>, Status> {
381 let conn = self.conn.lock().await;
382 let mut stmt = conn
383 .prepare("SELECT name FROM sqlite_master WHERE type='table'")
384 .map_err(|e| Status::internal(format!("Failed to prepare statement: {e}")))?;
385
386 let rows = stmt
387 .query_map(params![], |row| row.get::<_, String>(0))
388 .map_err(|e| Status::internal(format!("Failed to execute query: {e}")))?;
389
390 let mut tables = Vec::new();
391 for row in rows {
392 tables.push(row.map_err(|e| Status::internal(format!("Failed to read row: {e}")))?);
393 }
394
395 Ok(tables)
396 }
397
398 async fn get_table_schema(&self, table_name: &str) -> Result<Arc<Schema>, Status> {
399 let conn = self.conn.lock().await;
400 let mut stmt = conn
401 .prepare(&format!("PRAGMA table_info({table_name})"))
402 .map_err(|e| Into::<Status>::into(DuckDbErrorWrapper(e)))?;
403
404 let mut fields = Vec::new();
405 let rows = stmt
406 .query_map(params![], |row| {
407 let name: String = row.get(1)?;
408 let type_str: String = row.get(2)?;
409 let nullable: bool = row.get(3)?;
410
411 let data_type = match type_str.to_uppercase().as_str() {
412 "INTEGER" | "BIGINT" => DataType::Int64,
413 "DOUBLE" | "REAL" => DataType::Float64,
414 _ => DataType::Utf8,
415 };
416
417 Ok((name, data_type, !nullable))
418 })
419 .map_err(|e| Into::<Status>::into(DuckDbErrorWrapper(e)))?;
420
421 for row in rows {
422 let (name, data_type, required) =
423 row.map_err(|e| Into::<Status>::into(DuckDbErrorWrapper(e)))?;
424 fields.push(Field::new(name, data_type, required));
425 }
426
427 Ok(Arc::new(Schema::new(fields)))
428 }
429
430 async fn export_to_parquet(
431 &self,
432 table_name: &str,
433 path: &std::path::Path,
434 ) -> Result<(), Status> {
435 if let Some(parent) = path.parent() {
437 std::fs::create_dir_all(parent)
438 .map_err(|e| Status::internal(format!("Failed to create export directory: {e}")))?;
439 }
440
441 let sql = format!(
442 "COPY {} TO '{}' (FORMAT PARQUET)",
443 table_name,
444 path.display()
445 );
446 self.execute_statement(&sql).await
447 }
448
449 async fn import_from_parquet(
450 &self,
451 table_name: &str,
452 path: &std::path::Path,
453 ) -> Result<(), Status> {
454 if !path.exists() {
455 return Err(Status::not_found(format!(
456 "Parquet file not found: {}",
457 path.display()
458 )));
459 }
460
461 let sql = format!(
462 "COPY {} FROM '{}' (FORMAT PARQUET)",
463 table_name,
464 path.display()
465 );
466 self.execute_statement(&sql).await
467 }
468}
469
470#[async_trait]
471impl CacheEviction for DuckDbBackend {
472 async fn execute_eviction(&self, query: &str) -> Result<(), Status> {
473 self.execute_statement(query).await
474 }
475}
476
477#[cfg(test)]
478mod tests {
479 use super::*;
480
481 #[tokio::test]
485 async fn test_query_sql_returns_all_rows_beyond_one_chunk() -> Result<(), Status> {
486 const ROWS: i64 = 5_000;
487 let backend = DuckDbBackend::new_in_memory()?;
488 let schema = Arc::new(Schema::new(vec![
489 Field::new("id", DataType::Int64, false),
490 Field::new("name", DataType::Utf8, true),
491 ]));
492 backend.create_table("big", &schema).await?;
493 let batch = RecordBatch::try_new(
494 schema,
495 vec![
496 Arc::new(Int64Array::from_iter_values(0..ROWS)) as ArrayRef,
497 Arc::new(StringArray::from_iter(
498 (0..ROWS).map(|i| (i % 7 == 0).then(|| format!("n{i}"))),
499 )) as ArrayRef,
500 ],
501 )
502 .map_err(|e| Status::internal(e.to_string()))?;
503 backend.insert_into_table("big", batch).await?;
504
505 let handle = backend.prepare_sql("SELECT * FROM big").await?;
506 let result = backend.query_sql(&handle).await?;
507 assert_eq!(
508 result.num_rows() as i64,
509 ROWS,
510 "plain SELECT larger than one chunk must return every row"
511 );
512
513 let handle = backend
515 .prepare_sql("SELECT id, COUNT(*) AS n FROM big GROUP BY id")
516 .await?;
517 let result = backend.query_sql(&handle).await?;
518 assert_eq!(
519 result.num_rows() as i64,
520 ROWS,
521 "GROUP BY must return every group"
522 );
523 Ok(())
524 }
525}