DuckDB v1.5.4 (Variegata) Key Features & Production Best Practices

-- Launch DuckDB CLI with forced dark mode
duckdb -dark-mode

-- Or explicitly specify light mode
duckdb -light-mode

-- Automatic detection in dark terminals
duckdb

-- Create a table with VARIANT type
CREATE TABLE events AS
SELECT 
    event_id,
    user_data::VARCHAR AS user_json,
    PARSE_JSON(user_data) AS variant_data
FROM (VALUES
    (1, '{"name": "Alice", "age": 30, "tags": ["data", "duckdb"]}'),
    (2, '{"name": "Bob", "age": 25, "tags": ["analytics"]}'),
    (3, '{"name": "Charlie", "age": 35, "tags": ["engineering", "duckdb"]}'),
    (4, '{"name": "Diana", "age": 28, "tags": ["ml", "data"]}'),
    (5, '{"name": "Eve", "age": 32, "tags": ["devops"]}')
) AS t(event_id, user_data);

-- Query VARIANT field with FILTER condition
-- Previously returned wrong rows, now fixed
SELECT 
    event_id,
    variant_data:name::VARCHAR AS name,
    variant_data:age::INTEGER AS age
FROM events
WHERE variant_data:tags[*] = 'duckdb';

-- Result:
-- ┌──────────┬─────────┬───────┐
-- │ event_id │  name   │  age  │
-- │  int32   │ varchar │ int32 │
-- ├──────────┼─────────┼───────┤
-- │        1 │ Alice   │    30 │
-- │        3 │ Charlie │    35 │
-- └──────────┴─────────┴───────┘

-- Safely parse JSON, ignoring error rows
CREATE TABLE raw_logs AS
SELECT 
    id,
    TRY_PARSE_JSON(log_data) AS parsed
FROM log_entries;

-- Extract keys from nested objects using json_keys
SELECT 
    id,
    json_keys(parsed, '$') AS keys
FROM raw_logs
WHERE parsed IS NOT NULL;

-- Validate that JSON keys are not NULL
SELECT json_valid('{}') AS is_valid;  -- TRUE
SELECT json_valid(NULL) IS NULL;       -- TRUE

-- Set up source and target tables
CREATE TABLE target_products (
    product_id INTEGER PRIMARY KEY,
    name VARCHAR,
    price DECIMAL(10,2),
    updated_at TIMESTAMP
);

CREATE TABLE staging_products (
    product_id INTEGER,
    name VARCHAR,
    price DECIMAL(10,2),
    created_at TIMESTAMP
);

-- Insert test data
INSERT INTO staging_products VALUES
    (1, 'Laptop Pro', 1299.99, CURRENT_TIMESTAMP),
    (2, 'Wireless Mouse', 29.99, CURRENT_TIMESTAMP),
    (3, 'USB-C Hub', 49.99, CURRENT_TIMESTAMP);

-- Correct MERGE INTO usage
MERGE INTO target_products t
USING staging_products s
ON t.product_id = s.product_id
WHEN MATCHED THEN
    UPDATE SET 
        price = s.price,
        updated_at = CURRENT_TIMESTAMP
WHEN NOT MATCHED THEN
    INSERT (product_id, name, price, updated_at)
    VALUES (s.product_id, s.name, s.price, CURRENT_TIMESTAMP);

-- Verify results
SELECT * FROM target_products ORDER BY product_id;

-- Enable and view Parquet scan statistics
INSTALL spatial;
LOAD spatial;

-- Set to display operator statistics
SET enable_profiling = true;
SET profiling_mode = 'json';

-- Query Parquet files (leveraging statistics pruning)
SELECT 
    city,
    AVG(population) AS avg_population,
    COUNT(*) AS record_count
FROM read_parquet('/data/geography/*.parquet')
WHERE population > 100000
GROUP BY city
ORDER BY avg_population DESC;

-- View row group scan info in execution plan
EXPLAIN ANALYZE
SELECT * 
FROM read_parquet('/data/geography/*.parquet')
WHERE area_sqkm > 500;

INSTALL spatial;
LOAD spatial;

-- Create table with geometric data
CREATE TABLE locations (
    id INTEGER,
    name VARCHAR,
    geom GEOMETRY
);

-- Insert geographic data
INSERT INTO locations VALUES
    (1, 'Beijing', ST_GeomFromText('POINT(116.4074 39.9042)')),
    (2, 'Shanghai', ST_GeomFromText('POINT(121.4737 31.2304)')),
    (3, 'Guangzhou', ST_GeomFromText('POINT(113.2644 23.1291)'));

-- Query locations in a specific region
SELECT name, ST_AsText(geom) 
FROM locations 
WHERE ST_DWithin(geom, ST_GeomFromText('POINT(116.4074 39.9042)'), 500000);

-- Automatically rebuild indexes when attaching
ATTACH 'path/to/database.duckdb' AS remote_db (
    vacuum_rebuild_indexes true
);

-- Use the attached database
SELECT * FROM remote_db.my_table LIMIT 10;

-- Complex window function query
WITH ranked_sales AS (
    SELECT 
        product_id,
        sale_date,
        amount,
        RANK() OVER (PARTITION BY product_id ORDER BY amount DESC) AS rank,
        SUM(amount) OVER (
            PARTITION BY product_id 
            ORDER BY sale_date 
            ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
        ) AS moving_avg_7d
    FROM sales_data
)
SELECT * FROM ranked_sales WHERE rank <= 3;

-- Securely read external Parquet files
-- v1.5.4 handles anomalous input much better
SELECT * 
FROM read_parquet('/data/untrusted/*.parquet', 
    use_vfs=true, 
    max_size=104857600);  -- Limit max read to 100MB

# Method 1: Upgrade via pip
pip install --upgrade duckdb

# Method 2: Upgrade via conda
conda update -c conda-forge duckdb

# Method 3: Download latest CLI
wget https://github.com/duckdb/duckdb/releases/download/v1.5.4/duckdb_cli-linux-amd64.zip
unzip duckdb_cli-linux-amd64.zip
chmod +x duckdb
./duckdb --version  # Verify version

import duckdb

# Check current version
print(duckdb.__version__)  # Should output 1.5.4

# Use new features
con = duckdb.connect(':memory:')

# Better terminal experience in dark mode
result = con.execute("""
    SELECT 
        'v1.5.4' AS version,
        'Variegata' AS codename,
        'CLI dark mode support' AS new_feature
""").fetchall()

for row in result:
    print(row)

# Example: Rapidly build a log analysis microservice
import duckdb
from fastapi import FastAPI

app = FastAPI()

@app.get("/analyze")
def analyze_logs(file_path: str):
    con = duckdb.connect()
    result = con.execute(f"""
        SELECT 
            error_level,
            COUNT(*) as count,
            PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY response_time) as p95
        FROM read_csv_auto('{file_path}')
        GROUP BY error_level
    """).fetchdf()
    return result.to_dict()

-- Query template for auto-generated daily reports
SELECT 
    DATE_TRUNC('day', created_at) AS report_date,
    COUNT(DISTINCT user_id) AS active_users,
    SUM(revenue) AS total_revenue,
    ROUND(AVG(order_value), 2) AS avg_order_value
FROM orders
WHERE created_at >= CURRENT_DATE - INTERVAL '7' DAY
GROUP BY DATE_TRUNC('day', created_at)
ORDER BY report_date DESC;