SHOW
Use
SHOW commands to get information about databases, tables, and user sessions.Shows the CREATE SERVER statement that could have been used to create the server.
SHOW CREATE SERVER <servername>
SHOW CREATE SERVER default_local_delimited;
create_server_sql
CREATE SERVER default_local_delimited FOREIGN DATA WRAPPER DELIMITED_FILE
WITH (STORAGE_TYPE='LOCAL_FILE');
Shows the CREATE TABLE statement that could have been used to create the table.
SHOW CREATE TABLE <tablename>
SHOW CREATE TABLE heavyai_states;
CREATE TABLE heavyai_states (
id TEXT ENCODING DICT(32),
abbr TEXT ENCODING DICT(32),
name TEXT ENCODING DICT(32),
omnisci_geo GEOMETRY(MULTIPOLYGON, 4326
) NOT NULL);
Retrieve the databases accessible for the current user, showing the database name and owner.
SHOW DATABASES
Database Owner
omnisci admin
2004_zipcodes admin
game_results jane
signals jason
...
Show registered compile-time UDFs and extension functions in the system and their arguments.
SHOW FUNCTIONS [DETAILS]
SHOW FUNCTIONS
Scalar UDF
distance_point_line
ST_DWithin_Polygon_Polygon
ST_Distance_Point_ClosedLineString
Truncate
ct_device_selection_udf_any
area_triangle
_h3RotatePent60cw
ST_Intersects_Polygon_Point
ST_DWithin_LineString_Polygon
ST_Intersects_Point_Polygon
box_contains_box
Displays a list of all row-level security (RLS) policies that exist for a user or role; admin rights are required. If EFFECTIVE is used, the list also includes any policies that exist for all roles that apply to the requested user or role.
SHOW [EFFECTIVE] POLICIES <name>;
Returns a list of queued queries in the system; information includes session ID, status, query string, account login name, client address, database name, and device type (CPU or GPU).
show queries;
query_session_id|current_status|submitted |query_str |login_name|client_address |db_name |exec_device_type
834-8VAA |Pending |2020-05-06 08:21:15|select d_date_sk, count(1) from date_dim group by d_date_sk;|admin |tcp:localhost:48596|tpcds_sf10|CPU
826-CLKk |Running |2020-05-06 08:20:57|select count(1) from store_sales, store_returns; |admin |tcp:localhost:48592|tpcds_sf10|CPU
828-V6s7 |Pending |2020-05-06 08:21:13|select count(1) from store_sales; |admin |tcp:localhost:48594|tpcds_sf10|GPU
946-rtJ7 |Pending |2020-05-06 08:20:58|select count(1) from item; |admin |tcp:localhost:48610|tpcds_sf10|GPU
Admin users can see and interrupt all queries, and non-admin users can see and interrupt only their own queries
NOTE: SHOW QUERIES is only available if the runtime query interrupt parameter (
enable-runtime-query-interrupt) is set.If included with a name, lists the role granted directly to a user or role. SHOW EFFECTIVE ROLES with a name lists the roles directly granted to a user or role, and also lists the roles indirectly inherited through the directly granted roles.
SHOW [EFFECTIVE] ROLES <name>
If the user name or role name is omitted, then a regular user sees their own roles, and a superuser sees a list of all roles existing in the system.
Show user-defined runtime functions and table functions.
SHOW RUNTIME [TABLE] FUNCTIONS
SHOW RUNTIME [TABLE] FUNCTION DETAILS
Show data connectors.
show supported data sources
Displays storage-related information for a table, such as the table ID/name, number of data/metadata files used by the table, total size of data/metadata files, and table epoch values.
You can see table details for all tables that you have access to in the current database, or for only those tables you specify.
SHOW TABLE DETAILS [<table-name>, <table-name>, ...]
Show details for all tables you have access to:
omnisql> show table details;
table_id|table_name |column_count|is_sharded_table|shard_count|max_rows |fragment_size|max_rollback_epochs|min_epoch|max_epoch|min_epoch_floor|max_epoch_floor|metadata_file_count|total_metadata_file_size|total_metadata_page_count|total_free_metadata_page_count|data_file_count|total_data_file_size|total_data_page_count|total_free_data_page_count
1 |heavyai_states |11 |false |0 |4611686018427387904|32000000 |-1 |1 |1 |0 |0 |1 |16777216 |4096 |4082 |1 |536870912 |256 |242
2 |heavyai_counties |13 |false |0 |4611686018427387904|32000000 |-1 |1 |1 |0 |0 |1 |16777216 |4096 |NULL |1 |536870912 |256 |NULL
3 |heavyai_countries|71 |false |0 |4611686018427387904|32000000 |-1 |1 |1 |0 |0 |1 |16777216 |4096 |4022 |1 |536870912 |256 |182
Show details for table
omnisci_states:omnisql> show table details heavyai_states;
table_id|table_name |column_count|is_sharded_table|shard_count|max_rows |fragment_size|max_rollback_epochs|min_epoch|max_epoch|min_epoch_floor|max_epoch_floor|metadata_file_count|total_metadata_file_size|total_metadata_page_count|total_free_metadata_page_count|data_file_count|total_data_file_size|total_data_page_count|total_free_data_page_count
1 |heavyai_states|11 |false |0 |4611686018427387904|32000000 |-1 |1 |1 |0 |0 |1 |16777216 |4096 |4082 |1 |536870912 |256 |242
The number of columns returned includes system columns. As a result, the number of columns in
column_count can be up to two greater than the number of columns created by the user.Displays the list of available system (built-in) table functions.
SHOW TABLE FUNCTIONS;
tf_compute_dwell_times
tf_feature_self_similarity
tf_feature_similarity
tf_rf_prop
tf_rf_prop_max_signal
tf_geo_rasterize_slope
tf_geo_rasterize
generate_random_strings
generate_series
tf_mandelbrot_cuda_float
tf_mandelbrot_cuda
tf_mandelbrot_float
tf_mandelbrot
Show detailed output information for the specified table function. Output details vary depending on the table function specified.
SHOW TABLE FUNCTIONS DETAILS <function_name>
View SHOW output for the
generate_series table function:
Output Header | Output Details |
|---|---|
name | generate_series |
signature | (i64 series_start, i64 series_stop, i64 series_step)
(i64 series_start, i64 series_stop) -> Column |
input_names | series_start, series_stop, series_step
series_start, series_stop |
input_types | i64 |
output_names | generate_series |
output_types | Column i64 |
CPU | true |
GPU | true |
runtime | false |
filter_table_transpose | false |
Retrieve the servers accessible for the current user.
SHOW SERVERS;
server_name|data_wrapper|created_at|options
default_local_delimited|DELIMITED_FILE|2022-03-15 10:06:05|{"STORAGE_TYPE":"LOCAL_FILE"}
default_local_parquet|PARQUET_FILE|2022-03-15 10:06:05|{"STORAGE_TYPE":"LOCAL_FILE"}
default_local_regex_parsed|REGEX_PARSED_FILE|2022-03-15 10:06:05|{"STORAGE_TYPE":"LOCAL_FILE"}
...
Retrieve the tables accessible for the current user.
SHOW TABLES;
table_name
----------
omnisci_states
omnisci_counties
omnisci_countries
streets_nyc
streets_miami
...
Lists name, ID, and default database for all or specified users for the current database. If the command is issued by a superuser, login permission status is also shown. Only superusers see users who do not have permission to log in.
SHOW USER DETAILS
NAME ID DEFAULT_DB
mike.nuumann 191 mondale
Dale 184 churchill
Editor_Test 141 mondale
Jerry.wong 181 alluvial
AA_superuser 139
BB_superuser 2140
PlinyTheElder 183 windsor
aaron.tyre 241 db1
achristie 243 sid
eve.mandela 202 nancy
...
SHOW [ALL] USER DETAILS lists name, ID, superuser status, default database, and login permission status for all users across the HeavyDB instance. This variant of the command is available only to superusers. Regular users who run the SHOW ALL USER DETAILS command receive an error message.
Show all user details for all users:
heavysql> show all user details;
NAME|ID|IS_SUPER|DEFAULT_DB|CAN_LOGIN
admin|0|true|(-1)|true
ua|2|false|db1(2)|true
ub|3|false|db1(2)|true
uc|4|false|db1(2)|false
ud|5|false|db2(3)|true
ue|6|false|db2(3)|true
uf|7|false|db2(3)|false
Show all user details for specified users ue, ud, ua, and uf:
heavysql> \db db2
User admin switched to database db2
heavysql> show all user details ue, ud, uf, ua;
NAME|ID|IS_SUPER|DEFAULT_DB|CAN_LOGIN
ua|2|false|db1(2)|true
ud|5|false|db2(3)|true
ue|6|false|db2(3)|true
uf|7|false|db2(3)|false
If a specified user is not found, the superuser sees an error message:
heavysql> show user details ue, ud, uf, ua;
User "ua" not found.
Show user details for specified users ue, ud, and uf:
heavysql> show user details ue, ud, uf;
NAME|ID|DEFAULT_DB|CAN_LOGIN
ud|5|db2(3)|true
ue|6|db2(3)|true
uf|7|db2(3)|false
Show user details for all users:
heavysql> show user details;
NAME|ID|DEFAULT_DB|CAN_LOGIN
ud|5|db2(3)|true
ue|6|db2(3)|true
uf|7|db2(3)|false
Running SHOW ALL USER DETAILS results in an error message:
heavysql> \db
User ua is using database db1
heavysql> show all user details;
SHOW ALL USER DETAILS is only available to superusers. (Try SHOW USER DETAILS instead?)
Show user details for all users:
heavysql> show user details;
NAME|ID|DEFAULT_DB
ua|2|db1
ub|3|db1
If a specified user is not found, the user sees an error message:
heavysql> show user details ua, ub, uc;
User "uc" not found.
Show user details for user ua:
heavysql> show user details ua;
NAME|ID|DEFAULT_DB
ua|2|db1
Retrieve all persisted user sessions, showing the session ID, user login name, client address, and database name. Admin or superuser privileges required.
SHOW USER SESSIONS;
session_id login_name client_address db_name
453-X6ds mike http:198.51.100.1 game_results
453-0t2r erin http:198.51.100.11 game_results
421-B64s shauna http:198.51.100.43 game_results
213-06dw ahmed http:198.51.100.12 signals
333-R28d cat http:198.51.100.233 signals
497-Xyz6 inez http:198.51.100.5 ships
...
Interrupt a queued query. Specify the query by using its session ID.
show queries;
query_session_id|current_status |executor_id|submitted |query_str |login_name|client_address |db_name|exec_device_type
713-t1ax |PENDING_QUEUE |0 |2021-08-03 ...|SELECT ... |John |http:::1 |omnisci|GPU
491-xpfb |PENDING_QUEUE |0 |2021-08-03 ...|SELECT ... |Patrick |http:::1 |omnisci|GPU
451-gp2c |PENDING_QUEUE |0 |2021-08-03 ...|SELECT ... |John |http:::1 |omnisci|GPU
190-5pax |PENDING_EXECUTOR |1 |2021-08-03 ...|SELECT ... |Cavin |http:::1 |omnisci|GPU
720-nQtV |RUNNING_QUERY_KERNEL|2 |2021-08-03 ...|SELECT ... |Cavin |tcp:::ffff:127.0.0.1:50142|omnisci|GPU
947-ooNP |RUNNING_IMPORTER |0 |2021-08-03 ...|IMPORT_GEO_TABLE|Rio |tcp:::ffff:127.0.0.1:47314|omnisci|CPU
To interrupt the last query in the list (ID
946-ooNP):kill query '946-ooNP'
Showing the queries again indicates that
946-ooNP has been deleted:show queries;
query_session_id|current_status |executor_id|submitted |query_str |login_name|client_address |db_name|exec_device_type
713-t1ax |PENDING_QUEUE |0 |2021-08-03 ...|SELECT ... |John |http:::1 |omnisci|GPU
491-xpfb |PENDING_QUEUE |0 |2021-08-03 ...|SELECT ... |Patrick |http:::1 |omnisci|GPU
451-gp2c |PENDING_QUEUE |0 |2021-08-03 ...|SELECT ... |John |http:::1 |omnisci|GPU
190-5pax |PENDING_EXECUTOR |1 |2021-08-03 ...|SELECT ... |Cavin |http:::1 |omnisci|GPU
720-nQtV |RUNNING_QUERY_KERNEL|2 |2021-08-03 ...|SELECT ... |Cavin |tcp:::ffff:127.0.0.1:50142|omnisci|GPU
- KILL QUERY is only available if the runtime query interrupt parameter (
enable-runtime-query-interrupt) is set. - Interrupting a query in ‘PENDING_QUEUE’ status is supported in both distributed and single-server mode.
- To enable query interrupt for tables imported from data files in local storage, set
enable_non_kernel_time_query_interruptto TRUE. (It is enabled by default.)
Last modified 9mo ago