Introduction to Bash Scripting: Pipes and Redirections

By: Daniel Farina   |   Comments   |   Related: > SQL Server on Linux

Problem

There have been several tips about using bash scripting to help the SQL Server DBA working in a Linux environment. In this tip you will learn about the importance of pipes and redirections for bash scripting in Linux and its usage.

Solution

If you ask a Linux user what's the best feature of Bash, you will find that most of the time they answer "redirection and pipes" or redirections for short. Of course, there are reasons for that answer, which are:

• Efficiency: It gives us the chance to concatenate commands in one single line.
• Fast coding: Expressions with redirection and/or pipes are fast to write.

But since nothing comes for free, there are some drawbacks. Pipes and redirections are difficult to understand for people new to Linux. It's not impossible, but you will need to put some time into this.

Understanding Pipes and Redirections in Bash

As I mentioned, both pipes and redirections allow us to concatenate commands. Most of the time you will find that people use the word redirection to refer to pipes and redirections (and I will do so in this tip). That's because in fact they both redirect streams, but they do it differently.

With Linux every command executed has three available streams that can be redirected. Those streams are the standard input which is the way commands get interaction, the standard output that is where the command will show its results and finally the standard error which is the stream where the error messages will be sent. Before continuing, let me remind you that on Linux systems everything is a file, even these streams I told you about.

All of these three file descriptors point by default to the file /dev/tty which is the current terminal in use. You can execute the tty command to see which terminal you are running (i.e. /dev/tty1 /dev/tty2)

As a side note, when you run the tty command you will see a /dev/tty(n) console only if you are working physically on the server. Otherwise your results will be something like /dev/pts/(n) which refers to a pseudo terminal device and is used when you access remotely or you are using the GUI.

Difference between Pipes and Redirections

I told you previously that both redirections and pipes redirect streams (a file descriptor if you want the proper definition) of the process being executed. The redirections are used to send the output stream to a file and to send the content of a given file to the input stream. In contrary, a pipe connects two commands by sending the output stream of the first command to the input stream of the second command. This connection is performed before any redirections specified by the command.

Bash Redirections

In order to be able to do redirections you have to use the redirection operators which are the greater than sign ">" and the less than sign "<".

The greater than sign ">" is used when you want to redirect the output of a command (or command expression) to a file. If the file doesn't exist it is created, but if the file already exists it is overwritten without any warning. On the next line of code, I am saving the output of the ls -l command into a file named listing.txt and then I show the contents of that file.

ls –l > listing.txt 
cat listing.txt

On the next screen capture you can see the result of executing the previous code.

But if you want to append the output of a command to a file you can use two greater than signs together ">>". That will create a file in case it doesn't exist) and append at the end of the file the command output being redirected. In the next script I am appending the output of date command, which is the current date, to a file named mydate.txt.

date >> mydate.txt
cat mydate.txt

Take a look at the next screen capture where I show the execution of the previous code.

As you can see, the code was run two times in order to create the new file and then to append the command's output to the already existing file. Notice that first I checked that the file doesn't exist by using the ls filename command.

On the contrary, if we want to send the content of a file as the input for a given command, we must use the less than sign "<".

As an example, I will show you how to run a script file in sqlcmd in a way that if you ever worked with MySQL will resemble the process of restoring a database dump script, but in this case I am not going to restore anything. I will show you how to send as an input to sqlcmd a text file with a script taken from this previous tip of mine SQL Server scripts to use with sqlcmd. I will be using the tempdb_space_by_versionstore.sql script file from that tip.

Our first step is to create the script file named tempdb_space_by_versionstore.sql. We can do that by using the following command:

cat > tempdb_space_by_versionstore.sql 

After you hit the enter button you will not see the command prompt, instead you will see the cursor blinking and waiting for your input. At this step you can copy and paste the following query into the terminal.

SELECT DB_NAME(database_id) as 'Database Name',
       reserved_page_count,
       reserved_space_kb 
FROM   sys.dm_tran_version_store_space_usage;
GO  

After pasting the previous query, press CTRL+D key combination which sends the End Of File (EOF) signal and therefore closes the file. The next screen capture serves as an illustration.

In case you didn't notice, we have created the tempdb_space_by_versionstore.sql file using a redirection of the cat command. If you run the cat command without any parameters you will also see the prompt blinking waiting for your input like on the next screen capture, but in this case when you press CTRL+D it won't save any data.

Now, to send the content of that file as the input for the sqlcmd command I will write the following into the command shell.

sqlcmd -S localhost -Usa -PPa\$\$w0rd < tempdb_space_by_versionstore.sql

The previous execution of sqlcmd may look a little bit trickier, because I am escaping the $ character by using an inverted slash. Remember that on sqlcmd you have to escape the special characters you may have in the command line. For those of you who don't know about using sqlcmd, in the next table I explain each parameter of its invocation.

On the next screen capture you can see the execution of the previous command.

If you ever had a chance to work with MySQL on Linux, the previous command may resemble the command used to restore a dump on MySQL. Below is the syntax for restoring a dump to MySQL so you can see the similarities.

mysql -u [user] –p[Password] < db_backup.dmp

The Pipe

To concatenate the output of a command as the input of another we have to use a vertical bar "|". Something to note is that each command is run synchronously, which means that the shell will wait for each command at both ends of the pipe to finish before returning control.

On the next example, I will show you how to use a pipe to get the last message of the syslog related to SQL Server process. To do so I will concatenate the commands grep and tail with a pipe.

The command grep is used to search for a pattern in files. In the next example it receives the parameters "sqlser" as the pattern to be found and /var/log/syslog for the file to search. Then the tail command prints the last ten lines of a file, or if no file is specified like in this case, the standard input which in this case it's the output of the grep command.

grep sqlser /var/log/syslog | tail

Take a look at the next image to see how the previous line of code is executed in my test system.

Next Steps

• If you didn't read my previous tips about the Introduction to Bash Scripting for SQL Server DBAs, here is where to start Introduction to Bash Scripting for SQL Server DBAs.
• After reading the previous tip, you can keep reading the next ones:
  • Introduction to Bash Scripting for SQL Server: Declaration of Variables and Constants
  • Introduction to Bash Scripting: Conditional Statements
  • Introduction to Bash Scripting: Display User Messages and Catch User Input
  • Introduction to Bash Scripting: Functions
  • Introduction to Bash Scripting: Iterative Statements Using Loops.
• You can practice reference redirections by creating a folder with the scripts from the SQL Server scripts to use with sqlcmd tip.
• Stay tuned to the SQL Server on Linux tips category for more tips about SQL Server on Linux.

About the author

Daniel Farina was born in Buenos Aires, Argentina. Self-educated, since childhood he showed a passion for learning.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips