Command: cluster sql
The cluster sql command provides an interface to interact with the managed
service SQL utilities in a safe and gated way that maintains the stability of
the cluster. For example, the Vertica vsql utility.
Since not all DBA commands are available in managed service, this is a convenient way to run common (non-distructive) sql commands, especially for SQL commands that may need elevated levels of access. It is also a quick way for someone to check status without needing to run vsql or another sql client.
The way the command works is that it provides a set of sql commands that can be initiated by the command line utility. You pick the command from a list and it handles running the sql on the specified cluster and returns the results.
Usage
edw cluster sql -describe
edw cluster sql \
-cmd <id_of_command_to_run> \
-cluster-id <cluster_id> \
-environment-id <cluster_environment_id>
edw cluster sql \
-status-id <id_of_running_comand> \
-cluster-id <cluster_id> \
-environment-id <cluster_environment_id>
Required Flags
-cmd- (Required if initiating a new command) The ID of one of the available admintools commands to run. This will run for a period of time (see-timeout) or til completion if sooner. If it times out the command will provide a status-id that can be used to look up the status of the running command. Cannot be used with-status-id-status-id- (Required if checking on running command) If the command is long running, this id is returned, and the command can be run again to use this id to check on the status of the remote running command. Cannot be used with-cmd-cluster-id- The cluster ID where commands will be executed.-environment-id- The environment ID that this cluster belongs to.
Note: If the environment has not been configured or set via environment variables, global flags are also required.
Optional Flags
-describe- Lists the available vsql commands to run. This list will change over time; for the current list of available commands see below.
Available Remote SQL Commands
- Get all node status
- Provides the status of recovery operations
- Get current state of resource pools on each node
- Get all database columns
- Get current K-Safety value
- Get information about the node subscriptions in the database
- Get the session subscriptions in the database
- Indicates how up to date the catalog is on communal storage
- Get all users in the database
- Get data usage broken by nodes
- Get data usage by top 10 tables
- Get license status
Output
If -describe is used, it will provide a list of commands to pick from:
(0) for: Get all node status
(1) for: Provides the status of recovery operations
(2) for: Get current state of resource pools on each node
...
If -cmd is used, it will provide the ongoing Status til a time out is reached,
or the command completes, in which case it will provide the remote commands
output. Calling -status-id on an already running command will also provide a
similar output.
Status: InProgress
Status: InProgress
Status: Success
Output:
DB | Host | State
--------+------+-------
cli_db | ALL | UP
Example: Describe the available commands
$ edw cluster sql -describe
(0) for: Get all node status
(1) for: Provides the status of recovery operations
(2) for: Get current state of resource pools on each node
(3) for: Get all database columns
(4) for: Get current K-Safety value
(5) for: Get information about the node subscriptions in the database
(6) for: Get the session subscriptions in the database
(7) for: Indicates how up to date the catalog is on communal storage
(8) for: Get all users in the database
(9) for: Get data usage broken by nodes
(10) for: Get data usage by top 10 tables
(11) for: Get license status
Example: 0 - Get all node status
$ edw cluster sql \
-cmd 0 \
-cluster-id <cluster_id> \
-environment-id <cluster_environment_id>
Status: InProgress
Status: InProgress
Status: Success
Output:
node_name | node_id | node_state | is_primary | node_address | node_address_family | export_address | export_address_family | catalog_path | node_type | is_ephemeral | standing_in_for | subcluster_name | last_msg_from_node_at | node_down_since
-------------------+-------------------+------------+------------+--------------+---------------------+----------------+-----------------------+---------------------------------------------------+-----------+--------------+-----------------+-----------------+-------------------------------+-----------------
v_cli_db_node0001 | 45035996273704978 | UP | t | 10.0.101.37 | ipv4 | 10.0.101.37 | ipv4 | /catalog/cli_db/v_cli_db_node0001_catalog/Catalog | PERMANENT | f | | | 2020-03-16 18:15:26.412799+00 |
v_cli_db_node0002 | 45035996273838832 | UP | t | 10.0.101.166 | ipv4 | 10.0.101.166 | ipv4 | /catalog/cli_db/v_cli_db_node0002_catalog/Catalog | PERMANENT | f | | | 2020-03-16 18:15:26.413812+00 |
(2 rows)
Example: 1 - Get status of recovery operations
$ edw cluster sql \
-cmd 1 \
-cluster-id <cluster_id> \
-environment-id <cluster_environment_id>
Status: InProgress
Status: InProgress
Status: InProgress
Status: Success
Output:
node_name | recover_epoch | recovery_phase | splits_completed | splits_total | historical_completed | historical_total | current_completed | current_total | is_running
-------------------+---------------+----------------+------------------+--------------+----------------------+------------------+-------------------+---------------+------------
v_cli_db_node0001 | | | 0 | 0 | 0 | 0 | 0 | 0 | f
v_cli_db_node0002 | 32 | | 0 | 0 | 0 | 0 | 0 | 0 | f
(2 rows)
Example: 2 - Get current state of resource pools on each node
$ edw cluster sql \
-cmd 2 \
-cluster-id <cluster_id> \
-environment-id <cluster_environment_id>
Status: InProgress
Status: InProgress
Status: InProgress
Status: Success
Output:
node_name | pool_oid | pool_name | is_internal | memory_size_kb | memory_size_actual_kb | memory_inuse_kb | general_memory_borrowed_kb | queueing_threshold_kb | max_memory_size_kb | max_query_memory_size_kb | running_query_count | planned_concurrency | max_concurrency | is_standalone | queue_timeout | queue_timeout_in_seconds | execution_parallelism | priority | runtime_priority | runtime_priority_threshold | single_initiator | query_budget_kb | cpu_affinity_set | cpu_affinity_mask | cpu_affinity_mode
-------------------+-------------------+-----------+-------------+----------------+-----------------------+-----------------+----------------------------+-----------------------+--------------------+--------------------------+---------------------+---------------------+-----------------+---------------+---------------+--------------------------+-----------------------+----------+------------------+----------------------------+------------------+-----------------+------------------+-------------------+-------------------
v_cli_db_node0001 | 45035996273704996 | general | t | 6255012 | 6255012 | 0 | 0 | 5942261 | 6255012 | | 0 | 4 | | t | 00:05 | 300 | AUTO | 0 | MEDIUM | 2 | false | 1485565 | | 3 | ANY
v_cli_db_node0001 | 45035996273704998 | sysquery | t | 349112 | 349112 | 0 | 0 | 6301483 | 6633140 | | 0 | 4 | | f | 00:05 | 300 | AUTO | 110 | HIGH | 0 | false | 87278 | | 3 | ANY
v_cli_db_node0001 | 45035996273705000 | sysdata | t | 0 | 0 | 0 | 0 | 0 | 0 | | 0 | 1 | 0 | t | 0 | 0 | AUTO | 0 | HIGH | 0 | false | | | 3 | ANY
v_cli_db_node0001 | 45035996273705002 | wosdata | t | 0 | 0 | 0 | 0 | 1658284 | 1745563 | | 0 | 2 | 0 | f | 0 | 0 | AUTO | 0 | HIGH | 0 | false | | | 3 | ANY
v_cli_db_node0001 | 45035996273705004 | tm | t | 349112 | 349112 | 0 | 0 | 6301483 | 6633140 | | 0 | 6 | 7 | f | 00:05 | 300 | AUTO | 105 | MEDIUM | 60 | true | 58185 | | 3 | ANY
v_cli_db_node0001 | 45035996273705006 | refresh | t | 0 | 0 | 0 | 0 | 5969826 | 6284028 | | 0 | 4 | | f | 00:05 | 300 | AUTO | -10 | MEDIUM | 60 | true | 1485565 | | 3 | ANY
v_cli_db_node0001 | 45035996273705008 | recovery | t | 0 | 0 | 0 | 0 | 5969826 | 6284028 | | 0 | 4 | 2 | f | 00:05 | 300 | AUTO | 107 | MEDIUM | 60 | true | 1485565 | | 3 | ANY
v_cli_db_node0001 | 45035996273705010 | dbd | t | 0 | 0 | 0 | 0 | 5969826 | 6284028 | | 0 | 4 | | f | 0 | 0 | AUTO | 0 | MEDIUM | 0 | true | 1485565 | | 3 | ANY
v_cli_db_node0001 | 45035996273705096 | jvm | t | 0 | 0 | 0 | 0 | 663313 | 698225 | | 0 | 4 | | f | 00:05 | 300 | AUTO | 0 | MEDIUM | 2 | false | 165828 | | 3 | ANY
v_cli_db_node0001 | 45035996273705098 | blobdata | t | 0 | 0 | 0 | 0 | 663313 | 698225 | | 0 | 2 | 0 | f | 0 | 0 | AUTO | 0 | HIGH | 0 | false | | | 3 | ANY
v_cli_db_node0001 | 45035996273705100 | metadata | t | 29016 | 29016 | 0 | 0 | 5969826 | 6284028 | | 0 | 1 | 0 | f | 0 | 0 | AUTO | 108 | HIGH | 0 | false | | | 3 | ANY
v_cli_db_node0002 | 45035996273704996 | general | t | 6252066 | 6252066 | 0 | 0 | 5939462 | 6252066 | | 0 | 4 | | t | 00:05 | 300 | AUTO | 0 | MEDIUM | 2 | false | 1484865 | | 3 | ANY
v_cli_db_node0002 | 45035996273704998 | sysquery | t | 349112 | 349112 | 0 | 0 | 6301483 | 6633140 | | 0 | 4 | | f | 00:05 | 300 | AUTO | 110 | HIGH | 0 | false | 87278 | | 3 | ANY
v_cli_db_node0002 | 45035996273705000 | sysdata | t | 0 | 0 | 0 | 0 | 0 | 0 | | 0 | 1 | 0 | t | 0 | 0 | AUTO | 0 | HIGH | 0 | false | | | 3 | ANY
v_cli_db_node0002 | 45035996273705002 | wosdata | t | 0 | 0 | 0 | 0 | 1658284 | 1745563 | | 0 | 2 | 0 | f | 0 | 0 | AUTO | 0 | HIGH | 0 | false | | | 3 | ANY
v_cli_db_node0002 | 45035996273705004 | tm | t | 349112 | 349112 | 0 | 0 | 6301483 | 6633140 | | 0 | 6 | 7 | f | 00:05 | 300 | AUTO | 105 | MEDIUM | 60 | true | 58185 | | 3 | ANY
v_cli_db_node0002 | 45035996273705006 | refresh | t | 0 | 0 | 0 | 0 | 5969826 | 6284028 | | 0 | 4 | | f | 00:05 | 300 | AUTO | -10 | MEDIUM | 60 | true | 1484865 | | 3 | ANY
v_cli_db_node0002 | 45035996273705008 | recovery | t | 0 | 0 | 0 | 0 | 5969826 | 6284028 | | 0 | 4 | 2 | f | 00:05 | 300 | AUTO | 107 | MEDIUM | 60 | true | 1484865 | | 3 | ANY
v_cli_db_node0002 | 45035996273705010 | dbd | t | 0 | 0 | 0 | 0 | 5969826 | 6284028 | | 0 | 4 | | f | 0 | 0 | AUTO | 0 | MEDIUM | 0 | true | 1484865 | | 3 | ANY
v_cli_db_node0002 | 45035996273705096 | jvm | t | 0 | 0 | 0 | 0 | 663313 | 698225 | | 0 | 4 | | f | 00:05 | 300 | AUTO | 0 | MEDIUM | 2 | false | 165828 | | 3 | ANY
v_cli_db_node0002 | 45035996273705098 | blobdata | t | 0 | 0 | 0 | 0 | 663313 | 698225 | | 0 | 2 | 0 | f | 0 | 0 | AUTO | 0 | HIGH | 0 | false | | | 3 | ANY
v_cli_db_node0002 | 45035996273705100 | metadata | t | 31962 | 31962 | 0 | 0 | 5969826 | 6284028 | | 0 | 1 | 0 | f | 0 | 0 | AUTO | 108 | HIGH | 0 | false | | | 3 | ANY
(22 rows)
Example: 3 - Get all database columns
$ edw cluster sql \
-cmd 3 \
-cluster-id <cluster_id> \
-environment-id <cluster_environment_id>
Status: InProgress
Status: Success
Output:
table_id | table_schema | table_name | is_system_table | column_id | column_name | data_type | data_type_id | data_type_length | character_maximum_length | numeric_precision | numeric_scale | datetime_precision | interval_precision | ordinal_position | is_nullable | column_default | column_set_using | is_identity
----------+--------------+------------+-----------------+-----------+-------------+-----------+--------------+------------------+--------------------------+-------------------+---------------+--------------------+--------------------+------------------+-------------+----------------+------------------+-------------
(0 rows)