query

Queries define common SQL statements that may be used alone, or referenced in other blocks like controls and charts.

Note that a Powerpipe query is NOT a database resource. It does not create a view, stored procedure, etc. query blocks are interpreted by and executed by Powerpipe, and are only available from Powerpipe, not from 3rd party tools.

Example Usage

query "plus_size_instances" {
  title = "EC2 Instances xlarge and bigger"
  sql   = "select * from aws_ec2_instance where instance_type like '%xlarge'"
}

You can run a query by its name with the powerpipe query run command:

$ powerpipe query run plus_size_instances

Argument Reference

Database

Mods are typically written for a specific database engine and schema. Powerpipe allows you to set a default database connection for your mod. Any query, control, chart, or other query-provider resource that does not specifically set the database argument will use this database. Because the majority of mods are currently written for Steampipe databases, the default database is set to the local Steampipe instance if it is not specified for the mod (postgres://steampipe@localhost:9193/steampipe). It is generally advisable not to set the database on the mod resources in the code, and instead use the mod default.

As with the default database connection, the database may be:

• A database connection reference, eg connection.steampipe.default.
• A connection string, eg sqlite://myfile.db
• A Pipes workspace, eg acme/anvils

Connection Strings

The connection string syntax for database argument is the same whether you set it in the mod or the query, card, chart, etc.

Postgres

Powerpipe can connect to any Postgres database. The Postgres database follows the standard URI syntax supported by psql and pgcli:

postgresql://[user[:password]@][host][:port][/dbname][?param1=value1&...]

For example:

query "my_query" {
  database = "postgresql://myusername:mypassword@acme-prod.apse1.db.cloud.turbot.io:9193/aaa000"
  sql               = "select * from my_table;" 
}

MySQL

The MySQL connection string supports the syntax of the GO SQL driver for MySQL:

mysql://[user[:password]@]network-location[:port][/dbname][?param1=value1&...]

query "my_query" {
  database = "mysql://root:my_pass@tcp(localhost)/mysql"
  sql      = "select * from my_table;" 
}

SQLite

The SQLite database connection string is the path to a SQLite database file:

sqlite:path/to/file

The path is relative to the mod location, and // is optional after the scheme, thus the following are equivalent:

database = "sqlite:./my_sqlite_db.db"
database = "sqlite://./my_sqlite_db.db"
database = "sqlite://my_sqlite_db.db"

For example:

query "my_query" {
  database = "sqlite:./my_sqlite_db.db"
  sql      = "select * from my_table;" 
}

DuckDB

The DuckDB connection string is the path to a DuckDB database file:

duckdb:path/to/file

The path is relative to the mod location, and // is optional after the scheme, thus the following are equivalent:

database = "duckdb:./my_ducks.db"
database = "duckdb://./my_ducks.db"
database = "duckdb://my_ducks.db"

For example:

query "my_query" {
  database = "duckdb:./my_ducks.db
  sql      = "select * from my_table;" 
}

param

One or more param blocks may optionally be used in a query to define parameters that the query accepts. Note that the SQL statement only supports positional arguments ($1, $2, ...) and that the param blocks are assigned in order -- the first param block describes $1, the second describes $2, etc.

variable "max_access_key_age" {
  type = number
}

query "old_access_keys" {
  sql = <<-EOT
    select
      access_key_id,
      user_name,
      create_date,
      age(create_date) as age
    from
      aws_iam_access_key
    where
      create_date < NOW() - ($1 ::numeric || ' days')::interval;
  EOT

  param "max_days" {
    default     = var.max_access_key_age
    description = "The maximum allowed key age, in days."
  } 
}

Query-based Resources

There are many Powerpipe mod elements that execute a query, including control and most dashboard visualization elements (card, chart, node, edge, graphs etc). These resources essentially implement the same interface:

• They have an sql argument for specifying an SQL string to execute
• They have a query argument for referencing a query to execute
• They require the user to set either sql or query, but both may not be specified.
• They have an optional param argument to specify parameters that the sql accepts
• They have an optional args argument to specify what arguments to pass to the query or sql

Query v/s SQL

When using a query-based resource, you must specify either the sql or query argument, but not both.

The difference between these arguments is somewhat subtle.

The sql argument is a simple string that defines a SQL statement to execute. This allows you to inline the statement in the resource definition:

card {
  sql = <<-EOQ
    select
      count(*) as "Buckets"
    from
      aws_s3_bucket
  EOQ

  width = 2
}

The query argument is a reference to a named query resource to run. This allows you to reuse a query from multiple other resources:

card {
  width = 2
  query = query.bucket_count
}

query "bucket_count" {
  sql = <<-EOQ
    select
      count(*) as "Buckets"
    from
      aws_s3_bucket
  EOQ

}

Params v/s Args

Query-based resources allow you to specify parameters that the sql accepts and to specify what arguments to pass to the query or sql. The difference between param and args is a common source of confusion.

A param block is used to define a parameter that the sql accepts - It is a definition of what you can pass. The args block is used to specify what values to pass to the query or sql - these are the actual values to use when running the query.

param blocks are only used when the sql argument is specified and the SQL statement includes parameters ($1, $2):

card "bucket_count_for_region" {
  sql = <<-EOQ
    select
      count(*) as "Buckets"
    from
      aws_s3_bucket
    where
      region = $1
  EOQ

  param "region" {
    default = "us-east-1"
  }

  width = 2
}

You can only specify param blocks for resources that are defined as top-level named resources in your mod. It would not make sense to specify a param block for an anonymous resource that is defined in a dashboard since you cannot reference it anyway.

The args argument is used to pass values to a query at run time. If the query resource has parameters defined, then the args argument is used to pass values to the query:

dashboard "s3_dashboard" {
  card {
    width = 2
    query = query.bucket_count_for_region

    args = {
      region = "us-east-1"
    }
  }
}

query "bucket_count_for_region" {
  sql = <<-EOQ
    select
      count(*) as "Buckets"
    from
      aws_s3_bucket
    where
      region = $1
  EOQ

  param "region" {}
}

Note that most query-based resources can be defined as top-level resources and reused with base, and you can pass arguments to them in the same manner:

dashboard "s3_buckets" {

  card {
    base = card.bucket_count_for_region
    args = {
      region = "us-east-1"
    }
  }

  card {
    base = card.bucket_count_for_region
    args = {
      region = "us-east-2"
    }
  }
}

card "bucket_count_for_region" {
  sql = <<-EOQ
    select
      count(*) as "Buckets"
    from
      aws_s3_bucket
    where
      region = $1
  EOQ

  param "region" {
    default = "us-east-1"
  }

  width = 2
}

While param blocks are recommended when SQL statements define parameters, they are not required -- you won't be able to pass arguments by name, but you can still pass them positionally using the array format of the args argument:

dashboard "s3_dashboard" {
  card {
    width = 2
    query = query.bucket_count_for_region

    args  = ["us-east-1"]
  }
}

query "bucket_count_for_region" {
  sql = <<-EOQ
    select
      count(*) as "Buckets"
    from
      aws_s3_bucket
    where
      region = $1
  EOQ
}