Awk - UNIX Command and Its Use in ETL Testing

 

Introduction             

This document describes the possible ways of using the awk command in UNIX, its advantages and different usages with examples and an ETL case study

Usage

awk –F\<<delimiter>> ‘{print $0}’ <<file name>>

Ex:  awk –F\| ‘{print $0}’ Unix_File.dat

This command will display all the records in the file Unix_File.dat

‘|’ is the delimiter

‘$0’ refers to the entire set of records

This is as good as cat <<filename>>

Purpose

The purpose of awk command is to know exactly what value is present in particular column of a flat file on UNIX and also to display the records that match a particular criteria. If cat command is used, user may have to manually search for the value in bunch of records and it will be very tedious. To make it easy grep command also could be used but filtering criteria can be better applied using awk

Using awk we can:

1. Display entire contents of the file

2. Display data in particular fields of the file

3. Display the number of fields in each record

4. Display the rows matching a particular criterion

Example to display entire file content:

Consider a flat file on UNIX with name Sample_file.txt

The contents of the file are as below:

cat Sample_file.txt      

0^AWER^1A

1^DEcY^B9

2^    ^c1O

User wants to display all the records. The command to be used is:

awk -F\^ '{print $0}' Sample_file.txt

Output:

0^AWER^1A

1^DEcY^B9

2^    ^c1O

Note:

‘^’ is the delimiter      

‘$0’ refers to the record set

Example to display data in a particular field:

Taking the file used in above example, user wants to display only the values in second column of the file.

The command to be used is:

awk -F\^ '{print $2}' Sample_file.txt

Output:

AWER

DEcY

Note:

    ‘^’ is the delimiter

‘$2’ refers to 2^nd column of the file

Example to display the number of fields in every record of the file:

User wants to know the number of fields in every record for the file Sample_Vendorfile.txt with below content:

cat Sample_Vendorfile.txt

H|VenZ

D|2|AW23

D|3|Zwe3

D|4|$5T1

T|3

Command to be used:

awk -F\| '{print NF "fields  found in row#" NR}' Sample_Vendorfile.txt

Output:

2fields found in row#1

3fields found in row#2

3fields found in row#3

3fields found in row#4

2fields found in row#5     

Note:

 ‘|’ is the delimiter

‘NF’ refers to number of fields

‘NR’ refers to record number

Example to display rows matching particular criteria:

Taking the file used in above example, user would like to display all the records whose 1^st field is D

Command to be used:

awk -F\| '($1=="D") {print $0}' Sample_Vendorfile.txt

Output:

D|2|AW23

D|3|Zwe3

D|4|$5T1

Note:

$1 refers to 1^st field, $2 refers to 2^nd field…….$X refers to the Xth field etc.

            Sample ETL testing case study

UNIX Server

Flat file

Database

ETL

                                                Fig 1.0

Consider the ETL scenario in Fig 1.0 where data is being loaded into database tables from flat files on UNIX

Tester has written few test cases covering the below scenarios

1. Verify the length of each field in the flat file

2. Verify the number of fields per record

3. Perform data validations between file data and table data

4. Verify that a particular field is having a particular value only (if it is hardcoded as per the design)

For example, consider an ETL process between an external flat UNIX file and a database table

File name:  Unix_Source_File.dat

File Content:

1|Sashi|Tester|Infosys|TE   

2|Koushik|Delivery Manager|Infosys|DM

3|Prabhu|Process Lead|Infosys|PL

Verifying field length:

To test that the length of the 5^th field in the file is not more than 2 characters, this is the command to be used:

awk –F\| ‘{print  length($5)}’  Unix_Source_File.dat

Output:

2

2

2

Or this can be done like this

awk  -F\| ‘(length($5)!=2) { print “Field length is not 2 for record#” NR}’  Unix_Source_File.dat

Output:

This will display the message “Field length is not 2 for record#X” only for that particular record X where length of the 5^th field is not 2 characters  

Verifying the no. of fields per record:

To verify that the number of fields per record is 5, tester can use the awk command like this:

awk -F\| '(NF!=5) {print "Field number mismatch for record#"NR}' Unix_Source_File.dat

Output:

This will display the message “Field number mismatch for record#X” only for that particular record X where no. of fields is not 5

Data validation:

Source UNIX flat file 1|Sashi|Tester|Infosys|TE 2|Koushik|Delivery Manager|Infosys|DM 3|Prabhu|Process Lead|Infosys|PL

ETL

Target database table EMP_Table

                                                          Fig 2.0

As shown in fig 2.0, only the first 4 field’s data is being loaded into target database. We can export both target and source data onto a spread sheet and compare the data. The below steps have to be followed.

Step1: Execute below awk command on UNIX

awk -F\| '{print $1"|",$2"|",$3"|",$4}' Unix_Source_File.dat >Temp.txt

The data in Temp.txt would look like this

1|Sashi|Tester|Infosys

2|Koushik|Delivery Manager|Infosys

3|Prabhu|Process Lead|Infosys

Step2: Place the file Temp.txt onto local desktop using Secure FX tool

Step3: Copy the data present in Temp.txt in MS excel

Step4: On database fire the below SQL query

Select Emp_Id,Emp_name,Portfolio,Organization from EMP_Table;

Step5: Export the data resultant of above SQL query onto the same spreadsheet of step3

Step6: Compare both source and target data

Verifying data in a particular field:        

To verify that the field #4 is having the value as only “Infosys”, use awk command as below:

awk -F\| '($4!="Infosys") {print "Data mismatch for record#"NR}' Unix_Source_File.dat

Output:

The above command will display the message “Data mismatch for record#X” only for that particular record X where field# 4 data is not “Infosys”

Advantages of awk command

1.    Helps to apply filters over files on UNIX

2.    Easy to locate and display data from huge chunk of records in a file

3.    Data validation on UNIX flat files will be easy with awk command

4.    Helps to reformat the data while displaying

5.    Helps in effective testing of ETL scenarios

Limitations

The awk command can be used only on serial ASCII files which have data in delimited format.

Posted in: ETL Testing