Databricks
About Databricks
Databricks is a powerful modern data digestion and analytics platform created by the original founders of Apache Spark. Integrated AI systems analyze big data to optimize performance and research capabilities. Azure Databricks works on top of the Azure environment, allowing for increased compatibility with existing Microsoft Azure services. Databricks operations are fast and highly customizable, allowing for dynamic and powerful workflows.
Accessing Databricks
To access Databricks, you'll need a link to the Darden Databricks workspace.
Please contact Rohit Singla to gain access and to begin setting up a personal storage container, or for any data requests or concerns:
singlar@darden.virginia.edu
Using Azure Storage Explorer
Installing
The Storage Explorer install page can be found here. On this page, select Download Now and choose the version that matches your machine.
Next, open your personal file explorer and click on the setup executable. When prompted, accept the license agreement.
Select Install, then wait for the installation to finish. Upon opening Storage Explorer, you will be greeted with the Get Started page. Select Sign in With Azure. When prompted to "Select an Azure Environment," simply select Azure.
You will be redirected to a browser window to sign in - do so using your Darden email. Now, under Account Management on the left menu, you should see your email and "Darden Business School," under which you should see the subscription "DAR-ORS-PRD-2." If you do not see this subscription or have any questions, please contact singlar@darden.virginia.edu.
Using Storage Explorer
To access your personal container, select Explorer on the lefthand menu. Under the DAR-ORS-PRD-02 subscription, select Storage Accounts -> labusers -> Blob Containers.
Under this folder, you should see a container with your name. This is your personal storage container where you can upload custom data to be used in Databricks.
If you do not see a storage container matching your name, please refer to the section "Accessing Databricks" or contact singlar@darden.virginia.edu if you have already completed those steps.
Double clicking your container on the left hand menu will open it in the main view. From here, you can perform actions typical of a file explorer on your data: upload/download, folder creation, copy/paste. You can also edit access permissions on your files in case someone else has access to your container.
To upload a file, first select Upload in the top bar, and choose wether you are uploading a directory or a individual files. You will be presented with a few options either way.
First, select the file(s) you want to upload.
Under Blob type you can choose from a few different means of file optimization. Block blobs are ideal for large data uploads. Page blobs are optimized to be accessed randomly. Append blobs are optimized for append operations. You can read more about the blob types here.
Under Target access tier you can optimize your data storage based on the access needs of the data you will upload. The "Hot" tier is ideal for data that will be read from and written to frequently. You can read about the other options and their meanings here.
Under Destination directory you can select where in your container the file will go.
Under Overwrite option you can determine whether identical files will overwrite each other or do nothing. Selecting "Prompt" will cause the program to ask you what to do for each case.
Getting Started
Before we get into how to use Databricks with your data, let's quickly go over where things are located within Databricks.
Workspace
The Workspace is where you can find all of the resources you've previously created or uploaded into Databricks. This includes notebooks, queries, dashboards, and AI Genie sessions you may have opened.
You can also find resources other users have shared with you under Shared with me
Catalog
In the Catalog you can find Darden-managed tables:
The dlabinternal container contains project-specific data.
The dlabpublic contains data available to all Darden researchers.
The institutional container contains institution-specific data.
The userdata container contains your personal schema, where you can build your own interim tables suited to your research.
Development Spaces
In the menu on the left-hand side, under the section labeled SQL, you can find links to many of the available development environments provided by Databricks.
SQL Editor: A dedicated space to query SQL tables from the Catalog.
Queries: A place to find previously created queries.
Dashboards: Allows for larger-scale data visualization in an organized and aesthetically pleasing manner.
Genie space: The AI Genie is Databricks' built in assistant for quickly creating SQL queries, writing code, and providing development insights.
Databricks Notebooks
This section will explain how to read and write data in Databricks using notebooks. We will cover reading user-uploaded csv files and Darden-managed SQL tables, as well as writing to csv files. This guide will primarily use Python and SQL
Creating a Notebook
To get started, open up Databricks and navigate to the workspace. From here, click Create on the right-hand side of the screen, and select Notebook
Using the Notebook
Now, within the notebook space, you should see an auto-generated cell. Each cell is a section of your notebook and can either be code or text. Cells can be edited with the build-in AI assistant. To create a new cell as any of these three options, hover below or above any existing cell and select your preferred option.
When you run a notebook using the Run all button, each cell will execute in order from top to bottom, with the lower cells waiting for the cell above to finish before they run. All cells that have finished executing will retain their outputs and information unless rerun later. Cells can also be rerun individually and out of order, but this can cause errors if your cells reference variables set by other cells above them.
In Databricks, the default coding language for your cells is Python, but you can change this default at the top of the page. You can also change the language on a cell-by-cell basis in the top-right corner of any cell. The language options are Python, SQL, Scala, or R.
Another way of changing a cell's language is using the language magic command, %<language> (ex. %python). Using this syntax you can also run shell commands (%sh), dbutils filesystem commands (%fs) or add various types of documentation (%md).
AI Genie
One of the most powerful tools within Databricks is the AI genie within notebooks. At the top right corner of any cell there is a small star symbol. Hitting this makes a prompt space where you can ask the AI to generate code for you. For example:
This feature is primarily for generating code that you intend to run. You can ask the AI to explain something to you, but it will do so by filling in the cell with code comments.
Notebook Persistence
Because Databricks Notebooks run on compute clusters and not your personal machine, once you have hit run on a notebook or an individual cell, your code will continue to run even if you close the page.
On most standard compute options, the cluster will remain active as long as there is something actively running. This is useful for large queries or any program that will take a long time to run.
Reading and Writing Data
This section will cover methods for reading data and saving it in Databricks notebooks. If you are unfamiliar with Databricks notebooks, please read the previous guide.
Reading SQL Tables
To read a SQL table from a notebook, you can either create a SQL cell and write a simple query like so:
Or you may want to load the table into a PySpark dataframe like so:
Typically, PySpark dataframes allow for more complex data manipulation. Scenarios where a dataframe is more advantageous include:
Merging a SQL table with a .csv or .parquet file
Seeking rows that match criteria that can't be easily written in a query (ex. a frequently-changing list from an external source)
Filtering data intended to be processed further with another python program
For more information regarding PySpark operations and techniques, we recommend reading the official documentation.
Reading Custom Data
If you have uploaded your own data into your personal storage container through Azure Storage Explorer, you are able to load that data directly into a notebook. This is as simple as creating a Python cell in your notebook and running the following command:
Where the container name is the same as your container in Azure Storage Explorer, and the path is the file path in that system as well. If you have an external location set up, this should run without requiring any extra authentication. The format option specifies the file type of your data source, for example “csv” or “json”
Writing to Azure Storage Explorer
If you'd like to save a PySpark dataframe back to your personal storage container, run the following:
This will save your dataframe to a file with an arbitrary name within the specified folder, due to the nature of PySpark. You may have to rename your file after this operation runs.
There are various writing options to choose from that allow for better control, such as the option “.mode('overwrite').” For more information regarding writing modes and options, we recommend reviewing the PySpark Documentation.
Using the AI Genie
The Databricks Genie allows you to query any table from the Catalog in plaintext English using AI to generate the SQL queries. These queries can span multiple tables, provided you add them to the list of data. Questions don't have to be SQL queries either - you can ask the genie to generate a table using SQL and then summarize that same table, or just explain the purpose of a column.
Creating a Genie Space in Databricks
To get started, open up Databricks and navigate to the workspace. From here, click Create on the right-hand side of the screen, and select Genie space.
When you first open a new Genie space, you will be prompted to “Connect your data.” Here you can select which tables you'd like to query (this can be changed later). Multiple tables can be selected if you want to prompt the Genie to gather insights across several tables.
Hit Create when you've selected your tables.
Now you are in the Genie space and should see a prompt on the bottom of the screen to ask the Genie questions, as well as a few tabs on the right-hand side.
Under the Data tab you can add or remove tables from the Unity Catalog from the Genie's scope.
Under Instructions you have several options to customize your Genie. The Text tab allows you to explain things to the Genie, like the purpose of certain columns in your data or what clarifications on questions users might ask the Genie later. The Joins tab allows you to get specific about how the Genie should join tables if asked to. The SQL Queries tab provides the opportunity to train the Genie on example queries that could guide its knowledge.
Under Settings you can change things like the name of your Genie space, the compute it uses, and the description of the space. You can also provide sample questions that the Genie will then prompt users to ask when they first open the space.
Querying Data Across Multiple Tables
One of the most valuable aspects of the AI Genie is its ability to take your plain English input and turn it into a multi-table SQL query. In the example below, the AI Genie gets reviews for a bakery and joins this data with demographic information about the customers who left the reviews, joining two separate tables.
We can also ask it to summarize these recent reviews in text form:
If you're ever unhappy with a response or the way a query is written, you can select Fix it to explain what was incorrect and what you would like to see.