EXAplus CLI

This section explains how to connect EXAplus to Exasol and run an SQL statement.

EXAplus is a simple command line interface client for running SQL statements in Exasol. EXAplus is a Java console application and is available in versions for Windows and Linux.

Windows

Environments

EXAplus has been successfully tested with Java 11 and Java 17 on the following operating systems:

  • Windows 10

  • Windows Server 2016

  • Windows Server 2019

Prerequisites

  • You must have Administrator rights on your Windows system.

  • Microsoft .NET Framework 4.0 Client Profile must be installed.

  • EXAplus requires a Java runtime environment for execution. To be able to use all features of EXAplus we recommend to use at least Java 8 and applied updates. We recommend that you select support for additional languages during the Java installation.

  • For correct formatting of special Unicode characters (such as Japanese characters), a font that is capable of displaying those characters must be installed on your Windows system.

Procedure

  1. Download the latest EXAplus client installer from the Exasol Downloads Portal and double-click to run the installation wizard.

    You must uninstall any existing versions of EXAplus before installing.

  2. Once EXAplus has been installed, open a command line shell in Windows and run the exaplusx64.exe executable from the EXAplus installation directory. The default location for the installation directory is C:\Program Files\Exasol\EXAplus.

    You can provide additional command line parameters to select different options when starting the EXAplus client. For a complete list of available options, see EXAplus command-line parameters (console mode only).

  3. Enter the username and password for the Exasol database.

  4. Enter the connection string to connect to the Exasol database.

    If the connection uses a TLS certificate and a valid certification path is not found, you may need to include the certificate fingerprint in the connection string.

    You can also run the JAR executable file directly from the command line shell using the command java -jar exaplus.jar. In this case the connection string and any command line parameters should be added after the JAR file path:

    java -jar exaplus.jar -c <hostname><fingerprint>:8563 -u <user> -P <password>

  5. After successfully connecting to the database, an interactive session is started where you can send SQL commands (refer to SQL Reference) or EXAplus commands (refer to EXAplus Commands) to the database. Result tables will be displayed as text.

    When using some options, such as -f, an interactive session will not be started.

    For example:

    EXAplus in cmd

Linux, macOS, FreeBSD

Environments

EXAplus has been successfully tested with OpenJDK JVM 11 on the following systems/distributions:

Linux (64-bit)

  • Red Hat / CentOS 7

  • Red Hat / CentOS 8 Stream

  • OpenSUSE Leap 15.2

  • Debian 10

  • Ubuntu 18.04 LTS

  • Ubuntu 20.04 LTS

macOS (64-bit)

  • macOS Big Sur (11.6.1)

  • macOS Monterey (12.0.1)

FreeBSD (64-bit)

  • FreeBSD 12.2

Prerequisite

  • Exasol should be installed and running on your system.
  • Java 8 or higher must be installed and the java executable must be included in the path.
  • For correct formatting of special Unicode characters (such as Japanese characters), a font that is capable of displaying those characters must be installed on your system.
  • To input Japanese characters using SCIM, we recommend installing SCIM version 1.4.7 or higher. Scim-Anthy for Japanese character support should be version 1.2.4 or higher.

Procedure

  1. Copy the URL of the EXAplus Linux (x86_64) installer from Exasol Downloads Portal.

    EXAplus CLI 7.1.x can be used to connect to Exasol 8 databases. There is currently no specific version of EXAplus CLI for Exasol 8.

  2. Run the following command using the path to the installer file.
    3. curl <installer download path> -o <filename>

    For example:

    curl https://x-up.s3.amazonaws.com/7.x/7.1.7/EXAplus-7.1.7.tar.gz -o EXAplus-7.1.7.tar.gz
  3. Use tar -xzf <filename> to extract the installer file. For example:
    tar -xzf EXAplus-7.1.7.tar.gz
  4. Go to the directory where you have extracted the EXAplus files. You can use the -help command to see the list of available options. Refer to EXAplus command-line parameters (console mode only) for a complete list of available options.
  5. Enter the connection details to connect to Exasol database. For example:
    ./exaplus -c 10.15.118.11:8563 -u sys -p exasol

    In the above command, -c is connection string, -u is the user, and -p is the password of the user.

    If the connection uses a TLS certificate and a valid certification path is not found, you may need to include the certificate fingerprint in the connection string.

  6. After successfully connecting to the database, an interactive session is started where the user can send SQL commands (refer to SQL Reference) or EXAplus commands (refer to EXAplus Commands) to the database. Result tables will be displayed as text.

    For example:

    Connect EXAplus Commandline to Exasol

    When using some options, such as -f, an interactive session will not be started.

Notes

  • You can exit EXAplus using the commands EXIT and QUIT.
  • All commands except EXIT and QUIT have to be finished by a semicolon (;), with the exception of function and script definitions, which must be finished by a forward slash (/) on a new line.
  • The command history can be viewed by using the up/down arrow keys.
  • Multi-line commands can be aborted by the shortcut <CTRL>-T.
  • Clicking <TAB> will make EXAplus try to autocomplete the input.
  • All command-line parameters are case sensitive.

EXAplus command-line parameters (console mode only)

Parameter Description
Help Options
-help Displays an overview of the command line parameters.
-version Displays version information.
Connection options
-c <connection string> Connection data which consists of the cluster nodes and the port (for example, 192.168.6.11..14:8563).
-u <user> User name for logging in to Exasol.
-p <passwd> Password for logging in to Exasol.
-s <schema> EXAplus will attempt to open the specified schema after logging in to Exasol.
-L Only one login attempt is performed. If the user name and password are specified in the command line, no connection is made in the event of an incorrect password. This parameter is very useful for utilization in scripts.
-profile <profile name>

Name of a connection profile defined in <configDir>/profiles.xml. For more information, see Profile handling options.

<configDir> here refers to the Exasol configuration directory on your local system. On a Windows system this is typically C:\Users\[username]\exasol and on a Linux system it is ~/.exasol

-k Use Kerberos based single sign-on.
Profile handling options
-wp <profile name>

Write connection options to a specified connection profile and exit. The saved profile can then be used to connect to Exasol.

For example: 

user@localhost:~/EXAplus-7.1.7$ ./exaplus -c localhost:8563 -u sys -p exasol -wp my_profile
EXAplus 7.1.7 (c) EXASOL AG

Profile my_profile is saved.

user@localhost:~/EXAplus-7.1.7$ ./exaplus -profile my_profile
EXAplus 7.1.7 (c) EXASOL AG

Friday, May 6, 2022 at 9:55:56 AM Central European Summer Time
Connected to database DB1 as user sys.
EXASolution 7.1.7 (c) EXASOL AG

SQL_EXA>
-lp Print a list of existing profiles and exit.
-dp <profile name> Delete a specified profile and exit.
File options

SQL files can be integrated via the parameters -f or -B, or via the EXAplus commands start, @, and @@. Refer to EXAplus Commands for more information.

EXAplus will first search for the specified files relatively to the working directory and in the folders which are defined in the environment variable SQLPATH. If the files are not found, EXAplus also tries to add an implicit .sql and .SQL ending.

The variable SQLPATH contains similar to the variable PATH a list of folder, separated by a colon (:) under Linux and by a semicolon (;) under Windows.
-init <file>

File which is initially executed after the start.

Default: exaplus.sql
-f <file> EXAplus executes all the statements in the file and then terminates. If auto-commit is enabled, EXAplus executes a COMMITafter each statement in the file and then terminates at the end of the file.
-B <file> Similar to -f, but EXAplus executes all commands at once in batch mode. If auto-commit is enabled, EXAplus executes a COMMIT only at the end of the file and then terminates.
-encoding <encoding>

Sets the character set for reading of SQL-scripts started with -f or -B. For supported encodings, refer to the Supported encodings for ETL processes section.

Default: UTF-8

By using the command SET ENCODING you can also change the character set during a session. The change will not affect already opened files.
-- <args>

SQL files can use arguments given over via the parameter -- by evaluating the variables &1, &2, and so on.

For example, the file test.sql including the content

--test.sql
SELECT * FROM &1;

can be called in the following way: 

exaplus -f test.sql -- dual
Reconnect options
-recoverConnection <ON|OFF>

Attempts to recover a lost connection.

Default: OFF
-retry <num>

Determines the number of attempts to recover a connection. If either -1 or UNLIMITED is specified, repeated attempts will be made to recover the connection indefinitely.

Default: UNLIMITED
-retryDelay <num>

Minimum interval between two recovery attempts in seconds.

Default: 5
-closeOnConnectionLost

Exits EXAplus after the loss of a connection that cannot be recovered.

Default: ON
Other options
-autocommit <ON|OFF|EXIT> Sets the auto-commit mode. ON enables auto-commit, OFF disables it. EXIT effects an auto-commit when the program is exited or disconnected.
Default: ON
-lang <EN|DE|JA> Defines the language of EXAplus messages.
Default: Depends on the system preferences
-characterwidth <HALF|FULL>

Defines the width of Unicode characters when displaying a query result.
Default:

  • lang = EN or DE - HALF
  • lang = JA - FULL
-q Quiet mode which suppresses additional output from EXAplus.
-x

EXAplus will exit on errors. It will return 0 for success, -2 for SQL errors, -1 for all other errors..
-F <num>

Determines the fetchsize in kB which specifies the amount of resultset data is sent during one single communication step with the database.

Default: 2000
-Q <num>

Query Timeout in seconds. A query will be aborted if the timeout is exceeded.

Default: -1 (unlimited)
-autoCompletion <ON|OFF>

If this function is enabled, the user can obtain proposals by pressing TAB. When using scripts, this prediction aid will be automatically deactivated.

Default: ON
-pipe

With this parameter you can use pipes on Linux/Unix systems. For example:


echo "SELECT * FROM dual;" | exaplus -pipe -c ... -u ... -p ...
echo file.csv | perl exaplus -c host -u login -p pass -s schema -sql "import into tst_pipe_import FROM LOCAL CSV FILE 'System.in';" -pipe

Default: OFF
-sql <SQL statement> With this parameter you can execute single SQL statements. EXAplus quits afterwards. Example: -sql "SELECT \"DUMMY\" FROM dual;"
-jdbcparam <JDBC parameter> Set additional JDBC parameters.

 

Next steps

After connecting EXAplus CLI to Exasol, you can start loading data into the Exasol database. For more information, see Load Data.