Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Introduction to Bash Scripting: Pipes and Redirections


By:   |   Last Updated: 2019-02-07   |   Comments   |   Related Tips: More > 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.

Name File Descriptor Description
/dev/stdin 0 Standard Input
/dev/stdout 1 Standard Output
/dev/stderr 2 Standard Error

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)

Printing your current terminal device name.

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.

A basic command redirection example.

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.

We can use the >> operator to both create a file and append data to it.

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.

Creating a script file using redirections.

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.

Execution of the cat command without any argument.

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.

Parameter Value Description
-S localhost The server we want to connect. In this case it's the local host computer.
-U sa The user name used to log in to the SQL Server instance.
-P Pa$$w0rd (by escaping the $ character it would be ¨Pa\$\$w0rd) The password of the given user.

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

Using a redirection to input the content of a script file to sqlcmd.

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.

Using a pipe to concatenate grep and tail commands.
Next Steps


Last Updated: 2019-02-07


get scripts

next tip button



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

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools