DEV Community: Vahid Fazel-Rezai

Using Tableau with DynamoDB: How to Build a Real-Time SQL Dashboard on NoSQL Data

Vahid Fazel-Rezai — Wed, 28 Aug 2019 07:00:00 +0000

In this blog, we examine DynamoDB reporting and analytics, which can be challenging given the lack of SQL and the difficulty running analytical queries in DynamoDB. We will demonstrate how you can build an interactive dashboard with Tableau, using SQL on data from DynamoDB, in a series of easy steps, with no ETL involved.

DynamoDB is a widely popular transactional primary data store. It is built to handle unstructured data models and massive scales. DynamoDB is often used for organization’s most critical business data, and as such there is value in being able to visualize and dig deeper into this data.

Tableau, also widely popular, is a tool for building live, interactive charts and dashboards. In this blog post, we will walk through an example of using Tableau to visualize data in DynamoDB.

DynamoDB works well out-of-the-box for simple lookups by the primary key. For lookups by a different attribute, DynamoDB allows creating a local or global secondary index. However, for even more complex access patterns like filtering on nested or multiple fields, sorting, and aggregations—types of queries that commonly power dashboards—DynamoDB alone is not sufficient. This blog post evaluates a few approaches to bridge this gap.

In this post, we will create an example business dashboard in Tableau on data in DynamoDB, using Rockset as the SQL intelligence layer in between, and JDBC to connect Tableau and Rockset.

The Data

For this example, I’ve combined sample data from Airbnb and mock data from Mockaroo to generate realistic records of users with listings, bookings, and reviews for a hypothetical home rental marketplace. (All names and emails are fake.) The mock data and scripts are available on Github.

The data model is typical for a DynamoDB use case—here’s an example item:

{
    "user_id": "28c38f9e-463d-4eae-b53d-16cdad48f150",
    "first_name": "Kimberlyn",
    "last_name": "Maudlin",
    "email": "kmaudlin24@bandcamp.com",
    "listings": [
        {
            "listing_id": "8472954",
            "title": "Private bedroom in adorable home",
            "description": "The spare bedroom in our adorable 2 bedroom home ... ",
            "city": "Bomomani",
            "country": "Indonesia",
            "listed_date": "2015-09-30",
            "cancellation_policy": "flexible",
            "price_usd": "51.00",
            "bathrooms": "2",
            "bedrooms": "2",
            "beds": "2",
            "bookings": [
                {
                    "user": {
                        "user_id": "530cd0c7-b79b-4f94-9e0f-969fc7f9855b",
                        "first_name": "Nahum",
                        "last_name": "Yaus",
                        "email": "nyaus9@angelfire.com"
                    },
                    "start_date": "2015-12-07",
                    "length_days": "5",
                    "review": {
                        "text": "Great convenient location, clean, and ... ",
                        "rating": "3"
                    },
                    "cost_usd": "230.84"
                }
            ]
        }
    ]
}

A few things to note:

In our data, sometimes the review field will be missing (if the user did not leave a review).
The bookings and listings arrays may be empty, or arbitrarily long!
The user field is denormalized and duplicated within a booking, but also exists separately as its own item.

We start with a DynamoDB table called rental_data loaded with 21,964 such records:

Connecting Tableau to DynamoDB

Let’s see this data into Tableau!

We’ll need accounts for Tableau Desktop and Rockset. I also assume we’ve already set up credentials to access our DynamoDB table.

First, we need to download the Rockset JDBC driver from Maven and place it in ~/Library/Tableau/Drivers for Mac or C:\Program Files\Tableau\Drivers for Windows.

Next, let’s create an API key in Rockset that Tableau will use for authenticating requests:

In Tableau, we connect to Rockset by choosing “Other Databases (JDBC)” and filling the fields, with our API key as the password:

Finally, back in Rockset, we just create a new collection directly from the DynamoDB table:

We see the new collection reflected as a table in Tableau:

Users Table

Our DynamoDB table has some fields of type Map and List, whereas Tableau expects a relational model where it can do joins on flat tables. To resolve this, we’ll compose SQL queries in the Rockset Console that reshapes the data as desired, and add these as custom SQL data sources in Tableau.

First, let’s just get a list of all the users on our rental platform:

In Tableau, we drag “New Custom SQL” to the top section, paste this query (without the LIMIT clause), and rename the result to Users:

Looks good! Now, let’s repeat this process to also pull out listings and bookings into their own tables.

Listings Table

Note that in the original table, each row (user) has an array of listing items. We want to pull out these arrays and concatenate them such that each item itself becomes a row. To do so, we can use the UNNEST function:

Now, let’s select the fields we want to have in our listings table:

And we paste this as custom SQL in Tableau to get our Listings table:

Bookings Table

Let’s create one more data source for our Bookings table with another UNNEST query:

Chart 1: Listings Overview

Let’s get a high level view of the listings around the world on our platform. With a few drag-and-drops, we use the city/country to place the listings on a map, sized by booking count and colored by cancellation policy.

Looks like we have a lot of listings in Europe, South America, and East Asia.

Chart 2: Listings Leaderboard

Let’s try to find out more about the listings pulling in the most revenue. We’ll build a leaderboard with the following information:

labeled by listing ID and email of host
total revenue as the sum of cost across all bookings (sorted from highest to lowest)
colored by year it was listed
details about title , description , and number of beds shown on hover

Note that to accomplish this, we have to combine information across all three of our tables, but we can do so directly in Tableau.

Chart 3: Rating by Length

Next, suppose we want to know what kind of users our platform is pleasing the most. Let's look at the average rating for each of the different lengths of bookings.

User Dashboard on Real-Time Data

Let’s throw all these charts together in a dashboard:

You may notice the ratings by length are roughly the same between length of stay—and that’s because the mock data was generated for each length from the same rating distribution!

To illustrate that this dashboard gets updated in real time on the live DynamoDB source, we’ll add one record to try and noticeably skew some of the charts.

Let’s say I decide to sign up for this platform and list my own bedroom in San Francisco, listed for $44 a night. Then, I book my own room 444 times and give it a rating of 4 each time. This Python code snippet generates that record and adds it to DynamoDB:

import boto3

booking = {
        "user": {
            "first_name": "Vahid",
            "last_name": "Fazel-Rezai",
            "email": "vahid@rockset.com",
            "user_id": "fc8ca81a-d1fa-4156-b983-dc2b07c1443c"
        },
        "start_date": "2019-04-04",
        "length_days": "4",
        "review": {
            "rating": "4",
            "text": "Worked 4 me!"
        },
        "cost_usd": "44.00"
    }
item = {
        "first_name": "Vahid",
        "last_name": "Fazel-Rezai",
        "email": "vahid@rockset.com",
        "user_id": "fc8ca81a-d1fa-4156-b983-dc2b07c1443c",
        "listings": [{
            "listing_id": "444444",
            "title": "Bedroom for rent",
            "description": "A place to stay, simple but sufficient.",
            "city": "San Francisco",
            "country": "United States",
            "listed_date": "2019-04-04",
            "price_usd": "11.00",
            "cancellation_policy": "flexible",
            "bathrooms": "1",
            "bedrooms": "1",
            "beds": "1",
            "bookings": 444 * [booking]
        }]
    }

dynamodb = boto3.resource("dynamodb")
table = dynamodb.Table("rental_data")
table.put_item(Item = item)

Sure enough, we just have to refresh our dashboard in Tableau and we can see the difference immediately!

Summary

In this blog post, we walked through creating an interactive dashboard in Tableau that monitors core business data stored in DynamoDB. We used Rockset as the SQL intelligence layer between DynamoDB and Tableau. The steps we followed were:

Start with data in a DynamoDB table.
Create a collection in Rockset, using the DynamoDB table as a source.
Write one or more SQL queries that return the data needed in Tableau.
Create a data source in Tableau using custom SQL.
Use the Tableau interface to create charts and dashboards.

Data-Driven Decisions for Where to Park in SF

Vahid Fazel-Rezai — Fri, 16 Aug 2019 07:00:00 +0000

Have you ever felt uncertain parking in a shady area? In particular, have you ever parked in San Francisco and wondered, if I measured the average inverse square distance to every vehicle incident recorded by the SFPD in the last year, at what percentile would my current location fall?

If so, we built an app for that. In this post we’ll explain our methodology and its implementation.

Parking in San Francisco

Vehicle-related break-ins and thefts are notoriously common in San Francisco. Just last week, items worth half a million dollars were stolen in a high-profile car burglary. There’s even a Twitter account tracking incidents.

The San Francisco Police Department maintains an ongoing dataset of all incidents since January 1, 2018 (there is another one for 2003-2018).The San Francisco Chronicle has created a great map visualization from this to track break-ins. We wanted to make this data even more actionable, to help asses the security of parking in a particular location in real-time.

Hence, the motivating question: if I am looking to park in SF, how can I get a sense of how safe my current spot is?

Defining a Risk Score

Of course, the risk of a parking spot can be measured in many different qualitative and quantitative ways. We chose a quantitative measure, admittedly quite arbitrary, as the average inverse square of the distance between the parking location and every break-in location in the past year.

This just gives a numerical score. We then evaluate this score across a representative sample of parking spots across SF, and place the current parking spot at a percentile within that sample. The higher the score, the closer the spot is to historical incidents (inverse of distance), the higher the risk.

We’ll wrap this all in a simple mobile web app, so in you can open it from your car, press a button, and get a percentile of how secure your parking spot is.

Now, we just have to use the data to compute the risk score percentile. For this task, we’ll load the SFPD data into a Rockset collection and query it upon a user clicking the button.

Loading the Data

To get started quickly, we’ll simply download the data as a CSV and upload the file into a new collection.

Later, we can set up a periodic job to forward the dataset into the collection via the API, so that it always stays up to date.

Filtering the Data

Let’s switch over to the query tab and try writing a query to filter down to the incidents we care about. There are a few conditions we want to check:

Vehicle-related incidents. Each incident has an “Incident Subcategory” assigned by the Crime Analysis Unit of the Police Department. We do a SELECT DISTINCT query on this field and scan the results to pick out the ones we consider vehicle-related.

Motor Vehicle Theft
Motor Vehicle Theft (Attempted)
Theft From Vehicle
Larceny - Auto Parts
Larceny - From Vehicle

Initial report. According to the data documentation, records cannot be edited once they are filed, so some records are filed as “supplemental” to an existing incident. We can filter those out by looking for the word “Initial” in the report type description.

Within SF. The documentation also specifies that some incidents occur outside SF, and that such incidents will have the value “Out of SF” in the police district field.

Last year. The dataset provides a datetime field, which we can parse and ensure is within the last 12 months.

Geolocation available. We notice some rows are missing the latitude and longitude fields, instead having an empty string. We will simply ignore these records by filtering them out.

Putting all these conditions together, we can prune down from 242,012 records in this dataset to just the 28,224 relevant vehicle incidents, packaged up into a WITH query.

Calculating a Risk Score, One Spot

Now that we have all vehicle incidents in the last year, let’s see if we can calculate the security score for San Francisco City Hall, which has a latitude of 37.7793° N and longitude of 122.4193° W.

Using some good old math tricks (radius times angle in radians to get arc length, approximating arc length as straight-line distance, and Pythagorean theorem), we can compute the distance in miles to each past incident:

We aggregate these distances using our formula from above, and voila!

For our app, we will replace the latitude/longitude of City Hall with parameters coming from the user’s browser location.

Sample of Parking Spots in SF

So we can calculate a risk score—1.63 for City Hall—but that is meaningless unless we can compare it to the other parking spots in SF. We need to find a representative set of all possible parking spots in SF and compute the risk score for each to get a distribution of risk scores.

Turns out, the SFMTA has exactly what we need—field surveys are conducted to count the number of on-street parking spots and their results are published as an open dataset. We’ll upload this into Rockset as well!

Let’s see what this dataset contains:

For each street, let’s pull out the latitude/longitude values (just the first point, close enough approximation), count of spots, and a unique identifier (casting types as necessary):

Calculating Risk Score, Every Spot in SF

Now, let’s try calculating a score for each of these points, just like we did above for City Hall:

And there we have it! A parking risk score for each street segment in SF. This is a heavy query, so to lighten the load we’ve actually sampled 5% of each streets and incidents.

(Coming soon to Rockset: geo-indexing—watch out for a blog post about that in the coming weeks!)

Let’s stash the results of this query in another collection so that we can use it to calculate percentiles. We first create a new empty collection:

Now we run an INSERT INTO sf_risk_scores SELECT ... query, bumping up to 10% sampling on both incidents and streets:

Ranking Risk Score as Percentile

Now let’s get a percentile for City Hall against the sample we’ve inserted into sf_risk_scores. We keep our spot score calculation as we had at first, but now also count what percent of our sampled parking spots are safer than the current spot.

Parking-Spot-Risk-Score-as-a-Service

Now that we have an arguably useful query, let’s turn it into an app!

We’ll keep it simple—we’ll create an AWS Lambda function that will serve two types of requests. On GET requests, it will serve a local index.html file, which serves as the UI. On POST requests, it will parse query params for lat and lon and pass them on as parameters in the last query above. The lambda code looks like this:

For the client-side, we write a script to fetch the browser's location and then call the backend:

We add some basic HTML elements and styling, and it’s ready to ship!

To finish it off, we add API Gateway as a trigger for our lambda and drop a Rockset API key into the environment, which can all be done in the AWS Console.

Conclusion

To summarize what we did here:

We took two fairly straightforward datasets—one for incidents reported by SPFD and one for parking spots reported by SFMTA—and loaded the data into Rockset.
Several iterations of SQL later, we had an API we could call to fetch a risk score for a given geolocation.
We wrote some simple code into an AWS Lambda to serve this as a mobile web app.

The only software needed was a web browser (download the data, query in Rockset Console, and deploy in AWS Console), and all told this took less than a day to build, from idea to production. The source code for the lambda is available here.

If you park in SF, feel free to try out the app here. If not, comment with what kind of real-life decisions you think could be improved with more data!