Skip to content

Instantly share code, notes, and snippets.

@olso
Forked from rvanbruggen/import_ESCO_csv_en.cql
Created February 18, 2019 11:47
Show Gist options
  • Save olso/45d8c9b8c5e13869243de392b70bc23b to your computer and use it in GitHub Desktop.
Save olso/45d8c9b8c5e13869243de392b70bc23b to your computer and use it in GitHub Desktop.
ESCO database in Neo4j
//Import ESCO using CSV files
create index ON :Occupation(ISCOGroup);
create index ON :Occupation(altLabels);
create index ON :Skill(altLabels);
create index ON :ISCOGroup(code);
create index ON :Skill(conceptUri);
create index ON :ISCOGroup(conceptUri);
create index ON :Occupation(conceptUri);
create index ON :Occupation(preferredLabel);
create index ON :Skill(preferredLabel);
//import skills and skillgroups
//skillgroups are also skills
load csv with headers from "file:///skillGroups_en.csv" as row
create (s:Skill:Skillgroup)
set s = row;
//skills
load csv with headers from "file:///skills_en.csv" as row
create (s:Skill)
set s = row;
//add the BROADER_THAN relationship between different skills
load csv with headers from "file:///broaderRelationsSkillPillar.csv" as row
match (smaller:Skill {conceptUri: row.conceptUri}), (broader:Skill {conceptUri: row.broaderUri})
create (broader)-[:BROADER_THAN]->(smaller);
//import occupations
load csv with headers from "file:///occupations_en.csv" as row
create (o:Occupation)
set o = row;
//import the International Standard Classification for Occupations of the ILO
load csv with headers from "file:///ISCOGroups_en.csv" as row
create (isco:ISCOGroup)
set isco = row;
//impirt the BROADER_THAN relationships between ISCO groups
load csv with headers from "file:///broaderRelationsOccPillar.csv" as row
match (smaller:ISCOGroup {conceptUri: row.conceptUri}), (broader:ISCOGroup {conceptUri: row.broaderUri})
create (broader)-[:BROADER_THAN]->(smaller);
//connect the occupations to their ISCOGroup
match (isco:ISCOGroup), (o:Occupation)
where isco.code = o.iscoGroup
create (o)-[:PART_OF_ISCOGROUP]->(isco);
//Connect Skills to Occupations
using periodic commit 500
load csv with headers from "file:///occupationSkillRelations.csv" as row
match (s:Skill {conceptUri: row.skillUri}), (o:Occupation {conceptUri: row.occupationUri})
CREATE (s)-[:RELATED_TO {type: row.relationType}]->(o);
// match ()-[r:RELATED_TO]->()
// return distinct r.type;
//differentiate the different types of relations between occupations and skills
match (a)-[r:RELATED_TO]->(b)
where r.type = "essential"
create (a)-[:ESSENTIAL_FOR]->(b);
match (a)-[r:RELATED_TO]->(b)
where r.type = "optional"
create (a)-[:OPTIONAL_FOR]->(b);
//remove the old relationships
match (a)-[r:RELATED_TO]->(b)
delete r;
//Import ESCO using CSV files
create index ON :Occupation(ISCOGroup);
create index ON :Occupation(altLabels);
create index ON :Skill(altLabels);
create index ON :ISCOGroup(code);
create index ON :Skill(conceptUri);
create index ON :ISCOGroup(conceptUri);
create index ON :Occupation(conceptUri);
create index ON :Occupation(preferredLabel);
create index ON :Skill(preferredLabel);
//import skills and skillgroups
//skillgroups are also skills
load csv with headers from "file:///skillGroups_nl.csv" as row
create (s:Skill:Skillgroup)
set s = row;
//skills
load csv with headers from "file:///skills_nl.csv" as row
create (s:Skill)
set s = row;
//add the BROADER_THAN relationship between different skills
load csv with headers from "file:///broaderRelationsSkillPillar.csv" as row
match (smaller:Skill {conceptUri: row.conceptUri}), (broader:Skill {conceptUri: row.broaderUri})
create (broader)-[:BROADER_THAN]->(smaller);
//import occupations
load csv with headers from "file:///occupations_nl.csv" as row
create (o:Occupation)
set o = row;
//import the International Standard Classification for Occupations of the ILO
load csv with headers from "file:///ISCOGroups_nl.csv" as row
create (isco:ISCOGroup)
set isco = row;
//impirt the BROADER_THAN relationships between ISCO groups
load csv with headers from "file:///broaderRelationsOccPillar.csv" as row
match (smaller:ISCOGroup {conceptUri: row.conceptUri}), (broader:ISCOGroup {conceptUri: row.broaderUri})
create (broader)-[:BROADER_THAN]->(smaller);
//connect the occupations to their ISCOGroup
match (isco:ISCOGroup), (o:Occupation)
where isco.code = o.iscoGroup
create (o)-[:PART_OF_ISCOGROUP]->(isco);
//Connect Skills to Occupations
using periodic commit 500
load csv with headers from "file:///occupationSkillRelations.csv" as row
match (s:Skill {conceptUri: row.skillUri}), (o:Occupation {conceptUri: row.occupationUri})
CREATE (s)-[:RELATED_TO {type: row.relationType}]->(o);
// match ()-[r:RELATED_TO]->()
// return distinct r.type;
//differentiate the different types of relations between occupations and skills
match (a)-[r:RELATED_TO]->(b)
where r.type = "essential"
create (a)-[:ESSENTIAL_FOR]->(b);
match (a)-[r:RELATED_TO]->(b)
where r.type = "optional"
create (a)-[:OPTIONAL_FOR]->(b);
//remove the old relationships
match (a)-[r:RELATED_TO]->(b)
delete r;
//Import ESCO database using RDF
CREATE INDEX ON :Resource(uri);
//Because of the size of the Turtle file, the import is split in 2 .ttl files
CALL semantics.importRDF("file:///Users/rvanbruggen/Library/Application Support/Neo4j Desktop/Application/neo4jDatabases/database-2ece41e7-abb9-4150-8565-b63abc7f409e/installation-3.4.6/import/esco1.ttl","Turtle", { shortenUrls: false, typesToLabels: true, commitSize: 5000 });
CALL semantics.importRDF("file:///Users/rvanbruggen/Library/Application Support/Neo4j Desktop/Application/neo4jDatabases/database-2ece41e7-abb9-4150-8565-b63abc7f409e/installation-3.4.6/import/esco2.ttl","Turtle", { shortenUrls: false, typesToLabels: true, commitSize: 5000 });
//refactoring the labels
MATCH (n:`http://data.europa.eu/esco/model#Occupation`) set n:ModelOccupation remove n:`http://data.europa.eu/esco/model#Occupation`;
MATCH (n:`http://data.europa.eu/esco/model#NodeLiteral`) set n:ModelNodeLiteral remove n:`http://data.europa.eu/esco/model#NodeLiteral`;
MATCH (n:`http://data.europa.eu/esco/model#MemberConcept`) set n:ModelMemberConcept remove n:`http://data.europa.eu/esco/model#MemberConcept`;
MATCH (n:`http://data.europa.eu/esco/model#LabelRole`) set n:ModelLabelRole remove n:`http://data.europa.eu/esco/model#LabelRole`;
MATCH (n:`http://data.europa.eu/esco/model#Skill`) set n:ModelSkill remove n:`http://data.europa.eu/esco/model#Skill`;
MATCH (n:`http://data.europa.eu/esco/model#Structure`) set n:ModelStructure remove n:`http://data.europa.eu/esco/model#Structure`;
MATCH (n:`http://data.europa.eu/esco/regulated-professions/Regulation`) set n:RegulatedProfessionsRegulation remove n:`http://data.europa.eu/esco/regulated-professions/Regulation`;
MATCH (n:`http://www.w3.org/2004/02/skos/core#Concept`) set n:CoreConcept remove n:`http://www.w3.org/2004/02/skos/core#Concept`;
MATCH (n:`http://www.w3.org/2004/02/skos/core#ConceptScheme`) set n:CoreConceptScheme remove n:`http://www.w3.org/2004/02/skos/core#ConceptScheme`;
MATCH (n:`http://data.europa.eu/esco/model#AssociationObject`) set n:ModelAssociationObject remove n:`http://data.europa.eu/esco/model#AssociationObject`;
MATCH (n:`http://data.europa.eu/esco/model#ConceptScheme`) set n:ModelConceptScheme remove n:`http://data.europa.eu/esco/model#ConceptScheme`;
//final refactoring is done with iterative batches
CALL apoc.periodic.iterate(
"MATCH (n:`http://www.w3.org/2008/05/skos-xl#Label`) return n",
"SET n:SkosXlLabel REMOVE n:`http://www.w3.org/2008/05/skos-xl#Label`", {batchSize:10000, parallel:true});
CALL semantics.importRDF("file:///Users/rvanbruggen/Library/Application Support/Neo4j Desktop/Application/neo4jDatabases/database-2ece41e7-abb9-4150-8565-b63abc7f409e/installation-3.4.6/import/ict_skills_collection.ttl","Turtle", { shortenUrls: false, typesToLabels: true, commitSize: 5000 });
CALL semantics.importRDF("file:///Users/rvanbruggen/Library/Application Support/Neo4j Desktop/Application/neo4jDatabases/database-2ece41e7-abb9-4150-8565-b63abc7f409e/installation-3.4.6/import/ict_skills_collection.ttl","Turtle", { shortenUrls: true, typesToLabels: false, commitSize: 5000 });
//query ESCO database
match (o:Occupation)
where o.preferredLabel contains "software"
return o;
match (o:Occupation)
where o.preferredLabel contains "bier"
return o
match (o1:Occupation), (o2:Occupation)
where o1.preferredLabel contains "software manager"
and o2.preferredLabel contains "bier"
return o1, o2;
match (o1:Occupation), (o2:Occupation),
paths = allshortestpaths ((o1)-[*]-(o2))
where o1.preferredLabel contains "software manager"
and o2.preferredLabel contains "bier"
return paths;
match (o1:Occupation), (o2:Occupation),
paths = allshortestpaths ((o1)-[:ESSENTIAL_FOR*]-(o2))
where o1.preferredLabel contains "software developer"
and o2.preferredLabel contains "bier"
return paths
limit 10
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment