Querying & Analytics

Query collected datasets with DuckDB SQL and generate analytics

Overview

After collecting data with a dataset pipeline, query it with SQL powered by DuckDB. This works directly on the Parquet files the pipeline wrote — no separate database needed.

Requires the data extra: pip install "anysite-cli[data]"

SQL Queries

anysite dataset query dataset.yaml \
  --sql "SELECT name, headline FROM profiles LIMIT 10"

# From a .sql file instead of --sql
anysite dataset query dataset.yaml --file report.sql

Query a Specific Source

--source auto-generates a SELECT for you, combined with --fields (dot-notation supported) or --exclude:

anysite dataset query dataset.yaml \
  --source profiles \
  --fields "name, headline, urn.value AS id"

--fields and --exclude are mutually exclusive; --fields takes precedence if both are given.

Complex Queries

# Aggregation
anysite dataset query dataset.yaml \
  --sql "SELECT industry, COUNT(*) as count FROM companies GROUP BY industry ORDER BY count DESC"

# Joins across sources
anysite dataset query dataset.yaml \
  --sql "
    SELECT p.name, p.headline, c.name as company
    FROM profiles p
    JOIN employees e ON p.urn_value = e.urn_value
    JOIN companies c ON e.company_id = c.urn_value
    LIMIT 20
  "

# Filtering
anysite dataset query dataset.yaml \
  --sql "SELECT * FROM profiles WHERE headline LIKE '%CTO%' OR headline LIKE '%CEO%'"

Interactive Mode

anysite dataset query dataset.yaml --interactive

Opens a DuckDB shell with all dataset sources registered as views, for exploring the data with plain SQL.

Column Statistics

dataset stats runs against one source and reports per-column statistics: total rows, non-null count, null count, distinct value count, and — for numeric columns — min, max, and average.

# Defaults to the first source if --source is omitted
anysite dataset stats dataset.yaml --source profiles

Dataset Profile

dataset profile runs across all sources in the pipeline and reports source-level quality metrics: record count, column count, and completeness (percentage of non-null values across all columns). It does not accept --source and does not compute per-column statistics.

anysite dataset profile dataset.yaml

Output Formats

Query, stats, and profile results support the same output formats:

# Table (default)
anysite dataset query dataset.yaml --sql "SELECT * FROM profiles LIMIT 5" --format table

# CSV export
anysite dataset query dataset.yaml --sql "SELECT * FROM profiles" --format csv --output report.csv

# JSON / JSONL
anysite dataset query dataset.yaml --sql "SELECT * FROM profiles" --format json
anysite dataset query dataset.yaml --sql "SELECT * FROM profiles" --format jsonl

Commands Reference

Command	Description
anysite dataset query <yaml> --sql "..."	Run a SQL query on collected data
anysite dataset query <yaml> --source <id>	Auto-generate and run a query against one source
anysite dataset query <yaml> --interactive	Open an interactive DuckDB SQL shell
anysite dataset stats <yaml> --source <id>	Per-column statistics for one source
anysite dataset profile <yaml>	Per-source quality summary across the whole pipeline

Next Steps

Examples

See complete end-to-end workflow examples

Database Loading

Load query results into a persistent database