Hi all! My team is currently considering adopting Datahub and we've been experimenting with it. We noticed that we weren't getting lineage using the Redshift connector unless a table was being generated with INSERT INTO statements even when using the mixed query ingestion approach. Unfortunately, a lot of our legacy code uses a DROP/CREATE approach and many tables are created with CREATE TABLE tablename (SELECT ....) statements. Is our understanding correct that these statements are not parsed by the Redshift connector?

what version of datahub you are using? which redshift connector you are using

redshift

or

redshift-legacy

redshift connect extract lineage from CREATE statements when

include_table_lineage:true

(default it true) what option you are using for lineage collector

table_lineage_mode

, best one to use mixed. Possible value [stl_scan_based, sql_based, mixed] Make sure metadata ingest user as proper access to system tables. set

capture_lineage_query_parser_failures:true

to capture lineage query parser errors ingest logs

We're using 0.12.1.5 with the mixed approach and

redshift

. We were able to see lineage when we used INSERT INTO statements. But none of the CREATE TABLE statements seemed to be parsed.

Can you post your ingestion logs and a sample of a

CREATE TABLE

query that you expect to see lineage from? Also note that if you're using temporary tables as part of this process, there's some additional flags you need to add at the moment

bump @damp-solstice-31196!

Hi! Appreciate the responses. The structure of the query was something like the following:

DROP TABLE IF EXISTS public.table1;

CREATE TABLE public.table1 AS (
  SELECT * FROM public.table2 a LEFT JOIN public.table3 b ON a.id=b.id
);

I won't be able to provide the ingestion logs immediately, I took down that setup a while back. What I can add to this that might be valuable is the following: 1. When I externally dropped the table and then recreated it via its DDL, then used an INSERT INTO query, that worked fine. Datahub was able to parse that and display column-level lineage. I now think it likely would have worked if we only removed the DROP TABLE statement given below point. 2. I also somewhat moved on from this problem since ultimately we wanted to integrate Datahub with our Airflow instance. I've been able to set that up, but am realizing that we will have to split all such DROP/CREATE TABLE ... AS (SELECT ...) queries into separate tasks since the DROP stops Datahub from ingesting the rest of the query when we're using the PostgresOperator. Sorry if the response is a bit jumbled, hopefully you'll find it interesting and maybe can confirm my hunches about what's going on with Datahub's SQL parser.

Re redshift ingestion lineage - I'm pretty surprised that we're not capturing those create table statements - to help us debug, can you run the latest rc release (0.12.1.6rc6) with

use_lineage_v2

enabled in the config? This should generate additional logs and reporting info The airflow integration currently uses our "raw" sql parser, which can only process sql with a single statement. We're working on a new SqlParsingAggregator interface that handles sequences of queries, but haven't integrated that into airflow yet. Most other warehouses have

create or replace table ... as

statements, which is why this hasn't cropped up before pWe have two "layers" of parsers - the raw

That's super helpful to know, Harshal. Thank you! I will try to test the

use_lineage_v2

when I have a minute

I'd recommend taking a look at our airflow integration https://datahubproject.io/docs/next/lineage/airflow/