For Redshift

This guide will walk you through the process of extracting metadata and query information from your Amazon RedShift cluster

Overview

To integrate RedShift with Single Origin, you'll need to extract the following information from your RedShift cluster and upload 5 separate files:

  1. Table schemas - Information about tables
  2. Column schemas - Information about columns and data types
  3. View schemas - View definitions and dependencies
  4. Query logs - Historical query execution data
  5. Query profiles - Query performance and execution statistics

Prerequisites

  1. Access to your RedShift cluster with appropriate read permissions

Generate Input Data Files

Table Schemas

  1. Go to the Redshift Editor console.
  2. Enter the follow SQL query to create the column schema table:
  3. Click Run to execute the query.
  4. After execution, click Export and select CSV as saved file format.
SELECT
  TRIM(n.nspname) AS schema_name,
  TRIM(c.relname) AS table_name,
  c.relkind AS table_type,
  TRIM(u.usename) AS table_owner,
  ti.tbl_rows AS row_count,
  (ti.size * 1024 * 1024)::bigint AS size_bytes
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
JOIN pg_user u ON u.usesysid = c.relowner
LEFT JOIN svv_table_info ti
    ON ti."schema" = n.nspname
   AND ti."table"  = c.relname
WHERE c.relkind = 'r'
  AND u.usename NOT IN ('rdsdb','postgres','root')

Column Schemas

  1. Go to the Redshift Editor console.
  2. Enter the follow SQL query to create the table schema table:
  3. Click Run to execute the query.
  4. After execution, click Export and select CSV as saved file format.
SELECT
      n.nspname as schema_name,
      c.relname as table_name,
      a.attname as column_name,
      pg_catalog.format_type(a.atttypid, a.atttypmod) as data_type,
      a.attnum as ordinal_position,
      CASE WHEN a.attnotnull THEN 'NO' ELSE 'YES' END as is_nullable,
      pg_catalog.col_description(a.attrelid, a.attnum) as column_comment,
      pg_get_userbyid(c.relowner) as table_owner
 FROM pg_catalog.pg_attribute a
 JOIN pg_catalog.pg_class c ON a.attrelid = c.oid
 JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid
 WHERE a.attnum > 0
     AND NOT a.attisdropped
     AND c.relkind = 'r'  -- only tables
     AND pg_get_userbyid(c.relowner) NOT IN ('rdsdb', 'postgres', 'root')

View Schemas

  1. Go to the Redshift Editor console.
  2. Enter the follow SQL query to create the view schema table:
  3. Click Run to execute the query.
  4. After execution, click Export and select CSV as saved file format.
SELECT
     schemaname as schema_name,
     viewname as view_name,
     definition as view_definition,
     viewowner as view_owner
FROM pg_views
WHERE view_owner != 'rdsdb'

Query Logs

  1. Go to the Redshift Editor console.
  2. Enter the follow SQL query to create the query table:
  3. Click Run to execute the query.
  4. After execution, click Export and select CSV as saved file format.
SELECT
      query_id,
      username,
      database_name,
      start_time,
      end_time,
      execution_time,
      TRIM(query_text),
      status
FROM sys_query_history
WHERE query_type != 'UTILITY' 
AND status = 'success'
AND q.start_time > '2025-09-18'
AND q.end_time > '2025-09-19'
AND q.execution_time > 0

Query Profiles

  1. Go to the Redshift Editor console.
  2. Enter the follow SQL query to create the query profile table:
  3. Click Run to execute the query.
  4. After execution, click Export and select CSV as saved file format.
SELECT
    qe.*,
    qx.plan_node,
    qx.plan_info
FROM sys_query_history q
JOIN sys_query_detail qe ON q.query_id = qe.query_id
LEFT JOIN sys_query_explain qx ON
    qe.query_id = qx.query_id
    AND qe.child_query_sequence = qx.child_query_sequence
    AND qe.plan_node_id = qx.plan_node_id
WHERE q.query_type != 'UTILITY'
AND q.status = 'success'
AND qe.metrics_level = 'step'
AND q.start_time > '2025-09-18'
AND q.end_time > '2025-09-19'
AND q.execution_time > 0