How can I configure BigQuery Technical Lineage "other query" when I am using INFORMATION_SCHEMA.JOBS instead of ROUTINES/PARAMETERS

Hi,

You could give this query a try:

SELECT
  r.routine_name,
  CONCAT(
    'CREATE ', r.routine_type, ' `', r.routine_schema, '.', r.routine_name, '`(',
    
    ARRAY_TO_STRING(ARRAY_AGG(CONCAT(p.parameter_name, ' ', p.data_type) ORDER BY p.ordinal_position), ', '),
    ') ',
    
    CASE WHEN r.routine_type = 'FUNCTION'
      THEN CONCAT('AS (', r.routine_definition, ')')
      ELSE r.routine_definition
    END
  ) as sourceCode,
  r.routine_type as groupName,
  r.routine_schema as schemaName
FROM `##PROJECT_ID##`.`##DSNAME##`.`INFORMATION_SCHEMA.ROUTINES` r
JOIN `##PROJECT_ID##`.`##DSNAME##`.`INFORMATION_SCHEMA.PARAMETERS` p
USING (specific_catalog, specific_schema, specific_name)
WHERE r.routine_body = 'SQL'
  AND p.is_result = 'NO'
GROUP BY r.routine_schema, r.routine_name, r.routine_type, r.routine_definition
UNION ALL
SELECT
  r.routine_name,
  CONCAT(
    'CREATE ', r.routine_type, ' `', r.routine_schema, '.', r.routine_name, '`(',
    ARRAY_TO_STRING(ARRAY_AGG(CONCAT(p.parameter_name, ' ', p.data_type) ORDER BY p.ordinal_position), ', '),
    ') ',
    
    CONCAT('LANGUAGE js AS """', r.routine_definition, '"""')
  ) as sourceCode,
  r.routine_type as groupName,
  r.routine_schema as schemaName
FROM `##PROJECT_ID##`.`##DSNAME##`.`INFORMATION_SCHEMA.ROUTINES` r
JOIN `##PROJECT_ID##`.`##DSNAME##`.`INFORMATION_SCHEMA.PARAMETERS` p
USING (specific_catalog, specific_schema, specific_name)
WHERE r.external_language = 'JAVASCRIPT'
  AND p.is_result = 'NO'
GROUP BY r.routine_schema, r.routine_name, r.routine_type, r.routine_definition
UNION ALL
SELECT
job_id as routine_name,
query as sourceCode,
job_type as groupName,
schemaDef.dataset_id as schemaName
FROM
`##PROJECT_ID##`.`region-eu`.INFORMATION_SCHEMA.JOBS
CROSS JOIN UNNEST(referenced_tables) AS schemaDef
WHERE
user_email ='[email protected]' 
AND state='DONE' 
AND DATE(end_time) = CURRENT_DATE() 
GROUP BY schemaDef.dataset_id, job_id, query, job_type

It will require these permissions.

Note that this is not officially supported.

Best Regards,

Kristof