Cassandra for Oracle DBA Part 8: Data Dictionary Overview

cqlsh> select * from system.sstable_activity;

 keyspace_name | columnfamily_name | generation | rate_120m | rate_15m

---------------+-------------------+------------+-----------+----------

 PortfolioDemo |         StockHist |          2 |         0 |        0

 PortfolioDemo |        Portfolios |          5 |         0 |        0

 PortfolioDemo |            Stocks |          5 |         0 |        0

 PortfolioDemo |         StockHist |          3 |         0 |        0

 PortfolioDemo |         StockHist |          1 |         0 |        0

(5 rows)

cqlsh>

cqlsh> select * from system.compaction_history;

 id                                   | bytes_in | bytes_out | columnfamily_name       | compacted_at             | keyspace_name | rows_merged

--------------------------------------+----------+-----------+-------------------------+--------------------------+---------------+--------------

 9490d1b0-470b-11e6-a73e-e7c99fc0dbe2 |    57105 |     56678 |                  Stocks | 2016-07-11 12:02:54+1000 | PortfolioDemo |    {1: 2842}

 6cf4a840-4650-11e6-b360-e7c99fc0dbe2 |      746 |       263 |        schema_keyspaces | 2016-07-10 13:43:12+1000 |        system | {1: 2, 4: 1}

 6ce0f930-4650-11e6-b360-e7c99fc0dbe2 |     8819 |      6806 |   schema_columnfamilies | 2016-07-10 13:43:11+1000 |        system | {1: 1, 2: 2}

 6ade7270-4650-11e6-b360-e7c99fc0dbe2 |      645 |       263 |        schema_keyspaces | 2016-07-10 13:43:08+1000 |        system | {1: 2, 3: 1}

 c219e750-4659-11e6-a73e-e7c99fc0dbe2 |    15917 |      6797 |   schema_columnfamilies | 2016-07-10 14:50:00+1000 |        system | {2: 2, 4: 1}

 6a9868c0-4650-11e6-b360-e7c99fc0dbe2 |      718 |       549 |                   local | 2016-07-10 13:43:08+1000 |        system |       {4: 1}

 6ce20aa0-4650-11e6-b360-e7c99fc0dbe2 |    12924 |     11444 |          schema_columns | 2016-07-10 13:43:11+1000 |        system | {1: 1, 2: 2}

 c2224bc0-4659-11e6-a73e-e7c99fc0dbe2 |    25270 |     11533 |          schema_columns | 2016-07-10 14:50:00+1000 |        system | {2: 2, 4: 1}

 c7ba1e40-4709-11e6-a73e-e7c99fc0dbe2 |     1314 |       536 |                   peers | 2016-07-11 11:50:01+1000 |        system | {2: 2, 3: 2}

 56681bc0-4709-11e6-a73e-e7c99fc0dbe2 |      730 |       312 |        schema_keyspaces | 2016-07-11 11:46:51+1000 |        system | {1: 2, 2: 2}

 94be7160-470b-11e6-a73e-e7c99fc0dbe2 |      436 |        57 | compactions_in_progress | 2016-07-11 12:02:54+1000 |        system |       {2: 2}

 94bb3d10-470b-11e6-a73e-e7c99fc0dbe2 |   577360 |    576097 |              Portfolios | 2016-07-11 12:02:54+1000 | PortfolioDemo |   {1: 10000}

 c83086c0-4709-11e6-a73e-e7c99fc0dbe2 |      780 |       543 |                   local | 2016-07-11 11:50:02+1000 |        system |       {4: 1}

 6157ad10-4651-11e6-a73e-e7c99fc0dbe2 |     1095 |       534 |                   local | 2016-07-10 13:50:02+1000 |        system |       {4: 1}

(14 rows)

cqlsh>

Getting Metadata of a table & Schema

cqlsh> describe table PortfolioDemo.Portfolios;

CREATE TABLE "Portfolios" (

  key bigint,

  column1 text,

  value double,

  PRIMARY KEY ((key), column1)

) WITH COMPACT STORAGE AND

  bloom_filter_fp_chance=0.010000 AND

  caching='KEYS_ONLY' AND

  comment='' AND

  dclocal_read_repair_chance=0.100000 AND

  gc_grace_seconds=60 AND

  index_interval=128 AND

  read_repair_chance=1.000000 AND

  replicate_on_write='true' AND

  populate_io_cache_on_flush='false' AND

  default_time_to_live=0 AND

  speculative_retry='NONE' AND

  memtable_flush_period_in_ms=0 AND

  compaction={'class': 'SizeTieredCompactionStrategy'} AND

  compression={'sstable_compression': 'LZ4Compressor'};

cqlsh> describe keyspace PortfolioDemo;

CREATE KEYSPACE "PortfolioDemo" WITH replication = {

  'class': 'SimpleStrategy',

  'replication_factor': '2'

};

Creating Table

USE "PortfolioDemo";

CREATE TABLE "HistLoss" (

  key text,

  column1 text,

  value text,

  PRIMARY KEY ((key), column1)

) WITH COMPACT STORAGE AND

  bloom_filter_fp_chance=0.010000 AND

  caching='KEYS_ONLY' AND

  comment='' AND

  dclocal_read_repair_chance=0.100000 AND

  gc_grace_seconds=60 AND

  index_interval=128 AND

  read_repair_chance=1.000000 AND

  replicate_on_write='true' AND

  populate_io_cache_on_flush='false' AND

  default_time_to_live=0 AND

  speculative_retry='NONE' AND

  memtable_flush_period_in_ms=0 AND

  compaction={'class': 'SizeTieredCompactionStrategy'} AND

  compression={'sstable_compression': 'LZ4Compressor'};

Creating Table

CREATE TABLE "Portfolios" (

  key bigint,

  column1 text,

  value double,

  PRIMARY KEY ((key), column1)

) WITH COMPACT STORAGE AND

  bloom_filter_fp_chance=0.010000 AND

  caching='KEYS_ONLY' AND

==================================================

Write Time like OraROWSCN

==================================================

Cannot use the primary / part key

cqlsh:PortfolioDemo> SELECT WRITETIME (value) from "Portfolios" where key=1535;

 writetime(value)

------------------

    1468200969975

    1468200969975

    1468200969975

    1468200969975

    1468200969975

    1468200969975

(6 rows)

===============================================

Finding Users & Roles & Privs

===============================================

cqlsh> select * from system_auth.users;

 name      | super

-----------+-------

  spillman | False

     boone | False

 cassandra |  True

     akers |  True

(4 rows)

==============================================

Knowing about Peers from each node

==============================================

root@wash-i-16ca26c8-prod ~/.ccm/repository/4.5.2/bin $ ccm node1 cqlsh

cqlsh> select * from system.peers;

 peer      | data_center | host_id                              | preferred_ip | rack  | release_version | rpc_address | schema_version                       | tokens                   | workload

-----------+-------------+--------------------------------------+--------------+-------+-----------------+-------------+--------------------------------------+--------------------------+-----------

 127.0.0.3 |   Cassandra | 023a4514-3a74-42eb-be49-feaa69bf098c |         null | rack1 |       2.0.10.71 |   127.0.0.3 | 97df5fdc-277b-339e-8aab-cb36317c1280 |  {'3074457345618258602'} | Cassandra

 127.0.0.2 |   Cassandra | ab247945-5989-48f3-82b3-8f44a3aaa375 |         null | rack1 |       2.0.10.71 |   127.0.0.2 | 97df5fdc-277b-339e-8aab-cb36317c1280 | {'-3074457345618258603'} | Cassandra

(2 rows)

cqlsh> exit

root@wash-i-16ca26c8-prod ~/.ccm/repository/4.5.2/bin $ ccm node2 cqlsh

Connected to geek_cluster at 127.0.0.2:9160.

[cqlsh 4.1.1 | Cassandra 2.0.10.71 | DSE 4.5.2 | CQL spec 3.1.1 | Thrift protocol 19.39.0]

Use HELP for help.

cqlsh> select * from system.peers;

 peer      | data_center | host_id                              | preferred_ip | rack  | release_version | rpc_address | schema_version                       | tokens                   | workload

-----------+-------------+--------------------------------------+--------------+-------+-----------------+-------------+--------------------------------------+--------------------------+-----------

 127.0.0.3 |   Cassandra | 023a4514-3a74-42eb-be49-feaa69bf098c |         null | rack1 |       2.0.10.71 |   127.0.0.3 | 97df5fdc-277b-339e-8aab-cb36317c1280 |  {'3074457345618258602'} | Cassandra

 127.0.0.1 |   Cassandra | 1dc82d65-f88d-4b79-9c1b-dc5aa2a55534 |         null | rack1 |       2.0.10.71 |   127.0.0.1 | 97df5fdc-277b-339e-8aab-cb36317c1280 | {'-9223372036854775808'} | Cassandra

(2 rows)