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.
In part 1 of this series we introduced Kentik Data Engine™, the backend to Kentik Detect™, which is a large-scale distributed datastore that is optimized for querying IP flow records (NetFlow v5/9, sFlow, IPFIX) and related network data (GeoIP, BGP, SNMP). We started our tour of KDE with a word about our database schema, and then used SQL queries to quantify how design features such as time-slice subqueries, results caching, and parallel dataseries help us achieve extraordinarily fast query performance even over huge volumes of data. In this post we’ll continue our tour of KDE, starting with filtering and aggregation. SQL gives us easy methods to do both filtering and aggregation, either by adding additional terms to the WHERE clause or by adding a GROUP BY clause. We can filter and aggregate by any combination of columns using those options. Let’s look at traffic volume for the top source → dest country pairs where both the source and dest are outside the US:
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’