# Add to mix.exs def deps do [ {:duckdb_ex, "~> 0.1.1"} ] end

# Connect to in-memory database {:ok, conn} = DuckdbEx.Connection.connect(:memory) # Or connect to a file {:ok, conn} = DuckdbEx.Connection.connect("/path/to/database.duckdb") # Execute SQL directly {:ok, result} = DuckdbEx.Connection.execute(conn, "SELECT 42 as answer") # Fetch all rows as maps {:ok, rows} = DuckdbEx.Connection.fetch_all(conn, "SELECT * FROM users") # => [%{"id" => 1, "name" => "Alice"}, ...] # Fetch single row {:ok, row} = DuckdbEx.Connection.fetch_one(conn, "SELECT * FROM users LIMIT 1") # => %{"id" => 1, "name" => "Alice"} # Close connection DuckdbEx.Connection.close(conn)

# Create a relation (no execution yet) relation = DuckdbEx.Connection.table(conn, "orders") # Chain operations (still no execution) result = relation |> DuckdbEx.Relation.filter("amount > 100") |> DuckdbEx.Relation.project(["customer_name", "amount", "order_date"]) |> DuckdbEx.Relation.order("amount DESC") |> DuckdbEx.Relation.limit(10) |> DuckdbEx.Relation.fetch_all() # Executes here # Result: Top 10 orders over $100 {:ok, rows} = result

# From a table or view relation = DuckdbEx.Connection.table(conn, "products") # From SQL relation = DuckdbEx.Connection.sql(conn, "SELECT * FROM generate_series(1, 100)") # From range (using DuckDB's range function) relation = DuckdbEx.Connection.sql(conn, "SELECT * FROM range(10)")

# Simple filter relation |> DuckdbEx.Relation.filter("price > 50") |> DuckdbEx.Relation.fetch_all() # Chain multiple filters (AND logic) relation |> DuckdbEx.Relation.filter("price > 50") |> DuckdbEx.Relation.filter("category = 'Electronics'") |> DuckdbEx.Relation.fetch_all() # Complex conditions relation |> DuckdbEx.Relation.filter("price > 50 AND (category = 'Electronics' OR category = 'Computers')") |> DuckdbEx.Relation.fetch_all()

# Select specific columns relation |> DuckdbEx.Relation.project(["name", "price"]) |> DuckdbEx.Relation.fetch_all() # Use expressions relation |> DuckdbEx.Relation.project([ "name", "price", "price * 1.1 as price_with_tax", "upper(category) as category_upper" ]) |> DuckdbEx.Relation.fetch_all()

# Order by column relation |> DuckdbEx.Relation.order("price DESC") |> DuckdbEx.Relation.fetch_all() # Multiple columns relation |> DuckdbEx.Relation.order("category ASC, price DESC") |> DuckdbEx.Relation.fetch_all() # Limit results relation |> DuckdbEx.Relation.limit(100) |> DuckdbEx.Relation.fetch_all() # Top-N query relation |> DuckdbEx.Relation.order("revenue DESC") |> DuckdbEx.Relation.limit(10) |> DuckdbEx.Relation.fetch_all()

# Count all rows relation |> DuckdbEx.Relation.aggregate("count(*) as total") |> DuckdbEx.Relation.fetch_all() # => {:ok, [%{"total" => 1000}]} # Multiple aggregations relation |> DuckdbEx.Relation.aggregate([ "count(*) as count", "sum(amount) as total", "avg(amount) as average", "min(amount) as minimum", "max(amount) as maximum" ]) |> DuckdbEx.Relation.fetch_all()

# Group by single column DuckdbEx.Connection.table(conn, "sales") |> DuckdbEx.Relation.aggregate( "sum(amount) as total_sales", group_by: ["region"] ) |> DuckdbEx.Relation.fetch_all() # Group by multiple columns DuckdbEx.Connection.table(conn, "sales") |> DuckdbEx.Relation.aggregate( ["sum(amount) as total", "count(*) as count"], group_by: ["region", "year"] ) |> DuckdbEx.Relation.fetch_all() # With filtering and ordering DuckdbEx.Connection.table(conn, "products") |> DuckdbEx.Relation.filter("price > 10") # WHERE clause |> DuckdbEx.Relation.aggregate( ["sum(price) as total", "count(*) as count"], group_by: ["category"] ) |> DuckdbEx.Relation.filter("total > 1000") # HAVING clause |> DuckdbEx.Relation.order("total DESC") |> DuckdbEx.Relation.fetch_all()

# Count rows relation |> DuckdbEx.Relation.count() |> DuckdbEx.Relation.fetch_all() # => {:ok, [%{"count" => 100}]} # Sum a column relation |> DuckdbEx.Relation.sum("amount") |> DuckdbEx.Relation.fetch_all() # => {:ok, [%{"sum" => 45000}]} # Average relation |> DuckdbEx.Relation.avg("price") |> DuckdbEx.Relation.fetch_all() # => {:ok, [%{"avg" => 42.5}]} # Min/Max relation |> DuckdbEx.Relation.min("temperature") |> DuckdbEx.Relation.fetch_all() relation |> DuckdbEx.Relation.max("score") |> DuckdbEx.Relation.fetch_all()

{:ok, conn} = DuckdbEx.Connection.connect(:memory) # Create and populate table DuckdbEx.Connection.execute(conn, """  CREATE TABLE orders (  order_id INTEGER,  customer_name VARCHAR,  product_category VARCHAR,  amount DECIMAL(10,2),  order_date DATE  ) """) DuckdbEx.Connection.execute(conn, """  INSERT INTO orders VALUES  (1, 'Alice', 'Electronics', 999.99, '2024-01-15'),  (2, 'Bob', 'Books', 29.99, '2024-01-16'),  (3, 'Alice', 'Electronics', 49.99, '2024-01-17'),  (4, 'Charlie', 'Furniture', 599.99, '2024-01-18'),  (5, 'Bob', 'Electronics', 299.99, '2024-01-19') """) # Analyze: Top customers by total spending in Electronics {:ok, top_customers} = conn |> DuckdbEx.Connection.table("orders") |> DuckdbEx.Relation.filter("product_category = 'Electronics'") |> DuckdbEx.Relation.aggregate( ["sum(amount) as total_spent", "count(*) as order_count"], group_by: ["customer_name"] ) |> DuckdbEx.Relation.filter("total_spent > 100") |> DuckdbEx.Relation.order("total_spent DESC") |> DuckdbEx.Relation.fetch_all() # Result: # [ # %{"customer_name" => "Alice", "total_spent" => 1049.98, "order_count" => 2}, # %{"customer_name" => "Bob", "total_spent" => 299.99, "order_count" => 1} # ]

# Daily sales aggregation with statistical measures {:ok, daily_stats} = conn |> DuckdbEx.Connection.table("sales") |> DuckdbEx.Relation.aggregate( [ "date_trunc('day', timestamp) as day", "sum(amount) as daily_total", "avg(amount) as daily_avg", "stddev_pop(amount) as daily_stddev", "count(*) as transaction_count" ], group_by: ["date_trunc('day', timestamp)"] ) |> DuckdbEx.Relation.order("day DESC") |> DuckdbEx.Relation.limit(30) |> DuckdbEx.Relation.fetch_all()

defmodule DataPipeline do def process_sales_data(conn) do # Reusable base relation base = DuckdbEx.Connection.table(conn, "raw_sales") # High-value customers high_value = base |> DuckdbEx.Relation.filter("total_purchases > 1000") |> DuckdbEx.Relation.project(["customer_id", "email"]) # Recent activity recent = base |> DuckdbEx.Relation.filter("order_date > '2024-01-01'") |> DuckdbEx.Relation.aggregate( "count(*) as recent_orders", group_by: ["customer_id"] ) # Execute both queries {:ok, high_value_customers} = DuckdbEx.Relation.fetch_all(high_value) {:ok, recent_activity} = DuckdbEx.Relation.fetch_all(recent) {high_value_customers, recent_activity} end end

# Use DuckDB's built-in functions conn |> DuckdbEx.Connection.sql("SELECT * FROM range(100)") |> DuckdbEx.Relation.filter("range % 2 = 0") # Even numbers only |> DuckdbEx.Relation.project(["range", "range * range as squared"]) |> DuckdbEx.Relation.fetch_all() # Generate test data conn |> DuckdbEx.Connection.sql("SELECT * FROM generate_series(1, 1000) as id") |> DuckdbEx.Relation.project([ "id", "random() as random_value", "case when id % 2 = 0 then 'even' else 'odd' end as parity" ]) |> DuckdbEx.Relation.aggregate( ["avg(random_value) as avg_random", "count(*) as count"], group_by: ["parity"] ) |> DuckdbEx.Relation.fetch_all()

# Elixir DuckDB {:ok, conn} = DuckdbEx.Connection.connect(:memory) {:ok, result} = conn |> DuckdbEx.Connection.table("users") |> DuckdbEx.Relation.filter("age > 25") |> DuckdbEx.Relation.project(["name", "email"]) |> DuckdbEx.Relation.order("name") |> DuckdbEx.Relation.limit(10) |> DuckdbEx.Relation.fetch_all()