spanner-relational-to-graph
Analyzes existing Spanner relational schemas on Google Cloud (DDL, foreign keys, interleaved tables, and junction tables) and generates `CREATE PROPERTY GRAPH` DDL statements (`NODE TABLES`, `EDGE TABLES`) without modifying underlying relational tables. Use when mapping existing Spanner tables to a Spanner Graph, identifying node and edge candidates from relational tables, creating SQL bridge views for implicit `ARRAY`/`JSON` or type-mismatched relationships, mapping polymorphic or self-referential tables, or troubleshooting `CREATE PROPERTY GRAPH` DDL compiler errors. Don't use for greenfield
Try your first task
A source-grounded starter task has not been verified for this skill yet.
SKILL.md
name: spanner-relational-to-graphmetadata: version: "1.0.0" category: Databasesdescription: >- Analyzes existing Spanner relational schemas on Google Cloud (DDL, foreign keys, interleaved tables, and junction tables) and generates `CREATE PROPERTY GRAPH` DDL statements (`NODE TABLES`, `EDGE TABLES`) without modifying underlying relational tables. Use when mapping existing Spanner tables to a Spanner Graph, identifying node and edge candidates from relational tables, creating SQL bridge views for implicit `ARRAY`/`JSON` or type-mismatched relationships, mapping polymorphic or self-referential tables, or troubleshooting `CREATE PROPERTY GRAPH` DDL compiler errors. Don't use for greenfield physical table design (use spanner-graph-schema-best-practices) or tuning slow ISO GQL queries (use spanner-graph-query-tuning).Spanner Relational-to-Property-Graph Schema Conversion
Convert an existing Spanner relational schema into a valid, production-ready
CREATE OR REPLACE PROPERTY GRAPH DDL definition while keeping existing
relational tables intact.
Usage Attribution
MUST READ: Before executing any gcloud, REST API, or MCP command, you MUST
read and follow the instructions in
Usage Attribution.
Prerequisites & Cloud Environment Setup
When inspecting a live Spanner instance or verifying DDL (see spanner-basics
for general Spanner instance provisioning and MCP setup):
- Google Cloud SDK (
gcloud): Ensure the CLI is installed (see Install the Google Cloud SDK). - Project, Billing & API: Verify an active Google Cloud project with
billing enabled (required even when provisioning a free trial instance) is
configured (
gcloud config set project {project_id}) and, after the user confirms, enable the Spanner API (gcloud services enable spanner.googleapis.com). Ask the user before changing the active project, billing, or enabled APIs, or creating or deleting instances, because these commands change the user's Google Cloud environment and can incur costs. - Authentication & IAM Roles: Authenticate via
gcloud auth loginorgcloud auth application-default login(orGOOGLE_APPLICATION_CREDENTIALSfor service accounts). Listing instances/databases requiresroles/spanner.viewer, inspecting DDL requiresroles/spanner.databaseReader, and applyingCREATE VIEW/CREATE PROPERTY GRAPHDDL requiresroles/spanner.databaseAdmin. - Instance Region & Pricing: For sandbox test instances, default to
regional-us-central1unless specified otherwise (see Spanner instance configurations, Google Cloud locations, Spanner pricing, and Google Cloud pricing).
Core Principles
- Zero Data Duplication: Spanner Graph maps existing relational tables or
SQL views directly into graph nodes and edges via
CREATE PROPERTY GRAPHmetadata—without copying data or breaking existing SQL applications. - Preserve Existing Tables: Treat all existing relational tables as
read-only inputs. Do not drop, rename, or alter existing columns or primary
keys when adding a property graph view. If an implicit relationship requires
key transformation (for example, unnesting an array or casting mismatched key
types), define a new SQL bridge view
(
CREATE VIEW ... SQL SECURITY INVOKER AS ...by default, orSQL SECURITY DEFINERwhen row-level access control is needed) and bind the edge or node to that view. - 100% Table Accounting: Account for every relational table in the input
schema—either mapped into
NODE TABLES/EDGE TABLESor explicitly listed as excluded (such as pure system audit logs, schema migration state tables, or unlinked lookup tables).
Step-by-Step Conversion Workflow
Step 1: Discover or Parse the Relational Schema
-
If the user provides DDL statements in the prompt, parse all
CREATE TABLE,PRIMARY KEY,INTERLEAVE IN PARENT, andFOREIGN KEYdefinitions directly. -
If the user provides a live Spanner instance and database ID (or a Google Cloud Console URL containing
instances/{instance_id}/databases/{database_id}?project={project_id}), extractproject,instance, anddatabaseand retrieve the live DDL using the read-onlygcloudcommand:gcloud spanner databases ddl describe {database_id} \ --instance={instance_id} \ --project={project_id}- Live-Database Safety Rule:
gcloud spanner databases ddl describeis read-only and safe to run automatically, whereas applyingCREATE VIEWorCREATE OR REPLACE PROPERTY GRAPHviagcloud spanner databases ddl updatemutates the live database—always ask for explicit user confirmation before runninggcloud spanner databases ddl update(and state this confirmation requirement clearly in your response even ifddl describefails due to missing credentials or network access). - See references/cloud-spanner-guide.md for environment discovery, emulator setup, and verification workflows.
- Live-Database Safety Rule:
Step 2: Classify Relational Tables into Nodes and Edges
Apply the structural classification rules in references/patterns.md:
- Core Entity Tables ->
NODE TABLES:- Tables representing domain nouns (
Users,Accounts,Products,Devices,Documents) become node tables. - Use the table's full
PRIMARY KEYas the nodeKEY (...).
- Tables representing domain nouns (
- Pure or Attributed M:N Junction Tables ->
EDGE TABLES:- Tables that link two entity keys (such as
ProjectAssignments(emp_id, project_id)orWireTransfers(transfer_id)withsrc_account_idanddst_account_id) become edge tables. - If the table also represents an entity referenced by other tables (for
example,
Ordersis a child ofCustomersand a parent ofOrderItems), mapOrdersas both aNODE TABLE(Orders AS OrderNode) and anEDGE TABLE(Orders AS PlacedOrder).
- Tables that link two entity keys (such as
- 1:N Foreign Keys and Interleaved Parent-Child Tables ->
EDGE TABLES(Table AS Edge):- An entity table
Bwith a foreign key (or interleaved parent key prefix) referencingAcan simultaneously act as aNODE TABLE(B AS NodeB) and anEDGE TABLE(B AS EdgeBA) connectingBtoA(orAtoB). - Self-Referential Hierarchies: A table like
Employees(emp_id, manager_id)maps toEmployees AS EmployeeinNODE TABLESandEmployees AS ReportsToEdgeinEDGE TABLES(SOURCE KEY (emp_id) REFERENCES Employee(emp_id)andDESTINATION KEY (manager_id) REFERENCES Employee(emp_id)).
- An entity table
- Implicit Relationships (Arrays, JSON, Polymorphic Keys) -> Bridge Views:
- When relationships are embedded in
ARRAY<INT64>columns, JSON fields, or type-mismatched keys (INT64vs.STRING), create a SQL bridge view that flattens and projects(edge_key, src_key, dst_key)and register the view inEDGE TABLESwith an explicitKEY (...). - View Security (
INVOKERvs.DEFINER): Every Spanner view requires aSQL SECURITYclause.SQL SECURITY INVOKERis the common default when callers already hold permissions on the underlying tables, whereasSQL SECURITY DEFINERis supported when you need row-level / fine-grained access control so callers can query the graph view without direct base-table grants. - View Key Uniqueness &
validate_element_key_uniqueness: By default, Spanner syntactically verifies that a view's declaredKEYis unique (either a single base table's primary key or matched bySELECT DISTINCT/GROUP BY), failing otherwise withcannot verify the specified element key ... is unique. UseSELECT DISTINCT(orGROUP BYthe key columns) when inputs can contain duplicate keys (such as duplicate elements inside anARRAY); if you know the view'sKEYis already semantically unique (for example, a key-preservingJOINorUNNEST(...) WITH OFFSET), you can instead appendOPTIONS (validate_element_key_uniqueness = false)toCREATE PROPERTY GRAPHto skip syntactic validation without extraDISTINCT/GROUP BYoverhead (you remain responsible for ensuring keys are unique in data).
- When relationships are embedded in
Step 3: Decide Graph Scoping (Unified vs. Multi-Graph)
- Default to 1 Unified Property Graph when tables share a connected domain.
- Recommend Multiple Property Graphs only when the database contains completely disconnected business domains or exceeds ~12+ distinct node/edge types where domain-specific subgraphs improve query ergonomics.
Step 4: Author the CREATE OR REPLACE PROPERTY GRAPH DDL
Follow the exact syntax and structural invariants in references/spanner-graph-ddl-syntax.md and references/structural-invariants.md:
CREATE OR REPLACE PROPERTY GRAPH GraphName
NODE TABLES (
TableName AS NodeAlias
KEY (pk_col1, pk_col2)
LABEL NodeLabel
)
EDGE TABLES (
EdgeTableName AS EdgeAlias
KEY (edge_pk_col1, edge_pk_col2)
SOURCE KEY (src_fk_col1) REFERENCES SourceNodeAlias(src_pk_col1)
DESTINATION KEY (dst_fk_col1) REFERENCES DestNodeAlias(dst_pk_col1)
LABEL EdgeLabel
);
Critical DDL Invariants Checklist
NODE TABLESmust be declared beforeEDGE TABLES.- Every
NodeAliasandEdgeAliasmust be globally unique across the entireCREATE PROPERTY GRAPHstatement (if a table is used as both a node and an edge, give them distinct aliases, e.g.,Orders AS OrderNodeandOrders AS PlacedOrderEdge). - Decouple Edge Identity (
KEY) from Endpoint Topology (SOURCE KEY/DESTINATION KEY):KEY (...)uniquely identifies the edge row in the backing table or view (for example,KEY (transfer_id)orKEY (src_id, dst_id, transfer_ts)), whileSOURCE KEYandDESTINATION KEYspecify the endpoints. Spanner enforces uniqueness: on a table, a customKEYmust contain all primary key columns or match aUNIQUEindex (soKEY (src_id, dst_id, transfer_ts)on a table whose primary key istransfer_idfails unless such an index exists). Rows where anyKEY,SOURCE KEY, orDESTINATION KEYcolumn isNULLare silently skipped byMATCH.- In
SOURCE KEY (...) REFERENCES NodeAlias(...)andDESTINATION KEY (...) REFERENCES NodeAlias(...), the target afterREFERENCESmust be theNodeAlias(not the underlying relational table name if an alias was used, and not theLABELname), and the column count and data types must match the referenced node'sKEY1-to-1. Spanner does not always enforce the count: with an explicit column list it accepts a partial composite key, and each edge then silently matches every node sharing that value.
- Property Exposure (Default
ALL COLUMNSvs. ExplicitPROPERTIES (...)/NO PROPERTIES):- By default, omitting the
PROPERTIESclause (implicitPROPERTIES ALL COLUMNS) exposes all columns of the input table and is concise when a table has its own label and non-conflicting column types. - Use explicit
PROPERTIES (col1 [AS alias], ...),PROPERTIES ALL COLUMNS EXCEPT (...), orNO PROPERTIESin four relational-conversion cases: (1) cross-table property type conflicts (aliasing colliding column names), (2) sharedLABELs across tables, (3) dualTable AS Node+Table AS Edgeor pure junction mappings (NO PROPERTIESor edge-only columns so node attributes are not duplicated onto edges), and (4) excluding internal/sensitive columns or avoiding duplicate exposure of aDYNAMIC PROPERTIES (json_col)column (PROPERTIES ALL COLUMNS EXCEPT (json_col)). - If two elements share the same
LABELname, they must expose the exact same set of property names and data types under that label. - A property name must have one data type across the whole graph, even
under different labels or on node vs. edge tables (for example,
id INT64in one table andid STRINGin another fails withThe property declaration of 'id' has type conflicts). Alias (id AS user_id) orCASTone side.
- By default, omitting the
- Optional Reverse Edge Alias (Naming Only):
- A single physical edge table can be registered a second time in
EDGE TABLESunder a reverse alias (swappingSOURCE KEYandDESTINATION KEY) when the domain needs a distinct directional label. Don't add one for performance: for undirected traversals use-[e:LABEL]-plus a reverse index; querying a swapped-key alias as a label union forces a union across two edge definitions at every hop, which is slower than-[e:LABEL]-on quantified paths.
- A single physical edge table can be registered a second time in
Step 5: Verify the DDL and Provide Recommendations
- Verify the DDL (when a target Spanner database or local emulator is available; see references/cloud-spanner-guide.md). Never apply DDL to a shared or production database without explicit user confirmation.
- Present a Structured 5-Part Deliverable (modeled in
references/synthesized-conversions.md):
- Part 1: Graph Extension Summary & Mapping Matrix (100% accounting of included and excluded tables).
- Part 2: Complete Executable Spanner Graph DDL (any required
CREATE VIEW ... SQL SECURITY {INVOKER | DEFINER}statements followed byCREATE OR REPLACE PROPERTY GRAPH). - Part 3: Performance & Indexing Recommendations (such as
reverse-traversal secondary indexes
ON EdgeTable (dst_key, src_key) INTERLEAVE IN DestNodeTableorSTORING (...), simplifying over-wide composite keys on junction tables, and high-degree hub foreign-key considerations). - Part 4: Sample ISO GQL Queries using
GRAPH GraphName MATCH ... RETURN ...(see references/gql-cheat-sheet.md). - Part 5: Schema Verification Status.
Reference Documentation
- Spanner Discovery Guide: Extracting
DDL from Spanner via
gcloud, parsing console URLs, and validatingCREATE PROPERTY GRAPHon a sandbox or Spanner Emulator. - Relational-to-Graph Mapping Patterns: Detailed rules for classifying entity tables, junction tables, interleaved hierarchies, self-referential FKs, polymorphic tables, and SQL bridge views.
- Spanner Graph DDL Syntax: Exact
BNF grammar and syntax rules for
CREATE PROPERTY GRAPH,NODE TABLES,EDGE TABLES,KEY, andPROPERTIES. - Structural Invariants: How to prevent and fix the 8 most common Spanner Graph DDL mistakes (alias collisions, shared-label and graph-wide property type conflicts, partial or mismatched key references, unverifiable view keys, and custom key uniqueness), with the actual Spanner error messages.
- ISO GQL Cheat Sheet: Quick syntax
reference for writing valid Spanner Graph ISO GQL queries (
MATCH,WHERE/FILTER, quantified paths{m,n},NEXT,RETURN). - Synthesized Examples: Four complete relational-to-property-graph conversion examples (E-Commerce, Financial AML, Enterprise Org Matrix, and Threat Intelligence).
- Official Spanner Graph Schema Design Documentation: Spanner Graph schema design best practices
If you need product information that's not found in these references, use the
search_documents tool of the Developer Knowledge MCP server.