Parse BigQuery federated queries

[cthrysoe]

Hi all,

In BigQuery, it is possible to execute queries against Cloud SQL instances (GCP managed database), using "federated queries" see example here, using the EXTERNAL_QUERY function, example:

SELECT c.customer_id, c.name, rq.first_order_date
FROM mydataset.customers AS c
LEFT OUTER JOIN EXTERNAL_QUERY(
  'us.connection_id',
  '''SELECT customer_id, MIN(order_date) AS first_order_date
  FROM orders
  GROUP BY customer_id''') AS rq ON rq.customer_id = c.customer_id
GROUP BY c.customer_id, c.name, rq.first_order_date;

Has anyone else had success with parsing these BigQuery federated queries? I am interested in extracting what tables are referred, both in the BigQuery SQL and the referred tables from the federated query. One option could be to add the function EXTERNAL_QUERY to my Analyzer and analyze the federated query first and use it somehow in the context of the entire query, but maybe someone has a better solution as to how I can parse these queries together? Thanks