> ## Documentation Index
> Fetch the complete documentation index at: https://docs.sensorup.com/llms.txt
> Use this file to discover all available pages before exploring further.
# How to Query Datasets with SQL Viewer
> Learn how to explore, query, and export data from your datasets using the SQL Viewer's interactive interface
## 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 datasets
**Estimated Time:** 10-15 minutes
**Difficulty 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
## Step-by-Step Instructions
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.
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
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.
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:**
```sql Example: Previous Quarter Closed Emissions theme={null}
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:**
```sql Example: Pending Emissions Pending Resolution theme={null}
SELECT *
FROM MainTable
WHERE status IN ('OPEN', 'IN_PROGRESS')
ORDER BY startTime DESC
LIMIT 100
```
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.
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
**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
**Success!** You've successfully queried your dataset. Your results are now ready for analysis.
## Query Execution Modes Explained
**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
**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
**Action:** Start with `LIMIT 100` when testing queries
**Why:** This helps you verify your query logic quickly without processing large amounts of data. Remove the limit once you're confident in your query.
**Action:** Running queries without a LIMIT on large datasets in Standard Mode
**Why:** This can cause timeouts and slow performance. Always test with a limit first, then use Async Mode for full datasets.
**Action:** Use the Query Cookbook templates as starting points
**Why:** Templates are pre-tested and follow SQL best practices. They save time and help you learn effective query patterns.
**Action:** Writing complex queries from scratch without reference
**Why:** 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:**
```sql theme={null}
WHERE observed_at >= now() - INTERVAL 7 DAY
```
**Specific date range:**
```sql theme={null}
WHERE observed_at BETWEEN '2024-01-01' AND '2024-01-31'
```
**Current month:**
```sql theme={null}
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:**
```sql theme={null}
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:**
```sql theme={null}
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.