Amazon Redshift

Required Information

To configure an Amazon Redshift data source in Alation, you will need the following information:

  • Hostname or IP address of your server

  • Port number

  • Database name

  • Service account with privileges listed

  • Query Log Ingestion setup:  custom table

../../_images/DS_Redshift01.png

Preliminaries

Firewall Configuration:

  • Open outbound TCP port 5439 to Amazon Redshift

Create Service Account

Note

From version 2021.3, you can authenticate with AWS IAM without using a service account. For details, see Configure MDE with AWS IAM Authentication.

Sample SQL to create an account:

CREATE USER alation WITH PASSWORD '[password]';

Metadata Extraction

GRANT USAGE ON SCHEMA PG_CATALOG TO alation;
GRANT SELECT ON ALL TABLES IN SCHEMA PG_CATALOG TO alation;

LOGIN as alation into the respective database; grants are eligible across that database.

GRANT SELECT ON TABLE stv_blocklist TO alation;
GRANT SELECT ON TABLE stv_partitions TO alation;
GRANT SELECT ON TABLE STV_SLICES TO alation;
GRANT SELECT ON TABLE stv_tbl_perm TO alation;

Table Profiles

GRANT SELECT ON ALL TABLES IN SCHEMA [schema] TO alation;
GRANT USAGE ON SCHEMA [schema] TO alation;
GRANT USAGE ON SCHEMA information_schema TO alation;
GRANT SELECT ON ALL TABLES IN SCHEMA information_schema TO alation;

Query History

Create a custom query log table using SQL from Appendix A.3  and provide SELECT privileges to Alation DB account on the table.

Compose

Available from release 2021.2

Amazon Redshift data sources support SSO authentication: SSO Authentication for Amazon Redshift Data Source.

Lineage

From version 2022.1, column-level lineage is supported for Amazon Redshift data sources in addition to table-level lineage.

Note

This feature requires the Amazon Redshift Column Level Lineage parser add-on. Contact Alation Support about purchasing this add-on.

Enable Column-Level Lineage

To enable column-level lineage:

  1. Go to Settings > Feature Configuration.

  2. Turn on the toggle Automatically extracts Column Level Lineage for Redshift data sources.

  3. Click the Save changes button to save the changes.

    ../../_images/DS_Redshift02.png

  1. In the Verify Feature Configuration Change dialog, click the Save Configuration button .

    ../../_images/DS_Redshift03.png

View Column-Level Lineage

With the column-level lineage add-on enabled, Alation will automatically generate column-level lineage data based on the existing metadata that was previously extracted with MDE, QLI, or ingested from Compose.

Note

Once the feature flag is enabled, the column-level lineage will be automatically generated only for the view definitions that are complete and already added to table-level lineage.

Complete view definition:

CREATE VIEW adbc_database_01.query_execution_main.table_view AS
  SELECT src.*
  FROM (SELECT col1, col2, col3 FROM tpch.sf1.customer LIMIT 10) src;

Incomplete view definition:

SELECT src.*
FROM (SELECT col1, col2, col3 FROM tpch.sf1.customer LIMIT 10) src;

More column-level lineage data will be created after you perform metadata extraction (MDE), query log ingestion (QLI), and (or) use Compose to run queries that create or update column objects.

To view column-level lineage data:

  1. Go to the Lineage tab on the catalog page of a table object that should have column-level lineage.

    ../../_images/DS_Snowflake10.png

  2. Expand the table or view lineage node to see the columns of the table or view and links to the dataflow object.

    ../../_images/DS_Snowflake11.png

  3. Click a column you want to trace lineage for. The column-level lineage paths for that particular column become highlighted.

    ../../_images/DS_Snowflake12.png

Note

If the column-level lineage feature flag is disabled, the column-level lineage links that were generated previously are not removed. Only the future queries and query history will not be processed.