As discussed earlier, we are going to use MySQL. In any MySQL server, either local or remote, create two tables, details_by_city_country
and details_by_ip
. Let's just focus on the details_by_city_country
table. In accordance with the output from our reducer, you can create it using the following command:
CREATE TABLE details_by_city_country ( date date NOT NULL, filename varchar(100) NOT NULL, http_status_code integer NOT NULL, city varchar(100) NOT NULL, country varchar(100) NOT NULL, edge_location varchar(10) NOT NULL, request_count BIGINT NOT NULL, hit_count BIGINT NOT NULL, miss_count BIGINT NOT NULL, error_count BIGINT NOT NULL, bytes_transferred BIGINT NOT NULL );
You should combine all the output in <output-directory-path>/detailsbycitycountry/
using the following command:
zcat output-directory-path>/detailsbycitycountry/part-r-* > detailsbycitycountry.csv
Now, you can import it to MySQL using the LOAD DATA LOCAL INFILE
command of MySQL...