Databricks sample NFL dataset

Create a Databricks database with sample NFL data

The code this post is relating to is here: https://github.com/mortie23/databricks-nfl-data.

This is using:

• Azure Databricks
• Azure Data Lake Storage (ADLS) Gen2

Create your storage account and container

In this demo I have created a storage account called nfl and a container within the account called nfldata I have added my Azure Active Directory account as a Storage Blob Data Contributor.

Load data to ADLS

I am using Azure Storage Explorer to do this. You could also use the Azure portal, the azcopy cli or many other methods.

Clone this repo to your Databricks

Open the notebook called db.sql.py

Run the Notebook

1. First we will create the Hive database for the tables.

create database nfl

2. The notebook has two functions.
   • Reading a CSV file and creating a Delta table
   • Reading all files in an ADLS container and running the aforementioned function for each.

Test the results

The notebook has a sample query to test the results. This will get all players from the SF 49ers and sort them by the number rushing yards they had in each game.

select
  p.PLAYER_NAME
  , p.FIELD_POSITION
  , t.TEAM_SHORT
  , p.RUSHING_YARDS
  , p.PASSING_YARDS
from
  nfl.player p
  inner join nfl.team_lookup
on p.team_id = t.team_id
  and t.team_short = 'SF'
order by p.RUSHING_YARDS desc

For the 2014 NFL season, for the 49ers, Frank Gore and Colin Kaepernick were racking up the rushing yards.

Top 20