2 posts tagged with "github"

GitHub Codespaces is a development environment completely hosted online, enabling seamless development without setting up local machines. One of the great features of Codespaces is its compatibility with IPython, providing an interactive computing environment to test and prototype StackQL queries.

Codespaces and the power of IPython and notebooks provide a quick and easy way to use StackQL to analyze and report on your cloud estate and resource configuration. No software necessary!

Setting Up StackQL in Codespaces​

See the stackql-codespaces-notebook repository as an example.

With the example devcontainer.json configuration file shown below, you can use the stackql/stackql-codespaces-base image, which includes stackql and the pystackql package (which provides the IPython magic extension used to run stackql queries and return Pandas dataframes for inspection or visualization).

{
  "image": "stackql/stackql-codespaces-base",
  "containerEnv": {
    "STACKQL_GITHUB_PASSWORD": "${secrets:STACKQL_GITHUB_PASSWORD}",
    "STACKQL_GITHUB_USERNAME": "${secrets:STACKQL_GITHUB_USERNAME}"
  },
  "hostRequirements": {
    "cpus": 2
  },  
  "customizations": {
    "vscode": {
      "extensions": [
        "ms-toolsai.jupyter",
        "ms-python.python"
      ]
    }
  }
}

Note that the devcontainer configuration includes the essential extensions for Jupyter and Python; you can also optionally specify host requirements for the Codespaces machine.

Provider Authentication​

The environment variables required to authenticate to your specific provider or providers can be supplied using Codespaces secrets and passed securely to the Codespaces container using the containerEnv object in the example above. For specifics about variables required for different providers, see the provider documentation for your provider in the StackQL Provider Registry.

Launching the Codespaces Environment​

Given the devcontainer configuration shown above in the root of your repository at .devcontainer/devcontainer.json, you can launch codespaces from <your_repo_slug>/codespaces for example https://github.com/stackql/stackql-codespaces-notebook/codespaces. You can start a codespaces environment on any branch of your repo.

Running queries and visualizing output​

When the environment is provisioned (usually takes a minute or two), you can use the StackQL magic extension and the %%stackql magic decorator to seamlessly run stackql queries, including variable substitution, for example.

%load_ext pystackql.magic

(loads the Stackql magic extension, making the %%stackql decorator available)

region = 'us-east-1'

(set some notebook variables for reusability)

%%stackql
SELECT instanceType, COUNT(*) as num_instances 
FROM aws.ec2.instances
WHERE region = '$region'
GROUP BY instanceType

(run a query)

_.plot(kind='pie', y='num_instances', labels=_['instanceType'], title='Instances by Type', autopct='%1.1f%%')

(visualize the results - using matplotlib, plotly, or any other visualization package)

heres an example:

Using the pystackql package, you can also run asynchronous queries such as querying assets across AWS regions, Azure resource groups (or subscriptions) or Google projects in one statement, for example:

# get multiple regions asynchronously
regions = ["ap-southeast-2", "us-east-1"]

queries = [
    f"""
    SELECT '{region}' as region, instanceType, COUNT(*) as num_instances
    FROM aws.ec2.instances
    WHERE region = '{region}'
    GROUP BY instanceType
    """
    for region in regions
]

instances_df = await stackql.executeQueriesAsync(queries)

Visit us and give us a ⭐ on GitHub

StackQL and the StackQL GitHub provider can be used to query objects in GitHub, including releases, tags, forks, commits, and much more. This article shows how you can automate releases using StackQL.

Push tags​

In my case, I merged a PR to the main branch for an updated GitHub action in a repo called stackql-exec, then I pushed a tag with an updated semver:

git tag v1.2.1
git push origin v1.2.1

Pull the StackQL GitHub provider​

From the StackQL shell or via the exec command, pull the latest GitHub provider for StackQL using:

REGISTRY PULL github;

Generate a template for creating a release (optional)​

StackQL allows you to create resource templates for creating resources such as ec2 instances, google cloud storage buckets, or github releases. This is done using the SHOW INSERT INTO command as seen here:

stackql exec --output text "SHOW INSERT INTO github.repos.releases"

The template will also generate an optional jsonnet variable block to define reusable or externally sourced variables (e.g., from environment variables).

Run your INSERT statement​

In this case, I have just used literals to keep it simple...

INSERT INTO github.repos.releases(
  owner,
  repo,
  data__body,
  data__draft,
  data__generate_release_notes,
  data__name,
  data__prerelease,
  data__tag_name,
  data__target_commitish
)
SELECT
  'stackql',
  'stackql-exec',
  'Updated authentication and added support for external variables',
   false,
   false,
  'v1.2.1',
   false,
  'v1.2.1',
  'main'
;

SELECT FROM github.repos.releases (optional)​

You can inspect the releases created for your repo, including the one you just created, using a simple SELECT statement as shown here...

select 
name,
tag_name,
target_commitish,
created_at
from github.repos.releases where owner = 'stackql' and repo = 'stackql-exec';

You will see output like the following:

|--------------------------------|-------------|------------------|----------------------|                                              
|              name              |  tag_name   | target_commitish |      created_at      |                                              
|--------------------------------|-------------|------------------|----------------------|                                              
| v1.2.1                         | v1.2.1      | main             | 2023-09-03T05:44:40Z |                                              
|--------------------------------|-------------|------------------|----------------------|                                              
| v1.2.0                         | v1.2.0      | main             | 2023-09-03T05:20:42Z |                                              
|--------------------------------|-------------|------------------|----------------------|                                              
| Fix composite action issue     | v1.0.1      | main             | 2023-03-01T09:14:20Z |                                              
| with path                      |             |                  |                      |                                              
|--------------------------------|-------------|------------------|----------------------|                                              
| Production release with        | v1.0.0      | main             | 2023-02-25T23:07:32Z |                                              
| outputs                        |             |                  |                      |                                              
|--------------------------------|-------------|------------------|----------------------|                                              
| Initial release                | v1.0.0-beta | main             | 2023-02-05T12:50:01Z |                                              
|--------------------------------|-------------|------------------|----------------------|              

More information about the GitHub Provider for StackQL can be found here.

Enjoy!