Last time, we set up our local machine for accessing AWS programmatically. This will allow us to use Terraform and Terragrunt to easily create all infrastructure needed for our data warehouse. Now, let's set up Terragrunt and Terraform.

Install Terraform

Navigate to the Terraform downloads page:

• Terraform Download

After installing Terraform enter the following in the terminal:

terraform --version

You should be greeted with output similar to:

Terraform v1.2.8
on darwin_arm64

Install Terragrunt

Terragrunt is a thin wrapper for Terraform, having a few additional tools for managing IaC projects.

Download and install it: * Terragrunt Downloads * Terragrunt Package Manager Install

After installing Terragrunt type the following in the terminal:

terragrunt --version

You should get the Terragrunt version as output:

terragrunt version v0.38.7

Create a Git Repository

I'll be using Github, but any git hosting service should be similar.

Use My IaC Template

If you want to skip the next part and use my template repository:

• self_sensored_iac

Visit the page and fork the repository into your Github account, then, clone it locally.

Open a terminal and type, replacing <YOUR_GITHUB_NAME> with your Github username:

git clone https://github.com/<YOUR_GITHUB_NAME>/self_sensored_iac.git

I'll explain in the next section why this repository is setup the way it is.

Create an IaC Template

In Github, create a new repository and call it self_sensored_iac or whatever name you'd like to give your personal enterprise. Then clone this repository locally.

Set "Add README.md" and "Add .gitignore". For the .gitignore template, select Terraform .

git clone https://github.com/<YOUR_NAME>/<YOUR_REPO>.git

Setup Enterprise Terragrunt Project

Whew, we made it. Our work machine is set up. Now, we need to create a Terragrunt project.

The idea of our Terragrunt project is to separate code into two major categories. First, common_modules will contain a folder for each of the major resources you plan to deploy. Imagine these are class definitions. The second category contains all the inputs needed to initialize the resources defined in the common_modules .

The easiest way to create this project is with a folder structure like this:

.
├── README.md
├── common_modules
│   ├── vpc
│   │   ├── data.tf
│   │   ├── main.tf
│   │   ├── outputs.tf
│   │   └── variables.tf
├── empty.yaml
└── prod
    ├── environment.yaml
    └── us-west-2
        ├── aws_provider.tf
        ├── terragrunt.hcl
        ├── region.yaml
        └── vpc
           └── terragrunt.hcl

common_modules

As I mentioned, the commond_modules is similar to a class in object-oriented programming. It is a blueprint of a resource and should be coded in a way to provide appropriate flexibility. That is, if we create a blueprint to set up a VPC, it should probably take only a few inputs. These inputs will then change certain behavior per deployment of the resource.

In Terragrunt, a module is defined by a folder containing a collection of files ending in .tf . Collectively, these files tell Terraform how to create the needed infrastructure within AWS.

Let's look at the VPC code. It is in ./commond_modules/vpc/ As the files may suggest, main.tf is where most of the magic happens. Let's take a look:

module "vpc" {
  source = "terraform-aws-modules/vpc/aws"

  name = "${var.vpc_name}"
  cidr = "${var.vpc_network_prefix}.0.0/16"

  azs             = ["${var.region}a", "${var.region}b", "${var.region}c"]
  private_subnets = ["${var.vpc_network_prefix}.1.0/24", "${var.vpc_network_prefix}.2.0/24", "${var.vpc_network_prefix}.3.0/24"]
  public_subnets  = ["${var.vpc_network_prefix}.101.0/24", "${var.vpc_network_prefix}.102.0/24", "${var.vpc_network_prefix}.103.0/24"]

  enable_nat_gateway = false
  single_nat_gateway = true

  enable_dns_hostnames = true
  enable_dns_support = true

  tags = {
    Terraform = "true"
    Environment = "${var.environment_name}"
  }

  nat_gateway_tags = {
    Project = "${var.project_name}"
    Terraform = "true"
    Environment = "${var.environment_name}"
  }
}

resource "aws_security_group" "allow-ssh" {
  vpc_id      = data.aws_vpc.vpc_id.id
  name        = "allow-ssh"
  description = "Security group that allows ssh and all egress traffic"

  egress {
    from_port   = 0
    to_port     = 0
    protocol    = "-1"
    cidr_blocks = ["0.0.0.0/0"]
  }

  // Allow direct access to the EC2 boxes for me only.
  ingress {
    from_port   = 22
    to_port     = 22
    protocol    = "tcp"
    cidr_blocks = ["${chomp(data.http.myip.body)}/32"]
  }

  tags = {
    Name = "allow-ssh"
  }

}

Note, the module "vpc" is a prebuilt VPC module, so what we are doing is grabbing a VPC module provided by AWS:

• Terraform VPC Module

Which handles a lot of the boilerplate setup. Then, we take care of even more settings we don't often want to change. That is, every place you see ${var.something} we are creating inputs that may be changed at the time of deployment.

Take the VPC name parameter for example:

module "vpc" {
  source = "terraform-aws-modules/vpc/aws"

  name = "${var.vpc_name}"
  ...

Keep this in mind while we look at the prod folder:

prod

The production folder and subfolder are where we consume the modules we have defined in the common_modules folder. Let's look at the /prod/us-west-2/terragrunt.hcl file.

terraform {
  source = "../../..//common_modules/vpc"
}

...

inputs = {
    vpc_name = "ladviens-analytics-stack-vpc"
    vpc_network_prefix = "10.17"
}

The important definitions here are the terraform and inputs maps. The terraform map will tell Terragrunt, when run from this folder, to treat the source directory specified as a Terraform module.

The inputs map contains all of the variables needed to make sure VPC is deployed correctly. You'll notice, we have hardcoded everything in our vpc module but the name and network prefix. This may not be ideal for you. Feel free to change anything in the vpc module files to make it more reusable. And to help, I'll provide an example a bit later in the article.

Planning Our VPC

One of the joys of Terraform is its ability to report what infrastructure would be built before it is actually built. This can be done by running the terragrunt plan command at the terminal when inside the directory ./prod/us-west-2/vpc/ .

At the terminal, navigate to the VPC definition directory:

cd prod/us-west-2/vpc

Then run:

terragrunt plan

You should end up with something like this:

Initializing modules...
Downloading registry.terraform.io/terraform-aws-modules/vpc/aws 3.18.1 for vpc...
- vpc in .terraform/modules/vpc

Initializing the backend...
...

After a little while, Terraform should print a complete plan. This consists of a bunch of diffs. The green + are indicating what will be added. Yellow ~ flagging what will be changed. And a red - indicates resources to be destroyed. At this point, the plan will return showing everything that needs to be built.

Deploying the VPC

Let's deploy the VPC. Still in the ./prod/us-west-2/vpc/ directory, type:

terragrunt apply

Again Terraform will assess the inputs in your terragrunt.hcl and the module definitions in ./common_modules/vpc/ then print out what will be created. However, this time it will ask if you want to deploy. Type yes and hit return.

Terraform will begin requesting resources in AWS on your behalf. Once it is done, I encourage you to open your AWS console and navigate to the "VPC" section. You should see a newly created VPC alongside your default VPC (the one with no name).

Huzzah!

Modifying modules to increase reusability

Let's look at how to add a new input to the vpc module we've made. Open the ./common_modules/vpc/variables.tf file go to the bottom of the file and add:

variable "enable_dns" {

}

We will add more to it, but this is good for now.

A variable in Terraform acts as an input for a module or file. With the enable_dns variable in place, in the terminal, in the directory ./prod/us-west-2/vpc/ , run the following:

terragrunt apply

This time you should be prompted with:

var.enable_dns
  Enter a value: 

This is Terragrunt seeing a variable definition and there's no matching input in your ./prod/us-west-2/vpc/terragrunt.hcl , so it prompts at the command line. This can come in handy, say, having a variable that is a password and you don't want it hard coded in your repository.

But let's go ahead and adjust our terragrunt.hcl to contain the needed input. In the file ./prod/us-west-2/vpc/terragrunt.hcl add the following enable_dns = true . The result should look like this:

terraform {
  source = "../../..//common_modules/vpc"
}

include {
  path = find_in_parent_folders()
}

inputs = {
    vpc_name = "ladviens-analytics-stack-vpc"
    vpc_network_prefix = "10.17"
    enable_dns = true
}

Now run terragrunt apply again. This time Terragrunt should find the input in the terragrunt.hcl file matching the variable name in the module.

Of course, we're not quite done. We still need to use the variable enable_dns in our Terraform module. Open the file ./common_modules/vpc/main.tf and edit the vpc module by modifying these two lines:

  enable_dns_hostnames = true
  enable_dns_support = true

It should look like this:

  enable_dns_hostnames = var.enable_dns
  enable_dns_support = var.enable_dns

Now, if you run terragrunt apply from ./prod/us-west-2/vpc/ again you should not be prompted for variable input.

A couple of clean-up items. First, let's go back to the enable_dns variable definition and add a description and default value. Back in the ./common_modules/vpc/variables.tf update the enable_dns variable to:

variable "enable_dns" {
  type = bool
  default = true
  description = "Should DNS services be enabled." 
}

It's a best practice to add descriptions to all your Terraform definitions, as they can be used to generate a dependency graph by running in the resource defintion folder:

terragrunt graph

But more importantly, make sure you add a type all your variables. They are some instances where Terraform assumes a variable is a type when the input will not be compatible. That is, Terraform may assume and variable is a string, but you meant to provide it a boolean. Trust me. Declaring an appropriate type on all variables will save you a lot of time debugging Terraform code one day--not that I've ever lost a day's work to such a problem.

Last clean-up item, let's go back to the ./commond_modules/vpc/ directory and run:

terraform fmt

This will ensure our code stays nice and tidy.

This last bit is optional, but if you've made additional changes and want to keep them, don't forget to commit them and push them to your repository:

git add .
git commit -m "Init"
git push

Let's Destroy a VPC

Lastly, let's use Terragrunt to destroy the VPC we made. I'm often experimenting on my own AWS account and get worried I'll leave something on waking with an astronomical bill. Getting comfortable with Terragrunt has taken a lot of the fear away, as at the of the day clean up is often as easy as running terragrunt destroy .

Let's use it to destroy our VPC. And don't worry, we can redeploy it by running terragrunt apply again.

In the terminal navigate to ./prod/us-west-2/vpc/ folder and run:

terragrunt destroy

Before you type "yes" ensure you are in the correct directory. If you are, type "yes," hit enter, and watch while Terraform destroys everything we built together.

What's Next

In the a next article we'll begin to add resources to our VPC, but we will take a break from Terraform and Terragrun and use the Serverless Framework to attach an API Gateway to our existing VPC. I'm so excited! 🙌🏽

Before we can begin creating infrastructure through tools like Terraform and the Serverless Framework, we need to set up an AWS account and credentials for accessing AWS through the AWS CLI . The AWS CLI will allow us to easily set up programmatic access to AWS, which is necessary to use Terraform and the Serverless Framework to rapidly deploy needed infrastructure.

Creating an AWS Account

Before beginning into AWS, let me warn you: Stuff can get expensive. Please exercise great caution, as leaving the wrong resource on can lead to a heft bill overnight.

To create an account visit: * Instructions for Creating an AWS Account

Once done, we should have an AWS "root" account. It is best practice to enable multi-factor authentication (MFA) on this account. If someone can access the root account, there's little they can't do.

Another good practice is to create a separate AWS user for programmatic access, we should create a separate user to act as our administration account.

In the end, our IAM dashboard should look like:

Enable MFA on Root Account

Let's create an "admin" account. Go to your account name and then "Settings". Enable MFA.

Create an Admin User

In the search bar above look for "IAM." This is AWS' users and permissions service. Let's make an administrative user; we will add this user's API credentials to our local system for use by Terraform and Serverless Framework.

Now go to "User":

Enter "admin" as the user name and select the "Access key - Programmatic access" option. If you would like to log in to the account from the web, then also select the "Password - AWS Management Console access" option.

Select "Attach existing policies directly":

Skip or add tags, review the new user, then create it.

My recommendation is to use a password manager like 1password or Lastpass to store your "Access Key ID" and "Secret access key" as we will be using them in the next step.

Also, it is a good practice to set up MFA on the admin user as well.

Setting Up the AWS CLI

Next, we need to install the AWS CLI. I usually only use the actual AWS CLI tool to manage credentials or spot-check infrastructure, but both are handy, so worth installing it.

• AWS' Instructions for Installing AWS CLI

After installing open up a terminal. Type the following to ensure it's installed properly.

aws --version

You should get an output like:

aws-cli/2.8.2 Python/3.10.8 Darwin/21.6.0 source/arm64 prompt/off

If you have any trouble, ping me in the comments.

Set Up AWS Programmatic Credentials

Still at the terminal, type:

aws configure

You will be prompted with questions similar to:

AWS Access Key ID [None]: AKIAIOSFODNN7EXAMPLE
AWS Secret Access Key [None]: wJalrXUtnFEMI/K7MDENG/bPxRfiCYEXAMPLEKEY
Default region name [None]: us-west-2
Default output format [None]: 

AWS Access Key ID and AWS Secret Access Key should be retrieved from your password manager.

Default region name and Default output format will depend on you.

For the sake of this article series, I'll be conducting all work in us-west-2 . Do know, many services and resources are localized to the region, so if you create infrastructure in us-west-2 , it will not be visible if you are in the UI but under the region us-west-1 . Also, identical resources in different regions will have different IDs, or Amazon Resource Numbers (ARNs).

If you've any trouble, you might review AWS instructions on programmatic access credential setup: * Config and credential file settings

What's Next

Next, we are going to set up Terraform and Terragrunt so we can easily deploy and manage the needed infrastructure for our data warehouse.

The Reason

I am the lead data engineer at Bitfocus , the best SaaS provider for homeless management information systems ( HMIS ).

For many reasons, I've advocated with our executives to switch our analytics flow to use a data warehouse. Strangely, it worked. I'm now positioned to design the beast. This series will be my humble attempt to journal everything I learn along the way.

The Plan

I've decided it would be better if I learned how to build an analytics warehouse on my own, before committing our entire business to an untested technology stack. Of course, I needed a project similar enough it had the same challenges. I've decided to expose my Apple HealthKit data in a business intelligence platform like Looker.

The Stack

I've attempted similar health data projects before:

• Give Me MyFitness Pal Data!

But I'm going to be a bit more ambitious this time. I'd like to get all of the data generated by my Apple Watch and iPhone out of HealthKit and send them to a SQL database inside AWS.

The ingestion of these data will be lightly structured. That is, no transformations will be done until the data are in the database. I'll use the Auto Health Export iOS app to send data to a web API endpoint.

The web API endpoint will be created using the Severless Framework . This should allow me to build a REST API on top of AWS' Lambda functions, enabling the Auto Health Export app to synchronize data to the database periodically (as low as every 5 minutes, but dictated by Apple).

Once the data are stored in a database, I'll use Dbt (Data Build Tool) to transform the data for exposing them in the business intelligence tool. This idea of processing data inside a database is often referred to as " extract, load, transform " or "ELT," which is becoming standard versus the classical " extract, transform, load " or "ETL."

After transformation, I'll use Dbt to shove the data back into a database built for analytics query processing. Aka, a "data warehouse." The most popular analytics database technologies right now are Snowflake and Redshift. I'll use neither. I can't afford them and I despise how pushy their salespeople are. Instead, I'll use Postgres. (It's probably what Snowflake and Redshift are built on anyway.)

Lastly, I'll stand up Lightdash as the business intelligence tool.

Tools

Auto Health Export

As mentioned, I've attempted to pull data from the Apple ecosystem several times in the past. One challenge I had was writing an iOS app to pull HealthKit data from an Apple device and send it to a REST API. It's not hard. I'm just not an iOS developer, making it time-consuming. And I'm at the point in my tech career I'm comfortable purchasing others' solutions to my problems.

Anyway, not a perfect app, but it does have the ability to pull your recent HealthKit data and send it to a REST API endpoint. * Auto Health Export

Serverless

The team creating our SaaS product has talked about going "serverless" for a while. In an attempt to keep up, I decided to focus on creating Lambda and API Gateway services to receive the data from the Auto Health Export app.

While I was researching serverless architecture I ran into the Serverless Framework. I quite like it. It allows you to focus on the code of Lambda, as it will build out the needed infrastructure on deployment. * Serverless

AWS' Lambda

The heart of the ingestion. This method will receive the health data as JSON and store it in the Postgres database for transformation. * AWS' Lambda

Postgres

I've selected the Postgres database after researching. I'll detail my reason for selecting Postgres later in the series.

Regardless of tech choice, this database will hold untransformed data, or "raw data," as well as act as the processing engine for transforming the data before moving it into the analytics warehouse. * Postgres >=9.5

Dbt

Data Build Tool, or Dbt, is quickly becoming the de facto tool for transforming raw data into an analytics data warehouse. At its core, it uses SQL and Jinja to enable consistent and powerful transformation "models." These models rely on a scalable SQL database, known as a "processing engine," to transform the data before sending it on to its final destination, the analytics warehouse.

• Dbt

MariaDB with ColumnStore

I'd like to use MariaDB as the actual analytics data warehouse. It is an unconventional choice compared to Snowflake or Redshift. But, I'm attempting to use open-source software (OSS) as much as possible, as our company is having a horrific experience with the downfall of Looker.

One of the better-kept secrets about MariaDB is its ColumnStore engine. It allows three major features which have convinced me to try it out:

• It stores data in a column, making it faster for analytics
• The data store is distributed among S3 buckets
• Data are run-length encoded , greatly reducing storage size
• ColumnStore engine tables have join capability with InnoDB tables

I'm also curious whether MariaDB could possibly be used as a processing engine, as I'd prefer to reduce the cognitive complexity of the stack by having only one SQL dialect.

• MariaDB >=10.5.4

Lightdash

At this point, you probably got I'm not happy with what Google has done to Looker. That stated, Looker is still my favorite business intelligence tool. Luckily, there is a budding Looker alternative that meets my OSS requirement: Lightdash. I've never used it, let alone deployed it into production. I guess we'll see how it goes.

• Lightdash

Terraform

You may notice, a data warehouse requires a lot of infrastructure. I hate spinning-up infrastructure through a UI. It isn't repeatable, it's not in version control, and I like writing code. Luckily, Hashicorp's got my back with Terraform. It allows defining and deploying infrastructure as code (IaC).

Terraform

Terragrunt

Terragrunt is a wonderful addition to Terraform. It allows an entire company's infrastructure to be easily maintained.

Terragrunt

The Beginning

First up, let's take a look at the Auto Health Export app and our Serverless architecture. A couple of personal notes before jumping in.

I'm writing this series as a journal of what I learn. That stated, be aware I may switch solutions anywhere in our stack, at any time.

Also, I know a lot of stuff. But there's more stuff I don't know. If I make a mistake, please let me know in the comments. All feedback is appreciated, but respectful feedback is adored.

Data. The world seems to be swimming in it. But what is it good for? Absolutely nothing, unless converted into insights.

I've worked as a data engineer for the last few years and realize this is a fairly universal problem. Converting data into insight is hard. There's too little time. The cloud bill is too much. The data are never clean. And there seems to be the assumption from the C-suite data innately have value. They don't. Data are a raw resource, which can be converted into insights with the skilled people and proper tools. And a data warehouse is one of the best tools for increasing the ease of generating insight from data. Now, insights, those are what we all are chasing, whether we know it or not. In this article, I hope to answer my own questions as to how a data warehouse can help solve drowning in data problem.

Insights over Data

Before moving on, let's define data and insights.

/>

There are many fancy explanations of what insights are. In my opinion, they are gain in knowledge about how something works. In the visualization above, we can see the females score higher than males, and males lower than non-binary genders. This is insight.

Looking at the "Data" pane, little insight can be gained. There is information , such as an email we could use to contact someone. Or their name, by which to address them. However, there isn't anything which explains how something works. These are data.

Unfortunately, in information technology, converting data into insight is tough. As I hope to demonstrate.

Heart Rate Variability

Throughout this article I'm going to use examples regarding Heart Rate Variability . As the name suggests, it is a measure of variability between heart-beats. To be clear, not the time between heart-beats, but the variability .

I find heart rate variability, or HRV, fascinating because it is linked to human stress levels. Research seems to indicate if HRV is high, then you are relaxed. But if your HRV is low, you are stressed. Evolutionarily, this makes sense. During times of stress your heart is one of the most important organs in your body. It focuses up and ensures all muscles and organs are ready for fighting hard or running harder. The famous fight-or-flight. However, during times of relaxation, your heart chills out too. Its beats are less well timed. Hey, your heart works hard!

Not required to follow this article, but if you want to read more about HRV:

• HRV psychological and social aspects

I got interested in HRV as I found Apple Watch now tracks it. And I've been curious if it is an actual valid indicator of my stress levels.

What's the Problem?

So what does this have to do with a data warehouses? Let's look at the HRV data the Apple Watch produces.

These data have a start and end time, and if the end time is blank it means it is still going. They also have a user_id and the heart_rate_variability score. Let's try to ask the data a question.

• What is my average HRV per day?

A naive method for querying these data might look like:

  SELECT AVG(heart_rate_variability)   AS avg_hrv,
         COALESCE(DATE(start), DATE('')) AS day
    FROM df
GROUP BY day

This produces the following:

/>

This looks pretty good. We can get a bit of insight, it looks like my average HRV might be 50. And it looks like 09-02 was an extremely relaxed day. But, what happened to 2021-09-03 ?

This is where things start getting complex. There is a long history in databases of converting sustained values to a start and stop value. Databases are highly optimized for updating records quickly and minimizing the amount of disk space needed to store the data. These types of databases are known as online transactional processing (OLTP) databases. And they are the antithesis of a data warehouse. Data warehouses are known as online analytical processing (OLAP) databases. Unlike OLTP, they are optimized for speed of providing insights. I'll go in more depth about OLTP vs. OLAP a bit later.

Let's get back to solving the problem.

The Infamous Calendar Table

Let's look at what the start-stop data actually represent.

Simple enough, right? But how do we transform the start and stop data into a list of all dates with sustained values. The most straightforward method is the calendar table.

In SQL, every query must begin with a base table. This is the table associated with the FROM keyword. This is the base for all other data are retrieved. In our problem, what we really need is a base table which fills in all the possible missing dates in the start-stop data.

In this situation, it is known as a "calendar table" and is simply a list of possible dates you wish to include in your query.

For example:

Nice! But how do we apply this to our question?

• What is my average HRV per day?

We can apply it with the following query:

   SELECT
          AVG(hrv) avg_hrv,
          c.date
     FROM calendar AS c
LEFT JOIN hrvs AS h
       ON c.date BETWEEN DATE(start)
                     AND COALESCE(DATE(end), DATE('2021-09-05'))
    GROUP BY c.date

This joins all of the HRV start-stop values to the calendar table where the calendar.date falls between the hrvs.start and hrvs.stop dates. This is the result:

/>

Perfect, this has the exact effect we wish. It expands the start-stop dates for all days and calculates the average heart rate variability per day. You may ask, "But this only generated one extra date right? Do we really need a data warehouse for it; can't a classical database solve it?" Yes, yes it can. But! Is it really one extra day?

Let's change our question slightly.

• What is my average HRV per hour ?

Some of you may see where I'm going here, but let's just draw it out anyway. We can answer the above question with another calendar table, but this one with a finer grain (level of granularity). That is, our calendar table will now have an entry for every hour between the minimum start and maximum exit.

Then, we can run the following query:

   SELECT
          AVG(hrv) avg_hrv,
          c.date
     FROM calendar AS c
LEFT JOIN hrvs AS h
       ON c.date BETWEEN start
                     AND COALESCE(end, '2021-09-05 23:59:59')
    GROUP BY c.date

Our result should look something like this:

/>

And here in lies the problem. When we begin to try and expand the data, they have a tendency to grow in number. These sorts of relationships have all sorts of terms associated with it. In SQL, we refer to the relationship having a one-to-many or many-to-many relationship. That is, one or more dates in the calendar table refer to many start-stop entries in the hrv table. And when the result increases greatly due to a join, we refer to this as "fan out."

Just One More

At this point you may be wondering if this calendar table was really the best way to answer the questions we have. Well, I'm going to risk boring you by giving you just a few more examples.

Let's say you want to ask the data:

• What is average HRV of everyone per hour? :exploding_head:

This would take data like this:

And join it to the calendar table. The math, roughly, works out to something like this.

[minutes between min and max dates] x [users] = [rows of data]

And to make it even more apparent, what if we ask something like:

• What is average HRV of everyone per minute?

2 user x 365 days x 24 hours x 60 minute = 1,051,200

For 5 users?

5 user x 525,600 minute = 2,628,000

100?

100 user x 525,600 minute = 52,560,000

This sheer number of rows of data which must be processed is the reason for a data warehouse.

OLTP

Let's examine Online Transactional Processing databases to understand why they have difficulty with reading large number of rows. First, what exactly are we talking about when we say "an OLTP database"? In the wild, most database technologies are OLTP, as they fit the most common need of a database.

For example,

• SQLite
• Postgres
• MariaDB
• MySQL
• Microsoft SQL Server
• Orcale

There are many, many more.

A clever individual may argue, "Wait, these database technologies are capable of being setup as an OLAP database." True, many of the above have "plugins" or settings to allow them to act as an analytics database, however, their primary purpose is transactional processing. This leads us to the question, "What is 'transactional processing' anyway?"

Don't let the name try to trick you. Transactional processing databases are exactly how they sound. They are technologies optimized for large amounts of small transactions. These transactions may be retrieving a username, then updating a service entry. Or retrieving a user ID, then adding an address. But most involve at least one read and write operation, thus "transaction." These transactions are usually extremely short and involve a small amount of data. Let's go through the details of the characteristics a bit more.

Supports Large Amounts of Concurrency

OLTP DBs are designed for large amounts of users making short queries of the database. This makes them perfect for the backend of many software applications. Let's take a small online bookstore called Amazingzone. This bookstore will often have 300 or so customers interacting with their webpage at a time.

Each time they click on a product the web application sends a query to the products table and retrieves the product information. Then, it updates a record in the visitor table indicating a user viewed a product. Given each customer is casually browsing, this may lead to a few thousand queries a minute. Each query would look something like this:

SELECT product_name, product_image_path
  FROM products
 WHERE id = 534;
 INSERT user_id, view_date INTO visitors VALUES (754, CURRENT_DATE());

Even though though there are thousands of queries a minute, each one is retrieving a single record, from a single table. This is what is meant when you see a database which is "optimized for concurrency." It means the database is designed to allow thousands of small queries to be executed simultaneously.

Data are Normalized

Another characteristic of an OLTP database is the data are often normalized into at least the 2rd Normal Form . An oversimplified explanation would be: "More tables, less data."

One of the goals of normalization is to reduce the amount of disk space a database needs. This comes from an age where databases were limited to kilobytes of data rather than exabytes. With that in mind, for the following example let's assume every cell beside the field headers require 1 byte regardless of the datatype or information contained.

Given the table above, it would take 24 bytes to store all the information on a database. If our goal is reducing disk space, 2nd Normal Form (2NF) can help. Let's see what these same data look like after "normalization."

We will split the data into two tables, hrv_recordings and users .

Here's the hrv_recordings table:

And the users table:

Now we have two tables joined by the id of the user's table, the Primary Key , and the user_id in the hrv_recordings table, the Foreign Key . When we query these data we can put the tables back together by using the JOIN keyword.

For example:

SELECT *
  FROM hrv_recordings AS hrvr
  JOIN users AS u
    ON hrvr.user_id = u.user_id

But why should we normalize these data in the first place. Well, if you go back and count the number of cells after normalization you will find their are 19. For our oversimplified scenario, that means we have saved 5 bytes through normalization! Pretty nifty.

And if 5 bytes doesn't seem impressive, change it to a percentage. This represents a 20% savings. Let's say your database is 100 GBs and our normalization savings scale linearly. The means you saved 20 GBs simply by changing how your data are stored! Nice job, you.

But the skeptics may be curious at about the catch. Well, the catch is the one reason normalized databases aren't great for analytics. Normalization optimizes for reduced disk space needed and quick inserts, not how quickly the data can be read.

Going back to the SQL query:

SELECT *
  FROM hrv_recordings AS hrvr
  JOIN users AS u
    ON hrvr.user_id = u.user_id

What's really happening in this join can be simplified into a comparison loop.

For example:

for i in range(hrv_recordings.shape[0]):
  for j in range(users.shape[0]):
    if hrv_recordings[i:i+1,0] == users[j:j+1,0]:
      return (hrv_recordings[i:i+1,:], users[j:j+1,:])

If the above Python snippet doesn't make sense, ignore it. Just know joins in SQL come with a computation cost every time a query is executed. And that's the primary trade off with normalization, you are reducing the amount of disk space needed to store the data at the cost of retrieving the data quickly.

Row Based

One of the greatest tricks of OLTPs is stored data in a row object. What does that mean? Let's look at an example.

Consider the data above. This is how they when you query the database, but how are they actually stored on the database? Well, they are stored in objects based on the row. For example, the above would be stored in text file something like

(1,47.5,2021-09-01,1,Thomas,41)
(2,120,2021-09-02,1,Thomas,41)
(3,120,2021-09-03,1,Thomas,41)
(4,65,2021-09-04,1,Thomas,41)

Each of the rows are an object. This actually gives huge benefits for a transactional database.

Take adding a new record for example, we could send the following to the database.

INSERT INTO hrv_recordings VALUES (70,"2021-09-05","Thomas", 55)

After execution, the database would like this:

(1,47.5,2021-09-01,1,Thomas,41)
(2,120,2021-09-02,1,Thomas,41)
(3,120,2021-09-03,1,Thomas,41)
(4,65,2021-09-04,1,Thomas,41)
(5,70,2021-09-05,Thomas, 41)

Note, the first value is the primary key and we will assume it automatically increments by 1 on every inserts into the table.

Ok, so what's the big deal? A lot actually. The insert did not need to move around any other data to be inserted. That is, row 1-4 were completely untouched. They didn't need to be moved. They didn't need to be updated. Nothing. The advantage of this for transactional databases really only becomes apparent when you compare it to a database which stores data as a column.

Let's take the same data and setup it up to be stored in columns

Here is how those same data would look if stored as column objects. And now, to insert the same data we would need to open the id column, insert the new value. Open the hrv column and insert the new value and resave it. And so forth.

(1,2,3,4)
(47,120,120,65)
(2021-09-01,2021-09-02,2021-09-03,2021-09-04)
(Thomas,Thomas,Thomas,Thomas)
(41,41,41,41)

This means, for the same insert, we have to do 5 inserts. Each one of these actions is blazing fast, but still, not as fast the row based insert which only took 1 insert action.

Fast Inserts, Updates, Upserts

Now we have talked about normalized and row based data structures, we can now see what "optimized for writing" means.

Let's revisit the normalized data structure. We have two small normalized tables, hrv_recordings and users .

Here's the hrv_recordings table:

And the users table:

Let's say a new user signs up, given this is a row based database and the data are broken into separate tables, we can add a user simply by inserting one row in the users table.

That is, to insert Jane who is 25 , we would simply add a row at the end.

(Thomas,41)
(Jane,25)

We don't need to add any rows to other tables, as Jane did not come into the system with any data.

Now, if the data were not normalized, we'd have to update this table

With the following row:

(NULL,NULL,Jane,25)

And the resulting table would be:

(1,47.5,2021-09-01,1,Thomas,41)
(2,120,2021-09-02,1,Thomas,41)
(3,120,2021-09-03,1,Thomas,41)
(4,65,2021-09-04,1,Thomas,41)
(5,NULL,NULL,Jane,25)

But this is wasteful, as there is no reason to insert NULL s.

OLAPs in Judgement

Let's Talk Rows and Columns

As the name suggests, MariaDB ColumnStore stores the data in column format. This is referred to a columnar database management system (CDBMS). They differ from the row based database management system (RDBMS) in how they store data. There is lots of history on why, but historically, the world has used RDBMS to store data.

You can think of CDBMS and RDBMS as tools for specific jobs. They both work with data, but they have pros and cons which must be assessed when applying them.

Here is my summary comparison of the two systems.

This articles relies on the code written by Fabian Bosler:

• Image Scraping with Python

I've only modified Bosler's code to make it a bit easier to pull images for multiple search terms.

The full code can be found the series' Github repository:

• deep_arcane -- scrap.py

Magic Symbols

As I've mentioned in my previous article, I needed a lot of images of magic symbols for training a deep convolutional generative adversarial network (DCGAN). Luckily, I landed on Bosler's article early on.

To get my images, I used Chrome browser, Chromedriver, Selenium, and a Python script to slowly scrape images from Google's image search. The scraping was done throttled to near human speed, but allowed automating the collection of a lot of images.

Regarding this process, I'll echo Bosler, I'm in no way a legal expert. I'm not a lawyer and nothing I state should be taking as legal advice. I'm just some hack on the internet. However, from what I understand, scraping the SERPs (search engine results pages) is not illegal, at least, not for personal use. But using Google's Image search for automated scraping of images is against their terms of service ( ToS ). Replicate this project at your own risk. I know when I adjusted my script to search faster Google banned my IP. I'm glad it was temporary.

Bosler's Modified Script

The script automatically searches for images and collects their underlying URL. After searching, it uses the Python requests library to download all the images into a folder named respective to the search term.

Here are the modifications I made to Bosler's original script: * Added a search term loop. This allows the script to continue running past one search term.
* The script was getting stuck when it ran into the "Show More Results," I've fixed the issue. * The results are saved in directories associated with the search term. If the script is interrupted and rerun it will look at what directories are created first, and remove those from the search terms. * I added a timeout feature; thanks to a user on Stack Overflow . * I parameterized the number of images to look for per search term, sleep times, and timeout.

Code: Libraries

You will need to install Chromedriver and Selenium--this is explained well in the original article.

• Image Scraping with Python

You will also need to install Pillow --a Python library for managing images.

You can install it with:

pip install pillow

After installing all the needed libraries the following block of code should execute without error:

import os
import time

import io
import hashlib
import signal
from glob import glob
import requests

from PIL import Image
from selenium import webdriver

If you have any troubles, revisit the original articles setup explanation or feel free to ask questions in the comments below.

Code: Parameters

I've added a few parameters to the script to make use easier.

number_of_images = 400
GET_IMAGE_TIMEOUT = 2
SLEEP_BETWEEN_INTERACTIONS = 0.1
SLEEP_BEFORE_MORE = 5
IMAGE_QUALITY = 85

output_path = "/path/to/your/image/directory"

The number_of_images tells the script how many images to search for per search term. If the script runs out of images before reaching number_of_images , it will skip to the next term.

GET_IMAGE_TIMEOUT determines how long the script should wait for a response before skipping to the next image URL.

SLEEP_BETWEEN_INTERACTIONS is how long the script should delay before checking the URL of the next image. In theory, this can be set low, as I don't think it makes any requests of Google. But I'm unsure, adjust at your own risk.

SLEEP_BEFORE_MORE is how long the script should wait before clicking on the "Show More Results" button. This should not be set lower than you can physically search. Your IP will be banned. Mine was.

Code: Search Terms

Here is where the magic happens. The search_terms array should include any terms which you think will get the sorts of images you are targeting.

Below are the exact set of terms I used to collect magic symbol images:

search_terms = [
    "black and white magic symbol icon",
    "black and white arcane symbol icon",
    "black and white mystical symbol",
    "black and white useful magic symbols icon",
    "black and white ancient magic sybol icon",
    "black and white key of solomn symbol icon",
    "black and white historic magic symbol icon",
    "black and white symbols of demons icon",
    "black and white magic symbols from book of enoch",
    "black and white historical magic symbols icons",
    "black and white witchcraft magic symbols icons",
    "black and white occult symbols icons",
    "black and white rare magic occult symbols icons",
    "black and white rare medieval occult symbols icons",
    "black and white alchemical symbols icons",
    "black and white demonology symbols icons",
    "black and white magic language symbols icon",
    "black and white magic words symbols glyphs",
    "black and white sorcerer symbols",
    "black and white magic symbols of power",
    "occult religious symbols from old books",
    "conjuring symbols",
    "magic wards",
    "esoteric magic symbols",
    "demon summing symbols",
    "demon banishing symbols",
    "esoteric magic sigils",
    "esoteric occult sigils",
    "ancient cult symbols",
    "gypsy occult symbols",
    "Feri Tradition symbols",
    "Quimbanda symbols",
    "Nagualism symbols",
    "Pow-wowing symbols",
    "Onmyodo symbols",
    "Ku magical symbols",
    "Seidhr And Galdr magical symbols",
    "Greco-Roman magic symbols",
    "Levant magic symbols",
    "Book of the Dead magic symbols",
    "kali magic symbols",
]

Before searching, the script checks the image output directory to determine if images have already been gathered for a particular term. If it has, the script will exclude the term from the search. This is part of my "be cool" code. We don't need to be downloading a bunch of images twice.

The code below grabs all the directories in our output path, then reconstructs the search term from the directory name (i.e., it replaces the "_"s with " "s.)

dirs = glob(output_path + "*")
dirs = [dir.split("/")[-1].replace("_", " ") for dir in dirs]
search_terms = [term for term in search_terms if term not in dirs]

Code: Chromedriver

Before starting the script, we have to kick off a Chromedriver session. Note, you must put the chromedriver executable into a folder listed in your PATH variable for Selenium to find it.

For MacOS users, setting up Chromedriver for Selenium use is a bit tough to do manually. But, using homebrew makes it easy.

brew install chromedriver

If everything is setup correctly, executing the following code will open a Chrome browser and bring up the Google search page.

wd = webdriver.Chrome()
wd.get("https://google.com")

Code: Chrome Timeout

The timeout class below I borrowed from Thomas Ahle at Stack Overflow . It is a dirty way of creating a timeout for the GET request to download the image. Without it, the script can get stuck on unresponsive image downloads.

class timeout:
    def __init__(self, seconds=1, error_message="Timeout"):
        self.seconds = seconds
        self.error_message = error_message

    def handle_timeout(self, signum, frame):
        raise TimeoutError(self.error_message)

    def __enter__(self):
        signal.signal(signal.SIGALRM, self.handle_timeout)
        signal.alarm(self.seconds)

    def __exit__(self, type, value, traceback):
        signal.alarm(0)

Code: Fetch Images

As I've hope I made clear, the code below I did not write; I just polished it. I'll provide a brief explanation, but refer back to Bosler's article for more information.

Essentially, the script: 1. Creates a directory corresponding to a search term in the array. 2. It passes the search term to the fetch_image_urls() , this function drives the Chrome session. The script navigates the Google to find images relating to the search term. It stores the image link in an list. After it has searched through all the images or reached the num_of_images it returns a list ( res ) containing all the image URLs. 3. The list of image URLs is passed to the persist_image() , which then downloads each one of the images into the corresponding folder. 4. It repeats steps 1-3 per search term.

I've added extra comments as a guide:

def fetch_image_urls(
    query: str,
    max_links_to_fetch: int,
    wd: webdriver,
    sleep_between_interactions: int = 1,
):
    def scroll_to_end(wd):
        wd.execute_script("window.scrollTo(0, document.body.scrollHeight);")
        time.sleep(sleep_between_interactions)

    # Build the Google Query.
    search_url = "https://www.google.com/search?safe=off&site=&tbm=isch&source=hp&q={q}&oq={q}&gs_l=img"

    # load the page
    wd.get(search_url.format(q=query))

    # Declared as a set, to prevent duplicates.
    image_urls = set()
    image_count = 0
    results_start = 0
    while image_count < max_links_to_fetch:
        scroll_to_end(wd)

        # Get all image thumbnail results
        thumbnail_results = wd.find_elements_by_css_selector("img.Q4LuWd")
        number_results = len(thumbnail_results)

        print(
            f"Found: {number_results} search results. Extracting links from {results_start}:{number_results}"
        )

        # Loop through image thumbnail identified
        for img in thumbnail_results[results_start:number_results]:
            # Try to click every thumbnail such that we can get the real image behind it.
            try:
                img.click()
                time.sleep(sleep_between_interactions)
            except Exception:
                continue

            # Extract image urls
            actual_images = wd.find_elements_by_css_selector("img.n3VNCb")
            for actual_image in actual_images:
                if actual_image.get_attribute(
                    "src"
                ) and "http" in actual_image.get_attribute("src"):
                    image_urls.add(actual_image.get_attribute("src"))

            image_count = len(image_urls)

            # If the number images found exceeds our `num_of_images`, end the seaerch.
            if len(image_urls) >= max_links_to_fetch:
                print(f"Found: {len(image_urls)} image links, done!")
                break
        else:
            # If we haven't found all the images we want, let's look for more.
            print("Found:", len(image_urls), "image links, looking for more ...")
            time.sleep(SLEEP_BEFORE_MORE)

            # Check for button signifying no more images.
            not_what_you_want_button = ""
            try:
                not_what_you_want_button = wd.find_element_by_css_selector(".r0zKGf")
            except:
                pass

            # If there are no more images return.
            if not_what_you_want_button:
                print("No more images available.")
                return image_urls

            # If there is a "Load More" button, click it.
            load_more_button = wd.find_element_by_css_selector(".mye4qd")
            if load_more_button and not not_what_you_want_button:
                wd.execute_script("document.querySelector('.mye4qd').click();")

        # Move the result startpoint further down.
        results_start = len(thumbnail_results)

    return image_urls


def persist_image(folder_path: str, url: str):
    try:
        print("Getting image")
        # Download the image.  If timeout is exceeded, throw an error.
        with timeout(GET_IMAGE_TIMEOUT):
            image_content = requests.get(url).content

    except Exception as e:
        print(f"ERROR - Could not download {url} - {e}")

    try:
        # Convert the image into a bit stream, then save it.
        image_file = io.BytesIO(image_content)
        image = Image.open(image_file).convert("RGB")
        # Create a unique filepath from the contents of the image.
        file_path = os.path.join(
            folder_path, hashlib.sha1(image_content).hexdigest()[:10] + ".jpg"
        )
        with open(file_path, "wb") as f:
            image.save(f, "JPEG", quality=IMAGE_QUALITY)
        print(f"SUCCESS - saved {url} - as {file_path}")
    except Exception as e:
        print(f"ERROR - Could not save {url} - {e}")

def search_and_download(search_term: str, target_path="./images/", number_images=5):
    # Create a folder name.
    target_folder = os.path.join(target_path, "_".join(search_term.lower().split(" ")))

    # Create image folder if needed.
    if not os.path.exists(target_folder):
        os.makedirs(target_folder)

    # Open Chrome
    with webdriver.Chrome() as wd:
        # Search for images URLs.
        res = fetch_image_urls(
            search_term,
            number_images,
            wd=wd,
            sleep_between_interactions=SLEEP_BETWEEN_INTERACTIONS,
        )

        # Download the images.
        if res is not None:
            for elem in res:
                persist_image(target_folder, elem)
        else:
            print(f"Failed to return links for term: {search_term}")

# Loop through all the search terms.
for term in search_terms:
    search_and_download(term, output_path, number_of_images)

Results

Scraping tehe images resulted in a lot of garbage images (noise) along with my ideal training images.

For example, out of all the images shown, I only wanted the image highlighted: />

There was also the problem of lots of magic symbols stored in a single image. These "collection" images would need further processing to extract all of the symbols.
/>

However, even with a few rough edges, the script sure as hell beat manually downloading the 10k images I had in the end.

I love folklore dealing with magic. Spells, witches, and summoning the dead. It all piques my interest. I think it inspires me as it is far removed from being a data engineer--I know it might kill aspirations of young data engineers reading, but data engineering can be a bit boring at times. To beat the boredom, I decided to mix my personal and professional interests.

I've scraped the internet for images of magic symbols, then trained a deep convolutional generative adversarial network ( DCGAN ) to generate new magic symbols, which are congruent to real magic symbols. The DCGAN is built using PyTorch. I usually roll with Tensorflow, but working on learning PyTorch.

I've taken the "nothing but net" approach with this project. Most of the data augmentation I've done during this project have been using other neural networks. Most of these augmenting nets were written in Tensorflow.

I've planned a series of articles, as there is too much to cover in one. A lot of the code has been borrowed and adapted; I'll do my best to give credit where it's due.

What was in my Head

Let's start with current results first. After getting the urge to teach a computer to make a magic sign, it took a couple days of hacking before I ended up with the images below.

Keep in mind, these are preliminary results . They were generated using my GTX 1060 6GB. The GPU RAM limits the model a lot--at least, until I rewrite the training loop. Why do I mention the the small GPU? Well, GANs are an architecture which provide much better results with more neurons. And the 6GB limits the network a lot for well performing GAN.

Anyway, 'nuff caveats. Let's dig in.

Signal

There are a few concepts I'll refer to a lot throughout these articles--let's define real quick.

First, " signal ." I like Wikipedia's definition, even if it is sleep inducing.

In signal processing, a signal is a function that conveys information about a phenomenon.

One of the mistakes I made early in this project was not defining the desired signal. In future projects, I'll lead with a written definition and modify it based on what I learn about the signal. However, for this project, here was my eventual definition.

The "magic symbol" signal had the following properties: * Used in traditional superstition * Defined

These terms became my measuring stick for determining whether an image was included in the training data.

Given poorly defined training images seemed to produce extremely muddy outputs, I decided each image should be "defined." Meaning, an image must be easily discernible at the resolution in which it was trained.

Here are examples of what I see as "defined": />

And examples of "used in traditional superstition." The top-left symbol is the Leviathan Cross and bottom-left is the Sigil of Bael .

/>

Results

Again, preliminary results. I'm shopping for a way to scale up the size of the network, which should increase the articulation of the outputs. Overall, the bigger the network the more interesting the results.

/>

Small Symbols (64x64)

The following symbols were generated with a DCGAN using 64x64 dimensions as output. These symbols were then post-processed by using a deep denoising varational auto-encoder (DDVAE). It was a fancy way of removing " pepper " from the images.

/>

Large Symbols (128x128)

The following symbols were generated with a GAN using 128x128 dimensions as input and output. These symbols were not post-processed.

/>

Assessment of Outputs

Overall, I'm pleased with the output. Looking at how muddy the outputs are on the 128x128 you may be wondering why. Well, a few reasons.

I've been able to avoid mode collapse in almost all of my training sessions. Mode collapse is the bane of GANs. Simply put, the generator finds one or two outputs which always trick the discriminator and then produces those every time.

There is a lot of pepper throughout the generated images. I believe a lot of this comes from dirty input data, so when there's time, I'll refine my dataset further. However, the denoising auto-encoder seems to be the easiest way to get rid of the noise--as you can see the 64x64 samples (denoised) are much cleaner than the 128x128 samples. Also, I might try applying the denoiser to the inputs, rather than the outputs. In short, I feel training will greatly improve as I continue to refine the training data.

But do they look like real magic symbols? I don't know. At this point, I'm biased, so I don't trust my perspective. I did show the output to a coworker and asked, "What does this look like?" He said, "I don't know, some sort of runes?" And my boss asked, "What are those Satan symbols?" So, I feel I'm on the right track.

A how-to guide on connecting your PC to an Arduino using Bluetooth LE and Python. To make it easier, we will use bleak an open source BLE library for Python. The code provided should work for connecting your PC to any Bluetooth LE devices.

Before diving in a few things to know

• Bleak is under-development. It will have issues
• Although Bleak is multi-OS library, Windows support is still rough
• PC operating systems suck at BLE
• Bleak is asynchronous; in Python, this means a bit more complexity
• The code provided is a proof-of-concept; it should be improved before use

Ok, all warnings stated, let's jump in.

Bleak

Bleak is a Python package written by Henrik Blidh . Although the package is still under development, it is pretty nifty. It works on Linux, Mac, or Windows. It is non-blocking, which makes writing applications a bit more complex, but extremely powerful, as your code doesn't have to manage concurrency.

• Bleak Documentation
• Bleak Github

Setup

Getting started with BLE using my starter application and bleak is straightforward. You need to install bleak and I've also included library called aioconsole for handling user input asynchronously

pip install bleak aioconsole

Once these packages are installed we should be ready to code. If you have any issues, feel free to ask questions in the comments. I'll respond when able.

The Code

Before we get started, if you'd rather see the full-code it can be found at:

• Bleak App

If you are new to Python then following code may look odd. You'll see terms like async , await , loop , and future . Don't let it scare you. These keywords are Python's way of allowing a programmer to "easily" write asynchronous code in Python.

If you're are struggling with using asyncio , the built in asynchronous Python library, I'd highly recommend Łukasz Langa's detailed video series; it takes a time commitment, but is worth it.

• Import asyncio

If you are an experienced Python programmer, feel free to critique my code, as I'm a new to Python's asynchronous solutions. I've got my big kid britches on.

Enough fluff. Let's get started.

Application Parameters

There are a few code changes needed for the script to work, at least, with the Arduino and firmware I've outlined in the previous article:

• Getting Started with Bluetooth LE on the Arduino Nano 33 Sense

The incoming microphone data will be dumped into a CSV; one of the parameters is where you would like to save this CSV. I'll be saving it to the Desktop. I'm also retrieving the user's home folder from the HOME environment variable, which is only available on Mac and Linux OS (Unix systems). If you are trying this project from Windows, you'll need to replace the root_path reference with the full path.

root_path = os.environ["HOME"]
output_file = f"{root_path}/Desktop/microphone_dump.csv"

You'll also need need to specify the characteristics which the Python app should try to subscribe to when connected to remote hardware. Referring back to our previous project, you should be able to get this from the Arduino code. Or the Serial terminal printout.

read_characteristic = "00001143-0000-1000-8000-00805f9b34fb"
write_characteristic = "00001142-0000-1000-8000-00805f9b34fb"

Main

The main method is where all the async code is initialized. Essentially, it creates three different loops, which run asynchronously when possible.

• Main -- you'd put your application's code in this loop. More on it later
• Connection Manager -- this is the heart of the Connection object I'll describe more in a moment.
• User Console -- this loop gets data from the user and sends it to the remote device.

You can imagine each of these loops as independent, however, what they are actually doing is pausing their execution when any of the loops encounter a blocking I/O event. For example, when input is requested from the user or waiting for data from the remote BLE device. When one of these loops encounters an I/O event, they let one of the other loops take over until the I/O event is complete.

That's far from an accurate explanation, but like I said, I won't go in depth on async Python, as Langa's video series is much better than my squawking.

Though, it's important to know, the ensure_future is what tells Python to run a chunk of code asynchronously. And I've been calling them "loops" because each of the 3 ensure_future calls have a while True statement in them. That is, they do not return without error.

After creating the different futures, the loop.run_forever() is what causes them to run.

if __name__ == "__main__":
    # Create the event loop.
    loop = asyncio.get_event_loop()

    data_to_file = DataToFile(output_file)
    connection = Connection(
        loop, read_characteristic, write_characteristic, data_to_file.write_to_csv
    )
    try:
        asyncio.ensure_future(main())
        asyncio.ensure_future(connection.manager())
        asyncio.ensure_future(user_console_manager(connection))
        loop.run_forever()
    except KeyboardInterrupt:
        print()
        print("User stopped program.")
    finally:
        print("Disconnecting...")
        loop.run_until_complete(connection.cleanup())

Where does bleak come in? You may have been wondering about the code directly before setting up the loops.

    connection = Connection(
        loop, read_characteristic, write_characteristic, data_to_file.write_to_csv
    )

This class wrap the bleak library and makes it a bit easier to use. Let me explain.

Connection()

You may be asking, "Why create a wrapper around bleak , Thomas?" Well, two reasons. First, the bleak library is still in development and there are several aspects which do not work well. Second, there are additional features I'd like my Bluetooth LE Python class to have. For example, if you the Bluetooth LE connection is broken, I want my code to automatically attempt to reconnect. This wrapper class allows me to add these capabilities.

I did try to keep the code highly hackable. I want anybody to be able to use the code for their own applications, with a minimum time investment.

Connection(): init

The Connection class has three required arguments and one optional.

• loop -- this is the loop established by asyncio , it allows the connection class to do async magic.
• read_characteristic -- the characteristic on the remote device containing data we are interested in.
• write_characteristic -- the characteristic on the remote device which we can write data.
• data_dump_handler -- this is the function to call when we've filled the rx buffer.
• data_dump_size -- this is the size of the rx buffer. Once it is exceeded, the data_dump_handler function is called and the rx buffer is cleared.

class Connection:

    client: BleakClient = None

    def __init__(
        self,
        loop: asyncio.AbstractEventLoop,
        read_characteristic: str,
        write_characteristic: str,
        data_dump_handler: Callable[[str, Any], None],
        data_dump_size: int = 256,
    ):
        self.loop = loop
        self.read_characteristic = read_characteristic
        self.write_characteristic = write_characteristic
        self.data_dump_handler = data_dump_handler
        self.data_dump_size = data_dump_size

Alongside the arguments are internal variables which track device state.

The variable self.connected tracks whether the BleakClient is connected to a remote device. It is needed since the await self.client.is_connected() currently has an issue where it raises an exception if you call it and it's not connected to a remote device. Have I mentioned bleak is in progress?

        # Device state
        self.connected = False
        self.connected_device = None

self.selected_device hangs on to the device you selected when you started the app . This is needed for reconnecting on disconnect.

The rest of variables help track the incoming data. They'll probably be refactored into a DTO at some point.

        # RX Buffer
        self.last_packet_time = datetime.now()
        self.rx_data = []
        self.rx_timestamps = []
        self.rx_delays = []

Connection(): Callbacks

There are two callbacks in the Connection class. One to handle disconnections from the Bluetooth LE device. And one to handle incoming data.

Easy one first, the on_disconnect method is called whenever the BleakClient loses connection with the remote device. All we're doing with the callback is setting the connected flag to False . This will cause the Connection.connect() to attempt to reconnect.

    def on_disconnect(self, client: BleakClient):
        self.connected = False
        # Put code here to handle what happens on disconnet.
        print(f"Disconnected from {self.connected_device.name}!")

The notification_handler is called by the BleakClient any time the remote device updates a characteristic we are interested in. The callback has two parameters, sender , which is the name of the device making the update, and data , which is a bytearray containing the information received.

I'm converting the data from two-bytes into a single int value using Python's from_bytes() . The first argument is the bytearray and the byteorder defines the endianness (usually big ). The converted value is then appended to the rx_data list.

The record_time_info() calls a method to save the current time and the number of microseconds between the current byte received and the previous byte.

If the length of the rx_data list is greater than the data_dump_size , then the data are passed to the data_dump_handler function and the rx_data list is cleared, along with any time tracking information.

    def notification_handler(self, sender: str, data: Any):
        self.rx_data.append(int.from_bytes(data, byteorder="big"))
        self.record_time_info()
        if len(self.rx_data) >= self.data_dump_size:
            self.data_dump_handler(self.rx_data, self.rx_timestamps, self.rx_delays)
            self.clear_lists()

Connection(): Connection Management

The Connection class's primary job is to manage BleakClient 's connection with the remote device.

The manager function is one of the async loops. It continually checks if the Connection.client exists, if it doesn't then it prompts the select_device() function to find a remote connection. If it does exist, then it executes the connect() .

    async def manager(self):
        print("Starting connection manager.")
        while True:
            if self.client:
                await self.connect()
            else:
                await self.select_device()
                await asyncio.sleep(15.0, loop=loop)       

The connect() is responsible for ensuring the PC's Bluetooth LE device maintains a connection with the selected remote device.

First, the method checks if the the device is already connected, if it does, then it simply returns. Remember, this function is in an async loop.

If the device is not connected, it tries to make the connection by calling self.client.connect() . This is awaited, meaning it will not continue to execute the rest of the method until this function call is returned. Then, we check if the connection is was successful and update the Connection.connected property.

If the BleakClient is indeed connected, then we add the on_disconnect and notification_handler callbacks. Note, we only added a callback on the read_characteristic . Makes sense, right?

Lastly, we enter an infinite loop which checks every 5 seconds if the BleakClient is still connected, if it isn't, then it breaks the loop, the function returns, and the entire method is called again.

    async def connect(self):
        if self.connected:
            return
        try:
            await self.client.connect()
            self.connected = await self.client.is_connected()
            if self.connected:
                print(f"Connected to {self.connected_device.name}")
                self.client.set_disconnected_callback(self.on_disconnect)
                await self.client.start_notify(
                    self.read_characteristic, self.notification_handler,
                )
                while True:
                    if not self.connected:
                        break
                    await asyncio.sleep(5.0, loop=loop)
            else:
                print(f"Failed to connect to {self.connected_device.name}")
        except Exception as e:
            print(e)

Whenever we decide to end the connection, we can escape the program by hitting CTRL+C , however, before shutting down the BleakClient needs to free up the hardware. The cleanup method checks if the Connection.client exists, if it does, it tells the remote device we no longer want notifications from the read_characteristic . It also sends a signal to our PC's hardware and the remote device we want to disconnect.

    async def cleanup(self):
        if self.client:
            await self.client.stop_notify(read_characteristic)
            await self.client.disconnect()

Device Selection

Bleak is a multi-OS package, however, there are slight differences between the different operating-systems. One of those is the address of your remote device. Windows and Linux report the remote device by it's MAC . Of course, Mac has to be the odd duck, it uses a Universally Unique Identifier ( UUID ). Specially, it uses a CoreBluetooth UUID, or a CBUUID .

These identifiers are important as bleak uses them during its connection process. These IDs are static, that is, they shouldn't change between sessions, yet they should be unique to the hardware.

The select_device method calls the bleak.discover method, which returns a list of BleakDevices advertising their connections within range. The code uses the aioconsole package to asynchronously request the user to select a particular device

    async def select_device(self):
        print("Bluetooh LE hardware warming up...")
        await asyncio.sleep(2.0, loop=loop) # Wait for BLE to initialize.
        devices = await discover()

        print("Please select device: ")
        for i, device in enumerate(devices):
            print(f"{i}: {device.name}")

        response = -1
        while True:
            response = await ainput("Select device: ")
            try:
                response = int(response.strip())
            except:
                print("Please make valid selection.")

            if response > -1 and response < len(devices):
                break
            else:
                print("Please make valid selection.")

After the user has selected a device then the Connection.connected_device is recorded (in case we needed it later) and the Connection.client is set to a newly created BleakClient with the address of the user selected device.

        print(f"Connecting to {devices[response].name}")
        self.connected_device = devices[response]
        self.client = BleakClient(devices[response].address, loop=self.loop)

Utility Methods

Not much to see here, these methods are used to handle timestamps on incoming Bluetooth LE data and clearing the rx buffer.

    def record_time_info(self):
        present_time = datetime.now()
        self.rx_timestamps.append(present_time)
        self.rx_delays.append((present_time - self.last_packet_time).microseconds)
        self.last_packet_time = present_time

    def clear_lists(self):
        self.rx_data.clear()
        self.rx_delays.clear()
        self.rx_timestamps.clear()

Save Incoming Data to File

This is a small class meant to make it easier to record the incoming microphone data along with the time it was received and delay since the last bytes were received.

class DataToFile:

    column_names = ["time", "delay", "data_value"]

    def __init__(self, write_path):
        self.path = write_path

    def write_to_csv(self, times: [int], delays: [datetime], data_values: [Any]):

        if len(set([len(times), len(delays), len(data_values)])) > 1:
            raise Exception("Not all data lists are the same length.")

        with open(self.path, "a+") as f:
            if os.stat(self.path).st_size == 0:
                print("Created file.")
                f.write(",".join([str(name) for name in self.column_names]) + ",\n")
            else:
                for i in range(len(data_values)):
                    f.write(f"{times[i]},{delays[i]},{data_values[i]},\n")

App Loops

I mentioned three "async loops," we've covered the first one inside the Connection class, but outside are the other two.

The user_console_manager() checks to see if the Connection instance has a instantiated a BleakClient and it is connected to a device. If so, it prompts the user for input in a non-blocking manner. After the user enters input and hits return the string is converted into a bytearray using the map() . Lastly, it is sent by directly accessing the Connection.client 's write_characteristic method. Note, that's a bit of a code smell, it should be refactored (when I have time).

async def user_console_manager(connection: Connection):
    while True:
        if connection.client and connection.connected:
            input_str = await ainput("Enter string: ")
            bytes_to_send = bytearray(map(ord, input_str))
            await connection.client.write_gatt_char(write_characteristic, bytes_to_send)
            print(f"Sent: {input_str}")
        else:
            await asyncio.sleep(2.0, loop=loop)

The last loop is the one designed to take the application code. Right now, it only simulates application logic by sleeping 5 seconds.

async def main():
    while True:
        # YOUR APP CODE WOULD GO HERE.
        await asyncio.sleep(5)

Closing

Well, that's it. You will have problems, especially if you are using the above code from Linux or Windows. But, if you run into any issues I'll do my best to provide support. Just leave me a comment below.

This article will show you how to program the Arduino Nano BLE 33 devices to use Bluetooth LE.

Introduction

Bluetooth Low Energy and I go way back. I was one of the first using the HM-10 module back in the day. Recently, my mentor introduced me to the Arduino Nano 33 BLE Sense . Great little board-- packed with sensors!

Shortly after firing it up, I got excited. I've been wanting to start creating my own smartwatch for a long time (as long the Apple watch has sucked really). And it looks like I wasn't the only one:

• The B&ND

This one board had many of the sensors I wanted, all in one little package. The board is a researcher's nocturnal emission.

Of course, my excitement was tamed when I realized there weren't tutorials on how to use the Bluetooth LE portion. So, after a bit of hacking I figured I'd share what I've learned.

Blue on Everything

This article will be part of a series. Here, we will be building a Bluetooth LE peripheral from the Nano 33, but it's hard to debug without having a central device to find and connect to the peripheral.

The next article in this series will show how to use use Python to connect to Bluetooth LE peripherals (above gif). This should allow one to connect to the Nano 33 from a PC. In short, stick with me. I've more Bluetooth LE content coming.

How to Install the Arduino Nano 33 BLE Board

After getting your Arduino Nano 33 BLE board there's a little setup to do. First, open up the Arduino IDE and navigate to the "Boards Manager."

/>

Search for Nano 33 BLE and install the board Arduino nRF528xBoards (MBed OS) .

/>

Your Arduino should be ready work with the Nano 33 boards, except BLE. For that, we need another library.

How to Install the ArduinoBLE Library

There are are a few different Arduino libraries for Bluetooth LE--usually, respective to the hardware. Unfortunate, as this means we would need a different library to work with the Bluetooth LE on a ESP32, for example. Oh well. Back to the problem at hand.

The official library for working with the Arduino boards equipped with BLE is:

• ArduinoBLE

It works pretty well, though, the documentation is a bit spotty.

To get started you'll need to fire up the Arduino IDE and go to Tools then Manager Libraries...

/>

In the search box that comes up type ArduinoBLE and then select Install next to the library:

/>

That's pretty much it, we can now include the library at the top of our sketch:

#include <ArduinoBLE.h>

And access the full API in our code.

Project Description

If you are eager, feel free to skip this information and jump to the code.

• Jump to Code

Before moving on, if the following terms are confusing:

• Peripheral
• Central
• Master
• Slave
• Server
• Client

You might check out EmbeddedFM's explanation:

• BLE Terms

I'll be focusing on getting the Arduino 33 BLE Sense to act as a peripheral BLE device. As a peripheral, it'll advertise itself as having services, one for reading, the other for writing.

UART versus Bluetooth LE

Usually when I'm working with a Bluetooth LE (BLE) device I want it to send and receive data. And that'll be the focus of this article.

I've seen this send-n-receive'ing data from BLE referred to as "UART emulation." I think that's fair, UART is a classic communication protocol for a reason. I've like the comparison as a mental framework for our BLE code.

We will have a rx property to get data from a remote device and a tx property where we can send data. Throughout the Arduino program you'll see my naming scheme using this analog. That stated, there are clear differences between BLE communication and UART. BLE is arguably more complex and versatile.

Data from the Arduino Microphone

To demonstrate sending and receiving data we need to data to send. We are going to grab information from the microphone on the Arduino Sense and send it to remote connected device. I'll not cover the microphone code here, as I don't understand it well enough to explain. However, here's a couple reads:

• Pulse-Density Modulation (data type output)
• Arduino Pulse-Density Modulation Library docs

Code

Time to code. Below is what I hacked together, with annotations from the "gotchas" I ran into.

One last caveat, I used Jithin 's code as a base of my project:

• Arduino BLE Example Explained Step by Step

Although, I'm not sure any of the original code is left. Cite your sources.

And if you'd rather look at the full code, it can be found at:

• Full Code

Initialization

We load in the BLE and the PDM libraries to access the APIs to work with the microphone and the radio hardware.

#include <ArduinoBLE.h>
#include <PDM.h>

Service and Characteristics

Let's create the service. First, we create the name displayed in the advertizing packet, making it easy for a user to identify our Arduino.

We also create a Service called microphoneService , passing it the full Universally Unique ID (UUID) as a string. When setting the UUID there are two options. A 16-bit or a 128-bit version. If you use one of the standard Bluetooth LE Services the 16-bit version is good. However, if you are looking to create a custom service, you will need to explore creating a full 128-bit UUID.

Here, I'm using the full UUIDs but with a standard service and characteristic, as it makes it easier to connect other hardware to our prototype, as the full UUID is known.

If you want to understand UUID's more fully, I highly recommend Nordic's article:

• Bluetooth low energy Services, a beginner's tutorial

Anyway, we are going to use the following UUIDs: * Microphone Service = 0x1800 -- Generic Access * rx Characteristic = 0x2A3D -- String * tx Characteristic = 0x2A58 -- Analog

You may notice reading the Bluetooth specifications, there are two mandatory characteristics we should be implementing for Generic Access :

• Device Name
• Appearance

For simplicity, I'll leave these up to the reader. But they must be implemented for a proper Generic Access service.

Right, back to the code.

Here we define the name of the device as it should show to remote devices. Then, the service and two characteristics, one for sending, the other, receiving.

// Device name
const char* nameOfPeripheral = "Microphone";
const char* uuidOfService = "0000181a-0000-1000-8000-00805f9b34fb";
const char* uuidOfRxChar = "00002A3D-0000-1000-8000-00805f9b34fb";
const char* uuidOfTxChar = "00002A58-0000-1000-8000-00805f9b34fb";

Now, we actually instantiate the BLEService object called microphoneService .

// BLE Service
BLEService microphoneService(uuidOfService);

The characteristic responsible for receiving data, rxCharacteristic , has a couple of parameters which tell the Nano 33 how the characteristic should act.

// Setup the incoming data characteristic (RX).
const int RX_BUFFER_SIZE = 256;
bool RX_BUFFER_FIXED_LENGTH = false;

RX_BUFFER_SIZE will be how much space is reserved for the rx buffer. And RX_BUFFER_FIXED_LENGTH will be, well, honestly, I'm not sure. Let me take a second and try to explain my ignorance.

When looking for the correct way to use the ArduinoBLE library, I referred to the documentation:

• BLECharacteristic()

There are several different ways to initialize a characteristic, as a single value (e.g., BLEByteCharacteristic , BLEFloatCharacteristic , etc.) or as buffer. I decided on the buffer for the rxCharacteristic . And that's where it got problematic.

Here's what the documentation states regarding initializing a BLECharacteristic with a buffer.

BLECharacteristic(uuid, properties, value, valueSize)
BLECharacteristic(uuid, properties, stringValue)
...
uuid: 16-bit or 128-bit UUID in string format
properties: mask of the properties (BLEBroadcast, BLERead, etc)
valueSize: (maximum) size of characteristic value
stringValue: value as a string

Cool, makes sense. Unfortunately, I never got a BLECharacteristic to work initializing it with those arguments. I finally dug into the actual BLECharacteristic source and discovered their are two ways to initialize a BLECharacteristic :

BLECharacteristic(new BLELocalCharacteristic(uuid, properties, valueSize, fixedLength))
BLECharacteristic(new BLELocalCharacteristic(uuid, properties, value))

I hate misinformation. Ok, that tale aside, back to our code.

Let's actually declare the rx and tx characteristics. Notice, we are using a buffered characteristic for our rx and a single byte value characteristic for our tx . This may not be optimal, but it's what worked.

// RX / TX Characteristics
BLECharacteristic rxChar(uuidOfRxChar, BLEWriteWithoutResponse | BLEWrite, RX_BUFFER_SIZE, RX_BUFFER_FIXED_LENGTH);
BLEByteCharacteristic txChar(uuidOfTxChar, BLERead | BLENotify | BLEBroadcast);

The second argument is where you define how the characteristic should behave. Each property should be separated by the | as they are constants which are being OR ed together into a single value (masking).

Here is a list of available properties:

• BLEBroadcast -- will cause the characteristic to be advertized
• BLERead -- allows remote devices to read the characteristic value
• BLEWriteWithoutResponse -- allows remote devices to write to the device without expecting an acknowledgement
• BLEWrite -- allows remote devices to write, while expecting an acknowledgement the write was successful
• BLENotify -- allows a remote device to be notified anytime the characteristic's value is update
• BLEIndicate -- the same as BLENotify, but we expect a response from the remote device indicating it read the value

Microphone

There are two global variables which keep track of the microphone data. The first is a small buffer called sampleBuffer , it will hold up to 256 values from the mic.

The volatile int samplesRead is the variable which will hold the immediate value from the mic sensor. It is used in the interrupt routine vector (ISR) function. The volatile keyword tells the Arduino's C++ compiler the value in the variable may change at any time and it should check the value when referenced, rather than relying on a cached value in the processor ( more on volatiles ).

// Buffer to read samples into, each sample is 16-bits
short sampleBuffer[256];

// Number of samples read
volatile int samplesRead;

Setup()

We initialize the Serial port, used for debugging.

void setup() {

  // Start serial.
  Serial.begin(9600);

  // Ensure serial port is ready.
  while (!Serial);

To see when the BLE actually is connected, we set the pins connected to the built-in RGB LEDs as OUTPUT .

  // Prepare LED pins.
  pinMode(LED_BUILTIN, OUTPUT);
  pinMode(LEDR, OUTPUT);
  pinMode(LEDG, OUTPUT);

Note, there is a bug in the source code where the LEDR and the LEDG are backwards . You can fix this by searching your computer for ARDUINO_NANO33BLE folder and editing the file pins_arduino.h inside.

Change the following:

#define LEDR        (22u)
#define LEDG        (23u)
#define LEDB        (24u)

To

#define LEDR        (23u)
#define LEDG        (22u)
#define LEDB        (24u)

And save. That should fix the mappings.

The onPDMdata() is an ISR which fires every time the microphone gets new data. And startPDM() starts the microphone integrated circuit.

  // Configure the data receive callback
  PDM.onReceive(onPDMdata);

  // Start PDM
  startPDM();

Now Bluetooth LE is setup, we ensure the Bluetooth LE hardware has been powered-on within the Nano 33. We set the device name and begin advertizing the service. Then, add the rx and tx characteristics to the microphoneService . Lastly, add the microphoneService to the BLE object.

  // Start BLE.
  startBLE();

  // Create BLE service and characteristics.
  BLE.setLocalName(nameOfPeripheral);
  BLE.setAdvertisedService(microphoneService);
  microphoneService.addCharacteristic(rxChar);
  microphoneService.addCharacteristic(txChar);
  BLE.addService(microphoneService);

Now the Bluetooth LE hardware is turned on, we add callbacks which will fire when the device connects or disconnects. Those callbacks are great places to add notifications, setup, and teardown.

We also add a callback which will fire every time the Bluetooth LE hardware has a characteristic written. This allows us to handle data as it streams in.

  // Bluetooth LE connection handlers.
  BLE.setEventHandler(BLEConnected, onBLEConnected);
  BLE.setEventHandler(BLEDisconnected, onBLEDisconnected);

  // Event driven reads.
  rxChar.setEventHandler(BLEWritten, onRxCharValueUpdate);

Lastly, we command the Bluetooth LE hardware to begin advertizing its services and characteristics to the world. Well, at least +/-30ft of the world.

  // Let's tell devices about us.
  BLE.advertise();

Before beginning the main loop, I like spiting out all of the hardware information we setup. This makes it easy to add it into whatever other applications we are developing., which will connect to the newly initialized peripheral.

  // Print out full UUID and MAC address.
  Serial.println("Peripheral advertising info: ");
  Serial.print("Name: ");
  Serial.println(nameOfPeripheral);
  Serial.print("MAC: ");
  Serial.println(BLE.address());
  Serial.print("Service UUID: ");
  Serial.println(microphoneService.uuid());
  Serial.print("rxCharacteristic UUID: ");
  Serial.println(uuidOfRxChar);
  Serial.print("txCharacteristics UUID: ");
  Serial.println(uuidOfTxChar);


  Serial.println("Bluetooth device active, waiting for connections...");
}

Loop()

The main loop grabs a reference to the central property from the BLE object. It checks if central exists and then it checks if central is connected. If it is, it calls the connectedLight() which will cause the green LED to come on, letting us know the hardware has made a connection.

Then, it checks if there are data in the sampleBuffer array, if so, it writes them to the txChar . After it has written all data, it resets the samplesRead variable to 0 .

Lastly, if the device is not connected or not initialized, the loop turns on the disconnected light by calling disconnectedLight() .

void loop()
{
  BLEDevice central = BLE.central();

  if (central)
  {
    // Only send data if we are connected to a central device.
    while (central.connected()) {
      connectedLight();

      // Send the microphone values to the central device.
      if (samplesRead) {
        // print samples to the serial monitor or plotter
        for (int i = 0; i < samplesRead; i++) {
          txChar.writeValue(sampleBuffer[i]);      
        }
        // Clear the read count
        samplesRead = 0;
      }
    }
    disconnectedLight();
  } else {
    disconnectedLight();
  }
}

Some may have noticed there is probably an issue with how I'm pulling the data from the sampleBuffer , as I've just noticed it myself writing this article, it may have a condition where the microphone's ISR is called in the middle of writing the buffer to the txChar . If I've need to fix this, I'll update this article.

Ok, hard part's over, let's move on to the helper methods.

Helper Methods

startBLE()

The startBLE() function initializes the Bluetooth LE hardware by calling the begin() . If it is unable to start the hardware, it will state so via the serial port, and then stick forever.

/*
 *  BLUETOOTH
 */
void startBLE() {
  if (!BLE.begin())
  {
    Serial.println("starting BLE failed!");
    while (1);
  }
}

onRxCharValueUpdate()

This method is called when new data is received from a connected device. It grabs the data from the rxChar by calling readValue and providing a buffer for the data and how many bytes are available in the buffer. The readValue method returns how many bytes were read. We then loop over each of the bytes in our tmp buffer, cast them to char , and print them to the serial terminal. This is pretty helpful when debugging.

Before ending, we also print out how many bytes were read, just in case we've received data which can't be converted to ASCII . Again, helpful for debugging.

void onRxCharValueUpdate(BLEDevice central, BLECharacteristic characteristic) {
  // central wrote new value to characteristic, update LED
  Serial.print("Characteristic event, read: ");
  byte tmp[256];
  int dataLength = rxChar.readValue(tmp, 256);

  for(int i = 0; i < dataLength; i++) {
    Serial.print((char)tmp[i]);
  }
  Serial.println();
  Serial.print("Value length = ");
  Serial.println(rxChar.valueLength());
}

LED Indicators

Not much to see here. These functions are called when our device connects or disconnects, respectively.

void onBLEConnected(BLEDevice central) {
  Serial.print("Connected event, central: ");
  Serial.println(central.address());
  connectedLight();
}

void onBLEDisconnected(BLEDevice central) {
  Serial.print("Disconnected event, central: ");
  Serial.println(central.address());
  disconnectedLight();
}

/*
 * LEDS
 */
void connectedLight() {
  digitalWrite(LEDR, LOW);
  digitalWrite(LEDG, HIGH);
}


void disconnectedLight() {
  digitalWrite(LEDR, HIGH);
  digitalWrite(LEDG, LOW);
}

Microphone

I stole this code from Arduino provided example. I think it initializes the PDM hardware (microphone) with a 16khz sample rate.

/*
 *  MICROPHONE
 */
void startPDM() {
  // initialize PDM with:
  // - one channel (mono mode)
  // - a 16 kHz sample rate
  if (!PDM.begin(1, 16000)) {
    Serial.println("Failed to start PDM!");
    while (1);
  }
}

Lastly, the onPDMData callback is fired whenever their are data available to be read. It checks how many bytes their are available by calling available() and reads that number of bytes into the buffer. Lastly, given the data are int16 , it divides the number of bytes by 2 as this is the number of samples read.

void onPDMdata() {
  // query the number of bytes available
  int bytesAvailable = PDM.available();

  // read into the sample buffer
  int bytesRead = PDM.read(sampleBuffer, bytesAvailable);

  // 16-bit, 2 bytes per sample
  samplesRead = bytesRead / 2;
}

Final Thoughts

Bluetooth LE is powerful--but tough to get right. To be clear, not saying I've gotten it right here, but I'm hoping I'm closer. If you find any issues please leave me a comment or send me an email and I'll get them corrected as quick as I'm able.

This article is part of a series documenting an attempt to create a LEGO sorting machine. This portion covers the Arduino Mega2560 firmware I've written to control a RAMPS 1.4 stepper motor board.

A big thanks to William Cooke, his wisdom was key to this project. Thank you, sir!

• William Cooke

Goal

To move forward with the LEGO sorting machine I needed a way to drive a conveyor belt. Stepper motors were a fairly obvious choice. They provide plenty of torque and finite control. This was great, as several other parts of the LEGO classifier system would need steppers motors as well-e.g.,turn table and dispensing hopper. Of course, one of the overall goals of this project is to keep the tools accessible. After some research I decided to meet both goals by purchasing an Ardunio / RAMPs combo package intended for 3D printers.

/>

• Amazon RAMPs Kits

At the time of the build, these kits were around $28-35 and included: * Arduino Mega2560 * 4 x Endstops * 5 x Stepers Drivers (A4988) * RAMPSs 1.4 board * Display * Cables & wires

Seemed like a good deal. I bought a couple of them.

I would eventually need: * 3 x NEMA17 stepper motors * 12v, 10A Power Supply Unit (PSU)

Luckily, I had the PSU and a few stepper motors lying about the house.

Physical Adjustments

Wiring everything up wasn't too bad. You follow about any RAMPs wiring diagram. However, I did need to make two adjustments before starting on the firmware.

First, underneath each of the stepper drivers there are three drivers for setting the microsteps of the respective driver. Having all three jumpers enables maximum microsteps, but would cause the speed of the motor to be limited by the clock cycles of the Arduino--more on that soon.

/>

I've also increased the amperage to the stepper. This allowed me to drive the entire belt from one NEMA17.

To set the amperage, get a small phillips screwdriver, two alligator clips, and a multimeter. Power on your RAMPs board and carefully attach the negative probe to the RAMPs GND . Attach the positive probe to an alligator clip and attach the other end to the shaft of your screwdriver. Use the screwdriver to turn the tiny potentiometer on the stepper driver. Watch the voltage on the multimeter--we want to use the lowest amperage which effectively drives the conveyor belt. We are watching the voltage, as it is related to the amperage we are feeding the motors.

current_limit = Vref x 2.5

Anyway, I found the lowest point for my motor, without skipping steps, was around ~ 0.801v .

current_limit = 0.801 x 2.5
current_limit = 2.0025

The your current_limit will vary depending on the drag of your conveyor belt and the quality of your stepper motor. To ensure a long-life of your motor, do not set the amperage higher than needed to do the job.

/>

Arduino Code

When I bought the RAMPs board I started thinking, "I should see if we could re-purpose Marlin to drive the conveyor belt easily." I took one look at the source and said, "Oh hell no." Learning how to hack Marlin to drive a conveyor belt seemed like learning heart surgery to hack your heart into a gas pump. So, I decided roll my own RAMPs firmware.

My design goals were simple: * Motors operate independently * Controlled with small packets via UART * Include four commands: motor select, direction, speed, duration

That's it. I prefer to keep stuff as simple as possible, unless absolutely necessary.

I should point out, this project builds on a previous attempt at firmware:

• Raspbery Pi, Arduino, RAMPS Turntable

But that code was flawed. It was not written with concurrent and independent motor operation in mind. The result, only one motor could be controlled at a time.

Ok, on to the new code.

Main

The firmware follows this procedure:

1. Check if a new movement packet has been received.
2. Decode the packet
3. Load direction, steps, and delay (speed) into the appropriate motor struct.
4. Check if a motor has steps to take and the timing window for the next step is open.
5. If a motor has steps waiting to be taken, move the motor one step and decrement the respective motor's step counter.
6. Repeat forever.

/* Main */
void loop()
{
  if (rxBuffer.packet_complete) {
    // If packet is packet_complete
    handleCompletePacket(rxBuffer);
    // Clear the buffer for the next packet.
    resetBuffer(&rxBuffer);
  }

  // Start the motor
  pollMotor();
}

serialEvent

Some code not in the main loop is the the UART RX handler. It is activated by an RX interrupt. If the interrupt fires, the new data is quickly loaded into the rxBuffer . If the incoming data contains a 0x03 character, this signals the packet is complete and ready to be decoded.

Here's the packet template:

MOTOR_PACKET = CMD_TYPE MOTOR_NUM DIR STEPS_1 STEPS_2 MILLI_BETWEEN 0x03

Each motor movement packet consists of seven bytes and five values: 1. CMD_TYPE = drive or halt 2. MOTOR_NUM = the motor selected X, Y, Z, E0, E1 3. DIR = direction of the motor 4. STEPS_1 = the high 6-bits of of steps to take 5. STEPS_2 = the low 6-bits of steps to take 6. MILLI_BETWEEN = number of milliseconds between each step (speed control) 7. 0x03 = this signals the end of the packet ( ETX )

Each of these bytes are encoded by left-shifting the bits by two. This means each of the bytes in the packet can only represent 64 values ( 2^6 = 64 ).

Why add this complication? Well, we want to be able to send commands to control the firmware, rather than the motors. The most critical is knowing when the end of a packet is reached. I'm using the ETX char, 0x03 to signal the end of a packet. If we didn't reserve the 0x03 byte then what happens if we send command to the firmware to move the motor 3 steps? Nothing good.

Here's the flow of a processed command:

1. CMD_TYPE       = DRIVE (0x01)
2. MOTOR_NUM      = X     (0x01)
3. DIR            = CW    (0x01)
4. STEPS          = 4095  (0x0FFF)
5. MILLI_BETWEEN  = 5ms   (0x05)
6. ETX            = End   (0x03)

Note, the maximum value of the STEPS byte is greater than 8-bits. To handle this, we break it into two bytes of 6-bits.

1. CMD_TYPE       = DRIVE (0x01)
2. MOTOR_NUM      = X     (0x01)
3. DIR            = CW    (0x01)
4. STEPS_1        = 3F
5. STEPS_2        = 3F
5. MILLI_BETWEEN  = 5     (0x05)
6. ETX            = End   (0x03)

Here's a sample motor packet before encoding:

uint8_t packet[7] = {0x01, 0x01, 0x01, 0x3F, 0x3F, 0x05, 0x03}

Now, we have to shift all of the bytes left by two bits, this will ensure 0x00 through 0x03 are reserved for meta-communication.

This process is a bit easier to see in binary:

Before shift:

1. CMD_TYPE       = 0000 0001
2. MOTOR_NUM      = 0000 0001
3. DIR            = 0000 0001
4. STEPS_1        = 0011 1111
5. STEPS_2        = 0011 1111
5. MILLI_BETWEEN  = 0000 0101
6. ETX            = 0000 0011

After shift:

1. CMD_TYPE       = 0000 0100
2. MOTOR_NUM      = 0000 0100
3. DIR            = 0000 0100
4. STEPS_1        = 1111 1100
5. STEPS_2        = 1111 1100
5. MILLI_BETWEEN  = 0001 0100
6. ETX            = 0000 0011

And back to hex:

1. CMD_TYPE       = 0x04
2. MOTOR_NUM      = 0x04
3. DIR            = 0x04
4. STEPS_1        = 0xFC
5. STEPS_2        = 0xFC
5. MILLI_BETWEEN  = 0x14
6. ETX            = 0x03

And after encoding:

uint8_t packet[7] = {0x04, 0x04,  0x04, 0xFC, 0xFC, 0x14, 0x03}

Notice the last byte is not encoded, as this is a reserved command character.

Here are the decode and encode functions. Fairly straightforward bitwise operations.

uint8_t decode(uint8_t value) {
  return (value >> 2) & 0x3F;
}

uint8_t encode(uint8_t value) {
  return (value << 2) & 0xFC;
}

And the serial handling as a whole:

void serialEvent() {

  // Get all the data.
  while (Serial.available()) {

    // Read a byte
    uint8_t inByte = (uint8_t)Serial.read();

    if (inByte == END_TX) {
      rxBuffer.packet_complete = true;
    } else {
      // Store the byte in the buffer.
      inByte = decodePacket(inByte);
      rxBuffer.data[rxBuffer.index] = inByte;
      rxBuffer.index++;
    }
  }
}

handleCompletePacket

When a packet is waiting to be decoded, the handleCompletePacket() will be executed. The first thing the method does is check the packet_type . Keeping it simple, there are only two and one is not implemented yet ( HALT_CMD )

#define DRIVE_CMD       (char)0x01
#define HALT_CMD        (char)0x02

Code is simple. It unloads the data from the packet. Each byte in the incoming packet represents different portions of the the motor move command. Each byte's value is loaded into local a variable.

The only note worth item is the steps bytes, as the steps consistent of a 12-bit value, which is contained in the 6 lower bits of two bytes. The the upper 6-bits are left-shifted by 6 and we OR them with lower 6-bits.

uint16_t steps = ((uint8_t)rxBuffer.data[3] << 6)  | (uint8_t)rxBuffer.data[4];

If the packet actually contains steps to move we call the setMotorState() , passing all of the freshly unpacked values as arguments. This function will store those values until the processor has time to process the move command.

Lastly, the handleCompletePacket() sends an acknowledgment byte ( 0x02 ).

void handleCompletePacket(BUFFER rxBuffer) {

    uint8_t packet_type = rxBuffer.data[0];

    switch (packet_type) {
      case DRIVE_CMD:

          // Unpack the command.
          uint8_t motorNumber =  rxBuffer.data[1];
          uint8_t direction =  rxBuffer.data[2];
          uint16_t steps = ((uint8_t)rxBuffer.data[3] << 6)  | (uint8_t)rxBuffer.data[4];
          uint16_t microSecondsDelay = rxBuffer.data[5] * 1000; // Delay comes in as milliseconds.

          if (microSecondsDelay < MINIMUM_STEPPER_DELAY) { microSecondsDelay = MINIMUM_STEPPER_DELAY; }

          // Should we move this motor.
          if (steps > 0) {
            // Set motor state.
            setMotorState(motorNumber, direction, steps, microSecondsDelay);
          }

          // Let the master know command is in process.
          sendAck();
        break;
      default:
        sendNack();
        break;
    }
}

setMotorState

Each motor has a struct MOTOR_STATE representing its current state.

struct MOTOR_STATE {
  uint8_t direction;
  uint16_t steps;
  unsigned long step_delay;
  unsigned long next_step_at;
  bool enabled;
};

There are five motor MOTOR_STATE s which are initialized a program start, one for each motor (X, Y, Z, E0, E1).

MOTOR_STATE motor_n_state = { DIR_CC, 0, 0, SENTINEL, false };

And whenever a valid move packet is processed, as we saw above, the setMotorState() is responsible for updating the respective MOTOR_STATE struct.

Everything in this function is intuitive, but the critical part for understanding how the entire program comes together to ensure the motors are able to move around at different speeds, directions, all simultaneously is:

motorState->next_step_at = micros() + microSecondsDelay;

micros() is built into the Arduino ecosystem. It returns the number of microseconds since hte program started.

• micros()

The next_step_at is set for when we want the this specific motor to take its next step. We get this number as the number of seconds from the programs start up, plus the delay we want between each step. This may be a bit hard to understand, however, like stated, it's key to the entire program working well. Later, we will update motorState->next_step_at with when this motor should take its next step. This "time to take the next step" threshold allows us to avoid creating a blocking loop on each motor.

For example, the wrong way may look like:

void main_loop() {

  // motor_x
  for(int i = 0; i < motor_x_steps; i++) {
    digitalWrite(motor.step_pin, HIGH);
    delayMicroseconds(motor.pulse_width_micros);
    digitalWrite(motor.step_pin, LOW);
  }

  // motor_y
  for(int i = 0; i < motor_y_steps; i++) {
    digitalWrite(motor.step_pin, HIGH);
    delayMicroseconds(motor.pulse_width_micros);
    digitalWrite(motor.step_pin, LOW);
  }

  // Etc
}

As you might have noticed, the motor_y would not start moving until motor_x took all of its steps. That's no good.

Anyway, keep this in mind as we start looking at the motor movement function--coming up next.

void setMotorState(uint8_t motorNumber, uint8_t direction, uint16_t steps, unsigned long microSecondsDelay) {

    // Get reference to motor state.
    MOTOR_STATE* motorState = getMotorState(motorNumber);

    ...

    // Update with target states.
    motorState->direction = direction;
    motorState->steps = steps;
    motorState->step_delay = microSecondsDelay;
    motorState->next_step_at = micros() + microSecondsDelay;
}

pollMotor

Getting to the action. Inside the main loop there is a call to pollMotor() , which loops all of the motors, checking if the motorState has steps to take. If it does, it takes one step and sets when it should take its next step:

motorState->next_step_at += motorState->step_delay;

This is key to all motors running together. By setting when each motor should take its next step, it frees microcontroller to do other work. And the microcontroller is quick, it can do its other work fast and come back and check if each motor needs to take its next step several hundred times before any motor needs to move again. Of course, it all depends on how fast you want your motors to go. For this project, it works like a charm.

/* Write to MOTOR */
void pollMotor() {
    unsigned long current_micros = micros();
    // Loop over all motors.
    for (int i = 0; i < int(sizeof(all_motors)/sizeof(int)); i++)
    {
      // Get motor and motorState for this motor.
      MOTOR motor = getMotor(all_motors[i]);
      MOTOR_STATE* motorState = getMotorState(all_motors[i]);

      // Check if motor needs to move.
      if (motorState->steps > 0) {

        // Initial step timer.
        if (motorState->next_step_at == SENTINEL) {
          motorState->next_step_at = micros() + motorState->step_delay;
        }

        // Enable motor.
        if (motorState->enabled == false) {
          enableMotor(motor, motorState);
        }

        // Set motor direction.
        setDirection(motor, motorState->direction);

        unsigned long window = motorState->step_delay;  // we should be within this time frame

        if(current_micros - motorState->next_step_at < window) {         
            writeMotor(motor);
            motorState->steps -= 1;
            motorState->next_step_at += motorState->step_delay;
        }
      }

      // If steps are finished, disable motor and reset state.
      if (motorState->steps == 0 && motorState->enabled == true ) {
        disableMotor(motor, motorState);
        resetMotorState(motorState);
      }
    }
}

Summary

We have the motor driver working. We now can control five stepper motors' speed and number steps, all independent of one another. And the serial communication protocol allows us to send small packets to each specific motor, telling how many steps to take and how quickly.

Next, we need a controller on the other side of the UART--a master device. This master device will coordinate higher level functions with the motor movements. I've already started work on this project, it will be a asynchronous Python package. Wish me luck.

I've been working on an automated system for sorting LEGOs. It seems like a simple enough task, however, the nuances of implementation are ugly. I have prototypical solutions for a few of these challenges, such as identifying the LEGO and creating training data for supporting the classifier. But one of the trickier problems has vexed me: How do we get the LEGO from a container to the classifier?

The answer is obvious, right? A conveyor belt. They are ubiquitous in manufacturing, so I thought, "Simple. I'll toss a conveyor belt together real quick and that'll solve that." Hah.

After a month and a half of failed attempts, I've eventually created a working prototype.

/>

The system consists of 5 parts:

1. Raspberry Pi
2. Arduino Mega2560
3. RAMPs 1.4 with A4988s
4. Conveyor belt
5. NEMA17 Stepper Motor and Mount

Covering all parts will be too much for one article, so in this article I'll focus on the setting up the environment and in a subsequent article I'll review the firmware, software, and physical build.

Remote VSCode (sshfs)

I hate trying to program on computers other than my workstation; I've also found it problematic to write a program for a Raspberry Pi on a PC. To get the best of both worlds I use sshfs . It lets me mount Raspberry Pi folders as local folder, enabling editing Raspberry Pi files from my workstation. Nice!

/>

The setup is pretty simple, depending on your workstation's OS.

Luckily, DigitalOcean has already put together a multi-OS walkthrough of setting up sshfs

• How To Use SSHFS to Mount Remote File Systems Over SSH

Once you have sshfs setup, you can create a directory and mount the entire Raspberry Pi.

For me, running Linux Mint, it was:

sshfs pi@192.168.1.x:/home/pi ~/rpi

A few notes on the above command: * The 192.168.1.x should be replaced with the ip of your Raspberry Pi * ~/rpi is the local directory where you are going to mount the Raspberry Pi.

If all goes well, you should be able to open your Raspberry Pi files in Visual Studio Code (or IDE of choice) by navigating to the ~/rpi directory.

To run files, you still have to ssh into the Pi. I usually do this by creating an integrated terminal in Visual Studio Code.

/>

Arduino CLI Setup

Now I had a way to edit Raspberry Pi files on my PC, but I still needed to be able to connect my Arduino to the Pi and program it from my workstation. The route people seem to use for remote programming is using a VNC program, like RealVNC, to access the Pi's desktop remotely. Gross. Give my command line back.

Enter Arduino's command line interface (CLI) .

Now I had all the needed pieces to make for comfortable coding: * Code the Pi from my workstation using VSCode * Any software written would be native to the Pi's ARM core * I could upload new firmware from the Raspberry Pi to the Arduino; enabling quick iterations

I was pretty excited. I just need to put the pieces together.

Python Convenience Scripts

First, I had to get the Arduino CLI running on the Raspberry Pi. That turned out pretty painless. In fact, I turned the installation into a Python script for you.

Script for Installing Arduino CLI

You can download my entire setup using git . From your Raspberry Pi's command line run:

git clone https://github.com/ladvien/ramps_controller.git
cd ramps_controller
python3 arduino-cli_setup.py

Or if you prefer to add it to your own Python script:

# 
import os, sys

# Install arduino-cli
os.system('curl -fsSL https://raw.githubusercontent.com/arduino/arduino-cli/master/install.sh | BINDIR=/bin sh')

# Configure arduino-cli
os.system('arduino-cli config init')

# Update the core.
os.system('arduino-cli core update-index')

# Add Arduino AVR and Mega cores.
os.system('arduino-cli core install arduino:avr')
os.system('arduino-cli core install arduino:megaavr')

The installation script downloads the Arduino CLI and installs it. It then updates the Arduino core libraries. Lastly, it ensures the AVR and Arduino Mega AVR cores are installed as well.

Script for Uploading using Arduino CLI

You should now be set to compile and install new firmware directly from the Raspberry Pi to the Arduino Mega2560. I've also created a firmware installation script, which eases installing new code.

python3 install_sketch.py

At the root of the install script are an Arduino CLI command to compile and then upload:

# Compile
os.system('arduino-cli compile -b arduino:avr:mega ramps_sketch')

# Upload
command_str = f'arduino-cli -v upload -p {write_port} --fqbn arduino:avr:mega ramps_sketch'
os.system(command_str)

Feel free to hack the script for other projects. You can replace the arduino:avr:mega with other chipsets and program tons of different devices using the same method. And the ramps_sketch refers to the program you want to upload. It is a folder containing and .ino file of the same name, which is the program you want to upload to the Arduino

/>

Here's an action shot:

A couple of notes, if you have trouble running the install script here are two issues I ran into:

pyserial

The install script uses Python to lookup what USB-serial bridges you have attached to your Pi. This Python relies on the pyserial package. Make sure it is installed using:

pip install pyserial

Access to USB

For the install script to work correctly, the executing user must have access to the USB-serial devices. This is known as the dialout group. The right way of doing this is by adding the permission to the user.

sudo adduser $USER dialout

If this fails, you can use the "wrong" way and just execute the ./install.py script using sudo .

python3 install_sketch.py

Ok, that's it for now. I'll tackle the firmware next.

I've you have any trouble with the code, or have questions, just leave a comment below.