Aller au contenu
1337skills 1337skills - Aide-mémoires

Metabase Cheat Sheet

Overview

Metabase is an open-source business intelligence tool that enables anyone in an organization to ask questions about their data and get answers in the form of charts, dashboards, and reports. It features an intuitive visual query builder that allows non-technical users to explore data without writing SQL, while also providing a powerful native query editor for analysts and engineers who prefer SQL.

Metabase connects to virtually any SQL database including PostgreSQL, MySQL, BigQuery, Snowflake, Redshift, MongoDB, and many others. It supports interactive dashboards with filters, drill-down capabilities, and auto-refresh. Metabase also offers embedded analytics for integrating dashboards into other applications, scheduled reports via email or Slack, and a question-based permissions model. The platform can be self-hosted or used via Metabase Cloud.

Installation

# Quick start with Docker
docker run -d \
    --name metabase \
    -p 3000:3000 \
    metabase/metabase:latest

# With PostgreSQL application database (production)
docker run -d \
    --name metabase \
    -p 3000:3000 \
    -e MB_DB_TYPE=postgres \
    -e MB_DB_DBNAME=metabase \
    -e MB_DB_PORT=5432 \
    -e MB_DB_USER=metabase \
    -e MB_DB_PASS=metabase_pass \
    -e MB_DB_HOST=postgres-host \
    metabase/metabase:latest

# Access at http://localhost:3000

Docker Compose

version: '3.8'
services:
  metabase:
    image: metabase/metabase:latest
    container_name: metabase
    ports:
      - "3000:3000"
    environment:
      MB_DB_TYPE: postgres
      MB_DB_DBNAME: metabase
      MB_DB_PORT: 5432
      MB_DB_USER: metabase
      MB_DB_PASS: metabase_pass
      MB_DB_HOST: metabase-db
      MB_SITE_URL: https://metabase.example.com
      MB_ENCRYPTION_SECRET_KEY: your-secret-key-here
    depends_on:
      - metabase-db
    restart: unless-stopped

  metabase-db:
    image: postgres:16
    environment:
      POSTGRES_DB: metabase
      POSTGRES_USER: metabase
      POSTGRES_PASSWORD: metabase_pass
    volumes:
      - metabase-pgdata:/var/lib/postgresql/data
    restart: unless-stopped

volumes:
  metabase-pgdata:

JAR Installation

# Download JAR
wget https://downloads.metabase.com/v0.49.0/metabase.jar

# Run (requires Java 11+)
java -jar metabase.jar

# Run with custom port and config
MB_JETTY_PORT=8080 \
MB_DB_TYPE=postgres \
MB_DB_HOST=localhost \
MB_DB_PORT=5432 \
MB_DB_DBNAME=metabase \
MB_DB_USER=metabase \
MB_DB_PASS=password \
java -jar metabase.jar

Kubernetes with Helm

helm repo add metabase https://pmint93.github.io/metabase-chart
helm install metabase metabase/metabase \
    --namespace analytics \
    --create-namespace \
    --set database.type=postgres \
    --set database.host=postgres.default.svc

Database Connections

Supported Databases

DatabaseDriverConnection String
PostgreSQLBuilt-inhost:5432/dbname
MySQLBuilt-inhost:3306/dbname
BigQueryBuilt-inProject ID + Service Account JSON
SnowflakeBuilt-inaccount.snowflakecomputing.com
RedshiftBuilt-incluster.region.redshift.amazonaws.com:5439/db
MongoDBBuilt-inmongodb://host:27017/db
SQLiteBuilt-in/path/to/database.db
SQL ServerBuilt-inhost:1433;database=db
ClickHousePluginhost:8123/database
DuckDBPlugin/path/to/database.duckdb
Trino/PrestoPluginhost:8080/catalog

Adding a Database via API

curl -X POST http://localhost:3000/api/database \
    -H "Content-Type: application/json" \
    -H "X-Metabase-Session: $SESSION_TOKEN" \
    -d '{
        "engine": "postgres",
        "name": "Production DB",
        "details": {
            "host": "db.example.com",
            "port": 5432,
            "dbname": "production",
            "user": "readonly_user",
            "password": "password",
            "ssl": true,
            "tunnel-enabled": false
        }
    }'

API Reference

Authentication

# Get session token
SESSION=$(curl -s -X POST http://localhost:3000/api/session \
    -H "Content-Type: application/json" \
    -d '{"username": "admin@example.com", "password": "password"}' \
    | jq -r '.id')

# Use token in requests
curl -H "X-Metabase-Session: $SESSION" http://localhost:3000/api/card

Common API Endpoints

EndpointMethodDescription
/api/sessionPOSTLogin and get session token
/api/cardGETList all saved questions
/api/cardPOSTCreate a new question
/api/card/{id}GETGet question details
/api/card/{id}/queryPOSTExecute a question
/api/dashboardGETList all dashboards
/api/dashboardPOSTCreate a dashboard
/api/dashboard/{id}GETGet dashboard details
/api/databaseGETList databases
/api/collectionGETList collections
/api/datasetPOSTExecute an ad-hoc query
/api/userGETList users
/api/alertGETList alerts
/api/pulseGETList subscriptions

Execute Custom Query

# Run SQL query via API
curl -X POST http://localhost:3000/api/dataset \
    -H "Content-Type: application/json" \
    -H "X-Metabase-Session: $SESSION" \
    -d '{
        "database": 1,
        "type": "native",
        "native": {
            "query": "SELECT DATE_TRUNC('\''month'\'', created_at) AS month, COUNT(*) AS orders FROM orders GROUP BY 1 ORDER BY 1"
        }
    }'

Configuration

Environment Variables

VariableDescriptionDefault
MB_DB_TYPEApplication database typeh2
MB_DB_HOSTDatabase hostlocalhost
MB_DB_PORTDatabase portvaries
MB_DB_DBNAMEDatabase namemetabase
MB_DB_USERDatabase usermetabase
MB_DB_PASSDatabase password
MB_JETTY_PORTHTTP port3000
MB_SITE_URLPublic-facing URLhttp://localhost:3000
MB_ENCRYPTION_SECRET_KEYEncryption key for DB secrets
MB_EMAIL_SMTP_HOSTSMTP server for emails
MB_EMAIL_SMTP_PORTSMTP port587
MB_EMAIL_FROM_ADDRESSSender email address
MB_EMBEDDING_SECRET_KEYKey for embedding tokens
MB_ENABLE_EMBEDDINGEnable embeddingfalse
MB_PREMIUM_EMBEDDING_TOKENPro/Enterprise license key
JAVA_OPTSJVM options-Xmx2g

Email Configuration

docker run -d \
    --name metabase \
    -p 3000:3000 \
    -e MB_EMAIL_SMTP_HOST=smtp.gmail.com \
    -e MB_EMAIL_SMTP_PORT=587 \
    -e MB_EMAIL_SMTP_SECURITY=tls \
    -e MB_EMAIL_SMTP_USERNAME=your@gmail.com \
    -e MB_EMAIL_SMTP_PASSWORD=app-password \
    -e MB_EMAIL_FROM_ADDRESS=analytics@yourcompany.com \
    metabase/metabase:latest

Advanced Usage

Embedded Analytics

<!-- Embedding a dashboard using iframe (signed JWT) -->
<iframe
    src="http://metabase.example.com/embed/dashboard/TOKEN"
    frameborder="0"
    width="100%"
    height="800"
    allowtransparency
></iframe>
# Generate embedding token (server-side)
import jwt
import time

METABASE_SITE_URL = "http://metabase.example.com"
METABASE_SECRET_KEY = "your-embedding-secret-key"

payload = {
    "resource": {"dashboard": 1},
    "params": {"customer_id": 42},
    "exp": round(time.time()) + (10 * 60)  # 10 min expiry
}

token = jwt.encode(payload, METABASE_SECRET_KEY, algorithm="HS256")
iframe_url = f"{METABASE_SITE_URL}/embed/dashboard/{token}#bordered=true&titled=true"

Custom SQL with Variables

-- Filter variable (dropdown)
SELECT * FROM orders
WHERE status = {{status}}

-- Text variable
SELECT * FROM products
WHERE name ILIKE '%' || {{search_term}} || '%'

-- Date range variable
SELECT * FROM orders
WHERE created_at BETWEEN {{start_date}} AND {{end_date}}

-- Optional filter clause
SELECT * FROM orders
[[WHERE category = {{category}}]]

-- Field filter (smart filter)
SELECT * FROM orders
WHERE {{created_at}}

Backup and Restore

# Backup application database (H2)
cp /path/to/metabase.db.mv.db /backups/metabase-$(date +%Y%m%d).db.mv.db

# Backup PostgreSQL app DB
pg_dump -h localhost -U metabase metabase > metabase_backup.sql

# Restore
psql -h localhost -U metabase metabase < metabase_backup.sql

Troubleshooting

IssueSolution
Slow dashboard loadingEnable caching in Admin > Performance. Optimize underlying SQL queries
Database connection timeoutCheck firewall rules. Increase connection timeout in database settings
Out of memoryIncrease JAVA_OPTS=-Xmx4g. Use PostgreSQL instead of H2 for app database
Embedding not workingVerify MB_ENABLE_EMBEDDING=true. Check JWT token expiry and secret key
Email alerts not sendingVerify SMTP configuration. Check Metabase logs for email errors
Permission issuesCheck collection permissions. Verify data model permissions for tables
H2 database corruptionMigrate to PostgreSQL for production. Restore from latest backup
SSL connection errorsAdd ssl=true to database connection. Import CA certificates if needed
Dashboard filter not workingVerify filter is connected to cards. Check field mapping in filter config
API rate limitingIncrease rate limits in admin settings. Use API keys for automation