Well greetings everybody! I'm pretty excited to kick things off in the Data Mining Lab, and get rolling on some cool projects. I didn't get much time to get warmed up with the promised "first couple of days of reading" to get brought up to speed in the lab, but instead got handed my first project. So after taking over a computer and making it mine with cool color schemes and wallpaper and such, I got started.
So the project was to import a ginormous mass of data into Postgres or "another database of my choice". Well seeing as I didn't have a clue about anything, Postgres sounded just fine. ;p Luckily for me I had some training in SQL, so I just had to learn Postgres specific commands. With the abundant help of Michael I managed to avoid looking though tons of boring documentation and got familiarized with the Linux command prompt and Postgres commands. Here's a couple of the obstacles we had to figure out how to overcome:
- We couldn't import the data in while inside Postgres, nor could we simply use the filename directly, so we had to do it on the command line and pipe the file into standard in the like so:
psql deseret_book -c "COPY tablename FROM STDIN DELIMITER '|' CSV HEADER" < filename.
I don't really remember why. It worked great though. ;p
- Some of the data wouldn't read in because of typos, and we had to figure out how to edit the files, which was kind of impractical since some of the files had over 10 million lines. So we came up with a pretty smart solution which involved dividing up the files into 3 parts using Linux head and tail commands, altering the wrong line, and then concatenating the 3 parts together again. Well, at least it seemed smart until Matt asked me why I wasn't doing it an easier way. So I did. I ended up writing up a quick Java program that analyzes an input file and separates the good and bad lines into two files. Doing it this way made it easy to get all the good lines into the database and isolate the bad ones.
So yeah, there it is. I can now do some stuff in Postgres. I also learned again that you ought to explore many different solutions to a problem. You might as well do something the easier way if you know how.
Oh yes, and how 'bout these little tips I figured out, no doubt already obvious to everyone else:
- Always close a file in Java when it's done, otherwise you may not get the results you want.
- When putting two operating systems on a machine, you have to have free unpartitioned memory. Linux didn't recognize my memory that was already partitioned.
- Postgres doesn't account for identical rows unless a primary key is created. It also doesn't put in any rows if one or more of them have an error.
- Write things down as you learn them, because you'll probably forget them later.
