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
     n.nspname as schema_name,
     c.relname as table_name,
     c.relkind as table_type,
     obj_description(c.oid) as table_comment,
     pg_get_userbyid(c.relowner) as table_owner
FROM pg_catalog.pg_class c
JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid
WHERE c.relkind = 'r'  -- only tables
    AND pg_get_userbyid(c.relowner) NOT IN ('rdsdb', 'postgres', 'root')
ORDER BY n.nspname, c.relname;

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')
 ORDER BY n.nspname, c.relname, a.attnum;

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,
      query_text,
      status
FROM sys_query_history
  and status = 'success'
ORDER BY start_time DESC

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.*
  FROM sys_query_history q
  JOIN sys_query_detail qe ON q.query_id = qe.query_id
order by q.query_id