Approach-1: metadata_capture.sh

g09@united-button-180007:/scripts$ cat metadata_capture.sh
#!/bin/sh

#comments section

#parameters reading

Source_Count=`gsutil cat gs://kohls-emp/GCP/Emp_Info.txt | wc -l`
echo Source_Bucket_Count=$Source_Count

start_time=`date`
echo Job_Start_Time = $start_time

#metadata info collection

#main task execution

echo Main job is executing

end_time=`date`
echo Job_End_Time = $end_time

Tgt_Table_Count=`bq query --quiet 'select count(*) from emp.emp_info'| cut -c 5-5 | head -4|tail -1`
echo Target_Table_Count=$Tgt_Table_Count


#exception handling

gspann09@united-button-180007:/scripts$


Output:



We can add more details like source file name, file path, table name, db name, bucket info, instance name.

Approach-2:
Created the audit table with below columns


Table name: audit.table_stats
Columns:
Script_Name,
Table_Name,
Schema_Name,
Start_Time,
End_Time,
No_Of_Rows_Affected,
DML_Operation,
Script_Status
  
We are inserting the table stats in to audit table through below shell script.


Sample.sh script Content:
  
#!/bin/sh

Tgt_Table_Count=`bq query --quiet 'select count(*) from emp.emp_info'| cut -c 5-5 | head -4|tail -1`

echo Target_Table_Count=$Tgt_Table_Count

bq query --use_legacy_sql=False
--parameter Script_Name:string:U86_UAOFFER1.sql
--parameter Table_Name:string:U86_UA_OFFER1
--parameter Schema_Name:string:CIA_LANDING
--parameter Start_Time:string:current_date
--parameter End_Time:string:current_date
--parameter No_Of_Rows_Affected:string:$Tgt_Table_Count  
--parameter DML_Operation:string:INSERT
--parameter Script_Status:string:RUNNING
"INSERT audit.table_stats (Script_Name,Table_Name,Schema_Name,Start_Time,End_Time,No_Of_Rows_Affected,DML_Operation,Script_Status)
VALUES (@Script_Name,@Table_Name,@Schema_Name,current_date,current_date,@No_Of_Rows_Affected,@DML_Operation,@Script_Status)"

echo Insert Success.

 Shell script execution:


Audit.table_stats table out put:

 We can insert all the table stats into the audit table.