Importing and Querying Demo Data
This topic walks you through importing and querying the demo (sample) data that is packaged with the Splice Machine standalone installer.
You must install the standalone version of Splice Machine on your computer before following the steps in this tutorial.
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:
||Standard headers from a transaction system|
||Standard detail records from a transaction system|
||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:
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:
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
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_DATAnear the bottom of the script. Change the file path parameter in each of these calls to use the absolute path to your Splice Machine
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',...;
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';
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:
The loading process can take several minutes: the
loadall.sqlfile creates the schema, loads the data, and creates indexes for the tables.
While the database is running, logging information is written to the
splice.logfile, which is found in the
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
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.