Skip to main content

5 posts tagged with "analytics"

View All Tags

Window Functions and CTEs Now Available in StackQL

· 2 min read
Jeffrey Aven
Technologist and Cloud Consultant

Window functions and Common Table Expressions (CTEs) are now generally available in StackQL. These features work with both the embedded SQLite backend and PostgreSQL backend.

Window Functions​

Window functions allow you to perform calculations across sets of rows related to the current row. Supported functions include:

  • Ranking: ROW_NUMBER(), RANK(), DENSE_RANK(), NTILE()
  • Offset: LAG(), LEAD(), FIRST_VALUE(), LAST_VALUE(), NTH_VALUE()
  • Distribution: PERCENT_RANK(), CUME_DIST()
  • Aggregates as window functions: SUM(), COUNT(), AVG(), etc. with OVER clause

Example: Ranking Contributors​

SELECT
    login,
    contributions,
    DENSE_RANK() OVER (ORDER BY contributions DESC) as rank
FROM github.repos.contributors
WHERE owner = 'stackql' AND repo = 'stackql';

Example: Running Totals​

SELECT
    login,
    contributions,
    SUM(contributions) OVER (ORDER BY contributions DESC) as running_total,
    ROUND(100.0 * contributions / SUM(contributions) OVER (), 2) as pct_of_total
FROM github.repos.contributors
WHERE owner = 'stackql' AND repo = 'stackql';

Common Table Expressions (CTEs)​

CTEs let you define temporary named result sets using the WITH clause. This simplifies complex queries by breaking them into logical components.

Example: Aggregating Across Multiple Resources​

WITH all_contributors AS (
    SELECT login, contributions
    FROM github.repos.contributors
    WHERE owner = 'stackql' AND repo = 'stackql'
    UNION ALL
    SELECT login, contributions
    FROM github.repos.contributors
    WHERE owner = 'stackql' AND repo = 'stackql-deploy'
)
SELECT
    DENSE_RANK() OVER (ORDER BY SUM(contributions) DESC) as rank,
    login,
    SUM(contributions) as total_contributions
FROM all_contributors
GROUP BY login
ORDER BY total_contributions DESC;

Documentation​

Full documentation is available:

Let us know your thoughts! Visit us and give us a star on GitHub.

Builtin Parallel Query Execution in StackQL

· 2 min read
Jeffrey Aven
Technologist and Cloud Consultant
info

stackql is a dev tool that allows you to query and manage cloud and SaaS resources using SQL, which developers and analysts can use for CSPM, assurance, user access management reporting, IaC, XOps and more.

An exciting new feature to cap off 2023! Parallel query execution in StackQL. With the latest release of StackQL, parameters in WHERE IN clauses are fetched asyncronously.

This query for example, queries lambda functions across 17 AWS regions in under 1.5 sec, technically these are 17 parallel queries to 17 different endpoints.

SELECT region, function_name
FROM aws.lambda.functions
WHERE region IN ( 'us-east-1','us-east-2','us-west-1','us-west-2','ap-south-1','ap-northeast-3','ap-northeast-2','ap-southeast-1','ap-southeast-2','ap-northeast-1','ca-central-1','eu-central-1','eu-west-1','eu-west-2','eu-west-3','eu-north-1','sa-east-1'
)

You could do something similar for other hyperscalars, for example querying resources across projects in GCP asynchronously, or querying across resource groups in Azure asynchronously.

This capability was previously available using the pystackql package, as discussed in the Query Resources Across AWS Regions Asynchronously blog post, but is now available natively in the StackQL query optimizer.

You just need to add --execution.concurrency.limit=-1 to your stackql exec or stackql shell commands or when starting a StackQL Server using stackql srv. More query optimizations coming! Happy New Year! 🎉 🎉 🎉

Let us know your thoughts! Visit us and give us a ⭐ on GitHub

Introducing Materialized Views with StackQL

· 2 min read
Kieran Rimmer
Technologist and Cloud Consultant

Materialized Views are now available in StackQL. Materialized Views can be used to improve performance for dependent or repetetive queries within StackQL provisioning or analytics routines.

Refresher on Materialized Views​

Unlike standard views that provide a virtual representation of data, a Materialized View physically stores the result set of a query. This implies that the data is pre-computed and stored, which can lead to performance gains as the data doesn't need to be fetched from the underlying resource(s) every time it is queried.

Benefits of Materialized Views in StackQL​

  1. Performance Boost: With data already stored and readily available, Materialized Views can substantially reduce StackQL query execution time, especially for complex and frequently-run queries.

  2. Data Consistency: Since Materialized Views provide a snapshot of the data at a specific point in time, it ensures consistent data is returned every time it is accessed until it is refreshed.

  3. Flexibility: You have the flexibility to refresh the Materialized View as needed usign the REFRESH MATERIALIZED VIEW lifecycle operation in StackQL. This is particularly useful when working with rapidly changing data.

Using Materialized Views in StackQL​

Here's a step-by-step guide on how you to use this new feature in StackQL:

  1. Create a Materialized View:
CREATE MATERIALIZED VIEW vw_ec2_instance_types AS 
SELECT 
 memoryInfo, 
 hypervisor, 
 autoRecoverySupported, 
 instanceType, 
 SPLIT_PART(processorInfo, '\n', 3) as processorArch, 
 currentGeneration, 
 freeTierEligible, 
 hibernationSupported,
 SPLIT_PART(vCpuInfo, '\n', 2) as vCPUs, 
 bareMetal, 
 burstablePerformanceSupported, 
 dedicatedHostsSupported 
FROM aws.ec2.instance_types 
WHERE region = 'us-east-1';
  1. Refresh the Materialized View:
REFRESH MATERIALIZED VIEW vw_ec2_instance_types;
  1. Use the Materialized View in a StackQL Query:
SELECT 
 i.instanceId, 
 i.instanceType, 
 it.vCPUs, 
 it.memoryInfo 
FROM aws.ec2.instances i 
 INNER JOIN vw_ec2_instance_types it 
 ON i.instanceType = it.instanceType 
WHERE i.region = 'us-east-1';
  1. Drop the Materialized View:
DROP MATERIALIZED VIEW vw_ec2_instance_types;

More information on Materialized Views in StackQL can be found here.

Introducing Table Valued Functions in StackQL

· 2 min read
Kieran Rimmer
Technologist and Cloud Consultant

Many provider query responses include columns which are arrays, the iam policy related resources in google are a classic example of this. for example, this query:

select * 
from google.cloudresourcemanager.projects_iam_policies 
where projectsId = 'stackql';

produces..

|-----------|--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|--------------|                               
| condition | members                                                                                                                                                                                    | role         |                               
|-----------|--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|--------------|                               
| null      | ["serviceAccount:1234567890-compute@developer.gserviceaccount.com","serviceAccount:1234567890@cloudservices.gserviceaccount.com","serviceAccount:stackql@appspot.gserviceaccount.com"] | roles/editor |                               
|-----------|--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|--------------|                               
| null      | ["serviceAccount:1234567890-compute@developer.gserviceaccount.com","serviceAccount:1234567890@cloudservices.gserviceaccount.com","serviceAccount:stackql@appspot.gserviceaccount.com"] | roles/editor |                               
|-----------|--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|--------------|                               
| null      | ["serviceAccount:1234567890-compute@developer.gserviceaccount.com","serviceAccount:1234567890@cloudservices.gserviceaccount.com","serviceAccount:stackql@appspot.gserviceaccount.com"] | roles/editor |                               
|-----------|--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|--------------|

What you want to do is unnest each member in members for each role binding (and condition if applicable)

Enter the table valued function json_each.

The json_each function accepts a field (optionally with a json path expression) and returns a table object with fields that can be projected in your result set, for example (querying the same underlying resource as above), this...

select 
iam.role, 
SPLIT_PART(json_each.value, ':', 1) as member_type,
SPLIT_PART(json_each.value, ':', 2) as member 
from google.cloudresourcemanager.projects_iam_policies iam, json_each(members) 
where projectsId = 'stackql';

now provides something much more useful from an analytic perspective:

|--------------------------------------|----------------|-----------------------------------------------------------------------------|
|                 role                 |  member_type   |                                   member                                    |
|--------------------------------------|----------------|-----------------------------------------------------------------------------|
| roles/appengine.serviceAgent         | serviceAccount | service-1234567890@gcp-gae-service.iam.gserviceaccount.com                |
|--------------------------------------|----------------|-----------------------------------------------------------------------------|
| roles/cloudbuild.builds.builder      | serviceAccount | 1234567890@cloudbuild.gserviceaccount.com                                 |
|--------------------------------------|----------------|-----------------------------------------------------------------------------|
| roles/cloudbuild.serviceAgent        | serviceAccount | service-1234567890@gcp-sa-cloudbuild.iam.gserviceaccount.com              |
|--------------------------------------|----------------|-----------------------------------------------------------------------------|
| roles/compute.serviceAgent           | serviceAccount | service-1234567890@compute-system.iam.gserviceaccount.com                 |
|--------------------------------------|----------------|-----------------------------------------------------------------------------|
| roles/container.serviceAgent         | serviceAccount | service-1234567890@container-engine-robot.iam.gserviceaccount.com         |
|--------------------------------------|----------------|-----------------------------------------------------------------------------|
| roles/containerregistry.ServiceAgent | serviceAccount | service-1234567890@containerregistry.iam.gserviceaccount.com              |
|--------------------------------------|----------------|-----------------------------------------------------------------------------|                                                                                    
| roles/dataflow.serviceAgent          | serviceAccount | service-1234567890@dataflow-service-producer-prod.iam.gserviceaccount.com |                                                                                    
|--------------------------------------|----------------|-----------------------------------------------------------------------------|                                                                                    
| roles/dataproc.serviceAgent          | serviceAccount | service-1234567890@dataproc-accounts.iam.gserviceaccount.com              |                                                                                    
|--------------------------------------|----------------|-----------------------------------------------------------------------------|                                                                                    
| roles/editor                         | serviceAccount | 1234567890-compute@developer.gserviceaccount.com                          |                                                                                    
|--------------------------------------|----------------|-----------------------------------------------------------------------------|                                                                                    
| roles/editor                         | serviceAccount | 1234567890@cloudservices.gserviceaccount.com                              |                                                                                    
|--------------------------------------|----------------|-----------------------------------------------------------------------------|                                                                                    
| roles/editor                         | serviceAccount | stackql@appspot.gserviceaccount.com                                         |                                                                                    
|--------------------------------------|----------------|-----------------------------------------------------------------------------|                                                                                    
| roles/firebaserules.system           | serviceAccount | service-1234567890@firebase-rules.iam.gserviceaccount.com                 |                                                                                    
|--------------------------------------|----------------|-----------------------------------------------------------------------------|                                                                                    
| roles/firestore.serviceAgent         | serviceAccount | service-1234567890@gcp-sa-firestore.iam.gserviceaccount.com               |                                                                                    
|--------------------------------------|----------------|-----------------------------------------------------------------------------|                                                                                    
| roles/owner                          | serviceAccount | stackql-provisioner@stackql.iam.gserviceaccount.com                         |                                                                                    
|--------------------------------------|----------------|-----------------------------------------------------------------------------|                                                                                    
| roles/owner                          | serviceAccount | t1-804@stackql.iam.gserviceaccount.com                                      |                                                                                    
|--------------------------------------|----------------|-----------------------------------------------------------------------------|                                                                                    
| roles/owner                          | user           | javen@stackql.io                                                            |                                                                                    
|--------------------------------------|----------------|-----------------------------------------------------------------------------|                                                                                    
| roles/owner                          | user           | krimmer@stackql.io                                                          |                                                                                    
|--------------------------------------|----------------|-----------------------------------------------------------------------------|                                                                                    
| roles/pubsub.serviceAgent            | serviceAccount | service-1234567890@gcp-sa-pubsub.iam.gserviceaccount.com                  |                                                                                    
|--------------------------------------|----------------|-----------------------------------------------------------------------------|                                                                                    
| roles/viewer                         | serviceAccount | testing-admin@stackql.iam.gserviceaccount.com                               |                                                                                    
|--------------------------------------|----------------|-----------------------------------------------------------------------------|

__json_each__is available from version 0.5.418 or stackql onwards, this function can also be used in StackQL GitHub Actions such as stackql-exec or stackql-assert and in Python and Pandas using pystackql.

Support for Outer Joins Released for StackQL

· 3 min read
Kieran Rimmer
Technologist and Cloud Consultant
info

stackql is a dev tool that allows you to query, manage, and perform analytics against cloud and SaaS resources in real time using SQL, which developers and analysts can use for CSPM, assurance, user access management reporting, IaC, XOps and more.

We are pleased to announce the addition of support for OUTER JOIN operations in StackQL queries. This is a significant addition to the language, and we are excited to see what our users will do with it!

info

An OUTER JOIN is a type of JOIN operation that returns all records from one table (or StackQL resource) and only those records from a second table or resource where the joined fields are equal (i.e. the JOIN condition is met). If there is no match, the missing side of the JOIN is filled with NULL values.

OUTER JOIN operations are important because they allow you to combine data from two or more resources (within a StackQL provider or across StackQL providers), even when there is no match between the two resources. This is a common scenario when performing analytics and reporting on user access management (for example between an IdP (like Okta) and a resource provider like AWS or Google).

Using OUTER JOIN operations in StackQL​

If you wanted to find all users in your AWS account that have not logged in to their account in the last 20 days, and compare that to the same information for users in your Google Workspace account, you could use an OUTER JOIN operation to do this. The following query:

select 
   aws_users.UserName as aws_user_name
  ,aws_users.PasswordLastUsed as aws_last_Login_time
  ,CASE 
    WHEN aws_users.PasswordLastUsed = '' then 'false' 
    WHEN ( strftime('%Y-%m-%d %H:%M:%SZ', aws_users.PasswordLastUsed) > ( datetime('now', '-20 days' ) ) ) then 'true' 
    else 'false' end as aws_is_active
  ,json_extract(google_users.name, '$.fullName') as google_user_name
  ,google_users.lastLoginTime as google_last_Login_time 
  ,CASE 
    WHEN google_users.lastLoginTime is null then 'false' 
    WHEN google_users.lastLoginTime = '' then 'false' 
    WHEN ( strftime('%Y-%m-%d %H:%M:%SZ', google_users.lastLoginTime) > ( datetime('now', '-20 days' ) ) ) then 'true' 
    else 'false' end as google_is_active
from 
  aws.iam.users aws_users 
  LEFT OUTER JOIN 
  googleadmin.directory.users google_users 
  ON lower(substr(aws_users.UserName, 1, 5)) = lower(substr(json_extract(google_users.name, '$.fullName'), 1, 5)) 
WHERE aws_users.region = 'us-east-1' AND google_users.domain = 'stackql.io'
;

would produce a result like this:

|------------------------|----------------------|---------------|------------------|--------------------------|------------------|
|     aws_user_name      | aws_last_Login_time  | aws_is_active | google_user_name |  google_last_Login_time  | google_is_active |
|------------------------|----------------------|---------------|------------------|--------------------------|------------------|
| demo-stackql-cicd-user | null                 | false         | null             | null                     | false            |
|------------------------|----------------------|---------------|------------------|--------------------------|------------------|
| github_actions         | null                 | false         | null             | null                     | false            |
|------------------------|----------------------|---------------|------------------|--------------------------|------------------|
| jeffrey.aven           | 2023-06-30T04:29:14Z | true          | null             | null                     | false            |
|------------------------|----------------------|---------------|------------------|--------------------------|------------------|
| kieran.rimmer          | 2023-06-03T08:40:49Z | false         | Kieran Rimmer    | 2023-06-23T06:01:46.000Z | true             |
|------------------------|----------------------|---------------|------------------|--------------------------|------------------|
| ...                    | ...                  | ...           | ...              | ...                      | ...              |
|------------------------|----------------------|---------------|------------------|--------------------------|------------------|

Currently only LEFT OUTER JOIN sppport is available, but we will be adding support for RIGHT OUTER JOIN and FULL OUTER JOIN in the near future. Stay tuned!