google-bigquerygoogle-cloud-spanner

Possible to do a Graph query in BigQuery?

Cloud Spanner supports GQL, and it's possible to set Spanner as an external dataset for BigQuery, such as indicated here. Given this, is it possible to execute a GQL query in BigQuery, even if written as a string to be executed at Spanner? If so, how could this be done?

Solution

  • You can set up an external connection from Cloud Spanner to BigQuery and run it using that connection id, for example the following Cloud Spanner database has connection id US.1234:

    SELECT * FROM EXTERNAL_QUERY("myproject.US.1234", """
GRAPH FinGraph
MATCH
  (from_person:Person {name: "Dana"})-[:Owns]->
  (from_account:Account)-[transfer:Transfers]->
  (to_account:Account)<-[:Owns]-(to_person:Person)
RETURN
  from_person.name AS from_account_owner,
  from_account.id AS from_account_id,
  to_person.name AS to_account_owner,
  to_account.id AS to_account_id,
  transfer.amount AS amount
""");

    See the EXTERNAL QUERY function.