Querying the Semantic Layer¶

Query semantic models and metrics using SQL, CLI, or Python APIs.

Query Methods¶

CLI Query¶

seeknal query ecommerce_semantic_model \
  --measures total_revenue,order_count \
  --dimensions region,category

Python Query¶

from seeknal.cli.query import query_metrics

df = query_metrics(
    model="ecommerce_semantic_model",
    measures=["total_revenue", "order_count"],
    dimensions=["region", "category"]
)

Direct SQL Query¶

SELECT
    region,
    SUM(total_revenue) as total_revenue,
    COUNT(*) as order_count
FROM ecommerce_semantic_model_mv
GROUP BY region;

Query Patterns¶

Time-Series Analysis¶

seeknal query ecommerce_metrics \
  --measures total_revenue \
  --dimensions order_date \
  --time_range "2024-01-01 to 2024-12-31"

Filtering¶

seeknal query ecommerce_metrics \
  --measures total_revenue \
  --dimensions region \
  --filters "region='North America'" \
  --filters "category='Electronics'"

Grouping¶

seeknal query ecommerce_metrics \
  --measures total_revenue,order_count \
  --dimensions region,category \
  --group-by region,category

Ordering¶

seeknal query ecommerce_metrics \
  --measures total_revenue \
  --dimensions region \
  --order-by total_revenue DESC

Advanced Queries¶

Subqueries¶

SELECT
    region,
    total_revenue,
    percentile
FROM (
    SELECT
        region,
        total_revenue,
        NTILE(4) OVER (ORDER BY total_revenue ASC) as percentile
    FROM regional_metrics
) subquery
WHERE percentile = 4;  -- Top quartile

Window Functions¶

SELECT
    order_date,
    total_revenue,
    SUM(total_revenue) OVER (
        ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) as cumulative_revenue
FROM daily_metrics;

Joins¶

SELECT
    r.region,
    r.total_revenue,
    c.customer_count
FROM regional_metrics r
JOIN customer_counts c
  ON r.region = c.region;

BI Tool Integration¶

Tableau¶

Connection: StarRocks ODBC
Server: your-starrocks-host
Port: 9030
Database: analytics_prod
Tables: ecommerce_semantic_model_mv, ecommerce_metrics_mv

Power BI¶

Connection: StarRocks
Server: tcp:your-starrocks-host,9030
Database: analytics_prod
Tables: ecommerce_semantic_model_mv, ecommerce_metrics_mv

Metabase¶

Database: StarRocks
Host: your-starrocks-host
Port: 9030
Database: analytics_prod
Username: seeknal_user
Password: your-password

Query Optimization¶

Use Materialized Views¶

-- This uses the pre-computed materialized view (fast)
SELECT region, SUM(total_revenue)
FROM ecommerce_metrics_mv
GROUP BY region;

Avoid Complex Subqueries¶

-- Instead of this
SELECT * FROM (
    SELECT * FROM (
        SELECT * FROM base_table
    ) WHERE condition1
) WHERE condition2;

-- Use this
SELECT * FROM base_table
WHERE condition1 AND condition2;

Limit Result Sets¶

seeknal query ecommerce_metrics \
  --measures total_revenue \
  --dimensions region \
  --limit 10

Troubleshooting¶

Query Timeout¶

Issue: Query takes too long to execute.

Solutions: - Check materialization strategy - Add appropriate filters - Review query execution plan

Incorrect Results¶

Issue: Query returns unexpected values.

Solutions: - Verify semantic model definition - Check metric formulas - Validate data sources

Best Practices¶

Use filters to reduce data volume
Leverage materialized views for performance
Test queries before BI integration
Document common queries for team
Monitor query performance regularly

Semantic Models - Model structure
Metrics - Metric definitions
Deployment - Production deployment

Return to: Semantic Layer Overview