Hitachi Vantara Pentaho Community Forums
Results 1 to 5 of 5

Thread: how to create a table automatically and dynamically in kettle

  1. #1
    Join Date
    Dec 2014
    Posts
    1

    Default how to create a table automatically and dynamically in kettle

    Before the step of table_output or bulk_loader in transformation, how to create a table automatically if the target table does not exist?

    There is a table named T in A database, I want to load data to B database and keep a copy everyday, like keeping a copy named T_20141204 today and T_20141205 tomorrow.
    How to use parameter to create tables dynamically named like T_20141204, T_20141205 and so on?

    Any help would be appreciated.

  2. #2
    Join Date
    Jun 2012
    Posts
    5,534

    Default

    Use a job and start with a "JavaScript" job entry:

    Code:
    date = new java.util.Date();
    dateFormat = new java.text.SimpleDateFormat("yyyyMMdd");
    tableName = "T_" + dateFormat.format(date);
    parent_job.setVariable("TABLE_NAME", tableName);
    true;
    Now you have your table name and can use a "Table Exists" job entry to conditionally create that table before your bulk loading transformation will be started.
    So long, and thanks for all the fish.

  3. #3

    Default need full discription

    please tell me in detail about this with steps if possible

    Quote Originally Posted by marabu View Post
    Use a job and start with a "JavaScript" job entry:

    Code:
    date = new java.util.Date();
    dateFormat = new java.text.SimpleDateFormat("yyyyMMdd");
    tableName = "T_" + dateFormat.format(date);
    parent_job.setVariable("TABLE_NAME", tableName);
    true;
    Now you have your table name and can use a "Table Exists" job entry to conditionally create that table before your bulk loading transformation will be started.

  4. #4
    Join Date
    Oct 2010
    Posts
    369

    Default sample transformation

    sample transformation
    Attached Files Attached Files

  5. #5

    Default

    Quote Originally Posted by lalgourav View Post
    sample transformation
    You could also do that from within a transformation. You can use a Get System Info to get the current date, then a Modified Javascript to build the "T_yyyyMMdd" table name followed by a Table Exists (also available in a transformation), a Filter Rows step to identify when a table does not exist and finally a Execute SQL Script step to do the actual Create Table; after that you can do whatever processing to fill in that table.


    HTH,


    --
    Julio.

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.