CODEWITHSUNDEEP
csvorientdb
S Shravan
S Shravan

Reputation: 1

OrientDB Loading Vertex and Edges from CSV

I am trying to create two vertices based on two columns in CSV, SubSystem and UsedBy, and the column in between is the edge (relationship). But everytime i run my script, i only get the vertex under SubSytem and nothing under UsedBy or Dependency. Any help is appreciated, relatively new to OrientDB.

{
  "source": { "file": { "path": "CDE-ODS-PROD-VDB-VCDPCDE1-2016-07-18_LVL1.csv" } },
  "extractor": { "row": {} },
  "transformers": [
    { "csv": {"seperator":",",
              "columnsOnFirstLine":true} },
    { "vertex": { "class": "Object" } }
  ],
  "command":{
        "command":"create edge ${input.Dependency} from (select from V where Subsystem=${input.Subsystem} to (select from V where UsedBy = ${input.UsedBy})",
        "output":"edge"
        },
  "loader": {
    "orientdb": {
       "dbURL": "remote:localhost/test",
        "dbUser":"admin",
        "dbPassword":"admin",
       "dbType": "graph",
       "classes": [
         {"name": "Object", "extends": "V"},
         {"name": "UsedBy", "extends": "V"},
         {"name": "Dependency", "extends": "E"} ],
"dbAutoCreate":false,
 "indexes": [
         {"class":"Object", "fields":["Subsystem:string"],"type":"NOTUNIQUE_HASH_INDEX" },
                 {"class":"UsedBy","fields":["UsedBy:string"],"type":"NOTUNIQUE_HASH_INDEX" }

       ]
    }
  }
}

here's the sample CSV file and the version is 2.2.5

Subsystem,Dependency,Used By,Strength VCDPCDE1.SCHEMA.CDE_STG.PACKAGE.CORE_CONSUMER_DRIVER_PKG.PROCEDURE.PULL_FROM_QUEUE,Code.Method Call,VCDPCDE1.SCHEMA.CDE_STG.PACKAGE.CORE_CONSUMER_DRIVER_PKG.PROCEDURE.PROCESS_UNTIL_QUEUE_IS_EMPTY,1 VCDPCDE1.SCHEMA.CDE_STG.PACKAGE.FX_ORDER_AMOUNT_TSP_PKG.PROCEDURE.LOAD_RECON_ROW_TO_TARGET,Code.Method Call,VCDPCDE1.SCHEMA.CDE_STG.PACKAGE.FX_ORDER_AMOUNT_TSP_PKG.PROCEDURE.RECON_REPAIR_DIFFERENCES,1 VCDPCDE1.SCHEMA.CDE_STG.PACKAGE.FX_ORDER_AMOUNT_TSP_PKG.PROCEDURE.UPSERT_ROW,Code.Method Call,VCDPCDE1.SCHEMA.CDE_STG.PACKAGE.FX_ORDER_AMOUNT_TSP_PKG.PROCEDURE.LOAD_ROW_TO_TARGET,2 VCDPCDE1.SCHEMA.CDE_STG.PACKAGE.UTILITY_PKG.PROCEDURE.RECON_ALL,Code.Method Call,VCDPCDE1.SCHEMA.CDE_STG.PACKAGE.UTILITY_PKG.PROCEDURE.RECON_COMPARE,1 VCDPCDE1.SCHEMA.CDE_STG.PACKAGE.UTILITY_PKG.PROCEDURE.RECON_ALL,Code.Method Call,VCDPCDE1.SCHEMA.CDE_STG.PACKAGE.UTILITY_PKG.PROCEDURE.RECON_REPAIR,1 VCDPCDE1.SCHEMA.FOS_TOW.VIEW.FOS_PORTFOLIO_REF,Query Table,VCDPCDE1.SCHEMA.FOS_TOW.VIEW.FOS_ICR_MONTHLY_ACCRUALS_VW,1 VCDPCDE1.SCHEMA.FOS_TOW.VIEW.FOS_PORTFOLIO_REF,Query Table,VCDPCDE1.SCHEMA.FOS_TOW.VIEW.FOS_INVOICE_DETAILS_VW,1 VCDPCDE1.SCHEMA.FOS_TOW.VIEW.FOS_PORTFOLIO_REF,Query Table,VCDPCDE1.SCHEMA.FOS_TOW.VIEW.FOS_PORTFOLIO_EVENTS_VW,1

i think i got the json to work, but now when i am trying to create the edge i get this error

2016-07-29 11:06:05:617 WARNI Transformer [csv] returned null, skip rest of pipeline execution [OETLPipeline][1:csv] DEBUG Transformer input: VCDPCDE1.SCHEMA.CD_DATA.MATERIALIZED VIEW.ORG_PERSON_DTL_MVW,Query Table,VCDPCDE1.SCHEMA.CDE_APP.PACKAGE.RSCH_ORG_HIERARCHY_SSP_PKG.PROCEDURE.GET_ANALYST_DETAIL_BY_NAME,1 [1:csv] DEBUG parsing=VCDPCDE1.SCHEMA.CD_DATA.MATERIALIZED VIEW.ORG_PERSON_DTL_MVW,Query Table,VCDPCDE1.SCHEMA.CDE_APP.PACKAGE.RSCH_ORG_HIERARCHY_SSP_PKG.PROCEDURE.GET_ANALYST_DETAIL_BY_NAME,1 [1:csv] DEBUG document={Subsystem:VCDPCDE1.SCHEMA.CD_DATA.MATERIALIZED VIEW.ORG_PERSON_DTL_MVW,Dependency:Query Table,UsedBy:VCDPCDE1.SCHEMA.CDE_APP.PACKAGE.RSCH_ORG_HIERARCHY_SSP_PKG.PROCEDURE.GET_ANALYST_DETAIL_BY_NAME,Strength:1} [1:csv] DEBUG Transformer output: {Subsystem:VCDPCDE1.SCHEMA.CD_DATA.MATERIALIZED VIEW.ORG_PERSON_DTL_MVW,Dependency:Query Table,UsedBy:VCDPCDE1.SCHEMA.CDE_APP.PACKAGE.RSCH_ORG_HIERARCHY_SSP_PKG.PROCEDURE.GET_ANALYST_DETAIL_BY_NAME,Strength:1} [1:command] DEBUG Transformer input: {Subsystem:VCDPCDE1.SCHEMA.CD_DATA.MATERIALIZED VIEW.ORG_PERSON_DTL_MVW,Dependency:Query Table,UsedBy:VCDPCDE1.SCHEMA.CDE_APP.PACKAGE.RSCH_ORG_HIERARCHY_SSP_PKG.PROCEDURE.GET_ANALYST_DETAIL_BY_NAME,Strength:1} [1:command] DEBUG executing command=create edge Query Table from (select from Object where Subsystem=VCDPCDE1.SCHEMA.CD_DATA.MATERIALIZED VIEW.ORG_PERSON_DTL_MVW to (select from UsedBy where UsedBy = VCDPCDE1.SCHEMA.CDE_APP.PACKAGE.RSCH_ORG_HIERARCHY_SSP_PKG.PROCEDURE.GET_ANALYST_DETAIL_BY_NAME)... Error in Pipeline execution: com.orientechnologies.orient.core.sql.OCommandSQLParsingException: Error parsing query: create edge Query Table from (select from Object where Subsystem=VCDPCDE1.SCHEMA.CD_DATA.MATERIALIZED VIEW.ORG_PERSON_DTL_MVW to (select from UsedBy where UsedBy = VCDPCDE1.SCHEMA.CDE_APP.PACKAGE.RSCH_ORG_HIERARCHY_SSP_PKG.PROCEDURE.GET_ANALYST_DETAIL_BY_NAME) ^ Encountered " "create "" at line 1, column 1. Was expecting one of: <SELECT> ... <TRAVERSE> ... <MATCH> ... <INSERT> ... <RETURN> ... <PROFILE> ... <FIND> ... <REBUILD> ... <OPTIMIZE> ... <GRANT> ... <REVOKE> ... <BEGIN> ... <COMMIT> ... <IF> ... <SLEEP> ... <CONSOLE> ... DB name="test" DB name="test"

Upvotes: 0

Views: 277

Answers (1)

Oleksandr Gubchenko
Oleksandr Gubchenko

Reputation: 1369

It isn't possible to create edge connection (Dependency) while your are loading vertexes (Object and UsedBy) in the same ETL process. Because the vertexes creation is still in progress, so it can't find vertexes to connect with an edge. You should separate your UsedBy data in a different csv file and run it with a second json config file containing edge creation (Object vertex in this case was created with the first ETL run).

Note: you also have Used By in your csv instead of UsedBy.

Upvotes: 1

Related Questions