D. Cooper Stevenson
September 28, 2010 0

Update Databases Dynamically

By in Artificial Nerual Networks, Programming

I recently had to work with data for importation into a database that provided several challenges. Here’s the raw output:

Calculating indicator AroonUp[25, {I:Prices HIGH}, {I:Prices LOW}] …
AroonUp[25, {I:Prices HIGH}, {I:Prices LOW}][2010-05-03 09:01:00] = 68.0000
AroonDown[25, {I:Prices HIGH}, {I:Prices LOW}][2010-05-03 09:01:00] = 12.0000
AroonOsc[25, {I:Prices HIGH}, {I:Prices LOW}][2010-05-03 09:01:00] = 56.0000

About 45,000 or so of these records. My table should look like this:

|————————————————————————————————-|

|     date        |     time   | aroonup25 | aroondown25 | aroonosc25 |

| 2010-05-03  | 09:01:00 | 68.0000    |     12.0000      |  56.0000    |

|————————————————————————————————-|

I don’t know ahead of time which indicator the file will specify (i.e. up25, down25, osc25) nor do I know whether or not the record is already in the database.

Putting the correct values is especially acute as a) I need to ensure the date & time corresponds with the correct values (using UNIX’s ‘cut’ just won’t cut it, pun intended) and b) over the course of literally “hundreds of thousands” of records, performance becomes a real issue.

I wrote a script (listed below) that coordinates the indicator type, date, time, and value. The first step is to parse the input file to an output into database syntax. Here’s the intermediate step’s output:

INSERT INTO ARG (aroonup25, date, time) VALUES (‘68.0000’, ‘2010-05-03′, ’09:01:00′) ON DUPLICATE KEY UPDATE aroonup25=’68.0000’;
INSERT INTO ARG (aroondown25, date, time) VALUES (‘12.0000’, ‘2010-05-03′, ’09:01:00′) ON DUPLICATE KEY UPDATE aroondown25=’12.0000’;
INSERT INTO ARG (aroonosc25, date, time) VALUES (‘56.0000’, ‘2010-05-03′, ’09:01:00′) ON DUPLICATE KEY UPDATE aroonosc25=’56.0000’;

Now that I’ve created a file that contains the correct database syntax, all that’s needed is to import it into the database:

$ cat ./aroon.sql | mysql minute -u cstevens -p

While the script seems simple enough it’s actually a cornerstone to building a sophisticated method for storing indicator (in this case) data.

Here’s the script:

#!/bin/ksh

parse_file=./aroon.txt
tmp_parse_file=./tmp.txt
sql_file=./aroon.sql
table_name=$security

database_table=minute

#break down the input file
for i in 25 #25 is the period length of the indicator (for future expansion to other time lengths)
do
cat $parse_file | while read line
do
#grab ’em (ticks), parse, and convert to lower case
awk -F'[‘ -v counter=$i ‘{print $1’counter’,$3}’| sed s/’] =’//g | tr [:upper:] [:lower:] > $tmp_parse_file
done
done

if [ -f $sql_file ]
then
rm $sql_file
fi

# create sql file output
cat $tmp_parse_file | while read indicator date time value
do
print INSERT INTO ARG ‘(‘$indicator’, date, time)’ VALUES ‘(‘”‘”$value”‘”, “‘”$date”‘”, “‘”$time”‘”‘)’ ON DUPLICATE KEY UPDATE $indicator=”‘”$value”‘”‘;’ >> $sql_file
done

Note that this script contains a couple of “hard coded” variables. I consider this poor programming practice. In this case, the hard coded variables are actual variables in my master script. I will run through the necessary changes as I import this code into the code tree.

Leave a Reply