EXAplus CLI

EXAplus is an user interface for dealing with SQL statements in Exasol. It is implemented in Java and is available as simple console version both under Windows and Linux.

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

For Windows

Environments

EXAplus has been successfully tested on the following systems:

  • Windows 10 (x86/x64)
  • Windows 7, Service Pack 1 (x86/x64)
  • Windows Server 2012 R2 (x86/x64)
  • Windows Server 2012 (x86/x64)
  • Windows Server 2008 R2, Service Pack 1 (x86/x64)
  • Windows Server 2008, Service Pack 2 (x86/x64)

Prerequisite

  • Administrator rights and the Microsoft .NET Framework 4.0 Client Profile ™ are required for installing EXAplus.
  • EXAplus requires a Java runtime environment for execution. To be able to use all features of EXAplus we recommend to use at least Java 7 and applied updates. It is also recommended that support for additional languages is selected during the Java installation. For correct formatting of special unicode characters (for example, Japanese characters), a font that is capable of displaying those characters must be installed on your Windows system.
  • EXAplus client should be installed on your system. You can download the installer from Exasol Download page.

Procedure

  1. Open exaplus.exe file from the EXAplus installation directory. The default location is C:\Program Files (x86)\Exasol\EXASolution-7.0\EXAplus.
  2. Enter the database username and password.
  3. Enter the connection string to connect to Exasol database.
  4. After successfully connecting to the database, an interactive session is started where users can send SQL commands (refer to SQL Referencesection ) or EXAplus commands refer to EXAplus-Specific Commands section) to the database. Result tables are then displayed in text form.

    Connect EXAplus Commanline to Exasol

For Linux and Mac OS

Environments

EXAplus has been successfully tested on the following systems using the specified Java version with the latest available updates:

  • Red Hat / CentOS 7, OpenJDK JVM 1.8.0 (x64)
  • Red Hat / CentOS 6, OpenJDK JVM 1.8.0 (x86/x64)
  • Debian 8, OpenJDK JVM 1.7.0 (x86/x64)
  • Ubuntu 16.04 LTS, OpenJDK JVM 1.8.0 (x86/64)
  • Ubuntu 14.04 LTS, OpenJDK JVM 1.7.0 (x86/64)
  • SUSE Linux Enterprise Server 12, IBM's JVM 1.7.0 (x64)
  • SUSE Linux Enterprise Desktop 12, OpenJDK JVM 1.7.0 (x64)
  • SUSE Linux Enterprise Server 11 Service Pack 3, IBM's JVM 1.7.0 (x86/x64)
  • openSUSE Leap 42.2, OpenJDK JVM 1.8.0 (x64)
  • macOS Sierra, JVM 1.8.0 (64Bit)
  • OS X 10.11, JVM 1.8.0 (64Bit)
  • FreeBSD 11.0, OpenJDK 1.8.0 (64Bit)
  • FreeBSD 10.3, OpenJDK 1.8.0 (64Bit)

Prerequisite

Exasol should be installed and running on your system.

  • Java 7 or higher has to be installed and the program "java" must be included in the path. For correct formatting of special unicode characters (e.g. Japanese characters), a font that is capable of displaying those characters must be installed on your system.
  • If you want to use Japanese characters and your Input Manager is Scim, we recommend to install at least version 1.4.7 of Scim. Additionally, Scim-Anthy (Japanese character support) should be installed in version 1.2.4 or higher.

Procedure

  1. Copy the path of the EXAplus installer from Exasol download page.
  2. Run the following command using the path of the installer file.
    curl <installer download path>
    For example,

    curl https://www.exasol.com/support/secure/attachment/111497/EXAplus-7.0.0.tar.gz

  3. Run the following command to extract the installer file.

    tar -xzf EXAplus-7.0.0.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. Additionally, you can refer to the EXAplus Command Line Parameters (Console mode only) table for more information.
  5. Enter the connection details to connect to Exasol database. For example,

    -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.

  6. After successfully connecting to the database, an interactive session is started where users can send SQL commands (refer to SQL Referencesection ) or EXAplus commands (refer to EXAplus-Specific Commands section) to the database. Result tables are then displayed in text form.

    Connect EXAplus Commandline to Exasol

Notes

  • You can exit EXAplus via the commands EXIT and QUIT.
  • All commands have to be finished by a semicolon (except view, function and script definitions which must be finished by a / in a new line and except the commands EXIT and QUIT).
  • The command history can be used via the arrow keys (up/down).
  • Multi-line commands can be aborted by using the shortcut <CTRL>-T.
  • By pressing <TAB> EXAplus tries to complete the input.

EXAplus Command Line Parameters (Console mode only)

Parameters Annotation
Help Options
-help Displays a short overview about 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 the log-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.
-encryption <ON|OFF> Enables the encryption of the client / server communication.
Default: ON
-profile <profile name> Name of connection profile defined in <configDir> / profiles.xml (changeable with profile handling parameters).
-k Use Kerberos based single sign-on.
Profile handling options
-lp Print a list of existing profiles and exit.
-dp <profile name> Delete a specified profile and exit.
-wp <profile name> Write a specified profile and exit. The profile is defined by connection options.
File Options
SQL files can be integrated via the parameters -f or -B or via the EXAplus commands start, @ and @@ (refer to the EXAplus-Specific Commands section for more information). EXAplus will search the specified files firstly relatively to the working directory, but also 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 an “:” under Linux and by an “;” under Windows.
-init <file> File which is initially executed after the start.
Default: exaplus.sql
-f <file> EXAplus executes that script and 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. As default the character set UTF-8 is used.

By using the command SET ENCODING you can also change the character set during a session. But you have to consider that the change has no impact on already opened files.

--<args>

SQL files can use arguments given over via the parameter “--” by evaluating the variables &1, &2 etc.

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 Stops EXAplus in the event of 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> By 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 Step

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