59 posts tagged with "stackql"

The StackQL Linode provider is now available. Using the StackQL Linode provider you can create, query, and manage Linodes (instances), Volumes, NodeBalancers, Firewalls, StackScripts, Databases, Kubernetes Clusters, Object Storage Buckets, and much more.

You can use the StackQL Linode provider with other StackQL providers (such as aws, google, azure, digitalocean, and more) to perform multi-cloud CSPM, inventory queries, or multi-provider stack deployments. Documentation for the Linode provider is available at StackQL Linode provider docs.

Here is an example of creating a Linode (a VM instance), passing variables from a jsonnet config file as well as CI secrets (GitHub Actions Secrets, GitLab CI Secrets, etc.):

INSERT INTO linode.instances.linodes(
  data__authorized_keys,
  data__authorized_users,
  data__root_pass,
  data__image,
  data__label,
  data__region,
  data__type
)
SELECT
  '[ "{{ .authorized_key }}" ]',
  '[ "{{ .authorized_user }}" ]',
  '{{ .root_pass }}',
  '{{ .image }}',
  '{{ .label }}',
  '{{ .region }}',
  '{{ .type }}'
;

Querying objects in Linode can be done using SELECT statements, such as:

select id, 
label, 
region, 
JSON_EXTRACT(specs, '$.vcpus') as vcpus,
JSON_EXTRACT(specs, '$.memory') as memory,
JSON_EXTRACT(specs, '$.disk') as disk,
status 
from linode.instances.linodes;

Which would return:

|----------|-----------|--------------|-------|--------|-------|---------|                                                                                                                                              
|    id    |   label   |    region    | vcpus | memory | disk  | status  |                                                                                                                                              
|----------|-----------|--------------|-------|--------|-------|---------|                                                                                                                                              
| 46063573 | my-linode | ap-southeast |     1 |   1024 | 25600 | running |                                                                                                                                              
|----------|-----------|--------------|-------|--------|-------|---------| 

Summary or aggregate queries such as GROUP BY -> COUNT or SUM are fully supported with StackQL, as are JOIN and UNION operations (including cross-provider JOIN operations).

StackQL supported outputs include table, csv (using a comma or user-specified delimiter), and json.

StackQL can be accessed through the interactive shell stackql shell as well as noninteractive access using stackql exec and server-based access using stackql srv - where you can use any Postgres wire protocol client to run StackQL queries. GitHub actions, Jupyter notebooks, and Superset dashboards are other options for using StackQL.

The Digital Ocean provider is now available for StackQL. You can use StackQL to provision, manage or report on Droplets, Apps, Functions, Databases, Volumes, Spaces, and more.

To use the Digital Ocean provider, generate a Personal Access Token from the Digital Ocean Control Panel under the API section. Export the value of the token created to a variable named DIGITALOCEAN_TOKEN (on your local system or as a CI secret). You can then run queries against the Digital Ocean provider using StackQL.

The following example demonstrates the creation of a Droplet in Digital Ocean.

INSERT INTO digitalocean.droplets.droplets ( 
data__name, 
data__region, 
data__size, 
data__image, 
data__backups, 
data__ipv6, 
data__monitoring, 
data__tags 
) 
SELECT 
'droplet-1.example.com', 
'nyc3', 
's-1vcpu-1gb', 
'ubuntu-20-04-x64', 
true, 
true, 
true, 
'["env:prod", "web"]';

You can use jsonnet as a configuration, templating language with StackQL to provide variables or parameters to IaC operations in StackQL; this can be done using the --data flag in the stackql exec command as follows:

./stackql exec --infile create_droplets.iql --iqldata vars.jsonnet

The code for create_droplets.iql and vars.jsonnet is shown here:

{{range $index, $element := .droplets}}
INSERT INTO digitalocean.droplets.droplets ( 
data__name, 
data__region, 
data__size, 
data__image, 
data__backups, 
data__ipv6, 
data__monitoring, 
data__tags 
) 
SELECT 
'droplet-{{$index}}.stackql.io', 
'nyc3', 
'{{.size}}', 
'ubuntu-20-04-x64', 
true, 
true, 
true, 
'["env:prod", "web"]';
{{end}}

{
    droplets: [
        { size: 's-1vcpu-512mb-10gb' },
        { size: 's-1vcpu-1gb' },
        { size: 's-1vcpu-1gb-amd' },
    ]
}

StackQL is a unified SQL-based framework that can be used for analytics and reporting as well as provisioning, de-provisioning, and lifecycle opertaions. As a native multi-cloud solution, StackQL can analyze and report across assets across multiple different providers; an example is shown here:

SELECT 
name, 
JSON_EXTRACT(region, '$.name') as region, 
JSON_EXTRACT(size, '$.slug') as size,
'digitalocean' as provider
FROM digitalocean.droplets.droplets
UNION
SELECT
instanceId as name,
'us-east-1' as region,
instanceType as size,
'aws' as provider
FROM aws.ec2.instances 
WHERE region = 'us-east-1';

Digital Ocean and multi-cloud queries can be visualized using BI tools or notebooks; examples of using StackQL with Jupyter can be found here.

More information about the Digital Ocean provider for StackQL can be found here.

For more background on using StackQL with GitHub Actions see StackQL GitHub Actions Tutorial

- name: setup StackQL
  uses: stackql/setup-stackql@v1.1.0
  with:
    use_wrapper: true

- name: get changed files
  env:
    STACKQL_GITHUB_USERNAME: ${{ secrets.STACKQL_GITHUB_USERNAME }}
    STACKQL_GITHUB_PASSWORD: ${{ secrets.STACKQL_GITHUB_PASSWORD }}
  shell: bash
  run: |
    ORG=$(echo "$GITHUB_REPOSITORY" | cut -d '/' -f1)
    REPO=$(echo "$GITHUB_REPOSITORY" | cut -d '/' -f2)
    QUERY="select filename FROM github.repos.commit_files where owner = '${ORG}' and ref = '${GITHUB_SHA}' and repo = '${REPO}'"
    echo "pulling github provider"
    stackql exec "REGISTRY PULL github"
    echo "running query: ${QUERY}"
    stackql --output json -f changed_files.txt exec "${QUERY}"

changed_files.txt looks like this...

[{"filename":"src/app.ts"},{"filename":"src/mod.ts"},...]

You could then do something with the changed files in a further step like:

- name: Do something with changed files
  run: |
    while IFS="" read -r filename || [ -n "$filename" ]
    do
      echo "processing ${filename}..."
      #
      # do something interesting here...
      #
    done < <(jq -r '.[] | .filename' changed_files.txt)

The github.repos.commit_files StackQL resource has other interesting fields which could be projected and used for actioning or reporting, these can be seen using:

DESCRIBE EXTENDED github.repos.commit_files;

Fields available in this resource include:

• status - one of added, removed, modified, renamed, copied, changed or unchanged
• filename - filename which has changed
• previous_filename - previous filename if the filename had changed in the commit
• additions - the number of additions in each file
• changes - the number of changes to each file
• deletions - the number of deletions in each file
• patch - git diff output for each file
• blob_url - the blob url for the file
• raw_url - the raw url for the file
• contents_url - the contents url for the file
• sha - The sha for each individual file

Read More​

Check out a full demo on using StackQL with GitHub Actions​

• stackql-actions-demo

Check the GitHub Repos​

• setup-stackql
• stackql-exec
• stackql-assert

We provide a set of useful actions that allow you use StackQL with GitHub Actions Workflow. These actions are available in the GitHub Marketplace.

setup-stackql​

This action installs the stackql CLI on Actions runners. It is used if you want to perform custom operations using StackQL queries.

Using the setup-stackql action allows you to perform StackQL CLI operations in the subsequent steps of a GitHub Actions job. The setup-stackql action installs the stackql CLI on the runner, making it available to all subsequent steps. This means you can use StackQL queries and commands directly in your GitHub Action Workflow Steps.

stackql-exec​

This action executes a StackQL query within an Actions workflow.

The stackql-exec action is a wrapper around setup-stackql that allows you to pass your StackQL query and authentication information as inputs. This makes it easy to execute StackQL queries directly within your GitHub Action Workflow Step.

stackql-assert​

This action is used to test assertions against the results of a StackQL query. You can use this action to validate the state of a resource after an IaC or lifecycle operation has been performed or to validate the system (e.g., CSPM or compliance queries).

The stackql-assert action is also a wrapper around setup-stackql that allows you to pass your StackQL query and authentication information as inputs. This makes it easy to test assertions against the results of your StackQL queries directly within your GitHub Action Workflow Step.

Use setup-stackql to run StackQL queries in your GitHub Workflow Job​

To run StackQL query with GitHub Actions, you need to install the stackql CLI on the runner. This can be done using the setup-stackql action. Once installed, the stackql CLI is available to all subsequent steps in the job via StackQL.

- name: setup StackQL
  uses: stackql/setup-stackql@v1.1.0
  with:
    use_wrapper: true

Run StackQL query​

After using the setup-stackql action, you can run the stackql CLI as a shell command in subsequent steps of the job. For example, you can run a dry run of a StackQL query as a shell command like this:

- name: Prep Google Creds (bash)
      if: ${{ matrix.os != 'windows-latest' }}
      run: | ## use the secret to create json file
        sudo echo ${{ secrets.GOOGLE_CREDS }} | base64 -d > sa-key.json

    - name: Use Google Provider
      run: | 
        stackql exec -i ./examples/google-example.iql --auth='{ "google": { "type": "service_account",  "credentialsfilepath": "sa-key.json" }}'

You can check more example here.

Use stackql-exec to run StackQL queries in a single step​

stackql-exec action provides a simpler and more streamlined way to executestackql CLI queries in your GitHub Workflow. You can create a step like this:

- name: stop running instances using stackql-exec
  uses: stackql/stackql-exec@v1.0.1
  with:
    auth_obj_path: './stackql/auth.json'
    query_file_path: './stackql/scripts/stop-instances.iql'

The auth_obj_path parameter specifies the path to the authentication object that contains the credentials required to authenticate with the data source. The query_file_path parameter specifies the path to the StackQL query file that you want to execute.

Once you have specified the authentication object and query file, you can run the StackQL query by executing the stackql-exec action in a single step in your GitHub Workflow.

You can check more examples here.

Validating resource state with stackql-assert​

stackql-assert is another useful action that StackQL provides, allowing you to test assertions against the results of a StackQL query.

This action is particularly helpful for validating the state of a resource after an infrastructure as code (IaC) or lifecycle operation has been performed.

You can run an assert step like this example:

- name: check terraform deployment using stackql-assert - should fail
  uses: stackql/stackql-assert@v1.0.2
  with:
    auth_obj_path: './stackql/auth.json'
    test_query_file_path: './stackql/scripts/check-terraform-instances.iql'
    expected_results_str: '[{"name":"terraform-test-1","name":"terraform-test-2"}]'

Authentication​

setup-stackql​

if you use basic token, you can setup authentication with the following example

- name: Use GitHub Provider
      run: |
        stackql exec -i ./examples/github-example.iql --auth='{ "github": { "type": "basic", "credentialsenvvar": "STACKQL_GITHUB_CREDS" } }'
      env: 
        STACKQL_GITHUB_CREDS: ${{  secrets.STACKQL_GITHUB_CREDS }}

Meanwhile if you are using a credential file, you can create the file via this example bash command

    - name: Prep Google Creds (bash)
      run: | ## use the secret to create json file
        sudo echo ${{ secrets.GOOGLE_CREDS }} | base64 -d > sa-key.json

    - name: Use Google Provider
      run: | 
        stackql exec "SELECT name, status
        FROM google.compute.instances 
        WHERE project = 'stackql-demo' AND zone = 'australia-southeast1-a';" --auth='{ "google": { "type": "service_account",  "credentialsfilepath": "sa-key.json" }}'

stackql-exec and stackql-assert​

For stackql-exec and stackql-assert, you can use the same authentication method as setup-stackql, except the authentication object is passed via the auth_obj_path or auth_strparameter

• auth_obj_path parameter to specify the path to the authentication object that contains the credentials required to authenticate with the data source.
• auth_str parameter to specify the authentication object as a string.

Example Authentication Object file (auth.json):​

{
  "github": {
    "type": "basic",
    "credentialsenvvar": "STACKQL_GITHUB_CREDS"
  }
}

Example Usage of auth_obj_path:​

  - name: Prep Google Creds (bash)
  if: ${{ matrix.os != 'windows-latest' }}
  shell: bash
  run: | ## use the base64 encoded secret to create json file
    sudo echo ${{ secrets.GOOGLE_CREDS }} | base64 -d > sa-key.json
    
  - name: stop running instances using stackql-exec
    uses: stackql/stackql-exec@v1.0.1
    with:
      auth_obj_path: './stackql/auth.json'
      query_file_path: './stackql/scripts/stop-instances.iql'

  - name: check if we have 4 instances using stackql-assert
    uses: stackql/stackql-assert@v1.0.2
    with:
      auth_obj_path: './stackql/auth.json'
      test_query_file_path: './stackql/scripts/check-instances.iql'
      expected_rows: 4

Example Usage of auth_str:​

  - name: Prep Google Creds (bash)
    if: ${{ matrix.os != 'windows-latest' }}
    shell: bash
    run: | ## use the base64 encoded secret to create json file
      sudo echo ${{ secrets.GOOGLE_CREDS }} | base64 -d > sa-key.json
      
  - name: exec github example
    uses: ./
    with:
      auth_str: '{ "github": { "type": "basic", "credentialsenvvar": "STACKQL_GITHUB_CREDS" } }'
      query: "REGISTRY PULL github v23.01.00104;
              SHOW PROVIDERS;
              select total_private_repos
              from github.orgs.orgs
              where org = 'stackql';"
    env: 
      STACKQL_GITHUB_CREDS: ${{ secrets.STACKQL_GITHUB_CREDS }}

  - name: Use test query string and expected rows, with auth string
    uses: ./
    with:
      auth_str: '{ "google": { "type": "service_account",  "credentialsfilepath": "sa-key.json" }}'
      test_query_file_path: './.github/workflows/workflow_scripts/google-example.iql'
      expected_rows: 1

Read More​

Check the full demo​

• stackql-actions-demo

Check the GitHub Repos​

• setup-stackql
• stackql-exec
• stackql-assert

pystackql is now available in PyPI, and documentation for the package is available at Read the Docs. pystackql can be used with pandas, matplotlib, plotly, jupyter and more to run queries and visualize results.

The latest version of stackql for any platform can be installed using:

pip install pystackql

here is a complete example...

from pystackql import StackQL
import pandas as pd
provider_auth =  {
    "aws": {
        "credentialsenvvar": "AWS_SECRET_ACCESS_KEY",
        "keyIDenvvar": "AWS_ACCESS_KEY_ID",
        "type": "aws_signing_v4"
    }
}
regions = ["ap-southeast-2", "us-east-1"]
stackql = StackQL(auth=provider_auth)

query = """
SELECT '%s' as region, instanceType, COUNT(*) as num_instances
FROM aws.ec2.instances
WHERE region = '%s'
GROUP BY instanceType
UNION
SELECT  '%s' as region, instanceType, COUNT(*) as num_instances
FROM aws.ec2.instances
WHERE region = '%s'
GROUP BY instanceType
""" % (regions[0], regions[0], regions[1], regions[1])

res = stackql.execute(query)
df = pd.read_json(res)
print(df)

  instanceType  num_instances          region
0    t2.medium              2  ap-southeast-2
1     t2.micro              7  ap-southeast-2
2     t2.small              4  ap-southeast-2
3     t2.micro              6       us-east-1

Source code for pystackql can be found at stackql/pystackql

Let us know what you think!