Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a collection of csv files with measurements with the following 54 columns:

SITE (string)
TIME (integer as yyyymmddhh)
PROD (double)
EST.0 (double)
...
EST.50 (double)

I want to read this to a mysql database. I would prefer a structure that would allow me to compute average and variance of values EST.0 .. EST.50, for each row.

What would be a good database structure for this data? Can I keep the columns as they are, or should I only have one "value" column? And if the former, how can I then get the averages and variances that I want?

From reading related questions, it looked like having so many value columns is not a good idea, so I was thinking about having a "value" column for all the double values, plus an "est_ID" column for an identifier, using -1 or NULL to denote the "PROD" value - does it make sense?

And what about the date-time column - will mysql understand it, or do I need to provide some information about date-time format?

As for handling multiple files, I guess that a small bash script calling mysqlimport should do the job nicely?

Thanks.

Michal

share|improve this question

migrated from stackoverflow.com Jan 7 '13 at 12:40

This question came from our site for professional and enthusiast programmers.

    
54 is a big number! –  Shiplu Jan 7 '13 at 8:38

1 Answer 1

up vote 1 down vote accepted

You will want to have two database tables for this.

One table with id int/bigint autoincremenmt, site, time and prod. id is the primaty key.

And one table with id (same as in previous table but no autoincrement, it refers to the id in the first table) the number of the EST, and EST . The primary key of this table would span two columns, id and number of the EST (this may require some fiddling with the database table creation tool you are using).

As for date columns, no mysql doesn't understand that format, it has its own date and datetime formats for columns. At our place here we are using unix timestamps and store these in the database as integers or bigints.

share|improve this answer
    
Thanks, this sounds like a good structure. The question now is, how do I get this from the CSV files to the mysql? –  Michal Kaut Jan 7 '13 at 10:18
    
I can't really give an unbiased answer to this, since I mostly do php on a webserver and use PDO database layer there. You can probably use bash and awk to import the data. Or you could write a small C program that accesses the mysql database. I don't know what tools you are good at .. –  Adder Jan 7 '13 at 10:27
    
I do not know how to do this with bash, since I would need access to the auto-generated IDs. I am considering C++, using some CSV parser (Boost tokenizer?) and a POCO Data library to access the database - but it will be some work. An alternative could be reading the whole file into a temp. table and then use SQL commands to create the two tables - but I do not have enough SQL skill to do this, or even to estimate how difficult it is :-( –  Michal Kaut Jan 7 '13 at 11:05
    
At the end, I went the C++ route. It was not that difficult, but it turned out the Poco Data MySQL connector is quite slow, so I ended up dumping the big EST table into a csv file and importing this from a bash script - see this thread. –  Michal Kaut Jan 16 '13 at 14:50

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.