Redshift (via Collector method) - v3.1.0

About Collectors

Collectors are extractors that are developed and managed by you (A customer of K).

KADA provides python libraries that customers can use to quickly deploy a Collector.

Why you should use a Collector

There are several reasons why you may use a collector vs the direct connect extractor:

1. You are using the KADA SaaS offering and it cannot connect to your sources due to firewall restrictions

2. You want to push metadata to KADA rather than allow it pull data for Security reasons

3. You want to inspect the metadata before pushing it to K

Using a collector requires you to manage

1. Deploying and orchestrating the extract code

2. Managing a high water mark so the extract only pull the latest metadata

3. Storing and pushing the extracts to your K instance.

Pre-requisites

Collector Server Minimum Requirements

Redshift Requirements

• Access to Redshift

Redshift Access

Log into Redshift as a Superuser. Superuser access is required to complete the following steps.

Create a Redshift user. This user MUST be either (one or the other below, we generally recommend 2.)

1. Be a Superuser. Refer to https://docs.aws.amazon.com/redshift/latest/dg/r_superusers.html to view all required data.

   ALTER USER <kada user> CREATEUSER; -- GRANTS SUPERUSER
   

2. Be a Database user with:

   1. Unrestricted SYSLOG ACCESS refer to https://docs.aws.amazon.com/redshift/latest/dg/c_visibility-of-data.html. This will allow full access to the STL tables for the user.

      SQL

      ALTER USER <kada user> SYSLOG ACCESS UNRESTRICTED; -- GRANTS READ ACCESS
      

   2. Select Access to existing and future tables in all Schemas for each Database you want K to ingest.

      1. List all existing Schema in the Database by running

         SQL

         SELECT DISTINCT schema_name FROM svv_all_tables; -- LIST ALL SCHEMAS
         

      2. For each schema above do the following to allow the user select access to all tables inside the Schema and any new tables created in the schema thereafter.

         SQL

         GRANT USAGE ON SCHEMA <schema name> TO <kada user>;
         GRANT SELECT ON ALL TABLES IN SCHEMA <schema name> TO <kada user>;
         ALTER DEFAULT PRIVILEGES IN SCHEMA <schema name> GRANT SELECT ON TABLES TO <kada user>;
         

PG Catalog

GRANT USAGE ON SCHEMA pg_catalog TO <kada user>;
GRANT SELECT ON ALL TABLES IN SCHEMA pg_catalog TO <kada user>;

PG Tables

• pg_class

• pg_user

• pg_group

• pg_namespace

• pg_proc

• pg_database

System Tables

• svv_all_columns

• svv_all_tables

• svv_tables

• svv_external_tables

• svv_external_schemas

• stl_query

• stl_querytext

• stl_ddltext

• stl_utilitytext

• stl_query_metrics

• stl_sessions

• stl_connection_log

Databases

• dev (The extractor uses the dev database as a test access point)

• All other databases that you want onboarded

Step 1: Create the Source in K

• Go to Settings, Select Sources and click Add Source

• Select "Load from File" option

• Give the source a Name - e.g. Redshift Production

• Add the Host name for the Redshift Server

• Click Finish Setup

Step 2: Getting Access to the Source Landing Directory

Step 3: Install the Collector

You can download the latest Core Library and whl via Platform Settings → Sources → Download Collectors

pip install kada_collectors_extractors_<version>-none-any.whl

pip install kada_collectors_lib-<version>-none-any.whl

Step 4: Configure the Collector

kada_redshift_extractor_config.json

{
    "host": "",
    "server":"",
    "username": "",
    "password": "",
    "databases": [],
    "port": 5439,
    "tunnel": false,
    "output_path": "/tmp/output",
    "mask": true,
    "compress": true
}

Step 5: Run the Collector

This is the wrapper script: kada_redshift_extractor.py

import os
import argparse
from kada_collectors.extractors.utils import load_config, get_hwm, publish_hwm, get_generic_logger
from kada_collectors.extractors.redshift import Extractor

get_generic_logger('root')

_type = 'redshift'
dirname = os.path.dirname(__file__)
filename = os.path.join(dirname, 'kada_{}_extractor_config.json'.format(_type))

parser = argparse.ArgumentParser(description='KADA Redshift Extractor.')
parser.add_argument('--config', '-c', dest='config', default=filename)
parser.add_argument('--name', '-n', dest='name', default=_type)
args = parser.parse_args()

start_hwm, end_hwm = get_hwm(args.name)

ext = Extractor(**load_config(args.config))
ext.test_connection()
ext.run(**{"start_hwm": start_hwm, "end_hwm": end_hwm})

publish_hwm(args.name, end_hwm)

Advance options:

If you wish to maintain your own high water mark files elsewhere you can use the above section's script as a guide on how to call the extractor. The configuration file is simply the keyword arguments in JSON format. Refer to this document for more information https://kadaai.atlassian.net/wiki/spaces/KKB/pages/3333849163/Collector+Integration+General+Notes#Storing-HWM-in-another-location

If you are handling external arguments of the runner yourself, you'll need to consider additional items for the run method. Refer to this document for more information https://kadaai.atlassian.net/wiki/spaces/KKB/pages/3333849163/Collector+Integration+General+Notes#The-run-method

Step 6: Check the Collector Outputs

K Extracts

A set of files (eg metadata, databaselog, linkages, events etc) will be generated. These files will appear in the output_path directory you set in the configuration details

High Water Mark File

A high water mark file is created in the same directory as the execution called redshift_hwm.txt and produce files according to the configuration JSON. This file is only produced if you call the publish_hwm method.

Step 7: Push the Extracts to K

Once the files have been validated, you can push the files to the K landing directory.

You can use Azure Storage Explorer if you want to initially do this manually. You can push the files using python as well (see Airflow example below)