Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

How to set a connection label for vsql... and see it in data collector tables

Hi,
Apparently vsql is lacking command line option to set connection label.
Meta-function set_connection_label is not affecting data collector tables, according to docs.

Question - how to set connection label from vsql, in a way it will be visible in data collector tables?
Thank you

Tagged:

Answers

  • Bryan_HBryan_H Administrator

    It looks like this works as expected to update DC tables, despite the doc note. Let me know if you find any issues:
    $ vsql -w Vertica1! -c "select set_client_label('this_is_vsql');select user_name,client_hostname,client_type,client_label from sessions;"
    user_name | client_hostname | client_type | client_label
    -----------+-----------------+-------------+--------------
    bryan | [::1]:32918 | vsql | this_is_vsql
    (1 row)

    d2=> select * from dc_requests_issued where client_label = 'this_is_vsql';
    time | node_name | session_id | user_id | user_name | transaction_id | statement_id | request_id | request_type | label | client_label | search_path | query_start_epoch | request | is_retry | digest
    -------------------------------+---------------+-------------------------------+-------------------+-----------+-------------------+--------------+------------+--------------+-------+--------------+-----------------------------------------------------------+-------------------+--------------------------------------------------------------------------+----------+----------------------
    2022-09-05 19:55:11.060942-04 | v_d2_node0001 | v_d2_node0001-410726:0x460d2c | 45035996526453358 | bryan | 45035996281654276 | 1 | 3 | QUERY | | this_is_vsql | "$user", public, v_catalog, v_monitor, v_internal, v_func | 19154563 | select user_name,client_hostname,client_type,client_label from sessions; | f | -3511289645001229613

  • That would be nice... unfortunately label is changed only in sessions, which runs out of internal memory structures.
    user_sessions that runs from data collector tables do not see it.
    vsql is missing command line parameter -l or --label, to properly set connection label

  • Bryan_HBryan_H Administrator

    Could you open a support case and direct to me? user_sessions joins from dc_session_starts so we would need to log it there. We'll log a new feature request from your ticket.

  • moshegmosheg Administrator

    You can mimic the session label by setting a vsql variable Name to your preferred label string.
    For example:

    vsql -U user01 -w 12345 -v S_LABEL='My_label_001' -ef - <<-EOF
    \set S_LABEL '''':S_LABEL''''
    CREATE TABLE my_label_reference AS SELECT SESSION_ID, current_trans_id(), current_statement(), :S_LABEL AS client_label
    FROM CURRENT_SESSION;
    EOF
    
    vsql -ef - <<-EOF01
    SELECT * FROM my_label_reference;
    SELECT left(request,80) My_label_001_session_statments FROM QUERY_REQUESTS
    WHERE SESSION_ID = (SELECT SESSION_ID FROM my_label_reference where client_label='My_label_001');
    EOF01
    
    The above run time output:
    CREATE TABLE my_label_reference AS SELECT SESSION_ID, current_trans_id(), current_statement(), 'My_label_001' AS client_label
    FROM CURRENT_SESSION;
    CREATE TABLE
    SELECT * FROM my_label_reference;
               SESSION_ID           | current_trans_id  | current_statement | client_label
    --------------------------------+-------------------+-------------------+--------------
     v_eevdb_node0001-1757:0x2b058f | 45035996273891825 |                 1 | My_label_001
    (1 row)
    
    SELECT left(request,80) My_label_001_session_statments FROM QUERY_REQUESTS
    WHERE SESSION_ID = (SELECT SESSION_ID FROM my_label_reference where client_label='My_label_001');
                              My_label_001_session_statments
    ----------------------------------------------------------------------------------
     CREATE TABLE my_label_reference AS SELECT SESSION_ID, current_trans_id(), curren
     INSERT INTO public.my_label_reference SELECT CURRENT_SESSION.session_id AS SESSI
    (2 rows)
    
  • edited September 7

    Thanks for your time putting together an example!
    May be I was not very clear what I need.
    I have an audit process to save all Vertica sessions. Every hour I am getting new sessions from user_sessions and copying them somewhere.
    User session runs from dc_session_start and dc_session_end (sorry if dc table name is misspelled).
    Everything in user_sessions has a label set properly, ODBC, JDBC, vertica-python. Even vertica-sql-go client set label on connection that is visible in user_sessions.
    Except vsql. Exactly as it said in docs, set_client_label is not reflected in dc_session_start, and not visible in user_sessions.

    Yes, set_client_label set it in sessions, which is going away as soon as session ends.

    My guess, there are many connection options for vsql. May be, it is time to do connection by connect string - instead of bunch of parameters vsql should just take URL with all parameters inside. ODBC and JDBC do connection string, vertica-python do not.

    I can file new feature in support, if needed.

Leave a Comment

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

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.