Inside the Kentik Data Engine, Part 2

Summary
In part 2 of our tour of Kentik Data Engine, the distributed backend that powers Kentik Detect, we continue our look at some of the key features that enable extraordinarily fast response to ad hoc queries even over huge volumes of data. Querying KDE directly in SQL, we use actual query results to quantify the speed of KDE’s results while also showing the depth of the insights that Kentik Detect can provide.
SELECT src_geo,
dst_geo,
Sum(both_bytes) AS f_sum_both_bytes
FROM big_backbone_router
WHERE src_geo <> ‘US’
AND dst_geo <> ‘US’
AND i_start_time > now() - interval ‘1 week’
GROUP BY src_geo,
dst_geo
ORDER BY f_sum_both_bytes DESC
LIMIT 10
|** src_geo | dst_geo | f_sum_both_bytes **|
| HK | BR | 27568963549063 |
| GB | BR | 8594666838327 |
| NL | DE | 6044367035356 |
| HK | GB | 6004897386415 |
| HK | SG | 5305439621766 |
| HK | CO | 4893091337832 |
| NL | BR | 4330923877223 |
| HK | JP | 4086102823771 |
| HK | PL | 3833512917644 |
| HK | TR | 3501243783418 |
SELECT 10
Time: 0.675s
The first row, HK (Hong Kong) → BR (Brazil) seems like an interesting, unlikely pair. Let’s filter on that and see who was talking to whom from a network/ASN perspective:
SELECT src_as,
i_src_as_name,
dst_as,
i_dst_as_name,
Sum(both_bytes) AS f_sum_both_bytes
FROM big_backbone_router
WHERE src_geo = ‘HK’
AND dst_geo = ‘BR’