I have a database called 'ticks', and a table called 'qcollector'. I am storing QCollector (tick data) data here.
root@media:~# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 29
Server version: 5.5.25-MariaDB-mariadb1~squeeze mariadb.org binary distribution
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> use ticks;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MariaDB [ticks]> desc qcollector;
+------------+------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+------------+------+-----+---------+----------------+
| key | bigint(20) | NO | PRI | NULL | auto_increment |
| instrument | char(4) | YES | MUL | NULL | |
| date | date | YES | MUL | NULL | |
| time | time | YES | | NULL | |
| last | double | YES | | NULL | |
| lastsize | int(10) | YES | | NULL | |
| totvol | int(10) | YES | | NULL | |
| bid | double | YES | | NULL | |
| ask | double | YES | | NULL | |
+------------+------------+------+-----+---------+----------------+
9 rows in set (0.01 sec)
MariaDB [ticks]> load data local infile '/shares/ES.txt' into table qcollector fields terminated by '\t' lines terminated by '\n' (@var1, time, last, lastsize, totvol, bid, ask, dummy, bidsize, asksize) set instrument = 'ES', date = STR_TO_DATE(@var1, '%m/%d/%Y');
Since I have just one table for multiple instruments, I need to define the instrument upon import (since that field is not in the QCollector file). Also the QCollector date format is mm/dd/yyyy, so it has to be reformated into a MySQL date field. We don't need the DTN tickid, so I discarded it using the dummy variable reference.