Sample "Generate Cypher script..." from Workspace: Import

neo4j_importer_cypher_script_2023-10-23.cypher

: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: 'territories.csv',
  file_1: 'regions.csv',
  file_2: 'suppliers.csv',
  file_3: 'shippers.csv',
  file_4: 'products.csv',
  file_5: 'categories.csv',
  file_6: 'orders.csv',
  file_7: 'customers.csv',
  file_8: 'employees.csv',
  file_9: 'employee-territories.csv',
  file_10: 'order-details.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: If your database version is below (not including) 4.4.0, please use the constraint creation syntax below:
// CREATE CONSTRAINT `imp_uniq_Territory_territoryID` IF NOT EXISTS
// ON (n: `Territory`)
// ASSERT n.`territoryID` IS UNIQUE;
// CREATE CONSTRAINT `imp_uniq_Region_regionID` IF NOT EXISTS
// ON (n: `Region`)
// ASSERT n.`regionID` IS UNIQUE;
// CREATE CONSTRAINT `imp_uniq_Supplier_supplierID` IF NOT EXISTS
// ON (n: `Supplier`)
// ASSERT n.`supplierID` IS UNIQUE;
// CREATE CONSTRAINT `imp_uniq_Shipper_shipperID` IF NOT EXISTS
// ON (n: `Shipper`)
// ASSERT n.`shipperID` IS UNIQUE;
// CREATE CONSTRAINT `imp_uniq_Product_productID` IF NOT EXISTS
// ON (n: `Product`)
// ASSERT n.`productID` IS UNIQUE;
// CREATE CONSTRAINT `imp_uniq_Category_categoryID` IF NOT EXISTS
// ON (n: `Category`)
// ASSERT n.`categoryID` IS UNIQUE;
// CREATE CONSTRAINT `imp_uniq_Order_orderID` IF NOT EXISTS
// ON (n: `Order`)
// ASSERT n.`orderID` IS UNIQUE;
// CREATE CONSTRAINT `imp_uniq_Customer_customerID` IF NOT EXISTS
// ON (n: `Customer`)
// ASSERT n.`customerID` IS UNIQUE;
// CREATE CONSTRAINT `imp_uniq_Employee_employeeID` IF NOT EXISTS
// ON (n: `Employee`)
// ASSERT n.`employeeID` IS UNIQUE;
//
// NOTE: The following constraint creation syntax is generated based on the current connected database version 4.4-aura.
CREATE CONSTRAINT `imp_uniq_Territory_territoryID` IF NOT EXISTS
FOR (n: `Territory`)
REQUIRE (n.`territoryID`) IS UNIQUE;
CREATE CONSTRAINT `imp_uniq_Region_regionID` IF NOT EXISTS
FOR (n: `Region`)
REQUIRE (n.`regionID`) IS UNIQUE;
CREATE CONSTRAINT `imp_uniq_Supplier_supplierID` IF NOT EXISTS
FOR (n: `Supplier`)
REQUIRE (n.`supplierID`) IS UNIQUE;
CREATE CONSTRAINT `imp_uniq_Shipper_shipperID` IF NOT EXISTS
FOR (n: `Shipper`)
REQUIRE (n.`shipperID`) IS UNIQUE;
CREATE CONSTRAINT `imp_uniq_Product_productID` IF NOT EXISTS
FOR (n: `Product`)
REQUIRE (n.`productID`) IS UNIQUE;
CREATE CONSTRAINT `imp_uniq_Category_categoryID` IF NOT EXISTS
FOR (n: `Category`)
REQUIRE (n.`categoryID`) IS UNIQUE;
CREATE CONSTRAINT `imp_uniq_Order_orderID` IF NOT EXISTS
FOR (n: `Order`)
REQUIRE (n.`orderID`) IS UNIQUE;
CREATE CONSTRAINT `imp_uniq_Customer_customerID` IF NOT EXISTS
FOR (n: `Customer`)
REQUIRE (n.`customerID`) IS UNIQUE;
CREATE CONSTRAINT `imp_uniq_Employee_employeeID` IF NOT EXISTS
FOR (n: `Employee`)
REQUIRE (n.`employeeID`) 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.`territoryID` IN $idsToSkip AND NOT row.`territoryID` IS NULL
CALL {
  WITH row
  MERGE (n: `Territory` { `territoryID`: row.`territoryID` })
  SET n.`territoryID` = row.`territoryID`
  SET n.`territoryDescription` = row.`territoryDescription`
} IN TRANSACTIONS OF 10000 ROWS;

LOAD CSV WITH HEADERS FROM ($file_path_root + $file_1) AS row
WITH row
WHERE NOT row.`regionID` IN $idsToSkip AND NOT row.`regionID` IS NULL
CALL {
  WITH row
  MERGE (n: `Region` { `regionID`: row.`regionID` })
  SET n.`regionID` = row.`regionID`
  SET n.`regionDescription` = row.`regionDescription`
} IN TRANSACTIONS OF 10000 ROWS;

LOAD CSV WITH HEADERS FROM ($file_path_root + $file_2) AS row
WITH row
WHERE NOT row.`supplierID` IN $idsToSkip AND NOT row.`supplierID` IS NULL
CALL {
  WITH row
  MERGE (n: `Supplier` { `supplierID`: row.`supplierID` })
  SET n.`supplierID` = row.`supplierID`
  SET n.`companyName` = row.`companyName`
  SET n.`contactName` = row.`contactName`
  SET n.`contactTitle` = row.`contactTitle`
  SET n.`address` = row.`address`
  SET n.`city` = row.`city`
  SET n.`region` = row.`region`
  SET n.`postalCode` = row.`postalCode`
  SET n.`country` = row.`country`
  SET n.`phone` = row.`phone`
  SET n.`fax` = row.`fax`
  SET n.`homePage` = row.`homePage`
} IN TRANSACTIONS OF 10000 ROWS;

LOAD CSV WITH HEADERS FROM ($file_path_root + $file_3) AS row
WITH row
WHERE NOT row.`shipperID` IN $idsToSkip AND NOT row.`shipperID` IS NULL
CALL {
  WITH row
  MERGE (n: `Shipper` { `shipperID`: row.`shipperID` })
  SET n.`shipperID` = row.`shipperID`
  SET n.`companyName` = row.`companyName`
  SET n.`phone` = row.`phone`
} IN TRANSACTIONS OF 10000 ROWS;

LOAD CSV WITH HEADERS FROM ($file_path_root + $file_4) AS row
WITH row
WHERE NOT row.`productID` IN $idsToSkip AND NOT row.`productID` IS NULL
CALL {
  WITH row
  MERGE (n: `Product` { `productID`: row.`productID` })
  SET n.`productID` = row.`productID`
  SET n.`productName` = row.`productName`
  SET n.`quantityPerUnit` = toInteger(trim(row.`quantityPerUnit`))
  SET n.`unitPrice` = toFloat(trim(row.`unitPrice`))
  SET n.`unitsInStock` = toInteger(trim(row.`unitsInStock`))
  SET n.`unitsOnOrder` = toInteger(trim(row.`unitsOnOrder`))
  SET n.`reorderLevel` = toInteger(trim(row.`reorderLevel`))
  SET n.`discontinued` = toLower(trim(row.`discontinued`)) IN ['1','true','yes']
} IN TRANSACTIONS OF 10000 ROWS;

LOAD CSV WITH HEADERS FROM ($file_path_root + $file_5) AS row
WITH row
WHERE NOT row.`categoryID` IN $idsToSkip AND NOT row.`categoryID` IS NULL
CALL {
  WITH row
  MERGE (n: `Category` { `categoryID`: row.`categoryID` })
  SET n.`categoryID` = row.`categoryID`
  SET n.`categoryName` = row.`categoryName`
  SET n.`description` = row.`description`
} IN TRANSACTIONS OF 10000 ROWS;

LOAD CSV WITH HEADERS FROM ($file_path_root + $file_6) AS row
WITH row
WHERE NOT row.`orderID` IN $idsToSkip AND NOT row.`orderID` IS NULL
CALL {
  WITH row
  MERGE (n: `Order` { `orderID`: row.`orderID` })
  SET n.`orderID` = row.`orderID`
  SET n.`orderDate` = row.`orderDate`
  SET n.`requiredDate` = row.`requiredDate`
  SET n.`shippedDate` = row.`shippedDate`
  SET n.`freight` = row.`freight`
  SET n.`shipName` = row.`shipName`
  SET n.`shipAddress` = row.`shipAddress`
  SET n.`shipCity` = row.`shipCity`
  SET n.`shipRegion` = row.`shipRegion`
  SET n.`shipPostalCode` = row.`shipPostalCode`
  SET n.`shipCountry` = row.`shipCountry`
} IN TRANSACTIONS OF 10000 ROWS;

LOAD CSV WITH HEADERS FROM ($file_path_root + $file_7) AS row
WITH row
WHERE NOT row.`customerID` IN $idsToSkip AND NOT row.`customerID` IS NULL
CALL {
  WITH row
  MERGE (n: `Customer` { `customerID`: row.`customerID` })
  SET n.`customerID` = row.`customerID`
  SET n.`companyName` = row.`companyName`
  SET n.`contactName` = row.`contactName`
  SET n.`contactTitle` = row.`contactTitle`
  SET n.`address` = row.`address`
  SET n.`city` = row.`city`
  SET n.`region` = row.`region`
  SET n.`postalCode` = row.`postalCode`
  SET n.`country` = row.`country`
  SET n.`phone` = row.`phone`
  SET n.`fax` = row.`fax`
} IN TRANSACTIONS OF 10000 ROWS;

LOAD CSV WITH HEADERS FROM ($file_path_root + $file_8) AS row
WITH row
WHERE NOT row.`employeeID` IN $idsToSkip AND NOT row.`employeeID` IS NULL
CALL {
  WITH row
  MERGE (n: `Employee` { `employeeID`: row.`employeeID` })
  SET n.`employeeID` = row.`employeeID`
  SET n.`lastName` = row.`lastName`
  SET n.`firstName` = row.`firstName`
  SET n.`title` = row.`title`
  SET n.`titleOfCourtesy` = row.`titleOfCourtesy`
  SET n.`birthDate` = row.`birthDate`
  SET n.`hireDate` = row.`hireDate`
  SET n.`address` = row.`address`
  SET n.`city` = row.`city`
  SET n.`region` = row.`region`
  SET n.`postalCode` = row.`postalCode`
  SET n.`country` = row.`country`
  SET n.`homePhone` = row.`homePhone`
  SET n.`extension` = row.`extension`
  SET n.`notes` = row.`notes`
  SET n.`photoPath` = row.`photoPath`
} 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_0) AS row
WITH row 
CALL {
  WITH row
  MATCH (source: `Territory` { `territoryID`: row.`territoryID` })
  MATCH (target: `Region` { `regionID`: row.`regionID` })
  MERGE (source)-[r: `IN_REGION`]->(target)
} IN TRANSACTIONS OF 10000 ROWS;

LOAD CSV WITH HEADERS FROM ($file_path_root + $file_4) AS row
WITH row 
CALL {
  WITH row
  MATCH (source: `Supplier` { `supplierID`: row.`supplierID` })
  MATCH (target: `Product` { `productID`: row.`productID` })
  MERGE (source)-[r: `SUPPLIES`]->(target)
} IN TRANSACTIONS OF 10000 ROWS;

LOAD CSV WITH HEADERS FROM ($file_path_root + $file_4) AS row
WITH row 
CALL {
  WITH row
  MATCH (source: `Product` { `productID`: row.`productID` })
  MATCH (target: `Category` { `categoryID`: row.`categoryID` })
  MERGE (source)-[r: `PART_OF`]->(target)
} IN TRANSACTIONS OF 10000 ROWS;

LOAD CSV WITH HEADERS FROM ($file_path_root + $file_6) AS row
WITH row 
CALL {
  WITH row
  MATCH (source: `Shipper` { `shipperID`: row.`shipVia` })
  MATCH (target: `Order` { `orderID`: row.`orderID` })
  MERGE (source)-[r: `SHIPS`]->(target)
} IN TRANSACTIONS OF 10000 ROWS;

LOAD CSV WITH HEADERS FROM ($file_path_root + $file_6) AS row
WITH row 
CALL {
  WITH row
  MATCH (source: `Customer` { `customerID`: row.`customerID` })
  MATCH (target: `Order` { `orderID`: row.`orderID` })
  MERGE (source)-[r: `PURCHASED`]->(target)
} IN TRANSACTIONS OF 10000 ROWS;

LOAD CSV WITH HEADERS FROM ($file_path_root + $file_6) AS row
WITH row 
CALL {
  WITH row
  MATCH (source: `Employee` { `employeeID`: row.`employeeID` })
  MATCH (target: `Order` { `orderID`: row.`orderID` })
  MERGE (source)-[r: `SOLD`]->(target)
} IN TRANSACTIONS OF 10000 ROWS;

LOAD CSV WITH HEADERS FROM ($file_path_root + $file_8) AS row
WITH row 
CALL {
  WITH row
  MATCH (source: `Employee` { `employeeID`: row.`employeeID` })
  MATCH (target: `Employee` { `employeeID`: row.`reportsTo` })
  MERGE (source)-[r: `REPORTS_TO`]->(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: `Employee` { `employeeID`: row.`employeeID` })
  MATCH (target: `Territory` { `territoryID`: row.`territoryID` })
  MERGE (source)-[r: `IN_TERRITORY`]->(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: `Order` { `orderID`: row.`orderID` })
  MATCH (target: `Product` { `productID`: row.`productID` })
  MERGE (source)-[r: `ORDERS`]->(target)
  SET r.`unitPrice` = toFloat(trim(row.`unitPrice`))
  SET r.`quantity` = toInteger(trim(row.`quantity`))
  SET r.`discount` = toFloat(trim(row.`discount`))
} IN TRANSACTIONS OF 10000 ROWS;