Neo4j中apoc.periodic.iterate内使用split函数报错的解决求助

问题场景 #

使用apoc.periodic.iterate批量导入CSV数据时，原查询可正常执行，但在WITH语句中加入split(csv.patients, ",")字符串分割函数后，触发参数数量错误的报错。

正常运行的查询 #

WITH collect(file.URL) AS fileURLs
UNWIND fileURLs AS fileURL
CALL apoc.periodic.iterate(
'
CALL apoc.load.csv($url) YIELD map AS csv
RETURN csv
',
'
WITH round(toFloat(csv.dendrogram_height),3,"HALF_UP") as height, csv.gene_name as gene, csv.event_no as event, csv.samples as sp, round(toFloat(csv.median_ge),3,"HALF_UP") as me, csv.no_of_samples as no_s, csv.leaf_status as leaf, csv.patients as pts, csv.no_of_uniq_patients as no_pt, round((toFloat(csv.log2fc)),3,"HALF_UP") as log2, round(toFloat(csv.padj_deseq2),3,"HALF_UP") as padj, csv.up_or_down as up
CREATE (:Expression:Primary {name: gene, event_no: event, dendrogram_height: height, samples: sp, median_exp: me, no_of_samples: no_s, leaf_status: leaf, patients: pts, no_of_unique_patients: no_pt, log2fc: log2, padj_deseq2: padj, up_or_down: up});
',
{batchSize:10000,parallel:true,params:{url:fileURL}}) YIELD batches, total
RETURN batches, total;

修改后报错的查询 #

MATCH (file:File) WITH file
WITH collect(file.URL) AS fileURLs
UNWIND fileURLs AS fileURL
CALL apoc.periodic.iterate(
'
CALL apoc.load.csv($url) YIELD map AS csv
RETURN csv
',
'
WITH round(toFloat(csv.dendrogram_height),3,"HALF_UP") as height, csv.gene_name as gene, csv.event_no as event, csv.samples as sp, round(toFloat(csv.median_ge),3,"HALF_UP") as me, csv.no_of_samples as no_s, csv.leaf_status as leaf, split(csv.patients,",") as pts, csv.no_of_uniq_patients as no_pt, round((toFloat(csv.log2fc)),3,"HALF_UP") as log2, round(toFloat(csv.padj_deseq2),3,"HALF_UP") as padj, csv.up_or_down as up
CREATE (:Expression:Primary {name: gene, event_no: event, dendrogram_height: height, samples: sp, median_exp: me, no_of_samples: no_s, leaf_status: leaf, patients: pts, no_of_unique_patients: no_pt, log2fc: log2, padj_deseq2: padj, up_or_down: up});
',
{batchSize:10000,parallel:true,params:{url:fileURL}}) YIELD batches, total
RETURN batches, total;

报错信息 #

Procedure call provides too many arguments: got 4 expected no more than 3.

Procedure apoc.periodic.iterate has signature: apoc.periodic.iterate(cypherIterate :: STRING?, cypherAction :: STRING?, config :: MAP?) :: batches :: INTEGER?, total :: INTEGER?, timeTaken :: INTEGER?, committedOperations :: INTEGER?, failedOperations :: INTEGER?, failedBatches :: INTEGER?, retries :: INTEGER?, errorMessages :: MAP?, batch :: MAP?, operations :: MAP?, wasTerminated :: BOOLEAN?, failedParams :: MAP?, updateStatistics :: MAP?
meaning that it expects at least 3 arguments of types STRING?, STRING?, MAP?
Description: Runs the second statement for each item returned by the first statement.
This procedure returns the number of batches and the total number of processed rows. (line 4, column 1 (offset: 90))
"CALL apoc.periodic.iterate("
 ^

报错原因 #

apoc.periodic.iterate的第二个参数是字符串形式的Cypher操作语句，当语句内部出现未转义的逗号时，Cypher解析器会错误地将这个逗号识别为apoc.periodic.iterate的参数分隔符，导致原本的3个参数被解析成4个，触发参数数量超限的报错。

解决方法 #

方法1：转义操作语句中的逗号 #

将split(csv.patients,",")中的逗号用反斜杠转义，避免被解析为函数参数分隔符：

MATCH (file:File) WITH file
WITH collect(file.URL) AS fileURLs
UNWIND fileURLs AS fileURL
CALL apoc.periodic.iterate(
'
CALL apoc.load.csv($url) YIELD map AS csv
RETURN csv
',
'
WITH round(toFloat(csv.dendrogram_height),3,"HALF_UP") as height, csv.gene_name as gene, csv.event_no as event, csv.samples as sp, round(toFloat(csv.median_ge),3,"HALF_UP") as me, csv.no_of_samples as no_s, csv.leaf_status as leaf, split(csv.patients,\",\") as pts, csv.no_of_uniq_patients as no_pt, round((toFloat(csv.log2fc)),3,"HALF_UP") as log2, round(toFloat(csv.padj_deseq2),3,"HALF_UP") as padj, csv.up_or_down as up
CREATE (:Expression:Primary {name: gene, event_no: event, dendrogram_height: height, samples: sp, median_exp: me, no_of_samples: no_s, leaf_status: leaf, patients: pts, no_of_unique_patients: no_pt, log2fc: log2, padj_deseq2: padj, up_or_down: up});
',
{batchSize:10000,parallel:true,params:{url:fileURL}}) YIELD batches, total
RETURN batches, total;

方法2：参数化传递分隔符 #

将分隔符作为参数传入配置的params中，在操作语句里引用该参数，彻底避免字符串内部逗号的解析问题：

MATCH (file:File) WITH file
WITH collect(file.URL) AS fileURLs
UNWIND fileURLs AS fileURL
CALL apoc.periodic.iterate(
'
CALL apoc.load.csv($url) YIELD map AS csv
RETURN csv
',
'
WITH round(toFloat(csv.dendrogram_height),3,"HALF_UP") as height, csv.gene_name as gene, csv.event_no as event, csv.samples as sp, round(toFloat(csv.median_ge),3,"HALF_UP") as me, csv.no_of_samples as no_s, csv.leaf_status as leaf, split(csv.patients, $delimiter) as pts, csv.no_of_uniq_patients as no_pt, round((toFloat(csv.log2fc)),3,"HALF_UP") as log2, round(toFloat(csv.padj_deseq2),3,"HALF_UP") as padj, csv.up_or_down as up
CREATE (:Expression:Primary {name: gene, event_no: event, dendrogram_height: height, samples: sp, median_exp: me, no_of_samples: no_s, leaf_status: leaf, patients: pts, no_of_unique_patients: no_pt, log2fc: log2, padj_deseq2: padj, up_or_down: up});
',
{batchSize:10000,parallel:true,params:{url:fileURL, delimiter: ","}}) YIELD batches, total
RETURN batches, total;

内容的提问来源于stack exchange，提问作者user2167741