[MERGE]Stop working in odb214

[robfrank]

From @simper66 on October 19, 2015 8:38

Hi,

I think Merge has stopped working in odb214.

The lookup goes fine but the merge is not being done.

[0:merge] DEBUG Transformer input: {geoNameISO:ES,geoNameISO3:ESP,..........}
[0:merge] DEBUG joinValue=2510, lookupResult=Country#20:0{geoNameId:2510,......} v1
[0:merge] DEBUG Transformer output: null

The output in odb21 works as expected:

[0:merge] DEBUG Transformer input: {geoNameISO:ES,....}
[0:merge] DEBUG joinValue=2510, lookupResult=Country#20:0{geoNameId:2510,...} v1
[0:merge] DEBUG merged record Country#20:0{geoNameId:2510, geoNameISO:ES,...}
[0:merge] DEBUG Transformer output: Country#20:0{geoNameId:2510, geoNameISO:ES,..} v1

Enviroment:

OrientDB: OrientDB 2.1.4 GA Community Edition (October 13, 2015)
SO: Windows7
JDK: Jdk 1.8.0_45
OrientDB Configuration: No special configuration. The one out of the box.
Mode: Standalone. server.bat & oetl.bat

Copied from original issue: orientechnologies/orientdb-etl#101

[robfrank]

From @simper66 on October 19, 2015 8:57

By the way, the MERGE block:

{"merge": { "log": "DEBUG", "joinFieldName":"geoNameId", "lookup":"LocationUIDS_IDX_TMP", "unresolvedLinkAction":"SKIP"}},

[robfrank]

Could you provide a full json configuration and a small set of data. If you can provide a query that confirm it isn't working, it would be the best.

[robfrank]

From @simper66 on October 19, 2015 11:0

Hi Rob,

I simplified as much of possible and able to execute with same issue.

DATABASE SCRIPT:

CREATE DATABASE remote:192.168.16.72:2424/xLocation root qaz plocal graph;
ALTER DATABASE DATEFORMAT yyyy-MM-dd;
ALTER DATABASE DATETIMEFORMAT yyyy-MM-dd'T'HH:mm:ss:SSSz;
ALTER DATABASE TIMEZONE UTC;
ALTER DATABASE CHARSET UTF-8;
ALTER DATABASE custom useLightweightEdges=true;

CREATE CLASS Location EXTENDS V;
CREATE PROPERTY Location.geoNameId integer;
ALTER CLASS Location STRICTMODE=true;
CREATE INDEX LocationUIDS_IDX ON Location(geoNameId) UNIQUE_HASH_INDEX;
CREATE INDEX LocationUIDS_IDX_TMP ON Location(geoNameId) UNIQUE_HASH_INDEX;

CREATE CLASS Country EXTENDS Location;
CREATE PROPERTY Country.geoNameISO string;
CREATE PROPERTY Country.geoNameISO3 string;
CREATE PROPERTY Country.geoNameISONumeric short;
CREATE PROPERTY Country.geoNameFips string;
CREATE PROPERTY Country.geoNameCountry string;
CREATE PROPERTY Country.geoNameCapital string;
CREATE PROPERTY Country.geoNameArea long;
CREATE PROPERTY Country.geoNameContinent string;
CREATE PROPERTY Country.geoNameTld string;
CREATE PROPERTY Country.geoNameCurrencyCode string;
CREATE PROPERTY Country.geoNameCurrencyName string;
CREATE PROPERTY Country.geoNamePhone string;
CREATE PROPERTY Country.geoNamePostalCodeFormat string;
CREATE PROPERTY Country.geoNamePostalCodeRegex string;
CREATE PROPERTY Country.geoNameLanguages string;
CREATE PROPERTY Country.geoNameNeighbours string;
CREATE PROPERTY Country.geoNameEquivalentFipsCode string;
ALTER CLASS Country STRICTMODE=true;
CREATE INDEX CountryISOS_IDX ON Country(geoNameISO) UNIQUE_HASH_INDEX;
CREATE INDEX CountryUIDS_IDX_TMP ON Country(geoNameId) UNIQUE_HASH_INDEX;

CREATE VERTEX Location SET geoNameId = 2510769

ETL SCRIPT:

{

"source" : {
    "file": { "path": "C:\Users\jorge\Desktop\odb214\databases\GEONAMES\data.txt", "lock" : false }
},

"extractor": { 
    "row": {} 
},

"transformers": [

    { 
        "csv": { 
            "separator": "\t", 
            "columnsOnFirstLine": false,
            "stringCharacter": "",
            "columns": [
                "geoNameISO:string",
                "geoNameISO3:string",
                "geoNameISONumeric:short",
                "geoNameFips:string",
                "geoNameCountry:string",
                "geoNameCapital:string",
                "geoNameArea:long",
                "geoNamePopulation:integer",
                "geoNameContinent:string",
                "geoNameTld:string",
                "geoNameCurrencyCode:string",
                "geoNameCurrencyName:string",
                "geoNamePhone:string",
                "geoNamePostalCodeFormat:string",
                "geoNamePostalCodeRegex:string",
                "geoNameLanguages:string",
                "geoNameId:integer",
                "geoNameNeighbours:string",
                "geoNameEquivalentFipsCode:string"
            ]
        }
    },

    {
        "command" : { "log": "DEBUG",
            "command": "select if (eval('count(*)>0'), 1, 0) as exists from index:CountryISOS_IDX where key = '${input.geoNameISO}'",
            "output": "idx"
        }
    },

    {"flow": { "log": "DEBUG", "if": "$idx.exists[0]=1", "operation" : "skip" }},

    {"field": { "log": "DEBUG", "fieldName": "geoNamePopulation", "operation": "remove"}},
    {"merge": { "log": "DEBUG", "joinFieldName":"geoNameId", "lookup":"LocationUIDS_IDX_TMP", "unresolvedLinkAction":"SKIP"}},

    {"vertex": { "log": "DEBUG", "class": "Country", "skipDuplicates": false}}

],

"loader": {

    "orientdb": {
        "dbURL": "remote:192.168.16.72:2424/xLocation",
        "dbUser": "admin",
        "dbPassword": "admin",
        "dbAutoCreate": false,
        "dbType": "graph",
        "wal": false,
        "tx": false,
        "txUseLog": false,
        "useLightweightEdges" : true
    }
}

}

DATA:

ES ESP 724 SP Spain Madrid 504782 46505963 EU .es EUR Euro 34 ##### ^(\d{5})$ es-ES,ca,gl,eu,oc 2510769 AD,PT,GI,FR,MA

WARN: All is a single line and there is one last TAB in the data line.

MERGE OUTPUT:

[0:merge] DEBUG Transformer input: {geoNameISO:ES,geoNameISO3:ESP,geoNameISONumeric:724,geoNameFips:SP,geoNameCountry:Spain,geoNameCapital:Madrid,geoNameArea:504782,geoNamePopulation:null,geoNameContinent:EU,geoNameTld:.es,geoNameCurrencyCode:EUR,geoNameCurrencyName:Euro,geoNamePhone:34,geoNamePostalCodeFormat:#####,geoNamePostalCodeRegex:^(\d{5})$,geoNameLanguages:es-ES,ca,gl,eu,oc,geoNameId:2510769,geoNameNeighbours:AD,PT,GI,FR,MA,geoNameEquivalentFipsCode:null}
[0:merge] DEBUG joinValue=2510769, lookupResult=Location#11:0{geoNameId:2510769} v1
[0:merge] DEBUG Transformer output: null

Don´t know if it is important but note that the class being loaded is different from the lookup target class. The former inherits the latter.

In ODB21 the 2 classes are merged and the existing Location vertex is extended and becoming a Country vertex with all the data loaded.

[robfrank]

From @simper66 on October 19, 2015 11:8

orientdb {db=xLocation}> select from location
0 |#11:0|Location|2510769

orientdb {db=xLocation}> select from country
0 item(s) found. Query executed in 0.003 sec(s).

[robfrank]

Thank you very much. I will write a test with this material.

[robfrank]

From @simper66 on October 19, 2015 14:34

I have looked better and i create the "base" vertex with the same class that the "merge" vertex.

So, in the DB SCRIPT we can change the last line:

CREATE VERTEX Location SET geoNameId = 2510769;
with:
CREATE VERTEX Country SET geoNameId = 2510769;

It fails anyway...

[robfrank]

I've fixed the problem with the merge Transformer. It will be on next 2.1.7 hot fixe and 2.2.