Table: pipes_workspace_pipeline - Query Pipes Workspace Pipelines using SQL

A Pipes Workspace Pipeline is a crucial component within the Pipes service that allows for the execution of a sequence of data processing steps. These pipelines can be used to extract, transform, and load (ETL) data from various sources into a target destination. They are highly configurable and can be managed and monitored for efficient data processing and analytics.

Table Usage Guide

The pipes_workspace_pipeline table provides insights into the Workspace Pipelines within the Pipes service. As a Data Engineer, explore pipeline-specific details through this table, including pipeline configuration, status, and related metadata. Utilize it to uncover information about pipelines, such as their current execution status, configuration details, and to manage and monitor them effectively.

Important Notes

• This table supports optional quals. Queries with optional quals in the where clause are optimised to use Turbot Pipes filters.

• Optional quals are supported for the following columns:

  • created_at
  • id
  • identity_handle
  • identity_id
  • pipeline
  • query_where - Allows use of query filters. For a list of supported columns for pipelines, please see Supported APIs and Columns. Please note that any query filter passed into the query_where qual will be combined with other optional quals.
  • title
  • updated_at
  • workspace_handle
  • workspace_id

Examples

Basic info

Explore which workspace pipelines are active, how frequently they run, and any associated tags. This can be useful in understanding the flow of data and identifying any potential bottlenecks in the pipeline.

select
  id,
  identity_handle,
  workspace_handle,
  title,
  frequency,
  pipeline,
  args,
  tags,
  last_process_id
from
  pipes_workspace_pipeline;

select
  id,
  identity_handle,
  workspace_handle,
  title,
  frequency,
  pipeline,
  args,
  tags,
  last_process_id
from
  pipes_workspace_pipeline;

List pipelines for a specific workspace

Discover the segments that are part of a particular workspace in order to understand how frequently they run, their specific parameters, and their latest process. This is useful for assessing the operational efficiency and identifying any potential bottlenecks within a specific workspace.

select
  id,
  identity_handle,
  workspace_handle,
  title,
  frequency,
  pipeline,
  args,
  tags,
  last_process_id
from
  pipes_workspace_pipeline
where
  workspace_handle = 'dev';

select
  id,
  identity_handle,
  workspace_handle,
  title,
  frequency,
  pipeline,
  args,
  tags,
  last_process_id
from
  pipes_workspace_pipeline
where
  workspace_handle = 'dev';

List pipelines of frequency type interval for a specific workspace

Analyze the settings to understand the pipelines configured to run at regular intervals in a specific workspace. This can be useful in managing and optimizing scheduled tasks within your workspace.

select
  id,
  identity_handle,
  workspace_handle,
  title,
  frequency,
  pipeline,
  args,
  tags,
  last_process_id
from
  pipes_workspace_pipeline
where
  workspace_handle = 'dev'
  and frequency ->> 'type' = 'interval';

select
  id,
  identity_handle,
  workspace_handle,
  title,
  frequency,
  pipeline,
  args,
  tags,
  last_process_id
from
  pipes_workspace_pipeline
where
  workspace_handle = 'dev'
  and json_extract(frequency, '$.type') = 'interval';

List pipelines for the AWS Compliance CIS v1.4.0 dashboard created in the last 7 days

Explore which pipelines have been created in the past week for the AWS Compliance CIS v1.4.0 dashboard. This is useful for auditing recent changes and ensuring compliance standards are being met.

select
  id,
  identity_handle,
  workspace_handle,
  title,
  frequency,
  pipeline,
  args,
  tags,
  last_process_id
from
  pipes_workspace_pipeline
where
  args ->> 'resource' = 'aws_compliance.benchmark.cis_v140'
  and created_at >= now() - interval '7 days';

select
  id,
  identity_handle,
  workspace_handle,
  title,
  frequency,
  pipeline,
  args,
  tags,
  last_process_id
from
  pipes_workspace_pipeline
where
  json_extract(args, '$.resource') = 'aws_compliance.benchmark.cis_v140'
  and created_at >= datetime('now', '-7 days');

List pipelines for the AWS Compliance CIS v1.4.0 dashboard created in the last 7 days using query filter

Explore recently created pipelines for a specific compliance dashboard. This is particularly useful for tracking the latest updates and changes made within the past week.

select
  id,
  identity_handle,
  workspace_handle,
  title,
  frequency,
  pipeline,
  args,
  tags,
  last_process_id
from
  pipes_workspace_pipeline
where
  query_where = 'title = ''Scheduled snapshot: CIS v1.4.0'' and created_at >= now() - interval ''7 days''';

select
  id,
  identity_handle,
  workspace_handle,
  title,
  frequency,
  pipeline,
  args,
  tags,
  last_process_id
from
  pipes_workspace_pipeline
where
  query_where = 'title = ''Scheduled snapshot: CIS v1.4.0'' and created_at >= datetime(' now ', ' -7 days ')';

This table is available as a standalone Exporter CLI. Steampipe exporters are stand-alone binaries that allow you to extract data using Steampipe plugins without a database.

You can download the tarball for your platform from the Releases page, but it is simplest to install them with the steampipe_export_installer.sh script:

/bin/sh -c "$(curl -fsSL https://steampipe.io/install/export.sh)" -- pipes

You can pass the configuration to the command with the --config argument:

steampipe_export_pipes --config '<your_config>' pipes_workspace_pipeline