Determining if a YugabyteDB Universe is Running a YSQL or YCQL Workload

Jim Knicely

The YugabyteDB Query Layer (YQL) serves as the main interface, enabling applications to interact through client drivers. This layer handles API-specific tasks, including query and command compilation, as well as runtime functions like data type representations and built-in operations.

From the application’s perspective, YQL is stateless, allowing clients to connect to one or more YB-TServers on the appropriate port to perform operations on a YugabyteDB cluster.

Although YQL is designed with extensibility in mind, allowing for new APIs to be added, it currently supports two types of distributed SQL APIs: YSQL and YCQL.

When creating a Universe as a DBA, you can choose to enable or disable the YSQL and/or YCQL APIs. If you’ve enabled both APIs for a Universe, at some point in the future you may wonder how a development team is using the cluster. Are they using YSQL or are they using YCQL?

One simple way to determine which API is being used is to count the user tables have been created. 

Example:

YSQL:

				
					# ysqlsh -h $(hostname -I | awk '{print $1}') -U yugabyte -c "SELECT COUNT(*) FROM pg_tables WHERE schemaname NOT IN ('information_schema', 'pg_catalog');"
 count
-------
     3
(1 row)

YCQL:

				
					# ycqlsh $(hostname -I | awk '{print $1}') -e "SELECT COUNT(*) FROM system_schema.tables WHERE keyspace_name NOT IN ('system', 'system_schema', 'system_auth');"

 count
-------
     1

(1 rows)

In this case, it appears that developers are using both YSQL and YCQL on this Universe.

But, as a DBA you may not be able to connect to the APIs. That is, you don’t know the database username and password, so querying the APIs is not an option.

That’s where the yb-admin command line utility comes in to play. We can use it to list the tables from each API without having the need for database login credentials. 

YSQL:

				
					# if (( $(yb-admin --master-addresses=$(hostname -I | awk '{print $1}') list_tables include_db_type include_table_type | grep ' table$' | grep "ysql.*" | wc -l) > 0 )); then echo "YSQL in play"; fi
YSQL in play

YCQL:

				
					[root@Cloud_Server_0 ~]# if (( $(yb-admin --master-addresses=$(hostname -I | awk '{print $1}') list_tables include_db_type include_table_type | grep ' table$' | grep "ycql.*" | wc -l) > 0 )); then echo "YCQL in play"; fi
YCQL in play

Have Fun!

Lucy and Captain Remembered