Skip to main content

​
What You’ll Learn

In this guide, you’ll learn how to query and analyze your data using the SQL Viewer. By the end, you’ll be able to:
  • Select and explore available datasets
  • Write SQL queries using the editor or visual builder
  • Execute queries and view results in an interactive table
  • Export large datasets in multiple formats

​
Before You Start

Prerequisites: Observer-level access or higher to view and query datasetsEstimated Time: 10-15 minutesDifficulty Level: Intermediate to Advanced
If you’re unsure which dataset contains the information you need, contact your Customer Success Manager for guidance before proceeding.

​
Understanding the SQL Viewer Interface

The SQL Viewer provides a workspace divided into three main sections:
  1. Dataset Browser (Left Panel) - Browse and select from available datasets
  2. Query Editor (Top Right Panel) - Write and build your SQL queries
  3. Results Viewer (Bottom Right Panel) - View and export query results
SQL Viewer interface showing dataset browser, query editor, and results table

​
Step-by-Step Instructions

1

Navigate to the SQL Viewer

From the main navigation menu, locate and click on Settings, then select CAM SQL Viewer.You’ll see the three-panel workspace with the dataset browser on the left and the query editor on the right.
2

Select a Dataset

In the left panel, browse the list of available datasets. You can:
  • Search by name: Use the search box at the top to filter datasets
  • Browse by group: Datasets are organized into groups using expandable sections
  • Review status: Look for error badges that indicate dataset issues
Click on a dataset to select it. Once selected:
  • The dataset name will be highlighted
  • The schema (list of columns) will appear below the dataset list
  • The query editor will be ready for use
Dataset browser showing search functionality and dataset selection
3

Review the Dataset Schema

After selecting a dataset, review the column list that appears in the left panel. This shows:
  • Column names: The fields available in your dataset
  • Data types: Whether each column contains text, numbers, dates, or other data types
You can click on column checkboxes to automatically add them to your SELECT clause in the query editor.
4

Build Your Query

In the query editor (top right panel), you have multiple options for building your query:Option 1: Text Editor
  • Write your SQL query directly in the text field
  • Use autocomplete by pressing Ctrl+Space or typing 2+ characters
  • Click the Format button to clean up your SQL formatting
Option 2: Visual Builder
  • Switch to the Visual Builder tab for a no-code interface
  • Use drag-and-drop controls to build your query
  • The SQL is automatically generated as you build
Option 3: Query Cookbook
  • Click the Help button (?) to open the Query Cookbook
  • Browse 20+ pre-built query templates
  • Click any template to insert it into your editor
  • Modify the template to match your specific needs
Generate a report of closed emissions from the previous quarter that have been processed through emission operations, including all resolution details:
Example: Previous Quarter Closed Emissions
SELECT * 
FROM MainTable 
WHERE startTime::DATE >= DATE_TRUNC('quarter', CURRENT_DATE - INTERVAL '3 months') 
  AND startTime::DATE < DATE_TRUNC('quarter', CURRENT_DATE) 
  AND status = 'CLOSED' 
LIMIT 100
View all emissions with OPEN or IN PROGRESS status that require resolution by the emission operations team. Use this query to track your team’s pending work items:
Example: Pending Emissions Pending Resolution
SELECT * 
FROM MainTable 
WHERE status IN ('OPEN', 'IN_PROGRESS')
ORDER BY startTime DESC
LIMIT 100
Query editor showing SQL editor with example query
5

Choose Your Query Mode

Before executing your query, select the appropriate execution mode and output format:Execution Mode:
  • Standard Mode (default): Best for testing queries. Returns results in 30 seconds or times out.
  • Async Mode (toggle on): For large or complex queries that take longer than 30 seconds. Results are processed in the background.
Output Format:
  • Table View (ARRAY): Displays up to 1,000 rows in an interactive table
  • CSV Export: Generates a comma-separated values file for unlimited rows
  • Parquet Export: Creates a columnar format file optimized for analytics
Best Practice: Always test your query in Standard Mode with Table View first to validate your SQL syntax. Once confirmed, switch to Async Mode with CSV/Parquet for large datasets.
6

Execute the Query

Click the Run Query button (or press Ctrl+Enter) to execute your query.You’ll see a progress indicator while the query runs. You can:
  • Cancel the query at any time by clicking the Cancel button
  • Monitor the status in the results panel
7

View and Export Results

For Standard Mode (Table View):
  • Results appear in the interactive table below the query editor
  • Sort columns by clicking on column headers
  • Filter data using the column menu options
  • Export visible results using the export buttons
For Async Mode (CSV/Parquet):
  • The file will be prepared in the background
  • When ready, your browser will automatically download the file
  • Open the file in Excel, a text editor, or your preferred analytics tool
  • The results panel will also load a preview of the first 1,000 rows
SQL Viewer interface showing dataset browser, query editor, and results table
Success! You’ve successfully queried your dataset. Your results are now ready for analysis.

​
Query Execution Modes Explained

Standard Mode

When to use:
  • Writing and testing new queries
  • Working with smaller datasets
  • Need immediate feedback
Limitations:
  • 30-second timeout
  • Up to 1,000 rows in table view

Async Mode

When to use:
  • Large datasets with millions of rows
  • Complex queries with joins or aggregations
  • Exporting complete datasets
Behavior:
  • No timeout limit
  • Results processed in background
  • Automatic file download when ready

​
Best Practices

Do This

Action: Start with LIMIT 100 when testing queriesWhy: This helps you verify your query logic quickly without processing large amounts of data. Remove the limit once you’re confident in your query.

Not That

Action: Running queries without a LIMIT on large datasets in Standard ModeWhy: This can cause timeouts and slow performance. Always test with a limit first, then use Async Mode for full datasets.

Do This

Action: Use the Query Cookbook templates as starting pointsWhy: Templates are pre-tested and follow SQL best practices. They save time and help you learn effective query patterns.

Not That

Action: Writing complex queries from scratch without referenceWhy: You might miss optimizations or make syntax errors. Leverage the cookbook for proven patterns.

​
Common Issues & Solutions

Solution: This is expected behavior in Standard Mode for large or complex queries.Steps to resolve:
  1. Add a LIMIT clause to test your query with fewer rows
  2. Once your query works, toggle on Async Mode
  3. Select CSV or Parquet as your output format
  4. Run the query again - it will process in the background without timeout limits
Solution: This indicates a SQL syntax issue or incorrect column name.Steps to resolve:
  1. Check the schema panel on the left to verify correct column names
  2. Column names with spaces or special characters need double quotes: "Column Name"
  3. Use the Format button to clean up your SQL
  4. Try clicking column checkboxes to auto-generate proper column references
  5. Review the Query Cookbook for syntax examples
Solution: The table view is limited to 1,000 rows for browser performance.Steps to resolve:
  1. Change the output format to CSV or Parquet
  2. Toggle on Async Mode if you expect a large result set
  3. Run your query
  4. The complete file will be downloaded to your computer with all rows
Solution: Error badges indicate issues with the dataset.Badge meanings:
  • Red badge: Connection error or dataset not found - contact support
  • Yellow badge: Previous query error - try selecting the dataset again to clear the error
If the error persists after reselecting, contact your administrator.
Solution: Autocomplete requires certain conditions to activate.Requirements:
  1. The query editor must be focused (cursor blinking)
  2. You’ve typed at least 2 characters
  3. A dataset is selected (for column suggestions)
Manual trigger: Press Ctrl+Space to manually open autocomplete at any time.
Solution: Query history is tracked during your current session.To reuse queries:
  1. Click the History button in the query editor toolbar
  2. Select from your recent queries (up to 50 stored)
  3. The query will be loaded into the editor
Query history is stored in your browser and will be cleared when you refresh the page. For important queries, save them in a text file or document.

​
Advanced Tips

When working with time-series data, use these SQL patterns for better performance:Last 7 days:
WHERE observed_at >= now() - INTERVAL 7 DAY
Specific date range:
WHERE observed_at BETWEEN '2024-01-01' AND '2024-01-31'
Current month:
WHERE date_trunc('month', observed_at) = date_trunc('month', now())
For queries returning millions of rows:
  1. Use Parquet format instead of CSV for better compression and faster downloads
  2. Enable Async Mode to avoid timeout issues
  3. Add indexes to your WHERE clause - filter on timestamp or ID columns when possible
  4. Aggregate when possible - use GROUP BY to summarize data instead of exporting raw rows
When summarizing data, follow these patterns:Daily averages:
SELECT date_trunc('day', observed_at) AS day,
       AVG(value) AS daily_average
FROM MainTable
WHERE observed_at >= now() - INTERVAL 30 DAY
GROUP BY day
ORDER BY day
Top 10 by metric:
SELECT sensor_id,
       AVG(value) AS avg_value
FROM MainTable
WHERE observed_at >= now() - INTERVAL 7 DAY
GROUP BY sensor_id
ORDER BY avg_value DESC
LIMIT 10
The schema panel offers quick ways to build queries:
  • Click individual columns to add/remove them from your SELECT clause
  • Click “Select All” to add all columns at once
  • Columns with special characters are automatically quoted correctly
  • The query updates in real-time as you click checkboxes
This is the fastest way to build initial SELECT statements without typing.

​
Supported SQL Features

The SQL Viewer uses DuckDB SQL dialect, which supports:
  • Standard SQL operations: SELECT, WHERE, GROUP BY, ORDER BY, LIMIT
  • Aggregations: COUNT, SUM, AVG, MIN, MAX, percentiles
  • Time functions: Date arithmetic, intervals, time zones
  • Window functions: Running totals, moving averages, rankings
  • String operations: Pattern matching, concatenation, formatting
  • Math operations: Basic arithmetic, rounding, statistical functions
For complex SQL syntax questions, use the Query Cookbook’s examples or consult the DuckDB SQL documentation for advanced features.