SHOW
Use SHOW
commands to get information about databases, tables, and user sessions.
SHOW CREATE SERVER
Shows the CREATE SERVER statement that could have been used to create the server.
Syntax
SHOW CREATE SERVER <servername>
Example
SHOW CREATE SERVER default_local_delimited;
create_server_sql
CREATE SERVER default_local_delimited FOREIGN DATA WRAPPER DELIMITED_FILE
WITH (STORAGE_TYPE='LOCAL_FILE');
SHOW CREATE TABLE
Shows the CREATE TABLE statement that could have been used to create the table.
Syntax
SHOW CREATE TABLE <tablename>
Example
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);
SHOW DATABASES
Retrieve the databases accessible for the current user, showing the database name and owner.
Example
SHOW DATABASES
Database Owner
omnisci admin
2004_zipcodes admin
game_results jane
signals jason
...
SHOW FUNCTIONS
Show registered compile-time UDFs and extension functions in the system and their arguments.
Syntax
SHOW FUNCTIONS [DETAILS]
Example
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
SHOW POLICIES
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.
Syntax
SHOW [EFFECTIVE] POLICIES <name>;
SHOW QUERIES
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).
Example
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.
To interrupt a query in the queue, see KILL QUERY.
SHOW ROLES
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.
Syntax
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 RUNTIME FUNCTIONS
Show user-defined runtime functions and table functions.
Syntax
SHOW RUNTIME [TABLE] FUNCTIONS
SHOW RUNTIME [TABLE] FUNCTION DETAILS
SHOW SUPPORTED DATA SOURCES
Show data connectors.
Syntax
show supported data sources
SHOW TABLE DETAILS
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.
Syntax
SHOW TABLE DETAILS [<table-name>, <table-name>, ...]
Examples
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
SHOW TABLE FUNCTIONS
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
For more information, see System Table Functions.
SHOW TABLE FUNCTIONS DETAILS
Show detailed output information for the specified table function. Output details vary depending on the table function specified.
Syntax
SHOW TABLE FUNCTIONS DETAILS <function_name>
Example - generate_series
View SHOW output for the generate_series
table function:

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
SHOW SERVERS
Retrieve the servers accessible for the current user.
Example
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"}
...
SHOW TABLES
Retrieve the tables accessible for the current user.
Example
SHOW TABLES;
table_name
----------
omnisci_states
omnisci_counties
omnisci_countries
streets_nyc
streets_miami
...
SHOW USER DETAILS
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.
Example
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.
Superuser Output
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
Non-Superuser Output
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
SHOW USER SESSIONS
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
...
KILL QUERY
Interrupt a queued query. Specify the query by using its session ID.
To see the queries in the queue, use the SHOW QUERIES command:
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
Last updated