CockroachDB Introduction

June 8, 2017

This blog post is a combination of notes and references used in my talk on June 8th, 2017 at ProtectWise.

SQL
ACID transactions but also scalable, consistent, and HA
Raft consensus
Features both Linearizability and Serializability but without atomic clocks (Spanner)
Currently supported languages:
- C++
- Clojure
- Go
- Java
- Node.JS
- PHP
- Python
- Ruby
- Rust
Aphyr’s Review

Definitions

Linearizability - absolutely ordered events
Serializability - guarantees that the constituent reads and writes within a transaction occur as though that transaction were given exclusive access to the database for the length of its execution, guaranteeing that no transactions interfere with each other

Demo

Note: This demo is a combination of a variety of tutorials from the CockroachDB tutorials and a bit of my own work. Any errors are my own.

Retrieve the latest version of CockroachDB and unpack it:

  wget https://binaries.cockroachdb.com/cockroach-latest.darwin-10.9-amd64.tgz
  tar xfz cockroach-latest.darwin-10.9-amd64.tgz

Verify version:

  cd cockroach-latest.darwin-10.9-amd64
  ./cockroach version

Start CockroachDB and add some nodes:

  ./cockroach start --insecure --host=localhost
  ./cockroach start --insecure \
    --store=node2 \
    --host=localhost \
    --port=26258 \
    --http-port=8081 \
    --join=localhost:26257
  ./cockroach start --insecure \
    --store=node3 \
    --host=localhost \
    --port=26259 \
    --http-port=8082 \
    --join=localhost:26257

Load test data:

  ./cockroach sql --insecure

  CREATE DATABASE bank;

  CREATE TABLE bank.accounts (id INT PRIMARY KEY, balance DECIMAL);

  INSERT INTO bank.accounts VALUES (1, 1000.50);

  SELECT * FROM bank.accounts;

Connect to the other node and query:

  cockroach sql --insecure --port=26258

  SELECT * FROM bank.accounts;

Turn off a node and try running a query, delete all data and bring node3 back up:

  # kill node3 now

  cockroach sql --insecure --port=26257       # node 1

  SELECT * FROM bank.accounts;

  DELETE FROM bank.accounts;

  # bring back up node3

  cockroach sql --insecure --port=26259       # node 3

  SELECT * FROM bank.accounts;    # should be empty

Load Star Trek data:

  cockroach gen example-data startrek | cockroach sql --insecure

Examine the data using various commands:

  cockroach sql --insecure --port=26257  

  SHOW DATABASES;
  SET DATABASE=startrek;
  SHOW TABLES;

  SELECT * FROM EPISODES WHERE STARDATE > 3500;
  SELECT * FROM episodes WHERE title LIKE '%The%';
  SELECT COUNT(*) FROM QUOTES WHERE CHARACTERS LIKE '%Kirk%';

  CREATE INDEX episode_idx ON QUOTES (episode DESC);
  SHOW INDEXES FROM quotes;

Python example:

  # kill existing cluster and delete data

  # spin up nodes as before

  # install psycopg2
  pip install psycopg2                               
  # create a user roachhotel
  cockroach user set roachhotel --insecure
  # create a database
  cockroach sql --insecure -e 'CREATE DATABASE bank'
  # grant perms
  cockroach sql --insecure -e 'GRANT ALL ON DATABASE bank TO roachhotel'
  # run python script (available in "Further Reading" section)
  python populate_bank.py

  cockroach sql --insecure --port=26257  

  # do some commands based on bank data
  SET DATABASE=bank;
  SHOW TABLES;

  SELECT * FROM accounts;

  # kill node 2 and bring back up, then run
  cockroach sql --insecure --port=26258

  # verify that the data is sound
  SELECT * FROM accounts;

Justin Miller