Is there an equivalent @@RowCount (MSSQL) in Vertica, and ways to store their results into Objects (

Roman_1Roman_1 Registered User
As i understand next two functions,GET_NUM_REJECTED_ROWS(), GET_NUM_ACCEPTED_ROWS(), work only with COPY command, but i cant added their results into any table like "insert into (table) select GET_NUM_REJECTED_ROWS();" is any way to do that. And what about if i want to store the results after INSERT INTO command (number of loaded counts), is any function to solve this. Could anyone help me with that, Appreciate for any help Thanks

Comments

  • You can get the information of the copy command in the table load_streams, there is a column for the rejected and accepted rows. 
    Hope this helps, 
    Eugenia
  • Roman_1Roman_1 Registered User
    Thanks Eugenia, 

    Couple notes, when i try to load with COPY not all  succesfull attempts have result string in load_streams, is it OK? or i maybe have some troubles with hardware?

    and the second, is there any object like load_streams, which describe similar results to INSERT command

    Thanks
  • Yes, you are right, only the one that take more than 1-2 seconds will be there. If you want to get all of them add the PROFILE word before the COPY statement. 

    About the insert, there is nothing, however, the insert will fail or rollback, you won't have row rejected and accepted. Not sure if I understand your question? 

    Eugenia
  • Roman_1Roman_1 Registered User
    PROFILE for COPY that is fantastic, thanks, it solve my question completely.

    About INSERT, i mean that if i do simple insert, for example 
    insert into ... select ... and i have 50 rows which added into table, and i'll be able to see that 50 rows as result for that transaction in table like load_streams for COPY.
  • if you profile the insert you could do this : 
    dbadmin=> profile insert into test select * from test;

    NOTICE 4788:  Statement is being profiled

    HINT:  Select * from v_monitor.execution_engine_profiles where transaction_id=45035996273715232 and statement_id=8;

    NOTICE 3557:  Initiator memory for query: [on pool general: 109568 KB, minimum: 109568 KB]

    NOTICE 5077:  Total memory required by query: [109568 KB]

     OUTPUT 

    --------

        466

    (1 row)


    Time: First fetch (1 row): 3288.437 ms. All rows formatted: 3288.497 ms

    dbadmin=> Select sum(counter_value)/2 from v_monitor.execution_engine_profiles where counter_name = 'written rows' and transaction_id=current_trans_id() and statement_id = current_statement()-1;

            ?column?        

    ------------------------

     466.000000000000000000

    (1 row)


    Time: First fetch (1 row): 663.605 ms. All rows formatted: 663.638 ms

    dbadmin=> 


    Hope it helps, 


    Eugenia


  • Roman_1Roman_1 Registered User
    Yes, that's exactly what i need. 
    Lots of Thanks !!!!!
  • My pleasure ! glad that I helped. 
    Have a nice halloween! 
    Eugenia

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file