omnisql

omnisql is the client-side SQL console that displays query results for SQL statements you submit to the OmniSciDB Server.
Syntax
omnisql [<database>] [<options>]
<database> is the name of the database to connect to. The default database name is omnisci.
Options
Option
Description
-h [ --help ]
Print help messages.
-v [ --version ]
Print omnisql version number.
-n [ --no-header ]
Do not print query result header.
-t [ --timing ]
Print timing information.
-d [ --delimiter ] arg (=|)
Field delimiter in row output.
--db arg
Database name (default is omnisci).
-u [ --user ] arg (=admin)
Username.
--ca-cert arg
Path to trusted server certificate. Initiates an encrypted connection.
-p [ --passwd ] arg
Password.
--history arg
History filename.
-s [ --server ] arg (=localhost)
Server hostname.
--port arg
Port number. Default is 6274.
--http
Use HTTP transport.
--https
Use HTTPS transport.
--skip-verify
Do not verify SSL certificate validity.
-q [ --quiet ]
Do not print result headers or connection strings.
Running omnisql
After starting omnisql, you can enter SQL queries or backslash commands from the command line.
The OmniSci server has a default one hour timeout on individual HTTP requests, including those made from omnisql, when using Thrift HTTP transport. If your queries are expected to exceed the timeout, use either the default omnisql TCP transport or increase the timeout using the omnisci_web_server --timeout option.
If the connection to the server is lost, omnisql automatically attempts to reconnect.
Commands
You can use the backslash commands listed in the table below for a variety of tasks beyond SQL queries.
The commands listed below return results based on privileges granted to the current user. For example, the \d command lists only those databases to which the active omnisql user has access privileges.
Command
Description
\c <database> <user> <password>
Connect to a database.
\clear_cpu
Clear the CPU memory. Generally, the server takes care of memory management and you would not need to use this command. If you are having unexpected memory issues, you can try clearing the CPU memory to see if performance improves.
\clear_gpu
Clear the GPU memory. Generally, the server takes care of memory management and you would not need to use this command. If you are having unexpected memory issues, you can try clearing the GPU memory to see if performance improves.
\copy <file path> <table>
Copy or append data from client-side file to table. The file is assumed to be in CSV format unless the file name ends with .tsv. OmniSci supports Latin-1 ASCII format and UTF-8. If you want to load data with another encoding (for example, UTF-16), convert the data to UTF-8 before loading it to OmniSci.
\cpu
Switch to CPU mode in the current session.
\d <table>
Describe table columns using a SQL CREATE TABLE statement.
\d <view>
Describe the results of a view SELECT statement.
\dash
List all dashboards accessible by the current user.
\detect [parquet] {<filePath> | <s3Link>}
Displays the inferred schema for CSV/TSV files, or the actual schema for Parquet files.

\detect does not support custom parameters. It uses default values, such as commas as the delimiter (tabs in .tsv files). This is not applicable for Parquet files.
\export_dashboard <dashboard name> <filename>
Exports a dashboard to a filepath. Files with spaces in their names should be quoted. If there is a quote within a quoted string, it should be escaped with a backslash.
\gpu
Switch to GPU mode in the current session.
\h
Help. List available backslash commands.
\historylen <number>
Set the history buffer size (default is 100).
\import_dashboard <dashboard name> <filename>
Imports a dashboard from a filepath. Files with spaces in their names should be quoted. If there is a quote within a quoted string, it should be escaped with a backslash.
\l
List databases.
\memory_summary
Print memory usage summary.
\multiline
Set multi-line command mode.
\notiming
Do not print timing information.
\o <table>
Return the optimal CREATE TABLE statement for a table, based on the size of the actual data stored.
\object_privileges {database|table} <object_name>
Reports all privileges granted to an object for all roles and users.
\privileges {<rolename>|<userName>}
Reports all database object privileges granted to role or user.
\q
Quit omnisql.
\role_list <username>
Reports all roles granted to user.
\roles
Reports all roles.
\set_license <licensekey>
Applies license key to the OmniSci instance. Does not require the services to be restarted to apply the license change.
\singleline
Set single-line command mode.
\status
Get the status of the server and its leaf nodes.
\t [<regex>]
List tables, with optional regular expression.
\timing
Print timing information.
\u [<regex>]
List users, with optional regular expression.
\v [<regex>]
List views, with optional regular expression.
\version
Print OmniSciDB server version.
Unlike SQL statements, backslash commands do not require a terminating semicolon character.
Runtime Examples
The , \u, and \v commands might return a long list of values. You can use a regular expression match pattern to filter the results. For example, you could use the following command to return only tables that start with the word flight.
omnisql> \t ^flight.*
flights_2008_10k
flights_2008_7M
SQL query example:
omnisql> SELECT * FROM movies WHERE movieId=260;
movieId|title|genres
260|Star Wars: Episode IV - A New Hope (1977)|Action|Adventure|Sci-Fi
Backslash command example that describes a table:
omnisql> \d movies
CREATE TABLE movies (
movieId INTEGER,
title TEXT ENCODING DICT(32),
genres TEXT ENCODING DICT(32))
If you frequently perform the same tasks, you can create a script and pipe it to omnisql. You can use both SQL commands and omnisql commands in your script.
cat script.sql | omnisql -p <password>
For example, if you periodically upload data to the movies table, you can append rows from files named movies.csv using the following script, and display the results.
\copy ./movies.csv movies
select * movies;
When you pipe the script to omnisql, you get results similar to the following.
$ cat ~/script.sql | ./omnisql -p MyPasswordShhSecret
User omnisci connected to database omnisci
movieId|title|genres
1|Explosions Extravaganza|Action
2|Cuddle Time|Romantic Comedy
3|Chuckle Buddies|Comedy
4|All the Feels|Drama
User omnisci disconnected from database omnisci

Option	Description
`-h [ --help ]`	Print help messages.
`-v [ --version ]`	Print omnisql version number.
`-n [ --no-header ]`	Do not print query result header.
`-t [ --timing ]`	Print timing information.
`-d [ --delimiter ]` `arg` `(=\|)`	Field delimiter in row output.
`--db` `arg`	Database name (default is omnisci).
`-u [ --user ]` `arg` `(=admin)`	Username.
`--ca-cert` `arg`	Path to trusted server certificate. Initiates an encrypted connection.
`-p [ --passwd ]` `arg`	Password.
`--history` `arg`	History filename.
`-s [ --server ]` `arg` `(=localhost)`	Server hostname.
`--port` `arg`	Port number. Default is 6274.
`--http`	Use HTTP transport.
`--https`	Use HTTPS transport.
`--skip-verify`	Do not verify SSL certificate validity.
`-q [ --quiet ]`	Do not print result headers or connection strings.

Command	Description
`\c <database> <user> <password>`	Connect to a database.
`\clear_cpu`	Clear the CPU memory. Generally, the server takes care of memory management and you would not need to use this command. If you are having unexpected memory issues, you can try clearing the CPU memory to see if performance improves.
`\clear_gpu`	Clear the GPU memory. Generally, the server takes care of memory management and you would not need to use this command. If you are having unexpected memory issues, you can try clearing the GPU memory to see if performance improves.
`\copy <file path> <table>`	Copy or append data from client-side file to table. The file is assumed to be in CSV format unless the file name ends with `.tsv`. OmniSci supports Latin-1 ASCII format and UTF-8. If you want to load data with another encoding (for example, UTF-16), convert the data to UTF-8 before loading it to OmniSci.
`\cpu`	Switch to CPU mode in the current session.
`\d <table>`	Describe table columns using a SQL CREATE TABLE statement.
`\d <view>`	Describe the results of a view SELECT statement.
`\dash`	List all dashboards accessible by the current user.
`\detect [parquet] {<filePath> \| <s3Link>}`	Displays the inferred schema for CSV/TSV files, or the actual schema for Parquet files. `\detect` does not support custom parameters. It uses default values, such as commas as the delimiter (tabs in .tsv files). This is not applicable for Parquet files.
`\export_dashboard <dashboard name> <filename>`	Exports a dashboard to a filepath. Files with spaces in their names should be quoted. If there is a quote within a quoted string, it should be escaped with a backslash.
`\gpu`	Switch to GPU mode in the current session.
`\h`	Help. List available backslash commands.
`\historylen <number>`	Set the history buffer size (default is 100).
`\import_dashboard <dashboard name> <filename>`	Imports a dashboard from a filepath. Files with spaces in their names should be quoted. If there is a quote within a quoted string, it should be escaped with a backslash.
`\l`	List databases.
`\memory_summary`	Print memory usage summary.
`\multiline`	Set multi-line command mode.
`\notiming`	Do not print timing information.
`\o <table>`	Return the optimal CREATE TABLE statement for a table, based on the size of the actual data stored.
`\object_privileges {database\|table} <object_name>`	Reports all privileges granted to an object for all roles and users.
`\privileges {<rolename>\|<userName>}`	Reports all database object privileges granted to role or user.
`\q`	Quit omnisql.
`\role_list <username>`	Reports all roles granted to user.
`\roles`	Reports all roles.
`\set_license <licensekey>`	Applies license key to the OmniSci instance. Does not require the services to be restarted to apply the license change.
`\singleline`	Set single-line command mode.
`\status`	Get the status of the server and its leaf nodes.
`\t [<regex>]`	List tables, with optional regular expression.
`\timing`	Print timing information.
`\u [<regex>]`	List users, with optional regular expression.
`\v [<regex>]`	List views, with optional regular expression.
`\version`	Print OmniSciDB server version.

Last modified 1yr ago