The initial distributed file was AOL-data.tgz (439MB). Inside it contains a simple readme file and 10 .gz archives, each containing a CSV collection. With a packed size of about 45MB each, when extracted they reveal the true treasure: a nice collection of actual user searches (each file at about 200MB).

Although AOL removed the file from their website, it was spread to far too many people already and continues to distribute over the Web or through Torrent systems.

Create the MySQL database

I have only worked with the first file since it's a huge database and quite hard to extract some useful information even from a single file, but the operations are the same for each part.

Why would you want to convert it to an actual database format? Well, if you know the power of SQL this question is very much answered by itself, if not, let's just say you can search, group and order data that you are looking for faster and easier than by other means.

The first step would be to create the database structure:

CREATE TABLE `aol` (
 `query_id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
 `AnonID` INT(10) UNSIGNED NOT NULL,
 `Query` VARCHAR(255) NOT NULL,
 `QueryTime` DATETIME NOT NULL,
 `ItemRank` TINYINT(3) UNSIGNED NOT NULL,
 `ClickURL` VARCHAR(255) NOT NULL,
 PRIMARY KEY  (`query_id`)
 );

You can add an index on the Query field if you wish, also you can remove the `query_id` if you are looking for high INSERT speed, but I would not recommend it if you wish to browse through results.

The PHP script to parse the file

Next, I've created a small PHP script to parse the plain text file and run queries in order to populate the database.

<?php

set_time_limit (1800);  // set the time limit - parsing the file can take several minutes
 
$file = "user-ct-test-collection-01.txt";    // change this for which file you want to parse

 
$link = mysql_connect("localhost", "root", "") 
       or die("Could not connect"); 
       
mysql_select_db("aol") or die("Could not select database."); 

 
$fd = fopen ($file, "r"); 
 
$i = 0;
 

while (!feof ($fd))
{ 
	$buffer = fgets($fd, 4096); 
 

	$a = explode("\t", $buffer);
	
	if (!is_numeric($a[0])) continue;
	
	// group the queries in packs of 100 (you can change the number)

	// - this should improve speed by sending less queries to the database
	
	if (($i%100) == 0)
	{
		if ($i>0)

		{
			$result = mysql_query($sq);
			
			$err = mysql_error();
			
			if (!empty($err))

			{
				echo "Error on line ".$i.": ".$err."
Query: ".$sq."

";
			}

		}
		
		// the table was called `aol`, you can change this to meet your needs
		
		$sq = "INSERT INTO `aol` ( `AnonID` , `Query` , `QueryTime` , `ItemRank`
 , `ClickURL` )
			VALUES (
			'".$a[0]."', '".addslashes($a[1])."', '".$a[2]."', '".$a[3]."',
 '".$a[4]."'
			)";
	}

	else
	{
		$sq .= ", (
			'".$a[0]."', '".addslashes($a[1])."', '".$a[2]."', '".$a[3]."',
 '".addslashes($a[4])."'
			)";
	}

	
	$i++;
	
}
 
echo "done.";
 
fclose ($fd); 
 
mysql_close($link);

?>
 

The result:

initial file: 212MB

mysql db: 224MB (264MB with index on Query)

3,558,400 records

All you need to do now is Enjoy the results!