ETL giving up after one error #6872

ghost · 2016-11-02T10:44:45Z

OrientDB Version, operating system, or hardware.

v2.0 SNAPSHOT[ ] - .18[ ] .17[ ] .16[ ] .15[ ] .14[ ] .13[ ] .12[ ] .11[ ] .10[ ] .9[ ] .8[ ] .7[ ] .6[ ] .5[ ] .4[ ] .3[ ] .2[ ] .1[ ] .0[ ]
v2.1 SNAPSHOT[ ] - .16[ ] .15[ ] .14[ ] .13[ ] .12[ ] .11[ ] .10[ ] .9[ ] .8[ ] .7[ ] .6[ ] .5[ ] .4[ ] .3[ ] .2[ ] .1[ ] .0[ ]
v2.2 SNAPSHOT[ 12] - .rc1[ ] .beta2[ ] .beta1[ ]

Operating System

[x ] Linux
MacOSX
Windows
Other Unix
Other, name?

Error in Pipeline execution: com.orientechnologies.orient.core.exception.OCommandExecutionException: No edge has been created because no target vertices
Why give up(see below log)? Can't this process just report the problem and continue? With millions of edges in question this is behavior is inefficient and wasting lots of time. In the other ETL transformers you can specify "unresolvedLinkAction". Why not add this(or similar features) to the Command Transformer also?

Expected behavior and actual behavior

From a three column csv-file(arg1,arg2,arg3) arguments are taken to create edges with one property.

The essence of the actual ETL json config is this:
"command" :
create edge from (select from ClassX where $input.arg1..) to (select from ClassX where $input.arg2..) set something=$input.arg3

Here is what happens if one target vertice is not found:

excerpt from logging:

extracted 7,996 rows (5 rows/sec) - 7,996 rows -> loaded 7,493 vertices (5 vertices/sec) Total time: 1662154ms [0 warnings, 0 errors]
extracted 8,000 rows (4 rows/sec) - 8,000 rows -> loaded 7,497 vertices (4 vertices/sec) Total time: 1663154ms [0 warnings, 0 errors]
extracted 8,004 rows (4 rows/sec) - 8,004 rows -> loaded 7,501 vertices (4 vertices/sec) Total time: 1664154ms [0 warnings, 0 errors]
extracted 8,008 rows (4 rows/sec) - 8,008 rows -> loaded 7,505 vertices (4 vertices/sec) Total time: 1665154ms [0 warnings, 0 errors]
Error in Pipeline execution: com.orientechnologies.orient.core.exception.OCommandExecutionException: No edge has been created because no target vertices
DB name="XXXX"
DB name="XXXX"
extracted 8,010 rows (1 rows/sec) - 8,010 rows -> loaded 7,507 vertices (1 vertices/sec) Total time: 1666155ms [0 warnings, 1 errors]
extracted 8,010 rows (0 rows/sec) - 8,010 rows -> loaded 7,507 vertices (0 vertices/sec) Total time: 1667155ms [0 warnings, 1 errors]
extracted 8,010 rows (0 rows/sec) - 8,010 rows -> loaded 7,507 vertices (0 vertices/sec) Total time: 1668155ms [0 warnings, 1 errors]
extracted 8,010 rows (0 rows/sec) - 8,010 rows -> loaded 7,507 vertices (0 vertices/sec) Total time: 1669155ms [0 warnings, 1 errors]
extracted 8,010 rows (0 rows/sec) - 8,010 rows -> loaded 7,507 vertices (0 vertices/sec) Total time: 1670156ms [0 warnings, 1 errors]

Steps to reproduce the problem

You need a class containing some 1000' records and a list of the vertices to be linked (see above).

robfrank · 2016-11-02T15:58:59Z

Can you provide me the configuration and sample data?

ghost · 2016-11-02T17:33:45Z

robfrank,

Linking items from the same class.

You need to edit filename in json and make your own for the inital data to be linked.
The linking will fail once it hits a col2-item that does not exist in the initially loaded data.
You need to edit/create your own json to load the classfile.

etlsamples.tar.gz

robfrank · 2016-11-03T09:08:28Z

Sorry, but I don't understand how to use your samples. The config json seems truncated.
Could you please write down the entire procedure and provide a working configuration?

ghost · 2016-11-03T09:49:10Z

The json is deliberately truncated. I expect you to fill in the remaining stuff yourself, but here it is, passwords removed.

run ETL to load the classfile - master data if you like.
{
"config": {
"parallel": false,
"log": "INFO"
},
"source": { "file": { "path": "/path-to-/" } },
"extractor": { "csv": {"separator": ","} },
"transformers": [
{ "vertex": { "class": "HumanProtein" } }
],
"loader": {
"orientdb": {
"dbType": "graph",
"dbURL": "remote:localhost/XXXXX",
"dbUser": "admin",
"dbPassword": "admin",
"serverUser": "root",
"serverPassword": "********",
"dbAutoCreate": false,
"wal": false,
"dbAutoCreateProperties": true,
"classes": [
{name: 'HumanProtein', extends:"V"}
],
"tx": false,
"batchCommit": 1600
}
}
}
run ETL with the below json.

{
"config": {
"parallel": false,
"log": "DEBUG"
},
"source": { "file": { "path": "/path-to-/" }
},
"extractor": {
"row": {}
},
"transformers": [
{
"csv": {
"separator": ","
}
},
{
"command" : {
"command" : "create edge InteractWith from (select from HumanProtein where STRINGproteinId= '${input.protein1}') to (select from HumanProtein where STRINGproteinId= '${input.protein2}') set Score=${input.combined_score}",
"output" : "edge"
}
}
],
"loader": {
"orientdb": {
"dbType": "graph",
"dbURL": "remote:localhost/XXXXX",
"dbUser": "admin",
"dbPassword": "admin",
"serverUser": "root",
"serverPassword": "*****",
"dbAutoCreate": false,
"wal": false,
"dbAutoCreateProperties": true,
"classes": [
{name: 'HumanProtein', extends:"V"},
{name: 'InteractWith', extends:"E"}
],
"tx": false
}
}
}

edit the link data to make sure one of the items in column 2 does not exist in the HumanProtein class you loaded in 1 by changing one of ENSPxxxx-code, i.e. in col 2.

Hope this helps.

robfrank · 2016-11-03T13:33:10Z

just to be sure and don't waste my time:

first run using classfile.txt as input
second run using linkfile.txt ad input BUT I need to modify the file

ghost · 2016-11-03T14:13:10Z

That's correct. The idea is to make sure the linking fails. Do you
understand the ETL concept?

nov. 2016 14.34 skrev "Roberto Franchini" notifications@github.com:

just to be sure and don't waste my time:

first run using classfile.txt as input

second run using linkfile.txt ad input BUT I need to modify the file

—
You are receiving this because you authored the thread.
Reply to this email directly, view it on GitHub
#6872 (comment),
or mute the thread
https://github.com/notifications/unsubscribe-auth/ASkWeGJb8Ist_f0dRNMSfaSzQQHTVuGDks5q6eKggaJpZM4KnF2r
.

ghost · 2016-11-04T08:37:18Z

The ETL processes fails the same way, even with other transformers. I believe ODB should consider
changing ETL to include a "dryrun".
'Dryrun' should just do basic checking while logging bad data-rows with line numbers.
Facing a situation with jobs loading numerous CSV files, receiving data glitches is inevitable. Cleaning up after a failed ETL is not an easy task.

{
"config": {
"log": "dryrun",

Thank you.

robfrank · 2016-11-04T16:12:19Z

Hi, I'll take care of this issues next week. Stay tuned

robfrank · 2016-11-09T05:25:50Z

A silly question, did you try with the flag haltOnError set to false?
http://orientdb.com/docs/last/Configuration-File.html#configuration-variables

ghost · 2016-11-09T10:40:46Z

This is not a silly question. I did not use it and don't know if it works, so I have to test it. The question is
whether it gives enough sensible info in the log to make any sense. Running ETL with debug leaves Gb of
useless repeated data when loading millions of rows.
So, in essence I maintain my request for a 'dry run'.

ghost · 2016-11-09T11:01:18Z

haltOnError set to false is unfortunately not any helpful option.
Neither INFO nor ERROR helped me locating the error in question. That only leaves DEBUG(which makes
to much noise).
By the way, why do you repeat the name of the database twice when things go wrong?

- removes system out in favor of OLogManager - adds specialized logging config file to etl scripts (bat/sh) - renames classes adding OETL prefix to all - refactors tests to use one in memory db for each test method refs #6872

refs #6872

robfrank · 2016-12-28T18:15:19Z

Logging in case of errors is improved with input data, executed command and exception message:

[445:command] ERROR exception=No edge has been created because no target vertices
	DB name="graph" - input={protein1:ENSP00000000233,protein2:ENSP00000270115,combined_score:204} - command=sql.create edge InteractWith from (select from HumanProtein where STRINGproteinId= 'ENSP00000000233') to (select from HumanProtein where STRINGproteinId='ENSP00000270115') set Score=204
Error in Pipeline execution: com.orientechnologies.orient.core.exception.OCommandExecutionException: No edge has been created because no target vertices
	DB name="graph"

WDYT?

I don't have any idea in how to implement a dry run. How to do a "dry run " interaction with the database? My suggestion is to use the plocal or even memory connection to test the ETL process.

refs orientechnologies/orientdb#6872

ghost · 2016-12-28T22:29:54Z

This makes perfect sense to me. The notion about 'dry run' was just a way to express an idea. Are this messages shown when log is ERROR or when any error is detected?

…

On 28 December 2016 at 19:15, Roberto Franchini ***@***.***> wrote: Logging in case of errors is improved with input data, executed command and exception message: [445:command] ERROR exception=No edge has been created because no target vertices DB name="graph" - input={protein1:ENSP00000000233,protein2:ENSP00000270115,combined_score:204} - command=sql.create edge InteractWith from (select from HumanProtein where STRINGproteinId= 'ENSP00000000233') to (select from HumanProtein where STRINGproteinId='ENSP00000270115') set Score=204 Error in Pipeline execution: com.orientechnologies.orient.core.exception.OCommandExecutionException: No edge has been created because no target vertices DB name="graph" WDYT? I don't have any idea in how to implement a dry run. How to do a "dry run " interaction with the database? My suggestion is to use the plocal or even memory connection to test the ETL process. — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub <#6872 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/ASkWeOGgkVcbKNMa48ql0Jl_LtI3_TRIks5rMqc8gaJpZM4KnF2r> .

-- Tore Austrått phone +47 90657231

robfrank · 2016-12-28T23:55:36Z

When the error is detected. This fix will be part of 2.2.15, but you can download a snapshot version of etl and use it.

robfrank self-assigned this Nov 2, 2016

robfrank added the bug label Nov 2, 2016

robfrank added the waiting reply label Nov 2, 2016

prjhub removed the waiting reply label Nov 2, 2016

robfrank added the waiting reply label Nov 3, 2016

prjhub removed the waiting reply label Nov 3, 2016

robfrank added the waiting reply label Nov 3, 2016

prjhub removed the waiting reply label Nov 3, 2016

ghost mentioned this issue Nov 4, 2016

Loading CSV data by ETL override Float values #6839

Closed

4 tasks

robfrank added the waiting reply label Nov 4, 2016

prjhub removed the waiting reply label Nov 9, 2016

robfrank added a commit that referenced this issue Dec 28, 2016

improves error messages

a2eaf66

refs #6872

robfrank added a commit that referenced this issue Dec 28, 2016

improves logging on errors

5cb5908

refs #6872

robfrank added the enhancement label Dec 28, 2016

robfrank added this to the 2.2.x (next hotfix) milestone Dec 28, 2016

robfrank added a commit to orientechnologies/orientdb-docs that referenced this issue Dec 28, 2016

add docs about logging configuration

ea73686

refs orientechnologies/orientdb#6872

robfrank closed this as completed Dec 29, 2016

robfrank modified the milestones: 2.2.x (next hotfix), 2.2.15 Jan 16, 2017

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

ETL giving up after one error #6872

ETL giving up after one error #6872

ghost commented Nov 2, 2016

robfrank commented Nov 2, 2016

ghost commented Nov 2, 2016

robfrank commented Nov 3, 2016

ghost commented Nov 3, 2016

robfrank commented Nov 3, 2016

ghost commented Nov 3, 2016

ghost commented Nov 4, 2016

robfrank commented Nov 4, 2016

robfrank commented Nov 9, 2016

ghost commented Nov 9, 2016

ghost commented Nov 9, 2016

robfrank commented Dec 28, 2016

ghost commented Dec 28, 2016 via email

robfrank commented Dec 28, 2016

ETL giving up after one error #6872

ETL giving up after one error #6872

Comments

ghost commented Nov 2, 2016

OrientDB Version, operating system, or hardware.

Operating System

Expected behavior and actual behavior

Here is what happens if one target vertice is not found:

Steps to reproduce the problem

robfrank commented Nov 2, 2016

ghost commented Nov 2, 2016

robfrank commented Nov 3, 2016

ghost commented Nov 3, 2016

robfrank commented Nov 3, 2016

ghost commented Nov 3, 2016

ghost commented Nov 4, 2016

robfrank commented Nov 4, 2016

robfrank commented Nov 9, 2016

ghost commented Nov 9, 2016

ghost commented Nov 9, 2016

robfrank commented Dec 28, 2016

ghost commented Dec 28, 2016 via email

robfrank commented Dec 28, 2016