Getting Started with Splice Machine

This topic helps you to quickly become productive with your new Splice Machine database by introducing you to some of the major features you’ll be using.

The information on this page assumes that you already have Splice Machine installed; if not, please follow the installation instructions for your platform.

To get started with Splice Machine, we recommend following these simple steps:

  1. Use our command line interpreter (splice>) to talk directly to your database.
  2. Familiarize yourself with Splice SQL
  3. Investigate our Developer Topics and Tutorials
  4. Explore our Developer Tutorials
  5. Learn About Product-Specific Features
  6. Optimize Your Use of This Documentation

1. Use our Command Line Interpreter

You can invoke the splice> command line interpreter in your terminal window by navigating to the splicemachine directory on your machine and issuing this command:

./sqlshell.sh

If you’re connected to a running instance of Splice Machine, you’ll see something like this (please ignore any WARNING messages):

Running Splice Machine SQL shell
For help: "splice> help;"
[INFO] Scanning for projects...
[WARNING]
[WARNING] Some problems were encountered while building the effective model for com.splicemachine:splice_machine:jar:2.5.0.1823-SNAPSHOT
[WARNING] 'dependencies.dependency.(groupId:artifactId:type:classifier)' must be unique: commons-codec:commons-codec:jar -> duplicate declaration of version 1.10 @ com.splicemachine:splice_machine:[unknown-version], /Users/garyh/git/Splice/spliceengine/splice_machine/pom.xml, line 146, column 21
[WARNING]
[WARNING] It is highly recommended to fix these problems because they threaten the stability of your build.
[WARNING]
[WARNING] For this reason, future Maven versions might no longer support building such malformed projects.
[WARNING]
[INFO]
[INFO] ------------------------------------------------------------------------
[INFO] Building splice_machine 2.5.0.1823-SNAPSHOT
[INFO] ------------------------------------------------------------------------
[INFO]
[INFO] --- exec-maven-plugin:1.4.0:java (default-cli) @ splice_machine ---
SPLICE* - 	jdbc:splice://localhost:1527/splicedb
* = current connection

Run a SHOW Command

As a first command, try running a simple SHOW TABLES command; since you’ve not yet created any of your own tables, we’ll display the tables defined in the SYS (system) schema:

splice> SHOW TABLES in SYS;
TABLE_SCHEM         |TABLE_NAME                                        |CONGLOM_ID|REMARKS
-------------------------------------------------------------------------------------------
SYS                 |SYSALIASES                                        |352       |
SYS                 |SYSBACKUP                                         |928       |
SYS                 |SYSBACKUPFILESET                                  |976       |
SYS                 |SYSBACKUPITEMS                                    |1168      |
SYS                 |SYSBACKUPJOBS                                     |1216      |
SYS                 |SYSCHECKS                                         |384       |
SYS                 |SYSCOLPERMS                                       |640       |
SYS                 |SYSCOLUMNS                                        |80        |
SYS                 |SYSCOLUMNSTATS                                    |1232      |
SYS                 |SYSCONGLOMERATES                                  |48        |
SYS                 |SYSCONSTRAINTS                                    |288       |
SYS                 |SYSDEPENDS                                        |256       |
SYS                 |SYSFILES                                          |240       |
SYS                 |SYSFOREIGNKEYS                                    |368       |
SYS                 |SYSKEYS                                           |272       |
SYS                 |SYSPERMS                                          |880       |
SYS                 |SYSPHYSICALSTATS                                  |1248      |
SYS                 |SYSPRIMARYKEYS                                    |320       |
SYS                 |SYSROLES                                          |800       |
SYS                 |SYSROUTINEPERMS                                   |720       |
SYS                 |SYSSCHEMAPERMS                                    |1328      |
SYS                 |SYSSCHEMAS                                        |32        |
SYS                 |SYSSEQUENCES                                      |864       |
SYS                 |SYSSNAPSHOTS                                      |1360      |
SYS                 |SYSSTATEMENTS                                     |336       |
SYS                 |SYSTABLEPERMS                                     |624       |
SYS                 |SYSTABLES                                         |64        |
SYS                 |SYSTABLESTATS                                     |1264      |
SYS                 |SYSTOKENS                                         |1408      |
SYS                 |SYSTRIGGERS                                       |528       |
SYS                 |SYSUSERS                                          |896       |
SYS                 |SYSVIEWS                                          |304       |

32 rows selected

Run a DDL Command

Now let’s run a DDL (Data Definition Language) command to create a table:

splice> CREATE TABLE myFirstTbl (id SMALLINT, name VARCHAR(32), location VARCHAR(32));
0 rows inserted/updated/deleted

This table gets created in the default schema, SPLICE. We can use the SHOW command to verify that our table has been created:

splice> SHOW TABLES IN SPLICE;
TABLE_SCHEM         |TABLE_NAME                                        |CONGLOM_ID|REMARKS
------------------------------------------------------------------------------------------
SPLICE              |MYFIRSTTBL                                        |1616      |

1 row selected

Run a DML Command

Let’s run a DML (Data Manipulation Language) command to insert some data into the table:

splice> INSERT INTO myFirstTbl VALUES ( 1, 'John Doe', 'San Francisco, CA, USA');
0 rows inserted/updated/deleted

And then examine our table with a SELECT statement:

splice> SELECT * FROM myFirstTbl;
ID    |NAME                            |LOCATION
------------------------------------------------------------------------
1     |John Doe                        |San Francisco, CA, USA USA

1 row selected

Key Things to Know About the Command Line Interpreter

A few key things to remember when using the command line:

  • Our Command Line Reference includes complete information about available commands and syntax.
  • Installing the rlWrap command line wrapper adds editing, history, and autocompletion to splice> and is highly recommended. See RlWrap Commands Synopsis for more information.
  • In general, commands are case-insensitive.
  • You must terminate each command with a semicolon (;) character.
  • 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;
    

2. Familiarize Yourself with Splice SQL

Our SQL Reference Manual specifies all of the SQL commands available for use with splice> and via programmatic connections. The manual is organized into syntactic categories, including:

Of particular interest is the Built-in System Procedures and Functions section, which describes procedures and functions developed by Splice Machine that add significant functionality to your database. There are a large number of these procedures, which you can use for a wide variety of purposes. You invoke these built-in system procedures with the CALL statement. For example, calling this procedure performs an incremental backup:

splice> CALL SYSCS_UTIL.SYSCS_BACKUP_DATABASE( 'hdfs:///home/backup', 'incremental' );
Statement executed.

3. Investigate our Developer Topics

Our Developer Topics Guide contains a collection of topics that are useful to database developers, most of which involve using basic SQL functionality and/or built-in system procedures. These topics are organized into multiple sections, including these:

Section Description
Fundamentals Contains a number of topics that show you how to use fundamental development tools and techniques with Splice Machine, including topics such as running transactions, using triggers, foreign keys, virtual tables, and external tables.
Functions and Stored Procedures Describes how to create, modify, and store functions and stored procedures.
Tuning and Debugging Helps you to optimize and debug your database queries.
Accessing Data in the Cloud Shows you how to access and upload data on Amazon AWS S3.
Connecting with JDBC Provides examples of using various programming languages to connect with your database, including Java, Ruby, Python, and Scala.
Connecting with ODBC Walks you through installing and using our ODBC driver.
Spark and Splice Machine Describes how to use Apache Spark with Splice Machine, including how to use our Native Spark adapter.
Splice\*Plus (PL/SQL) Describes Splice*Plus, our implementation of PL/SQL.

4. Explore our Developer Tutorials

Our Developer Tutorials includes deep dives into more complex developer topics, such as:

Section Description
Securing Your Database Includes sections on securing your JDBC connections, authentication methods, and authorization of users and roles.
Importing Data Compares and walks you through the different ways to import data into your database, and includes detailed examples.
Using the Database Console Introduces the *Splice Machine Database Console,* which is a browser-based tool that you can use to monitor database queries on your cluster in real time.
Connecting via JDBC and Connecting via ODBC Include examples in different programming languages that show you how to programmatically connect to your database.
Connecting BI Tools Walks you through connecting specific *Business Intelligence* tools to your database.

5. Learn About Product-Specific Features

If you’re using our On-Premise product, our On-Premise Database Guide contains product-specific sections, including Requirements, Installation, and Administration.

If you’re using our Database-as-a-Service product, you’ll find our Database-as-Service Guide invaluable for finding your way around the user interface and learning about using the integrated Apache Zeppelin notebooks interface.

6. Optimize Your Use of This Documentation

If you need any help with getting the most out of this documentation system, please see the Using our Documentation topic.