Using the splice> Command Line Interface

This topic presents information that will help you in using the Splice Machine splice> command line interpreter, in the following sections:

  • The splice> Command Line Interpreter section shows you how to invoke the splice> command line.
  • The Command Line Output section describes how you can adjust the appearance of output from the interepreter.
  • The Command Line Syntax section summarizes the syntax of commands, including capitalization and case-sensitivity rules, as well as various special characters you can use in your commands. It also shows you how to include comments in your command lines and how to run a file of SQL commands.
  • The Example Command Lines section shows several examples of command lines.
  • Our Scripting Splice Commands tutorial describes how to create a script of splice> commands to run a series of operations like loading a number of files into your database

The remainder of this section contains a reference page for each of the command line commands.

splice> Command Line Interpreter

To run the Splice Machine command line interpreter, run the sqlshell.sh script in your terminal window.

% ./sqlshell.sh
splice>

When the interpreter prompts you with splice>, you can enter commands, ending each with a semicolon. For a complete description of splice> syntax, see the next section in this topic, Command Line Syntax.

Splice Machine requires Oracle JDK 1.8, update 60 or higher to run; if you try to start sqlshell.sh on a system that doesn’t have the required version of the JDK, you’ll see an error message indicating that the connection has been terminated.

sqlshell.sh Command Line Options

You can optionally include parameter values when running sqlshell.sh script, to change default values. Here’s the syntax:

sqlshell.sh [-U url] [-h host] [-p port] [-u user] [-s pass] [-P] [-S]
            [-k principal] [-K keytab] [-w width] [-f script] [-o output] [-q]
Option Description Example
-U url The full JDBC URL for connecting to your Splice Machine database. -U 'jdbc:splice://xyz:1527/splicedb'
-h hostname The hostname or IP address of the Splice Machine HBase Region Server. -h splicetrial-mycluster.splicemachine.io
-p port

The port on which Splice Machine is listening.

The default value is 1527.

-p 10001
-u user Your Splice Machine database username. -u myName
-s pass Your Splice Machine database password. -s myPswd
-P

Tells Splice Machine to Prompt for your password.

Your keystrokes are obscured when entering your password.

-P
-S

Use basic connection Security (ssl=basic) for connecting to your database.

You must use this option when using sqlshell.sh with our Database-as-Service product.

-S
-k principal Your kerberos principal. -k splice
-K keytab

Your Kerberos keytab.

You must also specify the -k option when specifying this option.

-K splice.keytab
-w width

The width of output rows in your window.

The default width is 128.

-w 200
-f scriptFile

The fully-qualified name of the SQL file to be executed.

For more information about running SQL scripts, please see our Scripting Splice Commands] tutorial.

-f mySqlScript.sql
-o outputFile

Redirects the output of a script

This is typically used in conjunction with running a script with the -f option.

-o /tmp/myscript.out
-q

Starts sqlshell in quiet mode, which suppresses the series of messages that displays when you first start sqlshell.sh.

This is useful when running a script with the -f option.

-q

Command Line Output

Output from splice> commands is displayed in your terminal window. The maximumdisplaywidth setting affects how the output is displayed; specifically, it determines if the content of each column is truncated to fit within the width that you specify.

When you set maximumdisplaywidth to 0, all output is displayed, without truncation.

Command Line Syntax

This section briefly summarizes the syntax of command lines you can enter in Zeppelin notebooks and in response to the splice> prompt, including these subsections:

Finish Commands with a Semicolon

The command line interface allows you to enter multi-line commands, and waits for a non-escaped semicolon (;) character to signal the end of the command.

A command is not executed until you enter the semicolon character and press the Return or Enter key.

Capitalization and Case Sensitivity Rules

Certain identifiers and keywords are case sensitive:

Identifier Case Sensitive? Notes and Example

SQL keywords

Not case sensitive These are all equivalent: SELECT, Select, select, SeLeCt.
ANSI SQL identifiers Not case sensitive These are not case sensitive unless they are delimited.
Java-style identifiers Always case sensitive

These are NOT equivalent: my_name, My_Name.

Special Characters You Can Use

The following table describes the special characters you can use in commands:

Purpose Character(s) to use Notes and example
To delimit special identifiers Double quotation marks (") Special identifiers are also known as delimited identifiers.
To delimit character strings Single quotation marks (')
To escape a single quote or apostrophe within a character string Single quotation mark ( (')

Since single quotation marks are used to delimit strings, you must escape any single quotation marks you want included in the string itself.

Use the single quotation mark itself as the escape character, which means that you enter two single quotation marks within a character string to include one single quotation mark.

Example: 'This string includes ''my quoted string'' within it.'

To escape a double quote Not needed You can simply include a double quotation mark in your command lines.
To specify a wild card within a Select expression The asterisk (*) character

This is the SQL metasymbol for selecting all matching entries.

Example: SELECT * FROM MyTable;

To specify a wild card sequence in a string with the LIKE operator The percentage (%) character Example: SELECT * FROM MyTable WHERE Name LIKE 'Ga%';
To specify a single wild card character in a string with the LIKE operator The underline (_) character Example: SELECT * FROM MyTable WHERE Name LIKE '%Er_n%';
To begin a single-line comment Two dashes (--)

-- the following selects everything in my table:
SELECT * FROM MyTable;

To bracket a multi-line comment /* and */

All text between the comment start /* and the comment end */ is ignored.

/* the following selects everything in my table,
which we'll then display on the screen */
SELECT * FROM MyTable;

Entering Multi-line Commands

When using the command line (the splice> prompt), you must end each SQL statement with a semicolon (;). For example:

splice> select * from myTable;

You can extend SQL statements across multiple lines, as long as you end the last line with a semicolon. Note that the splice> command line interface prompts you with a fresh > at the beginning of each line. For example:

splice> select * from myTable
> where i > 1;

Running SQL Statements From a File

You can also create a file that contains a collection of SQL statements, and then use the run command to run those statements. For example:

splice> run 'path/to/file.sql';

Including Comments

You can include comments on the command line or in a SQL statement file by prefacing the command with two dashes (--). Any text following the dashes is ignored by the SQL parser. For example:

splice> select * from myTable   -- This selects everything in myTable
> ;

Misaligned Quotes

If you mistakenly enter a command line that has misaligned quotation symbols, the interpreter can seem unresponsive. The solution is to add the missing quotation mark(s), followed by a semicolon, and resubmit the line. It won’t work as expected, but it will enable you to keep working.

Using rlWrap on the Command Line

rlWrap is a Unix utility that Splice Machine encourages you to use: it allows you to scroll through your command line history, reuse and alter lines, and more. We’ve included a synopsis of it here.

Example Command Lines

Here are several example command lines:

Operation Command Example
Display a list of all tables and their schemas splice> show tables;
Display the columns and attributes of a table splice> describe tableA;
Limit the number of rows returned from a select statement splice> select * from tableA { limit 10 };
Print a current time stamp splice> values current_timestamp;

Remember that you must end your command lines with the semicolon (;) character, which submits the command line to the interpreter.