### Build DataStoria from Source

Source: https://github.com/frankchen021/datastoria/blob/master/README.md

Steps to clone the repository, install dependencies, and start the development server for building DataStoria locally.

```bash
# Clone the repository
git clone --recurse-submodules https://github.com/FrankChen021/datastoria.git
cd datastoria

# Install dependencies
npm install --force

# Start the development server
npm run dev
```

--------------------------------

### Build and Start Production Server

Source: https://github.com/frankchen021/datastoria/blob/master/docs/manual/01-getting-started/installation.md

Build the DataStoria application for production and then start the production server. The application will be accessible at http://localhost:3000.

```bash
npm run build

npm start
```

--------------------------------

### System Sync Replica Command Example

Source: https://github.com/frankchen021/datastoria/blob/master/docs/manual/02-ai-features/cluster-health-advisor.md

Example of a command used to synchronize a replica, often recommended for replication issues.

```sql
SYSTEM SYNC REPLICA db.table
```

--------------------------------

### Build and Run Application in Production Mode

Source: https://github.com/frankchen021/datastoria/blob/master/docs/dev/authentication.md

First, build the application for production using 'npm run build', then start the production server with 'npm start'. Remember to update your OAuth redirect URIs in your provider settings to match your production domain.

```bash
npm run build
npm start
```

--------------------------------

### Get ClickHouse Server Version

Source: https://github.com/frankchen021/datastoria/blob/master/docs/manual/02-ai-features/natural-language-sql.md

This example shows how to ask for the current ClickHouse server version using natural language. The generated SQL query is provided.

```SQL
SELECT version() AS clickhouse_version LIMIT 1
```

--------------------------------

### Install Dependencies

Source: https://github.com/frankchen021/datastoria/blob/master/docs/manual/01-getting-started/installation.md

Install all project dependencies, including building required submodules like number-flow and cmdk. This command ensures all necessary packages are set up for development.

```bash
npm install --force
```

--------------------------------

### Copy .env.example to .env

Source: https://github.com/frankchen021/datastoria/blob/master/docs/dev/llm-provider-api-key.md

Copy the example environment file to create a new .env file for your project configuration.

```bash
cp .env.example .env
```

--------------------------------

### Get Unified Execution Plan with Details

Source: https://github.com/frankchen021/datastoria/blob/master/docs/manual/03-query-experience/query-explain.md

Execute EXPLAIN PLAN with json, indexes, and actions parameters to get a comprehensive execution plan including primary keys, indexes, read scope, and execution flow.

```sql
EXPLAIN PLAN json=1, indexes=1, actions=1
```

--------------------------------

### Example User Request for Visualization Skill

Source: https://github.com/frankchen021/datastoria/blob/master/docs/manual/02-ai-features/skills.md

When a user asks a question that implies a visualization, the agent automatically loads the 'Visualization' skill. This example shows a natural language query that would trigger this skill.

```text
Visualize the number of commits in 2021 Feb by day in line chart
```

--------------------------------

### Run Datastoria Container (Basic)

Source: https://github.com/frankchen021/datastoria/blob/master/docker/README.md

Starts a Datastoria container with basic port mapping. Access the application via http://localhost:3000.

```bash
docker run --name datastoria -p 3000:3000 datastoria

```

--------------------------------

### Example User Request to Activate Official Skill

Source: https://github.com/frankchen021/datastoria/blob/master/docs/manual/02-ai-features/skills.md

To activate official ClickHouse skills, users can include keywords like 'best practice' in their requests. This example demonstrates how to prompt the agent to apply best practices when reviewing a table.

```text
Apply the best practice to review the table: default.sampel_table
```

--------------------------------

### Run Datastoria Container with Environment File

Source: https://github.com/frankchen021/datastoria/blob/master/docker/README.md

Starts a Datastoria container using environment variables defined in a .env file. This allows for custom configurations.

```bash
docker run --name datastoria -p 3000:3000 --env-file .env datastoria

```

--------------------------------

### Line Chart Specification Example

Source: https://github.com/frankchen021/datastoria/blob/master/resources/skills/visualization/SKILL.md

Example of a JSON structure for a line chart specification, including title, width, legend options, and the SQL datasource.

```chart-spec
{
  "type": "line",
  "titleOption": { "title": "Descriptive chart title", "align": "center" },
  "width": 6,
  "legendOption": { "placement": "bottom", "values": ["min", "max", "sum"] },
  "datasource": { "sql": "SELECT ..." }
}
```

--------------------------------

### Pie Chart Specification Example

Source: https://github.com/frankchen021/datastoria/blob/master/resources/skills/visualization/SKILL.md

Example of a JSON structure for a pie chart specification, including title, width, legend, label options, value format, and the SQL datasource.

```chart-spec
{
  "type": "pie",
  "titleOption": { "title": "Distribution by Category", "align": "center" },
  "width": 6,
  "legendOption": { "placement": "right" },
  "labelOption": { "show": true, "format": "name-percent" },
  "valueFormat": "short_number",
  "datasource": { "sql": "SELECT ..." }
}
```

--------------------------------

### Example Usage of /explain_error_code

Source: https://github.com/frankchen021/datastoria/blob/master/docs/manual/02-ai-features/slash-commands.md

Demonstrates how to use the /explain_error_code slash command to diagnose a ClickHouse error. Provide the error code, a relevant error message, and optionally the SQL query for context.

```text
/explain_error_code error code: 42
error message: DB::Exception: Number of arguments for function toDate doesn't match
sql:
SELECT toDate(event_time, 'UTC') FROM events
```

--------------------------------

### Generate Open Graph Image with ImageMagick

Source: https://github.com/frankchen021/datastoria/blob/master/docs/public/README.md

Use this command-line snippet to quickly generate a basic Open Graph image with DataStoria branding and tagline. Ensure ImageMagick is installed.

```bash
# Create a simple OG image with text
convert -size 1200x630 xc:'#3b82f6' \
  -font Arial-Bold -pointsize 72 -fill white \
  -gravity center -annotate +0-100 'DataStoria' \
  -pointsize 36 -annotate +0+50 'AI-native ClickHouse Console' \
  og-image.png
```

--------------------------------

### Count Select Queries in Past 3 Hours

Source: https://github.com/frankchen021/datastoria/blob/master/docs/manual/02-ai-features/natural-language-sql.md

This example demonstrates how to count SELECT queries executed in the last 3 hours using natural language. Ensure your database user has access to system tables.

```SQL
SELECT count() AS select_queries_last_3h
FROM system.query_log
WHERE event_date >= toDate(now() - INTERVAL 3 HOUR)
  AND event_time >= now() - INTERVAL 3 HOUR
  AND type = 'QueryFinish'
  AND is_initial_query = 1
  AND query_kind = 'Select'
SETTINGS log_queries = 1
```

--------------------------------

### Optimized SQL Query with Partition Pruning

Source: https://github.com/frankchen021/datastoria/blob/master/docs/manual/02-ai-features/query-optimization.md

An example of an optimized SQL query that leverages partition pruning by adding an event_date filter to reduce data scanning.

```sql
SELECT
    toStartOfDay(event_time) AS day,
    count() AS queries
FROM system.query_log
WHERE event_time >= now() - INTERVAL 7 DAY
  AND event_date >= today() - 7     -- use partition key for pruning
GROUP BY day
ORDER BY day;
```

--------------------------------

### Get Current Database

Source: https://github.com/frankchen021/datastoria/blob/master/resources/skills/diagnose-clickhouse-errors/references/60.md

Execute this query to retrieve the name of the current database context. This is used when the table name is unqualified.

```sql
SELECT currentDatabase()
```

--------------------------------

### Run DataStoria with Docker

Source: https://github.com/frankchen021/datastoria/blob/master/README.md

Command to run DataStoria using a pre-built Docker image. Supports both Intel/AMD and Apple Silicon Macs.

```bash
docker run -d -p 3000:3000 frankchen021/datastoria:latest
```

--------------------------------

### Build Docker Image from Source

Source: https://github.com/frankchen021/datastoria/blob/master/docker/README.md

Builds the Docker image for Datastoria from the source code. This is the default build method.

```bash
docker build -t datastoria -f docker/Dockerfile .

```

--------------------------------

### Check Cluster Health

Source: https://github.com/frankchen021/datastoria/blob/master/docs/manual/02-ai-features/cluster-health-advisor.md

Use this command to get an instant overview of current cluster issues and health status.

```text
Check cluster health
```

--------------------------------

### Build Docker Image with Prebuilt Artifacts

Source: https://github.com/frankchen021/datastoria/blob/master/docker/README.md

Builds the Docker image using prebuilt artifacts for faster CI builds. Requires running the build process outside of Docker first.

```bash
-- run build outside docker
npm run build

docker build -t datastoria -f docker/Dockerfile --target runner-prebuilt --build-arg BUILD_FROM_SOURCE=0 .

```

--------------------------------

### Get Simplified Data Read Statistics

Source: https://github.com/frankchen021/datastoria/blob/master/docs/manual/03-query-experience/query-explain.md

Obtain simplified statistics about the data to be read for a query using the EXPLAIN ESTIMATE statement.

```sql
EXPLAIN ESTIMATE
```

--------------------------------

### Configure .env for NextAuth and Google OAuth

Source: https://github.com/frankchen021/datastoria/blob/master/docs/dev/authentication.md

Sets up essential NextAuth.js configuration and enables Google OAuth by providing the client ID and secret. Ensure NEXTAUTH_GOOGLE_ENABLED is true to activate the provider.

```env
NEXTAUTH_SECRET=<paste-your-secret-here>

# Example for Google OAuth (provider is enabled when credentials are set AND NEXTAUTH_GOOGLE_ENABLED=true):
NEXTAUTH_GOOGLE_ENABLED=true
GOOGLE_CLIENT_ID=your-google-client-id
GOOGLE_CLIENT_SECRET=your-google-client-secret
```

--------------------------------

### Docker Compose Configuration

Source: https://github.com/frankchen021/datastoria/blob/master/docs/manual/01-getting-started/installation.md

A Docker Compose configuration for a production-ready DataStoria setup. It specifies the image, port mapping, environment, and restart policy.

```yaml
version: '3.8'
services:
  datastoria:
    image: frankchen021/datastoria:latest
    ports:
      - "3000:3000"
    environment:
      - NODE_ENV=production
    restart: unless-stopped
```

--------------------------------

### View Execution Pipeline

Source: https://github.com/frankchen021/datastoria/blob/master/docs/manual/03-query-experience/query-explain.md

Visualize the execution pipeline of a query using the EXPLAIN PIPELINE statement.

```sql
EXPLAIN PIPELINE
```

--------------------------------

### Accessing ProfileEvents in system.query_log (Map vs Flattened)

Source: https://github.com/frankchen021/datastoria/blob/master/resources/skills/clickhouse-system-queries/SKILL.md

Demonstrates how to access ProfileEvents data from the system.query_log table, showing both map-style access and flattened column access. Use map access when ProfileEvents is a Map, and flattened column access if it's flattened.

```sql
-- Map access
SELECT ProfileEvents['DistributedConnectionFailTry'] AS fails
FROM system.query_log WHERE event_date = today();
```

```sql
-- Flattened column access
SELECT ProfileEvent_DistributedConnectionFailTry AS fails
FROM system.query_log WHERE event_date = today();
```

--------------------------------

### Text Search within Queries

Source: https://github.com/frankchen021/datastoria/blob/master/resources/skills/clickhouse-system-queries/references/system-query-log.md

Filters queries that contain a specific keyword, ignoring case. The `positionCaseInsensitive` function returns the starting position of the keyword if found.

```sql
positionCaseInsensitive(query, 'keyword') > 0
```

--------------------------------

### Show Stack Trace Snippet

Source: https://github.com/frankchen021/datastoria/blob/master/docs/manual/03-query-experience/sql-snippets.md

Display demangled stack traces from system.stack_trace for debugging and performance analysis.

```sql
SELECT * FROM system.stack_trace LIMIT 10
```

--------------------------------

### Optimize a Specific SQL Query

Source: https://github.com/frankchen021/datastoria/blob/master/docs/manual/02-ai-features/query-optimization.md

Paste or write a SQL query directly for optimization. Ideal for optimizing a specific query or ensuring a new query is optimized from the start.

```text
please check if the following SQL can be optimized:

select toStartOfDay(event_time), count() 
from system.query_log
where event_time > now() - interval 7 days group by 1
```

--------------------------------

### Find Similar ClickHouse Settings

Source: https://github.com/frankchen021/datastoria/blob/master/resources/skills/diagnose-clickhouse-errors/references/115.md

Use this SQL query to find the closest matches for an unknown setting name in the `system.settings` table. Replace 'SETTING_NAME' with the actual unknown setting.

```sql
SELECT name, levenshteinDistance(name, 'SETTING_NAME') AS dist
FROM system.settings
ORDER BY dist
LIMIT 3
```

--------------------------------

### View Abstract Syntax Tree (AST) in Text Mode

Source: https://github.com/frankchen021/datastoria/blob/master/docs/manual/03-query-experience/query-explain.md

Switch to 'Text Mode' to view the traditional text-based AST output for a parsed SQL query.

```sql
EXPLAIN AST
```

--------------------------------

### Find Closest Table Matches (Unqualified Name)

Source: https://github.com/frankchen021/datastoria/blob/master/resources/skills/diagnose-clickhouse-errors/references/60.md

Use this query after getting the current database to find the closest table matches across all databases. Replace `TABLE_NAME` with the actual table name.

```sql
SELECT
    database,
    name,
    engine,
    levenshteinDistance(lower(name), lower('TABLE_NAME')) AS dist
FROM system.tables
ORDER BY dist
LIMIT 3
```

--------------------------------

### Clone DataStoria Repository

Source: https://github.com/frankchen021/datastoria/blob/master/docs/manual/01-getting-started/installation.md

Clone the DataStoria repository including its submodules. Navigate into the cloned directory. If you cloned without submodules initially, use the second command to initialize them.

```bash
git clone --recurse-submodules https://github.com/FrankChen021/datastoria.git
cd datastoria
```

```bash
git submodule update --init --recursive
```

--------------------------------

### Discover and Optimize Expensive Queries by CPU

Source: https://github.com/frankchen021/datastoria/blob/master/docs/manual/02-ai-features/query-optimization.md

Request to find and optimize the top N queries based on CPU usage. Requires privileges on the system.query_log table.

```text
Find the top 5 queries by CPU and optimize them
```

--------------------------------

### Show Disks Snippet

Source: https://github.com/frankchen021/datastoria/blob/master/docs/manual/03-query-experience/sql-snippets.md

View disk usage across all cluster nodes. This snippet shows free space, total space, and utilization ratios.

```sql
SELECT name, path, total_bytes, available_bytes, round(100 * (total_bytes - available_bytes) / total_bytes, 2) AS utilization_percent FROM system.disks
```

--------------------------------

### Restart Development Server

Source: https://github.com/frankchen021/datastoria/blob/master/docs/dev/llm-provider-api-key.md

After modifying the .env file, restart your development server to apply the changes.

```bash
npm run dev
```

--------------------------------

### View Syntax Check Results

Source: https://github.com/frankchen021/datastoria/blob/master/docs/manual/03-query-experience/query-explain.md

Use EXPLAIN SYNTAX to see how ClickHouse interprets and normalizes your SQL query syntax.

```sql
EXPLAIN SYNTAX
```

--------------------------------

### Configure .env for GitHub OAuth

Source: https://github.com/frankchen021/datastoria/blob/master/docs/dev/authentication.md

Enables GitHub OAuth by setting the NEXTAUTH_GITHUB_ENABLED flag and providing the GitHub application's client ID and secret. Ensure these are correctly obtained from GitHub Developer Settings.

```env
NEXTAUTH_GITHUB_ENABLED=true
GITHUB_CLIENT_ID=your-github-client-id
GITHUB_CLIENT_SECRET=your-github-client-secret
```

--------------------------------

### Executing SQL for Time-Bucket Visualization

Source: https://github.com/frankchen021/datastoria/blob/master/resources/skills/clickhouse-system-queries/SKILL.md

This SQL query is used with the 'execute_sql' tool to visualize query counts and average duration over five-minute buckets within the last hour. It requires the event_date to be today and event_time to be within the last hour.

```sql
SELECT toStartOfFiveMinutes(event_time) AS bucket,
       count() AS queries,
       avg(query_duration_ms) AS avg_ms
FROM system.query_log
WHERE event_date = today() AND event_time > now() - INTERVAL 1 HOUR
GROUP BY bucket ORDER BY bucket
```

--------------------------------

### Generate Visualization Data for HTTP and TCP Threads

Source: https://github.com/frankchen021/datastoria/blob/master/docs/manual/02-ai-features/intelligent-visualization.md

This SQL query aggregates and selects data for HTTPThreads and TCPThreads metrics from the system.asynchronous_metric_log table over the last 6 hours. It's designed to provide time-series data suitable for line charts.

```sql
SELECT
    toStartOfMinute(event_time) AS ts,
    maxIf(value, metric = 'HTTPThreads') AS HTTPThreads,
    maxIf(value, metric = 'TCPThreads') AS TCPThreads
FROM system.asynchronous_metric_log
WHERE event_date >= today() - 1
  AND event_time >= now() - INTERVAL 6 HOUR
  AND metric IN ('HTTPThreads', 'TCPThreads')
GROUP BY ts
ORDER BY ts
LIMIT 10000
```

--------------------------------

### Show Part History Snippet

Source: https://github.com/frankchen021/datastoria/blob/master/docs/manual/03-query-experience/sql-snippets.md

View data part operations history from system.part_log. It includes merge operations, duration, and size information.

```sql
SELECT * FROM system.part_log WHERE database = 'default' AND table = 'my_table' ORDER BY event_time DESC LIMIT 10
```

--------------------------------

### Discover and Optimize Queries by Disk Usage

Source: https://github.com/frankchen021/datastoria/blob/master/docs/manual/02-ai-features/query-optimization.md

Request to find and optimize queries with the highest disk I/O or storage usage. Requires privileges on the system.query_log table.

```text
Optimize queries with highest disk usage
```

--------------------------------

### Accessing System Query Log Across Replicas

Source: https://github.com/frankchen021/datastoria/blob/master/resources/skills/clickhouse-system-queries/references/system-query-log.md

Specifies the source table for querying the system.query_log, ensuring data from all replicas is included.

```sql
FROM {clusterAllReplicas:system.query_log}
```

--------------------------------

### SQL Function Not Exists Highlighting

Source: https://github.com/frankchen021/datastoria/blob/master/docs/manual/03-query-experience/error-diagnostics.md

Highlights an error where a function with the name 'toStartOfHou' does not exist in ClickHouse. This helps in correcting function names or using available functions.

```sql
select toStartOfHou(event_time), count()
       ^^^^^^^^^^^^ --- Function with name `toStartOfHou` does not exist
from system.query_log
where event_time > now() - interval 3 hour
group by toStartOfHour(event_tim)
```