Cypher Query Validation

[akshat_mittal]

Hello team ,

Please help me in validating the cypher query for bulk deletion of data under specific label:

call apoc.periodic.iterate("MATCH (n:SITE) return n", "DETACH DELETE n", {batchSize:1000}) yield batches, total return batches, total

Regards
Akshat

[stefan_armbrust]

This will work unless you have SITE nodes with a huge number (thinking millions or more) of relationships. In this case the DETACH DELETE will become too fat.

The way to handle this is to use two statements:

// delete relationships first
call apoc.periodic.iterate("MATCH (n:SITE)-[r]-() RETURN r", "DELETE r", {batchSize:1000});
// delete nodes second
call apoc.periodic.iterate("MATCH (n:SITE) RETURN n", "DETACH DELETE n", {batchSize:1000});

[akshat_mittal]

Hello Stefan,

Thanks for a quick reply.

I have 5 labels and their count respectively:

These labels are related like site to container , container to shelf , shelf to card , card to port .
Hence 4 relationship amongst 5 labels.

Best Regards
Akshat

[akshat_mittal]

Hello Mate,

How to deal with these type of query which contains millions of data:

call apoc.periodic.iterate("MATCH (m:MO_STATIC_TABLE)<-[r1:container2mostatic]-(c:CONTAINER) ,
(sc:SITE_CODE_TABLE)<-[R2]-(s:SITE)-[r4:site2container]->(c:CONTAINER)-[r5:container2shelf]->(sh:SHELF)-[r6:shelf2card]->(ca:CARD)-[r7:card2port]->(po:PORT) where c.EQUIP_TYPE='RRH' and m.STD_VENDOR='SAMSUNG'", "set po.PORT_CUSTOM_MANAGEDOBJECT='RIL_SAMSUNG .'+m.STD_TNS+'.'+c.EQUIP_EMS_INSTANCENAME+' NODE "'+po.PORT_NATIVENAME+'"',
po.PORT_CUSTOM_OPERATIONCONTEXT='.oc.'+sc.SITE_CIRCLE_CODE_OC+'_LSM-R'" , {batchSize:1000}) yield batches, total return "C 2 MO rel" , batches, total;

Best Regards
Akshat

[stefan_armbrust]

The approach I've sketched above is exactly what you want to use when deleting a large number of nodes: first remove relationships then remove nodes. Both steps in reasonable batches.

What exactly is the issue now?

[akshat_mittal]

Hello Stefan,

Issue is server connection getting lost while running the above query which sets the 2 properties under specific label for lacs of records in it.
So I need some faster way to do this.

Best Regards
Akshat

[stefan_armbrust]

Which version are you using? Both neo4j and apoc version.

[akshat_mittal]

Hello Stefan ,

Neo4j Version = 3.5.3
APOC Version = apoc-3.5.0.4-all.jar

Best Regards
Akshat

[akshat_mittal]

This is the error we got -

[root@jmngdprv009693 bin]# ./neo4j stop
Stopping Neo4j......................................................................................................................... failed to stop
Neo4j (pid 76877) took more than 120 seconds to stop.
Please see /datasan01/neo4japp/neo4j-enterprise-3.5.3/logs/neo4j.log for details.
[root@jmngdprv009693 bin]# ./neo4j status
Neo4j is running at pid 76877
[root@jmngdprv009693 bin]# kill -9 76877
[root@jmngdprv009693 bin]# ./neo4j status
Neo4j is running at pid 76877
[root@jmngdprv009693 bin]# ./neo4j status
Neo4j is not running
[root@jmngdprv009693 bin]# ./neo4j start
Active database: graph.db
Directories in use:
home: /datasan01/neo4japp/neo4j-enterprise-3.5.3
config: /datasan01/neo4japp/neo4j-enterprise-3.5.3/conf
logs: /datasan01/neo4japp/neo4j-enterprise-3.5.3/logs
plugins: /datasan01/neo4japp/neo4j-enterprise-3.5.3/plugins
import: /datasan01/neo4japp/neo4j-enterprise-3.5.3/import
data: /datasan01/neo4japp/neo4j-enterprise-3.5.3/data
certificates: /datasan01/neo4japp/neo4j-enterprise-3.5.3/certificates
run: /datasan01/neo4japp/neo4j-enterprise-3.5.3/run
Starting Neo4j.
Started neo4j (pid 903). It is available at http://0.0.0.0:7474/
There may be a short delay until the server is ready.
See /datasan01/neo4japp/neo4j-enterprise-3.5.3/logs/neo4j.log for current status.
[root@jmngdprv009693 bin]# ./neo4j status
Neo4j is running at pid 903
[root@jmngdprv009693 bin]#

[stefan_armbrust]

I don't remember in which apoc version we've fixed this. In the old days you needed to prefix the first statement with CYPHER runtime=slotted - otherwise it could have happened that the query result gets fully materialized in heap before the second statement is run.

call apoc.periodic.iterate("CYPHER runtime=slotted MATCH ....", "...", { ...})

[akshat_mittal]

Hello Stefan,

Getting below error when i added the runtime=slotted .

call apoc.periodic.iterate("CYPHER runtime=slotted MATCH (m:MO_STATIC_TABLE)<-[r1:container2mostatic]-(c:CONTAINER) ,(sc:SITE_CODE_TABLE)<-[R2]-(s:SITE)-[r4:site2container]->(c:CONTAINER)-[r5:container2shelf]->(sh:SHELF)-[r6:shelf2card]->(ca:CARD)-[r7:card2port]->(po:PORT) where c.EQUIP_TYPE='RRH' and m.STD_VENDOR='SAMSUNG'", "set po.PORT_CUSTOM_MANAGEDOBJECT='RIL_SAMSUNG .'+m.STD_TNS+'.'+c.EQUIP_EMS_INSTANCENAME+' NODE "'+po.PORT_NATIVENAME+'"',po.PORT_CUSTOM_OPERATIONCONTEXT='.oc.'+sc.SITE_CIRCLE_CODE_OC+'_LSM-R'" , {batchSize:1000}) yield batches, total return "C 2 MO rel" , batches, total;

Error:
Neo.ClientError.Statement.SyntaxError: Invalid input ''': expected whitespace, '.', node labels, '[', "=~", IN, STARTS, ENDS, CONTAINS, IS, '^', '*', '/', '%', '+', '-', '=', '~', "<>", "!=", '<', '>', "<=", ">=", AND, XOR, OR, ',' or ')' (line 1, column 426 (offset: 425))
"call apoc.periodic.iterate("CYPHER runtime=slotted MATCH (m:MO_STATIC_TABLE)<-[r1:container2mostatic]-(c:CONTAINER) ,(sc:SITE_CODE_TABLE)<-[R2]-(s:SITE)-[r4:site2container]->(c:CONTAINER)-[r5:container2shelf]->(sh:SHELF)-[r6:shelf2card]->(ca:CARD)-[r7:card2port]->(po:PORT) where c.EQUIP_TYPE='RRH' and m.STD_VENDOR='SAMSUNG'", "set po.PORT_CUSTOM_MANAGEDOBJECT='RIL_SAMSUNG .'+m.STD_TNS+'.'+c.EQUIP_EMS_INSTANCENAME+' NODE "'+po.PORT_NATIVENAME+'"',po.PORT_CUSTOM_OPERATIONCONTEXT='.oc.'+sc.SITE_CIRCLE_CODE_OC+'_LSM-R'" , {batchSize:1000}) yield batches, total return "C 2 MO rel" , batches, total;"
^

Best Regards
Akshat

[andrew_bowman]

You're running into a nested quotes issue. In the driving query (first query) of the iterate() call you're using double quotes around the query, with single quotes as needed around strings in that query. That one's fine.

But your updating query (the second one) has double-quotes in it, which terminates the string, and you're not handling the rest properly. If you do need to use double-quotes in your double-quote delimited string, you need to escape them so they don't end your string early and unexpectedly.

[akshat_mittal]

Hello Andrew,

Is this update correct from your side? I believe 2 queries ( posted by me ) is mixed up here.

Best Regards
Akshat