docker cassandra, cql script

cassandra.yml

# This compose file will start up Netflix OSS Tracker and load it with a days worth of data
#
# You need to export a variable before running it with your github key
#   export github_oauth=YOUR_KEY_HERE
#   export github_org=YOUR_ORG_HERE
#
version: '3'
services:
  cassandra:
    container_name: osstracker-cassandra
    image: cassandra:latest
    ports:
      - "9042:9042"
    environment:
      - "MAX_HEAP_SIZE=256M"
      - "HEAP_NEWSIZE=128M"
    restart: always
    volumes:
      - ./cassandra_data:/var/lib/cassandra

  elasticsearch:
    container_name: osstracker-elasticsearch
    image: elasticsearch:5.6.4
    environment:
      ES_JAVA_OPTS: "-Xmx256m -Xms256m"
    ports:
      - "9200:9200"
    environment:
      - "ES_JAVA_OPTS=-Xms512m -Xmx512m"
    restart: always
    ulimits:
      memlock:
        soft: -1
        hard: -1
    volumes:
      - ./es_data:/usr/share/elasticsearch/data

  kibana:
    container_name: osstracker-kibana
    image: kibana:5.6.4
    ports:
      - "5601:5601"
    restart: always

  osstracker-console:
    container_name: osstracker-console
    image: netflixoss/osstracker-console:latest
    ports:
      - "3000:3000"
    environment:
      CASS_HOST: cassandra
      CASS_PORT: 9042
      ES_HOST: elasticsearch
      ES_PORT: 9200
    restart: always
    depends_on:
      - cassandra
      - elasticsearch

  cassandra-load-keyspace:
    container_name: osstracker-cassandra-load-keyspace
    image: cassandra:latest
    depends_on:
      - cassandra
    volumes:
      - ./osstracker-ddl/osstracker.cql:/osstracker.cql
    command: /bin/bash -c "sleep 60 && echo loading cassandra keyspace && cqlsh cassandra -f /osstracker.cql"
    deploy:
      restart_policy:
        condition: on-failure
        delay: 5s
        max_attempts: 3
        window: 120s

  elasticsearch-configure-index-1:
    container_name: osstracker-elasticsearch-configure-index-1
    image: tutum/curl
    depends_on:
      - elasticsearch
    command: /bin/bash -c "sleep 60 && echo adding index && curl -X PUT elasticsearch:9200/osstracker"
    deploy:
      restart_policy:
        condition: on-failure
        delay: 5s
        max_attempts: 3
        window: 120s

  elasticsearch-configure-index-2:
    container_name: osstracker-elasticsearch-configure-index-2
    image: tutum/curl
    depends_on:
      - elasticsearch
    volumes:
      - ./osstracker-ddl/elasticsearch-mappings.json:/elasticsearch-mappings.json
    command: /bin/bash -c "sleep 80 && echo adding index && curl -X PUT -d @/elasticsearch-mappings.json elasticsearch:9200/osstracker/repo_stats/_mapping"
    deploy:
      restart_policy:
        condition: on-failure
        delay: 5s
        max_attempts: 3
        window: 120s

  osstracker-scraper-cassandra:
    container_name: osstracker-scraper-cassandra
    image:  netflixoss/osstracker-scraper:latest
    depends_on:
      - cassandra
      - elasticsearch
    environment:
      CASS_HOST: cassandra
      CASS_PORT: 9042
      ES_HOST: elasticsearch
      ES_PORT: 9200
      github_oauth: ${github_oauth}
      github_org: ${github_org}
    restart: always
    command: /bin/sh -c "sleep 100 && java -cp /osstracker-scraperapp-all.jar com.netflix.oss.tools.osstrackerscraper.app.RunGithubScraper --action updatecassandra"

  osstracker-scraper-elasticsearch:
    container_name: osstracker-scraper-elasticsearch
    image:  netflixoss/osstracker-scraper:latest
    depends_on:
      - cassandra
      - elasticsearch
    environment:
      CASS_HOST: cassandra
      CASS_PORT: 9042
      ES_HOST: elasticsearch
      ES_PORT: 9200
      github_oauth: ${github_oauth}
      github_org: ${github_org}
    restart: always
    command: /bin/sh -c "sleep 160 && java -cp /osstracker-scraperapp-all.jar com.netflix.oss.tools.osstrackerscraper.app.RunGithubScraper --action updateelasticsearch"

cql.sh

//Show toàn bộ databases
desc keyspaces
DESCRIBE keyspaces;

//Use database
use system_schema;

//Show toàn bộ tables của database
DESCRIBE tables;

//Lấy toàn bộ các rows của table types
select * from types ;

//Lấy 10 rows của table columns
select * from columns limit 10;

//Tạo database
CREATE KEYSPACE IF NOT EXISTS k1 WITH replication = {'class': 'SimpleStrategy', 'replication_factor': '1'}  AND durable_writes = true;

//Tạo bảng
CREATE TABLE person (
    id text,
    name text,
    surname text,
    PRIMARY KEY (id));
    
DESCRIBE person;

//Thêm cột
ALTER TABLE person ADD email text;

DESCRIBE person;

//Thêm bản ghi mới vào bảng person
INSERT INTO person (id, name, surname, email) VALUES ('001', 'Shalabh', 'Aggarwal', 'contact@shalabhaggarwal.com');

//Lấy toàn bộ các bản ghi từ bảng person
select * from person ;

//Cập nhật email cho bản ghi có id bằng 001
UPDATE person SET email='shalabh.agrwal@gmail.com' WHERE id='001';

//Thêm bản ghi mới vào bảng person
INSERT INTO person (id, name, surname, email) VALUES ('002', 'John', 'Doe', 'john@example.com');
INSERT INTO person (id, name, surname, email) VALUES ('003', 'Harry', 'Potter', 'harry@example.com');

//Lấy cột name, email của bản ghi có id = '001'
SELECT name, email FROM person WHERE id='001';

//Where in
select email, name from person where id  IN('001', '002' );

//CQL supports 3 kind of collections: Maps, Sets and Lists
http://cassandra.apache.org/doc/latest/cql/types.html#maps