Relate loads CSV files with LOAD CSV, so Parquet files need converting first. This guide converts two Parquet files to CSV with DuckDB, then loads them into a Relate graph of people and the projects they work on.
You need the relate command line tool and the duckdb command line tool. The example files are small, so you can follow along in an empty directory.
1. Create the Parquet files
If you have Parquet files already, skip this step and use your own names in the steps that follow. Otherwise, make a data directory and have DuckDB write two files into it, one of people and one of who works on which project since when:
mkdir data
duckdb -c "COPY (SELECT * FROM (VALUES ('Ada', 'engineer'), ('Grace', 'engineer'), ('Noor', 'maintainer')) AS t(name, role)) TO 'data/people.parquet' (FORMAT parquet)"
duckdb -c "COPY (SELECT * FROM (VALUES ('Ada', 'relate', 'Rust', 2024), ('Grace', 'relate', 'Rust', 2023), ('Grace', 'opencypher-tck', 'Gherkin', 2022)) AS t(person, project, language, since)) TO 'data/works_on.parquet' (FORMAT parquet)"2. Convert them to CSV
LOAD CSV reads files with a header row, so write each Parquet file out as CSV. Relate only reads files under one directory, so put them in a new import directory:
mkdir import
duckdb -c "COPY (SELECT * FROM 'data/people.parquet') TO 'import/people.csv' (HEADER, DELIMITER ',')"
duckdb -c "COPY (SELECT * FROM 'data/works_on.parquet') TO 'import/works_on.csv' (HEADER, DELIMITER ',')"3. Preview the rows
Read a file without writing anything. With no --database, Relate opens an in-memory store that is gone when the command exits. --load-csv-dir names the one directory Relate may read, and WITH HEADERS makes each row a map keyed by the header names:
relate --load-csv-dir ./import --mode table "LOAD CSV WITH HEADERS FROM 'file:///works_on.csv' AS row RETURN row.person AS person, row.since AS since, valueType(row.since) AS type"+---------+--------+-------------------+
| person | since | type |
+---------+--------+-------------------+
| "Ada" | "2024" | "STRING NOT NULL" |
| "Grace" | "2023" | "STRING NOT NULL" |
| "Grace" | "2022" | "STRING NOT NULL" |
+---------+--------+-------------------+Every value LOAD CSV reads is a string, including since, which was an integer in the Parquet file. The next steps convert it as they store it.
4. Create the nodes
Load the people into a new single-file store, team.rl. MERGE matches a person who already exists, so running the statement twice creates nothing new, and RETURN count(*) reports the rows read:
relate --database ./team.rl --load-csv-dir ./import --mode table "LOAD CSV WITH HEADERS FROM 'file:///people.csv' AS row MERGE (p:Person {name: row.name}) SET p.role = row.role RETURN count(*) AS people"+--------+
| people |
+--------+
| 3 |
+--------+5. Create the relationships
The second file connects people to projects. toInteger(row.since) stores a number, so a later ORDER BY since or since >= 2023 compares numbers instead of strings:
relate --database ./team.rl --load-csv-dir ./import --mode table "LOAD CSV WITH HEADERS FROM 'file:///works_on.csv' AS row MATCH (p:Person {name: row.person}) MERGE (proj:Project {name: row.project}) SET proj.language = row.language MERGE (p)-[w:WORKS_ON]->(proj) SET w.since = toInteger(row.since) RETURN count(w) AS relationships"+---------------+
| relationships |
+---------------+
| 3 |
+---------------+The other conversion functions are toFloat and toBoolean, plus date() and datetime() for ISO 8601 strings. An empty field reads as '', and toInteger('') is null, so an empty cell stores no property.
6. Query the graph
Open the file read-only and ask who works on what, earliest first:
relate --database ./team.rl --read-only --mode table "MATCH (p:Person)-[w:WORKS_ON]->(proj:Project) RETURN p.name AS person, proj.name AS project, w.since AS since ORDER BY since"+---------+------------------+-------+
| person | project | since |
+---------+------------------+-------+
| "Grace" | "opencypher-tck" | 2022 |
| "Grace" | "relate" | 2023 |
| "Ada" | "relate" | 2024 |
+---------+------------------+-------+Relate stores its own snapshots in Parquet by default, and Clone and convert a store covers those. Import a CSV covers LOAD CSV in full.
Try it yourself
GraphAcademy's Cypher Fundamentals course teaches you to read and write a graph with Cypher.
Comments (0)
Loading comments...