List Tablet Details for a YCQL Table From the Command Line

				
					#!/bin/bash
shopt -s expand_aliases

# Script:      list_tablets_ycql.sh
# Description: This script will list tablet details for a given YCQL table by scraping data from
#              the YB Master Admin UI.

# Created:     12/01/2022
# Last update: 12/04/2022

# Edits:
#   12/03/2022 Added a connection an request timeout of 3600 seconds to YCQL calls
#   12/04/2022 Added a check if ycqlsh access is set up properly
#   12/04/2022 Set CONSISTENCY ONE for ycql sessions running COUNTs to enable follower reads
#   12/04/2022 Fixed Raft Config output to include all nodes

# Call Spec:
#   ./list_tablets_ycql.sh <<Keyspace Name>> <<Table Name>> <<YB Master Node IP>> <<Database Username>> <<Password>>
#   The password parameter is optional. If ommitted, the user will be prompted for a password for each YCQL call.

# Example Use:
#   ./list_tablets_ycql.sh test test 172.152.101.162 cassandra password

# Reference:   https://docs.yugabyte.com/preview/reference/configuration/yb-master/#admin-ui

# Defaults
YCQL_TIMEOUT=600

# Verify input parameters

# Check if table keyspace name was provided
if [ -z "$1" ]
then
  echo "Please provide a keyspace name"
  exit 0
else
  YB_KEYSPACE=$1
fi

# Check if table name was provided
if [ -z "$2" ]
then
  echo "Please provide a table name"
  exit 0
else
  YB_TABLE_NAME=$2
fi

# Check if IP or hostname was provided
if [ -z "$3" ]
then
  echo "Please provide an IP or hostname for a YB Master node"
  exit 0
else
  YB_IP=$3
fi

# Check if database user name was provided
if [ -z "$4" ]
then
  echo "Please provide a database username"
  exit 0
else
  YB_USER=$4
fi

# Set up the YCQLSH alias below per your environment
YB_PATH='/home/yugabyte/yb-software/yugabyte-2.17.1.1186-b4-centos-x86_64/bin/ycqlsh'

# Check if ycqlsh access is set up properly
if ! command -v $YB_PATH &> /dev/null
then
    echo "ycqlsh could not be found"
    exit
fi

# If database password was provided, use it in building ycqlsh alias and variable
if [ -z "$5" ]
then
  alias y="$YB_PATH $YB_IP -u $YB_USER --connect-timeout=$YCQL_TIMEOUT"
  y1="$YB_PATH $YB_IP -u $YB_USER --connect-timeout=$YCQL_TIMEOUT"
else
  alias y="$YB_PATH $YB_IP -u $YB_USER -p $5 --connect-timeout=$YCQL_TIMEOUT --request-timeout=$YCQL_TIMEOUT"
  y1="$YB_PATH $YB_IP -u $YB_USER -p $5 --connect-timeout=$YCQL_TIMEOUT --request-timeout=$YCQL_TIMEOUT"
fi

# Check if table exists
tc=`y -e "SELECT COUNT(*) FROM system_schema.tables WHERE keyspace_name = '$YB_KEYSPACE' AND table_name = '$YB_TABLE_NAME';" | awk 'FNR==4{print $1}'`

if [ $tc -gt 0 ]
then
  # Get the PK HASH columns for the table.
  table_id=`curl -s http://$YB_IP:7000/tables | sed -e 's/<[^>]*>/|/g' | awk -F\| -v YB_KEYSPACE=$YB_KEYSPACE -v YB_TABLE_NAME=$YB_TABLE_NAME '{if ($3==YB_KEYSPACE && $6==YB_TABLE_NAME && $15=="") print $13}'`
  pk=`curl -s http://$YB_IP:7000/table?id=$table_id | grep "NOT NULL PARTITION KEY" | sed -e 's/<[^>]*>/|/g' | awk -F\| '{print $3}' | paste -d ',' -s`

if [ -z "$pk" ]
then
  echo "The table \"$1.$2\" does not have a HASH sharded primary key defined."
  exit 1
fi

# Get the number of nodes in the cluster
nc=`y -e "SELECT COUNT(*) FROM system.peers;" | awk 'FNR==4{print $1}'`
nc=$((nc+1))

# Scrap the YB Master UI for Tablet data and print to it to the screen
curl -s http://$YB_IP:9000/tablets | grep -A $nc $table_id | sed -e 's/<[^>]*>/|/g' | sed 's/\([|]\)\1\+/\1/g' | sed 's:^.\(.*\).$:\1:' | sed -z 's/\n|LEADER:/|LEADER:/g' | sed -z 's/\n|FOLLOWER:/ FOLLOWER:/g' | sed '/^$/d' | awk -F\| -v pk="$pk" -v y1="$y1" -v k="$YB_KEYSPACE" -v tn="$YB_TABLE_NAME" 'BEGIN{print "Tablet ID|Partition|State|Hidden|Row Count|Num SST Files|On-disk size|RaftConfig"}; {p=index($5,","); cmd=y1" -e \"CONSISTENCY ONE; SELECT COUNT("pk") FROM "k"."tn" WHERE partition_hash("pk") >= \""strtonum( "0x" substr($5, p-4, 4))"\" AND partition_hash("pk") <= \""strtonum( "0x" substr($5, p+4, 4))"\";\" | sed -n 5p"; cmd | getline rc; close(cmd); print $4"|"$5"|"$6"|"$7"|"strtonum(rc)"|"$8"|"$9" / "$10" / "$11" / "$12" / "$13"|"$14;}' | column -t -s"|"

else
  echo "The table \"$1.$2\" does not exist."
fi

exit 0