Hitachi Vantara Pentaho Community Forums
Results 1 to 16 of 16

Thread: How to do row counts for multiple tables in a transformation?

  1. #1
    Join Date
    Jun 2010
    Posts
    114

    Question How to do row counts for multiple tables in a transformation?

    Q)If I have a table A which has 5 table names (lets say Table 1 ... Table 5) for which I want row counts for each of them, how can we do that?

    I have tried using Database Lookup and Execute SQL script steps but, did not work for me.

    Thanks,
    Om
    Thanks,
    Om
    ----------------------
    Spoon version -4.4.0

  2. #2
    Join Date
    Nov 1999
    Posts
    9,729

    Default

    First construct the table name in something like Java Script:

    Code:
    var sqlText = "SELECT count(*) FROM " + TableName;
    Then use the "Dynamic SQL row" to get the numbers. Don't forget to put in a template query so that the step knows what to expect.

  3. #3
    Join Date
    Sep 2011
    Posts
    190

    Default

    Alternatively, maybe you can read this from the Information Schema in your database? (I don't know DB2 nor PostgreSQL, so I don't know how to do that). In MySQL it could be something like:
    Code:
    SELECT    `TABLE_NAME` as TabelNaam
    ,         `TABLE_ROWS` as Aantal
    FROM     `information_schema`.`TABLES` 
    where     `TABLE_TYPE` = 'BASE TABLE'
    and     `TABLE_SCHEMA` = 'myschema'
    and       `TABLE_ROWS` > 0
    ;

  4. #4
    Join Date
    Jun 2010
    Posts
    114

    Default

    Thanks Matt It worked for me
    @mbvt: Thanks for the reply. I will try the equivalent query in postgres and DB2.
    Name:  row_counts_trans.jpg
Views: 246
Size:  29.7 KB
    Last edited by omm; 01-10-2012 at 11:07 AM. Reason: added image
    Thanks,
    Om
    ----------------------
    Spoon version -4.4.0

  5. #5
    Join Date
    Nov 1999
    Posts
    9,729

    Default

    For extra bonus points you can apply quoting to the table name, just in case there are spaces or special characters involved ;-)

  6. #6
    Join Date
    Jun 2010
    Posts
    114

    Default

    @mbvt: It worked in DB2 and here is the equivalent query
    select TABNAME,CARD from syscat.tables where TABSCHEMA = 'myschema' and card>0

    For postgres:
    SELECT schemaname,relname,n_live_tup
    FROM pg_stat_user_tables
    ORDER BY relname ASC;
    Last edited by omm; 01-10-2012 at 12:31 PM. Reason: Added more relavent info
    Thanks,
    Om
    ----------------------
    Spoon version -4.4.0

  7. #7
    Join Date
    Jun 2010
    Posts
    114

    Default

    Quote Originally Posted by MattCasters View Post
    For extra bonus points you can apply quoting to the table name, just in case there are spaces or special characters involved ;-)


    Sure. I'll take care of that
    Thanks,
    Om
    ----------------------
    Spoon version -4.4.0

  8. #8
    Join Date
    Nov 1999
    Posts
    9,729

    Default

    For MySQL it's simply "show tables"

  9. #9
    Join Date
    Jun 2007
    Posts
    260

    Default

    Just take note that I got different results using the two methods :

    I accept it might be my version of mysql, but it's a warning just the same.

    Code:
     SELECT    ......   `TABLE_ROWS` > 0;
    +-------------+---------+
    | TabelNaam   | Aantal  |
    +-------------+---------+
    | card_status |      15 |
    | cdf         | 2274769 |
    | cdr         | 2170427 |
    +-------------+---------+
    Code:
    select count(*) from cdf;
    +----------+
    | count(*) |
    +----------+
    |  2274540 |
    +----------+
    1 row in set (0.54 sec)
    
    mysql> select count(*) from cdr;
    +----------+
    | count(*) |
    +----------+
    |  2225063 |
    +----------+
    1 row in set (0.59 sec)

  10. #10
    Join Date
    Jun 2009
    Posts
    28

    Default Can you clarify this statement?

    Quote Originally Posted by MattCasters View Post
    First construct the table name in something like Java Script:

    Code:
    var sqlText = "SELECT count(*) FROM " + TableName;
    Then use the "Dynamic SQL row" to get the numbers. Don't forget to put in a template query so that the step knows what to expect.
    i am not sure what is meant here. what would the template query be based on the javascript?

    Thanks in advance, Aashish

  11. #11
    Join Date
    Apr 2008
    Posts
    4,696

    Default

    This is a really old thread. Why not start a new thread explaining what you want to accomplish?

  12. #12
    Join Date
    Jun 2009
    Posts
    28

    Default

    With all due respect, i am attempting to do exactly what is described in this thread.

    i have a table with all of the tables (SYSTABLES) in a database. i want to create a transformation that does row counts for each of the tables in the SYSTABLES table.

    i understand everything up to the point of the template query. I am not sure what that is supposed to do. I have tried to put in something like 'Select 1 as RECORD_COUNT;' but that does not work.
    i keep getting the following error message.
    Couldn't get field info from [SELECT COUNT(*) FROM ac_mstr;]
    Last edited by rathodsa; 07-16-2015 at 07:28 PM.

  13. #13
    Join Date
    Apr 2008
    Posts
    4,696

    Default

    If you set your template SQL as:
    SELECT COUNT(*) from SYSTABLES

    And then you have a column in your incoming datastream
    SELECT COUNT(*) from ac_mstr
    SELECT COUNT(*) from ac_mstr2
    SELECT COUNT(*) from ac_mstr3

    As long as table ac_mstr exists, the Template SQL will be executed immediately to determine what the output of the SQL statement should look like.
    Then during execution, each of the statements from the datastream will be run.

    The fact that you are getting an error (Couldn't get field info from) indicates that there is some sort of mismatch.

  14. #14
    Join Date
    Jun 2009
    Posts
    28

    Default

    i still cannot get this to work. i'm doing what you suggested but it's the same error.

  15. #15
    Join Date
    Apr 2008
    Posts
    4,696

    Default

    All I can suggest at this point is for you to post your transform.
    We can do more detailed diagnosis with the transform in hand.

  16. #16
    Join Date
    Jun 2009
    Posts
    28

    Default

    i can't seem to post attachments in this forum. i'm getting some kind of system error. i have alerted the site admin.

Tags for this Thread

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.