Infobright Blog

Recently, I published a blog about a free, machine-generated dataset.  It's a great dataset to experiment with Infobright.  To get you started, I wanted to provide additional information on how to load the data into Infobright.  I've also provided some tweaks (using DomainExpert and lookup columns) which will help speed up your queries.

1. Download the data (http://www.fcc.gov/measuring-broadband-america).  You will want the monthly data (each month has its own file) along with the dimension unit metadata (three total files).
2. To use the steps below, unpack the data into it the following directory structure.  You can modify the storage locations to your data structure, but be sure to change the load statements below.
   
   • /data/fcc/2011[MM] where MM is the current month
   • /data/fcc/metadata for the three unit metadata (unit_masked_ip, unit_metadata, unit_tz)
3. Within Infobright, create a database 'fcc'
   • create database fcc;
4. Create your tables:

CREATE TABLE fcc.optimized_curr_httppostmt (unit_id int comment 'lookup', dtime timestamp, target varchar(500) comment 'lookup', fetch_time float, bytes_total float, bytes_sec float, warmup_time float, warmup_bytes float, sequence int, threads int, successes int, failures int );

create table fcc.optimized_curr_avail (unit_id int comment 'lookup', dtime timestamp, successes int, failures int);

create table fcc.optimized_curr_dlping (unit_id int comment 'lookup', dtime timestamp, target varchar(500) comment 'lookup', rtt_avg float, rtt_min float, rtt_max float, rtt_std float, successes int, failures int);

create table fcc.optimized_curr_dns (unit_id int comment 'lookup', dtime timestamp, nameserver varchar(200) comment 'lookup', lookup_host varchar(500) comment 'lookup', response_ip int, rtt int, successes int, failures int);

create table fcc.optimized_curr_httpgetmt (unit_id int comment 'lookup', dtime timestamp, target varchar(500) comment 'lookup', fetch_time float, bytes_total float, bytes_sec float, warmup_time float, warmup_bytes float, sequence int, threads int, successes int, failures int);

create table fcc.optimized_curr_netusage (unit_id int comment 'lookup', dtime timestamp, wan_r_bytes float, wan_tx_bytes float, sk_rx_bytes float, sk_tx_bytes float);

create table fcc.optimized_curr_ping (unit_id int comment 'lookup', dtime timestamp, target varchar(500) comment 'lookup', rtt_avg float, rtt_min float, rtt_max float, rtt_std float, successes int, failures int);

create table fcc.optimized_curr_udpjitter (unit_id int comment 'lookup', dtime timestamp, target varchar(500) comment 'lookup', packet_size float, stream_rate float, duration float, packet_up_sent int, packets_down_sent int, packets_up_recv int, packets_down_recv int, jitter_up float, jitter_down float, latency float, successes int, failures int);

create table fcc.optimized_curr_udplatency (unit_id int comment 'lookup', dtime timestamp, target varchar(500) comment 'lookup', rtt_avg float, rtt_min float, rtt_max float, rtt_std float, successes int, failures int);

create table fcc.optimized_curr_ulping (unit_id int comment 'lookup', dtime timestamp, target varchar(500) comment 'lookup', rtt_avg float, rtt_min float, rtt_max float, rtt_std float, successes int, failures int);

create table fcc.optimized_curr_videostream (unit_id int comment 'lookup', dtime timestamp, target varchar(500) comment 'lookup', downthrpt float, downjitter float, latency float, jitter float, buffer_underruns float, buffer_delay float, buffer_filltime float, duration float, bitrate float, buffer_size float, successes int, failures int);

create table fcc.optimized_curr_webget (unit_id int comment 'lookup', dtime timestamp, target varchar(500) comment 'lookup', fetch_time float, bytes_total float, bytes_sec float, objects int, successes int, failures int);

create table fcc.optimized_unit_metadata (unit_id int comment 'lookup', isp varchar(200) comment 'lookup', technology varchar(50) comment 'lookup', validated_by varchar(100) comment 'lookup', isp_down float, isp_up float, state varchar(10) comment 'lookup', region varchar(50) comment 'lookup');

create table fcc.optimized_unit_tz (unit_id int comment 'lookup', tz int, dst int);

create table fcc.optimized_unit_masked_ip (unit_id int comment 'lookup', masked_ip varchar(20) comment 'lookup');
5. Create your Domain Experts (Only available in ICE 4.0+):    CALL sys_infobright.create_rule('masked_ip', '%d.%d.%d.XXX', 'Masked IP Addresses');
   CALL sys_infobright.create_rule('uri', '%s://%s.%s.%s', 'URI');
   CALL sys_infobright.create_rule('uri_short', '%s.%s.%s', 'URI');
   CALL sys_infobright.set_decomposition_rule('fcc', 'optimized_unit_masked_ip', 'masked_ip', 'masked_ip');
   CALL sys_infobright.set_decomposition_rule('fcc', 'optimized_curr_httppostmt', 'target', 'uri');
   CALL sys_infobright.set_decomposition_rule('fcc', 'optimized_curr_httpgetmt', 'target', 'uri');
   CALL sys_infobright.set_decomposition_rule('fcc', 'optimized_curr_ping', 'target', 'uri');
   CALL sys_infobright.set_decomposition_rule('fcc', 'optimized_curr_udpjitter', 'target', 'uri');
   CALL sys_infobright.set_decomposition_rule('fcc', 'optimized_curr_udplatency', 'target', 'uri');
   CALL sys_infobright.set_decomposition_rule('fcc', 'optimized_curr_ulping', 'target', 'uri');
   CALL sys_infobright.set_decomposition_rule('fcc', 'optimized_curr_videostream', 'target', 'uri');
   CALL sys_infobright.set_decomposition_rule('fcc', 'optimized_curr_webget', 'target', 'uri');
   CALL sys_infobright.set_decomposition_rule('fcc', 'optimized_curr_dlping', 'target', 'uri');
   CALL sys_infobright.set_decomposition_rule('fcc', 'optimized_curr_dns', 'nameserver', 'IPv4_C');
   CALL sys_infobright.set_decomposition_rule('fcc', 'optimized_curr_dns', 'lookup_host', 'uri_short');
6. Load the data into the database:

load data infile '/data/fcc/201102/curr_httppostmt.csv' into table fcc.optimized_curr_httppostmt fields terminated by ',' enclosed by '"';
load data infile '/data/fcc/201102/curr_avail.csv' into table fcc.optimized_curr_avail fields terminated by ',' enclosed by '"';
load data infile '/data/fcc/201102/curr_dlping.csv' into table fcc.optimized_curr_dlping fields terminated by ',' enclosed by '"';
load data infile '/data/fcc/201102/curr_dns.csv' into table fcc.optimized_curr_dns fields terminated by ',' enclosed by '"';
load data infile '/data/fcc/201102/curr_httpgetmt.csv' into table fcc.optimized_curr_httpgetmt fields terminated by ',' enclosed by '"';
load data infile '/data/fcc/201102/curr_netusage.csv' into table fcc.optimized_curr_netusage fields terminated by ',' enclosed by '"';
load data infile '/data/fcc/201102/curr_ping.csv' into table fcc.optimized_curr_ping fields terminated by ',' enclosed by '"';
load data infile '/data/fcc/201102/curr_udpjitter.csv' into table fcc.optimized_curr_udpjitter fields terminated by ',' enclosed by '"';
load data infile '/data/fcc/201102/curr_udplatency.csv' into table fcc.optimized_curr_udplatency fields terminated by ',' enclosed by '"';
load data infile '/data/fcc/201102/curr_ulping.csv' into table fcc.optimized_curr_ulping fields terminated by ',' enclosed by '"';
load data infile '/data/fcc/201102/curr_videostream.csv' into table fcc.optimized_curr_videostream fields terminated by ',' enclosed by '"';
load data infile '/data/fcc/201102/curr_webget.csv' into table fcc.optimized_curr_webget fields terminated by ',' enclosed by '"';

load data infile '/data/fcc/201103/curr_httppostmt.csv' into table fcc.optimized_curr_httppostmt fields terminated by ',' enclosed by '"';
load data infile '/data/fcc/201103/curr_avail.csv' into table fcc.optimized_curr_avail fields terminated by ',' enclosed by '"';
load data infile '/data/fcc/201103/curr_dlping.csv' into table fcc.optimized_curr_dlping fields terminated by ',' enclosed by '"';
load data infile '/data/fcc/201103/curr_dns.csv' into table fcc.optimized_curr_dns fields terminated by ',' enclosed by '"';
load data infile '/data/fcc/201103/curr_httpgetmt.csv' into table fcc.optimized_curr_httpgetmt fields terminated by ',' enclosed by '"';
load data infile '/data/fcc/201103/curr_netusage.csv' into table fcc.optimized_curr_netusage fields terminated by ',' enclosed by '"';
load data infile '/data/fcc/201103/curr_ping.csv' into table fcc.optimized_curr_ping fields terminated by ',' enclosed by '"';
load data infile '/data/fcc/201103/curr_udpjitter.csv' into table fcc.optimized_curr_udpjitter fields terminated by ',' enclosed by '"';
load data infile '/data/fcc/201103/curr_udplatency.csv' into table fcc.optimized_curr_udplatency fields terminated by ',' enclosed by '"';
load data infile '/data/fcc/201103/curr_ulping.csv' into table fcc.optimized_curr_ulping fields terminated by ',' enclosed by '"';
load data infile '/data/fcc/201103/curr_videostream.csv' into table fcc.optimized_curr_videostream fields terminated by ',' enclosed by '"';
load data infile '/data/fcc/201103/curr_webget.csv' into table fcc.optimized_curr_webget fields terminated by ',' enclosed by '"';
load data infile '/data/fcc/201104/curr_httppostmt.csv' into table fcc.optimized_curr_httppostmt fields terminated by ',' enclosed by '"';

load data infile '/data/fcc/201104/curr_avail.csv' into table fcc.optimized_curr_avail fields terminated by ',' enclosed by '"';
load data infile '/data/fcc/201104/curr_dlping.csv' into table fcc.optimized_curr_dlping fields terminated by ',' enclosed by '"';
load data infile '/data/fcc/201104/curr_dns.csv' into table fcc.optimized_curr_dns fields terminated by ',' enclosed by '"';
load data infile '/data/fcc/201104/curr_httpgetmt.csv' into table fcc.optimized_curr_httpgetmt fields terminated by ',' enclosed by '"';
load data infile '/data/fcc/201104/curr_netusage.csv' into table fcc.optimized_curr_netusage fields terminated by ',' enclosed by '"';
load data infile '/data/fcc/201104/curr_ping.csv' into table fcc.optimized_curr_ping fields terminated by ',' enclosed by '"';
load data infile '/data/fcc/201104/curr_udpjitter.csv' into table fcc.optimized_curr_udpjitter fields terminated by ',' enclosed by '"';
load data infile '/data/fcc/201104/curr_udplatency.csv' into table fcc.optimized_curr_udplatency fields terminated by ',' enclosed by '"';
load data infile '/data/fcc/201104/curr_ulping.csv' into table fcc.optimized_curr_ulping fields terminated by ',' enclosed by '"';
load data infile '/data/fcc/201104/curr_videostream.csv' into table fcc.optimized_curr_videostream fields terminated by ',' enclosed by '"';
load data infile '/data/fcc/201104/curr_webget.csv' into table fcc.optimized_curr_webget fields terminated by ',' enclosed by '"';

load data infile '/data/fcc/201105/curr_httppostmt.csv' into table fcc.optimized_curr_httppostmt fields terminated by ',' enclosed by '"';
load data infile '/data/fcc/201105/curr_avail.csv' into table fcc.optimized_curr_avail fields terminated by ',' enclosed by '"';
load data infile '/data/fcc/201105/curr_dlping.csv' into table fcc.optimized_curr_dlping fields terminated by ',' enclosed by '"';
load data infile '/data/fcc/201105/curr_dns.csv' into table fcc.optimized_curr_dns fields terminated by ',' enclosed by '"';
load data infile '/data/fcc/201105/curr_httpgetmt.csv' into table fcc.optimized_curr_httpgetmt fields terminated by ',' enclosed by '"';
load data infile '/data/fcc/201105/curr_netusage.csv' into table fcc.optimized_curr_netusage fields terminated by ',' enclosed by '"';
load data infile '/data/fcc/201105/curr_ping.csv' into table fcc.optimized_curr_ping fields terminated by ',' enclosed by '"';
load data infile '/data/fcc/201105/curr_udpjitter.csv' into table fcc.optimized_curr_udpjitter fields terminated by ',' enclosed by '"';
load data infile '/data/fcc/201105/curr_udplatency.csv' into table fcc.optimized_curr_udplatency fields terminated by ',' enclosed by '"';
load data infile '/data/fcc/201105/curr_ulping.csv' into table fcc.optimized_curr_ulping fields terminated by ',' enclosed by '"';
load data infile '/data/fcc/201105/curr_videostream.csv' into table fcc.optimized_curr_videostream fields terminated by ',' enclosed by '"';
load data infile '/data/fcc/201105/curr_webget.csv' into table fcc.optimized_curr_webget fields terminated by ',' enclosed by '"';

load data infile '/data/fcc/201106/curr_httppostmt.csv' into table fcc.optimized_curr_httppostmt fields terminated by ',' enclosed by '"';
load data infile '/data/fcc/201106/curr_avail.csv' into table fcc.optimized_curr_avail fields terminated by ',' enclosed by '"';
load data infile '/data/fcc/201106/curr_dlping.csv' into table fcc.optimized_curr_dlping fields terminated by ',' enclosed by '"';
load data infile '/data/fcc/201106/curr_dns.csv' into table fcc.optimized_curr_dns fields terminated by ',' enclosed by '"';
load data infile '/data/fcc/201106/curr_httpgetmt.csv' into table fcc.optimized_curr_httpgetmt fields terminated by ',' enclosed by '"';
load data infile '/data/fcc/201106/curr_netusage.csv' into table fcc.optimized_curr_netusage fields terminated by ',' enclosed by '"';
load data infile '/data/fcc/201106/curr_ping.csv' into table fcc.optimized_curr_ping fields terminated by ',' enclosed by '"';
load data infile '/data/fcc/201106/curr_udpjitter.csv' into table fcc.optimized_curr_udpjitter fields terminated by ',' enclosed by '"';
load data infile '/data/fcc/201106/curr_udplatency.csv' into table fcc.optimized_curr_udplatency fields terminated by ',' enclosed by '"';
load data infile '/data/fcc/201106/curr_ulping.csv' into table fcc.optimized_curr_ulping fields terminated by ',' enclosed by '"';
load data infile '/data/fcc/201106/curr_videostream.csv' into table fcc.optimized_curr_videostream fields terminated by ',' enclosed by '"';
load data infile '/data/fcc/201106/curr_webget.csv' into table fcc.optimized_curr_webget fields terminated by ',' enclosed by '"';

load data infile '/data/fcc/metadata/unit_metadata.csv' into table fcc.optimized_unit_metadata fields terminated by ',' enclosed by '"';
load data infile '/data/fcc/metadata/unit_tz.csv' into table fcc.optimized_unit_tz fields terminated by ',' enclosed by '"';
load data infile '/data/fcc/metadata/unit_masked_ip.csv' into table fcc.optimized_unit_masked_ip fields terminated by ',' enclosed by '"';

I'm still generating benchmarks on this data.  I plan to compare Infobright with lookups/domainexperts as compared to Infobright without these optimizations.  I hope to release that information sometime by next week.