Create a graph from a SQL view

Learn how to create a graph using SQL views . This document provides step-by-step instructions and code examples for defining views and using them to define node and edge tables. Explore examples with sample code that demonstrate use cases for creating graphs with views. To learn more about using views to create a property graph, including benefits and considerations, see Overview of graphs created from SQL views .

Before you begin

To create a graph, you must:

Ensure your Spanner Graph environment is set up .
Familiarize yourself with how Spanner Graph schemas work .

Create a graph using views

To create a graph using views:

Define views for your graph. Make sure your views follow one of the required view patterns . For more information, see Create a view .
Use your views in the NODE TABLES and EDGE TABLES clauses of the CREATE PROPERTY GRAPH statement to create a graph.
Include the KEY clause in the CREATE PROPERTY GRAPH statement. The KEY clause specifies the columns from the source view that uniquely identify each graph element.

Example: Create a graph using views

This example creates the following views over Customer and Account tables: AsiaCustomer , AsiaBankAccount , and AsiaAccountsOwnership . Then, the example uses these views to create the following in a graph:

Create the Customer node table using the AsiaCustomer view.
Create the Account node table using the AsiaBankAccount view.
Create the Owns edge table using the AsiaAccountsOwnership view. This edge connects Customer nodes with Account nodes.

Step 1: Create the tables

First, create the data tables. The following code creates the Customer and Account tables.

  CREATE 
  
 TABLE 
  
 Customer 
  
 ( 
  
 customer_id 
  
 INT64 
  
 NOT 
  
 NULL 
 , 
  
 name 
  
 STRING 
 ( 
 MAX 
 ), 
  
 address_continent 
  
 STRING 
 ( 
 MAX 
 ), 
  
 address_country 
  
 STRING 
 ( 
 MAX 
 ), 
 ) 
  
 PRIMARY 
  
 KEY 
 ( 
 customer_id 
 ); 
 CREATE 
  
 TABLE 
  
 Account 
  
 ( 
  
 account_id 
  
 INT64 
  
 NOT 
  
 NULL 
 , 
  
 customer_id 
  
 INT64 
  
 NOT 
  
 NULL 
 , 
  
 account_type 
  
 STRING 
 ( 
 MAX 
 ), 
  
 balance 
  
 INT64 
 , 
  
 create_time 
  
 TIMESTAMP 
 , 
  
 address_continent 
  
 STRING 
 ( 
 MAX 
 ), 
  
 address_country 
  
 STRING 
 ( 
 MAX 
 ), 
  
 CONSTRAINT 
  
 FK_CustomerId 
  
 FOREIGN 
  
 KEY 
  
 ( 
 customer_id 
 ) 
  
 REFERENCES 
  
 Customer 
  
 ( 
 customer_id 
 ) 
 ) 
  
 PRIMARY 
  
 KEY 
 ( 
 account_id 
 );

Step 2: Create the views

Next, create views to transform or filter data from the tables. These views filter the tables to include only customers and accounts in Asia. Views used to create graph elements must ensure that rows in the view are unique .

  -- View for 'Customer' nodes, filtered for Asia 
 CREATE 
  
 VIEW 
  
 AsiaCustomer 
  
 SQL 
  
 SECURITY 
  
 INVOKER 
  
 AS 
  
 SELECT 
  
 customer 
 . 
 customer_id 
 , 
  
 customer 
 . 
 name 
  
 FROM 
  
 Customer 
  
 customer 
  
 WHERE 
  
 LOWER 
 ( 
 customer 
 . 
 address_continent 
 ) 
  
 = 
  
 "asia" 
 ; 
 -- View for 'Account' nodes, filtered for Asia. 
 CREATE 
  
 VIEW 
  
 AsiaBankAccount 
  
 SQL 
  
 SECURITY 
  
 INVOKER 
  
 AS 
  
 SELECT 
  
 account 
 . 
 account_id 
 , 
  
 account 
 . 
 balance 
 , 
  
 account 
 . 
 account_type 
 , 
  
 account 
 . 
 create_time 
  
 FROM 
  
 Account 
  
 account 
  
 WHERE 
  
 LOWER 
 ( 
 account 
 . 
 address_continent 
 ) 
  
 = 
  
 "asia" 
 ; 
 -- View for 'Owns' edges, connecting customers to accounts in Asia. 
 CREATE 
  
 VIEW 
  
 AsiaAccountsOwnership 
  
 SQL 
  
 SECURITY 
  
 INVOKER 
  
 AS 
  
 SELECT 
  
 account 
 . 
 customer_id 
 , 
  
 account 
 . 
 account_id 
  
 FROM 
  
 Account 
  
 account 
  
 WHERE 
  
 LOWER 
 ( 
 account 
 . 
 address_continent 
 ) 
  
 = 
  
 "asia" 
 ;

Step 3: Create the property graph

Now, create the AsiaFinGraph using the views you created. The CREATE PROPERTY GRAPH statement includes the KEY clause for each graph element definition to specify columns that uniquely identify the graph elements.

  CREATE 
  
 PROPERTY 
  
 GRAPH 
  
 AsiaFinGraph 
  
 NODE 
  
 TABLES 
  
 ( 
  
 AsiaCustomer 
  
 AS 
  
 Customer 
  
 KEY 
 ( 
 customer_id 
 ), 
  
 AsiaBankAccount 
  
 AS 
  
 Account 
  
 KEY 
 ( 
 account_id 
 ) 
  
 ) 
  
 EDGE 
  
 TABLES 
  
 ( 
  
 AsiaAccountsOwnership 
  
 AS 
  
 Owns 
  
 KEY 
 ( 
 customer_id 
 , 
  
 account_id 
 ) 
  
 SOURCE 
  
 KEY 
  
 ( 
 customer_id 
 ) 
  
 REFERENCES 
  
 Customer 
  
 ( 
 customer_id 
 ) 
  
 DESTINATION 
  
 KEY 
  
 ( 
 account_id 
 ) 
  
 REFERENCES 
  
 Account 
  
 ( 
 account_id 
 ) 
  
 );

Use cases examples

SQL views offer benefits over using tables for property graph elements. The following examples demonstrate some use cases for defining graph elements with views instead of tables.

Example: Enforce fine-grained graph data access control

To enforce row-level security on your graph data, define your node or edge tables using definer's rights views . The view exposes a permitted subset of the underlying data to the graph

For example, to restrict graph access to only employees in an engineering cost center, you can create an EngineerEmployeeView view and grant SELECT permissions on the view to an engineering_data_reader role using the GRANT clause.

When you define a graph node table using this view, users running graph queries with the engineering_data_reader role can see only the rows filtered by the view, which include engineering employees.

  -- The table containing all employee data. 
 CREATE 
  
 TABLE 
  
 Employee 
  
 ( 
  
 id 
  
 INT64 
  
 NOT 
  
 NULL 
 , 
  
 cost_center 
  
 STRING 
 ( 
 MAX 
 ), 
  
 job_title 
  
 STRING 
 ( 
 MAX 
 ), 
  
 office 
  
 STRING 
 ( 
 MAX 
 ) 
 ) 
  
 PRIMARY 
  
 KEY 
  
 ( 
 id 
 ); 
 -- The definer's rights view that filters for engineering employees. 
 CREATE 
  
 VIEW 
  
 EngineerEmployeeView 
  
 SQL 
  
 SECURITY 
  
 DEFINER 
  
 AS 
  
 SELECT 
  
 e 
 . 
 id 
 , 
  
 e 
 . 
 cost_center 
 , 
  
 e 
 . 
 job_title 
 , 
  
 e 
 . 
 office 
  
 FROM 
  
 Employee 
  
 e 
  
 WHERE 
  
 LOWER 
 ( 
 e 
 . 
 cost_center 
 ) 
  
 = 
  
 "engineering" 
 ; 
 -- The role that is granted to read the view. 
 CREATE 
  
 ROLE 
  
 engineering_data_reader 
 ; 
 GRANT 
  
 SELECT 
  
 ON 
  
 VIEW 
  
 EngineerEmployeeView 
  
 TO 
  
 ROLE 
  
 engineering_data_reader 
 ; 
 -- The graph that uses definer's rights view. 
 CREATE 
  
 PROPERTY 
  
 GRAPH 
  
 EngineeringGraph 
  
 NODE 
  
 TABLES 
  
 ( 
  
 EngineerEmployeeView 
  
 KEY 
 ( 
 id 
 ) 
  
 );

Example: Model derived graph elements

You can use views to define graph elements that require data transformations. A key benefit is that the view defines the transformation, so you don't need to maintain a separate table for the derived data.

For example, you can UNNEST data from an ARRAY column (or an array field within a JSON column) to model multiple edge relationships from a single row.

In the following supply chain schema example, a Parts table stores a list of sub-components in a dependent_parts array. A view can use the UNNEST operator to transform each element of that array into distinct rows. This view can then serve as an edge table, letting you model a PartDependsOnPart edge to represent dependency relationships between parts.

  -- Parts table with an ARRAY of dependent parts. 
 CREATE 
  
 TABLE 
  
 Parts 
  
 ( 
  
 part_id 
  
 INT64 
  
 NOT 
  
 NULL 
 , 
  
 dependent_parts 
  
 ARRAY<INT64> 
 ) 
  
 PRIMARY 
  
 KEY 
  
 ( 
 part_id 
 ); 
 -- A view that unnests the dependent_parts array. 
 -- GROUP BY ensures uniqueness for the graph element KEY. 
 CREATE 
  
 VIEW 
  
 PartDependsOnPart 
  
 SQL 
  
 SECURITY 
  
 INVOKER 
  
 AS 
  
 SELECT 
  
 p 
 . 
 part_id 
 , 
  
 dependent_part_id 
  
 FROM 
  
 Parts 
  
 AS 
  
 p 
 , 
  
 UNNEST 
 ( 
 p 
 . 
 dependent_parts 
 ) 
  
 AS 
  
 dependent_part_id 
  
 GROUP 
  
 BY 
  
 p 
 . 
 part_id 
 , 
  
 dependent_part_id 
 ; 
 -- Graph modeling the part dependency relationship. 
 CREATE 
  
 PROPERTY 
  
 GRAPH 
  
 SupplyChainGraph 
  
 NODE 
  
 TABLES 
  
 ( 
  
 Parts 
  
 ) 
  
 EDGE 
  
 TABLES 
  
 ( 
  
 PartDependsOnPart 
  
 KEY 
  
 ( 
 part_id 
 , 
  
 dependent_part_id 
 ) 
  
 SOURCE 
  
 KEY 
  
 ( 
 part_id 
 ) 
  
 REFERENCES 
  
 Parts 
 ( 
 part_id 
 ) 
  
 DESTINATION 
  
 KEY 
  
 ( 
 dependent_part_id 
 ) 
  
 REFERENCES 
  
 Parts 
 ( 
 part_id 
 ) 
  
 );

Example: Schemaless data transition

Schemaless data management lets you create a flexible graph definition without predefined node and edge types. While schemaless data management provides flexibility, you might need to transition to a more formal structure as your data becomes more defined. A more formal structure exposes the graph's node and edge relationships, labels, and properties in the schema, which reduces the need for manual data exploration to understand the graph schema.

You can use views to formalize the node and edge types without migrating your underlying data. For example, you can transition from a typical schemaless model that uses canonical GraphNode and GraphEdge tables. To do this, you create views that extract the data from your schemaless tables:

Define a view for each node and edge type you want to formalize (for example, Person or WorksFor ). In the view, filter the data by its label (for example, WHERE n_label = "person" ) and cast the properties from the JSON column to specific data types (for example, STRING(prop.name) AS name ).
Define a new property graph where NODE TABLES and EDGE TABLES reference the typed views you just created.

A schemaless graph provides better performance than a formalized graph for some queries (for example, a quantified path pattern with multiple edge types). If formalized metadata is important for your use case, then you can use views to transition from a schemaless graph to a typed schema. You can also choose to use a schemaless graph for some use cases and a typed schema graph for other use cases. For more information, see Choose a schema design based on graph queries .

The following example demonstrates the workflow for transitioning from a schemaless to a formalized graph in four steps:

Define the canonical GraphNode and GraphEdge tables for the schemaless model.
Create an initial, flexible graph on those schemaless tables.
Define typed views ( Person , Company , WorksFor ) that extract and formalize the data from the schemaless tables.
Create the final, strongly-typed graph that uses these views as its node and edge tables.

  -- 1. Create the canonical tables for a schemaless model. 
 CREATE 
  
 TABLE 
  
 GraphNode 
  
 ( 
  
 id 
  
 INT64 
  
 NOT 
  
 NULL 
 , 
  
 label 
  
 STRING 
 ( 
 MAX 
 ) 
  
 NOT 
  
 NULL 
 , 
  
 properties 
  
 JSON 
 ) 
  
 PRIMARY 
  
 KEY 
  
 ( 
 id 
 ); 
 CREATE 
  
 TABLE 
  
 GraphEdge 
  
 ( 
  
 id 
  
 INT64 
  
 NOT 
  
 NULL 
 , 
  
 dest_id 
  
 INT64 
  
 NOT 
  
 NULL 
 , 
  
 edge_id 
  
 INT64 
  
 NOT 
  
 NULL 
 , 
  
 label 
  
 STRING 
 ( 
 MAX 
 ) 
  
 NOT 
  
 NULL 
 , 
  
 properties 
  
 JSON 
 ) 
  
 PRIMARY 
  
 KEY 
  
 ( 
 id 
 , 
  
 dest_id 
 , 
  
 edge_id 
 ), 
  
 INTERLEAVE 
  
 IN 
  
 PARENT 
  
 GraphNode 
 ; 
 -- 2. Define a schemaless graph. 
 CREATE 
  
 PROPERTY 
  
 GRAPH 
  
 FinGraph 
  
 NODE 
  
 TABLES 
  
 ( 
  
 GraphNode 
  
 DYNAMIC 
  
 LABEL 
  
 ( 
 label 
 ) 
  
 DYNAMIC 
  
 PROPERTIES 
  
 ( 
 properties 
 ) 
  
 ) 
  
 EDGE 
  
 TABLES 
  
 ( 
  
 GraphEdge 
  
 SOURCE 
  
 KEY 
  
 ( 
 id 
 ) 
  
 REFERENCES 
  
 GraphNode 
 ( 
 id 
 ) 
  
 DESTINATION 
  
 KEY 
  
 ( 
 dest_id 
 ) 
  
 REFERENCES 
  
 GraphNode 
 ( 
 id 
 ) 
  
 DYNAMIC 
  
 LABEL 
  
 ( 
 label 
 ) 
  
 DYNAMIC 
  
 PROPERTIES 
  
 ( 
 properties 
 ) 
  
 ); 
 -- 3. Define typed views that extract and formalize the data. 
 --    Convert JSON fields to primitive types (for example, INT64, STRING) to 
 --    ensure type safety. 
 CREATE 
  
 VIEW 
  
 Person 
  
 SQL 
  
 SECURITY 
  
 INVOKER 
  
 AS 
  
 SELECT 
  
 n 
 . 
 id 
 , 
  
 STRING 
 ( 
 n 
 . 
 properties 
 . 
 name 
 ) 
  
 AS 
  
 name 
 , 
  
 INT64 
 ( 
 n 
 . 
 properties 
 . 
 age 
 ) 
  
 AS 
  
 age 
  
 FROM 
  
 GraphNode 
  
 n 
  
 WHERE 
  
 n 
 . 
 label 
  
 = 
  
 "person" 
 ; 
 CREATE 
  
 VIEW 
  
 Company 
  
 SQL 
  
 SECURITY 
  
 INVOKER 
  
 AS 
  
 SELECT 
  
 n 
 . 
 id 
 , 
  
 STRING 
 ( 
 n 
 . 
 properties 
 . 
 name 
 ) 
  
 AS 
  
 company_name 
 , 
  
 BOOL 
 ( 
 n 
 . 
 properties 
 . 
 is_public 
 ) 
  
 AS 
  
 is_public 
  
 FROM 
  
 GraphNode 
  
 n 
  
 WHERE 
  
 n 
 . 
 label 
  
 = 
  
 "company" 
 ; 
 CREATE 
  
 VIEW 
  
 WorksFor 
  
 SQL 
  
 SECURITY 
  
 INVOKER 
  
 AS 
  
 SELECT 
  
 e 
 . 
 id 
  
 AS 
  
 person_id 
 , 
  
 e 
 . 
 dest_id 
  
 AS 
  
 company_id 
 , 
  
 e 
 . 
 edge_id 
  
 AS 
  
 edge_id 
 , 
  
 STRING 
 ( 
 e 
 . 
 properties 
 . 
 since 
 ) 
  
 AS 
  
 since 
  
 FROM 
  
 GraphEdge 
  
 e 
  
 WHERE 
  
 e 
 . 
 label 
  
 = 
  
 "worksfor" 
 ; 
 -- 4. Create the final, formalized graph from the typed views. 
 CREATE 
  
 PROPERTY 
  
 GRAPH 
  
 typed_formalized_graph 
  
 NODE 
  
 TABLES 
  
 ( 
  
 Person 
  
 KEY 
 ( 
 id 
 ) 
  
 PROPERTIES 
  
 ( 
 name 
 , 
  
 age 
 ), 
  
 Company 
  
 KEY 
 ( 
 id 
 ) 
  
 PROPERTIES 
  
 ( 
 company_name 
 , 
  
 is_public 
 ) 
  
 ) 
  
 EDGE 
  
 TABLES 
 ( 
  
 WorksFor 
  
 KEY 
 ( 
 person_id 
 , 
  
 company_id 
 , 
  
 edge_id 
 ) 
  
 SOURCE 
  
 KEY 
  
 ( 
 person_id 
 ) 
  
 REFERENCES 
  
 Person 
 ( 
 id 
 ) 
  
 DESTINATION 
  
 KEY 
  
 ( 
 company_id 
 ) 
  
 REFERENCES 
  
 Company 
 ( 
 id 
 ) 
  
 PROPERTIES 
  
 ( 
 since 
 ) 
  
 );

What's next

Learn about the Spanner Graph schema .
Learn about best practices for designing a Spanner Graph schema .