Database operations with SQL and Korn shell
Submitted by Igor on November 20, 2005 – 11:46 amNo Comment
The following Korn shell function will read the a directory containing CSV data files named server_sysstat.20051119.csv with format:
server_2,7,1461,2,2195979,90,00:00:17,42
server_2,6,1467,2,2200018,85,00:02:18,42
server_2,6,1462,2,2198306,89,00:04:20,42
The function will read each file line by line and do either an insert to or an update of the database table. It is important to remember that, if your source data contains single quotes (apostrophes), you will need to preceed all single quotes with the backward \ slash (use three slashes “\\\” with KSH).
For example:
country=$(echo "$LINE" | awk -F'@' '{print $2}' | sed "s+'+\\\\'+g")
And here’s the script:
SQL_Diskmon_Sysstat_Func() { ls "$INPUTDIR3" | sort | fgrep "_sysstat." | while read REPORT do year=$(echo "$REPORT" | awk -F'.' '{print $2}' | cut -c 1-4) month=$(echo "$REPORT" | awk -F'.' '{print $2}' | cut -c 5-6) day=$(echo "$REPORT" | awk -F'.' '{print $2}' | cut -c 7-8) DATE=$(echo "${year}-${month}-${day}") cat "${INPUTDIR3}/${REPORT}" | while read LINE do STORAGE_UNIT=$(echo "$LINE" | awk -F',' '{print $1}') VAR2=$(echo "$LINE" | awk -F',' '{print $2}') VAR3=$(echo "$LINE" | awk -F',' '{print $3}') VAR4=$(echo "$LINE" | awk -F',' '{print $4}') VAR5=$(echo "$LINE" | awk -F',' '{print $5}') VAR6=$(echo "$LINE" | awk -F',' '{print $6}') TIME=$(echo "$LINE" | awk -F',' '{print $7}') DATETIME=$(echo "$DATE $TIME") VAR8=$(echo "$LINE" | awk -F',' '{print $8}') echo "$STORAGE_UNIT,$DATETIME,$VAR2,$VAR3,$VAR4,$VAR5,$VAR6,$VAR8" >> "$TMPF3" echo "$STORAGE_UNIT,$DATETIME,$VAR2,$VAR3,$VAR4,$VAR5,$VAR6,$VAR8" done done if [ -f "$TMPF3" ] then cat "$TMPF3" | while read LINE do HOST_NAME=$(echo "$LINE" | awk -F',' '{print $1}') DATETIME=$(echo "$LINE" | awk -F',' '{print $2}') VAR3=$(echo "$LINE" | awk -F',' '{print $3}') VAR4=$(echo "$LINE" | awk -F',' '{print $4}') VAR5=$(echo "$LINE" | awk -F',' '{print $5}') VAR6=$(echo "$LINE" | awk -F',' '{print $6}') VAR7=$(echo "$LINE" | awk -F',' '{print $7}') VAR8=$(echo "$LINE" | awk -F',' '{print $8}') $MYSQL -u$DBUSER -p$DBPASS $DBNAME << EOF | wc -l | awk '{print $1}' | read COUNT SELECT * FROM sandisk_performance WHERE storage_unit='$STORAGE_UNIT' AND line_datetime='$DATETIME' \ AND threads_running='$VAR3' AND threads_blocked='$VAR4' AND var5='$VAR5' AND free_memory='$VAR6' \ AND cpu_idle='$VAR7' AND cpu_threashold='$VAR8'; EOF if [ $COUNT -eq 0 ] then echo "Inserting report for : ${STORAGE_UNIT}($DATETIME)" $MYSQL -u$DBUSER -p$DBPASS $DBNAME << EOF INSERT INTO sandisk_performance(storage_unit,line_datetime,threads_running,threads_blocked,var5,\ free_memory,cpu_idle,cpu_threashold) values('$STORAGE_UNIT','$DATETIME','$VAR3','$VAR4','$VAR5','$VAR6',\ '$VAR7','$VAR8'); EOF else echo "Updating report for : ${STORAGE_UNIT}($DATETIME)" $MYSQL -u$DBUSER -p$DBPASS $DBNAME << EOF UPDATE sandisk_performance SET storage_unit='$STORAGE_UNIT', line_datetime='$DATETIME', threads_running='$VAR3', threads_blocked='$VAR4', var5='$VAR5', free_memory='$VAR6', cpu_idle='$VAR7', cpu_threashold='$VAR8' WHERE storage_unit='$STORAGE_UNIT' AND line_datetime='$DATETIME' \ AND threads_running='$VAR3' AND threads_blocked='$VAR4' AND var5='$VAR5' AND free_memory='$VAR6' \ AND cpu_idle='$VAR7' AND cpu_threashold='$VAR8'; EOF fi done fi }
Popularity: 4% [?]
Related posts:
