Execute a Bash script (Shell Script) from YugabyteDB

Jim Knicely

Shell scripting is a powerful and versatile tool for working with databases and SQL.

It can help you automate tasks, manipulate data, and perform complex queried.

Wouldn’t it be cool to run a shell script from YugabyteDB? 

Here’s a quick tip on how to accomplish that!

First, let’s create a table to track and record calls to the shell script. This is optional, but it’s good practice to log this type of activity.

				
					yugabyte=# CREATE TABLE shell_program(user_name TEXT DEFAULT current_user, called TIMESTAMP DEFAULT current_timestamp, result TEXT);
CREATE TABLE

Here is the contents of a very simple shell script that will run ysqlsh to load some data into a table named TEST:

				
					yugabyte=# \! cat /root/shared/some_program.sh
ysqlsh -h yugabytedb.tech -c "INSERT INTO test VALUES (1), (2), (3);"

Note: In a multi-node cluster, make sure the shell script exists in the same directory on each node. This is best accomplished via a network share drive.

Continuing, here’s the table to be loaded:

				
					yugabyte=# CREATE TABLE test (id INT);
CREATE TABLE
To execute the shell script, we are going to use the PROGRAM option of the COPY command, like this: 
				
					yugabyte=# COPY shell_program(result) FROM PROGRAM '/root/some_program/some_program.sh';
COPY 1

If we query the TEST table we’ll see that 3 rows were inserted:

				
					yugabyte=# SELECT * FROM test ORDER BY 1;
 id
----
  1
  2
  3
(3 rows)

And we can query the SHELL_PROGRAM table to see our log data:

				
					yugabyte=# SELECT * FROM shell_program;
 user_name |           called           |   result
-----------+----------------------------+------------
 yugabyte  | 2024-01-22 15:55:48.285968 | INSERT 0 3
(1 row)

Have Fun!

Snowy Porch View