List Tablet Details for a YSQL Table From the Command Line

				
					
#!/bin/bash
shopt -s expand_aliases

# Script:      list_tablets.sh
# Description: This script will list tablet details for a given YSQL table by scraping data from
#              the YB Master Admin UI. This script uses a user definded SQL function call get_table_id
#              which is discussed here: https://yugabytedb.tips/?p=1383

# Created:     11/30/2022
# Last update: 12/01/2022

# Edits:
#   12/01/2022  Added Tablet Row Counts
#   12/02/2022  Check for hash sharded primary key

# Call Spec:
#   ./list_tablets.sh <<Table Name>> <<YB Master Node IP>> <<Database Name>> <<Database Username>>

# Example Use:
#   ./list_tablets.sh public.test 172.152.101.162 yugabyte yugabyte

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

# Important Notes:
#  1. Table must have a HASH sharded Primary Key defined

# Verify input parameters

# Check if table name was provided
# Table name must be in the format: schema_name.table_name
if [ -z "$1" ]
then
  echo "Please provide a table name in the format: schema_name.table_name"
  exit 0
else
  YB_SCHEMA_NAME=$(echo $1 | cut -f1 -d.)
  YB_TABLE_NAME=$(echo $1 | cut -f2 -d.)
fi

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

# Check if table database name was provided
if [ -z "$3" ]
then
  echo "Please provide a database name"
  exit 0
else
  YB_DATABASE=$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 YSQLSH alias below per your environment
YB_PATH='/home/yugabyte/yb-software/yugabyte-2.15.3.2-b1-linux-x86_64/bin/ysqlsh'

alias y="$YB_PATH -h $YB_IP -U $YB_USER -d $YB_DATABASE"
y1="$YB_PATH -h $YB_IP -U $YB_USER -d $YB_DATABASE"

# Check if table exists
tc=`y -Atc "SELECT COUNT(*) FROM pg_tables WHERE schemaname = '$YB_SCHEMA_NAME' AND tablename = '$YB_TABLE_NAME';" | awk '{ print $1'}`

if [ $tc -gt 0 ]
then
  # Get the PK HASH columns for the table.
  pk=`y -Atc "WITH q AS (SELECT SUBSTR(pg_catalog.pg_get_indexdef(i.indexrelid, 0, true), POSITION('lsm' IN pg_catalog.pg_get_indexdef(i.indexrelid, 0, true))+4) x FROM pg_catalog.pg_index i JOIN pg_catalog.pg_class c ON c.oid = i.indrelid JOIN pg_catalog.pg_class c2 ON c2.oid = i.indexrelid JOIN pg_catalog.pg_class class ON c.oid = class.oid JOIN pg_catalog.pg_namespace n ON n.oid = class.relnamespace JOIN pg_catalog.pg_constraint con ON (conrelid = i.indrelid AND conindid = i.indexrelid AND contype IN ('p')) WHERE n.nspname = '$YB_SCHEMA_NAME' AND class.relname = '$YB_TABLE_NAME' AND (SUBSTR(pg_catalog.pg_get_indexdef(i.indexrelid, 0, true), POSITION('lsm' IN pg_catalog.pg_get_indexdef(i.indexrelid, 0, true))+4) ILIKE '%HASH,%' OR SUBSTR(pg_catalog.pg_get_indexdef(i.indexrelid, 0, true), POSITION('lsm' IN pg_catalog.pg_get_indexdef(i.indexrelid, 0, true))+4) ILIKE '%HASH\)%') ORDER BY i.indisprimary DESC, i.indisunique DESC, c2.relname) SELECT REPLACE(REPLACE(x_1, '(', ''), ')', '') FROM (SELECT SUBSTR(x, 1, CASE WHEN POSITION('HASH,' IN x)-1 <= 1 THEN POSITION('HASH)' IN x)-1 ELSE POSITION('HASH,' IN x)-1 END)  || ')' x_1 FROM q) foo ORDER BY LENGTH(x_1) DESC LIMIT 1;"`

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

# Scrap the YB Master UI for Tablet data and print it to the screen
curl -s http://$YB_IP:9000/tablets | grep -A 1 `y -Atc "SELECT get_table_id('$YB_SCHEMA_NAME', '$YB_TABLE_NAME');"` | sed -e 's/<[^>]*>/|/g' | sed 's/\([|]\)\1\+/\1/g' | sed 's:^.\(.*\).$:\1:' | sed -z 's/\n|LEADER/|LEADER/g' | sed '/^$/d' | awk -F\| -v pk="$pk" -v y1="$y1" -v sn="$YB_SCHEMA_NAME" -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" -Atc \"SELECT COUNT(*) FROM "sn"."tn" WHERE yb_hash_code("pk") BETWEEN CAST(x\047\""substr($5, p-4, 4)"\"\047 AS INT) AND CAST(x\047\""substr($5, p+4, 4)"\"\047 AS INT);\""; cmd | getline rc; close(cmd); print $4"|"$5"|"$6"|"$7"|"rc"|"$8"|"$9" / "$10" / "$11" / "$12" / "$13"|"$14;}' | column -t -s"|"

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

exit 0