Write data from tables to a graph analysis engine

Graph analysis engines let you model and query complex relationships such as network topologies, device connections, fault propagation paths, behavior chains, and fund transfers. This topic walks you through importing data from AnalyticDB for PostgreSQL tables into a graph analysis engine so you can run Cypher queries on the data.

Important

These operations cannot be performed using Data Management (DMS) due to DMS client limitations.

Version requirements

AnalyticDB for PostgreSQL V7.0 instances running version V7.2.1.0 or later.

Note

To check your minor version, view the minor version on the Basic Information page of your instance in the AnalyticDB for PostgreSQL console. If your instance is on an earlier version, update the minor engine version.

Prerequisites

Before you begin, ensure that you have:

(Required) The age extension installed on your instance. Without this extension, you cannot create graphs or run Cypher queries.

Note
The age extension is automatically installed on V7.0 instances with minor engine version 7.2.1.4 or later.
(Required) Permission to execute functions. Due to a version-related issue, submit a ticket to request this permission before proceeding.
(Optional) ag_catalog added to the search path. This simplifies queries by letting you omit the schema prefix on AGE functions. Use one of the following methods:
- Session-level (applies to the current session only):
```
SET search_path TO "$user", public, ag_catalog;
```
- Database-level (applies permanently to the database):
```
ALTER DATABASE <database_name> SET search_path TO "$user", public, ag_catalog;
```
(Optional) Permissions granted to other users on the graph extension. Use the initial account or a privileged user with the RDS_SUPERUSER role:
```
GRANT USAGE ON SCHEMA ag_catalog TO <username>;
```

How it works

This topic uses a sample graph with two node types and one relationship to demonstrate the import process:

The three source tables map to graph entities as follows:

Source table	Graph entity type	Label
`place_raw`	Node	`Place`
`organization_raw`	Node	`Organization`
`organization_isLocatedIn_place_raw`	Edge	`IS_LOCATED_IN`

The overall process is:

Create source tables and insert raw data
Create helper functions to convert integer IDs to AGE's internal graphid format
Create the graph and define node and edge labels
Import node data from the source tables
Import edge data from the source tables
Verify the imported data with a Cypher query

Step 1: Create tables and insert data

Create the three source tables and insert sample data.

Create the organization_raw table:

CREATE TABLE IF NOT EXISTS organization_raw (
    id BIGINT,
    name TEXT,
    url TEXT,
    type TEXT
);

INSERT INTO organization_raw VALUES
('0', 'company', 'http://dbpedia.org/resource/Kam_Air', 'Kam_Air'),
('1', 'company', 'http://dbpedia.org/resource/Balkh_Airlines', 'Balkh_Airlines'),
('8', 'company', 'http://dbpedia.org/resource/Khalifa_Airways', 'Khalifa_Airways'),
('9', 'company', 'http://dbpedia.org/resource/Tassili_Airlines', 'Tassili_Airlines');

Create the place_raw table:

CREATE TABLE IF NOT EXISTS place_raw (
    id BIGINT,
    name TEXT,
    url TEXT,
    type TEXT
);

INSERT INTO place_raw VALUES
('59', 'Afghanistan', 'http://dbpedia.org/resource/Afghanistan', 'country'),
('60', 'Algeria', 'http://dbpedia.org/resource/Algeria', 'country');

Create the organization_isLocatedIn_place_raw table:

CREATE TABLE IF NOT EXISTS organization_isLocatedIn_place_raw (
    start_id BIGINT,
    end_id BIGINT
);

INSERT INTO organization_isLocatedIn_place_raw VALUES ('0','59'), ('1','59'), ('8','60'), ('9','60');

Step 2: Create helper functions

Apache AGE (AGE) stores node and edge IDs using an internal graphid type that encodes the label ID and a sequential integer. The two helper functions below convert your integer IDs into this format and retrieve the sequence name for updating sequence values after bulk inserts. These functions are required by the INSERT statements in Steps 4 and 5.

-- Returns the starting graphid value for a given label.
-- Used to convert integer IDs from your source tables into AGE graphids.
CREATE OR REPLACE FUNCTION age_name_to_idx_start(graph_name text, kind_name text, label_name text)
  RETURNS bigint
  AS 'SELECT id::bigint<<48 FROM ag_catalog.ag_label WHERE kind = kind_name AND name = label_name AND graph = (SELECT graphid FROM ag_catalog.ag_graph WHERE name = graph_name)'
  LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE;

-- Returns the fully qualified sequence name for a given label.
-- Used to update the sequence after a bulk insert so AGE assigns correct IDs to future nodes.
CREATE OR REPLACE FUNCTION age_name_to_seq(graph_name text, kind_name text, label_name text)
  RETURNS text
  AS $$SELECT graph_name || '."' || seq_name || '"' FROM ag_catalog.ag_label WHERE kind = kind_name and name = label_name and graph = (SELECT graphid FROM ag_catalog.ag_graph WHERE name = graph_name)$$
  LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE;

Step 3: Create a graph and labels

Create the graph, then define the node labels and edge labels.

-- Create the graph.
SELECT ag_catalog.create_graph('social_network');

-- Create node labels.
SELECT ag_catalog.create_vlabel('social_network', 'Place');
SELECT ag_catalog.create_vlabel('social_network', 'Organization');

-- Create the edge label.
SELECT ag_catalog.create_elabel('social_network', 'IS_LOCATED_IN');

Step 4: Import node data

The INSERT statements below follow the same pattern:

Column	Expression	Purpose
First (ID)	`age_name_to_idx_start(...) + id`	Converts your integer `id` to an AGE `graphid`
Second (properties)	`row_to_json(...)::ag_catalog.agtype`	Serializes the remaining columns as a property map

After each bulk insert, setval() advances the sequence so AGE assigns correct IDs to future nodes.

-- Import Place nodes.
INSERT INTO social_network."Place"
  SELECT
  (age_name_to_idx_start('social_network', 'v', 'Place') + id)::text::ag_catalog.graphid,
  row_to_json((SELECT x FROM (SELECT name, url, type) x))::text::ag_catalog.agtype
  FROM place_raw;

-- Advance the Place sequence to the next available ID.
SELECT setval(age_name_to_seq('social_network', 'v', 'Place'), (SELECT max(id) + 1 FROM place_raw));


-- Import Organization nodes.
INSERT INTO social_network."Organization"
  SELECT
  (age_name_to_idx_start('social_network', 'v', 'Organization') + id)::text::ag_catalog.graphid,
  row_to_json((SELECT x FROM (SELECT name, url, type) x))::text::ag_catalog.agtype
  FROM organization_raw;

-- Advance the Organization sequence to the next available ID.
SELECT setval(age_name_to_seq('social_network', 'v', 'Organization'), (SELECT max(id) + 1 FROM organization_raw));

Step 5: Import edge data

Each edge connects an Organization node (start) to a Place node (end). The start_id and end_id from the source table are converted to graphid values using the same pattern as the node import.

-- Import IS_LOCATED_IN edges.
-- start_id references the Organization node; end_id references the Place node.
INSERT INTO social_network."IS_LOCATED_IN"(start_id, end_id, properties)
SELECT
(age_name_to_idx_start('social_network', 'v', 'Organization') + start_id)::text::ag_catalog.graphid,
(age_name_to_idx_start('social_network', 'v', 'Place') + end_id)::text::ag_catalog.graphid,
'{}'::text::ag_catalog.agtype
FROM organization_isLocatedIn_place_raw;

Step 6: Verify the data

Run the following Cypher query to confirm that the nodes and edges were imported correctly. The query matches all Organization nodes connected to Place nodes via the IS_LOCATED_IN relationship and returns up to 5 results.

SELECT * FROM ag_catalog.cypher('social_network', $$
  MATCH (o:Organization)-[:IS_LOCATED_IN]->(p:Place)
  RETURN o, p
  LIMIT 5
  $$) AS (o ag_catalog.agtype, p ag_catalog.agtype);

If the import was successful, the query returns rows where each o column shows an Organization node and each p column shows the corresponding Place node.