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.
  • The 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,

Note that you can optionally specify a file of sql commands that the interpreter will run using the -f parameter; after running those commands, the interpreter exits. For example:

./sqlshell.sh -f /home/mydir/sql/test.sql

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

sqlshell.sh [-h host] [-p port ] [-u username] [-s password] [-f commandsFile

-host

The hostname or IP address of your Splice Machine HBase RegionServer.

The default value is localhost.

-port

The port on which Splice Machine is listening for your connection.

The default value is 1527.

-username

The user name for your Splice Machine database.

The default value is splice.

-password

The password for your Splice Machine database.

The default value is admin.

-f [fileName]

The name of a file with SQL commands in it: sqlshell starts up the splice> command line interpreter, runs the commands in the file, and then exits. For example:

$ ./sqlshell.sh -f /home/mydir/sql/test.sql

 ========= rlwrap detected and enabled.  Use up and down arrow keys to scroll through command line history. ========

Running Splice Machine SQL shell
For help: "splice> help;"
SPLICE* - 	jdbc:splice://10.1.1.111:1527/splicedb
* = current connection
splice> elapsedtime on;
splice> select count(*) from CUST_EMAIL;
1
--------------------
0

1 row selected
ELAPSED TIME = 6399 milliseconds
splice>
$

The test.sql file used in the above example contains the following commands:

elapsedtime on;
select count(*) from CUST_EMAIL;

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.

Scripting splice> Commands

You can use the Splice Machine Command Line Interface (splice>) to interactively run database commands. This topic describes how to create a script of splice> commands to run a series of operations, such as loading a number of files into your database. To script a series of splice> commands, you need to create:

  • an SQL commands file containing the SQL statements you want executed
  • an SQL file to connect to the database and invoke the SQL commands file
  • a shell script using Bash (/bin/bash)

Follow these steps to create your script:

  1. Create a file of SQL commands:

    First, create a file that contains the SQL commands you want to run against your Splice Machine database. For this example, we’ll create a file named create-my-tables.sql that creates a table in the database:

    create table customers (
       CUSTOMER_ID BIGINT,
       FIRST_NAME VARCHAR(30),
       LAST_NAME VARCHAR(30)
    );
    
  2. Create an SQL file to connect to the database and invoke the commands file

    We need a separate SQL file named my_load_datascript.sqlthat connects to your database and then invokes the file of SQL commands we just created.

    The connect command in this file must run before running the file of SQL statements.

    Here we name the first SQL file, and define it to run the SQL statements file named create-my-tables.sql:

       --First connect to the database
    connect 'jdbc:splice://<regionServer>:1527/splicedb';
    
       --Next run your sql file
    run '/users/yourname/create-my-tables.sql';
    
    show tables;
    quit;
    

    If you are running Splice Machine on a cluster, connect from a machine that is NOT running an HBase RegionServer and specify the IP address of a regionServer node, e.g. 10.1.1.110. If you’re using the standalone version of Splice Machine, specify localhost instead.

  3. Create a shell script to run your SQL connect file

    We now create a shell script named load_datascript.sh to run the my_load_datascript.sql file:

    #!/bin/bash
    
    export CLASSPATH=<FULL_PATH_TO_SPLICEMACHINE_JAR_FILE>
    java -Djdbc.drivers=com.splicemachine.db.jdbc.ClientDriver -Dij.outfile=my_load_datascript.out com.splicemachine.db.tools.ij < my_load_datascript.sql
    

    The first line of this script must set the CLASSPATH to the location of your Splice Machine jar file. The second line runs the ij command, specifying its output file (my_load_datascript.out) and its SQL commands input file, which is the my_load_datascript_sql file that we created in the previous step.

  4. Make your shell script executable

    We need to make the shell script executable with the chmod shell command:

    chmod +x load_datascript.sh
    
  5. Use nohup to run the script

    The nohup utility allows you to run a script file in the background, which means that it will continue running if you log out, disconnect from a remote machine, or lose your network connection.

    nohup ./load_datascript.sh > ./load_datascript.out 2>&1 &
    

    Here’s the syntax for the nohup utility:

    nohup ./command-name.sh > ./command-name.out 2>&1 &
    

    command-name.sh

    The name of the shell script or a command name.

    command-name.out

    The name of the file to capture any output written to stdout.

    2>&1

    This causes stderr (file descriptor 2) to be written to stdout (file descriptor 1); this means that all output will be captured in command-name.out.

    &

    The nohup utility does not automatically run its command in the background, so we add the & to do