Pentaho

 View Only
  • 1.  Create tables automatically with PDI blueprints Matt Casters

    Posted 04-25-2023 06:11
      |   view attached

    Hi, i´m trying to run the included example in blueprints to auto create table and i have a error. I can´t get it to work.
    The example of PDI blueprint: https://github.com/mattcasters/blueprints
    I have the setup ok but when i run "create-all-tables.ktr" get a error in the transformation "drop-create-table.ktr"  in the step javascript with the function dbMeta.quoteField(name): " Unexpected error : org.mozilla.javascript.EcmaError: TypeError: Cannot call method "quoteField" of null (trans_End#8)"

    I would be very grateful for any help.






    Log:
    2023/04/25 09:47:16 - Spoon - Using legacy execution engine
    2023/04/25 09:47:16 - create-all-tables - Transformation is pre-loaded.
    2023/04/25 09:47:16 - Spoon - Transformation opened.
    2023/04/25 09:47:16 - Spoon - Launching transformation [create-all-tables]...
    2023/04/25 09:47:16 - Spoon - Started the transformation execution.
    2023/04/25 09:47:16 - create-all-tables - Dispatching started for transformation [create-all-tables]
    2023/04/25 09:47:16 - create-all-tables - Nr of arguments detected:0
    2023/04/25 09:47:16 - create-all-tables - This is not a replay transformation
    2023/04/25 09:47:16 - create-all-tables - I found 2 different steps to launch.
    2023/04/25 09:47:16 - create-all-tables - Allocating rowsets...
    2023/04/25 09:47:16 - create-all-tables -  Allocating rowsets for step 0 --> list .json files
    2023/04/25 09:47:16 - create-all-tables -   prevcopies = 1, nextcopies=1
    2023/04/25 09:47:16 - create-all-tables - Transformation allocated new rowset [list .json files.0 - drop-create-table.0]
    2023/04/25 09:47:16 - create-all-tables -  Allocated 1 rowsets for step 0 --> list .json files
    2023/04/25 09:47:16 - create-all-tables -  Allocating rowsets for step 1 --> drop-create-table
    2023/04/25 09:47:16 - create-all-tables -  Allocated 1 rowsets for step 1 --> drop-create-table
    2023/04/25 09:47:16 - create-all-tables - Allocating Steps & StepData...
    2023/04/25 09:47:16 - create-all-tables -  Transformation is about to allocate step [list .json files] of type [GetFileNames]
    2023/04/25 09:47:16 - list .json files.0 - distribution activated
    2023/04/25 09:47:16 - list .json files.0 - Starting allocation of buffers & new threads...
    2023/04/25 09:47:16 - list .json files.0 - Step info: nrinput=0 nroutput=1
    2023/04/25 09:47:16 - list .json files.0 - output rel. is  1:1
    2023/04/25 09:47:16 - list .json files.0 - Found output rowset [list .json files.0 - drop-create-table.0]
    2023/04/25 09:47:16 - list .json files.0 - Finished dispatching
    2023/04/25 09:47:16 - create-all-tables -  Transformation has allocated a new step: [list .json files].0
    2023/04/25 09:47:16 - create-all-tables -  Transformation is about to allocate step [drop-create-table] of type [TransExecutor]
    2023/04/25 09:47:16 - drop-create-table.0 - distribution activated
    2023/04/25 09:47:16 - drop-create-table.0 - Starting allocation of buffers & new threads...
    2023/04/25 09:47:16 - drop-create-table.0 - Step info: nrinput=1 nroutput=0
    2023/04/25 09:47:16 - drop-create-table.0 - Got previous step from [drop-create-table] #0 --> list .json files
    2023/04/25 09:47:16 - drop-create-table.0 - input rel is 1:1
    2023/04/25 09:47:16 - drop-create-table.0 - Found input rowset [list .json files.0 - drop-create-table.0]
    2023/04/25 09:47:16 - drop-create-table.0 - Finished dispatching
    2023/04/25 09:47:16 - create-all-tables -  Transformation has allocated a new step: [drop-create-table].0
    2023/04/25 09:47:16 - create-all-tables - This transformation can be replayed with replay date: 2023/04/25 09:47:16
    2023/04/25 09:47:16 - create-all-tables - Initialising 2 steps...
    2023/04/25 09:47:16 - list .json files.0 - Released server socket on port 0
    2023/04/25 09:47:16 - drop-create-table.0 - Released server socket on port 0
    2023/04/25 09:47:16 - create-all-tables - Step [list .json files.0] initialized flawlessly.
    2023/04/25 09:47:16 - create-all-tables - Step [drop-create-table.0] initialized flawlessly.
    2023/04/25 09:47:16 - create-all-tables - Transformation has allocated 2 threads and 1 rowsets.
    2023/04/25 09:47:16 - list .json files.0 - Starting to run...
    2023/04/25 09:47:16 - drop-create-table.0 - Starting to run...
    2023/04/25 09:47:16 - Drop and Create target table - Transformation is pre-loaded.
    2023/04/25 09:47:16 - list .json files.0 - Finished processing (I=0, O=0, R=0, W=5, U=0, E=0)
    2023/04/25 09:47:16 - Drop and Create target table - Dispatching started for transformation [Drop and Create target table]
    2023/04/25 09:47:16 - Drop and Create target table - Nr of arguments detected:0
    2023/04/25 09:47:16 - Drop and Create target table - This is not a replay transformation
    2023/04/25 09:47:16 - Drop and Create target table - I found 4 different steps to launch.
    2023/04/25 09:47:16 - Drop and Create target table - Allocating rowsets...
    2023/04/25 09:47:16 - Drop and Create target table -  Allocating rowsets for step 0 --> JSON static info
    2023/04/25 09:47:16 - Drop and Create target table -   prevcopies = 1, nextcopies=1
    2023/04/25 09:47:16 - Drop and Create target table - Transformation allocated new rowset [JSON static info.0 - fields info.0]
    2023/04/25 09:47:16 - Drop and Create target table -  Allocated 1 rowsets for step 0 --> JSON static info
    2023/04/25 09:47:16 - Drop and Create target table -  Allocating rowsets for step 1 --> fields info
    2023/04/25 09:47:16 - Drop and Create target table -   prevcopies = 1, nextcopies=1
    2023/04/25 09:47:16 - Drop and Create target table - Transformation allocated new rowset [fields info.0 - pick fields.0]
    2023/04/25 09:47:16 - Drop and Create target table -  Allocated 2 rowsets for step 1 --> fields info
    2023/04/25 09:47:16 - Drop and Create target table -  Allocating rowsets for step 2 --> pick fields
    2023/04/25 09:47:16 - Drop and Create target table -   prevcopies = 1, nextcopies=1
    2023/04/25 09:47:16 - Drop and Create target table - Transformation allocated new rowset [pick fields.0 - drop/create.0]
    2023/04/25 09:47:16 - Drop and Create target table -  Allocated 3 rowsets for step 2 --> pick fields
    2023/04/25 09:47:16 - Drop and Create target table -  Allocating rowsets for step 3 --> drop/create
    2023/04/25 09:47:16 - Drop and Create target table -  Allocated 3 rowsets for step 3 --> drop/create
    2023/04/25 09:47:16 - Drop and Create target table - Allocating Steps & StepData...
    2023/04/25 09:47:16 - Drop and Create target table -  Transformation is about to allocate step [JSON static info] of type [JsonInput]
    2023/04/25 09:47:16 - JSON static info.0 - distribution de-activated
    2023/04/25 09:47:16 - JSON static info.0 - Starting allocation of buffers & new threads...
    2023/04/25 09:47:16 - JSON static info.0 - Step info: nrinput=0 nroutput=1
    2023/04/25 09:47:16 - JSON static info.0 - output rel. is  1:1
    2023/04/25 09:47:16 - JSON static info.0 - Found output rowset [JSON static info.0 - fields info.0]
    2023/04/25 09:47:16 - JSON static info.0 - Finished dispatching
    2023/04/25 09:47:16 - Drop and Create target table -  Transformation has allocated a new step: [JSON static info].0
    2023/04/25 09:47:16 - Drop and Create target table -  Transformation is about to allocate step [fields info] of type [JsonInput]
    2023/04/25 09:47:16 - fields info.0 - distribution activated
    2023/04/25 09:47:16 - fields info.0 - Starting allocation of buffers & new threads...
    2023/04/25 09:47:16 - fields info.0 - Step info: nrinput=1 nroutput=1
    2023/04/25 09:47:16 - fields info.0 - Got previous step from [fields info] #0 --> JSON static info
    2023/04/25 09:47:16 - fields info.0 - input rel is 1:1
    2023/04/25 09:47:16 - fields info.0 - Found input rowset [JSON static info.0 - fields info.0]
    2023/04/25 09:47:16 - fields info.0 - output rel. is  1:1
    2023/04/25 09:47:16 - fields info.0 - Found output rowset [fields info.0 - pick fields.0]
    2023/04/25 09:47:16 - fields info.0 - Finished dispatching
    2023/04/25 09:47:16 - Drop and Create target table -  Transformation has allocated a new step: [fields info].0
    2023/04/25 09:47:16 - Drop and Create target table -  Transformation is about to allocate step [pick fields] of type [SelectValues]
    2023/04/25 09:47:16 - pick fields.0 - distribution activated
    2023/04/25 09:47:16 - pick fields.0 - Starting allocation of buffers & new threads...
    2023/04/25 09:47:16 - pick fields.0 - Step info: nrinput=1 nroutput=1
    2023/04/25 09:47:16 - pick fields.0 - Got previous step from [pick fields] #0 --> fields info
    2023/04/25 09:47:16 - pick fields.0 - input rel is 1:1
    2023/04/25 09:47:16 - pick fields.0 - Found input rowset [fields info.0 - pick fields.0]
    2023/04/25 09:47:16 - pick fields.0 - output rel. is  1:1
    2023/04/25 09:47:16 - pick fields.0 - Found output rowset [pick fields.0 - drop/create.0]
    2023/04/25 09:47:16 - pick fields.0 - Finished dispatching
    2023/04/25 09:47:16 - Drop and Create target table -  Transformation has allocated a new step: [pick fields].0
    2023/04/25 09:47:16 - Drop and Create target table -  Transformation is about to allocate step [drop/create] of type [ScriptValueMod]
    2023/04/25 09:47:16 - drop/create.0 - distribution activated
    2023/04/25 09:47:16 - drop/create.0 - Starting allocation of buffers & new threads...
    2023/04/25 09:47:16 - drop/create.0 - Step info: nrinput=1 nroutput=0
    2023/04/25 09:47:16 - drop/create.0 - Got previous step from [drop/create] #0 --> pick fields
    2023/04/25 09:47:16 - drop/create.0 - input rel is 1:1
    2023/04/25 09:47:16 - drop/create.0 - Found input rowset [pick fields.0 - drop/create.0]
    2023/04/25 09:47:16 - drop/create.0 - Finished dispatching
    2023/04/25 09:47:16 - Drop and Create target table -  Transformation has allocated a new step: [drop/create].0
    2023/04/25 09:47:16 - Drop and Create target table - This transformation can be replayed with replay date: 2023/04/25 09:47:16
    2023/04/25 09:47:16 - Drop and Create target table - Initialising 4 steps...
    2023/04/25 09:47:16 - JSON static info.0 - Released server socket on port 0
    2023/04/25 09:47:16 - fields info.0 - Released server socket on port 0
    2023/04/25 09:47:16 - pick fields.0 - Released server socket on port 0
    2023/04/25 09:47:16 - drop/create.0 - Released server socket on port 0
    2023/04/25 09:47:16 - Drop and Create target table - Step [JSON static info.0] initialized flawlessly.
    2023/04/25 09:47:16 - Drop and Create target table - Step [fields info.0] initialized flawlessly.
    2023/04/25 09:47:16 - Drop and Create target table - Step [pick fields.0] initialized flawlessly.
    2023/04/25 09:47:16 - Drop and Create target table - Step [drop/create.0] initialized flawlessly.
    2023/04/25 09:47:16 - JSON static info.0 - Starting to run...
    2023/04/25 09:47:16 - fields info.0 - Starting to run...
    2023/04/25 09:47:16 - pick fields.0 - Starting to run...
    2023/04/25 09:47:16 - Drop and Create target table - Transformation has allocated 4 threads and 3 rowsets.
    2023/04/25 09:47:16 - drop/create.0 - Starting to run...
    2023/04/25 09:47:16 - JSON static info.0 - Opening file: file:///C:/Users/lcv64e/Downloads/blueprints-master/blueprints-master/auto_create_table/firstnames.json
    2023/04/25 09:47:16 - JSON static info.0 - We found [1] records
    2023/04/25 09:47:16 - JSON static info.0 - Finished processing files.
    2023/04/25 09:47:16 - JSON static info.0 - Finished processing (I=1, O=0, R=0, W=1, U=0, E=0)
    2023/04/25 09:47:16 - fields info.0 - We found [4] records
    2023/04/25 09:47:16 - drop/create.0 - Value #0 -  [targetdatabase] is used in the script.
    2023/04/25 09:47:16 - drop/create.0 - Value #1 -  [targetschema] is used in the script.
    2023/04/25 09:47:16 - drop/create.0 - Value #2 -  [targettable] is used in the script.
    2023/04/25 09:47:16 - drop/create.0 - Value #3 -  [fieldname] is used in the script.
    2023/04/25 09:47:16 - drop/create.0 - Value #4 -  [fieldtype] is used in the script.
    2023/04/25 09:47:16 - drop/create.0 - Value #5 -  [fieldlength] is used in the script.
    2023/04/25 09:47:16 - drop/create.0 - Value #6 -  [fieldprecision] is used in the script.
    2023/04/25 09:47:16 - drop/create.0 - This script is using 7 values from the input stream(s)
    2023/04/25 09:47:16 - drop/create.0 - Optimization level set to 9.
    2023/04/25 09:47:16 - drop/create.0 - No starting Script found!
    2023/04/25 09:47:16 - fields info.0 - Finished processing files.
    2023/04/25 09:47:16 - fields info.0 - Finished processing (I=4, O=0, R=1, W=4, U=0, E=0)
    2023/04/25 09:47:16 - drop/create.0 - No tran_Status found. Transformation status checking not available.
    2023/04/25 09:47:16 - pick fields.0 - Finished processing (I=0, O=0, R=4, W=4, U=0, E=0)
    2023/04/25 09:47:17 - drop/create.0 - ERROR (version 8.3.0.0-371, build 8.3.0.0-371 from 2019-06-11 11.09.08 by buildguy) : Unexpected error : org.mozilla.javascript.EcmaError: TypeError: Cannot call method "quoteField" of null (trans_End#8)
    2023/04/25 09:47:17 - drop/create.0 - ERROR (version 8.3.0.0-371, build 8.3.0.0-371 from 2019-06-11 11.09.08 by buildguy) : Error stack trace:
    2023/04/25 09:47:17 - drop/create.0 - org.mozilla.javascript.EcmaError: TypeError: Cannot call method "quoteField" of null (trans_End#8)
    2023/04/25 09:47:17 - drop/create.0 -     at org.mozilla.javascript.ScriptRuntime.constructError(ScriptRuntime.java:3785)
    2023/04/25 09:47:17 - drop/create.0 -     at org.mozilla.javascript.ScriptRuntime.constructError(ScriptRuntime.java:3763)
    2023/04/25 09:47:17 - drop/create.0 -     at org.mozilla.javascript.ScriptRuntime.typeError(ScriptRuntime.java:3791)
    2023/04/25 09:47:17 - drop/create.0 -     at org.mozilla.javascript.ScriptRuntime.typeError2(ScriptRuntime.java:3810)
    2023/04/25 09:47:17 - drop/create.0 -     at org.mozilla.javascript.ScriptRuntime.undefCallError(ScriptRuntime.java:3829)
    2023/04/25 09:47:17 - drop/create.0 -     at org.mozilla.javascript.ScriptRuntime.getPropFunctionAndThisHelper(ScriptRuntime.java:2334)
    2023/04/25 09:47:17 - drop/create.0 -     at org.mozilla.javascript.ScriptRuntime.getPropFunctionAndThis(ScriptRuntime.java:2327)
    2023/04/25 09:47:17 - drop/create.0 -     at org.mozilla.javascript.gen.trans_End_42._c_script_0(trans_End:8)
    2023/04/25 09:47:17 - drop/create.0 -     at org.mozilla.javascript.gen.trans_End_42.call(trans_End)
    2023/04/25 09:47:17 - drop/create.0 -     at org.mozilla.javascript.ContextFactory.doTopCall(ContextFactory.java:426)
    2023/04/25 09:47:17 - drop/create.0 -     at org.mozilla.javascript.ScriptRuntime.doTopCall(ScriptRuntime.java:3178)
    2023/04/25 09:47:17 - drop/create.0 -     at org.mozilla.javascript.gen.trans_End_42.call(trans_End)
    2023/04/25 09:47:17 - drop/create.0 -     at org.mozilla.javascript.gen.trans_End_42.exec(trans_End)
    2023/04/25 09:47:17 - drop/create.0 -     at org.pentaho.di.trans.steps.scriptvalues_mod.ScriptValuesMod.processRow(ScriptValuesMod.java:514)
    2023/04/25 09:47:17 - drop/create.0 -     at org.pentaho.di.trans.step.RunThread.run(RunThread.java:62)
    2023/04/25 09:47:17 - drop/create.0 -     at java.lang.Thread.run(Unknown Source)





    ------------------------------
    Luis Clemente
    Systems Engineer
    SMS - HVC
    ------------------------------

    Attachment(s)

    zip
    blueprints-master.zip   7.03 MB 1 version


  • 2.  RE: Create tables automatically with PDI blueprints Matt Casters

    Posted 04-27-2023 13:56

    Hi Luis,
    trans can not find database. So name of database in var targetDb, respectively in targetdatabase field, is wrong. Check if database demo exists. in trans.



    ------------------------------
    Petr Prochazka
    Systems Engineer
    P.V.A. systems s.r.o.
    ------------------------------



  • 3.  RE: Create tables automatically with PDI blueprints Matt Casters

    Posted 04-29-2023 04:27

    Hi, thank you very much for your reply.
    The database "demo" exist in the trans. Can you think of anything else I can try?










    ------------------------------
    Luis Clemente
    Systems Engineer
    SMS - HVC
    ------------------------------



  • 4.  RE: Create tables automatically with PDI blueprints Matt Casters

    Posted 05-02-2023 02:34

    I tried run from zip archive and works (respectively failed to connect db - I didn't create db).

    Check input rows and field targetdatabase. From first row is used db, schema and table. Or check if name or filed  have white chars.



    ------------------------------
    Petr Prochazka
    Systems Engineer
    P.V.A. systems s.r.o.
    ------------------------------



  • 5.  RE: Create tables automatically with PDI blueprints Matt Casters

    Posted 05-10-2023 08:46

    Hi, finally i fix the problem !!! Thank you very much for your answers and your help . The problem was that the transformation did not save the database connection because the database connection  it was not used . The solution has been easy , i have created a step in the transformation that uses the database connection. Then, when run the tranformation not crash y run correctly.
    Thank you very much for your interest .






    ------------------------------
    Luis Clemente
    Systems Engineer
    SMS - HVC
    ------------------------------



  • 6.  RE: Create tables automatically with PDI blueprints Matt Casters

    Posted 05-11-2023 06:05

    Hi Luis,
    good news. :-)
    Or disable this option in configuration:



    ------------------------------
    Petr Prochazka
    Systems Engineer
    P.V.A. systems s.r.o.
    ------------------------------



  • 7.  RE: Create tables automatically with PDI blueprints Matt Casters

    Posted 05-11-2023 06:12

    Oh great, thank you very much !!!



    ------------------------------
    Luis Clemente
    Systems Engineer
    SMS - HVC
    ------------------------------