SQL Console

Using SQL Console to run advanced queries

You can run SQL queries against the synced data using SQL Console. The SQL Console provides access to the data from the latest sync in the underlying ClickHouse database.

In the left sidebar, you see Asset Inventory Tables, which are tables normalized across all cloud resources synced. Additionally, Integration Tables includes all the tables synced from the individual integrations. Expand the tables to see their schema.

SQL Console with an executed query.

Executing a query

Queries use ClickHouse SQL syntax. Similar to Searches, Queries can be saved, shared and used via the API.

SQL Console allows executing only read-only queries. You cannot modify the data or add tables or views.

For example, here is a query that finds all unassigned EC2 images, by joining the aws_ec2_imagestable with the aws_ec2_instancestable:

SELECT 
    img.image_id,
    inst.image_id as ec2_image, -- should all be NULL to show unassigned
    img.name,
    img.creation_date,
    img.state,
    img.tags
FROM 
    aws_ec2_images img
LEFT JOIN 
    aws_ec2_instances inst ON inst.image_id = img.image_id
WHERE 
    inst.image_id IS NULL -- AMIs not associated with any EC2 instance
    AND img.state = 'available'
    AND img.creation_date <= NOW() - INTERVAL '30 days'
ORDER BY 
    img.creation_date ASC;

Press the Run Query button (or Ctrl/Command + Enter shortcut) to run the query. The results will be displayed in the bottom part of the screen.

SQL Query Results

If a query returns more than 100 rows, the results will be automatically paged.

To copy a value from a table cell or to see the details, click the cell and a sidebar with "Cell Inspection" will open. This allows exploring and getting the values from more complex JSON objects in the query results.

Cell Inspection view with a complex JSON object

Understanding the data structures

The data integrations sync comes directly from the API endpoints the integrations are connecting to and the Integrations Tables are not transformed in any way. This makes it easy to go to the API endpoint documentation to learn what the data looks like and how it relates to other API endpoints (and eventually tables in CloudQuery Platform).

To find what API endpoint a specific table syncs from, find the table in CloudQuery Hub. For example, here is the documentation for AWS EC2 Instances table.

For additional hints or ideas on how to use the synced data, see the Tutorials on CloudQuery Blog.

See this in action

CloudQuery’s SQL Console gives you unlimited power to query your cloud inventory in real-time. But where do you start? We have compiled a collection of practical examples showcasing how you can leverage CloudQuery for security, compliance, and cost optimization.

➡️ Explore real-world query examples to see CloudQuery in action and unlock the full potential of your cloud data.