DEV Community: David G. Simmons

How to avoid bricking your device during update rollouts

David G. Simmons — Fri, 20 Sep 2024 15:29:21 +0000

Having an update brick )render inoperable) your device is a real risk, and even the largest of companies have been known to have it happen. This has just happened to Apple (Apple pauses iPadOS 18 rollout for M4 iPad Pro after bricking complaints and I think we're all well aware of the Crowdstrike incident by now.

It's such an issue that, given the recent disasters with update rollouts (which I wrote about briefly here) it seemed like a good topic to dive into.

I've always believed that if you can't come at a problem with a solution, you're probably not helping, so I looked at ways to actually solve the problem of updates that "go bad" and how to implement better strategies for deploying them.

Most of what I said is, of course, not new, or earth-shattering but it is worth taking a hard look at if you are deploying large numbers of devices that will at any time need to be managed and updated in the field. Let's face it, at some point something will go wrong. It always does. So planning for how to recover from it before it happens is prudent, and shows your customers that you are looking after them and their interests. It's really putting your customers first by ensuring that they can see you as a trusted partner over the long haul.

I cover some key topics like A/B partitioning, roll-backs after failed updates, and others, but I'm not going to go into all the details here. I encourage you to watch the video

youtube.com

and let me know your thoughts.

I'd love to start a wider discussion about this topic of resilience in updates as I firmly believe that, along with security, it is a critical area where IoT needs to focus in order to begin to fulfill the promise of the technology.

This talk relies heavily on products from Zymbit including Zymkey and Bootware but the general concepts are applicable across a variety of vendors.

Note: If you're involved in IoT in North Carolina, Northern Virginia, or Colorado, and you don't know about R!OT then you are missing out on one of the best resources for IoT professionals. They hold regular events, workshops, Lunch and Learns, etc. for their members. This talk was originally presented as a Lunch and Learn for R!OT. I encourage you to join, and to attend their events if this is an area that interests you.

How you can avoid another Crowdstrike fiasco

David G. Simmons — Tue, 13 Aug 2024 15:55:08 +0000

By now we've all heard about -- or been affected by -- the Crowdstike fiasco. If you haven't, here's a quick recap:

An update to the Crowdstrike Falcon platform, pushed on a Friday afternoon, caused computers to crash and be unbootable. The update was pushed to all customers at once, and the only way to recover was to boot into "Safe Mode" and uninstall the update. That often required direct physical access to the affected computer, making recovery times even longer.

I'm not going to go into a detailed post-mortem of all that went wrong, because I honestly don't know the gory details (was it dereferencing a null pointer? Was it an attempt to read a protected memory location?). There have been any number of posts and articles written about the incident, and I'm sure that Crowdstrike is doing a deep dive into what went wrong and how to prevent it from happening again. (Plus there's that $10 UberEats gift card!)

This is absolutely not a finger-pointing exercise. I'm not here to say "I told you so" or "you should have done it this way." There's plenty of blame to go around in any large-scale incident like this. but examining how we could have avoided such an incident, and how to make sure it doesn't repeat, is a valuable exercise.

Here are the issues I have with how this was rolled out:

The update was pushed to all customers at once. This is a recipe for disaster. Updates should be pushed in a phased manner, starting with a small subset of customers, and then increasing the number of customers as the update is proven to be stable. There are, of course, situations where this is just not possible. Zero-day exploits need to be patched immediately, and there is no time for a phased rollout.
The updates were run as "mandatory" and "background" updates. This means that the updates were pushed to the computers without the user's knowledge or consent. In general, System Admins should have warning before such updates are applied, and should have the ability to forgo the updates if they choose.
The update was pushed on a Friday afternoon. This is a classic "bad idea" in the world of IT. Updates should be pushed during the week, when there are more people available to deal with any issues that arise. (Let's not get into this argument, shall we? There are lots of opinions on this, and I'm sure that you have yours.) Again, there are always situations where an emergency update is required, and there is no time for a phased rollout.
There was no fall-back strategy in place for if the update went wrong.

The main thing missing from the entire episode was any idea of resiliency. How do you recover from a failed update? How do you ensure that your customers are not left high and dry when an update goes wrong? How do you ensure that your customers are not left with a bricked computer? These are all questions of resiliency that should be explicitly addressed in any update strategy.

How the embedded world handles updates

In the embedded world, we have been designing systems to handle firmware updates -- and update failures -- for decades. Back in my Sun SPOT days we had a firmware update architecture that would update the firmware on the device, and if the update failed, the device would automatically roll back to the previous version. This was a system that was designed to be updated over the air (OTA) and to be updated in the field. We had to design for the worst-case scenario that was both robust and recoverable.

How did we do this? We implemented what is known as A/B partitioning. The device has 2 partitions (well, more really, but we'll cover that in a minute), A and B. When an update is pushed, it is pushed to the partition that is not currently running the active system. The device then reboots into the new partition, and if the update is successful, the device continues to run normally. If the update fails, the device reboots into the previous partition, and the device is back up and running. The failed partition can then be updated again, and the process repeated until the update is successful.

It is a fail-safe way to push updates to devices that are in the field, and it is a system that has been in use for decades in the embedded world. If you have an Android-based phone, chances are that your phone is using this system to update the OS. If you have a Mac, you may notice that you have a small disk partition that can be used for 'recovery' in case of a system failure. It's not a full A/B partitioning system, but it is a similar idea.

Why didn't Crowdstrike use this system?

Well, the first problem is that these systems were all running Windows. I'll defer the whole "don't run production systems on Windows" argument for another time, but suffice it to say that Windows is not known for its robustness in the face of updates. Windows updates have been known to cause all sorts of problems, from the benign to the catastrophic. Anyone that runs Windows daily can attest to these problems. Drivers conflict, updates fail, and the system is left in a state that is not recoverable. The dreaded Blue Screen of Death (BSOD).

Windows is also not built to use such an A/B partitioning system. Windows is designed to be updated in place, and by the user, and the user is expected to be able to recover from a failed update. Much of this has been taken over by remote administration, but the basic idea is still the same: Windows is designed to be updated in place. Recent versions of Windows do have a system of "snapshots" of prior working versions that can be rolled-back to, but this is not the same as a full A/B partitioning system and the roll-back is not automatic.

The Crowdstrike update pushed a change to a driver that is loaded early in the boot phase. When this driver (or any driver that is loaded into kernel space) fails for any reason, Windows will refuse to boot and go to the Blue Screen of Death (BSOD). This is how Windows protects itself from malicious drivers and driver conflicts. Effective, sure, but not the most resilient way to go when you're dealing with large production systems that must be up and running all the time.

Production servers simply must be available all the time. There is no room for downtime, and there is no room for failure. This is why we have resiliency built into our systems. This is why we have A/B partitioning.

How does A/B partitioning actually work?

A/B partitioning schemes are designed to provide high resiliency in systems that must be up and running all the time, or where a failed update can be extremely difficult or costly to recover. Think about a small sensor embedded in a hydro-electric dam that needs to get a firmware update over the air. Now think about what happens if that update fails for some reason. Having that sensor off-line due to a failed update can have catastrophic results. Sending a technician out to that remote place, to crawl into some small, dark, possibly unsafe place to fix it just isn't a viable solution. Having a system that allows for the graceful recovery of the device is the only practical solution.

This is a simple diagram of how A/B partitioning works. The system boots into partition A, and when an update is pushed, it is written to partition B. The system then reboots from partition B, and if the update is successful, the system continues to run the updated firmware. If the update fails, the system reboots from partition A, and the system is back up and running. The failed partition can then be updated again, and the process repeated. Once successful, the system will be running out of partition B, and partition A will be the new 'spare'.

During the next update cycle, the system will update the partition that is not currently running (in this example partition A). This way, the system is always running on a known-good partition, and the system can always recover from a failed update. This makes the system extremely resilient in the face of failed updates.

Resiliency. It's a good thing.

Is there a cost for this? Of course. There is a cost in terms of storage space, obviously. You need to have enough space to hold 2 copies of the OS, and that can be expensive. But you don't have to have double the storage space of the entire system, as only the critical OS components need be stored in the A/B partitions. All of the user and application data can be stored in a separate partition that is always available to the running OS no matter which partition it is running from.

Is storage expensive? It can be, yes, but let's compare the relatively minor cost of this extra bit of storage to, say, the $500 million dollars that Delta Airlines has reportedly lost over the latest incident. Seems like a small price to pay for the added resiliency that it could provide.

Conclusion

The Crowdstrike meltdown was a failure of epic proportions, that much is clear. But there are a lot of factors that could have prevented it. Once the update escaped the validation and Q/A phase at Crowdstrike (which they acknowledge it shouldn't have), the lack of resiliency in the update process was the main issue, and one that could have been addressed with an A/B partitioning scheme. A/B partitioning isn't available on Windows machines, but it is absolutely available for other architectures. (I know I said I wouldn't get into the "don't run your production systems on Windows" argument, but I just can't help myself.)

We here at Zymbit have have designed a system to provide this kind of resiliency for our customers. Our Bootware system is designed to provide a secure and resilient way to update devices in the field. We use A/B partitioning to ensure that the device is always running from a known-good instance, and that the device can always recover from a failed update. Bootware also provides encrypted filesystems, secure boot, and secure updates, all designed to provide the highest level of security and resiliency for your devices.

All drivers for Microsoft Windows must be cyptographically signed. Most software for Windows is also required to be signed. The same is true for macOS. All system and driver updates -- really anything that runs in any privileged mode -- should be cryptographically signed and verified before being allowed to be installed and run. Bootware ensures that all updates are cryptographically signed and verified before being allowed to be installed and run. This is a critical part of the security and resiliency of the system.

How a truly secure and resilient update process should be rolled out:

Updates should be pushed in a phased manner, starting with a small subset of customers, and then increasing the number of customers as the update is proven to be stable whenever possible. Non-phased rollouts should be reserved for true emergency situation like zero-day exploits.
Updates should be pushed during the week, when there are more people available to deal with any issues that arise. Again, there are always situations where an emergency update is required, and there is no time for a phased rollout.
Administrators should be informed before an update is rolled out, and should have the ability to forgo the update if they choose.
There should be a fall-back and recovery strategy in place for when an update goes wrong.

This is how a truly secure and resilient update process should be rolled out. This is how we at Zymbit do it, and this is how we think it should be done. We build systems with this level of trust and resiliency built in because our customers expect it, and because we believe it is the right way to do things. If your updates are not done this way, it's time you start asking why not.

A KubeCon Wrap Up, of sorts

David G. Simmons — Tue, 14 Nov 2023 21:22:00 +0000

Well, KubeCon North America 2024 is over and done with, and I thought I'd write a short recap of my time there.

While the final attendance numbers aren't in yet, it's likely that there were between 7,000 and 8,000 attendees in Chicago the week of November 6th. That's a lot of Kubernetes enthusiasts! And the weather could not have been better (I mean, for Chicago, in November).

At the Otterize booth we were extremely busy too! On day one, November 7th, I personally was in the booth for 11 hours (some of that was booth setup). That's a long day.

We had some very popular swag! The Otter was a huge hit, again, and we handed out about 600 of them over the course of the week.

We also handed out several hundred T-Shirts and an uncountable number of Otterize stickers of various kinds. Ok, enough about the swag, how was the booth otherwise?

Well, we scanned over 450 badges (last I checked) and had some really interesting conversations with a lot of super interesting people. We gave over 100 demos (at least, I didn't count them all) to some very enthusiastic attendees and overall got some great feedback.

A rundown of some numbers:

600 otters given out
200+ T-shirts given out
36 new members joined our Community

I've done a lot of booth duty in my years in this business, and I can honestly say that this KubeCon NA Otterize booth was one of the most successful, fun, and productive booths I've ever done.

It was, of course, due in large part to the amazing group of Otters that were there.

I think we may have more fun than anyone though!

If you didn't get a chance to stop by the booth and see us, please, join our Community, ⭐️ us on GitHub, and reach out to set up a demo!

Deploying an EKS cluster with attached storage

David G. Simmons — Mon, 06 Nov 2023 20:12:34 +0000

I'll admit, up front, that I'm new to Kubernetes, and probably always will be. There's just too much to learn! That being said, it is my job (well, I am Head of #DevRel for Otterize, and we're all about Kubernetes) so I have to know a certain amount.

What I was trying to do

I do a lot of demos. As part of that, I have to provision, a lot of Kubernetes clusters. A lot. Several times a day if I'm working on building or debugging a demo or tutorial.

Being able to create, and recreate, clusters on any of the major cloud providers (mainly AWS and Google Cloud, for now) is essential to me being able to create tutorials that always work, and demos that work under pressure (like on stage!).

The problem child

Doing this on GKE was relatively straight forward. When I'd create a cluster that needed persistent storage, it was just provisioned and added, and everything worked as expected.

Imagine my surprise when I created a cluster in EKS and deployed the demo applications and ... Kafaka just failed because there was no attached storage!

It turns out that EKS doesn't auto-provision storage. So I started trying to figure out how to attach storage.

Just because it's documented doesn't mean you can do it

After reading a ton of documentation (which, quite frankly made my head hurt and didn't get me any closer to a solution) I posted in the r/kubernetes subreddit hoping for help.

Pretty much all that did was point me back to the documentation, which wasn't helpful. Nothing I could find pointed to a solution that I could simply add to my EKS config.json to build the cluster.

I wanted something to add so that my command

eksctl create cluster -f eks-config.yaml --profile ME

would create the cluster with everything I needed.

The solution

Working with one of the great engineers here at Otterize we finally hit on a solution.

It was so amazingly simple that I am still a little dumbfounded that the AWS docs make it so hard.

In the addons section I had already added the VPC CNI

addons:
  - name: vpc-cni
    version: v1.14.0-eksbuild.3
    attachPolicyARNs: #optional
    - arn:aws:iam::aws:policy/AmazonEKS_CNI_Policy
    configurationValues: |-
      enableNetworkPolicy: "true"

and a few others, like DNS

  - name: coredns
    version: 1.10.1-eksbuild.1
  - name: kube-proxy
    version: v1.27.1-eksbuild.1

And here's where the addition of the EBS storage came in, and was just stupid-easy:

- name: aws-ebs-csi-driver
    version: v1.22.0-eksbuild.2
    attachPolicyARNs:
    - arn:aws:iam::aws:policy/service-role/AmazonEBSCSIDriverPolicy

And I could now create a cluster with a single command, from the command-line, that would run my demo app (with Kafaka) on EKS without any trouble.

Tell me about it

If you read this and say "hey dummy, that's not how any of this works!" please reach out and tell me why! I'm just trying to learn, so learning from my mistakes is a huge bonus.

If you read this and are like "that's effing genius!" please also reach out! I'd love to hear from you either way!

Finally

Please come and join us in the Otterize Community and learn more about what we do, and how we're trying to make securing your cluster dead-simple!

Bite-size Otterize: moving fast and (never) breaking things.

David G. Simmons — Wed, 16 Aug 2023 12:47:23 +0000

If you read my last blog post, you'll know that I'm not new at computering™. I'm old enough, and have enough experience, to know that the "move fast and break things" culture just means you end up with broken things. But at least you broke them quickly? No.

What we want is to move fast and fix things! I don't need the stress and anxiety of worrying about breaking a production app. I mean, everyone's broken prod once (or will someday) and no one wants to do it (again or for the first time).

And if I'm going to move fast, the tools I use must make the job much, much easier: they need to give me superpowers. They need to help me tackle big tasks with little effort and little stress. Easy to use. Simple to configure. Safe to deploy. No unintended side-effects.

This is especially true when I'm using a tool to do hard things where mistakes are costly. Have you tried deploying network policies to a large cluster? Or Istio auth policies? Or how about mTLS? None of those things are easy to do nor at all forgiving of mistakes. That's why I want a power tool to do it.

Make it precise

I want to be able to target how I use the tool. Precision. Sure, I want the tool to be easy, and prevent me from making mistakes, but I also don't want to make massive changes all at once. I want to target my usage so I can walk before I run. I want rapid incremental progress that adds value at each step.

And remember, I've been doing this a long time, so I want to be sure that what I'm doing is working. Before it has a chance to blow up on me. Actually seeing what will happen before it happens would be really nice. I'm old enough to remember "trust but verify."

No matter what anyone tells you, sanity is not overrated. So sanity-checking a deployment before it has a chance to go sideways is a good thing. I want to trust my tool, but it has to earn my trust first. So let's do things to sanity-check its abilities.

What am I talking about?

As Head of DevRel here at Otterize, making things right for developers is what I focus on. I make sure that Otterize makes things that are intuitive, easy to use, solve hard problems, and let developers make quick iterations that add value.

When I got here I knew nothing about Otterize, Kubernetes, or securing services within a Kubernetes cluster. In reality, I was the perfect test-subject. And I was eager to ramp up.

As luck would have it, during my onboarding, Otterize was working on some new capabilities to suit precisely those users who wanted more precision in configuring access controls, in driving to zero trust. Users who wanted to deploy Otterize in a more "bite size" way: rapidly, easily, predictably, and incrementally, getting more and more secure at every step.

Making it bite-sized

I was still new to Otterize and the IBAC (intent-based access control) approach when these capabilities became available. The first time I tried them, I was like "YES! THIS is how it's supposed to be!"

And that's because I was able to just pick a single service, any service, that I wanted to protect. I could prepare to protect it by automatically ensuring all clients that should be able to reach it would be able to, in one step, and then actually protect it, in a second step. Without impacting anything else! The protection is done by configuring network policies, which are built into Kubernetes itself. But I didn't have to touch network policies themselves, much less be an expert with them.

In fact, step 1, knowing which clients intend to call the service is one of those superpowers I mentioned. The Otterize open source network mapper finds the clients calling services in your cluster. If you believe those calls are likely the way the code is supposed to behave, you can export those call patterns as client intents. And you can export just the clients of a single server:

% otterize network-mapper export --server frontend.otterize-ecom-demo -o clients.yaml

All you need to do to declare that these are the authorized clients is run kubectl apply -f clients.yaml, and Otterize will ensure they'll have access to the frontend.otterize-ecom-demo service when you protect it.

The Otterize Cloud access graph UI will even show you, visually, that these authorized clients will be able to call that service once protected. Think of it as a dry run for what will happen when you protect.

Then protecting it, step 2, is just as easy. Create this short protect-frontend.yaml file and kubectl apply it too:

apiVersion: k8s.otterize.com/v1alpha2
kind: ProtectedService
metadata:
  name: frontend
  namespace: otterize-ecom-demo
spec:
  name: frontend

And really that's all it takes! Quick. Easy. High precision. It just works. Otterize labeled pods, labeled namespaces, and created and applied network policies to a single service without affecting anything else and without me having to fuss over anything.

So then I thought "Let me just work my way across my access graph here and protect another service, and then another ..."

Watch as I move across the access graph and protect everything, one service at a time.

Doing all of that took 8 more commands: 2 to create all the intents, and then 6 more to protect each service. Most important for someone as careful as me: I didn't have to do it all at once.

It does what I want

I now have a tool that easily and effortlessly does exactly what I want — and I generally dislike the use of the word "easy" because, well, just because something's easy for me doesn't mean it will be easy for you.

At this point I'll refer you back to the previous section when I told you I knew less than nothing about deploying network policies in a Kubernetes cluster. Guess what? I still don't know how to do it. At least not the old-fashioned way. But I just did it. I did it slowly, service-by-service, and I didn't break anything in the process. I ended up with all of my services protected. And it wasn't a 6-month project. It took 5 minutes.

That's it!

No, really, that's it! It's a joy to work with. I can apply intents and protection and see it in my Otterize Cloud access graph in real time so I know what's happening the whole time. I can grow my level of confidence as I progress — and I can complete it without breaking anything. HUZZAH!!

Ready to experience this game-changer for yourself?
Take our new Otterize tutorial out for a spin and join our Slack Community to let us know what you think!

Joining Otterize and the modern network stack

David G. Simmons — Wed, 19 Jul 2023 11:48:07 +0000

Hi! 👋🏼 I'm new here.

I joined Otterize about 3 weeks ago as the Head of Developer Relations and I could not be more excited to be here.

Some of you who know me might be asking the questions "Wait, I thought you were a data nerd and an IoT junkie? What are you doing going into Kubernetes?"

Absolutely fair question! Let me explain a bit.

First, Some history

Way back at the dawn of time, when I first began my career in tech I worked for a US Government research lab. I wrote network intrusion detection software. It was called The NERD (Network Event Recording Device) and it was deployed site-wide in what was (and probably still is) one of the most complicated, and highly secure, computing environments in the world.

We wanted to monitor all traffic, and report anomalies in real-time. Most of the reporting traffic was over UDP (syslogd) but no traffic of any kind was allowed to traverse the network unencrypted. We used Kerberos 2048kb encryption for all TCP traffic, but there was no such beast for UDP. Until I wrote it. I also wrote a bunch of extensions to syslogd to further secure it with ingress and egress filters.

Our group also maintained detailed network maps of all servers and services and what they were connected to, which servers talked to which servers, etc. These network maps were always hopelessly out of date and we had no real way to automatically generate them, so we just sort of hoped we were right most of the time. Invariably we were not.

Fun story: We were absolutely convinced, based on our network topology maps, that one of the most sensitive networks was air-gapped and therefore could not be penetrated from the outside. Given the nature of the data we had on this network, it was absolutely critical that it remain secure.

To test this theory, we asked Tsutomu Shimomura to try to gain access to it. We were certain that he would be unable to do so. Imagine our level of panic when, 5 minutes into the test, he had taken over a terminal we were using on this "air-gapped" network.

It was time to update the network maps! And to rethink our network access policies.

This was all back in the mid 1990s when everything was just migrating from mainframes to client-server architectures. Everything was either a client or a server on the network, and each one had its own firewall, security policies, etc. It was a nightmare to manage. We relied on individual groups to report to us in the NOC (Network Operations Center) when they added a computer, or a service, to the network. They rarely did, so we were always playing catch-up.

The Cloud has entered the chat

As client-server computing, and maintaining your own data center or server farm, became too costly, we saw the migration to the cloud. In the beginning, "The Cloud" really was just "someone else's computers." We trusted someone else to run the data center and the servers, and they sold us time on those servers to run our workloads.

In the early days of cloud computing, this was revolutionary. It was a huge cost savings for companies, and it allowed them to focus on their core business rather than on maintaining their own data centers. They could off-load the expensive part to cloud providers, but they essentially had to manage all of the services — and security for them — themselves. It was better, but still not ideal.

The Cloud has evolved

It turns out that just moving to "other people's computers" wasn't really enough to realize the true power of cloud computing. When you truly want to scale to the massive sizes that a lot of modern applications require, it certainly wasn't enough.

It was time for another revolution in computing. This time, it was the revolution of containers and container orchestration. This is where Kubernetes comes in.

Containers had been around for a long time, and they exploded in popularity with the release of Docker. Docker made it easy to create, manage, and deploy applications in containers, but it was still a lot of work to manage them all. You had to manage the container images, the container registries, the container runtimes, the container networks, etc. It was still a lot of work.

Kubernetes was the answer to this problem. (Well, mostly.) It was a way to manage all of the containers, all of the services, all of the networks, all of the storage, all of the security, and all of the everything else. It was a way to manage all of the things all at once. It was also the way to introduce scaling to the container architectures, allowing you to scale both up and down as demand required.

We have now moved from client-server to cloud architectures, and have virtualized the data centers and services using Kubernetes. Progress!

But here's the thing: The basics of it all have not really changed. The physical machines have been virtualized. The networking between them has been virtualized. The storage has been virtualized. The services have been virtualized. The security has been virtualized. But it's all still there, and it all still has to be managed. Somehow.

And guess what? All the problems with all of those things are still there as well. Manging the network access between servers and services. Managing the permissions and properties for databases, streaming data services (like Kafka) and everything. Yes, it's easier now, and there are a lot better tools for doing it, but the problems are still there.

So why am I here?

Yes, we're finally getting to that. First and foremost, I'm here at Otterize because, after talking to the founders, I had such enormous respect and admiration for them that I really wanted to join them. Second, I had been wanting to work with Uri Sarid for a very long time. Finally, I was thoroughly convinced that what they were building was both technologically brilliant and absolutely necessary.

One of the things that absolutely sealed the deal for me was the origin story of the company name Otterize. The founders were brainstorming names and Uri suggested "Otterize" as a joke. But when they looked up the meaning of the word "otterize" they found that it actually means "to make something more delightful, more fun, or more charming." And that really resonated with me.

Remember, I started with some history about my experience in the networking and data services area. So while I don't understand Kubernetes well (yet) the underlying principles are much the same as my previous experience, and almost all of the problems I identified in that history are addressed by Otterize and Intent Based Access Control (IBAC). In fact, it goes further and solves several additional problems that I hadn't covered before. It's a brilliant solution to a set of very real problems.

So here I am, stepping out of my tech comfort zone yet again. But I'm stepping sort of back into my own history as well. I started my career in networking and security, so in a way Otterize is bringing me full circle. And I'm excited to learn from and work with Uri and the other brilliant folks here. I'm confident that together we can build something truly transformational that makes managing data services and security in the cloud era much easier and more secure.

What now?

For me, it's on to learning more about Kubernetes (since it's not been an area of expertise for me). I'll also be building more demos and tutorials (and we already have some great ones, so go check those out!). And I'll be back on the road, coming to a DevOps, Platform Engineering, or Kubernetes conference near you soon!

If you're looking for a speaker, I'd love to hear from you via email, twitter, LinkedIn, or mastodon.

Handy Tools I Use

David G. Simmons — Thu, 08 Apr 2021 21:18:53 +0000

This started as a simple post to an internal Slack group. Then I got asked to post it to another channel in that Slack. Then I decided to post it to the DevRel Collective Slack. At which point I was asked to make it a blog post so it didn’t get lost in the scroll-back (DevRel Collective uses a free Slack account, so we can only scroll back 10,000 messages. With 2,000+ members, that happens faster than you think.

Letters to Santa - Automating Joy to the World, At Scale

David G. Simmons — Fri, 18 Dec 2020 13:30:13 +0000

It’s that time of year again. The time when the world’s largest order fulfillment operation experiences its heaviest load. No, not Amazon - we’re talking about Santa Claus, Inc. - the largest logistics company in the world, with a 24-hour global delivery window at peak load.

This year is different, however. Earlier this year, Saint Nick clicked on an ad on his Facebook feed, one promising a digital nomad lifestyle through automating his business. Sick of the Arctic weather and the stress of traveling, the thought of sitting on a beach in Thailand - while still bringing joy to children around the world - was enticing.

Santa paid for the course and applied the principles of process automation, task decomposition and distribution, and integration with third-party services to his business.

Now he's kicking back on a beach on Koh Samui, while the automation brings joy to the world - at scale.

So this Christmas, children’s letters to Santa are routed to independent associates (their parents), who fulfill the orders using Amazon. Santa’s successful business transformation became a case study, which we’re going to share with you here.

Here’s how it’s done.

The Front End

Given that Santa's a modern guy, and in case he needed to supplement his retirement income with some contract front-end development work, Santa decided to do a crash course in learning to program in React.js. It seemed like the thing all the cool kids were doing, so Santa wanted to give it a shot.

While it was harder than he thought, thanks to a lot of hard-core googling, and a lot of copy-paste (remember kids, good developers copy, great developers paste!) he was able to come up with a site that at least looks passable, and handles the simple function of accepting a letter to Santa and submitting it to the process engine.

For the process engine Santa of course chose Camunda!

Once the form was designed, all that was left to do was submit the form using some JavaScript:

const handleSubmit = event => {
    event.preventDefault();
    setSubmitting(true);
    if (!event.target.checkValidity()) {
      // form is invalid! so we do nothing
      return;
    }
    const requestOptions = {
      method: 'POST',
      headers: { 'Content-Type': 'application/x-www-form-urlencoded;charset=UTF-8' },
      body: JSON.stringify(formData)
    };
    fetch('https://write-a-letter-to-santa.org:9091/santa', requestOptions);

    alert('Santa has been notified! You can reload the page to send another letter.');
  }

Using a simple alert to let the user know that the form was submitted was the path of least resistance, and Santa was getting lazy.

The Process

Handling a letter by just forwarding it to the parents as-is seemed a little too lazy, even for Santa, so he quickly designed a business process using Cawemo to handle the routing of the letters.

Here's what that process looks like:

And here's the flow:

1) A letter comes in, which starts the process.
2) The letter is analyzed using some Natural Language Processing (NLP) algorithms to extract some parts of the letter to help figure out what the writer is asking for:
1) Identify any items the writer is asking for.
2) Do some Sentiment Analysis to try to figure out how important each item is to the writer.
3) If there are no items identified, then the letter is routed to a manual process where one of the Elves can do some more investigation, and update the list.
4) Once this is done, go find some possible Amazon links for the things identified.
5) Send a letter to the parents with a copy of the original letter, the items they asked for (linked to Amazon of course) and some helpful hints as to what the writer wanted most.
6) Store the product information in a local database for analysis later.

Now, before anyone tries to have Santa fined for non-compliance with GDPR, he's not storing any names, email addresses, or any other personal data. Santa already knows everything about you! He just stores the items asked for. So he can do some demand-gen analysis later, of course.

Santa wrote a pretty basic web-server in Go to handle the incoming letters, and submit them to the Camunda BPM processing engine:

http.HandleFunc("/santa", santa)
    err := http.ListenAndServe(":9091", nil) // set listen port
    if err != nil {
        log.Fatal("ListenAndServe: ", err)
  }

And then a handler function:

func santa(w http.ResponseWriter, r *http.Request) {
    enableCors(&w)
    if r.Method == "GET" {
        log.Println("GET Method Not Supported")
        http.Error(w, "GET Method not supported", 400)
    } else {
        body, err := ioutil.ReadAll(r.Body)
        if err != nil {
            panic(err)
        }
        log.Println(string(body))
        var t SantaData
        err = json.Unmarshal(body, &t)
        if err != nil {
            panic(err)
        }
        log.Println(t.Letter)
        w.WriteHeader(200)
        client := camundaclientgo.NewClient(camundaclientgo.ClientOptions{
            EndpointUrl: "http://localhost:8000/engine-rest",
            ApiUser:     "demo",
            ApiPassword: "demo",
            Timeout:     time.Second * 10,
        })

        processKey := "santa"
        variables := map[string]camundaclientgo.Variable{
            "name":   {Value: t.Name, Type: "string"},
            "email":  {Value: t.ParentEmailAddress, Type: "string"},
            "letter": {Value: t.Letter, Type: "string"},
        }
        _, err = client.ProcessDefinition.StartInstance(
            camundaclientgo.QueryProcessDefinitionBy{Key: &processKey},
            camundaclientgo.ReqStartInstance{Variables: &variables},
        )
        if err != nil {
            log.Printf("Error starting process: %s\n", err)
            return
        }
    }
}

He did have to enable CORS to allow the cross-origin posting of data. That's rather a key point in all of this, since the server here runs on a different port than the server that handles posting the letters.

After that, a bit of magic with the Camunda Go Client and the letter is submitted to the Camunda BPM Process Engine.

Natural Language Processing?

Yes, it's a form of Artificial Intelligence (AI) that allows you to break up written text and identify parts of it based on certain criteria. Done right, it can be very accurate.

So let's take a sample letter:

Dear Santa,

My name is Leon and I'm 36 years old (yeah, I still believe in Santa 😇)

This year I've been the goodest kid ever, so I kinda deserve a big present...

I was thinking about a nice LEGO box like the skyline kit or the New York City one. If that's not an option, I'd settle for some good chocolate too!

Thank you,
Leon

Now you and I can easily pick out the items in that letter that would be gifts, but it turns out that doing that is harder than it seems.

When we run that through our NLP processor we get:

This year I've been the goodest kid ever, so I kinda deserve a big present...
Sentiment: 0.300000, positive   Item: name   Type: OTHER
Sentence: I was thinking about a nice LEGO box like the skyline kit or the New York City one.
Sentiment: 0.200000, positive   Item: LEGO box   Type: OTHER
Item: skyline kit    Type: OTHER
Sentence: If that's not an option, I'd settle for some good chocolate too!
Sentiment: 0.700000, positive   Item: option     Type: OTHER
Item: chocolate  Type: OTHER
Sentence: Thank you,
Leon
Sentiment: 0.800000, positive

Hmmm ... Not great.

If Leon had written Santa a more specific letter, we could have gotten some better results for him:

Dear Santa,

My name is Leon and I'm 36 years old (yeah, I still believe in Santa 😇)

This year I've been the goodest kid ever, so I kinda deserve a big present...

I was thinking about a nice Lego skyline kit or the Lego New York City Skyline Kit.

If that's not an option, I'd settle for some good Belgian Chocolate too!

Thank you,
Leon

When we process that letter, we get better results:

Letter is 4 sentences long.
Sentence: Dear Santa, My name is Leon and I'm 36 years old (yeah, I still believe in Santa :innocent:) This year I've been the goodest kid ever, so I kinda deserve a big present...
Sentiment: 0.500000, positive   Item: name   Type: OTHER
Item: Santa  Type: OTHER
Sentence: I was thinking about a nice Lego skyline kit or the Lego New York City Skyline Kit.
Sentiment: 0.000000, positive   Item: skyline kit    Type: OTHER
Item: Lego   Type: ORGANIZATION
Item: Skyline Kit    Type: CONSUMER_GOOD
Sentence: If that's not an option, I'd settle for some good Belgian Chocolate too!
Sentiment: 0.400000, positive   Item: option     Type: OTHER
Item: Belgian Chocolate  Type: CONSUMER_GOOD
Sentence: Thank you, Leon
Sentiment: 0.800000, positive

You'll notice that now we have identified some CONSUMER_GOODS in the letter, which are much easier to find.

So let's see how Santa went about finding links.

What about if there are no CONSUMER_GOODS?

That's where the magic of manual processes and forms comes in, of course. We have an exclusive gateway that checks to see if any CONSUMER_GOODS have been identified. If not, then it would be harder for the Amazon-search process to find anything meaningful.

This part of the process is where the Elves come into play. They didn't all lose their jobs once the whole operation was automated! But they were able to join the "Work From Home" movement, so now they do their jobs from wherever they want! (Look for elves in your neighborhood!)

Let's say Leon had written a letter that just said "I want world peace. And I'd love harmony". While those are lofty ideals, they aren't really things that can be ordered from Amazon (at least not yet).

Here's the form the Elves get when a letter gets routed to them for intervention:

And then after the Elves have given it some thought, checked the Naughty/Nice list, they can update the items:

The form is then routed back into the process.

There is a bit of work to do in building the form though. First thing is to build the form according to the docs. Since Santa put everything into a JSON object when the letter was parsed, he had a bit more work to do though.

<form role="form" name="form" id="santa-form">
<h1>Edit any Gifts to make them easier to search for</h1>
  <script cam-script type="text/form-script">
    var variableManager = camForm.variableManager;

    camForm.on('form-loaded', function() {
      // fetch the variable 'gifts'
      variableManager.fetchVariable('gifts');
      console.log(variableManager.variableValue('gifts'))
    });

    camForm.on('variables-fetched', function() {
      // value has been fetched from the backend
      var value = variableManager.variableValue('gifts');
      var frm = document.getElementById('santa-form')
      var g = 0;// will be the number of gifts.
        // it's an array of Json so we have to parse it.
      var m = JSON.parse(variableManager.variables.gifts.originalValue);
      for(var x = 0; x < m.length;x++){
        for(var y = 0; y < m[x].gift.length; y++){
          var textfield = document.createElement("INPUT");
          textfield.type = "text";
          // set the ID so we know where the gift goes back in the JSON array
          textfield.id = "gift-" + x + "-" + y
          textfield.value = m[x].gift[y];
          textfield.classList.add("form-control");
          var label = document.createElement("Label");
          label.htmlFor = textfield.id;
          g++
        }
      }
    });

    camForm.on('submit', function(evt) {
      // get the form
      var frm = document.getElementById('santa-form')
      // parse the original JSON
      var m = JSON.parse(variableManager.variables.gifts.originalValue);
      // get all the inputs
      var inputs = document.forms["form"].getElementsByTagName("input");
      for(var x = 0; x < inputs.length;x++){
        var e = inputs[x].id.split("-");
        if(e.length > 0){
          m[parseInt(e[1])].gift[parseInt(e[2])] = inputs[x].value
        }
      }
      // re-stringify the updated JSON
      var final = JSON.stringify(m)
      var backendValue = variableManager.variable('gifts').value;
      if(final === backendValue) {
        // prevent submit if value of form field was not changed
        evt.submitPrevented = true;
      } else {
        // set value in variable manager so that it can be sent to backend
        variableManager.variableValue('gifts', final);
      }
    });
  </script>
</form>

Santa had to create all the form elements on-the-fly, and then read them back into the instance variable at the end.

Now, here's the tricky bit: If you're uploading a form along with your diagram, you can't use the easy interface provided by the Modeler. You have to use a manual process. Santa, being an old-school command-line guy, used curl:

curl -w “\n” — cookie cookie.txt \
  -H “Accept: application/json” \
  -F "deployment-name=santa" \
  -F "enable-duplicate-filtering=false" \
  -F "deploy-changed-only=false" \
  -F "santa.bpmn=@/Users/santa/Downloads/santa.bpmn" \
  -F "ManualLetter.html=@/Users/santa/github.com/letter-to-santa/src/ManualLetter.html" \
   http://santa-server.com:8080/engine-rest/deployment/create

That uploads the BPMN file and the Form to the Camunda BPM Server, and then when the manual process is called, the form shows up!

Finding Links

Being Santa, and having an entire year to plan for this, you would have thought Santa could have been better prepared, but, well, the retirement decision was sort of last-minute, and the beach in Thailand was sooo nice, he sort of forgot a few details.

The main detail he forgot was to create an Amazon Seller Account, which would have given him access to the product search API. With that, he could have done a much better job of searching for products, looking at the results, etc.

This was not the case, alas. But thankfully one of Santa's smarter elves stepped up at the last minute and told him to just use an Amazon search URL. Next year, Santa will be more prepared for this.

Sending the Email

Since Santa didn't really want to do, well, much of anything, even the email portion was automated.

He took all the information gathered in the previous steps, and pulled it all together into a nice email to the Parents:

Seasons Greetings!

Guess what? Leon has written me a letter asking for a few things. As I've now retired to a beach in Thailand, I thought maybe you'd like to know what Lean asked for. Here's the letter:

"Dear Santa,

My name is Leon and I'm 36 years old (yeah, I still believe in Santa 😇)

This year I've been the goodest kid ever, so I kinda deserve a big present...

I was thinking about a nice Lego skyline kit or the Lego New York City Skyline Kit.

If that's not an option, I'd settle for some good Belgian Chocolate too!

Thank you,
Leon"

I've taken the liberty of figuring out which things they want most, and provided you with a list so that you can just purchase these items directly. Don't worry, the Elves are not out of work! They're working from home to monitor all the processes. And no, they are not available for purchase.

So, that list:

Lego ⁉️
Skyline Kit ⁉️
Belgian Chocolate ❗️

In case you're wondering, since I'm retired, I'm also lazy. So I've used some Artificial Intelligence (which really isn't all that intelligent) to sort of 'rate' what they asked for. I could have ordered the list, but as I just told you, I'm retired, and lazy. Here's the rating system:

⚠️: meh.

⁉️: Ok, I guess.

❗: Now we're talkin!

‼️: Oh please! Oh Please! Oh please!

All the best from me and Mrs. Claus

--
PS: Please don't write back to this email address. I'm retired!

Write your own letter!

Santa was now done. And he didn't have to lift a finger!

How did he do it all?

It did take writing some code, but Santa was able to use the Camunda Golang client library to handle everything.

As we saw, once the letter was submitted, the web server created a new task in Camunda and submitted it, along with all the process variables it needed to keep track of (to start with, just the name, email address and the letter itself). We've already seen how that was done.

But once that was submitted as a task, how was that task handled?

Handling a task

This is the technical bit. In that same Go process that handles the incoming letters (though it could have been in a completely separate process), we listen for new tasks on the santa queue. Specifically, we first listen for nlp-extraction tasks.

First, we have to create a client for the Camunda BPM engine:

client := camundaclientgo.NewClient(camundaclientgo.ClientOptions{
        EndpointUrl: "http://hostname:8080/engine-rest",
        // ApiUser:     "demo",
        // ApiPassword: "demo",
        Timeout: time.Second * 10,
    })
    logger := func(err error) {
        fmt.Println(err.Error())
  }

Once we have the client, we can begin to create some processes that watch the various task queues. So for the NLP queue:

proc := processor.NewProcessor(client, &processor.ProcessorOptions{
        WorkerId:                  "nlpProcessor",
        LockDuration:              time.Second * 10,
        MaxTasks:                  10,
        MaxParallelTaskPerHandler: 100,
        LongPollingTimeout:        10 * time.Second,
    }, logger)
    // NLP Handler
    proc.AddHandler(
        &[]camundaclientgo.QueryFetchAndLockTopic{
            {TopicName: "nlp-extraction"},
        },
        func(ctx *processor.Context) error {
            fmt.Printf("Running task %s. WorkerId: %s. TopicName: %s\n", ctx.Task.Id, ctx.Task.WorkerId, ctx.Task.TopicName)
            var sentRes camundaclientgo.Variable
            var err error
            varb := ctx.Task.Variables
            text := fmt.Sprintf("%v", varb["letter"].Value)
            fmt.Println(text)
            sentRes, err = analyze(text) // <-- **this is the important bit
            if err != nil {
                log.Fatal(err)
            }
            vars := make(map[string]camundaclientgo.Variable)
            vars["status"] = camundaclientgo.Variable{Value: "true", Type: "boolean"}
            vars["gifts"] = sentRes
            err = ctx.Complete(processor.QueryComplete{
                Variables: &vars,
            })
            if err != nil {
                fmt.Printf("Error set complete task %s: %s\n", ctx.Task.Id, err)
            }
            fmt.Printf("Task %s completed\n", ctx.Task.Id)
            return nil
        },
    )

This process creation process is also provided by the Go Client.

The process is created, using the client created previously, and telling the process what tasks to listen for, how long to lock the task (so no one else tries to claim and process it) and then what to do once the task is claimed. A Camunda Client Variable object is created, and then the analyze() function is called.

The analysis function returns the Variable which has been filled out with all the parts identified. Those are all stored in a JSON object (represented by a struct in Go)

  type Gift []struct {
    Gifts      []string `json:"gift"`
    Types      []string `json:"type"`
    Sentiments []int    `json:"sentiment"`
    Amazon     []string `json:"amazon"`
}

After the analyze function completes, the Gifts, Types and Sentiments are all filled out, but the Amazon portion is empty because we haven't done that yet.

Since we've completed the analysis of the letter, we take all the results, package them up into some new variables, and put everything back into the Camunda BPM engine.

Of course, the next step is to create a similar process to watch for tasks on the amazon-search queue. The process is really identical to the previous one, except that it listens for different task identifiers, and calls a different method to execute on the instance variables.

Once the amazon-search task is completed (and the Amazon portion of the data structure is filled in for each Gift idea), the whole thing is returned to Camunda BPM and the task is marked as completed.

Which moves it on to the email portion.

Again, a processor is defined to listen for email tasks, claim them, and then compose and send the email to the recipient. Once this is done, the task is marked as completed, and returned.

Finally, we have a task that stores all the Gifts in a database so that Santa can see what sorts of gifts people asked for this year. He may be retired, but still needs to keep a finger on the pulse of what kids want!

Work Flow Completion

This entire workflow is extremely efficient. It generally completes in a few seconds at most. It's so fast, in fact, that Santa can't even see any processes sitting around in Cockpit! Unless there's a problem. Which there won't be, because Santa doesn't want to be disturbed.

Areas for improvement

Of course the NLP part could be improved substantially. Santa simply used the free-tier of Google's Natural Language Processing engine, with zero adjustments, and took the results without any further analysis. (Need I remind you of Santa's laziness at this point?).

Further, the Amazon search portion could be much better with an actual Amazon Reseller account. Maybe next year.

If you can think of other areas for improvement -- and there must be a lot! -- please feel free to reach out to David G. Simmons, Principal Developer Advocate at Camunda who was responsible for helping Santa get this entire process set up.

What in the job hopping hell?

David G. Simmons — Mon, 12 Oct 2020 16:29:43 +0000

You what?

I changed jobs. Again. Yes. I did. But there’s a good reason for it. It wasn’t that long ago that I posted about leaving InfluxData and joining QuestDB as their Head of Developer Relations. In fact, it was only 6 months ago. And yet, here I am, once again, posting about a job change. This is, you may notice, not characteristic of me. I was at Sun for 15 years, they Riverbed for 2.5 years, then InfluxData for 2.5 years. So what’s going on?

What’s going on?

You ever get an offer you just can’t refuse? Well, I did.

If you work in Developer Relations, then you probably know (or know of) Mary Thengvall. She quite literally wrote the book on Developer Relations. If you haven’t read it, you should. She’s also been a friend of mine for a few years (and sort of my hero in DevRel).

A couple of years ago she recommended me for a job as Director of DevRel at a small German company called Camunda. I interviewed, and was pretty excited about it, but it sort of fizzled. And then Mary came to me to let me know that, while she had been working with them to find the Director, they had decided what they really wanted was Mary herself! Duh! So she took the job and I couldn’t have been happier for her. Heck, if I had the choice between hiring me or hiring Mary, I’d have hired her too!

As soon as she told me that, I said “hire me!” Of course. again, duh. But she didn’t have any openings, was just settling in to the new role, and so on.

Fast forward a year (ish). Mary was hiring. For senior roles. Anyone who works in Devrel knows that senior roles don’t come around all that often, and senior roles working for Mary are a once-in-a-lifetime opportunity.

You’ll either get this, or you won’t.

I asked, she offered, and I had to take it!

So what now?

Now I’m a Principle Developer Advocate at Camunda! But wait, that’s not IoT! That’s not Time Series Databases! WTF is going on here even?

You’re right. It’s none of those things. But what it is, for me, is an opportunity to learn more about Developer Relations (again, from the woman who literally wrote the book on it) in one place than I ever could any other way. It’s a way to expand my knowledge of the industry, and to learn a completely new skill (Business Process Automation — spoiler, if you dig back in my past far enough, you’ll find I did it 20 years ago!).

So here we are. And I couldn’t be more excited or more pleased to be starting this new adventure!

What Happens When you Put Your SQL Database on the Internet

David G. Simmons — Thu, 25 Jun 2020 12:35:32 +0000

And then we posted it to Hacker News.

If you listen to, well, pretty much anyone rational, they will tell you in no uncertain terms that the last thing you ever want to do is put your SQL Database on the internet. And even if you’re crazy enough to do that, you certainly would never go post the address to it on a place like Hacker News. Not unless you were a masochist anyway.

We did it though, and we’re not even a little bit sorry about it.

The Idea

QuestDB has built what we think is the fastest Open Source SQL Database in existence. We really do. And we’re pretty proud of it, in fact. So much so that we wanted to give anyone that wanted the opportunity a chance to take it for a spin. With real data. Doing real queries. Almost anyone can pull together a demo that performs great under just the right conditions, with all the parameters tightly controlled.

But what happens if you unleash the hordes on it? What happens if you let anyone run queries against it? Well, we can tell you, now.

What It Is

First off, it’s a SQL-based Time Series database, built from the ground up for performance. It’s built to store and query very large amounts of data very quickly.

We deployed it on an AWS c5.metal server in the London, UK datacenter (sorry all you North Americans, there’s some built-in latency due to the laws of physics). It was configured with 196GB of RAM, but we were only using 40GB at peak usage. The c5.metal instance provides 2 24-core CPUs (48 cores), but we only used one of them (24 cores) on 23 threads. We really weren’t using anywhere close to the full potential of this AWS instance. That didn’t appear to matter at all.

The data is stored on an AWS EBS volume that provides SSD access to the data. It’s not all in memory.

The data is the entire NYC Taxi Database plus associated weather data. It amounts to 1.6 billion records, weighing in at about 350GB of data. That’s a lot. And it’s too much to store in-memory. It’s too much to cache.

We provided some clickable queries to get people started (none of the results were cached or pre-calculated) but we essentially didn’t restrict the kinds of queries that users could run. We wanted to see, and let users see, how well this performed on whatever queries they threw at it.

It was not disappointing!

The Hacker News Post

A few weeks ago, we wrote about how we implemented SIMD instructions to aggregate a billion rows in milliseconds [1] thanks in great part to Agner Fog’s VCL library [2]. Although the initial scope was limited to table-wide aggregates into a unique scalar value, this was a first step towards very promising results on more complex aggregations. With the latest release of QuestDB, we are extending this level of performance to key-based aggregations.

To do this, we implemented Google’s fast hash table aka “Swisstable” [3] which can be found in the Abseil library [4]. In all modesty, we also found room to slightly accelerate it for our use case. Our version of Swisstable is dubbed “rosti”, after the traditional Swiss dish [5]. There were also a number of improvements thanks to techniques suggested by the community such as prefetch (which interestingly turned out to have no effect in the map code itself) [6]. Besides C++, we used our very own queue system written in Java to parallelise the execution [7].

The results are remarkable: millisecond latency on keyed aggregations that span over billions of rows.

We thought it could be a good occasion to show our progress by making this latest release available to try online with a pre-loaded dataset. It runs on an AWS instance using 23 threads. The data is stored on disk and includes a 1.6billion row NYC taxi dataset, 10 years of weather data with around 30-minute resolution and weekly gas prices over the last decade. The instance is located in London, so folks outside of Europe may experience different network latencies. The server-side time is reported as “Execute”.

We provide sample queries to get started, but you are encouraged to modify them. However, please be aware that not every type of query is fast yet. Some are still running under an old single-threaded model. If you find one of these, you’ll know: it will take minutes instead of milliseconds. But bear with us, this is just a matter of time before we make these instantaneous as well. Next in our crosshairs is time-bucket aggregations using the SAMPLE BY clause.

If you are interested in checking out how we did this, our code is available open-source [8]. We look forward to receiving your feedback on our work so far. Even better, we would love to hear more ideas to further improve performance. Even after decades in high performance computing, we are still learning something new every day.

[1] https://questdb.io/blog/2020/04/02/using-simd-to-aggregate-b…[2] https://www.agner.org/optimize/vectorclass.pdf[3] https://www.youtube.com/watch?v=ncHmEUmJZf4[4] https://github.com/abseil/abseil-cpp[5] https://github.com/questdb/questdb/blob/master/core/src/main…[6] https://github.com/questdb/questdb/blob/master/core/src/main…[7] https://questdb.io/blog/2020/03/15/interthread[8] https://github.com/questdb/questdb

And then we posted the link to the live database.

And sat back.

And watched the incoming traffic.

And tried not to panic.

What Happened

First off, we made the front page of Hacker News. Then we stayed there. For hours.

We saw a lot of traffic. I mean a lot of traffic. Somewhere over 20,000 unique IP addresses.

Excluding simple show queries, we saw 17,128 SQL queries with 2,485 syntax errors in the queries. We sent back almost 40GB of data in response to the queries. The response times were phenomenal. Sub-second responses to nearly all of the queries.

Someone in the HN comments suggested that column stores are bad at joins, which led to someone coming in and trying to join the table to itself. Ordinarily, this would be a stunningly bad decision.

The result was … not what they were expecting:

Yeah, that’s 2,671,914,914,060,072,000 rows. In 69ms (including network transfer time). That’s a lot of results in a very short amount of time. Definitely not what they were expecting.

We saw only a couple of bad actors try something cute:

2020-06-23T20:59:02.958813Z I i.q.c.h.p.JsonQueryProcessorState [8536] exec [q='drop table trips'] 
2020-06-24T02:56:55.782072Z I i.q.c.h.p.JsonQueryProcessorState [6318] exec [q='drop *']

But those didn’t work. We may be crazy, but we’re not naive.

What we learned

It turns out that when you do something like this, you learn a lot. You learn about your strengths and your weaknesses. And you learn about what users want to do with your product as well as what they will do to try to break it.

Joining the table to itself was one such lesson. But we also saw a lot of folks use a where clause, which caused fairly slow results. We were not entirely surprised by this result, as we are aware that we have not done the optimizations on that path to yield the fast results we want. But it was a great insight into how often it is used, and how people use it.

We saw a number of people use the group by clause as well, and then be surprised that it didn’t work. We probably should have warned people about that. In short, group by is automatically inferred, so it’s not needed. But since it’s not implemented at all, it causes an error. So we’re looking at ways to handle that.

We are taking all these lessons to heart, and making plans to address everything we saw in upcoming releases.

Conclusions

It seems that the vast majority of the people that tried the demo were very impressed with it. The speed is truly breathtaking.

Here are just a few of the comments we got in the thread:

I abused LEFT JOIN to create a query that produces 224,964,999,650,251 rows. Only 3.68ms execution time, now that’s impressive!

Very cool. Major props for putting this out there and accepting arbitrary queries.

Very impressive, i think building your own (performant) database from scratch is one of the most impressive software engineering feats.

Very cool and impressive!! Is full PostreSQL wire compatibility on the roadmap? I like postgres compatibility

(Yes, full PostgreSQL Wire Protocol is on the roadmap!)

Mind blowing, did not know about questDB. The back button seems broken on chrome mobile

Yes, the demo did break the Back button on your browser. There’s an actual reason for that, but it is true, for now. We’re definitely addressing that one right away.

Try It Yourself

Want to try it yourself? You’ve read this far, you really should! got to http://try.questdb.io:9000/ to give it a whirl.

We’d love to have you join us on our Community Slack Channel, give us a Star on GitHub, and Download and try it yourself!

QuestDB With Python

David G. Simmons — Mon, 15 Jun 2020 12:08:02 +0000

QuestDB Tutorial for Python

Getting Started

To get started, you'll need a few things installed and set up. This should be quick.

QuestDB: To install Questdb you can see Installation for complete instructions in case you want to use Docker, or brew on MacOS, but the easiest way is to download the binaries and run it directly. Instructions for that are Here.
Jupyter Notebooks: That's what this is. To run it, you should:
1. make sure you are running Python 3.x and not Python 2.7. If you're in doubt, python --version will tell you.
2. install Jupyter Notebooks with pip3 install --upgrade ipython jupyter
3. make sure that the libraries we use in this tutorial are also installed with pip3 install requests urlib matplotlib pandas
4. clone this repository (git clone https://github.com/davidgs/QuestNotebook)
5. in the repository directory run jupyter notebook

That will get you right back to a page like this that is interactive, allowing you to run the code and interact with the database yourself.

If you get errors like ModuleNotFoundError: No module named 'requests' for any of the libraries you installed above, double-check to make sure that you are actually using Python 3.x jupytper --path will let you know if Jupyter is using 2.7 or 3.x

Create A Database

We will need someplace to store our data, so let's create a test database where we can put some random data.

We will create a simple table with 5 columns, one of which is a timestamp

The Create operation in QuestDB appends records to bottom of a table. If the table has a designated timestamp, new record timestamps must be superior or equal to the latest timestamp. Attempts to add a timestamp in middle of a table will result in a timestamp out of order error.

cust_id is the customer identifier. It uniquely identifies customer.
balance_ccy balance currency. We use SYMBOL here to avoid storing text against each record to save space and increase database performance.
balance is the current balance for customer and currency tuple.
inactive is used to flag deleted records.
timestamp timestamp in microseconds of the record. Note that if you receive the timestamp data as a string, it could also be inserted using to_timestamp.

This should return a 200 status the first time you run it. If you run it more than once, subsequent runs will return 400 because the database already exists.

import requests
import urllib.parse as par

q = 'create table balances'\
    '(cust_id int,'\
    ' balance_ccy char,'\
    'balance double,'\
    'inactive boolean,'\
    'timestamp timestamp)'\
    'timestamp(timestamp)'
r = requests.get("http://localhost:9000/exec?query=" + q)
print(r.status_code)

Generate some Data

Since we have a new setup, we should add some data to QuestDB so that we can have something to query. We will add some random data, for now.

You can re-run this section as many times as you want to add 100 entries at a time, or simply change the range(100) to add as many datapoints as you wish.

import requests
import random
from datetime import datetime
success = 0
fail = 0
currency = ["$", "€", "£", "¥"]
random.seed()
for x in range(1000):
    cust = random.randint(20, 42)
    cur = random.choice(currency)
    bal = round(random.uniform(10.45, 235.15), 2)
    act = bool(random.getrandbits(1))
    query = "insert into balances values("\
        + str(cust) + ",'"\
        + cur + "'," \
        + str(bal) + "," \
        + str(act) + ",systimestamp())"
    r = requests.get("http://localhost:9000/exec?query=" + query)
    if r.status_code == 200:
        success += 1
    else:
        fail += 1

print("Rows inserted: " + str(success))
if fail > 0:
    print("Rows Failed: " + str(fail))

Query Data from QuestDB

Now that we have data available, let's try querying some of it to see what we get back!

import requests
import io

r = requests.get("http://localhost:9000/exp?query=select * from  balances")
rawData = r.text
print(rawData)

Read the content into Pandas Dataframe

So you'll notice that the returned data is just a massive csv string. If you'd rather have json data, then you would change the endpoint to http://localhost:9000/exec ... But since we're going to use Pandas to frame our data, we'll stick with csv.

We are also telling pandas to parse the timestamp field as a date. This is important since we're dealing with Time Series data.

import pandas as pd

pData = pd.read_csv(io.StringIO(rawData), parse_dates=['timestamp'])
print(pData)

Narrow the search

That's just getting us all the data, but let's narrow the search using some SQL clauses. Let's look for a specific cust_id and only balances of that customer that are in $s. We are also only interested in times the customer was active Since this is SQL, you can make this query as simple, or as complex, as you'd like.

Since all of the data was generated randomly, this exact query may return no results, so you may have to adjust the cust_id below until you get results back.

**Note:* The query string must be URL-encoded before it is sent.

import urllib.parse

q = "select cust_id,"\
    " balance,"\
    " balance_ccy,"\
    " inactive,"\
    " timestamp"\
    " from balances"\
    " where cust_id = 26"\
    " and balance_ccy = '$'"\
    " and not inactive"
query = urllib.parse.quote(q)
r = requests.get("http://localhost:9000/exp?query=" + query)
queryData = r.content
rawData = pd.read_csv(io.StringIO(queryData.decode('utf-8')), parse_dates=['timestamp'])
print(rawData)

Plot the data

We will use matplotlib to plot the data

from matplotlib import pyplot as plt

rawData.plot("timestamp", ["balance"], subplots=True)

And, with any luck, you'll get a great little plot like this:

Clean up

Now we will clean everything up for the next time.

r = requests.get("http://localhost:9000/exec?query=drop table balances")
if r.status_code == 200:
    print("Database Table dropped")
else:
    print("Database Table not Dropped: " + str(r.status_code))

And you're done! If you liked this tutorial, please give us a ⭐ on Github and be sure to follow us on twitter!

A QuestDB Dashboard with Node-Red

David G. Simmons — Tue, 09 Jun 2020 12:31:58 +0000

This is really a follow-on to my post from last week where I connected an Arduino with a temperature and humidity sensor to QuestDB.

It’s one thing to send data to your database, but being able to visualize that data is the next logical step. So let’s dive right in to doing that.

QuestDB is rather new, and hence we haven’t completed our Grafana Data Source Plugin yet, so I wanted to make a quick dashboard to show the incoming temperature/humidity data (and you’ll see just how awful the sensor really is). To do this, I chose Node-Red because, well, it seems the obvious choice!

Building the Nodes:

As you can see, it uses only a few nodes, so I’ll walk through them one by one.

The initial node is an injector node that fires at a regular, configurable interval. Mine fires every 10 seconds just to keep from being too noisy. It triggers the SetQuery node which builds the query:

var q = {}
q["query"] = "select temp_c, humidity, timestamp from iot where timestamp > (systimestamp() - 5000000)" 
msg.payload = q
return msg;

I set the payload to a query, in this case, I’m getting the temperature and the humidity for the past 5 seconds (remember, we are dealing with microsecond timestamps, so 5 seconds is 5M microseconds). I then send that query, as the payload, to an http request node that I’ve called Query QuestDB. I’ve set the host to be my local machine, the URL to the query API endpoint, and I append the incoming msg.payload to the URL.

The query returns a a JSON string, so I’ll need to run it through a JSON Node to turn it into a JSON Object. I then send the result of that JSON-parsing to 2 additional nodes, one for temperature and one for Humidity. After the JSON Parsing, I get an object back that has several things in it I want to go through.

The first thing to note is that the payload contains a query field that shows the query I executed. Cool! Next, I get a columns field that is an array with an entry for each column if data I am getting back. Since I queried for temp_c, humidity and timestamp I would expect this array to have 3 elements in it, and indeed it does. It also tells me, in each element, the name, and the type of value it has returned which is helpful information.

Finally, there is a dataset field which contains a array of arrays with my data that I requested. Since I requested 5 seconds worth of data, and, if you remember from the previous post, I was sending data once per second, I get back an array with 5 arrays in it, one for each second. By expanding these arrays, I see that I have gotten 2 doubles and a timestamp in each one corresponding to what the columns field told me I would get. Nice! So all that’s left is to send that data to some dashboard elements. Well, almost.

var f = {}
f.topic = "Temperature ºC"
f.payload = msg.payload.dataset[msg.payload.dataset.length-1][0]
f.timestamp = msg.payload.dataset[msg.payload.dataset.length-1][2]
return f

For the Set Temp node, I pull the last element out of the dataset, and grab the temperature value and the timestamp value. I then send those on, as the payload, to the actual Dashboard elements. I do the exact same thing for the Set Humidity Node. By dragging in the dashboard nodes, Node-Red automatically sets up a web dashboard with these elements, and I can go to it and see my new dashboard:

Now that you can actually visualize the data, you can see how awful the data really is! There is no way it’s 2.3º C in my office right now! I guess my next task is to get a real temperature and humidity sensor set up to send more accurate data! Lucky for me, I have a few of those lying around, so that will have to be my next project I guess.

We’re Done Here

As always, please visit our GitHub and give us a star if you think this was useful! You can follow me on twitter, but also follow QuestDB!