SQLite Notes

 

Open a database:

$sqlite3 peeringdb.sqlite3 

 

Get helps:

.help

 

List tables:

.tables

 

See table schema:

.schema peeringdb_network

# or

sqlite> PRAGMA table_info (peeringdb_network);
0|id|INTEGER|1||1
1|status|varchar(255)|1||0
2|created|datetime|1||0
3|updated|datetime|1||0
4|version|INTEGER|1||0
5|asn|integer unsigned|1||0
6|name|varchar(255)|1||0
7|aka|varchar(255)|1||0
8|irr_as_set|varchar(255)|1||0
9|website|varchar(255)|1||0
10|looking_glass|varchar(255)|1||0
11|route_server|varchar(255)|1||0
12|notes|TEXT|1||0
13|notes_private|TEXT|1||0
14|info_traffic|varchar(39)|1||0
15|info_ratio|varchar(45)|1||0
16|info_scope|varchar(39)|1||0
17|info_type|varchar(60)|1||0
18|info_prefixes4|integer unsigned|0||0
19|info_prefixes6|integer unsigned|0||0
20|info_unicast|bool|1||0
21|info_multicast|bool|1||0
22|info_ipv6|bool|1||0
23|policy_url|varchar(255)|1||0
24|policy_general|varchar(72)|1||0
25|policy_locations|varchar(72)|1||0
26|policy_ratio|bool|1||0
27|policy_contracts|varchar(36)|1||0
28|org_id|INTEGER|1||0
29|info_never_via_route_servers|bool|1||0
30|name_long|varchar(255)|1||0
31|status_dashboard|varchar(255)|0||0
32|rir_status|varchar(255)|0||0
33|rir_status_updated|datetime|0||0

 

Run a query:

select irr_as_set,info_prefixes4,info_prefixes6 FROM peeringdb_network limit 10;

 

Where:

select irr_as_set from peeringdb_network where irr_as_set is not null and irr_as_set != "" order by irr_as_set limit 100;
select irr_as_set from peeringdb_network where irr_as_set not like "AS-%" and irr_as_set != "" order by irr_as_set limit 100;

 

Get the averageas (median, mean, mode):

# get the highest or lowest value using ASC or DESC:
sqlite> select info_prefixes6 FROM peeringdb_network order by info_prefixes6 desc limit 1;
160000

sqlite> select count(*) from peeringdb_network;
26784

sqlite> select count(info_prefixes6) from peeringdb_network where info_prefixes6 !=0;
15766

sqlite> select count(info_prefixes4) from peeringdb_network where info_prefixes4 !=0;
18656

# Median - element right in the middle of a set - when the set has an odd number of entires:
sqlite> select info_prefixes6 FROM peeringdb_network order by info_prefixes6 limit 1 offset (SELECT COUNT(*) FROM peeringdb_network) / 2;
1

# Median - average of the two middle elemenents when the set has an even number of entries:
sqlite> select avg(info_prefixes6) from (select info_prefixes6 FROM peeringdb_network order by info_prefixes6 limit 2 offset (SELECT (COUNT(*) -1) /2 FROM peeringdb_network));
1.0

# Exclude zero values:
sqlite> select avg(info_prefixes6) from (select info_prefixes6 FROM peeringdb_network where info_prefixes6 != 0 order by info_prefixes6 limit 2 offset (SELECT (COUNT(*) -1) /2 FROM peeringdb_network));
100.0

sqlite> select avg(info_prefixes4) from (select info_prefixes4 FROM peeringdb_network where info_prefixes4 != 0 order by info_prefixes4 limit 2 offset (SELECT (COUNT(*) -1) /2 FROM peeringdb_network));
100.0


# Mode - most common value - excluding 0 values
sqlite> select count(*),info_prefixes4 from (select info_prefixes4 from peeringdb_network where info_prefixes4 != 0) group by info_prefixes4 order by count(*) desc limit 10;
1877|1
1391|100
1384|10
1156|4
1094|2
842|20
807|5
783|50
580|1000
572|7

sqlite> select count(*),info_prefixes6 from (select info_prefixes6 from peeringdb_network where info_prefixes6 != 0) group by info_prefixes6 order by count(*) desc limit 10;
4399|1
1573|10
1152|100
897|5
852|2
807|50
673|20
510|3
472|32
409|1000

# 50th percentile - excluding 0 values
sqlite> select info_prefixes4 from peeringdb_network where info_prefixes4 != 0 order by info_prefixes4 asc limit 1 offset (select count(*) from peeringdb_network where info_prefixes4 != 0) * 5 / 10 -1;
20

sqlite> select info_prefixes6 from peeringdb_network where info_prefixes6 != 0 order by info_prefixes6 asc limit 1 offset (select count(*) from peeringdb_network where info_prefixes6 != 0) * 5 / 10 -1;
10

# 98.5th pecentile - excluding 0 values
sqlite> select info_prefixes4 from peeringdb_network where info_prefixes4 != 0 order by info_prefixes4 asc limit 1 offset ((select count(info_prefixes4) from peeringdb_network where info_prefixes4 != 0) / 100 * 98.5) - 1;
27000

sqlite> select info_prefixes6 from peeringdb_network where info_prefixes6 != 0 order by info_prefixes6 asc limit 1 offset ((select count(info_prefixes6) from peeringdb_network where info_prefixes6 != 0) / 100 * 99) - 1;
5000

 

---