HEAVY.AI Docs
v8.1.0
v8.1.0
  • Welcome to HEAVY.AI Documentation
  • Overview
    • Overview
    • Release Notes
  • Installation and Configuration
    • System Requirements
      • Hardware Reference
      • Software Requirements
      • Licensing
    • Installation
      • Free Version
      • Installing on Docker
        • HEAVY.AI Installation using Docker on Ubuntu
      • Installing on Ubuntu
        • HEAVY.AI Installation on Ubuntu
        • Install NVIDIA Drivers and Vulkan on Ubuntu
      • Installing on Rocky Linux / RHEL
        • HEAVY.AI Installation on RHEL
        • Install NVIDIA Drivers and Vulkan on Rocky Linux and RHEL
      • Getting Started on AWS
      • Getting Started on GCP
      • Getting Started on Azure
      • Getting Started on Kubernetes (BETA)
      • Upgrading
        • Upgrading HEAVY.AI
        • Upgrading from Omnisci to HEAVY.AI 6.0
        • CUDA Compatibility Drivers
      • Uninstalling
      • Ports
    • Services and Utilities
      • Using Services
      • Using Utilities
    • Executor Resource Manager
    • Configuration Parameters
      • Overview
      • Configuration Parameters for HeavyDB
      • Configuration Parameters for HEAVY.AI Web Server
      • Configuration Parameters for HeavyIQ
    • Security
      • Roles and Privileges
        • Column-Level Security
      • Connecting Using SAML
      • Implementing a Secure Binary Interface
      • Encrypted Credentials in Custom Applications
      • LDAP Integration
    • Distributed Configuration
  • Loading and Exporting Data
    • Supported Data Sources
      • Kafka
      • Using HeavyImmerse Data Manager
      • Importing Geospatial Data
    • Command Line
      • Loading Data with SQL
      • Exporting Data
  • SQL
    • Data Definition (DDL)
      • Datatypes
      • Users and Databases
      • Tables
      • System Tables
      • Views
      • Policies
      • Comment
    • Data Manipulation (DML)
      • SQL Capabilities
        • ALTER SESSION SET
        • ALTER SYSTEM CLEAR
        • DELETE
        • EXPLAIN
        • INSERT
        • KILL QUERY
        • LIKELY/UNLIKELY
        • SELECT
        • SHOW
        • UPDATE
        • Arrays
        • Logical Operators and Conditional and Subquery Expressions
        • Table Expression and Join Support
        • Type Casts
      • Geospatial Capabilities
        • Uber H3 Hexagonal Modeling
      • Functions and Operators
      • System Table Functions
        • generate_random_strings
        • generate_series
        • tf_compute_dwell_times
        • tf_feature_self_similarity
        • tf_feature_similarity
        • tf_geo_rasterize
        • tf_geo_rasterize_slope
        • tf_graph_shortest_path
        • tf_graph_shortest_paths_distances
        • tf_load_point_cloud
        • tf_mandelbrot*
        • tf_point_cloud_metadata
        • tf_raster_contour_lines; tf_raster_contour_polygons
        • tf_raster_graph_shortest_slope_weighted_path
        • tf_rf_prop_max_signal (Directional Antennas)
        • ts_rf_prop_max_signal (Isotropic Antennas)
        • tf_rf_prop
      • Window Functions
      • Reserved Words
      • SQL Extensions
      • HeavyIQ LLM_TRANSFORM
  • HeavyImmerse
    • Introduction to HeavyImmerse
    • Admin Portal
    • Control Panel
    • Working with Dashboards
      • Dashboard List
      • Creating a Dashboard
      • Configuring a Dashboard
      • Duplicating and Sharing Dashboards
    • Measures and Dimensions
    • Using Parameters
    • Using Filters
    • Using Cross-link
    • Chart Animation
    • Multilayer Charts
    • SQL Editor
    • Customization
    • Joins (Beta)
    • Chart Types
      • Overview
      • Bubble
      • Choropleth
      • Combo
      • Contour
      • Cross-Section
      • Gauge
      • Geo Heatmap
      • Heatmap
      • Linemap
      • Number
      • Pie
      • Pointmap
      • Scatter Plot
      • Skew-T
      • Table
      • Text Widget
      • Wind Barb
    • Deprecated Charts
      • Bar
      • Combo - Original
      • Histogram
      • Line
      • Stacked Bar
    • HeavyIQ SQL Notebook
  • HEAVYIQ Conversational Analytics
    • HeavyIQ Overview
      • HeavyIQ Guidance
  • HeavyRF
    • Introduction to HeavyRF
    • Getting Started
    • HeavyRF Table Functions
  • HeavyConnect
    • HeavyConnect Release Overview
    • Getting Started
    • Best Practices
    • Examples
    • Command Reference
    • Parquet Data Wrapper Reference
    • ODBC Data Wrapper Reference
    • Raster Data Wrapper Reference
  • HeavyML (BETA)
    • HeavyML Overview
    • Clustering Algorithms
    • Regression Algorithms
      • Linear Regression
      • Random Forest Regression
      • Decision Tree Regression
      • Gradient Boosting Tree Regression
    • Principal Components Analysis
  • Python / Data Science
    • Data Science Foundation
    • JupyterLab Installation and Configuration
    • Using HEAVY.AI with JupyterLab
    • Python User-Defined Functions (UDFs) with the Remote Backend Compiler (RBC)
      • Installation
      • Registering and Using a Function
      • User-Defined Table Functions
      • RBC UDF/UDTF Example Notebooks
      • General UDF/UDTF Tutorial Notebooks
      • RBC API Reference
    • Ibis
    • Interactive Data Exploration with Altair
    • Additional Examples
      • Forecasting with HEAVY.AI and Prophet
  • APIs and Interfaces
    • Overview
    • heavysql
    • Thrift
    • JDBC
    • ODBC
    • Vega
      • Vega Tutorials
        • Vega at a Glance
        • Getting Started with Vega
        • Getting More from Your Data
        • Creating More Advanced Charts
        • Using Polys Marks Type
        • Vega Accumulator
        • Using Transform Aggregation
        • Improving Rendering with SQL Extensions
      • Vega Reference Overview
        • data Property
        • projections Property
        • scales Property
        • marks Property
      • Migration
        • Migrating Vega Code to Dynamic Poly Rendering
      • Try Vega
    • RJDBC
    • SQuirreL SQL
    • heavyai-connector
  • Tutorials and Demos
    • Loading Data
    • Using Heavy Immerse
    • Hello World
    • Creating a Kafka Streaming Application
    • Getting Started with Open Source
    • Try Vega
  • Troubleshooting and Special Topics
    • FAQs
    • Troubleshooting
    • Vulkan Renderer
    • Optimizing
    • Known Issues and Limitations
    • Logs and Monitoring
    • Archived Release Notes
      • Release 6.x
      • Release 5.x
      • Release 4.x
      • Release 3.x
Powered by GitBook
On this page
  • Syntax
  • Options
  • Running heavysql
  • Commands
  • Runtime Examples
Export as PDF
  1. APIs and Interfaces

heavysql

heavysql is the client-side SQL console that displays query results for SQL statements you submit to the HeavyDB Server.

Syntax

heavysql [<database>] [<options>]

<database> is the name of the database to connect to. The default database name is heavyai.

Options

Option

Description

-a [ --echo-all ]

When input is read from a file, print all executed commands and queries.

--ca-cert arg

Path to trusted server certificate. Initiates an encrypted connection.

-d [ --delimiter ] arg (=|)

Field delimiter in row output.

--db arg

Database name (default is heavyai).

-h [ --help ]

Print help messages.

--history arg

History filename.

--http

Use HTTP transport.

--https

Use HTTPS transport.

-n [ --no-header ]

Do not print query result header.

-p [ --passwd ] arg

Password.

--port arg

Port number. Default is 6274.

-q [ --quiet ]

Do not print result headers or connection strings.

-s [ --server ] arg (=localhost)

Server hostname.

--skip-verify

Do not verify SSL certificate validity.

-t [ --timing ]

Print timing information.

-u [ --user ] arg (=admin)

Username.

-v [ --version ]

Print heavysql version number.

Running heavysql

After starting heavysql, you can enter SQL queries or backslash commands from the command line.

The HEAVY.AI server has a default one hour timeout on individual HTTP requests, including those made from heavysql, when using Thrift HTTP transport. If your queries are expected to exceed the timeout, use either the default heavysql TCP transport or increase the timeout using the heavy_web_server --timeout option.

If the connection to the server is lost, heavysql 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 heavysql 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. HEAVY.AI 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 HEAVY.AI.

\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 [geo | parquet | raster] <filename> {<filePath> | <s3Link>}

Displays the inferred schema for CSV/TSV files, or the actual schema for geo, Parquet, or raster 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 geo, Parquet, or raster 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 HEAVY.AI 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 HeavyDB server version.

Unlike SQL statements, backslash commands do not require a terminating semicolon character.

Runtime Examples

The \t, \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.

heavysql> \t ^flight.*
flights_2008_10k
flights_2008_7M

SQL query example:

heavysql> 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:

heavysql> \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 heavysql. You can use both SQL commands and heavysql commands in your script.

cat script.sql | heavysql -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 heavysql, you get results similar to the following.

$ cat ~/script.sql | ./heavysql -p MyPasswordShhSecret
User heavyai connected to database heavyai
movieId|title|genres
1|Explosions Extravaganza|Action
2|Cuddle Time|Romantic Comedy
3|Chuckle Buddies|Comedy
4|All the Feels|Drama
User heavyai disconnected from database heavyai
PreviousOverviewNextThrift