Skip to content

Latest commit

 

History

History
122 lines (93 loc) · 6.63 KB

sql_usage.md

File metadata and controls

122 lines (93 loc) · 6.63 KB

StackQL SQL Usage

Contents
  1. Basic SELECT operations
  2. Built in functions
  3. String/JSON functions
  4. Date functions
  5. Summary/Aggregation operations
  6. UNION and JOIN operations

StackQL implements a SQL language variant for querying cloud resources. The language is ANSI SQL with extensions. Complete documentation for the language can be found in the Language Specification. A summary of salient features is provided below.

Basic SELECT operations

Simple SELECT queries can be run against a resource (provided the necessary authentication was provided). Column projection, as well as SELECT * are supported. For example, to list all instances in a given AWS region showing the instanceId and instanceType fields, run the following:

SELECT instanceId, instanceType 
FROM 
aws.ec2.instances 
WHERE region = 'us-east-1';

To return the id, name and machineType of all instances in a given GCP project and zone, run the following:

SELECT id, 
name, 
machineType 
FROM 
google.compute.instances 
WHERE project = 'stackql-demo' 
AND zone = 'australia-southeast1-a';

StackQL keywords such as SELECT, FROM etc, are not case sensitive - they are often capitalized by convention. However, object names and field names are case-sensitive.

Built-in functions

Most common scalar functions you would expect in a SQL language are supported with StackQL, including string, date, math, regular expression and json scalar functions. More information on functions, their usage, and examples see the StackQL docs in the Functions section of the Language Specification.

String/JSON functions

In many API responses, values are nested in JSON objects. StackQL provides a JSON_EXTRACT function to extract values from JSON objects. In addition, some resource fields are returned as urls or self-links. StackQL provides a SPLIT_PART function to extract parts of a string and just provide the meaningful value. This query demonstrates the use of both the JSON_EXTRACT and SPLIT_PART functions:

The id field contains the string /subscriptions/631d1c6d-2a65-43e7-93c2-688bfe4e1468/resourceGroups/stackql-ops-cicd-dev-01/providers/Microsoft.Compute/virtualMachines/test and the properties field contains an object with a hardwareProfile field which in turn contains a vmSize field. The SPLIT_PART function is used to extract the subscription id and the resource group name from the id field. The JSON_EXTRACT function is used to extract the vmSize value from the properties field.

SELECT name,  
 split_part(id, '/', 3) as subscription,
 split_part(id, '/', 5) as resource_group,
 json_extract(properties, '$.hardwareProfile.vmSize') as vm_size
FROM azure.compute.virtual_machines 
 WHERE resourceGroupName = 'stackql-ops-cicd-dev-01' 
 AND subscriptionId = '631d1c6d-2a65-43e7-93c2-688bfe4e1468';

Date functions

StackQL implements several date/time functions including DATE, DATETIME, JULIANDAY, STRFIME and TIME. The following query demonstrates the use of the JULIANDAY along with the ROUND mathematical function to calculate the number of days since a bucket was created in GCP:

SELECT name, timeCreated,
round(julianday('now')-julianday(timeCreated)) as days_since
FROM google.storage.buckets WHERE project = 'stackql';

Summary/Aggregation operations

StackQL supports the standard SQL GROUP BY and HAVING clauses for summary and aggregation operations. Standard summary functions such as COUNT, SUM, AVG, and extrema function such as MIN and MAX are supported.

The following query demonstrates the use of the GROUP BY clause to return the number of instance types for instances in an AWS region:

SELECT instanceType, COUNT(*) as num_instances 
FROM aws.ec2.instances 
WHERE region = 'us-east-1' 
GROUP BY instanceType;

The following example demonstrates the use of the HAVING clause to return the number of instances in a given GCP project and zone grouped by instance type and status, where the number of instances is greater than 2:

SELECT SUBSTR(machineType,103) as machineType, status, COUNT(*) as num_instances
FROM google.compute.instances 
WHERE project = 'stackql-demo' AND zone = 'australia-southeast1-a'
GROUP BY machineType, status
HAVING COUNT(*) > 2;

UNION and JOIN operations

Standard relational algebra operations such as UNION and JOIN are supported. The following query demonstrates the use of the UNION operation to return the number of instances across multiple AWS regions:

🚀 UNION and JOIN operations are fully supported across providers!

SELECT 'us-east-1' as region, COUNT(*) as num_instances
FROM aws.ec2.instances
WHERE region = 'us-east-1'
UNION
SELECT 'us-west-2' as region, COUNT(*) as num_instances
FROM aws.ec2.instances
WHERE region = 'us-west-1';

JOIN operations, including complex JOIN operations spanning multiple resources (tables) are supported. The following query demonstrates a JOIN:

select n.id, n.name, n.IPv4Range, s.name as subnetwork_name
from google.compute.networks n 
inner join google.compute.subnetworks s on n.name =  split_part(s.network, '/', 10) 
where n.project = 'stackql-demo'
and s.project = 'stackql-demo'
and s.region = 'australia-southeast1';