Monday, December 17, 2007
Pushing data to mysql db - using BASH
Assume one of my application generates some output in a file named "fan.out" in the following format.
$ cat fan.out
FAN_NAME|TIME|RPM|STATUS
FAN1|1195699322|4566|Moderate Condition
FAN12|1195699112|3562|Bad Condition
FAN11|1195699321|5676|Good Condition
FAN15|1195699117|8923|Good Condition
And I want these data to be automatically pushed to my mysql "FAN.DETAILS" table through a BASH script.
This is how I did that.
Some testings, basically I am interested in transforming above fan.out to a .sql file and I did it this way.
$ sed -e 1d -e 's/|/","/g' -e 's/^/insert into FAN.DETAILS values ("/' -e 's/$/");/' fan.out
insert into FAN.DETAILS values ("FAN1","1195699322","4566","Moderate Condition");
insert into FAN.DETAILS values ("FAN12","1195699112","3562","Bad Condition");
insert into FAN.DETAILS values ("FAN11","1195699321","5676","Good Condition");
insert into FAN.DETAILS values ("FAN15","1195699117","8923","Good Condition");
Some mysql work, I will create the schema of the table.
mysql> create database FAN;
Query OK, 1 row affected (0.00 sec)
mysql> use FAN;
Database changed
mysql> create table DETAILS( fan_name char(11), time CHAR(15), rpm int(15), status char(20) );
Query OK, 0 rows affected (0.09 sec)
mysql> desc DETAILS;
+----------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------+------+-----+---------+-------+
| fan_name | char(11) | YES | | NULL | |
| time | char(15) | YES | | NULL | |
| rpm | int(15) | YES | | NULL | |
| status | char(20) | YES | | NULL | |
+----------+----------+------+-----+---------+-------+
4 rows in set (0.03 sec)
And Here is my final script:
#!/bin/bash
#http://unstableme.blogspot.com
SQLFILE=/tmp/fan.out.sql
trap "rm -f $SQLFILE" EXIT
MYUSER=root
INPUTF=./fan.out
TABLE="FAN.DETAILS"
sed -e 1d -e 's/|/","/g' -e 's/^/insert into '"$TABLE"' values ("/' -e 's/$/");/' $INPUTF > $SQLFILE
echo "quit" >> $SQLFILE
/usr/local/mysql/bin/mysql -u $MYUSER < $SQLFILE
echo "Completed"
Executing the script:
$ ./push2mysql.sh
Completed
Now check table "FAN.DETAILS", those data in fan.out are published in the same.
mysql> select * from FAN.DETAILS;
+----------+------------+------+--------------------+
| fan_name | time | rpm | status |
+----------+------------+------+--------------------+
| FAN1 | 1195699322 | 4566 | Moderate Condition |
| FAN12 | 1195699112 | 3562 | Bad Condition |
| FAN11 | 1195699321 | 5676 | Good Condition |
| FAN15 | 1195699117 | 8923 | Good Condition |
+----------+------------+------+--------------------+
4 rows in set (0.00 sec)
Subscribe to:
Post Comments (Atom)
Posts
No comments:
Post a Comment