Import intelligence dataset

intelligence_dataset_import.cql

//Clean up the db
MATCH (n)
OPTIONAL MATCH (n)-[r]-()
DELETE n,r;

//-----------------------
//Import persons
//-----------------------
CREATE CONSTRAINT ON (a:Person)
ASSERT a.id IS UNIQUE;

USING PERIODIC COMMIT 2000
LOAD CSV WITH HEADERS FROM "file:///C:///persons.csv" AS line
FIELDTERMINATOR ','
WITH line
MERGE (a:Person {id: line.id})
ON CREATE SET a.first_name= line.first_name,
    a.last_name=line.last_name,
    a.full_name=line.full_name,
    a.gender=line.gender,
    a.country=line.country,
    a.phone=line.phone,
    a.dob=line.dob,
    a.position=line.position;
  
//-----------------------
//Import addresses
//-----------------------
USING PERIODIC COMMIT 2000
LOAD CSV WITH HEADERS FROM "file:///C:///persons.csv" AS line
FIELDTERMINATOR ','
WITH line
MERGE (a:Address {country: line.country, city: line.city, latitude: line.latitude, longitude: line.longitude, street_address: line.street_address});

MATCH (a1:Address), (a2:Address)
WITH a1, a2
WHERE a1.country = a2.country AND a1.city = a2.city AND a1.latitude = a2.latitude AND a1.longitude = a2.longitude AND a1.street_address = a2.street_address
SET a1=a2;

//-----------------------
//Import phone #s
//-----------------------
CREATE CONSTRAINT ON (a:PhoneNumber)
ASSERT a.name IS UNIQUE;

USING PERIODIC COMMIT 2000
LOAD CSV WITH HEADERS FROM "file:///C:///persons.csv" AS line
FIELDTERMINATOR ','
WITH line
MERGE (a:PhoneNumber {name: line.phone});

//-----------------------
//Import countries
//-----------------------
CREATE CONSTRAINT ON (a:Country)
ASSERT a.name IS UNIQUE;

USING PERIODIC COMMIT 2000
LOAD CSV WITH HEADERS FROM "file:///C:///persons.csv" AS line
FIELDTERMINATOR ','
WITH line
MERGE (a:Country {name: line.country});

//-----------------------
//Import organizations
//-----------------------
CREATE CONSTRAINT ON (a:Organization)
ASSERT a.name IS UNIQUE;

USING PERIODIC COMMIT 2000
LOAD CSV WITH HEADERS FROM "file:///C:///organizations.csv" AS line
FIELDTERMINATOR ','
WITH line
MERGE (a:Organization {name: line.organization})
ON CREATE SET a.description=line.description;

//-----------------------
//Relationships between people and nationalities
//-----------------------
 
USING PERIODIC COMMIT 2000
LOAD CSV WITH HEADERS FROM "file:///C:///persons.csv" AS line
FIELDTERMINATOR ','
MATCH (b:Person {id: line.id})
MATCH (a:Country {name: line.country})
MERGE (b)-[r:HAS_NATIONALITY]->(a);

//-----------------------
//Relationships between people and addresses
//-----------------------
 
USING PERIODIC COMMIT 2000
LOAD CSV WITH HEADERS FROM "file:///C:///persons.csv" AS line
FIELDTERMINATOR ','
MATCH (b:Person {id: line.id})
MATCH (a:Address {country: line.country, city: line.city, latitude: line.latitude, longitude: line.longitude, street_address: line.street_address})
MERGE (b)-[r:HAS_ADDRESS]->(a);

//-----------------------
//Relationships between people and phone #s
//-----------------------
 
USING PERIODIC COMMIT 2000
LOAD CSV WITH HEADERS FROM "file:///C:///persons.csv" AS line
FIELDTERMINATOR ','
MATCH (b:Person {id: line.id})
MATCH (a:PhoneNumber {name: line.phone})
MERGE (b)-[r:HAS_PHONENUMBER]->(a);

//-----------------------
//Relationships between addresses and countries
//-----------------------
 
USING PERIODIC COMMIT 2000
LOAD CSV WITH HEADERS FROM "file:///C:///persons.csv" AS line
FIELDTERMINATOR ','
MATCH (b:Country {name: line.country})
MATCH (a:Address {country: line.country, city: line.city, latitude: line.latitude, longitude: line.longitude, street_address: line.street_address})
MERGE (a)-[r:HAS_COUNTRY]->(b);

//-----------------------
//Relationships between associates
//-----------------------

USING PERIODIC COMMIT 2000
LOAD CSV WITH HEADERS FROM "file:///C:///known_associates.csv" AS line
FIELDTERMINATOR ','
MATCH (b:Person {id: line.From})
MATCH (a:Person {id: line.To})
MERGE (b)-[r:IS_ASSOCIATE_OF]->(a);

MATCH (a:Person)-[r:IS_ASSOCIATE_OF]->(b:Person)
WITH a, b, collect(r)[1..] as rels
FOREACH (r in rels | DELETE r);

//-----------------------
//Relationships between people and countries (visitors)
//-----------------------

USING PERIODIC COMMIT 2000
LOAD CSV WITH HEADERS FROM "file:///C:///travelers.csv" AS line
FIELDTERMINATOR ','
MATCH (b:Person {id: line.From})
MATCH (a:Country {name: line.To})
MERGE (b)-[r:HAS_VISITED]->(a);

//-----------------------
//Relationships between phone #s
//-----------------------

USING PERIODIC COMMIT 2000
LOAD CSV WITH HEADERS FROM "file:///C:///phone_records.csv" AS line
FIELDTERMINATOR ','
MATCH (b:PhoneNumber {name: line.From})
MATCH (a:PhoneNumber {name: line.To})
MERGE (b)-[r:HAS_CALLED]->(a);