Skip to content

Instantly share code, notes, and snippets.

@leandrommendes

leandrommendes/cassandra.md

Last active June 12, 2026 01:17
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save leandrommendes/b1dcf5eb48d9188758d49df6e42c27da to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save leandrommendes/b1dcf5eb48d9188758d49df6e42c27da to your computer and use it in GitHub Desktop.
Download ZIP
Raw
cassandra.md

Cassandra

Docker Commands;

Pull

 docker pull cassandra

Run

 docker run --name Cassandra -p 9042:9042 -d cassandra

Acessando CQL

 docker exec -it Cassandra cqlsh

Comandos Cassandra

Criar e usar um Keyspace

 CREATE KEYSPACE music_space
   WITH replication = {'class': 'SimpleStrategy', 'replication_factor' : 3};
 use ks1;

Criando duas tabelas

 CREATE TABLE bands (
 	id int PRIMARY KEY,
 	name text,
 	genre text
 );

 CREATE TABLE players (
 	id int,
    name text,
    birth_year int,
 	age int,
 	principal_instrument text,
 	secondary_instrument text,
 	PRIMARY KEY  (id, name)
  ) ;

Verifique a criação

 DESCRIBE TABLE bands;
 DESCRIBE TABLE players;

Altere a estrutura e valide

 ALTER TABLE bands  ADD creation_year int;
 ALTER TABLE bands  ADD country text;
 ALTER TABLE players ADD band text;

Populando

 INSERT INTO bands (id, country, name) VALUES (101, 'Australia', 'AC\DC');
 INSERT INTO bands (id, name) VALUES (102, 'Nirvana');
 INSERT INTO bands (id, name) VALUES (103, 'Foo Fighters');
 INSERT INTO bands (id, country, name) VALUES (104, 'Reino Unido', 'Iron Maiden');
 INSERT INTO bands (id, country, name) VALUES (105, 'Brasil', 'Molejo');


 INSERT INTO players (id, name, band, birth_year, principal_instrument) VALUES (101, 'AC\DC', 'Angus McKinnon Young', 1955, 'Guitar');
 INSERT INTO players (id, name, band, birth_year, principal_instrument) VALUES (101, 'AC\DC', 'Ronald Belford Scott ', 1946, 'vocal');
 INSERT INTO players (id, name, band, principal_instrument, secondary_instrument) VALUES (104, 'Dave Grohl', 'Foo Fighters', 'Vocal', 'Guitar');
 INSERT INTO players (id, name, band, principal_instrument, secondary_instrument) VALUES (105, 'Dave Grohl', 'Nirvana', 'Drum', 'backing vocals');
 INSERT INTO players (id, name, band, principal_instrument) VALUES (106, 'Bruce Dickinson', 'Iron Maiden', 'Vocal' );

Criando Indices

 CREATE INDEX name_index ON   bands(name);
 CREATE INDEX country_index ON   bands(country);
 SELECT name, country FROM  bands WHERE name = 'Nirvana';
 SELECT name, country FROM  bands WHERE country = 'Brasil';

Update

 UPDATE bands SET country =  'U.S.A' WHERE id = 102;

Pesquisas, seguindo

 SELECT name, genre  FROM bands  WHERE id IN (104, 105, 103);
 SELECT name as nome, country as pais  FROM bands  WHERE id IN (104, 106, 103);
 
 select name from bands where id > 103 allow filtering;
 
 select count(*) from bands;

Valide tudo

 SELECT * FROM bands;
 SELECT name, band FROM players   WHERE id = 105;
 SELECT name, band FROM players   WHERE name = 'Dave Grohl';

Só pode selecionar se houver índice!

 SELECT name, band FROM players   WHERE name = 'Dave Grohl' allow filtering;

deletando

 DELETE secondary_instrument FROM players WHERE id = 105;
 DELETE FROM bands WHERE id = 105;

Entendendo a Partition Key e Clustering Key

Vamos Observer a seguinte tabela:

CREATE TABLE bands_with_players (
	music_genre text,
	band_name text,
    player_name text,
    creation_date int,
	principal_instrument text,
	secondary_instrument text,
	birth_date date,
	birth_country text,
    PRIMARY KEY ((music_genre, band_name), player_name)
);

describe bands_with_players

Imagem1

Vamos incluir alguns dados para exemplificar melhor

INSERT INTO bands_with_players (
    music_genre, 
    band_name, 
    creation_date, 
    player_name, 
    principal_instrument, 
    secondary_instrument, 
    birth_country)
    VALUES (
        'rock and roll',
        'AC\DC',
        1973,
        'Malcolm Young',
        'Guitarra',
        'Back Vocals',
        'australiano'
    );

    INSERT INTO bands_with_players (
    music_genre, 
    band_name, 
    creation_date, 
    player_name, 
    principal_instrument, 
    birth_country)
    VALUES (
        'rock and roll',
        'AC\DC',
        1973,
        'Angus McKinnon Young',
        'Guitarra',
        'australiano'
    );

        INSERT INTO bands_with_players (
    music_genre, 
    band_name, 
    creation_date, 
    player_name, 
    principal_instrument, 
    birth_country)
    VALUES (
        'rock and roll',
        'AC\DC',
        1973,
        'Bon Scott',
        'Vocal',
        'australiano'
    );

    INSERT INTO bands_with_players (
    music_genre, 
    band_name, 
    creation_date, 
    player_name, 
    principal_instrument, 
    secondary_instrument, 
    birth_country)
    VALUES (
        'post-grunge',
        'Foo Fighters',
        1994,
        'Dave Grohl',
        'Vocal',
        'Guitarra',
        'Americano'
    );

    INSERT INTO bands_with_players (
    music_genre, 
    band_name, 
    creation_date, 
    player_name, 
    principal_instrument, 
    birth_country)
    VALUES (
        'post-grunge',
        'Foo Fighters',
        1994,
        'Nate Mendel',
        'Baixo',
        'Americano'
    );

    INSERT INTO bands_with_players (
    music_genre, 
    band_name, 
    creation_date, 
    player_name, 
    principal_instrument, 
    secondary_instrument,
    birth_country)
    VALUES (
        'post-grunge',
        'Foo Fighters',
        1994,
        'Rami Jaffee',
        'Teclado',
        'acordeão',
        'Americano'
    );

    INSERT INTO bands_with_players (
    music_genre, 
    band_name, 
    creation_date, 
    player_name, 
    principal_instrument, 
    secondary_instrument,
    birth_country)
    VALUES (
        'Pagode',
        'Molejo',
        1988,
        'André Silva',
        'Vocal',
        'Surdo',
        'Brasileiro'
    );

    INSERT INTO bands_with_players (
    music_genre, 
    band_name, 
    creation_date, 
    player_name, 
    principal_instrument, 
    birth_country)
    VALUES (
        'Pagode',
        'Molejo',
        1988,
        'Anderson Leonardo',
        'Vocal',
        'Brasileiro'
    );

    INSERT INTO bands_with_players (
    music_genre, 
    band_name, 
    creation_date, 
    player_name, 
    principal_instrument, 
    secondary_instrument,
    birth_country)
    VALUES (
        'Heavy Metal',
        'Iron Maiden',
        1975,
        'Bruce Dickinson',
        'Vocal',
        'Violão',
        'Britânico'
    );

        INSERT INTO bands_with_players (
    music_genre, 
    band_name, 
    creation_date, 
    player_name, 
    principal_instrument, 
    secondary_instrument,
    birth_country)
    VALUES (
        'Heavy Metal',
        'Iron Maiden',
        1975,
        'Steve Harris',
        'Baixo',
        'Vocal',
        'Britânico'
    );

            INSERT INTO bands_with_players (
    music_genre, 
    band_name, 
    creation_date, 
    player_name, 
    principal_instrument, 
    birth_country)
    VALUES (
        'Heavy Metal',
        'Iron Maiden',
        1975,
        'Dave Murray',
        'Guitarra',
        'Britânico'
    );

            INSERT INTO bands_with_players (
    music_genre, 
    band_name, 
    creation_date, 
    player_name, 
    principal_instrument, 
    secondary_instrument,
    birth_country)
    VALUES (
        'Heavy Metal',
        'Iron Maiden',
        1975,
        'Adrian Smith',
        'Guitarra',
        'Vocal',
        'Britânico'
    );

                INSERT INTO bands_with_players (
    music_genre, 
    band_name, 
    creation_date, 
    player_name, 
    principal_instrument, 
    secondary_instrument,
    birth_country)
    VALUES (
        'Heavy Metal',
        'Iron Maiden',
        1975,
        'Nicko McBrain',
        'Bateria',
        'Percussão',
        'Britânico'
    );


                INSERT INTO bands_with_players (
    music_genre, 
    band_name, 
    creation_date, 
    player_name, 
    principal_instrument, 
    birth_country)
    VALUES (
        'Heavy Metal',
        'Iron Maiden',
        1975,
        'Janick Gers',
        'Guitarra',
        'Britânico'
    );


select * from bands_with_players;

Imagem1

Agora digamos que queremos consultar os dados das bandas não apenas com a ordem dos nomes, mas também ordenando de forma decrescente pela data de criação da banda:

CREATE TABLE bands_with_players_date (
	music_genre text,
	band_name text,
    player_name text,
    creation_date int,
	principal_instrument text,
	secondary_instrument text,
	birth_date date,
	birth_country text,
    PRIMARY KEY ((music_genre, band_name), player_name, creation_date)
    )    WITH CLUSTERING ORDER BY (player_name ASC, creation_date DESC) 
;

describe bands_with_players_date;

Então devemos novamente inserir os dados na nova Tabela:

INSERT INTO bands_with_players_date (
    music_genre, 
    band_name, 
    creation_date, 
    player_name, 
    principal_instrument, 
    secondary_instrument, 
    birth_country)
    VALUES (
        'rock and roll',
        'AC\DC',
        1973,
        'Malcolm Young',
        'Guitarra',
        'Back Vocals',
        'australiano'
    );

    INSERT INTO bands_with_players_date (
    music_genre, 
    band_name, 
    creation_date, 
    player_name, 
    principal_instrument, 
    birth_country)
    VALUES (
        'rock and roll',
        'AC\DC',
        1973,
        'Angus McKinnon Young',
        'Guitarra',
        'australiano'
    );

        INSERT INTO bands_with_players_date (
    music_genre, 
    band_name, 
    creation_date, 
    player_name, 
    principal_instrument, 
    birth_country)
    VALUES (
        'rock and roll',
        'AC\DC',
        1973,
        'Bon Scott',
        'Vocal',
        'australiano'
    );

    INSERT INTO bands_with_players_date (
    music_genre, 
    band_name, 
    creation_date, 
    player_name, 
    principal_instrument, 
    secondary_instrument, 
    birth_country)
    VALUES (
        'post-grunge',
        'Foo Fighters',
        1994,
        'Dave Grohl',
        'Vocal',
        'Guitarra',
        'Americano'
    );

    INSERT INTO bands_with_players_date (
    music_genre, 
    band_name, 
    creation_date, 
    player_name, 
    principal_instrument, 
    birth_country)
    VALUES (
        'post-grunge',
        'Foo Fighters',
        1994,
        'Nate Mendel',
        'Baixo',
        'Americano'
    );

    INSERT INTO bands_with_players_date (
    music_genre, 
    band_name, 
    creation_date, 
    player_name, 
    principal_instrument, 
    secondary_instrument,
    birth_country)
    VALUES (
        'post-grunge',
        'Foo Fighters',
        1994,
        'Rami Jaffee',
        'Teclado',
        'acordeão',
        'Americano'
    );

    INSERT INTO bands_with_players_date (
    music_genre, 
    band_name, 
    creation_date, 
    player_name, 
    principal_instrument, 
    secondary_instrument,
    birth_country)
    VALUES (
        'Pagode',
        'Molejo',
        1988,
        'André Silva',
        'Vocal',
        'Surdo',
        'Brasileiro'
    );

    INSERT INTO bands_with_players_date (
    music_genre, 
    band_name, 
    creation_date, 
    player_name, 
    principal_instrument, 
    birth_country)
    VALUES (
        'Pagode',
        'Molejo',
        1988,
        'Anderson Leonardo',
        'Vocal',
        'Brasileiro'
    );

    INSERT INTO bands_with_players_date (
    music_genre, 
    band_name, 
    creation_date, 
    player_name, 
    principal_instrument, 
    secondary_instrument,
    birth_country)
    VALUES (
        'Heavy Metal',
        'Iron Maiden',
        1975,
        'Bruce Dickinson',
        'Vocal',
        'Violão',
        'Britânico'
    );

        INSERT INTO bands_with_players_date (
    music_genre, 
    band_name, 
    creation_date, 
    player_name, 
    principal_instrument, 
    secondary_instrument,
    birth_country)
    VALUES (
        'Heavy Metal',
        'Iron Maiden',
        1975,
        'Steve Harris',
        'Baixo',
        'Vocal',
        'Britânico'
    );

            INSERT INTO bands_with_players_date (
    music_genre, 
    band_name, 
    creation_date, 
    player_name, 
    principal_instrument, 
    birth_country)
    VALUES (
        'Heavy Metal',
        'Iron Maiden',
        1975,
        'Dave Murray',
        'Guitarra',
        'Britânico'
    );

            INSERT INTO bands_with_players_date (
    music_genre, 
    band_name, 
    creation_date, 
    player_name, 
    principal_instrument, 
    secondary_instrument,
    birth_country)
    VALUES (
        'Heavy Metal',
        'Iron Maiden',
        1975,
        'Adrian Smith',
        'Guitarra',
        'Vocal',
        'Britânico'
    );

                INSERT INTO bands_with_players_date (
    music_genre, 
    band_name, 
    creation_date, 
    player_name, 
    principal_instrument, 
    secondary_instrument,
    birth_country)
    VALUES (
        'Heavy Metal',
        'Iron Maiden',
        1975,
        'Nicko McBrain',
        'Bateria',
        'Percussão',
        'Britânico'
    );


                INSERT INTO bands_with_players_date (
    music_genre, 
    band_name, 
    creation_date, 
    player_name, 
    principal_instrument, 
    birth_country)
    VALUES (
        'Heavy Metal',
        'Iron Maiden',
        1975,
        'Janick Gers',
        'Guitarra',
        'Britânico'
    );

Agora uma forma simples de lidar com novas consultas:

CREATE MATERIALIZED VIEW IF NOT EXISTS ks1.view_bands_with_players_date_desc
AS SELECT     
    music_genre, 
    band_name, 
    creation_date, 
    player_name, 
    principal_instrument, 
    birth_country
FROM ks1.bands_with_players_date
WHERE music_genre IS NOT NULL 
AND band_name IS NOT NULL
AND player_name IS NOT NULL
AND creation_date IS NOT NULL
PRIMARY KEY ( (music_genre, band_name), player_name, creation_date )
WITH CLUSTERING ORDER BY (player_name DESC, creation_date ASC);

select * from view_bands_with_players_date_desc;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment