SQL

A SQL node is used to execute SQL queries in your CloudFlow using the SQL node editor. You can type queries directly into the SQL node and perform actions based on the results. This is useful for introducing data-driven conditional logic into your flow, making it easier to make complex decisions based on your billing data stored in DoiT Cloud Intelligence. For example, you could write a SQL query that queries your AWS billing data. You can then filter on the output to identify your most expensive services and notify your stakeholders.

SQL query example

Below is an example of a SQL query that helps you perform cost optimization and governance by finding the 20 most expensive AWS resources that are currently missing cost allocation tags over the last seven days.

WITH recent AS (
  SELECT
    resource_id,
    service_description,
    SUM(cost) AS untagged_cost_7d
  FROM `aws_dci`
  WHERE usage_start_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
    AND (labels IS NULL OR ARRAY_LENGTH(labels) = 0)
  GROUP BY resource_id, service_description
)
SELECT
  resource_id,
  service_description,
  ROUND(untagged_cost_7d, 2) AS untagged_cost_7d
FROM recent
WHERE untagged_cost_7d > 5
ORDER BY untagged_cost_7d DESC
LIMIT 20;

Below is an example of a resulting table that provides a prioritized list of AWS resources that need immediate attention.

resource_id	service_description	untagged_cost_7d
i-0f23ab91c5d4ef123	Amazon Elastic Compute Cloud	142.37
vol-03bc1198a7e9d7812	Amazon Elastic Block Store	58.12
arn:aws:lambda:...func1	AWS Lambda	27.44
bucket-analytics-prod	Amazon S3	12.91

Configure a SQL node

To configure a SQL node, create and test your query, then view the expected output for the fields you can reference in subsequent nodes.

When you select Create query, the Create a SQL query window appears, where you can write and test your SQL query.

In the Query tab, write your SQL query. The SQL node uses an ANSI-compliant Structured Query Language (SQL). As you write, DoiT Cloud Intelligence validates your SQL query, instantly highlighting any syntax errors or other potential issues.

Select Run to test your query. The SQL node editor requires the query to execute successfully before you can create it, ensuring only validated queries are created.

When you select Run, the results are displayed in the Results tab, allowing you to validate the query's logic, efficiency, and data output. The output schema adheres to the DoiT billing data schema.

The Query Output Schema shows the expected output from the query. These are the fields that you can reference in subsequent nodes.