Hitachi Vantara Pentaho Community Forums
Results 1 to 3 of 3

Thread: Sequencing one-time SQL statements within an ETL job

  1. #1
    Join Date
    Mar 2007
    Posts
    16

    Default Sequencing one-time SQL statements within an ETL job

    Hi all,

    I'm attempting to insert a few one-time SQL scripts within a batch ETL process and would appreciate any advice on what is the best practice for doing so.

    Specifically I'd like to do an extraction-transformation, drop my fact table indices, load the fact table, then rebuild the indices.

    Is there a way to sequence this in Spoon without doing the extract-transform, writing to a temp table (or file), waiting for a Blocking Step, running the one-time "drop index" SQL script, waiting for another Blocking Step, extracting from the temp table and loading the fact table, waiting for another Blocking Step, then ending with another one-time "create index" SQL script?

    My impression is that doing this without the temp table -- just inserting a Blocking Step before each SQL script -- will not do the trick b/c it prevents the entire result set from being passed through to later steps.

    I also tried isolating the extract-transform and loading to 2 separate transformations, then sequencing these in a job along with SQL scripts, but couldn't get this to work either.

    Is the "Wait for" job entry any use here?

    Thanks,
    Dave

  2. #2

    Default

    Hi Dave,
    If you want to sequence process, you have to use Job.
    For example create One Job A that conrain :

    1- xtraction-transformation (call a trans)

    2- drop my fact table indice (use SQL job entry )

    3- load the fact table (probably a trans)

    4- then rebuild the indices

    The "Wait For" job entry let you create a delay time between 2 job entry.

    Samatar

  3. #3
    Join Date
    Mar 2007
    Posts
    16

    Default

    Thanks, Samatar.

    I tried exactly that but perhaps I made an error.. I'll try it again

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.