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:
- Table schemas - Information about tables
- Column schemas - Information about columns and data types
- View schemas - View definitions and dependencies
- Query logs - Historical query execution data
- Query profiles - Query performance and execution statistics
Prerequisites
- Access to your RedShift cluster with appropriate read permissions
Generate Input Data Files
Table Schemas
- Go to the Redshift Editor console.
- Enter the follow SQL query to create the column schema table:
- Click Run to execute the query.
- 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
- Go to the Redshift Editor console.
- Enter the follow SQL query to create the table schema table:
- Click Run to execute the query.
- 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
- Go to the Redshift Editor console.
- Enter the follow SQL query to create the view schema table:
- Click Run to execute the query.
- 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
- Go to the Redshift Editor console.
- Enter the follow SQL query to create the query table:
- Click Run to execute the query.
- 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
- Go to the Redshift Editor console.
- Enter the follow SQL query to create the query profile table:
- Click Run to execute the query.
- 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
Updated 10 days ago
What’s Next