use std::sync::Arc;
use datafusion::arrow::datatypes::DataType;
use datafusion::datasource::MemTable;
use datafusion::prelude::SessionContext;
use crate::session::geo_context;
use fv_value::Value;
use serde_json::Value as J;
use crate::convert::{batches_to_rows, infer_type, rows_to_batch};
use crate::row::Row;
pub struct Input {
pub columns: Vec<String>,
pub rows: Vec<Row>,
}
pub const NATIVE_OPS: &[&str] = &["join", "aggregate", "window", "union"];
#[async_trait::async_trait]
pub trait SqlRunner: Send + Sync {
async fn run(&self, tables: &[(&str, &Input)], sql: &str) -> Result<Vec<Row>, String>;
}
pub struct LocalSqlRunner;
#[async_trait::async_trait]
impl SqlRunner for LocalSqlRunner {
async fn run(&self, tables: &[(&str, &Input)], sql: &str) -> Result<Vec<Row>, String> {
let ctx = geo_context();
for (name, input) in tables {
register(&ctx, name, input)?;
}
run_sql(&ctx, sql).await
}
}
fn str_list(j: &J) -> Vec<String> {
j.as_array()
.map(|a| a.iter().filter_map(|v| v.as_str().map(String::from)).collect())
.unwrap_or_default()
}
pub async fn run(step: &J, inputs: Vec<Input>) -> Result<Vec<Row>, String> {
run_with(step, inputs, &LocalSqlRunner).await
}
pub async fn run_with(step: &J, inputs: Vec<Input>, runner: &dyn SqlRunner) -> Result<Vec<Row>, String> {
match step["op"].as_str().unwrap_or("") {
"aggregate" => {
let group_by = str_list(&step["groupBy"]);
let aggs: Vec<Aggregation> = step["aggregations"]
.as_array()
.map(|a| {
a.iter()
.map(|g| Aggregation {
op: g["op"].as_str().unwrap_or("").to_string(),
column: g["column"].as_str().map(String::from),
alias: g["as"].as_str().unwrap_or("").to_string(),
})
.collect()
})
.unwrap_or_default();
aggregate(&inputs[0], &group_by, &aggs, runner).await
}
"join" => {
join(
&inputs[0],
&inputs[1],
step["how"].as_str().unwrap_or("inner"),
&str_list(&step["on"]),
runner,
)
.await
}
"union" => union(&inputs, runner).await,
"window" => {
let funcs: Vec<WindowFn> = step["functions"]
.as_array()
.map(|a| {
a.iter()
.map(|g| WindowFn {
op: g["op"].as_str().unwrap_or("").to_string(),
column: g["column"].as_str().map(String::from),
alias: g["as"].as_str().unwrap_or("").to_string(),
})
.collect()
})
.unwrap_or_default();
window(
&inputs[0],
&str_list(&step["partitionBy"]),
&str_list(&step["orderBy"]),
&funcs,
runner,
)
.await
}
other => Err(format!("unknown native op '{other}'")),
}
}
fn register(ctx: &SessionContext, name: &str, input: &Input) -> Result<(), String> {
let batch = rows_to_batch(&input.rows, &input.columns)?;
let provider = MemTable::try_new(batch.schema(), vec![vec![batch]]).map_err(|e| e.to_string())?;
ctx.register_table(name, Arc::new(provider))
.map_err(|e| e.to_string())?;
Ok(())
}
async fn run_sql(ctx: &SessionContext, sql: &str) -> Result<Vec<Row>, String> {
let df = ctx.sql(sql).await.map_err(|e| e.to_string())?;
let batches = df.collect().await.map_err(|e| e.to_string())?;
batches_to_rows(&batches)
}
fn sql_type(dt: &DataType) -> &'static str {
match dt {
DataType::Boolean => "BOOLEAN",
DataType::Utf8 => "VARCHAR",
_ => "DOUBLE",
}
}
#[derive(Debug, Clone)]
pub struct Aggregation {
pub op: String, pub column: Option<String>,
pub alias: String,
}
pub async fn aggregate(
input: &Input,
group_by: &[String],
aggregations: &[Aggregation],
runner: &dyn SqlRunner,
) -> Result<Vec<Row>, String> {
let mut selects: Vec<String> = group_by.iter().map(|g| q(g)).collect();
for a in aggregations {
selects.push(format!("{} AS {}", agg_expr(a)?, q(&a.alias)));
}
let mut sql = format!("SELECT {} FROM input", selects.join(", "));
if !group_by.is_empty() {
sql.push_str(&format!(
" GROUP BY {}",
group_by.iter().map(|g| q(g)).collect::<Vec<_>>().join(", ")
));
}
runner.run(&[("input", input)], &sql).await
}
fn agg_expr(a: &Aggregation) -> Result<String, String> {
let col = || {
a.column
.as_deref()
.map(q)
.ok_or_else(|| format!("{}: column required", a.op))
};
Ok(match a.op.as_str() {
"sum" => format!("SUM({})", col()?),
"avg" => format!("AVG({})", col()?),
"min" => format!("MIN({})", col()?),
"max" => format!("MAX({})", col()?),
"count" => match &a.column {
Some(c) => format!("COUNT({})", q(c)),
None => "COUNT(*)".to_string(),
},
other => return Err(format!("unknown aggregation op '{other}'")),
})
}
fn q(ident: &str) -> String {
format!("\"{}\"", ident.replace('"', "\"\""))
}
pub async fn join(
left: &Input,
right: &Input,
how: &str,
on: &[String],
runner: &dyn SqlRunner,
) -> Result<Vec<Row>, String> {
let is_left = how.eq_ignore_ascii_case("left");
let right_nonkey: Vec<&String> = right.columns.iter().filter(|c| !on.contains(c)).collect();
let mut selects: Vec<String> = Vec::new();
for c in &left.columns {
if right_nonkey.contains(&c) {
if is_left {
selects.push(format!("COALESCE(r.{c}, l.{c}) AS {c}", c = q(c)));
} else {
selects.push(format!("r.{c} AS {c}", c = q(c)));
}
} else {
selects.push(format!("l.{c} AS {c}", c = q(c)));
}
}
for c in &right_nonkey {
if !left.columns.iter().any(|lc| &lc == c) {
selects.push(format!("r.{c} AS {c}", c = q(c)));
}
}
let join_kw = if is_left { "LEFT JOIN" } else { "JOIN" };
let cond = on
.iter()
.map(|k| format!("l.{k} = r.{k}", k = q(k)))
.collect::<Vec<_>>()
.join(" AND ");
let sql = format!("SELECT {} FROM l {join_kw} r ON {cond}", selects.join(", "));
runner.run(&[("l", left), ("r", right)], &sql).await
}
pub async fn union(inputs: &[Input], runner: &dyn SqlRunner) -> Result<Vec<Row>, String> {
let inputs: Vec<&Input> = inputs.iter().filter(|i| !i.rows.is_empty()).collect();
if inputs.is_empty() {
return Ok(Vec::new());
}
let mut columns: Vec<String> = Vec::new();
for inp in &inputs {
for c in &inp.columns {
if !columns.contains(c) {
columns.push(c.clone());
}
}
}
let types: Vec<DataType> = columns
.iter()
.map(|c| {
let (mut s, mut b, mut num) = (false, false, false);
for inp in &inputs {
if inp.columns.contains(c) {
match infer_type(&inp.rows, c) {
DataType::Utf8 => s = true,
DataType::Boolean => b = true,
_ => num = true,
}
}
}
if s || (b && num) {
DataType::Utf8
} else if b {
DataType::Boolean
} else {
DataType::Float64
}
})
.collect();
let names: Vec<String> = (0..inputs.len()).map(|i| format!("u{i}")).collect();
let mut branches = Vec::new();
for (i, inp) in inputs.iter().enumerate() {
let name = &names[i];
let sel: Vec<String> = columns
.iter()
.zip(&types)
.map(|(c, dt)| {
if inp.columns.contains(c) {
format!("CAST({col} AS {ty}) AS {col}", col = q(c), ty = sql_type(dt))
} else {
format!("CAST(NULL AS {ty}) AS {col}", col = q(c), ty = sql_type(dt))
}
})
.collect();
branches.push(format!("SELECT {} FROM {name}", sel.join(", ")));
}
let bindings: Vec<(&str, &Input)> = names.iter().map(String::as_str).zip(inputs.iter().copied()).collect();
runner.run(&bindings, &branches.join(" UNION ALL ")).await
}
#[derive(Debug, Clone)]
pub struct WindowFn {
pub op: String, pub column: Option<String>,
pub alias: String,
}
pub async fn window(
input: &Input,
partition_by: &[String],
order_by: &[String],
funcs: &[WindowFn],
runner: &dyn SqlRunner,
) -> Result<Vec<Row>, String> {
const ORD: &str = "__fv_ord";
let mut cols = input.columns.clone();
cols.push(ORD.to_string());
let mut rows = input.rows.clone();
for (i, r) in rows.iter_mut().enumerate() {
r.0.push((ORD.to_string(), Value::Num(i as f64)));
}
let augmented = Input { columns: cols, rows };
let part = if partition_by.is_empty() {
String::new()
} else {
format!(
"PARTITION BY {} ",
partition_by.iter().map(|c| q(c)).collect::<Vec<_>>().join(", ")
)
};
let mut stable: Vec<String> = order_by.iter().map(|c| q(c)).collect();
stable.push(q(ORD));
let ord_stable = stable.join(", ");
let ord_peers = if order_by.is_empty() {
q(ORD)
} else {
order_by.iter().map(|c| q(c)).collect::<Vec<_>>().join(", ")
};
let running = format!("OVER ({part}ORDER BY {ord_stable} ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)");
let numbered = format!("OVER ({part}ORDER BY {ord_stable})");
let ranked = format!("OVER ({part}ORDER BY {ord_peers})");
let mut selects: Vec<String> = input.columns.iter().map(|c| q(c)).collect();
for f in funcs {
let expr = match f.op.as_str() {
"rowNumber" => format!("ROW_NUMBER() {numbered}"),
"rank" => format!("RANK() {ranked}"),
"denseRank" => format!("DENSE_RANK() {ranked}"),
"cumSum" => format!(
"SUM({}) {running}",
q(f.column.as_deref().ok_or("cumSum: column required")?)
),
"cumCount" => match &f.column {
Some(c) => format!("COUNT({}) {running}", q(c)),
None => format!("COUNT(*) {running}"),
},
other => return Err(format!("unknown window op '{other}'")),
};
selects.push(format!("{expr} AS {}", q(&f.alias)));
}
let sql = format!("SELECT {} FROM w ORDER BY {}", selects.join(", "), q(ORD));
runner.run(&[("w", &augmented)], &sql).await
}