My first KG! Relationships not working!

samvernon · April 17, 2024, 7:49pm

Hello everyone! I'm new here. I'm trying to create a KG in Aura. I have created my input data in CSV's and selected the key unique identifier for each node. I have created the relationship csv, but the relationship fields are not the unique identifier, which I think may be the issue? I don't really know where to go from here?

glilienfield · April 17, 2024, 9:15pm

Can you provide more details? Import scripts? Sample data? Current results and what is not working?

samvernon · April 18, 2024, 1:19am

Hi Gary, i tried to upload the zip files from the Aura importer. Shall i just cut and paste the raw code?

glilienfield · April 18, 2024, 2:26am

Yes. If you paste code try to use the “preformatted text” markup “</>” so it renders better. It’s under the gear icon on an iPhone

samvernon · April 18, 2024, 5:07am

:param {
  // Define the file path root and the individual file names required for loading.
  // https://neo4j.com/docs/operations-manual/current/configuration/file-locations/
  file_path_root: 'file:///', // Change this to the folder your script can access the files at.
  file_0: 'WebMappingApplications.csv',
  file_1: 'WebMaps.csv',
  file_2: 'WebMapLayers.csv',
  file_3: 'TablesServices.csv',
  file_4: 'Tables.csv',
  file_5: 'Services.csv',
  file_6: 'FMEWorkspaceWriters.csv',
  file_7: 'FMEWorkspaces.csv',
  file_8: 'FMEWorkspaceReaders.csv',
  file_9: '1.Relationship_WebMappingApplications_to_WebMaps.csv',
  file_10: '2.Relationship_WebMaps_to_WebMapLayers.csv',
  file_11: '4.Relationship_WebMapLayers_to_Services.csv',
  file_12: '3.Relationship_WebMapLayers_to_TablesServices.csv',
  file_13: '5.Relationship_TablesServices_to_Tables.csv',
  file_14: '6.Relationship_TablesServices_to_Services.csv',
  file_15: '9.Relationship_TablesServices_to_FMEWorkspaceReaders.csv',
  file_16: '10.Relationship_TablesServices_to_FMEWorkspaceWriters.csv',
  file_17: '12.Relationship_Tables_to_FMEWorkspaceWriters.csv',
  file_18: '13.Relationship_Tables_to_FMEWorkspaceReaders.csv',
  file_19: '15.Relationship_FMEWorkspaceWriters_to_FMEWorkspaces.csv',
  file_20: '16.Relationship_FMEWorkspaceReaders_to_FMEWorkspaces.csv'
};

// CONSTRAINT creation
// -------------------
//
// Create node uniqueness constraints, ensuring no duplicates for the given node label and ID property exist in the database. This also ensures no duplicates are introduced in future.
//
// NOTE: The following constraint creation syntax is generated based on the current connected database version 5.19-aura.
CREATE CONSTRAINT `wa_webAppItemId_WebApp_uniq` IF NOT EXISTS
FOR (n: `WebApp`)
REQUIRE (n.`wa_webAppItemId`) IS UNIQUE;
CREATE CONSTRAINT `wm_webMapItemId_WebMap_uniq` IF NOT EXISTS
FOR (n: `WebMap`)
REQUIRE (n.`wm_webMapItemId`) IS UNIQUE;
CREATE CONSTRAINT `wml_webMapLayerUrl_WebMapLayers_uniq` IF NOT EXISTS
FOR (n: `WebMapLayers`)
REQUIRE (n.`wml_webMapLayerUrl`) IS UNIQUE;
CREATE CONSTRAINT `ts_serviceURL_TableServices_uniq` IF NOT EXISTS
FOR (n: `TableServices`)
REQUIRE (n.`ts_serviceURL`) IS UNIQUE;
CREATE CONSTRAINT `t_tableName_Tables_uniq` IF NOT EXISTS
FOR (n: `Tables`)
REQUIRE (n.`t_tableName`) IS UNIQUE;
CREATE CONSTRAINT `s_serviceURL_Services_uniq` IF NOT EXISTS
FOR (n: `Services`)
REQUIRE (n.`s_serviceURL`) IS UNIQUE;
CREATE CONSTRAINT `fmefw_workspaceName_FMEWorkspaceWriters_uniq` IF NOT EXISTS
FOR (n: `FMEWorkspaceWriters`)
REQUIRE (n.`fmefw_workspaceName`) IS UNIQUE;
CREATE CONSTRAINT `fmef_workspaceName_FMEWorkspaces_uniq` IF NOT EXISTS
FOR (n: `FMEWorkspaces`)
REQUIRE (n.`fmef_workspaceName`) IS UNIQUE;
CREATE CONSTRAINT `fmefr_workspaceName_FMEWorkspaceReaders_uniq` IF NOT EXISTS
FOR (n: `FMEWorkspaceReaders`)
REQUIRE (n.`fmefr_workspaceName`) IS UNIQUE;

:param {
  idsToSkip: []
};

// NODE load
// ---------
//
// Load nodes in batches, one node label at a time. Nodes will be created using a MERGE statement to ensure a node with the same label and ID property remains unique. Pre-existing nodes found by a MERGE statement will have their other properties set to the latest values encountered in a load file.
//
// NOTE: Any nodes with IDs in the 'idsToSkip' list parameter will not be loaded.
LOAD CSV WITH HEADERS FROM ($file_path_root + $file_0) AS row
WITH row
WHERE NOT row.`wa_webAppItemId` IN $idsToSkip AND NOT row.`wa_webAppItemId` IS NULL
CALL {
  WITH row
  MERGE (n: `WebApp` { `wa_webAppItemId`: row.`wa_webAppItemId` })
  SET n.`wa_webAppItemId` = row.`wa_webAppItemId`
  SET n.`wa_webAppTitle` = row.`wa_webAppTitle`
  SET n.`wa_webAppNumViews` = toInteger(trim(row.`wa_webAppNumViews`))
  SET n.`wa_webAppOwner` = row.`wa_webAppOwner`
  SET n.`wa_webAppSnippet` = row.`wa_webAppSnippet`
  SET n.`wa_webAppModified` = row.`wa_webAppModified`
  SET n.`wa_webAppCreated` = row.`wa_webAppCreated`
  SET n.`wa_webAppAccess` = row.`wa_webAppAccess`
  SET n.`wa_webAppItemURL` = row.`wa_webAppItemURL`
  SET n.`wa_webMapItemId` = row.`wa_webMapItemId`
  SET n.`wa_description` = row.`wa_description`
  SET n.`wa_documentation` = row.`wa_documentation`
  SET n.`wa_groupDesignations` = row.`wa_groupDesignations`
  SET n.`wa_listed` = row.`wa_listed`
  SET n.`wa_num` = toInteger(trim(row.`wa_num`))
  SET n.`wa_numComments` = toLower(trim(row.`wa_numComments`)) IN ['1','true','yes']
  SET n.`wa_accessInformation` = row.`wa_accessInformation`
  SET n.`wa_numRatings` = toLower(trim(row.`wa_numRatings`)) IN ['1','true','yes']
  SET n.`wa_properties` = row.`wa_properties`
  SET n.`wa_scoreCompleteness` = toInteger(trim(row.`wa_scoreCompleteness`))
  SET n.`wa_size` = toInteger(trim(row.`wa_size`))
  SET n.`wa_spatialReference` = row.`wa_spatialReference`
  SET n.`wa_total` = toInteger(trim(row.`wa_total`))
  SET n.`wa_type` = row.`wa_type`
  SET n.`wa_url` = row.`wa_url`
  SET n.`wa_tags` = row.`wa_tags`
  SET n.`wa_typeKeywords` = row.`wa_typeKeywords`
  SET n.`wa_environment` = row.`wa_environment`
  SET n.`wa_thumbnailURL` = row.`wa_thumbnailURL`
  SET n.`wa_viewURL` = row.`wa_viewURL`
  SET n.`wa_traceURL` = row.`wa_traceURL`
} IN TRANSACTIONS OF 10000 ROWS;

LOAD CSV WITH HEADERS FROM ($file_path_root + $file_1) AS row
WITH row
WHERE NOT row.`wm_webMapItemId` IN $idsToSkip AND NOT row.`wm_webMapItemId` IS NULL
CALL {
  WITH row
  MERGE (n: `WebMap` { `wm_webMapItemId`: row.`wm_webMapItemId` })
  SET n.`wm_webMapItemId` = row.`wm_webMapItemId`
  SET n.`wm_webMapTitle` = row.`wm_webMapTitle`
  SET n.`wm_webMapNumViews` = toInteger(trim(row.`wm_webMapNumViews`))
  SET n.`wm_webMapOwner` = row.`wm_webMapOwner`
  SET n.`wm_webMapSnippet` = row.`wm_webMapSnippet`
  SET n.`wm_webMapModified` = row.`wm_webMapModified`
  SET n.`wm_webMapCreated` = row.`wm_webMapCreated`
  SET n.`wm_webMapAccess` = row.`wm_webMapAccess`
  SET n.`wm_webMapItemURL` = row.`wm_webMapItemURL`
  SET n.`wm_description` = row.`wm_description`
  SET n.`wm_documentation` = row.`wm_documentation`
  SET n.`wm_groupDesignations` = row.`wm_groupDesignations`
  SET n.`wm_listed` = row.`wm_listed`
  SET n.`wm_num` = toInteger(trim(row.`wm_num`))
  SET n.`wm_numComments` = toLower(trim(row.`wm_numComments`)) IN ['1','true','yes']
  SET n.`wm_numRatings` = toLower(trim(row.`wm_numRatings`)) IN ['1','true','yes']
  SET n.`wm_properties` = row.`wm_properties`
  SET n.`wm_accessInformation` = row.`wm_accessInformation`
  SET n.`wm_scoreCompleteness` = toInteger(trim(row.`wm_scoreCompleteness`))
  SET n.`wm_size` = toInteger(trim(row.`wm_size`))
  SET n.`wm_spatialReference` = row.`wm_spatialReference`
  SET n.`wm_total` = toInteger(trim(row.`wm_total`))
  SET n.`wm_type` = row.`wm_type`
  SET n.`wm_url` = row.`wm_url`
  SET n.`wm_tags` = row.`wm_tags`
  SET n.`wm_typeKeywords` = row.`wm_typeKeywords`
  SET n.`wm_environment` = row.`wm_environment`
  SET n.`wm_thumbnailURL` = row.`wm_thumbnailURL`
  SET n.`wm_viewURL` = row.`wm_viewURL`
  SET n.`wm_traceURL` = row.`wm_traceURL`
} IN TRANSACTIONS OF 10000 ROWS;

LOAD CSV WITH HEADERS FROM ($file_path_root + $file_2) AS row
WITH row
WHERE NOT row.`wml_webMapLayerUrl` IN $idsToSkip AND NOT row.`wml_webMapLayerUrl` IS NULL
CALL {
  WITH row
  MERGE (n: `WebMapLayers` { `wml_webMapLayerUrl`: row.`wml_webMapLayerUrl` })
  SET n.`wml_webMapLayerUrl` = row.`wml_webMapLayerUrl`
  SET n.`wml_webMapItemId` = row.`wml_webMapItemId`
  SET n.`wml_webMapTitle` = row.`wml_webMapTitle`
  SET n.`wml_webMapName` = row.`wml_webMapName`
  SET n.`wml_webMapNumViews` = toInteger(trim(row.`wml_webMapNumViews`))
  SET n.`wml_webMapOwner` = row.`wml_webMapOwner`
  SET n.`wml_webMapSnippet` = row.`wml_webMapSnippet`
  SET n.`wml_webMapModified` = row.`wml_webMapModified`
  SET n.`wml_webMapCreated` = row.`wml_webMapCreated`
  SET n.`wml_webMapAccess` = row.`wml_webMapAccess`
  SET n.`wml_webMapItemURL` = row.`wml_webMapItemURL`
  SET n.`wml_webMapLayerId` = row.`wml_webMapLayerId`
  SET n.`wml_environment` = row.`wml_environment`
  SET n.`wml_serviceURL` = row.`wml_serviceURL`
} IN TRANSACTIONS OF 10000 ROWS;

LOAD CSV WITH HEADERS FROM ($file_path_root + $file_3) AS row
WITH row
WHERE NOT row.`ts_serviceURL` IN $idsToSkip AND NOT row.`ts_serviceURL` IS NULL
CALL {
  WITH row
  MERGE (n: `TableServices` { `ts_serviceURL`: row.`ts_serviceURL` })
  SET n.`ts_serviceURL` = row.`ts_serviceURL`
  SET n.`ts_tableName` = row.`ts_tableName`
  SET n.`ts_dataSourceType` = row.`ts_dataSourceType`
  SET n.`ts_fileGeodatabasePath` = row.`ts_fileGeodatabasePath`
  SET n.`ts_databaseServer` = row.`ts_databaseServer`
  SET n.`ts_database` = row.`ts_database`
  SET n.`ts_databaseUser` = row.`ts_databaseUser`
  SET n.`ts_databaseAuthentication` = row.`ts_databaseAuthentication`
  SET n.`ts_databaseVersion` = row.`ts_databaseVersion`
  SET n.`ts_documentPath` = row.`ts_documentPath`
  SET n.`ts_serviceName` = row.`ts_serviceName`
  SET n.`ts_folderName` = row.`ts_folderName`
  SET n.`ts_serviceType` = row.`ts_serviceType`
  SET n.`ts_mapServiceDefinition` = row.`ts_mapServiceDefinition`
  SET n.`ts_manifestURL` = row.`ts_manifestURL`
  SET n.`ts_environment` = row.`ts_environment`
  SET n.`ts_dataUniqueID` = row.`ts_dataUniqueID`
} IN TRANSACTIONS OF 10000 ROWS;

LOAD CSV WITH HEADERS FROM ($file_path_root + $file_4) AS row
WITH row
WHERE NOT row.`t_tableName` IN $idsToSkip AND NOT row.`t_tableName` IS NULL
CALL {
  WITH row
  MERGE (n: `Tables` { `t_tableName`: row.`t_tableName` })
  SET n.`t_tableName` = row.`t_tableName`
  SET n.`t_OBJECTID` = row.`t_OBJECTID`
  SET n.`t_dataSourceType` = row.`t_dataSourceType`
  SET n.`t_fileGeodatabasePath` = row.`t_fileGeodatabasePath`
  SET n.`t_databaseServer` = row.`t_databaseServer`
  SET n.`t_database` = row.`t_database`
  SET n.`t_databaseUser` = row.`t_databaseUser`
  SET n.`t_databaseAuthentication` = row.`t_databaseAuthentication`
  SET n.`t_databaseVersion` = row.`t_databaseVersion`
  SET n.`t_documentPath` = row.`t_documentPath`
  SET n.`t_environment` = row.`t_environment`
  SET n.`t_dataUniqueID` = row.`t_dataUniqueID`
} IN TRANSACTIONS OF 10000 ROWS;

LOAD CSV WITH HEADERS FROM ($file_path_root + $file_5) AS row
WITH row
WHERE NOT row.`s_serviceURL` IN $idsToSkip AND NOT row.`s_serviceURL` IS NULL
CALL {
  WITH row
  MERGE (n: `Services` { `s_serviceURL`: row.`s_serviceURL` })
  SET n.`s_serviceURL` = row.`s_serviceURL`
  SET n.`s_server` = row.`s_server`
  SET n.`s_serviceName` = row.`s_serviceName`
  SET n.`s_folderName` = row.`s_folderName`
  SET n.`s_featureServiceId` = row.`s_featureServiceId`
  SET n.`s_featureServiceTitle` = row.`s_featureServiceTitle`
  SET n.`s_featureServiceNumViews` = row.`s_featureServiceNumViews`
  SET n.`s_featureServiceOwner` = row.`s_featureServiceOwner`
  SET n.`s_featureServiceSnippet` = row.`s_featureServiceSnippet`
  SET n.`s_featureServiceModified` = row.`s_featureServiceModified`
  SET n.`s_featureServiceCreated` = row.`s_featureServiceCreated`
  SET n.`s_featureServiceAccess` = row.`s_featureServiceAccess`
  SET n.`s_featureServiceItemURL` = row.`s_featureServiceItemURL`
  SET n.`s_description` = row.`s_description`
  SET n.`s_documentation` = row.`s_documentation`
  SET n.`s_groupDesignations` = row.`s_groupDesignations`
  SET n.`s_listed` = row.`s_listed`
  SET n.`s_num` = row.`s_num`
  SET n.`s_numComments` = row.`s_numComments`
  SET n.`s_accessInformation` = row.`s_accessInformation`
  SET n.`s_numRatings` = row.`s_numRatings`
  SET n.`s_properties` = row.`s_properties`
  SET n.`s_scoreCompleteness` = row.`s_scoreCompleteness`
  SET n.`s_size` = row.`s_size`
  SET n.`s_spatialReference` = row.`s_spatialReference`
  SET n.`s_total` = row.`s_total`
  SET n.`s_type` = row.`s_type`
  SET n.`s_tags` = row.`s_tags`
  SET n.`s_typeKeywords` = row.`s_typeKeywords`
  SET n.`s_environment` = row.`s_environment`
  SET n.`s_thumbnailURL` = row.`s_thumbnailURL`
  SET n.`s_viewURL` = row.`s_viewURL`
  SET n.`s_traceURL` = row.`s_traceURL`
  SET n.`s_OBJECTID` = row.`s_OBJECTID`
} IN TRANSACTIONS OF 10000 ROWS;

LOAD CSV WITH HEADERS FROM ($file_path_root + $file_6) AS row
WITH row
WHERE NOT row.`fmefw_workspaceName` IN $idsToSkip AND NOT row.`fmefw_workspaceName` IS NULL
CALL {
  WITH row
  MERGE (n: `FMEWorkspaceWriters` { `fmefw_workspaceName`: row.`fmefw_workspaceName` })
  SET n.`fmefw_workspaceName` = row.`fmefw_workspaceName`
  SET n.`fmefw_repositoryName` = row.`fmefw_repositoryName`
  SET n.`fmefw_workspaceTitle` = row.`fmefw_workspaceTitle`
  SET n.`fmefw_workspaceID` = row.`fmefw_workspaceID`
  SET n.`fmefw_environment` = row.`fmefw_environment`
  SET n.`fmefw_datasetType` = row.`fmefw_datasetType`
  SET n.`fmefw_format` = row.`fmefw_format`
  SET n.`fmefw_location` = row.`fmefw_location`
  SET n.`fmefw_tableName` = row.`fmefw_tableName`
  SET n.`fmefw_method` = row.`fmefw_method`
} IN TRANSACTIONS OF 10000 ROWS;

LOAD CSV WITH HEADERS FROM ($file_path_root + $file_7) AS row
WITH row
WHERE NOT row.`fmef_workspaceName` IN $idsToSkip AND NOT row.`fmef_workspaceName` IS NULL
CALL {
  WITH row
  MERGE (n: `FMEWorkspaces` { `fmef_workspaceName`: row.`fmef_workspaceName` })
  SET n.`fmef_workspaceName` = row.`fmef_workspaceName`
  SET n.`fmef_repositoryName` = row.`fmef_repositoryName`
  SET n.`fmef_workspaceTitle` = row.`fmef_workspaceTitle`
  SET n.`fmef_repositoryOwner` = row.`fmef_repositoryOwner`
  // Your script contains the datetime datatype. Our app attempts to convert dates to ISO 8601 date format before passing them to the Cypher function.
  // This conversion cannot be done in a Cypher script load. Please ensure that your CSV file columns are in ISO 8601 date format to ensure equivalent loads.
  SET n.`fmef_workspaceLastPublished` = datetime(row.`fmef_workspaceLastPublished`)
  SET n.`fmef_workspaceTotalRuns` = toInteger(trim(row.`fmef_workspaceTotalRuns`))
  SET n.`fmef_workspacePublisher` = row.`fmef_workspacePublisher`
  SET n.`fmef_avgElapsedTime` = toFloat(trim(row.`fmef_avgElapsedTime`))
  SET n.`fmef_minutesTaken` = toFloat(trim(row.`fmef_minutesTaken`))
  SET n.`fmef_workspaceID` = row.`fmef_workspaceID`
  SET n.`fmef_workspaceRunMethod` = row.`fmef_workspaceRunMethod`
  SET n.`fmef_workspaceTrigger` = row.`fmef_workspaceTrigger`
  SET n.`fmef_environment` = row.`fmef_environment`
  SET n.`fmef_viewURL` = row.`fmef_viewURL`
  SET n.`fmef_traceURL` = row.`fmef_traceURL`
} IN TRANSACTIONS OF 10000 ROWS;

LOAD CSV WITH HEADERS FROM ($file_path_root + $file_8) AS row
WITH row
WHERE NOT row.`fmefr_workspaceName` IN $idsToSkip AND NOT row.`fmefr_workspaceName` IS NULL
CALL {
  WITH row
  MERGE (n: `FMEWorkspaceReaders` { `fmefr_workspaceName`: row.`fmefr_workspaceName` })
  SET n.`fmefr_workspaceName` = row.`fmefr_workspaceName`
  SET n.`fmefr_repositoryName` = row.`fmefr_repositoryName`
  SET n.`fmefr_workspaceTitle` = row.`fmefr_workspaceTitle`
  SET n.`fmefr_workspaceID` = row.`fmefr_workspaceID`
  SET n.`fmefr_environment` = row.`fmefr_environment`
  SET n.`fmefr_datasetType` = row.`fmefr_datasetType`
  SET n.`fmefr_format` = row.`fmefr_format`
  SET n.`fmefr_location` = row.`fmefr_location`
  SET n.`fmefr_tableName` = row.`fmefr_tableName`
  SET n.`fmefr_method` = row.`fmefr_method`
} IN TRANSACTIONS OF 10000 ROWS;


// RELATIONSHIP load
// -----------------
//
// Load relationships in batches, one relationship type at a time. Relationships are created using a MERGE statement, meaning only one relationship of a given type will ever be created between a pair of nodes.
LOAD CSV WITH HEADERS FROM ($file_path_root + $file_10) AS row
WITH row 
CALL {
  WITH row
  MATCH (source: `WebMap` { `wm_webMapItemId`: row.`wml_webMapItemId` })
  MATCH (target: `WebMapLayers` { `wml_webMapLayerUrl`: row.`wm_webMapItemId` })
  MERGE (source)-[r: `CONTAINS`]->(target)
} IN TRANSACTIONS OF 10000 ROWS;

LOAD CSV WITH HEADERS FROM ($file_path_root + $file_9) AS row
WITH row 
CALL {
  WITH row
  MATCH (source: `WebApp` { `wa_webAppItemId`: row.`wa_webMapItemId` })
  MATCH (target: `WebMap` { `wm_webMapItemId`: row.`wm_webMapItemId` })
  MERGE (source)-[r: `CONTAINS`]->(target)
} IN TRANSACTIONS OF 10000 ROWS;

LOAD CSV WITH HEADERS FROM ($file_path_root + $file_11) AS row
WITH row 
CALL {
  WITH row
  MATCH (source: `WebMapLayers` { `wml_webMapLayerUrl`: row.`wml_serviceURL` })
  MATCH (target: `Services` { `s_serviceURL`: row.`s_serviceURL` })
  MERGE (source)-[r: `CONTAINS`]->(target)
} IN TRANSACTIONS OF 10000 ROWS;

LOAD CSV WITH HEADERS FROM ($file_path_root + $file_12) AS row
WITH row 
CALL {
  WITH row
  MATCH (source: `WebMapLayers` { `wml_webMapLayerUrl`: row.`wml_serviceURL` })
  MATCH (target: `TableServices` { `ts_serviceURL`: row.`ts_serviceURL` })
  MERGE (source)-[r: `CONTAINS`]->(target)
} IN TRANSACTIONS OF 10000 ROWS;

LOAD CSV WITH HEADERS FROM ($file_path_root + $file_13) AS row
WITH row 
CALL {
  WITH row
  MATCH (source: `TableServices` { `ts_serviceURL`: row.`ts_dataUniqueID` })
  MATCH (target: `Tables` { `t_tableName`: row.`t_dataUniqueID` })
  MERGE (source)-[r: `CONTAINS`]->(target)
} IN TRANSACTIONS OF 10000 ROWS;

LOAD CSV WITH HEADERS FROM ($file_path_root + $file_14) AS row
WITH row 
CALL {
  WITH row
  MATCH (source: `TableServices` { `ts_serviceURL`: row.`ts_serviceURL` })
  MATCH (target: `Services` { `s_serviceURL`: row.`s_serviceURL` })
  MERGE (source)-[r: `CONTAINS`]->(target)
} IN TRANSACTIONS OF 10000 ROWS;

LOAD CSV WITH HEADERS FROM ($file_path_root + $file_15) AS row
WITH row 
CALL {
  WITH row
  MATCH (source: `FMEWorkspaceReaders` { `fmefr_workspaceName`: row.`fmefr_tableName` })
  MATCH (target: `TableServices` { `ts_serviceURL`: row.`ts_tableName` })
  MERGE (source)-[r: `READS`]->(target)
} IN TRANSACTIONS OF 10000 ROWS;

LOAD CSV WITH HEADERS FROM ($file_path_root + $file_16) AS row
WITH row 
CALL {
  WITH row
  MATCH (source: `FMEWorkspaceWriters` { `fmefw_workspaceName`: row.`fmefw_tableName` })
  MATCH (target: `TableServices` { `ts_serviceURL`: row.`ts_tableName` })
  MERGE (source)-[r: `WRITES`]->(target)
} IN TRANSACTIONS OF 10000 ROWS;

LOAD CSV WITH HEADERS FROM ($file_path_root + $file_17) AS row
WITH row 
CALL {
  WITH row
  MATCH (source: `FMEWorkspaceWriters` { `fmefw_workspaceName`: row.`fmefw_tableName` })
  MATCH (target: `Tables` { `t_tableName`: row.`t_tableName` })
  MERGE (source)-[r: `WRITES`]->(target)
} IN TRANSACTIONS OF 10000 ROWS;

LOAD CSV WITH HEADERS FROM ($file_path_root + $file_18) AS row
WITH row 
CALL {
  WITH row
  MATCH (source: `FMEWorkspaceReaders` { `fmefr_workspaceName`: row.`fmefr_tableName` })
  MATCH (target: `Tables` { `t_tableName`: row.`t_tableName` })
  MERGE (source)-[r: `READS`]->(target)
} IN TRANSACTIONS OF 10000 ROWS;

LOAD CSV WITH HEADERS FROM ($file_path_root + $file_19) AS row
WITH row 
CALL {
  WITH row
  MATCH (source: `FMEWorkspaces` { `fmef_workspaceName`: row.`fmef_workspaceName` })
  MATCH (target: `FMEWorkspaceWriters` { `fmefw_workspaceName`: row.`fmefw_workspaceName` })
  MERGE (source)-[r: `CONTAINS`]->(target)
} IN TRANSACTIONS OF 10000 ROWS;

LOAD CSV WITH HEADERS FROM ($file_path_root + $file_20) AS row
WITH row 
CALL {
  WITH row
  MATCH (source: `FMEWorkspaces` { `fmef_workspaceName`: row.`fmef_workspaceName` })
  MATCH (target: `FMEWorkspaceReaders` { `fmefr_workspaceName`: row.`fmefr_workspaceName` })
  MERGE (source)-[r: `CONTAINS`]->(target)
} IN TRANSACTIONS OF 10000 ROWS;

LOAD CSV WITH HEADERS FROM ($file_path_root + $file_9) AS row
WITH row 
CALL {
  WITH row
  MATCH (source: `WebMap` { `wm_webMapItemId`: row.`wm_webMapItemId` })
  MATCH (target: `WebApp` { `wa_webAppItemId`: row.`wa_webMapItemId` })
  MERGE (source)-[r: `SUPPLIES`]->(target)
} IN TRANSACTIONS OF 10000 ROWS;

LOAD CSV WITH HEADERS FROM ($file_path_root + $file_10) AS row
WITH row 
CALL {
  WITH row
  MATCH (source: `WebMapLayers` { `wml_webMapLayerUrl`: row.`wml_webMapItemId` })
  MATCH (target: `WebMap` { `wm_webMapItemId`: row.`wm_webMapItemId` })
  MERGE (source)-[r: `SUPPLIES`]->(target)
} IN TRANSACTIONS OF 10000 ROWS;

LOAD CSV WITH HEADERS FROM ($file_path_root + $file_12) AS row
WITH row 
CALL {
  WITH row
  MATCH (source: `TableServices` { `ts_serviceURL`: row.`ts_serviceURL` })
  MATCH (target: `WebMapLayers` { `wml_webMapLayerUrl`: row.`wml_serviceURL` })
  MERGE (source)-[r: `SUPPLIES`]->(target)
} IN TRANSACTIONS OF 10000 ROWS;

LOAD CSV WITH HEADERS FROM ($file_path_root + $file_11) AS row
WITH row 
CALL {
  WITH row
  MATCH (source: `Services` { `s_serviceURL`: row.`s_serviceURL` })
  MATCH (target: `WebMapLayers` { `wml_webMapLayerUrl`: row.`wml_serviceURL` })
  MERGE (source)-[r: `SUPPLIES`]->(target)
} IN TRANSACTIONS OF 10000 ROWS;

LOAD CSV WITH HEADERS FROM ($file_path_root + $file_14) AS row
WITH row 
CALL {
  WITH row
  MATCH (source: `Services` { `s_serviceURL`: row.`s_serviceURL` })
  MATCH (target: `TableServices` { `ts_serviceURL`: row.`ts_serviceURL` })
  MERGE (source)-[r: `SUPPLIES`]->(target)
} IN TRANSACTIONS OF 10000 ROWS;

LOAD CSV WITH HEADERS FROM ($file_path_root + $file_13) AS row
WITH row 
CALL {
  WITH row
  MATCH (source: `Tables` { `t_tableName`: row.`t_dataUniqueID` })
  MATCH (target: `TableServices` { `ts_serviceURL`: row.`ts_dataUniqueID` })
  MERGE (source)-[r: `SUPPLIES`]->(target)
} IN TRANSACTIONS OF 10000 ROWS;

LOAD CSV WITH HEADERS FROM ($file_path_root + $file_19) AS row
WITH row 
CALL {
  WITH row
  MATCH (source: `FMEWorkspaceWriters` { `fmefw_workspaceName`: row.`fmefw_workspaceName` })
  MATCH (target: `FMEWorkspaces` { `fmef_workspaceName`: row.`fmef_workspaceName` })
  MERGE (source)-[r: `PART OF`]->(target)
} IN TRANSACTIONS OF 10000 ROWS;

LOAD CSV WITH HEADERS FROM ($file_path_root + $file_20) AS row
WITH row 
CALL {
  WITH row
  MATCH (source: `FMEWorkspaceReaders` { `fmefr_workspaceName`: row.`fmefr_workspaceName` })
  MATCH (target: `FMEWorkspaces` { `fmef_workspaceName`: row.`fmef_workspaceName` })
  MERGE (source)-[r: `PART OF`]->(target)
} IN TRANSACTIONS OF 10000 ROWS;

LOAD CSV WITH HEADERS FROM ($file_path_root + $file_15) AS row
WITH row 
CALL {
  WITH row
  MATCH (source: `TableServices` { `ts_serviceURL`: row.`ts_tableName` })
  MATCH (target: `FMEWorkspaceReaders` { `fmefr_workspaceName`: row.`fmefr_tableName` })
  MERGE (source)-[r: `READ BY`]->(target)
} IN TRANSACTIONS OF 10000 ROWS;

LOAD CSV WITH HEADERS FROM ($file_path_root + $file_17) AS row
WITH row 
CALL {
  WITH row
  MATCH (source: `Tables` { `t_tableName`: row.`t_tableName` })
  MATCH (target: `FMEWorkspaceWriters` { `fmefw_workspaceName`: row.`fmefw_tableName` })
  MERGE (source)-[r: `WRITTEN BY`]->(target)
} IN TRANSACTIONS OF 10000 ROWS;

LOAD CSV WITH HEADERS FROM ($file_path_root + $file_16) AS row
WITH row 
CALL {
  WITH row
  MATCH (source: `TableServices` { `ts_serviceURL`: row.`ts_tableName` })
  MATCH (target: `FMEWorkspaceWriters` { `fmefw_workspaceName`: row.`fmefw_tableName` })
  MERGE (source)-[r: `WRITTEN BY`]->(target)
} IN TRANSACTIONS OF 10000 ROWS;

LOAD CSV WITH HEADERS FROM ($file_path_root + $file_18) AS row
WITH row 
CALL {
  WITH row
  MATCH (source: `Tables` { `t_tableName`: row.`t_tableName` })
  MATCH (target: `FMEWorkspaceReaders` { `fmefr_workspaceName`: row.`fmefr_tableName` })
  MERGE (source)-[r: `READ BY`]->(target)
} IN TRANSACTIONS OF 10000 ROWS;

glilienfield · April 18, 2024, 7:56am

Ok, what is and what isn’t working as expected?

samvernon · April 18, 2024, 8:34pm

I really appreciate your help Gary! So as an example, I was expecting relationships between file_0: 'WebMappingApplications.csv', and file_1: 'WebMaps.csv', but none were created.
Relationship file:

wa_webMapItemId	wm_webMapItemId
892ce0c8327749558e78931529ac3715	892ce0c8327749558e78931529ac3715

glilienfield · April 18, 2024, 9:13pm

I don’t know what the columns are file9, but is the following correct in the code bellow that creates the relationship between the WebApp and WebMap nodes? You are matching the WebApp node on property wa_webAppItemId with the spreadsheet column wa_webMapItemId. One is web app while the other is web map. Should they be web app?

MATCH (source: `WebApp` { `wa_webAppItemId`: row.`wa_webMapItemId` })

LOAD CSV WITH HEADERS FROM ($file_path_root + $file_9) AS row
WITH row 
CALL {
  WITH row
  MATCH (source: `WebApp` { `wa_webAppItemId`: row.`wa_webMapItemId` })
  MATCH (target: `WebMap` { `wm_webMapItemId`: row.`wm_webMapItemId` })
  MERGE (source)-[r: `CONTAINS`]->(target)
} IN TRANSACTIONS OF 10000 ROWS;

samvernon · April 18, 2024, 9:27pm

Ah, ok, they should be WebMap. So should I set the unique key in WebApp to be WebMap so that WebMap is used in the relationship?

glilienfield · April 18, 2024, 9:52pm

The correct answer depends on the data in your spreadsheets. I thought it looked like it could have been a typo. I thought it may have been the following.

LOAD CSV WITH HEADERS FROM ($file_path_root + $file_9) AS row
WITH row 
CALL {
  WITH row
  MATCH (source: `WebApp` { `wa_webAppItemId`: row.`wa_webAppItemId` })
  MATCH (target: `WebMap` { `wm_webMapItemId`: row.`wm_webMapItemId` })
  MERGE (source)-[r: `CONTAINS`]->(target)
} IN TRANSACTIONS OF 10000 ROWS;

samvernon · April 22, 2024, 2:26am

Thanks Gary, so I've gone back to the start and used only WebApp and WebMap as nodes and only relationship in each direction, and now nothing seems to load in Aura.
So, the unique ID for WebApp is wa_webAppItemID, and the field that relates to the WebMap node is wa_webMapItemID, In the WebApp node the wa_webMapItemID is not unique. Can this field be used in the relationship? The cypher for this is:

:param {
  // Define the file path root and the individual file names required for loading.
  // https://neo4j.com/docs/operations-manual/current/configuration/file-locations/
  file_path_root: 'file:///', // Change this to the folder your script can access the files at.
  file_0: 'WebMaps.csv',
  file_1: 'WebMappingApplications.csv',
  file_2: '1.Relationship_WebMappingApplications_to_WebMaps.csv'
};

// CONSTRAINT creation
// -------------------
//
// Create node uniqueness constraints, ensuring no duplicates for the given node label and ID property exist in the database. This also ensures no duplicates are introduced in future.
//
// NOTE: The following constraint creation syntax is generated based on the current connected database version 5.19-aura.
CREATE CONSTRAINT `wa_webMapItemId_WebApp_uniq` IF NOT EXISTS
FOR (n: `WebApp`)
REQUIRE (n.`wa_webMapItemId`) IS UNIQUE;
CREATE CONSTRAINT `wm_webMapItemId_WebMap_uniq` IF NOT EXISTS
FOR (n: `WebMap`)
REQUIRE (n.`wm_webMapItemId`) IS UNIQUE;

:param {
  idsToSkip: []
};

// NODE load
// ---------
//
// Load nodes in batches, one node label at a time. Nodes will be created using a MERGE statement to ensure a node with the same label and ID property remains unique. Pre-existing nodes found by a MERGE statement will have their other properties set to the latest values encountered in a load file.
//
// NOTE: Any nodes with IDs in the 'idsToSkip' list parameter will not be loaded.
LOAD CSV WITH HEADERS FROM ($file_path_root + $file_1) AS row
WITH row
WHERE NOT row.`wa_webMapItemId` IN $idsToSkip AND NOT row.`wa_webMapItemId` IS NULL
CALL {
  WITH row
  MERGE (n: `WebApp` { `wa_webMapItemId`: row.`wa_webMapItemId` })
  SET n.`wa_webMapItemId` = row.`wa_webMapItemId`
  SET n.`wa_webAppItemId` = row.`wa_webAppItemId`
  SET n.`wa_webAppTitle` = row.`wa_webAppTitle`
  SET n.`wa_webAppNumViews` = toInteger(trim(row.`wa_webAppNumViews`))
  SET n.`wa_webAppOwner` = row.`wa_webAppOwner`
  SET n.`wa_webAppSnippet` = row.`wa_webAppSnippet`
  SET n.`wa_webAppModified` = row.`wa_webAppModified`
  SET n.`wa_webAppCreated` = row.`wa_webAppCreated`
  SET n.`wa_webAppAccess` = row.`wa_webAppAccess`
  SET n.`wa_webAppItemURL` = row.`wa_webAppItemURL`
  SET n.`wa_description` = row.`wa_description`
  SET n.`wa_documentation` = row.`wa_documentation`
  SET n.`wa_groupDesignations` = row.`wa_groupDesignations`
  SET n.`wa_listed` = row.`wa_listed`
  SET n.`wa_num` = toInteger(trim(row.`wa_num`))
  SET n.`wa_numComments` = toLower(trim(row.`wa_numComments`)) IN ['1','true','yes']
  SET n.`wa_accessInformation` = row.`wa_accessInformation`
  SET n.`wa_numRatings` = toLower(trim(row.`wa_numRatings`)) IN ['1','true','yes']
  SET n.`wa_properties` = row.`wa_properties`
  SET n.`wa_scoreCompleteness` = toInteger(trim(row.`wa_scoreCompleteness`))
  SET n.`wa_size` = toInteger(trim(row.`wa_size`))
  SET n.`wa_spatialReference` = row.`wa_spatialReference`
  SET n.`wa_total` = toInteger(trim(row.`wa_total`))
  SET n.`wa_type` = row.`wa_type`
  SET n.`wa_url` = row.`wa_url`
  SET n.`wa_tags` = row.`wa_tags`
  SET n.`wa_typeKeywords` = row.`wa_typeKeywords`
  SET n.`wa_environment` = row.`wa_environment`
  SET n.`wa_thumbnailURL` = row.`wa_thumbnailURL`
  SET n.`wa_viewURL` = row.`wa_viewURL`
  SET n.`wa_traceURL` = row.`wa_traceURL`
} IN TRANSACTIONS OF 10000 ROWS;

LOAD CSV WITH HEADERS FROM ($file_path_root + $file_0) AS row
WITH row
WHERE NOT row.`wm_webMapItemId` IN $idsToSkip AND NOT row.`wm_webMapItemId` IS NULL
CALL {
  WITH row
  MERGE (n: `WebMap` { `wm_webMapItemId`: row.`wm_webMapItemId` })
  SET n.`wm_webMapItemId` = row.`wm_webMapItemId`
  SET n.`wm_webMapTitle` = row.`wm_webMapTitle`
  SET n.`wm_webMapNumViews` = toInteger(trim(row.`wm_webMapNumViews`))
  SET n.`wm_webMapOwner` = row.`wm_webMapOwner`
  SET n.`wm_webMapSnippet` = row.`wm_webMapSnippet`
  SET n.`wm_webMapModified` = row.`wm_webMapModified`
  SET n.`wm_webMapCreated` = row.`wm_webMapCreated`
  SET n.`wm_webMapAccess` = row.`wm_webMapAccess`
  SET n.`wm_webMapItemURL` = row.`wm_webMapItemURL`
  SET n.`wm_description` = row.`wm_description`
  SET n.`wm_documentation` = row.`wm_documentation`
  SET n.`wm_groupDesignations` = row.`wm_groupDesignations`
  SET n.`wm_listed` = row.`wm_listed`
  SET n.`wm_num` = toInteger(trim(row.`wm_num`))
  SET n.`wm_numComments` = toLower(trim(row.`wm_numComments`)) IN ['1','true','yes']
  SET n.`wm_numRatings` = toLower(trim(row.`wm_numRatings`)) IN ['1','true','yes']
  SET n.`wm_properties` = row.`wm_properties`
  SET n.`wm_accessInformation` = row.`wm_accessInformation`
  SET n.`wm_scoreCompleteness` = toInteger(trim(row.`wm_scoreCompleteness`))
  SET n.`wm_size` = toInteger(trim(row.`wm_size`))
  SET n.`wm_spatialReference` = row.`wm_spatialReference`
  SET n.`wm_total` = toInteger(trim(row.`wm_total`))
  SET n.`wm_type` = row.`wm_type`
  SET n.`wm_url` = row.`wm_url`
  SET n.`wm_tags` = row.`wm_tags`
  SET n.`wm_typeKeywords` = row.`wm_typeKeywords`
  SET n.`wm_environment` = row.`wm_environment`
  SET n.`wm_thumbnailURL` = row.`wm_thumbnailURL`
  SET n.`wm_viewURL` = row.`wm_viewURL`
  SET n.`wm_traceURL` = row.`wm_traceURL`
} IN TRANSACTIONS OF 10000 ROWS;


// RELATIONSHIP load
// -----------------
//
// Load relationships in batches, one relationship type at a time. Relationships are created using a MERGE statement, meaning only one relationship of a given type will ever be created between a pair of nodes.
LOAD CSV WITH HEADERS FROM ($file_path_root + $file_2) AS row
WITH row 
CALL {
  WITH row
  MATCH (source: `WebApp` { `wa_webMapItemId`: row.`wa_webMapItemId` })
  MATCH (target: `WebMap` { `wm_webMapItemId`: row.`wm_webMapItemId` })
  MERGE (source)-[r: `CONTAINS`]->(target)
  SET r.`wa_webMapItemId` = row.`wa_webMapItemId`
  SET r.`wm_webMapItemId` = row.`wm_webMapItemId`
} IN TRANSACTIONS OF 10000 ROWS;

LOAD CSV WITH HEADERS FROM ($file_path_root + $file_2) AS row
WITH row 
CALL {
  WITH row
  MATCH (source: `WebMap` { `wm_webMapItemId`: row.`wa_webMapItemId` })
  MATCH (target: `WebApp` { `wa_webMapItemId`: row.`wm_webMapItemId` })
  MERGE (source)-[r: `SUPPLIES`]->(target)
  SET r.`wa_webMapItemId` = row.`wa_webMapItemId`
  SET r.`wm_webMapItemId` = row.`wm_webMapItemId`
} IN TRANSACTIONS OF 10000 ROWS;

Relationship Table

wa_webMapItemId	wm_webMapItemId
892ce0c8327749558e78931529ac3715	892ce0c8327749558e78931529ac3715
de9623df5d424f59a0979e520cbe9995	de9623df5d424f59a0979e520cbe9995
daa210a77e2a4268b178d30b5fbad0d3	daa210a77e2a4268b178d30b5fbad0d3
e80ad8ad3c784425a31ba6a3c6589910	e80ad8ad3c784425a31ba6a3c6589910
932ba4b4f96f4a32b7c9ad14ab8aee06	932ba4b4f96f4a32b7c9ad14ab8aee06
7186397bde0d4c6792b4955c10ccd09d	7186397bde0d4c6792b4955c10ccd09d
de9623df5d424f59a0979e520cbe9995	de9623df5d424f59a0979e520cbe9995

WebMap Table:

wm_webMapItemId	wm_webMapTitle
b7f6525c6c814839a46d15ee4c28e8a0	System Performance ips_QA tgweb 3.3
1e6d1b7e3a65466e8858dfdd3fb774af	Servicing Strategy for Helensville and Parakai WebMap
0a3ebcd3c856475a8a63ee8de9d3705b	System Performance UAT ips_tgweb 3.3
99e0c8d5361c41c68b024b47fcd0ad0b	Netview for Customer Care (pgweb 2.0) Web Map
01466cdb5be841dd889a47b091bf461d	Netview (pgweb 2.0) Web Map
9f649da4546e4c798683a81c95d5ff14	System Performance ips_pgweb 3.3
ee47835831014091a688274020e5ec4a	Netview for Operations  (pgweb 2.0) Web Map
217882f274d04ee8953e0f8d50b6e65d	Netview for Developer Services (pgweb 2.1) Web Map
42adbd692d2d4271a9f6e7ab8c302800	Netview for Planning (pgweb 2.0) Web Map
4b51ca66a6184e15bb7454c11a077fc7	Netview for MSN (pgweb 2.0) Web Map
239e11492d2942debbab6c4c8ad8190d	Netview for Property (pgweb 2.0) Web Map
2b92642a9b8b41e7a3b8f0ce0b2f3886	Netview for Worksover (pgweb 2.0) Web Map
892ce0c8327749558e78931529ac3715	Address Locator_FME Prod Internal Web Map (pgweb 1.0)
450693af4f1c4882a0c29abae5756391	Servicing Strategy for Helensville and Parakai WebMap
7f134260b8a44286a6b6bf3a0cb4162c	Netview for Major Developments (pgweb 2.0) Web Map
de9623df5d424f59a0979e520cbe9995	Hawkeye Dashboard Web Map (pgweb 1.0)
7186397bde0d4c6792b4955c10ccd09d	Central Interceptor (tgwebe 2.0) Web Map
daa210a77e2a4268b178d30b5fbad0d3	[Archive]Water Outage Dashboard Web Map (pgweb 1.0)
932ba4b4f96f4a32b7c9ad14ab8aee06	Central Interceptor Editing (tgwebe 1.0) Web Map
e80ad8ad3c784425a31ba6a3c6589910	Water Outage Dashboard Web Map (pgweb 1.1)
d7182cf18e694f96a3a02020680aeb69	P1 P2 & P3  Live Incidents (pgappe 1.0)
f734ceca531f4ff3890b09e352769b4a	P1 P2 & P3  Incidents (pgweb 1.0)
b653dd29fd7b4b7686d660fb5b3b3739	Service Folder Internal ArcGIS Enterprise
5090901a5ef54dee96468b84a6dce2bd	Analysis Map
57cea879928f4d749d881a9e1b395df7	Default Base Map
4bba941f886541f2a82f81cd353cfad8	Rosedale SMA
1425c5733b144fb1a1c47cf35809b682	Default Base Map-Copy
37d81d182b724e5b9298cea16b7e6f84	Army Bay NI and Rosedale SMA - catchments and populations

WebApp Tables:

wa_webAppItemId	wa_webAppTitle	wa_webMapItemId
1f52fe12426a442490d6793cff70569d	Address Locator_FME Prod Internal Web App (pgweb 1.0)	892ce0c8327749558e78931529ac3715
1c5c50b97527454689cec93bfc110421	Hawkeye Dashboard (pgweb 1.0)	de9623df5d424f59a0979e520cbe9995
443b0128ac924530beef28db519a83d7	[Archive]Water Outage Dashboard (pgweb 1.0)	daa210a77e2a4268b178d30b5fbad0d3
ea1ae398c7d64095b1d9cbbb1ed48ade	Water Outage Dashboard (pgweb 1.1)	e80ad8ad3c784425a31ba6a3c6589910
f8cba01880384ed4bcbaf100d3391986	Central Interceptor Editing (tgwebe 1.0) Web App	932ba4b4f96f4a32b7c9ad14ab8aee06
19ebf017db5c4b79b51845a59094cb3b	Central Interceptor (tgwebe 2.0) Web App	7186397bde0d4c6792b4955c10ccd09d
f9066d2031ac4baeb317a6e7c5f1a23a	P1 P2 & P3  Incidents (pgweb 1.0) (copy)	de9623df5d424f59a0979e520cbe9995

Topic		Replies	Views
Relationships not appearing in Aura after csv import (Neo4j Data Importer) while following the "Building Knowledge Graphs A Practitioner's Guide" Chapter 4 instructions Cypher browser , cypher , import	1	268	December 14, 2023
Preview works fine but import does not create nodes nor relationships General import	0	92	March 22, 2024
Newbie issue on Importer: succeeding to import nodes but not relationship Neo4j Graph Platform migrated	2	109	July 9, 2022
Hi from Clem, long time lurker, new real user Introduce-Yourself	0	269	November 5, 2020
Same type relationship with different property cannot be loaded with aura's importer tool Aura & Cloud import	1	208	May 24, 2023

My first KG! Relationships not working!

Related topics