Create and query a graph

This document shows you how to use BigQuery Graph to create a graph with financial information and run graph queries using the Graph Query Language (GQL).

Required roles

To get the permissions that you need to create and query graphs, ask your administrator to grant you the BigQuery Data Editor (roles/bigquery.dataEditor) IAM role on the dataset in which you create the node tables, edge tables, and graph. For more information about granting roles, see Manage access to projects, folders, and organizations.

You might also be able to get the required permissions through custom roles or other predefined roles.

Create node and edge tables

Graphs are built from existing BigQuery tables and stored in datasets. To store the tables and graph that you create in the following examples, create a dataset. The following query creates a dataset called graph_db:

CREATE SCHEMA IF NOT EXISTS graph_db;

The following tables contain information about people and accounts, and the relationships between each of these entities:

  • Person: information about people.
  • Account: information about bank accounts.
  • PersonOwnAccount: information about who owns which accounts.
  • AccountTransferAccount: information about transfers between accounts.

To create these tables, run the following CREATE TABLE statements:

CREATE OR REPLACE TABLE graph_db.Person (
  id               INT64,
  name             STRING,
  birthday         TIMESTAMP,
  country          STRING,
  city             STRING,
  PRIMARY KEY (id) NOT ENFORCED
);

CREATE OR REPLACE TABLE graph_db.Account (
  id               INT64,
  create_time      TIMESTAMP,
  is_blocked       BOOL,
  nick_name        STRING,
  PRIMARY KEY (id) NOT ENFORCED
);

CREATE OR REPLACE TABLE graph_db.PersonOwnAccount (
  id               INT64 NOT NULL,
  account_id       INT64 NOT NULL,
  create_time      TIMESTAMP,
  PRIMARY KEY (id, account_id) NOT ENFORCED,
  FOREIGN KEY (id) REFERENCES graph_db.Person(id) NOT ENFORCED,
  FOREIGN KEY (account_id) REFERENCES graph_db.Account(id) NOT ENFORCED
);

CREATE OR REPLACE TABLE graph_db.AccountTransferAccount (
  id               INT64 NOT NULL,
  to_id            INT64 NOT NULL,
  amount           FLOAT64,
  create_time      TIMESTAMP NOT NULL,
  order_number     STRING,
  PRIMARY KEY (id, to_id, create_time) NOT ENFORCED,
  FOREIGN KEY (id) REFERENCES graph_db.Account(id) NOT ENFORCED,
  FOREIGN KEY (to_id) REFERENCES graph_db.Account(id) NOT ENFORCED
);

Create a graph

To create a graph, you use the CREATE PROPERTY GRAPH statement. The following example creates a graph called FinGraph in the graph_db dataset. The Account and Person tables are the node tables. The AccountTransferAccount and PersonOwnAccount tables are the edge tables, which represent relationships between the node tables.

CREATE OR REPLACE PROPERTY GRAPH graph_db.FinGraph
  NODE TABLES (
    graph_db.Account,
    graph_db.Person
  )
  EDGE TABLES (
    graph_db.PersonOwnAccount
      SOURCE KEY (id) REFERENCES Person (id)
      DESTINATION KEY (account_id) REFERENCES Account (id)
      LABEL Owns,
    graph_db.AccountTransferAccount
      SOURCE KEY (id) REFERENCES Account (id)
      DESTINATION KEY (to_id) REFERENCES Account (id)
      LABEL Transfers
  );

Insert data

To update the data in a graph, you update the data in your node and edge tables. When you create a graph, your data isn't moved or copied. Instead, a graph acts as a logical view of the data that exists in your node and edge tables. Your graph queries return results based on the data that exists in your node and edge tables at the time you run the query.

The following query inserts data into the tables that you created:

INSERT INTO graph_db.Account
  (id, create_time, is_blocked, nick_name)
VALUES
  (7,"2020-01-10 06:22:20.222",false,"Vacation Fund"),
  (16,"2020-01-27 17:55:09.206",true,"Vacation Fund"),
  (20,"2020-02-18 05:44:20.655",false,"Rainy Day Fund");

INSERT INTO graph_db.Person
  (id, name, birthday, country, city)
VALUES
  (1,"Alex","1991-12-21 00:00:00","Australia","Adelaide"),
  (2,"Dana","1980-10-31 00:00:00","Czech_Republic","Moravia"),
  (3,"Lee","1986-12-07 00:00:00","India","Kollam");

INSERT INTO graph_db.AccountTransferAccount
  (id, to_id, amount, create_time, order_number)
VALUES
  (7,16,300,"2020-08-29 15:28:58.647","304330008004315"),
  (7,16,100,"2020-10-04 16:55:05.342","304120005529714"),
  (16,20,300,"2020-09-25 02:36:14.926","103650009791820"),
  (20,7,500,"2020-10-04 16:55:05.342","304120005529714"),
  (20,16,200,"2020-10-17 03:59:40.247","302290001255747");

INSERT INTO graph_db.PersonOwnAccount
  (id, account_id, create_time)
VALUES
  (1,7,"2020-01-10 06:22:20.222"),
  (2,20,"2020-01-27 17:55:09.206"),
  (3,16,"2020-02-18 05:44:20.655");

Visualization of financial graph example

Query a graph

To query a graph, you run queries that use the Graph Query Language.

The following query uses a MATCH statement to find information about who Dana transferred money to:

GRAPH graph_db.FinGraph
MATCH
  (person:Person {name: "Dana"})-[own:Owns]->
  (account:Account)-[transfer:Transfers]->(account2:Account)<-[own2:Owns]-(person2:Person)
RETURN
  person.name AS owner,
  transfer.amount AS amount,
  person2.name AS transferred_to
ORDER BY person2.name

The results look similar to the following:

+-------+--------+----------------+
| owner | amount | transferred_to |
+-------+--------+----------------+
| Dana  | 500.0  | Alex           |
| Dana  | 200.0  | Lee            |
+-------+--------+----------------+

Visualize graph query results

You can visualize your graph query results in a notebook by using the %%bigquery --graph magic command followed by your GQL query. The query must return graph elements in the JSON format. To visualize the results of the query you ran in the previous section, run the following query in a notebook code cell:

%%bigquery --graph
GRAPH graph_db.FinGraph
MATCH
  p = ((person:Person {name: "Dana"})-[own:Owns]->
  (account:Account)-[transfer:Transfers]->(acount2:Account)<-[own2:Owns]-(person2:Person))
RETURN
  TO_JSON(p) AS path

Visualization of Dana's transfers in a notebook

Delete a graph

To delete a graph, use the DROP PROPERTY GRAPH statement. Deleting a graph has no effect on the tables that were used to define the graph nodes and edges. The following query deletes the FinGraph graph:

DROP PROPERTY GRAPH graph_db.FinGraph;

What's next