Extract YCQL Types Into DDL Commands

Migrating system metadata between YCQL Universes can be challenging since system keyspaces, such as system_schema, cannot be transferred using simple INSERT INTO or COPY commands.

NOTE: The YugabyteDB Anywhere Backup and Recovery feature supports the system_schema.types system table. However, only UDTs that are in use by at least one table will be restored to the target Universe.

Today’s YugabyteDB tip simplifies the manual process of migrating all user-defined types (UDTs) stored in the system_schema.types system table. We’ll extract the information and generate DDL commands to recreate the types seamlessly.

Example:

I have a YCQL database which has the following user created types:

				
					cassandra@ycqlsh> SELECT * FROM system_schema.types;

 keyspace_name | type_name  | field_names                                     | field_types
---------------+------------+-------------------------------------------------+----------------------------------------
            k2 | basic_info | ['birthday', 'nationality', 'weight', 'height'] |  ['timestamp', 'text', 'text', 'text']
            k1 |     person |           ['first_name', 'last_name', 'emails'] | ['text', 'text', 'frozen<list<text>>']

(2 rows)

The following script can be used to generate DDL commands for recreating the above types:

				
					#!/bin/bash
shopt -s expand_aliases

# Script:      ycql_extract_types.sh
# Description: This script will extract the data from the system_schema.types
#              system table, and then generate DDL commands which can be kept
#              as a backup, or executed on another YCL database to migrate the
#              types.
# Created:     11/17/2024
# Last update: 11/17/2024
# Reference:   https://docs.yugabyte.com/preview/api/ycql/ddl_create_type/

# Call Spec:
#   ./ycql_extract_types.sh <<YB TServer Host IP>>

# Important Notes:
#  1. This script is for YCQL only.
#  2. The Database Node IP must be running the YB T-Server process
#  3. Specify the path to the ycqlsh CLI via the YB_PATH variable
#  4. Specify the YCQL user and password in the YB_USER and YB_PASSWORD variables

# Set up the YCQLSH alias below per your environment
YB_PATH='/root/yugabyte-2.23.0.0/bin/ycqlsh'

# Provide YCQL super user and password
YB_USER='cassandra'
YB_PASSWORD='cassandra'

# Check if IP or hostname was provided
if [ -z "$1" ]
then
  echo "Please provide an IP or hostname for a YB Database Node"
  exit 1
else
  YB_IP=$1
fi

# Setup alias for ycqlsh
alias y="$YB_PATH $YB_IP -u $YB_USER -p $YB_PASSWORD"

# Extract as DDL commands (CREATE TYPE...)
y -e "SELECT * FROM system_schema.types;" | awk -F '|' '(NF==4 && FNR>=4) {gsub(" ","", $1); gsub(" ","", $2); gsub(" ","", $3); gsub("\\[","", $3); gsub("\\]","", $3); gsub(/\047/, "", $3); split($3,a,","); gsub(" ","", $4); gsub("\\[","", $4); gsub("\\]","", $4); gsub(/\047/, "", $4); split($3,a,","); split($4,b,","); printf "CREATE TYPE " $1 "." $2 "("; for(i in a) if (i < length(a)) {printf"%s %s, ", a[i], b[i]} else {printf"%s %s", a[i], b[i]}; print ");"}' | sort

exit 0

Let’s give it a try!

				
					[root@cloud-server-0 ~]# ./ycql_extract_types.sh 127.0.0.1
CREATE TYPE k1.person(first_name text, last_name text, emails frozen<list<text>>);
CREATE TYPE k2.basic_info(birthday timestamp, nationality text, weight text, height text);

Be sure to run the script after adding or removing types to ensure your backup is always up to date.

Have Fun!