> ## Documentation Index
> Fetch the complete documentation index at: https://docs.open-metadata.org/llms.txt
> Use this file to discover all available pages before exploring further.

# Metadata Ingestion - Incremental Extraction - Redshift

> Configure incremental metadata ingestion from Redshift to capture changes efficiently.

# Metadata Ingestion - Incremental Extraction - Redshift

## Approach

In order to implement the Incremental Extraction for Redshift we rely on the [`SYS_QUERY_HISTORY`](https://docs.aws.amazon.com/redshift/latest/dg/SYS_QUERY_HISTORY.html) to get the latest DDL changes.

We then match the `query_text` with different regex to get the Schema and Table name and understand if it was a deletion.

If no schema is present in the query we infer that it is the default for the database.

## Requisites

The user should be a Superuser to be able to see all the records.

## Used Regex

### Table Name

```python theme={null}
r"(\w+\.){0,2}\w+"
```

### Create Table

```python theme={null}
rf"^.*CREATE\s+(LOCAL\s+|EXTERNAL\s+)?(TEMPORARY\s+|TEMP\s+)?TABLE\s+(IF\s+NOT\s+EXISTS\s+)?(?P<table>{TABLE_NAME_RE}).*$"
```

### Alter Table

```python theme={null}
rf"^.*(ALTER\s+TABLE)\s+(?P<table>{TABLE_NAME_RE}).*$"
```

### Drop Table

```python theme={null}
rf"^.*DROP\s+TABLE\s+(IF\s+EXISTS\s+)?(?P<table>{TABLE_NAME_RE}).*$"
```

### Create View

```python theme={null}
rf"^.*CREATE\s+(OR\s+REPLACE\s+)?(EXTERNAL\s+|MATERIALIZED\s+)?VIEW\s+(?P<table>{TABLE_NAME_RE}).*$"
```

### Alter View

```python theme={null}
rf"^.*ALTER\s+(EXTERNAL\s+)?VIEW\s+(?P<table>{TABLE_NAME_RE}).*$"
```

### Drop View

```python theme={null}
rf"^.*DROP\s+(EXTERNAL\s+|MATERIALIZED\s+)?VIEW\s+(IF\s+EXISTS\s+)?(?P<table>{TABLE_NAME_RE}).*$"
```

### Comment

```python theme={null}
# Not supporting Comment changes on Constraint
rf"^.*COMMENT\s+ON\s+(TABLE|COLUMN|VIEW)\s+(?P<table>{TABLE_NAME_RE}).*$"
```

## Used Query

```sql theme={null}
SELECT
    query_text
FROM SYS_QUERY_HISTORY
WHERE status = 'success'
  AND (
    query_type = 'DDL' OR
    (query_type = 'UTILITY' AND query_text ilike '%%COMMENT ON%%') OR
    (query_type = 'CTAS' AND query_text ilike '%%CREATE TABLE%%')
  )
  and database_name = '{database}'
  and end_time >= '{start_date}'
ORDER BY end_time DESC
```