Neo4j: Relationships

[Survival]

I am currently working on a project which aims to use graph databases, in particular Neo4j. My question concerns how to create the "Relationship" relations between the different nodes, for information, the data to be used is in CSV format, in this case, I will import data that is in .csv format, first we create the nodes based on this data, then we move on to creating the relationships between the different nodes.

I tried a lot of code but we didn't get the desired result regarding the creation of relationships between nodes.

I used the following code:

Load csv with headers from "file:///airports.csv" as airports create (a1: Airport {label: airports.label, city: airports.city, state: airports.state})
match (n) return (n)

Load csv with headers from "file:///flights.csv" as flights create (n: Flights {flight: flights.flight, airline: flights.airline, capacity: flights.capacity})
match(n) return(n)

while the nodes are created. but neither do relationships. in addition I had the following message: (no changes, no records) this after the execution of the following code:

Load csv with headers from "file:///flights.csv" as flights match (a: Flights {flight: flights.flight}), (b: Airport {label: flights.arrive}) create (a) –[r : Arrivals] -> (b)
match (n) return (n)

Load csv with headers from "file:///flights.csv" as flights match (a: Flight { flight: flights. flight}), (b: Airport {label: flights. depart}) create (a) –[r : Departures] -> (b)

match (n) return (n)

To inform you, I have two tables of data whose extension .csv one for airports and the other for flights. How should we create the relationship that exists between airports and flights? I don't know where is the error.

[Cobra]

Hello @Survival 🙂

Can you share CSV files?

Regards,
Cobra

[Survival]

neo4j relationship

I thank you for having answered me it is now or I saw your message following health reasons.

according to your post do you want me to send you the csv files i am working with

Pending a favorable response, please sir receive my best regards.

[Cobra]

You can upload CSV files here.

[Survival]

Continuation of the previous message

[Survival]

Hello,

Thank you sir for answering me.
On https://community.neo4j.com/ (this Neo4j community) we cannot load files with extension .csv I thought of another solution and take screenshots and put them in .jpg image format in order to send them to you to display the content of my own .csv files.
Please accept sir my best regards.

[Cobra]

Hello @Survival 🙂

I took the liberty of optimizing the queries and changing the data model a bit. Also, I put all column names in CSV files in lowercase.

// Create unique constraints
CREATE CONSTRAINT constraint_Airport IF NOT EXISTS FOR (airport:Airport) REQUIRE airport.label IS UNIQUE;
CREATE CONSTRAINT constraint_Flight IF NOT EXISTS FOR (flight:Flight) REQUIRE flight.flight IS UNIQUE;
// Create Airport nodes
LOAD CSV WITH HEADERS FROM "file:///airports.csv" AS line 
MERGE (airport:Airport {label: line.label}) 
SET airport += {
	city: line.city, 
	state: line.state
};
// Create Flight nodes
LOAD CSV WITH HEADERS FROM "file:///flights.csv" AS line 
MERGE (flight:Flight {flight: line.flight}) 
SET flight += {
	airline: line.airline, 
	capacity: line.capacity
};
// Create relationships
LOAD CSV WITH HEADERS FROM "file:///flights.csv" AS line 
MATCH (flight:Flight {flight: line.flight}) 
MATCH (airport_start:Airport {label: line.depart}) 
MATCH (airport_end:Airport {label: line.arrive}) 
MERGE (flight)-[:ORIGIN]->(airport_start) 
MERGE (flight)-[:DESTINATION]->(airport_end);

Regards,
Cobra

[Survival]

thank you sir for answering me so soon
when I ran the following code:

// Create Airport nodes
LOAD CSV WITH HEADERS FROM "file:///airports.csv" AS line
MERGE (airport:Airport {label: line.label})
SET airport += {
city: line.city,
state: line.state
};
I am displaying the following message :

Cannot merge the following node because of null property value for 'label': (:Airport {label: null})

while my airports.csv file that I have already sent you a screen print contains four lines in total
can you help me to solve this error, really it is an urgent work that I must submit it.

waiting for a favorable response, please sir accept my best regards

[Cobra]

Did you replace Label with label in your CSV files? Make sure the column names match the names in the query. In my answer, I didn't use your column names so you should check that.

[Survival]

I thank you Sir for having answered me, for the code that you sent me, I tried it in its entirety and I modified the labels according to my .csv table used while I have as results the non-display nodes and relationships.

For this purpose, I tried to modify the code in my way for the creation of the nodes and it worked well.

The code is the following:

// Creation of nodes related to airports

Load CSV With headers from “file:///airports.csv” as row Create (a:airports) set a= row {.label, .City, .State}

MATCH (a:airports) RETURN n LIMIT 4

// Creation of the nodes relating to the flights

Load CSV With headers from “file:///flights.csv” as row Create (b:flights) set b=row {.flight, .Depart, .Arrive, .Capacity}

MATCH (n:flights) RETURN n LIMIT 24

While I used your code to create the relationships, the code after modification of the labels according to my .csv tables is the following:

LOAD CSV WITH HEADERS FROM “file:///flights.csv” AS line

MATCH (flights: flights {flight: line. Flights})

MATCH (Depart: flights {Label: line.Depart})

MATCH (Arrive: flights {Label: line. Arrive})

MERGE (flights)-[: ORIGIN]-> (Depart]

MERGE (flights)-[: Destination]-> (Arrive);

when I got the following message: (no changes, no records)

according to the screen prints that I sent relating to my tables and the codes that I used and mentioned in this message, can you Sir show me where exactly the error is?

For information, at first, I used your code, which is the following:

// Create unique contraints

CREATE CONSTRAINT constraint_airports IF NOT EXISTS FOR (airports:Airports) REQUIRE airports.Label IS UNIQUE;

CREATE CONSTRAINT constraint_flights IF NOT EXISTS FOR (flights:flights) REQUIRE flights.flights IS UNIQUE;

Then I used the following code which didn't work, although I made appropriate corrections to its content according to my own .csv tables used.

// Create Airports nodes

LOAD CSV WITH HEADERS FROM file:///airports.csv as line

MERGE (airports: airports {Label: line. Label})

SET airport += {

              City: line. City,

State: line. State

};

//Create Flights nodes

LOAD CSV WITH HEADERS FROM file:///flights.csv AS line

MERGE (flights:flights {flight: line.flight})

SET flights += {

             Depart: line.Depart,

             Arrive: line.Arrive

             Capacity: line.Capacity

   };

Waiting for a favorable response, please Sir receive my best regards.

[Survival]

Hello,

I sent you the last time a message containing in detail the code that I executed.

sir, I would like you to answer me as soon as possible, because I have to submit this task soon.

cordial greetings.

[Cobra]

Can you share your CSV files please? You can replace the extension of the file by txt to upload them here normally.

[Cobra]

Hello @Survival,

I used these queries and they are working well. I don't have any errors. I'm using Neo4j 4.4.12.

// Create unique constraints
CREATE CONSTRAINT constraint_Airport IF NOT EXISTS FOR (airport:Airport) REQUIRE airport.label IS UNIQUE;
CREATE CONSTRAINT constraint_Flight IF NOT EXISTS FOR (flight:Flight) REQUIRE flight.flight IS UNIQUE;
// Create Airport nodes
LOAD CSV WITH HEADERS FROM "file:///airports.csv" AS line 
MERGE (airport:Airport {label: line.label}) 
SET airport += {
	city: line.city, 
	state: line.state
};
// Create Flight nodes
LOAD CSV WITH HEADERS FROM "file:///flights.csv" AS line 
MERGE (flight:Flight {flight: line.flight}) 
SET flight += {
	airline: line.airline, 
	capacity: line.capacity
};
// Create relationships
LOAD CSV WITH HEADERS FROM "file:///flights.csv" AS line 
MATCH (flight:Flight {flight: line.flight}) 
MATCH (airport_start:Airport {label: line.depart}) 
MATCH (airport_end:Airport {label: line.arrive}) 
MERGE (flight)-[:ORIGIN]->(airport_start) 
MERGE (flight)-[:DESTINATION]->(airport_end);

Here is the content of the airports.csv file:

label	city	state
DTW	Detroit	Michigan
ATL	Atlanta	Georgia
PIT	Pittsburguer	Pennsylvana
BOS	Boston	Massachussette

Here is the contents of the flight.csv file:

flight	airline	depart	arrive	capacity
23	DELTA	ATL	DTW	160
24	DELTA	DTW	ATL	160
27	DELTA	DTW	BOS	160
28	DELTA	BOS	DTW	160
35	DELTA	DTW	PIT	128
36	DELTA	PIT	DTW	128
37	DELTA	ATL	BOS	160
38	DELTA	BOS	ATL	160
43	DELTA	ATL	PIT	160
44	DELTA	PIT	ATL	160
45	DELTA	BOS	PIT	160
46	DELTA	PIT	BOS	160
101	Southwes	DTW	BOS	136
102	Southwes	BOS	DTW	136
103	Southwes	DTW	PIT	136
104	Southwes	PIT	DTW	136
1231	American	DTW	BOS	160
1232	American	DTW	PIT	160
1257	American	DTW	ATL	128

I don't know why it's not working on your computer.

Here is the resulting graph:

Best regards,
Cobra

[Survival]

thank you sir for answering me
please find attached the files whose extension is .txt.

can you sir show me how to transfer a file whose extension is .xlsx to a file in the extension is .csv
maybe the errors are caused when I transfer between files, ie from .xlsx to .csv.

cordial greetings

[Survival]

when attaching the .txt file I got an error message as demonstrated in the previous message

[Cobra]

It's not possible anymore but look at my last message, I created the same dataset as you and it works.

I don't know what else to say.

[Survival]

Hello,
I inform you sir that the code you sent me worked well the problem that I encountered last time is mainly due to the .csv format that I used, well I set the format and I rewrote the code you sent me and it worked fine.
I sincerely thank you.
Good for the moment I have another question to ask you if it is possible, based on the previous code, I wrote the following code:
// Create stop_times nodes
LOAD CSV WITH HEADERS FROM "file:///stop_times.csv" AS line
MERGE (stop_times:stop_times {stop_id:line.stop_id AND trip_id:line.trip_id})
SET stop_times+= {
arrival_time: line. arrival_time,
departure_time: line.departure_time,
stop_sequence: line.stop_sequence,
stop_headsign: line.stop_headsign
};
While I got the following message:

Neo.ClientError.Statement.SyntaxError: Variable `trip_id` not defined (line 2, column 56 (offset: 117))
"MERGE (stop_times:stop_times {stop_id:line.stop_id AND trip_id:line.trip_id})"

How should we settle it? I send you the tables used in the form of images for better understanding.
the question is when I will have a table whose primary key is the concatenation of two other keys which are primary keys each in its own table. in this case how can I express this using the cyfer language.
Pending a favorable response, please sir receive my best regards.

[glilienfield]

Your error is due to invalid syntax. Replace the ‘AND’ with a comma. 

I will let @Cobra continuing helping you with your solution. 

[Survival]

Hello,
I sincerely thank you sir for having answered me, I tried what you told me and I replaced the AND by the comma so I received the following message:
Neo.ClientError.Schema.ConstraintValidationFailed: Node(294) already exists with label `stop_times` and property `trip_id` = '1'
so, how should it be resolved?
waiting for a favorable response please accept sir my best regards

[glilienfield]

You have a uniqueness constraint on property ‘trip_id’ for label ‘stop_times’. In your code, you ‘merge’ on two properties: stop_id and trip_id. Your data must not have an existing node with that combination of values, so it is created. You get the violation, because you already have a node with the value of trip_id. 

MERGE (stop_times:stop_times {stop_id:line.stop_id AND trip_id:line.trip_id})

the solution is to match using only the combination of property that uniquely identifies that entity.  All other properties are added using ‘set’. 

assuming that ‘trip_id’ is your primary key for ‘stop_times’ label, you could modify your query to the following:

LOAD CSV WITH HEADERS FROM "file:///stop_times.csv" AS line
MERGE(stop_times:stop_times{trip_id:line.trip_id})
SET stop_times+= {

stop_id: line.stop_id,

arrival_time: line. arrival_time,
departure_time: line.departure_time,
stop_sequence: line.stop_sequence,
stop_headsign: line.stop_headsign
};

[Survival]

I thank you sir for having answered me I tried it and it worked well.

but I inform you sir that the primary key of the stop_times table has as primary key the combination of the two keys stop_id and trip_id so in the code which allows to create the unique constraints for each class I used the code below:

CREATE CONSTRAINT constraint_stop_times IF NOT EXISTS FOR (stop_time:stop_times) REQUIRE stop_time.trip_id IS UNIQUE;
CREATE CONSTRAINT constraint_stop_times IF NOT EXISTS FOR (stop_time:stop_times) REQUIRE stop_time.stop_id IS UNIQUE;

while according to the attached image it shows that if I take the trip_id column alone there are repeated cases while in the definition of the primary key the latter must not be repeated and if I take the column relating to stop_id only I will have the same case of the values which are repeated whereas if I combine these two columns I will have a unique primary key which checks the definition of the primary key the question is how to define the unique constraint on two columns whose combination forms our primary key of the "stop_times" table
Ok for that I used the following code:

CREATE CONSTRAINT constraint_stop_times IF NOT EXISTS FOR (stop_time:stop_times) REQUIRE stop_time.trip_id IS UNIQUE;
CREATE CONSTRAINT constraint_stop_times IF NOT EXISTS FOR (stop_time:stop_times) REQUIRE stop_time.stop_id IS UNIQUE;

in addition, the code you sent me is certainly successful, but logically I should not consider trip_id alone as the primary key of the "stop_times" table because there are repeating values.

In this case I would like to have a code that verifies this.

pending a favorable response, please accept sir my best regards.

[glilienfield]

Since your primary key for that label is a composite key consisting of trip_id and stop_id, you should drop the all constraints these two properties and create one node key constraint on the two properties jointly. 

CREATE CONSTRAINT stop_times_node_key IF NOT EXISTS FOR (n:stop_times) REQUIRE (n.stop_id, n.trip_id) IS NODE KEY

A node key constraint requires uniqueness and existence. It also creates a composite index. 

After doing this, your original code with the ‘merge’ using the two properties should work as you need it. 

[Survival]

thank you sir for answering me
I added the line of code you sent me regarding the constraint instead of the two lines of antecedent.

I attach below the complete code that I used as well as the results obtained.

to inform you about the stop_times table which includes the primary key composed when creating the relationship that exists between this table and the two tables trips and stops (in which its main key is composed of the two primary keys of these two tables: trips and stops, I got the following error message:

"Neo.DatabaseError.Statement.ExecutionFailed: java.net.URISyntaxException: Illegal character in path at index 6: file:/stop_times.csv"

How do you think this should be resolved?
waiting for a favorable response, please accept sir my best regards.

// Create unique constraints agency routes trips stop_times calendar stops

CREATE CONSTRAINT constraint_agency IF NOT EXISTS FOR (agency:Agency) REQUIRE agency.agency_id IS UNIQUE;

CREATE CONSTRAINT constraint_route IF NOT EXISTS FOR (route:routes) REQUIRE route.route_id IS UNIQUE;

CREATE CONSTRAINT constraint_trips IF NOT EXISTS FOR (trip:trips) REQUIRE trip.trip_id IS UNIQUE;

//CREATE CONSTRAINT constraint_stop_times IF NOT EXISTS FOR (stop_time:stop_times) REQUIRE stop_time.trip_id IS UNIQUE;

//CREATE CONSTRAINT constraint_stop_times IF NOT EXISTS FOR (stop_time:stop_times) REQUIRE stop_time.stop_id IS UNIQUE;

CREATE CONSTRAINT stop_times_node_key IF NOT EXISTS FOR (n:stop_times) REQUIRE (n.stop_id, n.trip_id) IS NODE KEY

CREATE CONSTRAINT constraint_calendar IF NOT EXISTS FOR (calendar:calendar) REQUIRE calendar. service_id IS UNIQUE;

CREATE CONSTRAINT constraint_stops IF NOT EXISTS FOR (stop:stop) REQUIRE stop.stop_id IS UNIQUE;

//. Creation des noeuds pour chaque classes d’entités agency routes trips stop_times calendar stops.

// Create agency nodes

LOAD CSV WITH HEADERS FROM "file:///agency.csv" AS line

MERGE (agency:agency {agency_id: line.agency_id})

SET agency+= {

                agency_name: line. agency_name,

                agency_url: line. agency_url,

              agency_timezone: line. agency_timezone,

             agency_phone: line. agency_phone,

             agency_lang: line. agency_lang,

             agency_fare_url: line. agency_fare_url

};

// Create routes nodes

LOAD CSV WITH HEADERS FROM "file:///routes.csv" AS line

MERGE (route:routes {route_id: line. route_id })

SET route += {

                agency_id: line.agency_id,

                route_short_name: line. route_short_name,

              route_long_name: line.route_short_name,

              route_type: line.route_type

};

// Create trips nodes

LOAD CSV WITH HEADERS FROM "file:///trips.csv" AS line

MERGE (trip:trips {trip_id: line.trip_id})

SET trip+= {

                route_id: line. route_id,

                service_id: line. service_id,

              trip_headsign: line. trip_headsign

};

// Create calendar nodes

LOAD CSV WITH HEADERS FROM "file:///calendar.csv" AS line

MERGE (calendar:calendar {service_id: line.service_id})

SET calendar+= {

                monday: line.monday,

                tuesday: line.tuesday,

            wednesday: line.Wednesday,

            thursday: line.Thursday,

            friday : line.Friday,

             saturday: line. Saturday,

            sunday: line. Sunday,

            start_date: line. start_date,

            end_date: line. end_date

};

// Create stop_times nodes

LOAD CSV WITH HEADERS FROM "file:///stop_times.csv" AS line

MERGE (stop_times:stop_times {stop_id:line.stop_id, trip_id:line.trip_id})

SET stop_times+= {

                arrival_time: line. arrival_time,

                departure_time: line. departure_time,

            stop_sequence: line. stop_sequence,

            stop_headsign: line.stop_headsign

};

// Create stops nodes

LOAD CSV WITH HEADERS FROM "file:///stops.csv" AS line

MERGE (stops:stops {stop_id: line.stop_id})

SET stops+= {

            stop_desc: line. stop_desc,

             stop_lat: line. stop_lat,

           stop_lon: line. stop_lon,

            zone_id: line. zone_id,

            loc_type:line. loc_type,

            stop_name :line. stop_name

};

// create relationships between (agency p, routes f : agency_id) (routes p, trips f : route_id) (calendar p, trips f : service_id) (stops p, stop_times f : stop_id) (trips p, stop_times f : trip_id)

// create relationships between (agency p, routes f : agency_id)

LOAD CSV WITH HEADERS FROM "file:///routes.csv" AS line

MATCH (route:routes {route_id: line.route_id})

MATCH (agency:agency {agency_id: line.agency_id})

MERGE (route)-[:related]->(agency);

// create relationships between (routes p, trips f : route_id)

LOAD CSV WITH HEADERS FROM "file:///trips.csv" AS line

MATCH (trip:trips {trip_id: line.trip_id})

MATCH (route:routes {route_id: line.route_id})

MERGE (trip)-[:related1]->(route);

// create relationships between (calendar p, trips f : service_id)

LOAD CSV WITH HEADERS FROM "file:///trips.csv" AS line

MATCH (trip:trips {trip_id: line.trip_id})

MATCH (calendar:calendar {service_id: line.service_id})

MERGE (trip)-[:related2]->(calendar);

// create relationships between (stops p, stop_times f : stop_id)

LOAD CSV WITH HEADERS FROM "file:/// stop_times.csv" AS line 

MATCH (n: stop_times {trip_id: line.trip_id, stop_id: line. stop_id}) 

MATCH (stop:stops {stop_id: line.stop_id}) 

MERGE (n)-[:related3]->(stop); 

// create relationships between (trips p, stop_times f : trip_id)

LOAD CSV WITH HEADERS FROM "file:/// stop_times.csv" AS line 

MATCH (n: stop_times {trip_id: line.trip_id, stop_id: line. stop_id}) 

MATCH (trip:trips {trip_id: line.trip_id}) 

MERGE (n)-[:related4]->(trips);