# DataBricks Useful Commands

## Visualization on DataBricks

Databricks actually provides a tableau-like visualization solution. The `display()` function gives a friendly UI to generate any aggregate plots you like. It's a button that shows under the cell with the `display()` command. It will generate the graph on the first 1000 rows by default, once you're satsified with your graph, you might want to click "aggregate over whole data" link that shows on Plot Options UI.

Or you can use `.histogram` of `.hist` on a PySpark data frame. --> see [python - Pyspark: show histogram of a data frame column - Stack Overflow](https://stackoverflow.com/questions/39154325/pyspark-show-histogram-of-a-data-frame-column)

[Introduction to DataFrames - Scala | Databricks on AWS](https://docs.databricks.com/spark/latest/dataframes-datasets/introduction-to-dataframes-scala.html) on operations on DataFrames in Scala; from DataBricks. [Plot Data from Apache Spark | Python/v3 | Plotly](https://plotly.com/python/v3/apache-spark/) guide. You can also use Matplotlib on a Python object, like Pandas or Numpy.

NOTE: And if your matplotlib figure didn't show, even with `plt.show()` wrap it in display command, `display(plt.show())`

## Folders in a path, DataBricks

If you just use `dbutils.fs.ls(path)` it will return confusing list that has folder name and other things, which is hard to scan through, visually or programatically. The function below parse that for you and extract only folder names

```
def files_in_path(path):
	lst=[str(item).split('name=')[1].split(",")[0].split("'")[1].strip('/') for item in dbutils.fs.ls(path)]
	return lst
```

Read docs on [DataBricks dbutils](https://docs.microsoft.com/en-us/azure/databricks/dev-tools/databricks-utils)

### DataBricks File System Workspace Storage, DBFS

Aside from the the cloud blob storage, whether that is Azure, AWS, or Google Cloud; there's also a "local" DataBricks storage path starts with `/dbfs` we used to be able to navigate through that with a GUI by clicking on the topmost button on the left menu in DataBricks Notebook interface, but not anymore. It still exists nonetheless.&#x20;

To explore what files currently exist in it, use command `dbutils.fs.ls("/dbfs"/)` or the `os` Python library equivalent of `os.listdir("/dbfs")`&#x20;

I use this path when I save my PySpark models while I'm testing. You can also save .txt, .json, .csv small files.&#x20;

To make it orderly, I recommend creating your own folder in `("/dbfs/FileStore/shared_uploads/my_folder/models/")` I belive ("/dbfs/FileStore/shared\_uploads/") exist by default. Create your folder and its subfolders using os library or DataBricks utilities dbutils.

Don't confuse that with the local path that starts with `("./FileStore/shared_uploads/")` because it techniqually doesn't exist, yet somehow you can save models to, but not load from. It confused me at the time of writing this page. Again verify yourself, and load your model to make sure it has been saved where you intended _before_ you shut down your notebook, to not lose your work.    &#x20;

## Deleting a table

No need to remind you to use extra caution when deleting stuff, you already know. right? right?! I use it to drop tables that I have saved. I make sure to end the name of each table I create with my initials, such that it's easy to retrieve and locate. DataBricks' `dbutils.fs.rm("path", True)` which will return True if removed succesfully. Or using SQL Context `DROP TABLE`

```
%sql
DROP TABLE [IF EXISTS] {table_name}
```

If using Azure Synapse Analytics, the magic command is double %% instead of just one. See [Azure SQL docs entry](https://docs.microsoft.com/en-us/sql/t-sql/statements/drop-table-transact-sql?view=sql-server-ver15)

## Passing arguments to a notebook when running from another notebook

You know that you can call/run a DataBricks notebook from your current notebook with the command

```
%run "path"
```

Whether that is the absolute or relative path. Make sure there's nothing else in that cell with the run command, not even a comment. otherwise it won't work.

You can also pass variables to change or override the ones in the notebook you're calling. One way, is to already know the variables you want to tweak when writing the first notebook, then when you call it, you fix the value of the wanted variables in the notebook you're calling it in, e.g.

```
var1= "some_value"

%run "./first_notebook"
```

Assuming that usually your imported notebook is a collection of functions and procedures you want to use over and over. Another way, is to use DataBricks' `dbutils.notebook.run` here's the template

```
dbutils.notebook.run(
	"notebook-name", 
	3600, 
	{"parameterFloat":2.5, "parameterString":"abc"})
```

Where 3600 is the timeout in seconds.

Resources: [notebook workflows](https://docs.microsoft.com/en-us/azure/databricks/notebooks/notebook-workflows) [great Medium post comparing the two methods](https://medium.com/datasentics/how-to-execute-a-databricks-notebook-from-another-notebook-b4a5b093cb2) This post the author mentions that you can't actually override functions and variables with the `dbutils.notebook.run` I haven't tried it myself.

**NOTE**&#x20;

In other Spark Zeppelin notebook providers, for example Azure Synapse Analytics, you can accomplish that with this template

```
%run "other_notebook_path" {"parameterInt": 1, "parameterFloat":2.5, "parameterBool":True, "parameterString":"abc"}
```

Resource: [Azure Synapse Docs](https://docs.microsoft.com/en-us/azure/synapse-analytics/spark/apache-spark-development-using-notebooks) section: Notebook Reference By the time of writing this post, Azure Synapse Analytics lagged behind DataBricks in adopting Spark 3.0 and it's bare bone in terms of Spark, doesn't have all the nice optimizations that DataBricks provide for you. Though it does have nice features like IDE-like code typing assistance, and notebook outline. They call their Spark clusters: Spark Pools. But Microsoft provided a really good documentation for their Azure Synapse Analytics, and one can argue that having all your data needs, in theory, in one place is helpful. So it's a trade off.

## Collection of quick things to do in DataBricks

### **displaying x number or rows**

Using `display()` on a dataframe, shows the first 1000 rows by default, where you can scroll through. If for some reason you want just the first, say 10, you'd need to use .limit like this `df.limit(10).display()` or `display(df.limit(10))` Using `df.display(10)` doesn't take effect.

### **displaying matplotlib graphs**

If for some reason your plot didn't show, even if you add `plt.show()` at the end of your plotting snippet, add `display(plt.show())` instead; that will print it out for sure.

### **exploring and editing blob storage**

DataBricks has its own tools for that, called `dbutils` with its sub module `fs`. Search that online for all your needs to copy, move, delete, etc a table from blob storage. Use dbutils.fs.ls("blob\_storage\_path")' to explore it. Results will be a list, that needs some text editing with `.split()` if you want only the name of the folder. Here's my practicaly not so pretty way of doing it,

```
def files_in_path(path):
  lst= [str(item).split("name=")[1].split(",")[0].split("'")[1].strip("/") for item in dbutils.fs.ls(path)]
	return lst
```

### **installing libraries in a session**

You can install the library on the cluster itself, but I think it's a better practice to install it in the notebook on the Spark session instead; such that you always know the version, and you can upgrade the libraries easily

```
#this is the very first cell in the notebook after authenticaion cell if any
dbutils.library.installPyPI("package", version="xx.x.xx", repo= "repo", extras="extras")
#repeat command for all the libraries you need
dbutils.library.restartPython()
```

After that, import the libraries you just installed, and any other library from Spark or Python in the cell after.

### **Reveal Python version in the current cluter**

```
import sys
sys.version
```

**Reading Delta tables**

```
spark.read.format("delta").timestampAsOf or versionAsOf
``
```
