Search code examples
orientdb

ORIENT ETL many to many

ETL for M:M

We have following tables in RDBMS:

  1. ExternalMessageConfig (id,name,...);
  2. ExternalMessageDataConfig (id,dataname,...);
  3. ExternalMessageConfigMapping (messageConfigId,dataConfigId) (M:M) (Link table).

We created 2 vertex: ExternalMessageConfig and ExternalMessageDataConfig.

We created: out_ExternalMessageDataConfig TYPE LINKLIST in ExternalMessageConfig to populate ExternalMessageDataConfig.

Questions:

  • How to write ETL to populate out_ExternalMessageDataConfig?

Or

  • How to write create CREATE LINK query ?

ORIENT DB Version : 2.0.13

Solution

  • The goal is: once you have imported tables from RDBMS to OrientDB (through the ETL tool), the following script allow you to create links between the various entities with many-to-many relationship (currently not possible with ETL).

    The default scenario involves the following steps / conditions:

    1) run ETL importing 3 tables: Table 1 (left_table), Table 2 (right_table), Table 3 (mm-table that contains the id for the relation m: n)

    2) in the orient database there will be three classes that contain data

    3) run the code which will create edges that connect left_table -> table_right, cancellation of the mm-table now useless class, cancellation of the fields 'id' of the left_table and right_table because in OrientDB ids are not needed.

    JAVASCRIPT

    Function with parameters: "leftTable" , "rightTable" , "mmTable" , "nameIdLeft" , "nameIdRight" , "nameEdge"

var g=orient.getGraphNoTx();
g.command("sql","CREATE CLASS " + nameEdge + " EXTENDS E");
var MM_table = g.command("sql","select from " + mmTable);
for(i=0;i<MM_table.length;i++){
var recordMM=g.command("sql","select " + nameIdLeft + " , " + nameIdRight + " from "+ MM_table[i].getId());
var idLeft=recordMM[0].getProperty(nameIdLeft);
var idRight=recordMM[0].getProperty(nameIdRight);
var left=g.command("sql","select from " + leftTable + " where " + nameIdLeft + " = " + idLeft);
var right=g.command("sql","select from " + rightTable + " where " + nameIdRight + " = " + idRight);
g.command("sql","create edge Left_Right from " + left[0].getId() + " to " + right[0].getId());
}
g.command("sql","drop class " + mmTable + " unsafe");
g.command("sql","UPDATE " + leftTable + " REMOVE " + nameIdLeft);
g.command("sql","UPDATE " + rightTable + " REMOVE " + nameIdRight);

    call from studio:

    select myFunction("Animal","Person","MMtable","idAnimal","idPerson","Left_Right")

    JAVA

    public class ManyToMany {

    static final String REMOTE      = "remote:localhost/";
    static final String NOMEDB      = "MoltiAmolti";
    static final String CURRENTPATH = REMOTE + NOMEDB;

    public static void main(String[] args) throws IOException {

        OServerAdmin serverAdmin = new OServerAdmin(CURRENTPATH).connect("root", "root");

        OrientGraphNoTx g = new OrientGraphFactory(CURRENTPATH).getNoTx();

        createEdgeManyToMany(g, "Animal", "Person", "mmTable", "idAnimal", "idPerson", "Left_Right");
    }


    public static void createEdgeManyToMany (OrientGraphNoTx  g, String leftTable, String rightTable, String mmTable, 
                         String nameIdLeft, String nameIdRight, String nameEdge){

        System.out.println("Start processing...");

        //query on mmTable to save 2 id
        String getMMvertex = "select from "+mmTable;
        Iterable<Vertex> mm_vertex = g.command(new OSQLSynchQuery<Vertex>(getMMvertex)).execute();
        List<OrientVertex> listaVertex = new ArrayList<OrientVertex>();
        CollectionUtils.addAll(listaVertex, mm_vertex.iterator());

        //for each record
        String idLeft = ""; 
        String idRight = "";
        Iterable<Vertex> vertexLeft;
        Iterable<Vertex> vertexRight;
        String queryGetLeft = "";
        String queryGetRight = "";
        String queryEdge = "";
        String tmpIdleft = "";
        String tmpIdRight = "";

        //create class edge
        OClass edge = g.createEdgeType(nameEdge, "E");

        for (int i = 0; i<listaVertex.size(); i++) {
            System.out.println("Record Many-to-Many n°"+(i+1));
            idLeft = listaVertex.get(i).getProperty(nameIdLeft).toString();
            idRight = listaVertex.get(i).getProperty(nameIdRight).toString();

            queryGetLeft = "SELECT FROM " + leftTable + " WHERE " + nameIdLeft + " = " + idLeft;
            vertexLeft = g.command(new OCommandSQL(queryGetLeft)).execute();

            queryGetRight = "SELECT FROM " + rightTable + " WHERE " + nameIdRight + " = " + idRight;
            vertexRight = g.command(new OCommandSQL(queryGetRight)).execute();

            //create edge from vertex-left to vertex-right
            tmpIdleft = vertexRight.iterator().next().getId().toString();
            tmpIdRight = vertexLeft.iterator().next().getId().toString();
            queryEdge = "CREATE EDGE "+nameEdge+" FROM "+tmpIdleft+" TO "+tmpIdRight;
            g.command(new OCommandSQL(queryEdge)).execute();

            System.out.println("   - Created edge: "+queryEdge);
        }

        //remove id from origin vertex now useless
        g.command(new OCommandSQL("DROP PROPERTY "+leftTable+"."+nameIdLeft+" FORCE")).execute();
        g.command(new OCommandSQL("UPDATE "+leftTable+" REMOVE "+nameIdLeft+"")).execute();
        g.command(new OCommandSQL("DROP PROPERTY "+rightTable+"."+nameIdRight+" FORCE")).execute();
        g.command(new OCommandSQL("UPDATE "+rightTable+" REMOVE "+nameIdRight+"")).execute();
        System.out.println("Update old vertex eliminating id fields");
        g.command(new OCommandSQL("DROP class "+mmTable+" unsafe")).execute();
        System.out.println("Remove class many-to-many");

        System.out.println("End!"); 
        g.shutdown();
    }
}

    enter image description here

    EDIT

    'WHITOUT EDGE BUT WITH LINKLIST'

    Ok, so the result you wuold like to have it should be this: enter image description here

    The code is not changed a lot, the different is that is added to Linklist properties in the LeftTable and created the link pointing to rightTable.

    JAVA CHANGES

    public static void createEdgeManyToMany (OrientGraphNoTx  g, String leftTable, String rightTable, String mmTable, 
                                  String nameIdLeft, String nameIdRight, String link) {

    System.out.println("Start processing...");

    //query on mmTable to save 2 id
    String getMMvertex = "select from "+mmTable;
    Iterable<Vertex> mm_vertex = g.command(new OSQLSynchQuery<Vertex>(getMMvertex)).execute();
    List<OrientVertex> listaVertex = new ArrayList<OrientVertex>();
    CollectionUtils.addAll(listaVertex, mm_vertex.iterator());

    //for each record
    String idLeft = ""; 
    String idRight = "";
    Iterable<Vertex> vertexLeft;
    Iterable<Vertex> vertexRight;
    String queryGetLeft = "";
    String queryGetRight = "";
    String queryEdge = "";
    String tmpIdleft = "";
    String tmpIdRight = "";
    String queryLink = "";
    boolean created = false;

    for (int i = 0; i<listaVertex.size(); i++) {
        System.out.println("Record Many-to-Many n°"+(i+1));
        idLeft = listaVertex.get(i).getProperty(nameIdLeft).toString();
        idRight = listaVertex.get(i).getProperty(nameIdRight).toString();

        queryGetLeft = "SELECT FROM " + leftTable + " WHERE " + nameIdLeft + " = " + idLeft;
        vertexLeft = g.command(new OCommandSQL(queryGetLeft)).execute();

        queryGetRight = "SELECT FROM " + rightTable + " WHERE " + nameIdRight + " = " + idRight;
        vertexRight = g.command(new OCommandSQL(queryGetRight)).execute();

        //create edge from vertex-left to vertex-right
        tmpIdleft = vertexLeft.iterator().next().getId().toString();
        tmpIdRight = vertexRight.iterator().next().getId().toString();

        //add in the schema class the property link
        if (!created) {
            OClass leftTableClass = g.getRawGraph().getMetadata().getSchema().getClass(leftTable);
            OClass rightTableclass = g.getRawGraph().getMetadata().getSchema().getClass(rightTable);
            leftTableClass.createProperty(link, OType.LINKLIST, rightTableclass);
            created = true;
        }

        //without edge but with link
        queryLink = "update "+tmpIdleft+" ADD "+link+" = "+tmpIdRight;
        g.command(new OCommandSQL(queryLink)).execute();

        System.out.println("   - Created link: "+queryLink);
    }
    //remove id from origin vertex now useless
    g.command(new OCommandSQL("DROP PROPERTY "+leftTable+"."+nameIdLeft+" FORCE")).execute();
    g.command(new OCommandSQL("UPDATE "+leftTable+" REMOVE "+nameIdLeft+"")).execute();
    g.command(new OCommandSQL("DROP PROPERTY "+rightTable+"."+nameIdRight+" FORCE")).execute();
    g.command(new OCommandSQL("UPDATE "+rightTable+" REMOVE "+nameIdRight+"")).execute();
    System.out.println("Update old vertex eliminating id fields");
    g.command(new OCommandSQL("DROP class "+mmTable+" unsafe")).execute();
    System.out.println("Remove class many-to-many");

    System.out.println("End!"); 
    g.shutdown();
}

    JAVASCRIPT CHANGES

    parameters: leftTable,rightTable,mmTable,nameIdLeft,nameIdRight,nameLink

    var g=orient.getGraphNoTx();
g.command("sql","CREATE PROPERTY " + leftTable + "." + nameLink + " linklist " + rightTable);
var MM_table = g.command("sql","select from " + mmTable);
for(i=0;i<MM_table.length;i++){
    var recordMM=g.command("sql","select " + nameIdLeft + " , " + nameIdRight + " from "+ MM_table[i].getId());
    var idLeft=recordMM[i].getProperty(nameIdLeft);
    var idRight=recordMM[i].getProperty(nameIdRight);
    var left=g.command("sql","select from " + leftTable + " where " + nameIdLeft + " = " + idLeft);
    var right=g.command("sql","select from " + rightTable + " where " + nameIdRight + " = " + idRight);
    g.command("sql","update " + left[0].getId() + " add " + nameLink + " = [ " + right[0].getId()+ " ]");
}
g.command("sql","drop class " + mmTable + " unsafe");
g.command("sql","UPDATE " + leftTable + " REMOVE " + nameIdLeft);
g.command("sql","UPDATE " + rightTable + " REMOVE " + nameIdRight);