scylldba notes

scylldba_notes.md

data-modeling

docker run --name scyllaU -d scylladb/scylla:3.0.10
docker exec -it scyllaU nodetool status
docker exec -it scyllaU cqlsh

CREATE KEYSPACE Pets_Clinic WITH replication = {'class': 'SimpleStrategy', 'replication_factor' : 1};
use Pets_Clinic; 

CREATE TABLE heartrate_v1 (
    pet_chip_id  uuid,
    date text,
    time timestamp,
    heart_rate int,
    PRIMARY KEY (pet_chip_id, time));
    
INSERT INTO heartrate_v1(pet_chip_id, date, time, heart_rate) VALUES (123e4567-e89b-12d3-a456-426655440b23, '2019-09-05', '2019-09-05 09:34:26', 100);
INSERT INTO heartrate_v1(pet_chip_id, date, time, heart_rate) VALUES (123e4567-e89b-12d3-a456-426655440b23, '2019-09-05', '2019-09-05 09:34:31', 98);
INSERT INTO heartrate_v1(pet_chip_id, date, time, heart_rate) VALUES (123e4567-e89b-12d3-a456-426655440b23, '2019-09-05', '2019-09-05 09:34:36', 101);

CREATE TABLE heartrate_v2 (
    pet_chip_id  uuid,
    date text,
    time timestamp,
    heart_rate int,
    PRIMARY KEY ((pet_chip_id,date), time));

Common Data Types and Collections

Text: a UTF8 encoded string (same as varchar) Int: a 32-bit signed integer UUID: a universal unique identifier, generated via uuid(), for example 123e4567-e89b-12d3-a456-426655440000 TIMEUUID: a version 1 UUID, generally used as a “conflict-free” timestamp, generated using now() TIMESTAMP: A timestamp (date and time) with millisecond precision, stored as a 64-bit integer. Displayed in cqlsh as yyyy-mm-dd HH:mm:ssZ

CREATE TABLE pets_v1 (
    pet_chip_id text PRIMARY KEY,
    pet_name text,
    favorite_things map<text, text> // A map of text keys, and text values
);
INSERT INTO pets_v1 (pet_chip_id, pet_name, favorite_things)
           VALUES ('123e4567-e89b-12d3-a456-426655440b23', 'Rocky', { 'food' : 'Turkey', 'toy' : 'Tennis Ball' });

CREATE TABLE pets_v2 (
    pet_name text PRIMARY KEY,
    address text,
    vaccinations set<text> 
);   
 INSERT INTO pets_v2 (pet_name, address, vaccinations)
           VALUES ('Rocky', '11 Columbia ave, New York NY', { 'Heartworm', 'Canine Hepatitis' });

CREATE TABLE pets_v3 (
    pet_name text PRIMARY KEY,
    address text,
    vaccinations list<text>
);
    
INSERT INTO pets_v3 (pet_name, address, vaccinations)
         VALUES ('Rocky', '11 Columbia ave, New York NY',  ['Heartworm', 'Canine Hepatitis', 'Heartworm']);

Time To Live (TTL)

CREATE TABLE heartrate_ttl (
    pet_chip_id  uuid,
    time timestamp,
    heart_rate int,
    PRIMARY KEY (pet_chip_id, time))
    WITH default_time_to_live = 604800;

ALTER TABLE heartrate_ttl WITH default_time_to_live = 3600;
INSERT INTO heartrate_ttl(pet_chip_id, time, heart_rate) VALUES (123e4567-e89b-12d3-a456-426655440b23, '2019-03-04 07:01:00', 100);

;And now retrieve the TTL using the TTL function:
select pet_chip_id,time, heart_rate, TTL(heart_rate), WRITETIME(heart_rate) from heartrate_ttl;

Counters

CREATE TABLE pet_type_count (pet_type  text PRIMARY KEY, pet_counter counter);
UPDATE pet_type_count SET pet_counter = pet_counter + 6 WHERE pet_type = 'dog';
SELECT * FROM pet_type_count;

User Defined Types (UDT)

CREATE TYPE phone (
    country_code int,
    number text,
);
CREATE TYPE address (
   street text,
   city text,
   zip text,
   phones map<text, frozen<phone>>
);
CREATE TABLE pets_v4 (
  name text PRIMARY KEY,
  addresses map<text, frozen<address>>
);
INSERT INTO pets_v4 (name, addresses) 
               VALUES ('Rocky', {
                  'home' : {
                   street: '1600 Pennsylvania Ave NW',
                   city: 'Washington',
                   zip: '20500',
                   phones: { 'cell' : { country_code: 1, number: '202 456-1111' },
                             'landline' : { country_code: 1, number: '202 456-1234' } }
                           },
                   'work' : {
                             street: '1600 Pennsylvania Ave NW',
                             city: 'Washington',
                             zip: '20500',
                             phones: { 'fax' : { country_code: 1, number: '202 5444' } }
                            }
               });