Harvesting materialized views that were generated via an external script

The lineage harvester can harvest materialized views that are native to a data source—meaning the data flow is performed by SQL code stored in the data source. If, however, an external script is used to materialize views into tables, so to speak, they cannot be harvested by the lineage harvester. In this case, you could create a custom technical lineage, which requires a user-defined JSON file.

Tip We recommend creating a script to generate a list of SQL queries to be harvested by the lineage harvester.

For each pair of source (view) and target (materialized view table), create a script as follows:

INSERT INTO 'dhw.sales.mv_customers'
	SELECT * FROM 'dhw.sales.v_customers';

The generated SQL queries then need to be harvested by the lineage harvester. There are two options for this, depending on where you choose to store the generated SQL code:

• If you store the SQL code in text files, it is harvested using an additional SqlDirectory type source.
• If you store the SQL code in a table in the data source, you need to modify the harvesting query, to harvest the table.
  In this case, actually, the generated SQL queries don't have to be stored anywhere; rather, they are generated on the fly by a harvesting query. Modify the harvesting query as follows:
  

  SELECT
  	t.table_name,
  	t.ddl as sourceCode,
  	CONCAT(t.table_schema, '.', t.table_name) as groupName,
  	t.table_schema as schemaName
  FROM `##PROJECT_ID##`.`##DSNAME##`.`INFORMATION_SCHEMA.TABLES` t
  WHERE t.table_type IN ('MATERIALIZED VIEW','VIEW')
  
  UNION ALL
  
  SELECT
  	CONCAT('m', t.table_name),
  	CONCAT('INSERT INTO `m', t.table_name, '` SELECT * FROM `', t.table_name, '`') as sourceCode,
  	CONCAT('Generated m', t.table_schema, '.', t.table_name) as groupName,
  	t.table_schema as schemaName
  FROM `##PROJECT_ID##`.`##DSNAME##`.`INFORMATION_SCHEMA.TABLES` t
  WHERE
  	t.table_type IN ('VIEW')
  	AND STARTS_WITH(t.table_name, 'v_')
  

  The second SELECT generates the necessary INSERT INTOs for all views in your data source that have a name starting with v_.