33 posts tagged with "analytics"

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:

• Window Functions
• WITH (CTEs)
• SELECT

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

Latest release or the datadog provider for StackQL is now available, enabling SQL-based querying and management of Datadog cloud resources. The new datadog provider includes:

• coverage for the latest Datadog features
• simplified service organization
• enhanced user documentation

For more see the Datadog Provider User Docs.

⭐ us on GitHub and join our community!

Latest release or the Linode provider for StackQL is available, enabling SQL-based querying and management of Linode cloud resources.

Provider Overview​

The Linode provider offers comprehensive access to 20 services covering the entire Linode platform, with 136 resources and 425 methods, allowing you to extract data and insights from your Linode environment using familiar SQL syntax.

Key Services​

The provider includes access to:

• Compute: Manage Linode instances and configurations
• Storage: Control volumes and object storage
• Networking: Handle IPs, VLANs, VPCs, and firewall rules
• Kubernetes: Administer LKE clusters and node pools
• Databases: Manage Linode's database service
• Account & Billing: Access account information and billing details

Example Queries​

Get Account Information​

SELECT 
  company,
  country,
  balance,
  balance_uninvoiced,
  active_since
FROM linode.account.account;

List Database Engines​

SELECT
  id,
  engine,
  version
FROM linode.databases.engines;

View Regional Availability​

SELECT
  available,
  plan,
  region
FROM linode.regions.availability;

List Linode Instances​

SELECT
  id,
  label,
  region,
  status,
  type,
  ipv4,
  tags
FROM linode.linode.instances;

Getting Started​

To use the Linode provider, simply register it and set your authentication:

-- Pull the provider
registry pull linode;

-- Set your authentication
SET linode.global.auth.credentialsenvvar = 'LINODE_TOKEN';

⭐ us on GitHub and join our community!

The new deno provider for StackQL is now available, enabling developers to query and manage Deno Deploy resources using familiar SQL syntax. This provider allows you to interact with Deno Deploy's control plane API, giving you complete control over your organizations, projects, deployments, domains, and KV databases through a consistent SQL interface.

The Deno Deploy provider offers comprehensive coverage of the Deno Deploy API, organized into logical services that mirror Deno's resource structure:

With the deno provider for StackQL, you can now include Deno Deploy in your infrastructure-as-code workflows, analytics dashboards, and cross-cloud orchestration processes using the same SQL syntax you use for other cloud providers.

For more information on the Deno Deploy provider for StackQL, see the Deno Deploy Provider Docs which provides detailed documentation for every resource and method, along with ready-to-use SQL examples.

Getting Started​

To start using the deno provider, simply pull it from the StackQL registry:

registry pull deno;

You'll need to set up your authentication by exporting your Deno API token:

export DENO_DEPLOY_TOKEN="your_deno_api_token"

Then you can begin querying your Deno Deploy resources:

-- List all projects in an organization
SELECT 
  id,
  name,
  description,
  createdAt,
  updatedAt
FROM deno.project.projects
WHERE organizationId = 'your-org-id';

|-----------------------------|-------------|--------------------------------------|----------------------|-----------------------------|
|          createdAt          | description |                  id                  |         name         |          updatedAt          |
|-----------------------------|-------------|--------------------------------------|----------------------|-----------------------------|
| 2022-12-13T04:20:13.379555Z |             | 34f9440f-3275-40e2-b327-2ea65269feb5 | stackql-dev-registry | 2025-09-06T00:47:04.579279Z |
|-----------------------------|-------------|--------------------------------------|----------------------|-----------------------------|
| 2022-12-13T04:16:22.054548Z |             | 616de983-3e37-4f44-a99f-b6bb5c59e80a | stackql-registry     | 2025-09-06T01:22:46.872203Z |
|-----------------------------|-------------|--------------------------------------|----------------------|-----------------------------|

-- List deployments with key information
SELECT
  id,
  description,
  status,
  JSON_EXTRACT(domains, '$[0]') as domain,
  JSON_EXTRACT("databases", '$.default') as db_id,
  createdAt,
  updatedAt
FROM deno.deployment.deployments
WHERE projectId = 'your-project-id'
AND "order" = 'desc';

|--------------|--------------------------------|---------|----------------------------------------|--------------------------------------|-----------------------------|-----------------------------|
|      id      |          description           | status  |                 domain                 |                db_id                 |          createdAt          |          updatedAt          |
|--------------|--------------------------------|---------|----------------------------------------|--------------------------------------|-----------------------------|-----------------------------|
| pe60nqshxq3g | Latest deployment              | success | example.com                            | 8971aec3-f72f-44c4-bc9f-0b2a513bd9fc | 2025-09-06T01:22:41.740151Z | 2025-09-06T01:22:49.067315Z |
|--------------|--------------------------------|---------|----------------------------------------|--------------------------------------|-----------------------------|-----------------------------|

⭐ us on GitHub and join our community!

The latest digitalocean provider for StackQL is available now (v25.09.00345), featuring a comprehensive reorganization of services and resources that better aligns with DigitalOcean's API structure. This update improves discoverability, logical grouping, and overall usability when working with DigitalOcean resources through StackQL, while adding exciting new capabilities like support for Gradient AI Platform through the genai service.

What's Changed​

The updated DigitalOcean provider features a more granular and logical organization of services that mirrors DigitalOcean's API architecture more closely. Here is a summary of the services included in the latest digitalocean provider:

Enhanced Documentation​

The new DigitalOcean Provider Docs provide comprehensive documentation on how to use the new digitalocean provider including ready-to-use SQL examples for each resource and method. A standout feature is the copy-paste functionality for all SQL queries, making it incredibly easy to:

1. Compose Infrastructure-as-Code workflows: Each method documentation includes working SQL examples that can be directly copied into your deployment scripts or CI/CD pipelines. Simply click the copy button next to any example to get production-ready SQL code.

2. Build analytics dashboards: Create sophisticated cross-service queries by combining examples from different resources. The documentation's consistent query formatting makes it simple to join related data across multiple DigitalOcean services.

3. Develop governance reports: Copy baseline queries and customize them for your specific compliance needs. The pre-formatted SQL provides the perfect starting point for custom reporting.

Gradient AI Platform Support​

A standout addition to this release is comprehensive support for DigitalOcean's Gradient AI Platform through the new genai service. This enables developers to:

• Deploy and manage AI models using familiar SQL syntax
• Monitor AI workloads alongside other cloud resources
• Incorporate AI capabilities into infrastructure-as-code workflows
• Create cross-service orchestrations that leverage AI capabilities

Getting Started​

To start using the updated digitalocean provider, simply pull the latest version from stackql shell or stackql registry command:

registry pull digitalocean;

Then you can begin querying your DigitalOcean resources with SQL:

-- List all droplets
SELECT
  id,
  name,
  status,
  size_slug,
  JSON_EXTRACT(size, '$.vcpus') as vcpus,
  JSON_EXTRACT(size, '$.memory') as memory,
  JSON_EXTRACT(size, '$.disk') as disk_size_gb,
  JSON_EXTRACT(size, '$.price_hourly') as price_hourly,
  JSON_EXTRACT(size, '$.price_monthly') as price_monthly
FROM digitalocean.compute.droplets;

|-----------|---------------------------------------|--------|------------------------|-------|--------|--------------|--------------|---------------|
|    id     |                 name                  | status |       size_slug        | vcpus | memory | disk_size_gb | price_hourly | price_monthly |
|-----------|---------------------------------------|--------|------------------------|-------|--------|--------------|--------------|---------------|
| 457265395 | ubuntu-s-1vcpu-1gb-35gb-intel-syd1-01 | active | s-1vcpu-1gb-35gb-intel |     1 |   1024 |           35 |       0.0119 |             8 |
|-----------|---------------------------------------|--------|------------------------|-------|--------|--------------|--------------|---------------|
| 510398669 | pool-urzofvwy7-l5vvz                  | active | s-2vcpu-4gb            |     2 |   4096 |           80 |      0.03571 |            24 |
|-----------|---------------------------------------|--------|------------------------|-------|--------|--------------|--------------|---------------|

-- List all Kubernetes clusters
SELECT 
  id,
  name,
  cluster_subnet,
  region,
  JSON_EXTRACT(status, '$.state') as state
FROM 
  digitalocean.kubernetes.clusters;

|--------------------------------------|------------------------------------|----------------|--------|---------|
|                  id                  |                name                | cluster_subnet | region |  state  |
|--------------------------------------|------------------------------------|----------------|--------|---------|
| 52d8fa14-eca0-46c8-80a8-8aae70a2c82a | k8s-1-33-1-do-2-syd1-1753698369839 | 10.150.0.0/16  | syd1   | running |
|--------------------------------------|------------------------------------|----------------|--------|---------|

Using the App Platform Functions​

The apps services provide access to DigitalOcean's modern deployment platforms. Here's an example of how to list your deployed applications:

-- List all deployed applications
SELECT
  id,
  default_ingress,
  active_deployment,
  created_at,
  updated_at,
  region,
  tier_slug
FROM
  digitalocean.apps.apps;

Using the GenAI Service​

The new genai service provides access to DigitalOcean's Gradient AI Platform capabilities. Here's an example of how to list deployed agents:

-- List all deployed AI agents
SELECT
  name,
  uuid,
  url,
  project_id,
  created_at,
  updated_at,
  region,
  temperature
FROM
  digitalocean.genai.agents;

Use Cases for the DigitalOcean Provider​

1. Infrastructure as Code: Manage your DigitalOcean resources alongside other cloud providers in a unified IaC approach, see stackql-deploy.

2. Cost Optimization: Identify unused resources and opportunities for cost savings with queries that expose pricing information across all resource types.

3. Security and Compliance: Audit firewall rules, network configurations, and access patterns to ensure compliance with security policies.

4. Performance Monitoring: Track resource utilization, database performance, and identify optimization opportunities.

5. Cross-Provider Orchestration: Build workflows that span DigitalOcean and other resource providers, enabling sophisticated data and infrastructure pipelines.

6. AI-Enhanced Infrastructure: Leverage the new GenAI service to incorporate AI capabilities into your infrastructure management workflows.

7. Automated Reporting: Create automated reports on DigitalOcean usage, performance, and costs.

Provider Metrics​

The latest DigitalOcean provider includes:

• 15 services
• 192 resources
• 516 total methods
• 254 selectable methods

This comprehensive coverage ensures you can manage and query virtually all aspects of your DigitalOcean infrastructure using familiar SQL syntax.

⭐ us on GitHub and join our community!