Here are the slides I pre­sented for a Click­House SF Bay Area Meetup in July 2022, hosted by Al­tin­ity. They are about Akvo­rado, a net­work flow col­lec­tor and vi­su­al­izer, and no­tably on how it re­lies on Click­House, a col­umn-ori­ented database.

The meetup was recorded and avail­able on YouTube. Here is the part rel­e­vant to my pre­sen­ta­tion, with sub­titles:¹

I got a few ques­tions about how to get in­for­ma­tion from the higher lay­ers, like HTTP. As my use case for Akvo­rado was at the net­work edge, my an­swers were mostly neg­a­tive. How­ever, as sFlow is ex­ten­si­ble, when col­lect­ing flows from Linux servers in­stead, you could em­bed ad­di­tional data and they could be ex­ported as well.

I also got a ques­tion about do­ing ag­gre­ga­tion in a sin­gle ta­ble. Click­House can au­to­mat­i­cally ag­gre­gate data us­ing TTL. My an­swer for not do­ing that is par­tial. There is an­other rea­son: the re­ten­tion pe­ri­ods of the var­i­ous ta­bles may over­lap. For ex­am­ple, the main ta­ble keeps data for 15 days, but even in these 15 days, if I do a query on a 12-hour win­dow, it is faster to use the flows_​1m0s ag­gre­gated ta­ble, un­less I re­quest some­thing about ports and IP ad­dresses.

Transcript#

Hello. So I am Vin­cent Bernat. I’m a French net­work en­gi­neer work­ing at Free. I’m do­ing this pre­sen­ta­tion from Paris, France. So it’s nice to see you. I’m go­ing to present Akvo­rado, a net­work flow col­lec­tor and vi­su­al­izer backed by ClickHouse.

It was de­vel­oped in­ter­nally at Free. Free is a French ISP founded in 1999. It was one of the first to of­fer in­ter­net ac­cess with­out a sub­scrip­tion or sur­charged phone num­ber in France. It con­trib­uted to the DSL ex­pan­sion in France by driv­ing prices down. It was quite in­no­va­tive by in­tro­duc­ing the Free­box. This is the first triple play setup box in­clud­ing In­ter­net, TV and phone ser­vices. It also con­trib­uted a lot to the IPv6 adop­tion in France in 2008. The mo­bile of­fer is also quite in­no­va­tive with the big data al­lowance. You have more than 100 GB of data in­cluded and you can roam in the world with that but we are not avail­able in San Fran­cisco yet.

So Akvo­rado is a NetFlow, IPFIX, sFlow col­lec­tor. In­ter­net routers send a sam­ple of the pack­ets they re­ceived to Akvo­rado, like one packet out of 10,000.

There are sev­eral pro­to­cols for that. IPFIX is the IETF ver­sion of NetFlow. NetFlow is Cisco pro­pri­etary pro­to­col. sFlow is an­other kind of pro­to­col. The dif­fer­ence is that it does not ag­gre­gate data. It sends data in real time. But it’s a small dif­fer­ence be­cause NetFlow is also able to send data al­most in re­al­time with a good con­fig­u­ra­tion. You can have a 10s lag. So mostly you choose the pro­to­col that your router sup­port. So it’s im­por­tant to sup­port sev­eral pro­to­cols be­cause not all routers sup­port all protocols.

So Akvo­rado re­ceives that and then it en­riches the flow with ad­di­tional data. No­tably it adds GeoIP in­for­ma­tion us­ing the MaxMind data­bases, add in­ter­face names and de­scrip­tions with the SNMP pro­to­col and it adds AS num­bers. AS num­bers are the main way to iden­tify or­ga­ni­za­tion on the in­ter­net. From the IP ad­dress, you can get the AS num­ber and from the AS num­ber you can get the name.

We also add some at­tributes to the routers us­ing clas­si­fi­ca­tion rules: we add the lo­ca­tion, the role, the ten­ant of the router and we also add at­tributes to in­ter­faces: the provider, the bound­ary, the con­nec­tiv­ity (is it the tran­sit in­ter­face or a peer­ing in­ter­face). All this in­for­ma­tion are very use­ful to have when you need to query the data that you’ve collected.

And then, Akvo­rado will se­ri­al­ize the flow us­ing Pro­to­buf and send that to a Kafka clus­ter. Since re­cently, this is an open source project. We have pub­lished it on GitHub. There is also a nice web fron­tend to query the data.

Here are a few screen­shot but I will do a live demo.

You can try the demo your­self. The URL is demo.akvorado.net. It’s run­ning on a small vir­tual ma­chine. It uses fake data.

The home page is just a few met­rics to show that it works cor­rectly but it also dis­plays the last re­ceived flow. You can see what it looks like. You have a time stamp, the num­ber of bytes, the num­ber of pack­ets, the router that sent the flow. Its name.

The fol­low­ing at­tributes come from the clas­si­fi­ca­tion rules. The sam­pling rate, which is quite high for the demo. It means that the router sent one flow out of 50,000 flows it re­ceived. The source ad­dress. The source AS num­ber. GeoIP is not con­fig­ured so they don’t get the coun­try. You get the source port.

As for the in­com­ing in­ter­face, you get the name, the de­scrip­tion, the speed and also from the clas­si­fi­ca­tion rules the fact that it’s an ex­ter­nal fac­ing in­ter­face (it’s con­nected to the in­ter­net). It’s a tran­sit in­ter­face con­nected to Co­gent which is a tran­sit provider and you have the same stuff for the des­ti­na­tion ad­dress, the coun­try, and the out­go­ing in­terface.

We have EType which is mostly IPv4 or IPv6. For­ward­ing sta­tus. 64 means it has been for­warded. 128 means the packet has been dropped. So if you have fire­wall rules it’s in­ter­est­ing. And the pro­to­col: it’s 17 for UDP, 6 for TCP. So most of the fields were re­ceived from the flow but some of them were added later.

Okay. But the most in­ter­est­ing tab is vi­su­al­ize tab. Let me zoom back a bit to show the last seven days. So it an­swers the ques­tion “where does my traf­fic come from?” We can see for ex­am­ple in this demo that most of the traf­fic comes from Net­flix but also Google and Face­book. There is a fil­ter and you can add more stuff like, for ex­am­ple, I want to only see the IPv6 traf­fic. I can in­put this fil­ter and when I ap­ply, I get the IPv6 traf­fic. Be­cause all the data is fake it’s a bit dif­fi­cult to see the dif­fer­ence but you see IPv6 is at most 60Gbps of traf­fic while the whole traf­fic was about 90 Gbps.

An­other in­ter­est­ing vi­su­al­iza­tion that the web in­ter­face shows is a sankey graph. It’s an­other kind of vi­su­al­iza­tion and it shows us the top three talk­ers: Net­flix, Google and Face­book. It shows that two thirds of the traf­fic is go­ing through tran­sit providers. One third is go­ing through in­ter­net ex­changes.

In­ter­net ex­change is a place where peo­ple can con­nect to the same switch and ex­change traf­fic for free or not. But you don’t get the whole in­ter­net just by go­ing to an in­ter­net ex­change. If you want to get the whole in­ter­net you need to have a tran­sit provider.

So for ex­am­ple, we can an­swer the ques­tion “How do we get google traf­fic?” It seems that you get most of the traf­fic us­ing a tran­sit provider but a small part is go­ing through an in­ter­net ex­change. It’s also very use­ful vi­su­al­iza­tion to get for a net­work en­gi­neer. Let me get back to the pre­sentation.

How did we use Click­House? So that’s the ta­bles that we have in our setup. First a bit of warn­ing. It’s the first time I have used Click­House. I am a net­work en­gi­neer, not a data­base en­gi­neer. I have a few no­tions of data­base but very light. So feed­back is wel­come and take ev­ery­thing with a grain of salt.

So the pur­ple squares are the ta­bles, the blue ones are the views and the white one are the dic­tio­nar­ies. I was too fast. I think I for­got to tell… No, no. Okay, sorry. So like I told pre­vi­ously, flows are com­ing from Kafka on the flows_​2_raw ta­ble which is not backed by disk stor­age. There is the con­sumer ex­tract­ing the flows and send­ing them to the flows ta­ble which is the main ta­ble con­tain­ing all the flows. Then we have a few other flow ta­bles which are ag­gre­gat­ing data over time to take less space and speed up queries. I will go in more de­tails in the next slides.

So in­ges­tion is done us­ing the Kafka en­gine. We re­ceive the data us­ing the Kafka en­gine. The data is de­coded with the Pro­to­buf for­mat. Pro­to­buf schemas are ver­sioned. They are com­ing from ver­sioned top­ics from Kafka and stored in ver­sioned tables.

The flows ta­ble it­self is not ver­sioned. The flows con­sumer will nor­mal­ize data to match the data for­mat in the flows ta­ble. When there is a schema change, we in­crease the ver­sion num­ber. It al­lows seam­less up­dates. If you have old col­lec­tors run­ning in your net­work, they still work. For ex­am­ple, they will con­tinue to send the flows to the flows-v1 topic. It will be us­ing the FlowMessagev1 schema. It will be han­dled by the flows_​1_raw Kafka en­gine and the flows_​1_raw_​consumer to nor­mal­ize the data.

There is no reg­istry for the schemas. On each up­grade, you have to copy the schemas on each Click­House server. It’s a bit an­noy­ing but you don’t lose any data be­cause Kafka will buf­fer the mes­sage while Click­House is restart­ing. Click­House is able to use a reg­istry when you are us­ing Avro. But I think that it doesn’t work with Protobuf.

The main ta­ble is the flows ta­ble. Here is a par­tial view of it. Many col­umns are miss­ing. For each Src col­umn, you should have a Dst col­umn. For each InIf col­umn, you have an OutIf col­umn match­ing. There’s noth­ing spe­cial. We use LowCardinality when it makes sense and the ta­ble is or­dered by TimeReceived be­cause ev­ery queries will use this column.

The flows ta­ble in our setup keeps 15 days of data. It’s half a ter­abyte of data. It’s very slow to query for more than one hour of data and al­most im­pos­si­ble to query one day, two days, three days. It’s pos­si­ble but it’s slow. It takes many sec­onds and we don’t want that. We want an in­stant re­sponse. Also, we want to keep data for five years. This is a com­mon re­quire­ment for this kind of setup to be able to look at what hap­pened last year at the same pe­riod. To be able to do that we need to ag­gre­gate data when it gets older. Click­House ag­gre­gates data but it was not flex­i­ble enough for us.

We use an ap­proach in­spired by the round-robin data­base. Round-robin data­base (RRD) is an an­ces­tor of the time se­ries data­base. The data is stored in a cir­cu­lar buf­fer and af­ter some time the data is con­sol­i­dated us­ing a spe­cific func­tion, mostly max, min, and av­er­age. We do that. We use a sum­ming merge tree on bytes and pack­ets. We also drop IP ad­dresses and TCP and UDP ports.

We can­not keep IP ad­dresses for too long for le­gal rea­son be­cause it’s per­son­ally iden­ti­fi­able in­for­ma­tion and it’s not that in­ter­est­ing past a few days to know all the IP ad­dresses. The in­ter­est­ing parts of the IP ad­dress sur­vive through the net­work clas­si­fi­ca­tion. We can at­tach a re­gion to an IP ad­dress for ex­am­ple. Also through the in­for­ma­tion given by the AS num­bers: we know this IP ad­dress is owned by Face­book for ex­am­ple. So past a few days, we don’t keep the IP ad­dresses anymore.

TCP and UDP ports, it’s for an­other rea­son. We could have kept them but they are very ran­dom. Most of the time the source port or the des­ti­na­tion port are known, for ex­am­ple port 80 or 443 for HTTP. But the other port is ran­dom. So it’s a lot of ran­dom data, it’s not easy to know which part is a server port so it’s eas­ier to drop it and again past a few days, it’s not very in­ter­est­ing to keep the ports and it helps com­press the data far more, it helps ag­gre­gate the data more ef­fi­ciently to not have that.

Un­like RRD, we don’t keep the max­i­mum val­ues. It’s some­thing that you should do at some point. Be­cause when you zoom out… I can show you on the demo, it would be in­ter­est­ing… When you zoom out, you see it seems that I have the max at 60 Gbps of traf­fic. But if I ask for the last 30 days in­stead we see the max is a bit less. It’s be­cause it’s not re­ally a max, it’s an av­er­age at the res­o­lu­tion of the ta­ble. So an av­er­age over five min­utes and an av­er­age over one hour will give a dif­fer­ent max­i­mum value. But that’s some­thing that could be fix­able with Click­House, I think.

So we have one-minute ag­gre­gate ta­ble, a five-minute ag­gre­gate ta­ble, and a one-hour ag­gre­gate ta­ble. They keep data for seven days, 90 days, and for five years very ef­fi­ciently. Akvo­rado au­to­mat­i­cally chooses the best ta­ble de­pend­ing on the time range, the col­umns re­quested (if you re­quest a source IP ad­dress or a des­ti­na­tion ad­dress, you will have to use the main table).

I wanted to show you how we feed data to the ag­gre­gated ta­ble be­cause it shows a nice fea­ture of Click­House. You can se­lect ev­ery­thing with the star ex­cept a few col­umns. So we want to ex­clude source ad­dress, des­ti­na­tion ad­dress, source port, and des­ti­na­tion port. It’s very easy to do that and we can also re­place some col­umns. For ex­am­ple, the TimeReceived is trun­cated to the pre­vi­ous hour. It’s very nice to be able to do that.

The exporters ta­ble. It’s just a small helper ta­ble to have a list of ex­porter names, ad­dresses and in­ter­face, names and de­scrip­tions and other data. It’s mostly used for com­ple­tion for com­plet­ing user in­put when the user is writ­ing the fil­ter. I wanted to show you that be­cause it’s an­other nice fea­ture of Click­House. You have a lot of ways to ma­nip­u­late ar­rays. So with a sin­gle query, I can pop­u­late the ta­ble us­ing ARRAY JOIN and arrayEnumerate.

An­other in­ter­est­ing part is the dic­tio­nar­ies. We use three dic­tio­nar­ies. One dic­tio­nary to map AS num­bers to names. There are about 100,000 AS num­bers. From one AS num­ber you can get the name.

And we have an­other dic­tio­nary for pro­to­col num­bers to names. So pro­to­col num­ber 17 is UDP. The pro­to­col num­bers are im­mutable. AS names change very in­fre­quently. Mostly cos­metic. For ex­am­ple you can have the AS name for Twitch TV can change later to Ama­zon Twitch TV, but it’s mostly cos­metic and it’s only used for dis­play pur­pose. So we use these dic­tio­nar­ies dur­ing queries.

The third dic­tio­nary maps net­works, like this, to a name, a role, a re­gion, and a ten­ant. And this time we use this dic­tio­nary dur­ing in­ges­tion to ma­te­ri­al­ize some col­umns be­cause we don’t want his­toric data to change when you re­al­lo­cate a sub­net to a region.

The net­work clas­si­fi­ca­tion is done dur­ing in­ges­tion us­ing the ma­te­ri­al­ized view where we se­lect ev­ery­thing from what we re­ceive from Kafka and we just add the source net names, source net roles, etc., us­ing the dic­tio­nary. The networks dic­tio­nary is us­ing the IP_​TRIE lay­out. It means that given an IP Ad­dress, Click­House is able to do a very fast IP lookup to se­lect the right network.

These are not the only col­umns gen­er­ated from other data. If you re­mem­ber we have also data gen­er­ated from GeoIP lo­ca­tion, from AS num­bers and from clas­si­fi­ca­tion. It’s not done in Click­House for GeoIP data. It could have been done in Click­House us­ing the same sys­tem. It would have worked but it was easy to do that in Akvo­rado di­rectly. But the clas­si­fi­ca­tion is done with user-pro­vided rules. So this time it’s eas­ier to do that in the back­end. Not in ClickHouse.

So if you re­mem­ber the demo, the user in­puts a time range, col­umns, (in the web in­ter­face as they are called di­men­sions) and the fil­ter ex­pression.

And the fil­ter ex­pres­sion is also some­thing that I find in­ter­est­ing. Our users are net­work en­gi­neers, they may know SQL, but they are not flu­ent in SQL but they know it well enough that we are us­ing for fil­ters a SQL-like lan­guage that we trans­late to the Click­House SQL us­ing a parser.

Our do­main is sim­pler. So we can take short­cuts. We can sim­plify some as­pects. For ex­am­ple the IP ad­dress does not need to be quoted. You can use dou­ble quotes or sin­gle quotes. It doesn’t make a dif­fer­ence. You can use some con­stant. EType should be an in­te­ger but we can give a name in this case. Ev­ery­thing to be eas­ier to use. And the parser will trans­late that to Click­House SQL.

It’s also safe be­cause we parse the fil­ter and then we build the SQL query. If you use an un­known col­umn, you make a syn­tax er­ror or some­thing like that, the parser will fail with an er­ror mes­sage and no query is built. You can­not in­ject any­thing in Click­House. Even if Click­House is not very vul­ner­a­ble to in­jec­tion at­tacks, it’s not pos­si­ble be­cause the parser needs to un­der­stand what you want and it trans­lates that to ClickHouse.

Then the whole user query is trans­lated to Click­House SQL. Click­House helps a lot to re­turn data that would be di­rectly ex­ploitable by the fron­tend. For ex­am­ple, when some val­ues are miss­ing, we can fill it au­to­mat­i­cally with zero val­ues. That’s what is done here.

You no­tice that I am us­ing the ag­gre­gated ta­ble with the five-minute ag­gre­ga­tion. There is a magic value here which is 600. We adapt the res­o­lu­tion re­quested by the user, which may be 632 sec­onds, one value ev­ery 632 sec­onds. We adapt it to match the res­o­lu­tion of the ta­ble. It should be a mul­ti­ple of the res­o­lu­tion of the ta­ble for data to look good. So since the res­o­lu­tion is 300 sec­onds and the user re­quested about one point ev­ery 600 sec­onds, we will give them one point ev­ery 600 seconds.

And there is a sub­query. The user re­quested to get the source AS num­bers. We se­lect the top 10 AS num­bers match­ing the query with same time range and the same fil­ter. We will se­lect the top AS num­bers. For each AS num­ber that we get, if it’s one of the top 10, we will dis­play the AS num­ber along with its name with the dic­tio­nary. Oth­er­wise, we just dis­play Others. This way we don’t have an in­fi­nite list of AS num­bers re­turned to the user.

Let’s switch to the back end. It’s writ­ten in Go. And we are us­ing clickhouse-go/v2 bind­ings which is us­ing the na­tive client server pro­to­col. It’s a low level in­ter­face but it’s fine for us be­cause ab­strac­tions of­ten come with re­stric­tions. The doc­u­men­ta­tion is pretty poor. It’s not up to date with the Go stan­dard but there are a few ex­am­ples to un­der­stand how it works and oth­er­wise it works very well.

We write a lot of unit tests but they are not run against the real data­base. For each query we pro­vide canned re­sults by us­ing a mock gen­er­ated with GoMock. For ex­am­ple, dur­ing the test, this query is is­sued. GoMock will gen­er­ate a fake func­tion that will an­swer this re­sult: customer-1, customer-2, customer-3. It en­ables us to do a lot of tests, very fast with­out re­ly­ing on an ex­ter­nal database.

The last thing that I wanted to men­tion are the mi­gra­tions. With a reg­u­lar data­base, a prod­uct of­ten start mi­gra­tions when you up­date. For Click­House, it may be a bit more com­plex be­cause you can­not do what­ever you want. So we have an or­ches­tra­tor which man­ages the dif­fer­ent in­ter­nal and ex­ter­nal com­po­nent, in­clud­ing Click­House and Kafka. It man­ages the schema mi­gra­tions and it’s done with Go code. Each mi­gra­tion step has a de­scrip­tion, a test and a func­tion. There is no state: each mi­gra­tion step is ex­e­cuted on start. And the test says if a step can be skipped or not. It’s only man­ag­ing for­ward mi­grations.

The cur­rent steps cre­ate dic­tio­nar­ies: pro­to­col, AS num­bers, and net­work dic­tio­nary, cre­ate the flows ta­bles, the main one and the ag­gre­gated ones. If you up­grade from an old schema, there is a step to add the miss­ing col­umns. There is a step to cre­ate con­sumer flows ta­bles. There is a step to con­fig­ure the TTL as­so­ci­ated with ta­ble. This means that if the user con­fig­ures a dif­fer­ent TTL and restarts, the TTLs will be up­dated. And then a step for the exporters view, the raw flows ta­ble, etc.

There are two dif­fer­ent kinds of steps. The ones that will mod­ify the dic­tio­nar­ies and the views. We don’t need to keep data for this one. We do two checks. We check if the ta­ble ex­ists. The ta­ble, the view, or the dic­tio­nary ex­ists and we check if the ta­ble has the right col­umns at the right po­si­tions. So it’s done by hash­ing the name, type, po­si­tion from the sys­tem columns ta­ble and check­ing against an hard-coded value. So it’s quite nice that Click­House ex­poses a lot of things in the sys­tem ta­bles be­cause it en­ables us to do that.

And the sec­ond kind of steps that we can have is when we want to keep ex­ist­ing data. So in this case we use mu­ta­tions. We test “do we have the col­umn that we have to add?” If not then we use ALTER TABLE to add the col­umn. There are many lim­its of what you can mu­tate but with some com­pro­mises, un­til to­day, we were able to do what we wanted to do.

Mi­gra­tions are tested. They are part of the au­to­matic tests. There is a Click­House data­base spawned in a con­tainer and mi­gra­tions are tested from var­i­ous states in­clud­ing from an empty data­base. Each test must get the same fi­nal state and each time a mi­gra­tion step is added, the fi­nal state is recorded to be used in fu­ture tests us­ing the query on the slide. This en­ables us to en­sure that the user is able to mi­grate from one ver­sion to any other version.

Our setup is quite small. We are run­ning ev­ery­thing on a sin­gle VM in­clud­ing Kafka and Akvo­rado it­self. Ev­ery­thing is run­ning in con­tain­ers us­ing docker-compose. So it’s a very sim­ple setup. One ter­abyte of disk, 64 GB of RAM. Cur­rently it’s 30,000 flows/s and the tar­get is 100,000 flows/s. You see some us­age graph for CPU, mem­ory, and disk. Ev­ery­thing is quite low but there is a DDoS sys­tem run­ning in the back­ground, ev­ery five sec­onds, do­ing re­quests and it’s the one gen­er­at­ing most of the load.

To con­clude, my opin­ions about Click­House. It’s a great out of the box ex­pe­ri­ence, great doc­u­men­ta­tion. There are many builtin func­tions avail­able. String func­tions that con­verts to hu­man read­able strings. It re­ally feels like that this is some­thing where us­abil­ity prob­lems are solved di­rectly in Click­House in­stead of hav­ing an­other layer for that. It feels a bit like magic.

An­other pop­u­lar way to do the same thing is to use ElasticSearch. Un­like ElasticSearch, Click­House is very fast with­out much ef­fort and stays very fast. Man­ag­ing an ElasticSearch clus­ter is far more dif­fi­cult for this kind of us­age. Ag­gre­gat­ing merge ta­bles take some time to un­der­stand how they work and it’s easy to do some­thing that looks right but that isn’t. For ex­am­ple if you ag­gre­gate us­ing av­er­age, you don’t get what you ex­pect. Click­House has func­tions to help you do­ing that.

So that’s all for me. I wel­come a few ques­tions if you want.

Questions#

Vin­cent, that was an ab­so­lutely awe­some talk and we do have a cou­ple ques­tions queued up. I can see them. We have a cou­ple from Gi­lad. So the first one is Akvo­rado only parses layer three pro­to­cols, how does it de­tect which ap­pli­ca­tion it is?

It’s a lim­i­ta­tion of us­ing the net­work pro­to­cols NetFlow/IPFIX/sFlow pro­to­cols. These flow col­lec­tor pro­to­cols they col­lect layer 4 in­for­ma­tion. NetFlow/IPFIX are lim­ited to layer 4 in­for­ma­tion plus some meta­data like the AS num­bers. sFlow is able to pick more in­side the pack­ets. You can con­fig­ure it to get the layer 4 head­ers as well as 200 bytes of each sam­pled packet. You can do that but cur­rently Akvo­rado is not us­ing that.

You can guess your ap­pli­ca­tion us­ing ports or us­ing IP ad­dress. It de­pends. If for ex­am­ple you have a Ku­ber­netes clus­ter, the IP ad­dress should be able to give you the tar­get ap­pli­ca­tion. You can use that. Cur­rently, you don’t know for sure that it’s HTTP, you don’t know which HTTP re­quest was done. Um it’s not this kind of tool, yet.

Cool. The next ques­tion from Gi­lad has my name on it but I can’t re­ally an­swer it. Gi­lad said would you sug­gest hav­ing a sin­gle ta­ble in­stead of all three ta­bles. Gi­lad can you just speak up. I think you should be able to talk, you can de­scribe what you had in mind there.

So yes. Now I see you have three ta­bles, one for one-minute ag­gre­ga­tion, one for five-minute ag­gre­ga­tion, one for one-hour ag­gre­ga­tion. Have you tested us­ing one ta­ble, with TTL and ag­gre­gate one minute old to be five min­utes old and five min­utes old to be one hour old and all on the same ta­ble hav­ing one col­umn to iden­tify the bucket size?

Yes. I have tried that but when you ag­gre­gate data in­side the same ta­ble there is a re­quire­ment that the pri­mary key… I may be wrong but I didn’t suc­ceed to do that be­cause there is a re­quire­ment that the pri­mary key does not change.

In the pri­mary key, we have the TimeReceived and I want to trun­cate that to the near­est minute, near­est five min­utes and near­est hour. I can for ex­am­ple I can have one col­umn with TimeReceived, then I need an­other col­umn TimeReceived trun­cated at the minute, TimeReceived trun­cated at five min­utes and TimeReceived trun­cated at one hour. I need to have the three col­umns to ex­ist to be able to do the ag­gre­ga­tion into the same ta­ble and it seems more com­plex but maybe it would be a bet­ter ap­proach. I would be in­ter­ested to get feed­back on that.

Maybe it would have worked, but be­cause it can­not be mod­i­fied, it means that if I want to add a new kind of ag­gre­ga­tion for ex­am­ple, I want to ag­gre­gate at 10 min­utes, I can­not mod­ify the ta­ble to do that be­cause the pri­mary key can­not change. So that’s the main rea­son that I did go for three ta­bles even if it makes ap­pli­ca­tion a bit more com­plex be­cause you have to se­lect the right table.

So I just want to say that we have a so­lu­tion close to this and we did one ta­ble but we haven’t tested the per­for­mance about many ta­bles ver­sus one ta­ble. So it was in­ter­est­ing to ask.

Gi­lad, you had an­other ques­tion which to Vin­cent, which is how did you de­ploy Click­House, did you use Ku­ber­netes and Vin­cent, it looked like you were us­ing docker-compose. Did I see that correctly?

Yes, be­cause cur­rently it’s still a bit of a PoC but it’s work­ing so well that we didn’t try to do bet­ter. I wasn’t ex­pect­ing to be able to fit all our data on a sin­gle VM. But so cur­rently it’s a bit of proof of con­cept but it has gone to pro­duc­tion this way. So it’s a sin­gle VM us­ing docker-compose. You do docker-compose up and you get your work­ing set up and with­out a lot of mem­ory. So ev­ery­thing is run­ning into con­tain­ers in­clud­ing Click­House. So there is no clus­ter, it’s a sin­gle-node Click­House de­ploy­ment currently.

Cool. Yeah, that’s a great, yeah, that’s a re­ally great ex­am­ple. I have a ques­tion. I don’t see any ques­tions on Youtube. If you folks want to ask some­thing, feel free to throw it into the chat. Vin­cent, I had one. The UI that you have looks re­ally great. How did you build that?

It was a bit of pain be­cause I am not a fron­tend de­vel­oper. It is vue.js. This one. It’s this frame­work. With TailwindCSS. This frame­work. That’s mostly all. If you want to look, I think it’s ex­plained a bit more in the doc­u­men­ta­tion. It’s open source, you can have a bet­ter look. An im­por­tant com­po­nent are the graphs. It’s ECharts. It’s an Apache project which is called ECharts. That’s the one han­dling the graphs and it’s a very great project if you need to do some fron­tend work with graphs. It’s very ro­bust and very ver­sa­tile and it took me some time to dis­cover it.

Yeah, it looks awe­some and I like the in­ter­ac­tiv­ity of it is out­stand­ing. It’s re­ally cool. Good. Is there any other ques­tions to Vin­cent be­fore we pro­ceed to the next talk? Yes, they have an­other one. Do you have a plan to parse HTTP? So to in­ves­ti­gate Layer 7.

At Free, we are us­ing the NetFlow pro­to­col and the NetFlow pro­to­col doesn’t al­low to peak at the layer seven. We would have to use sFlow and we are an in­ter­net ser­vice provider and we don’t need that. So there is no plan yet. It’s mostly for telco net­work en­gi­neers or dat­a­cen­ter en­gi­neers. It could be in­ter­est­ing but no plan.

Nice. Great. Thank you. Al­right, thanks for the great ques­tions and I think I don’t see any fur­ther ques­tions queued up right now un­less I’m miss­ing some­thing. Vin­cent, thank you so much for a to­tally awe­some talk. This is re­ally en­light­en­ing and I think we can stamp you as an hon­orary data­base en­gi­neer. You did ev­ery­thing great.