Twing Data

Redshift Integration

This guide will help you integrate Redshift with Twing Data.

Live Integration

This creates a new user with limited permission to access the
necessary metadata from the following three tables:
SYS_QUERY_HISTORY, SVL_USER_INFO, and SYS_QUERY_TEXT.

-- Create user
CREATE USER twing_user WITH PASSWORD 'RANDOM_PASSWORD_HERE';

-- Grant read-only access to the necessary system tables
ALTER USER twing_user WITH SYSLOG ACCESS UNRESTRICTED;

One-time Integration

This is a one-time pull of two tables that can be loaded in manually for an analysis.

-- The query history
select s.user_id,
    u.usename as usename,
    s.query_id,
    s.query_type,
    s.database_name,
    trim(s.status) as status,
    s.result_cache_hit,
    s.start_time,
    s.end_time
from SYS_QUERY_HISTORY s
left join USER_ID_MAP u on u.usesysid = s.user_id
where s.query_type != 'UTILITY'
and trim(s.query_text) not in (
    'SELECT pg_backend_pid()',
    'select version()',
    'select current_schema()',
    'select current_database()',
    'select current_user()'
    )
order by s.start_time desc
limit 5000; -- Specify limit to match desired rows and file size

-- The query details since Redshift doesn't have the full query text in SYS_QUERY_HISTORY
SELECT query_id, sequence, text
FROM SYS_QUERY_TEXT
where query_id in (
    select s.query_id
    from SYS_QUERY_HISTORY s
    where s.query_type != 'UTILITY'
    and trim(s.query_text) not in (
        'SELECT pg_backend_pid()',
        'select version()',
        'select current_schema()',
        'select current_database()',
        'select current_user()'
    )
    limit 5000
)
order by query_id, sequence;