Importing and Querying Standalone Demo Data

The standalone version of Splice Machine includes sample data that you can use to quickly try importing and querying meaningful data. This topic walks you through using the sample data.

You must install the standalone version of Splice Machine on your computer before following the steps in this tutorial. You’ll find the link to the standalone installer and instructions in our Installing Splice Machine page.

The Splice Machine installer package includes demo data that you can import into your database, so you can get used to working with your new database. We recommend that you follow the steps in this topic to import this demo data, and then run a few test queries against it to verify your installation.

About the Demo Data

The demo data included in your installer package requires about 30 MB in compressed format. Importing the demo data creates three tables, each of which contains one million records:

Table Description
T_HEADER Standard headers from a transaction system
T_DETAIL Standard detail records from a transaction system
CUSTOMERS A list of target customers

Follow the Written Instructions

Import the Data

Follow these steps to import the demo data into your Splice Machine database:

  1. Start the command line interpreter

    You can use the Splice Machine command line interpreter (CLI), or splice> prompt, to work directly with your database. If you’re using the cluster version of Splice Machine, you can access the splice> prompt by entering this shell command on any node on which it is available:

    ./sqlshell.sh
    

    If you’re using the standalone version of Splice Machine, use these steps to access the splice> prompt:

    cd <your.splicemachine-directory>
    ./bin/sqlshell.sh
    
  2. Modify the script that loads the data to use your path:

    Before running the loadall.sql script, you must change the file path used in the script.

    There are calls to SYSCS_UTIL.IMPORT_DATA near the bottom of the script. Change the file path parameter in each of these calls to use the absolute path to your Splice Machine demodata directory:

    call SYSCS_UTIL.IMPORT_DATA('SPLICE', 'T_HEADER',  null, '<yourPath>/demodata/data/theader.csv', ...;call SYSCS_UTIL.IMPORT_DATA('SPLICE', 'T_DETAIL',  null, '<yourPath>/demodata/data/tdetail.csv', ...;call SYSCS_UTIL.IMPORT_DATA('SPLICE', 'CUSTOMERS', null, '<yourPath>/demodata/data/customers.csv', ...;
    

    Make sure you use the absolute (versus relative) path. For example:

    call SYSCS_UTIL.IMPORT_DATA('SPLICE', 'T_HEADER',  null, '/Users/myName/mySplice/demodata/data/theader.csv', ...;call SYSCS_UTIL.IMPORT_DATA('SPLICE', 'T_DETAIL',  null, '/Users/myName/mySplice/demodata/data/tdetail.csv', ...;call SYSCS_UTIL.IMPORT_DATA('SPLICE', 'CUSTOMERS', null, '/Users/myName/mySplice/demodata/data/customers.csv',...;
    
  3. Run the modify script to loads the data:

    From the splice> prompt, run the file that will load the data, using single quotes around the path/filename (and remember to include the semicolon at the end):

    splice> run 'demodata/sql/loadall.sql';
    
  4. Wait for the script to finish

    If your database is not currently running, start it up and launch the command line interpreter (splice> prompt) by issuing this command in your terminal window:

    ./bin/sqlshell.sh
    

    The loading process can take several minutes: the loadall.sql file creates the schema, loads the data, and creates indexes for the tables.

    While the database is running, logging information is written to the splice.log file, which is found in the splicemachine directory.

    When you again see the splice> prompt, the demo data is ready to use. We recommend running the sample queries in the next section to get a feel for using Splice Machine and the demo data.

Run Sample Queries

After you have imported the demo data, you can use the splice> command line interpreter to run the sample queries on this page to get some experience with using Splice Machine.

You can simply copy the select command from each of the samples below to your clipboard. Then paste from the clipboard at the splice> prompt and press the Enter key or Return key to submit the query.

Example of Selecting a Subset

You can use the following query to select the customer IDs from a subset of the transaction detail (T_DETAIL) table, based on transaction date and category ID.

select customer_master_id
   from T_DETAIL d
   where TRANSACTION_DT >= DATE('2010-01-01')
      and TRANSACTION_DT <= DATE('2013-12-31')
      AND ORIGINAL_SKU_CATEGORY_ID >= 44427
      and original_sku_category_id <= 44431;

Example of Selecting With a Join

You can use the following to query a join of the T_HEADER and CUSTOMERS tables.

select t.transaction_header_key, t.transaction_dt, t.store_nbr,
       t.geocapture_flg, t.exchange_rate_percent    from T_HEADER t, CUSTOMERS c   where c.customer_master_id=t.customer_master_id   and t.customer_master_id > 14000
   and t.customer_master_id < 15000;

Troubleshooting Transaction Exceptions on MacOS

If you’re running transactions in the standalone version of Splice Machine on MacOS, you may run into an exception caused by the clock having moved backwards. This happens only rarely, and is due to the fact that OS X has its own time-maintenance daemon that can (rarely) cause the clock to move backwards, which causes a transaction exception.

When this happens, you’ll see an exception messages like the following:

SQLSTATE: XJ001Java exception: 'java.io.IOException: java.lang.IllegalStateException: Unable to obtain timestamp, clock moved backwards

To correct the problem, simply re-run the query or statement that generated the exception.