SonicBase. In-memory embedded or distributed streaming sql database

Tutorial - Distributed Deploy


In this tutorial we will walk you through the process of creating a cluster, deploying to it, starting the cluster and running commands on it.

Install Java

Download The Software

Unpack Package

  • Linux/Mac: Type "tar -xzf sonicbase-<version>.tgz" in the parent directory where you want the software to go. A directory named "sonicbase" will be created.
  • Windows: Unzip the file sonicbase-<version>.zip in the parent directory where you want the software to go. A directory named "sonicbase" will be created.
  • Create a Config File

    In the sonicbase/config directory is the config file "config-1-local.yaml". Copy this file to "config-test.yaml".

    Set Paths In The Config


    Note on Windows you will need to escape the backslashes so the value can be stored in the yaml file.
    On Windows the directory may look like "c:\\db-data". On linux it may look like "/mnt/db-data". On linux and mac you also have the option of prefixing your path with "$HOME" to specify the home directory. This option is not available on Windows. The $WORKING_DIR macro should only be used on a local cluster.

    Configure Servers

    In the "config-test.yaml" file configure the servers you want to use as follows:

    Relication Factor

    Set the "replicationFactor" node to the number of replicas you want.

    Default Port

    Set the "defaultPort" node to the port that should be used for all servers.

    Default HTTP Port

    Set the "defaultHttpPort" node to the port that should be used for the http transport for all servers.

    Servers

    Set the the addresses of the servers you want to use.

    Example With 4 Servers

    replicationFactor: 2
    defaultPort: 9010
    defaultHttpPort: 8080
    servers:
    - server:
        address: 10.0.0.199
    - server:
        address: 10.0.0.132
    - server:
        address: 10.0.0.244
    - server:
        address: 10.0.0.100
    

    Start Admin Client

    Change to the sonicbase/bin directory and type "./cli" for MacOS and Linux and "./cli.bat" for cygwin and "cli.bat" for Windows. This will start the SonicBase admin client.

    Use Cluster

    In the client, type:
      use cluster test

    Passwordless SSH

    If your servers aren't local, for Linux and Mac you need to setup passwordless ssh between all your servers before taking the next steps. You also need passwordless ssh between your laptop and the first server configured in your cluster.

    PowerShell for Windows

    Deploy Cluster

    In the client, type:
      deploy cluster

    If all your servers are local, you do not need to deploy the cluster. Note that this command is not available in Windows. In Windows you need to manually copy the local install directory to each server in the location specified by "installDirectory" in the config file.

    Switch to Server for Administration

    If any of your servers are configured with a private address(no accessible via you laptop) you will need to administer your cluster from a server located in your private network. To do this, ssh to your server of choice and start the admin client. The following commands are run from the admin client on this server.

    Run Init Script

    In the client, type:
      run script init-tables.command

    This will start the cluster, create the database and create some tables. This script contains the following commands:
    
    start cluster
    create database db
    create table Employers (id VARCHAR(64), name VARCHAR(256))
    create index employerId on Employers(id)
    create table Persons (id1 BIGINT, id2 BIGINT, name VARCHAR(256), socialSecurityNumber VARCHAR(20), relatives VARCHAR(64000), restricted BOOLEAN, gender VARCHAR(8), PRIMARY KEY (id1))
    create table Memberships (personId BIGINT, personId2 BIGINT, membershipName VARCHAR(20), resortId BIGINT, PRIMARY KEY (personId, personId2))
    create table Resorts (resortId BIGINT, resortName VARCHAR(20), PRIMARY KEY (resortId))
        

    Insert Record

    In the client, type:
      insert into persons (id1, name, socialsecuritynumber) VALUES ('100', 'bob', '555-66-7777')

    Read Record

    In the client, type:
      select * from persons

    You should see the inserted record displayed in the client.

    Access From JDBC Driver


    Create and run the following class:

    
    package foo;
    
    public class Tutorial {
      public static void main(String[] args) throws ClassNotFoundException, SQLException {
        Class.forName("com.sonicbase.jdbcdriver.Driver");
        try (Connection conn = DriverManager.getConnection("jdbc:sonicbase:127.0.0.1:9010/db");
             PreparedStatement stmt = conn.prepareStatement("select * from persons");
             ResultSet rs = stmt.executeQuery()) {
          rs.next();
          System.out.println(rs.getString("name") + " " + rs.getString("socialsecuritynumber"));
        }
      }
    }

    Stop Cluster

    In the client, type:
      stop cluster