Getting Started With the splice> Command Line Interface

The splice> command line interpreter is an easy way to interact with your Splice Machine database. This topic introduces splice> and some of the more common commands you’ll use

The command line interpreter, as documented here, is not available in our Cloud-Managed Database-as-Service product.

You can complete this tutorial by watching a short video or by following the written version.

Watch the Video

The following video shows you how to launch and start using the splice> command line interpreter to connect to and interact with your database.

Follow the Written Version

This topic walks you through getting started with the splice> command line interpreter, in these sections:

NOTE: Although we focus here on executing command lines with the splice>, you can also use the command line interface to directly execute any SQL statement, including the DDL and DML statements that we introduce in the last section of this topic.

Starting splice>

To launch the splice> command line interpreter, follow these steps:

  1. Open a terminal window
  2. Navigate to your splicemachine directory

    cd ~/splicemachine    #Use the correct path for your Splice Machine installation
    
  3. Start splice>

    ./bin/sqlshell.sh
    

    The full path to this script on Splice Machine standalone installations is ./splicemachine/bin/sqlshell.sh.

  4. The command line interpreter starts:

    Running Splice Machine SQL ShellFor help: "Splice> help;"SPLICE** = current connectionsplice>
    

    SPLICE is the name of the default connection, which becomes the current connection when you start the interpreter.

Restarting splice>

If you are running the standalone version of Splice Machine and your computer goes to sleep, any live database connections are lost. You’ll need to restart Splice Machine by following these steps:

Step Command
Exit splice> splice> quit; (exit;)
Stop Splice Machine processes $ ./bin/stop-splice.sh
Restart Splice Machine processes $ ./bin/start-splice.sh
Restart splice> $ ./bin/sqlshell.sh

Basic Syntax Rules

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;

In most cases, the commands you enter are not case sensitive; you can Certain identifiers and keywords are case sensitive: this means that these commands are all equivalent:

splice> show connections;
splice> SHOW CONNECTIONS;
splice> Show Connections;

The Command Line Syntax topic contains a complete syntax reference for splice>.

Connecting to a Database

When you start splice>, you are automatically connected to your default database. You can connect to other databases with the connect command:

connect 'jdbc:splice://srv55:1527/splicedb;user=YourUserId;password=YourPassword' AS DEMO;

Anatomy of a Connection String

Here’s how to breakdown the connection strings we use to connect to a database:

Examples Component Comments
jdbc:splice: Connection driver name  

srv55:1527

localhost:1527

Server Name:Port splice> listens on port 1527
splicedb Database name The name of the database you're connecting to on the server.
user=YourUserId;password=YourPassword Connection parameters Any required connection parameters, such as userId and password.
AS DEMO Optional connection identifier

The name that you want associated with this connection.

If you don't supply a name, Splice Machine assigns one for your; for example: CONNECTION1.

Displaying Database Objects

We’ll first explore the show command, which is available to view numerous object types in your database, including: connections, schemas, tables, indexes, views, procedures, and others.

Displaying and Changing Connections

You can connection to multiple database in Splice Machine; one connection is designated as the current database; this is the database with which you’re currently working.

To view your current connections, use the  show connections command:

splice> show connections;
DEMO     - jdbc:splice://srv55:1527/splicedb
SPLICE*  - jdbc:splice://localhost:1527/splicedb
* = current connection

You can use the  set connection command to modify the current connection:

splice> SET CONNECTION DEMO;
splice> show connections;
DEMO*    - jdbc:splice://srv55:1527/splicedb
SPLICE   - jdbc:splice://localhost:1527/splicedb
* = current connection

You can use the  disconnect command to close a connection:

splice> Disconnect DEMO;
splice> show Connections;
SPLICE  - jdbc:splice://localhost:1527/splicedb
No current connection

Notice that there’s now no current connection because we’ve disconnected the connection named DEMO, which had been the current connection. We can easily resolve this by connecting to a named connection:

splice> connect splice;
splice> show connections;
SPLICE*  - jdbc:splice://localhost:1527/splicedb
* = current connection

Finally, to disconnect from all connections:

splice> disconnect all;
splice> show connections;
No connections available

Displaying Schemas

Use the show schemas command to display the schemas that are defined in your currently connected database:

splice> show schemas;
TABLE_SCHEM
------------------------------
NULLID
SPLICE
SQLJ
SYS
SYSCAT
SYSCS_DIAG
SYSCS_UTIL
SYSFUN
SYSIBM
SYSPROC
SYSSTAT
11 rows selected

The current schema is used as the default value when you issue commands that optionally take a schema name as a parameter. For example, you can optionally specify a schema name in the show tables command; if you don’t include a schema name, Splice Machine assumes the current schema name.

To display the current schema name, use the built-in  current schema function:

splice> values(current schema);
1
------------------------------
SPLICE
1 row selected

To change which schema is current, use the SQL set schema statement:

splice> set schema SYS;
0 rows inserted/updated/deleted
splice> values(current schema);
1
------------------------------
SYS

1 row selected

Displaying Tables

The  show tables command displays a list of all tables in all of the schemas in your database:

splice> SHOW TABLES;
TABLE_SCHEM  |TABLE_NAME            |CONGLOM_ID|REMARKS
--------------------------------------------------------
SYS          |SYSALIASES            |256       |
SYS          |SYSBACKUP             |992       |
SYS          |SYSBACKUPFILESET      |1008      |
SYS          |SYSBACKUPITEMS        |1104      |
SYS          |SYSBACKUPJOBS         |1216      |
SYS          |SYSCHECKS             |336       |
SYS          |SYSCOLPERMS           |608       |
SYS          |SYSCOLUMNS            |80        |
SYS          |SYSCOLUMNSTATS        |1264      |
SYS          |SYSCONGLOMERATES      |48        |
SYS          |SYSCONSTRAINTS        |304       |
SYS          |SYSDEPENDS            |368       |
SYS          |SYSFILES              |288       |
SYS          |SYSFOREIGNKEYS        |272       |
SYS          |SYSKEYS               |240       |
SYS          |SYSPERMS              |912       |
SYS          |SYSPHYSICALSTATS      |1280      |
SYS          |SYSPRIMARYKEYS        |320       |
SYS          |SYSROLES              |816       |
SYS          |SYSROUTINEPERMS       |656       |
SYS          |SYSSCHEMAPERMS        |1328      |
SYS          |SYSSCHEMAS            |32        |
SYS          |SYSSEQUENCES          |864       |
SYS          |SYSSTATEMENTS         |384       |
SYS          |SYSTABLEPERMS         |592       |
SYS          |SYSTABLES             |64        |
SYS          |SYSTABLESTATS         |1296      |
SYS          |SYSTRIGGERS           |576       |
SYS          |SYSUSERS              |928       |
SYS          |SYSVIEWS              |352       |
SYSIBM       |SYSDUMMY1             |1312      |
SPLICE       |CUSTOMERS             |1568      |
SPLICE       |T_DETAIL              |1552      |
SPLICE       |T_HEADER              |1536      |

34 rows selected

To display the tables in a specific schema (named SPLICE):

splice> show tables in SPLICE;
TABLE_SCHEM  |TABLE_NAME            |CONGLOM_ID|REMARKS
--------------------------------------------------------
SPLICE       |CUSTOMERS             |1568      |
SPLICE       |T_DETAIL              |1552      |
SPLICE       |T_HEADER              |1536      |

3 rows selected

To examine the structure of a specific table, use the DESCRIBE command:

splice> describe T_DETAIL;
COLUMN_NAME                 |TYPE_NAME|DEC |NUM |COLUM |COLUMN_DEF|CHAR_OCTE |IS_NULL
--------------------------------------------------------------------------------------------------
TRANSACTION_HEADER_KEY      |BIGINT   |0   |10  |19    |NULL      |NULL      |NO
TRANSACTION_DETAIL_KEY      |BIGINT   |0   |10  |19    |NULL      |NULL      |NO
CUSTOMER_MASTER_ID          |BIGINT   |0   |10  |19    |NULL      |NULL      |YES
TRANSACTION_DT              |DATE     |0   |10  |10    |NULL      |NULL      |NO
ORIGINAL_SKU_CATEGORY_ID    |INTEGER  |0   |10  |10    |NULL      |NULL      |YES

5 rows selected

Displaying Indexes

You can display all of the indexes in a schema:

splice> show indexes in SPLICE;
TABLE_NAME    |INDEX_NAME    |COLUMN_NAME         |ORDINAL&|NON_UNIQUE|TYPE |ASC&|CONGLOM_NO
---------------------------------------------------------------------------------------------
T_DETAIL      |TDIDX1        |ORIGINAL_SKU_CATEGO&|1       |true      |BTREE|A   |1585
T_DETAIL      |TDIDX1        |TRANSACTION_DT      |2       |true      |BTREE|A   |1585
T_DETAIL      |TDIDX1        |CUSTOMER_MASTER_ID  |3       |true      |BTREE|A   |1585
T_HEADER      |THIDX2        |CUSTOMER_MASTER_ID  |1       |true      |BTREE|A   |1601
T_HEADER      |THIDX2        |TRANSACTION_DT      |2       |true      |BTREE|A   |1601

5 rows selected

Or you can display the indexes defined for a specific table:

splice> show indexes FROM T_DETAIL;
TABLE_NAME    |INDEX_NAME    |COLUMN_NAME         |ORDINAL&|NON_UNIQUE|TYPE |ASC&|CONGLOM_NO
---------------------------------------------------------------------------------------------
T_DETAIL      |TDIDX1        |ORIGINAL_SKU_CATEGO&|1       |true      |BTREE|A   |1585
T_DETAIL      |TDIDX1        |TRANSACTION_DT      |2       |true      |BTREE|A   |1585
T_DETAIL      |TDIDX1        |CUSTOMER_MASTER_ID  |3       |true      |BTREE|A   |1585

3 rows selected

Note that we use IN to display the indexes in a schema, and FROM to display the indexes in a table.

Displaying Views

Similarly to indexes, you can use the  show views command to display all of the indexes in your database or in a schema:

splice> show views;
TABLE_SCHEM    |TABLE_NAME            |CONGLOM_ID|REMARKS
-----------------------------------------------------------
SYS            |SYSCOLUMNSTATISTICS   |NULL      |
SYS            |SYSTABLESTATISTICS    |NULL      |

2 rows selected
splice> show views in sys;TABLE_SCHEM    |TABLE_NAME            |CONGLOM_ID|REMARKS
-----------------------------------------------------------
SYS            |SYSCOLUMNSTATISTICS   |NULL      |
SYS            |SYSTABLESTATISTICS    |NULL      |

2 rows selected

Displaying Stored Procedures and Functions

You can create user-defined database functions that can be evaluated in SQL statements; these functions can be invoked where most other built-in functions are allowed, including within SQL expressions and SELECT statement. Functions must be deterministic, and cannot be used to make changes to the database. You can use the  show functions command to display which functions are defined in your database or schema:

splice> show functions in splice;
FUNCTION_SCHEM|FUNCTION_NAME               |REMARKS
-------------------------------------------------------------------------
SPLICE        |TO_DEGREES                  |java.lang.Math.toDegrees
1 row selected

You can also group a set of SQL commands together with variable and logic into a stored procedure, which is a subroutine that is stored in your database’s data dictionary. Unlike user-defined functions, a stored procedure is not an expression and can only be invoked using the CALL statement. Stored procedures allow you to modify the database and return Result Sets or nothing at all. You can use the  show procedures command to display which functions are defined in your database or schema:

splice> show procedures in SQLJ;
PROCEDURE_SCHEM     |PROCEDURE_NAME    |REMARKS
-------------------------------------------------------------------------------------------------
SQLJ                |INSTALL_JAR       |com.splicemachine.db.catalog.SystemProcedures.INSTALL_JAR
SQLJ                |REMOVE_JAR        |com.splicemachine.db.catalog.SystemProcedures.REMOVE_JAR
SQLJ                |REPLACE_JAR       |com.splicemachine.db.catalog.SystemProcedures.REPLACE_JAR

3 rows selected

Basic DDL and DML Statements

This section introduces the basics of running SQL Data Definition Language (DDL) and Data Manipulation Language (DML) statements from splice>.

See the DML Statements sections in our SQL Reference Manual for more information.

CREATE Statements

SQL uses CREATE statements to create objects such as tables. For example:

splice> CREATE schema MySchema1;
0 rows inserted/updated/deleted
splice> create Schema mySchema2;
0 rows inserted/updated/deleted
splice> show schemas;
TABLE_SCHEM
------------------------------
MYSCHEMA1MYSCHEMA2NULLID
SPLICE
SQLJ
SYS
SYSCAT
SYSCS_DIAG
SYSCS_UTIL
SYSFUN
SYSIBM
SYSPROC
SYSSTAT
13 rows selected
splice> SET SCHEMA MySchema1;
0 rows inserted/updated/deleted
splice> CREATE TABLE myTable ( myNum int, myName VARCHAR(64) );
0 rows inserted/updated/deleted
splice> CREATE TABLE Players(
    ID           SMALLINT NOT NULL PRIMARY KEY,
    Team         VARCHAR(64) NOT NULL,
    Name         VARCHAR(64) NOT NULL,
    Position     CHAR(2),
    DisplayName  VARCHAR(24),
    BirthDate    DATE
    );
0 rows inserted/updated/deleted
splice> SHOW TABLES IN MySchema1;
TABLE_SCHEM  |TABLE_NAME            |CONGLOM_ID|REMARKS
--------------------------------------------------------
MYSCHEMA1    |MYTABLE               |1616      |
MYSCHEMA1    |PLAYERS               |1632      |
2 rows selected

splice> describe Players;
COLUMN_NAME   |TYPE_NAME|DEC&|NUM&|COLUM&|COLUMN_DEF|CHAR_OCTE&|IS_NULL&
-------------------------------------------------------------------
ID            |SMALLINT |0   |10  |5     |NULL      |NULL      |NO
TEAM          |VARCHAR  |NULL|NULL|64    |NULL      |128       |NO
NAME          |VARCHAR  |NULL|NULL|64    |NULL      |128       |NO
POSITION      |CHAR     |NULL|NULL|2     |NULL      |4         |YES
DISPLAYNAME   |VARCHAR  |NULL|NULL|24    |NULL      |48        |YES
BIRTHDATE     |DATE     |0   |10  |10    |NULL      |NULL      |YES

6 rows selected

See the CREATE Statements section in our SQL Reference Manual for more information.

DROP Statements

SQL uses DROP statements to delete objects such as tables. For example:

splice> DROP schema MySchema2 restrict;0 rows inserted/updated/deleted

You must include the keyword restrict when dropping a schema; this enforces the rule that the schema cannot be deleted from the database if there are any objects defined in the schema.

splice> show schemas;
TABLE_SCHEM
------------------------------
MYSCHEMA1
MYSCHEMA2
NULLID
SPLICE
SQLJ
SYS
SYSCAT
SYSCS_DIAG
SYSCS_UTIL
SYSFUN
SYSIBM
SYSPROC
SYSSTAT
12 rows selected

splice> DROP TABLE myTable;
0 rows inserted/updated/deleted
splice> SHOW TABLES IN MySchema1;
TABLE_SCHEM  |TABLE_NAME            |CONGLOM_ID|REMARKS
--------------------------------------------------------
MYSCHEMA1    |PLAYERS               |1632      |1 row selected

See the DROP Statements section in our SQL Reference Manual for more information.

Inserting Data

Once you’ve created a table, you can use INSERT statements to insert records into that table; for example:

splice> INSERT INTO Players
   VALUES( 99, 'Giants', 'Joe Bojangles', 'C', 'Little Joey', '07/11/1991');
1 row inserted/updated/deleted

splice> INSERT INTO Players
   VALUES (99, 'Giants', 'Joe Bojangles', 'C', 'Little Joey', '07/11/1991'),
          (73, 'Giants', 'Lester Johns', 'P', 'Big John', '06/09/1984'),
          (27, 'Cards', 'Earl Hastings', 'OF', 'Speedy Earl', '04/22/1982');
3 rows inserted/updated/deleted

Selecting and Displaying Data

Now that you have a bit of data in your table, you can use SELECT statements to select specific records or portions of records. This section contains several simple examples of selecting data from the Players table we created in the previous section.

You can select a single column from all of the records in a table; for example:

splice> select NAME from Players;
NAME
----------------------------------------------------------------
Earl Hastings
Lester Johns
Joe Bojangles

3 rows selected

You can select all columns from all of the records in a table; for example:

splice> select * from Players;
ID    |TEAM   |NAME           |POS&|DISPLAYNAME    |BIRTHDATE
---------------------------------------------------------------
27    |Cards  |Earl Hastings  |OF  |Speedy Earl    |1982-04-22
73    |Giants |Lester Johns   |P   |Big John       |1984-06-09
99    |Giants |Joe Bojangles  |C   |Little Joey    |1991-07-11

3 rows selected

You can also qualify which records to select with a WHERE clause; for example:

splice> select * from Players WHERE Team='Cards';
ID    |TEAM   |NAME           |POS&|DISPLAYNAME    |BIRTHDATE
---------------------------------------------------------------
27    |Cards  |Earl Hastings  |OF  |Speedy Earl    |1982-04-22
1 row selected

You can easily count the records in a table by using the SELECT statement; for example:

splice> select count(*) from Players;
-----------------------
31 rows selected

See Also