notebooks/stackql.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a href=\"https://stackql.io/\" target=\"_blank\">\n",
    "<img src=\"./_images/stackql-logo-bold.png\" alt=\"StackQL\" width=\"25%\" height=\"25%\">\n",
    "</a>\n",
    "<br />\n",
    "<blockquote>\n",
    "<p>walkthrough is included in this notebook, modify this template or create your own notebook to get started with StackQL.</p>\n",
    "</blockquote>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%load_ext pystackql.magics"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "add variables here, for example:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "region = 'ap-southeast-2'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "run an inline StackQL query using `%stackql`, for example:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%stackql DESCRIBE aws.ec2.instances"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "run a multiline StackQL query using `%%stackql` using notebook variables, for example:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%stackql\n",
    "SELECT instanceType, COUNT(*) as num_instances\n",
    "FROM aws.ec2.instances \n",
    "WHERE region = '$region' GROUP BY instanceType"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "the result is a `pandas` dataframe, which is accessible using the `stackql_df` variable in the notebook, for example:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "stackql_df.plot(kind='pie', y='num_instances', labels=_['instanceType'], title='Instances by Type', autopct='%1.1f%%');"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "you can also supress the output using the `--no-display` flag (the dataframe is still accessible thereafter using the `stackql_df` variable), for example:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%stackql --no-display\n",
    "SELECT instanceType, COUNT(*) as num_instances\n",
    "FROM aws.ec2.instances \n",
    "WHERE region = '$region' GROUP BY instanceType"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(\"the previous stackql query returned %s rows\" % str(len(stackql_df)))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "if you need to use a `$` in your query, use `$$`, for example:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%stackql\n",
    "SELECT \n",
    "JSON_EXTRACT(Properties, '$$.BucketName') as BucketName,\n",
    "JSON_EXTRACT(Properties, '$$.Tags') as Tags\n",
    "FROM aws.cloud_control.resources \n",
    "WHERE region = '$region' \n",
    "AND data__TypeName = 'AWS::S3::Bucket' \n",
    "AND data__Identifier = 'stackql-trial-bucket-01'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Example Notebooks\n",
    "- [__Google Audit__](./google/google_audit/1_res_heirarchy.ipynb)\n",
    "- [__AWS Example__](./aws/aws.ipynb)\n",
    "- [__GitHub Example__](./github/github.ipynb)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.4"
  },
  "vscode": {
   "interpreter": {
    "hash": "3ad933181bd8a04b432d3370b9dc3b0662ad032c4dfaa4e4f1596c548f763858"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}