ใช้ตัวอย่างไฟล์ json จาก Read data in JSON format

# Required for StructField, StringType, IntegerType, etc.
from pyspark.sql.types import *

jsonSchema = StructType([
  StructField("id", LongType(), True),
  StructField("father", StringType(), True),
  StructField("mother", StringType(), True),
  StructField("children", ArrayType(StringType()), True)
])

jsonFile = "/mnt/training/sample.json"

testDF = (spark.read    # The DataFrameReader
  .schema(jsonSchema)   # Use the specified schema
  .json(jsonFile)       # Creates a DataFrame from JSON after reading in the file
)

ดู Schema และ column type

testDF.printSchema()

columns = testDF.dtypes
print(columns)

root
 |-- id: long (nullable = true)
 |-- father: string (nullable = true)
 |-- mother: string (nullable = true)
 |-- children: array (nullable = true)
 |    |-- element: string (containsNull = true)

[('id', 'bigint'), ('father', 'string'), ('mother', 'string'), ('children', 'array<string>')]

ทดสอบด้วย assert

assert len(columns) == 4, "Expected 4 columns but found " + str(len(columns))

assert columns[0][0] == "id",            "Expected column 0 to be \"id\" but found \"" + columns[0][0] + "\"."
assert columns[0][1] == "bigint",        "Expected column 0 to be of type \"bigint\" but found \"" + columns[0][1] + "\"."

assert columns[1][0] == "father",        "Expected column 0 to be \"father\" but found \"" + columns[0][0] + "\"."
assert columns[1][1] == "string",        "Expected column 0 to be of type \"string\" but found \"" + columns[0][1] + "\"."

assert columns[2][0] == "mother",        "Expected column 0 to be \"father\" but found \"" + columns[0][0] + "\"."
assert columns[2][1] == "string",        "Expected column 0 to be of type \"string\" but found \"" + columns[0][1] + "\"."

assert columns[3][0] == "children",      "Expected column 0 to be \"father\" but found \"" + columns[0][0] + "\"."
assert columns[3][1] == "array<string>", "Expected column 0 to be of type \"array<string>\" but found \"" + columns[0][1] + "\"."

print("Congratulations, all tests passed... that is if no jobs were triggered :-)\n")

Congratulations, all tests passed... that is if no jobs were triggered :-)

• Write data – Learn | Microsoft Docs

Just as there are many ways to read data, we have just as many ways to write data.

1. Writing Data

1. Writing Data

• Writing data to Parquet files

ใช้ตัวอย่างไฟล์ json จาก Read data in JSON format

# Required for StructField, StringType, IntegerType, etc.
from pyspark.sql.types import *

jsonSchema = StructType([
  StructField("id", LongType(), True),
  StructField("father", StringType(), True),
  StructField("mother", StringType(), True),
  StructField("children", StructType([
    StructField("first", StringType(), True),
    StructField("second", StringType(), True),
    StructField("third", StringType(), True)
  ]), True),
])

jsonFile = "/mnt/training/sample2.json"

jsonDF = (spark.read
    .schema(jsonSchema)
    .json(jsonFile)
)

display(jsonDF)

Now that we have a DataFrame, we can write it back out as Parquet files or other various formats.

parquetFile = "/mnt/training/family.parquet"

print("Output location: " + parquetFile)

(jsonDF.write                      # Our DataFrameWriter
  .option("compression", "snappy") # One of none, snappy, gzip, and lzo
  .mode("overwrite")               # Replace existing files
  .parquet(parquetFile)            # Write DataFrame to Parquet files
)

Now that the file has been written out, we can see it in the DBFS:

%fs ls /mnt/training/family.parquet

display(dbutils.fs.ls(parquetFile))

And lastly we can read that same parquet file back in and display the results:

display(spark.read.parquet(parquetFile))

Writing to CSV

ในตัวอย่างนี้ถ้า Write เป็น csv จะ error เพราะ CSV data source does not support struct<first:string,second:string,third:string> data type.

csvFile = "/mnt/training/family.csv"

print("Output location: " + csvFile)

(jsonDF.write                      # Our DataFrameWriter
  .mode("overwrite")               # Replace existing files
  .csv(csvFile)                    # Write DataFrame to Parquet files
)

ถ้าเป็น json นี้ Write เป็น csv จะ error เพราะ CSV data source does not support array<string> data type.

# Required for StructField, StringType, IntegerType, etc.
from pyspark.sql.types import *

jsonSchema = StructType([
  StructField("id", LongType(), True),
  StructField("father", StringType(), True),
  StructField("mother", StringType(), True),
  StructField("children", ArrayType(StringType()), True)
])

• Read data stored in tables and views – Learn | Microsoft Docs
• CREATE VIEW | Databricks on AWS
• SELECT | Databricks on AWS (named_expression)
• Databricks Runtime expression | Databricks on AWS (expression)
• Create views | Databricks on AWS (Data masking)
• LATERAL VIEW clause (Databricks SQL) | Databricks on AWS (LATERAL)

1. Databases and tables
2. Views
3. Reading from a Table/View
4. Temporary Views

1. Databases and tables

• Databases and tables – Azure Databricks | Microsoft Docs

An Azure Databricks database (schema) is a collection of tables. An Azure Databricks table is a collection of structured data. You can cache, filter, and perform any operations supported by Apache Spark DataFrames on Azure Databricks tables. You can query tables with Spark APIs and Spark SQL.

2. Views

• CREATE VIEW – Azure Databricks | Microsoft Docs

Constructs a virtual table that has no physical data based on the result-set of a SQL query. ALTER VIEW and DROP VIEW only change metadata.

3. Reading from a Table/View

tempDF = spark.read.table("jack_db.jack_table1")

tempDF.printSchema()

display(tempDF)

%sql
SELECT * 
FROM jack_db.jack_table1
LIMIT(5)

Review: Reading from Tables

• No job is executed – the schema is stored in the table definition on Databricks.
• The file is stored on the DBFS. If we used JDBC, it would open the connection to the database and read it in. If we used an object store (like what is backing the DBFS), it would read the data from source.

4. Temporary Views

We can create a [temporary] view with createOrReplaceTempView()

# create a temporary view from the resulting DataFrame
tempDF.createOrReplaceTempView("jack_view1")

%sql
SELECT * 
FROM jack_view1 
ORDER BY user_id 
DESC LIMIT (5)

** Note #1: ** The method createOrReplaceTempView(..) is bound to the SparkSession meaning it will be discarded once the session ends.

** Note #2: ** On the other hand, the method createOrReplaceGlobalTempView(..) is bound to the spark application.*

Or to put that another way, I can use createOrReplaceTempView(..) in this notebook only. However, I can call createOrReplaceGlobalTempView(..) in this notebook and then access it from another.

• Apache Parquet
• Read data in Parquet format – Learn | Microsoft Docs

1. ตรวจสอบไฟล์ข้อมูล
2. Read in the Parquet Files
3. Read in the Parquet Files – Schema

1. ตรวจสอบไฟล์ข้อมูล

ตัวอย่างไฟล์ parquet นำมาจาก kylo/samples/sample-data/parquet at master · Teradata/kylo · GitHub และทำการลบไฟล์ README.txt ออกก่อน ค่อยนำมาทดสอบ

%fs ls /mnt/training/sample_parquet/

2. Read in the Parquet Files

To read in this files, we will specify the location of the parquet directory.

parquetFile = "/mnt/training/sample_parquet/"

tempDF = (spark.read
    .parquet(parquetFile)
)

tempDF.printSchema()

root
 |-- registration_dttm: timestamp (nullable = true)
 |-- id: integer (nullable = true)
 |-- first_name: string (nullable = true)
 |-- last_name: string (nullable = true)
 |-- email: string (nullable = true)
 |-- gender: string (nullable = true)
 |-- ip_address: string (nullable = true)
 |-- cc: string (nullable = true)
 |-- country: string (nullable = true)
 |-- birthdate: string (nullable = true)
 |-- salary: double (nullable = true)
 |-- title: string (nullable = true)
 |-- comments: string (nullable = true)

display(tempDF)

Review: Reading from Parquet Files

• We do not need to specify the schema – the column names and data types are stored in the parquet files.
• Only one job is required to read that schema from the parquet file’s metadata.
• The parquet reader can “read” the schema very quickly because it’s reading that schema from the metadata.

3. Read in the Parquet Files – Schema

If you want to avoid the extra job entirely, we can, again, specify the schema even for parquet files:

** WARNING ** Providing a schema may avoid this one-time hit to determine the DataFrame's schema. However, if you specify the wrong schema it will conflict with the true schema and will result in an analysis exception at runtime.

# Required for StructField, StringType, IntegerType, etc.
from pyspark.sql.types import *

parquetSchema = StructType(
  [
    StructField("registration_dttm", TimestampType(), False),
    StructField("id", IntegerType(), False),
    StructField("first_name", StringType(), False),
    StructField("last_name", StringType(), False),
    StructField("email", StringType(), False),
    StructField("gender", StringType(), False),
    StructField("ip_address", StringType(), False),
    StructField("cc", StringType(), False),
    StructField("country", StringType(), False),
    StructField("birthdate", StringType(), False),
    StructField("salary", DoubleType(), False),
    StructField("title", StringType(), False),
    StructField("comments", StringType(), False)
  ]
)

• TimestampType (Spark 3.2.1 JavaDoc) (apache.org)
• IntegerType (Spark 3.2.1 JavaDoc) (apache.org)
• DoubleType (Spark 3.2.1 JavaDoc) (apache.org)

(spark.read               # The DataFrameReader
  .schema(parquetSchema)  # Use the specified schema
  .parquet(parquetFile)   # Creates a DataFrame from Parquet after reading in the file
  .printSchema()          # Print the DataFrame's schema
)

root
 |-- registration_dttm: timestamp (nullable = true)
 |-- id: integer (nullable = true)
 |-- first_name: string (nullable = true)
 |-- last_name: string (nullable = true)
 |-- email: string (nullable = true)
 |-- gender: string (nullable = true)
 |-- ip_address: string (nullable = true)
 |-- cc: string (nullable = true)
 |-- country: string (nullable = true)
 |-- birthdate: string (nullable = true)
 |-- salary: double (nullable = true)
 |-- title: string (nullable = true)
 |-- comments: string (nullable = true)

In most/many cases, people do not provide the schema for Parquet files because reading in the schema is such a cheap process.

1. ตรวจสอบไฟล์ข้อมูล
2. Reading from JSON – InferSchema
3. Reading from JSON – User-Defined Schema

1. ตรวจสอบไฟล์ข้อมูล

We can use %fs ls … to view the file on the DBFS.

%fs ls dbfs:/mnt/training/sample.json

We can use %fs head … to peek at the first couple thousand characters of the file.

%fs head /mnt/training/sample.json

{"id":1,"father":"Mark","mother":"Charlotte","children":["Tom"]}
{"id":2,"father":"John","mother":"Ann","children":["Jessika","Antony","Jack"]}
{"id":3,"father":"Bob","mother":"Monika","children":["Jerry","Karol"]}

และ

%fs head /mnt/training/sample2.json

{"id":1,"father":"Mark","mother":"Charlotte","children":{"first":"Tom1"}}
{"id":2,"father":"John","mother":"Ann","children":{"first":"Jack1","second":"Jack2"}}
{"id":3,"father":"Bob","mother":"Monika","children":{"first":"Karol1","second":"Karol2","third":"Karol3"}}

CREATE OR REPLACE TEMP VIEW tmp_json 
AS 
SELECT * 
FROM   JSON.`path/to/file.json` 

2. Reading from JSON – InferSchema

JSON Lines

• That there is one JSON object per line and…
• That it’s delineated by a new-line.

This format is referred to as JSON Lines or newline-delimited JSON. More information about this format can be found at http://jsonlines.org.

** Note: ** Spark 2.2 was released on July 11th 2016. With that comes File IO improvements for CSV & JSON, but more importantly, Support for parsing multi-line JSON and CSV files. You can read more about that (and other features in Spark 2.2) in the Databricks Blog.

Read The JSON File ตัวอย่าง 1

The command to read in JSON looks very similar to that of CSV.

jsonFile = "/mnt/training/sample.json"
tempDF = (spark.read           # The DataFrameReader
   .option("inferSchema", "true")   # Automatically infer data types & column names
   .json(jsonFile)              # Creates a DataFrame from CSV after reading in the file
)

tempDF.printSchema()

root
 |-- children: array (nullable = true)
 |    |-- element: string (containsNull = true)
 |-- father: string (nullable = true)
 |-- id: long (nullable = true)
 |-- mother: string (nullable = true)

With our DataFrame created, we can now take a peak at the data. But to demonstrate a unique aspect of JSON data (or any data with embedded fields), we will first create a temporary view and then view the data via SQL:

# create a view called temp_view
tempDF.createOrReplaceTempView("temp_view")

And now we can take a peak at the data with simple SQL SELECT statement:

%sql
SELECT * FROM temp_view

%sql
SELECT id, father, mother, children
FROM temp_view

Read The JSON File ตัวอย่าง 2

jsonFile2 = "/mnt/training/sample2.json"

tempDF2 = (spark.read           # The DataFrameReader
   .option("inferSchema", "true")   # Automatically infer data types & column names
   .json(jsonFile2)              # Creates a DataFrame from CSV after reading in the file
)

tempDF2.printSchema()

root
 |-- children: struct (nullable = true)
 |    |-- first: string (nullable = true)
 |    |-- second: string (nullable = true)
 |    |-- third: string (nullable = true)
 |-- father: string (nullable = true)
 |-- id: long (nullable = true)
 |-- mother: string (nullable = true)

# create a view called temp_view2
tempDF2.createOrReplaceTempView("temp_view2")

%sql
SELECT * FROM temp_view2

%sql
SELECT id, children.first, children.second, children.third 
FROM temp_view2 
WHERE children.second IS NOT NULL

Review: Reading from JSON – InferSchema

• We only need one job even when inferring the schema.
• There is no header which is why there isn’t a second job in this case – the column names are extracted from the JSON object’s attributes.

3. Reading from JSON – User-Defined Schema

To avoid the extra job, we can (just like we did with CSV) specify the schema for the DataFrame.

 ตัวอย่าง 1 Step #1 – Create the Schema

Note that we can support complex data types as seen in the field children.

# Required for StructField, StringType, IntegerType, etc.
from pyspark.sql.types import *

jsonSchema = StructType([
  StructField("id", LongType(), True),
  StructField("father", StringType(), True),
  StructField("mother", StringType(), True),
  StructField("children", ArrayType(StringType()), True)
])

• Spark ArrayType Column on DataFrame & SQL – Spark by {Examples} (sparkbyexamples.com)

For a small file, manually creating the the schema may not be worth the effort. However, for a large file, the time to manually create the schema may be worth the trade off of a really long infer-schema process.

 ตัวอย่าง 1 Step #2 – Read in the JSON

Next, we will read in the JSON file and print its schema.

(spark.read             # The DataFrameReader
  .schema(jsonSchema)   # Use the specified schema
  .json(jsonFile)       # Creates a DataFrame from JSON after reading in the file
  .printSchema()
)

root
 |-- id: long (nullable = true)
 |-- father: string (nullable = true)
 |-- mother: string (nullable = true)
 |-- children: array (nullable = true)
 |    |-- element: string (containsNull = true)

 ตัวอย่าง 2 Step #1 – Create the Schema

# Required for StructField, StringType, IntegerType, etc.
from pyspark.sql.types import *

jsonSchema2 = StructType([
  StructField("id", LongType(), True),
  StructField("father", StringType(), True),
  StructField("mother", StringType(), True),
  StructField("children", StructType([
    StructField("first", StringType(), True),
    StructField("second", StringType(), True),
    StructField("third", StringType(), True)
  ]), True),
])

 ตัวอย่าง 2 Step #2 – Read in the JSON

(spark.read             # The DataFrameReader
  .schema(jsonSchema2)  # Use the specified schema
  .json(jsonFile2)      # Creates a DataFrame from JSON after reading in the file
  .printSchema()
)

root
 |-- id: long (nullable = true)
 |-- father: string (nullable = true)
 |-- mother: string (nullable = true)
 |-- children: struct (nullable = true)
 |    |-- first: string (nullable = true)
 |    |-- second: string (nullable = true)
 |    |-- third: string (nullable = true)

Review: Reading from JSON – User-Defined Schema

• Just like CSV, providing the schema avoids the extra jobs.
• The schema allows us to rename columns and specify alternate data types.

คำสั่ง display()

tempDF2 = (spark.read
  .schema(jsonSchema2)
  .json(jsonFile2)
)

display(tempDF2)

• Read and write data in Azure Databricks – Learn | Microsoft Docs
• CSV file | Databricks on AWS

1. ตรวจสอบไฟล์ข้อมูล
2. Reading from CSV – InferSchema
3. Reading from CSV – User-Defined Schema
4. Reading from multiple CSV

1. ตรวจสอบไฟล์ข้อมูล

We can use %fs ls … to view the file on the DBFS.

%fs ls /mnt/training/

We can use %fs head … to peek at the first couple thousand characters of the file.

%fs head /mnt/training/sample.tsv

ID	Name	Weight
1	Man1	61
2	Man2	62
3	Man3	63
4	Man4	64
5	Man5	65
6	Man6	66
7	Man7	67
8	Man8	68
9	Man9	69

2. Reading from CSV – InferSchema

Step #1 – Read The CSV File

Let’s start with the bare minimum by specifying the tab character as the delimiter and the location of the file:

# A reference to our tab-separated-file
csvFile = "/mnt/training/sample.tsv"

tempDF = (spark.read           # The DataFrameReader
   .option("sep", "\t")        # Use tab delimiter (default is comma-separator)
   .csv(csvFile)               # Creates a DataFrame from CSV after reading in the file
)

This is guaranteed to trigger one job. A Job is triggered anytime we are “physically” required to touch the data. In some cases, one action may create multiple jobs (multiple reasons to touch the data). In this case, the reader has to “peek” at the first line of the file to determine how many columns of data we have.

We can see the structure of the DataFrame by executing the command printSchema()

tempDF.printSchema()

root
 |-- _c0: string (nullable = true)
 |-- _c1: string (nullable = true)
 |-- _c2: string (nullable = true)

the column names _c0, _c1, and _c2 (automatically generated names)

display(tempDF)

Step #2 – Use the File’s Header

tempDF2 = (spark.read          # The DataFrameReader
   .option("sep", "\t")        # Use tab delimiter (default is comma-separator)
   .option("header", "true")   # Use first line of all files as header
   .csv(csvFile)               # Creates a DataFrame from CSV after reading in the file
)

tempDF2.printSchema()

root
 |-- ID: string (nullable = true)
 |-- Name: string (nullable = true)
 |-- Weight: string (nullable = true)

display(tempDF2)

Step #3 – Infer the Schema

Lastly, we can add an option that tells the reader to infer each column’s data type (aka the schema)

tempDF3 = (spark.read              # The DataFrameReader
   .option("header", "true")       # Use first line of all files as header
   .option("sep", "\t")            # Use tab delimiter (default is comma-separator)
   .option("inferSchema", "true")  # Automatically infer data types
   .csv(csvFile)                   # Creates a DataFrame from CSV after reading in the file
)

tempDF3.printSchema()

root
 |-- ID: integer (nullable = true)
 |-- Name: string (nullable = true)
 |-- Weight: integer (nullable = true)

Two Spark jobs were executed (not one as in the previous example)

3. Reading from CSV – User-Defined Schema

The difference here is that we are going to define the schema beforehand and hopefully avoid the execution of any extra jobs.

Step #1

Declare the schema. This is just a list of field names and data types.

# Required for StructField, StringType, IntegerType, etc.
from pyspark.sql.types import *

csvSchema = StructType([
  StructField("ID", IntegerType(), False),
  StructField("Name", StringType(), False),
  StructField("Weight", IntegerType(), False)
])

Step #2

Read in our data (and print the schema). We can specify the schema, or rather the StructType, with the schema(..) command:

tempDF4 = (spark.read         # The DataFrameReader
  .option('header', 'true')   # Ignore line #1 - it's a header
  .option('sep', "\t")        # Use tab delimiter (default is comma-separator)
  .schema(csvSchema)          # Use the specified schema
  .csv(csvFile)               # Creates a DataFrame from CSV after reading in the file
)

tempDF4.printSchema()

root
 |-- ID: integer (nullable = true)
 |-- Name: string (nullable = true)
 |-- Weight: integer (nullable = true)

Zero Spark jobs were executed

4. Reading from multiple CSV

• python – Read multiple files from Databricks DBFS – Stack Overflow

tempDF = (spark.read
   .option("header", "true")
   .csv("/mnt/training/*.csv")
)

tempDF = (spark.read
   .format("csv")
   .option("header", "true")
   .load("/mnt/training/*.csv")
)

• Azure Databricks documentation | Microsoft Docs
• Ten Simple Databricks Notebook Tips & Tricks for Data Scientists – The Databricks Blog
• Secret scopes – Azure Databricks | Microsoft Docs

Databricks on AWS

• Databricks documentation | Databricks on AWS
• Runs CLI | Databricks on AWS
• Databricks File System (DBFS) | Databricks on AWS
• Databricks for SQL developers | Databricks on AWS
• Databricks SQL | Databricks on AWS
• Databricks ODBC and JDBC drivers | Databricks on AWS
• Authentication using Databricks personal access tokens | Databricks on AWS
• Developer tools and guidance | Databricks on AWS
• DBeaver integration with Databricks | Databricks on AWS
• Databricks Utilities | Databricks on AWS
• Secrets | Databricks on AWS
• Databricks SQL Connector for Python | Databricks on AWS
• Databricks for Python developers | Databricks on AWS

DROP DATABASE

• DROP DATABASE | Databricks on AWS

While usage of SCHEMA and DATABASE is interchangeable, SCHEMA is preferred.

Related articles

• CREATE SCHEMA
• DESCRIBE SCHEMA
• DROP SCHEMA
• SHOW SCHEMAS

DROP VIEW (Databricks SQL)

• DROP VIEW (Databricks SQL) | Databricks on AWS

DROP VIEW [ IF EXISTS ] view_name

Related articles

• CREATE VIEW
• ALTER VIEW
• SHOW VIEWS
• CREATE SCHEMA
• DROP SCHEMA

Other

ดูเวอร์ชันของ Databricks Runtime

• Checking the version of Databricks Runtime in Azure – Stack Overflow

%scala
dbutils.notebook.getContext.tags("sparkVersion")
// res1: String = 10.4.x-scala2.12

%python
spark.conf.get("spark.databricks.clusterUsageTags.sparkVersion")
# '10.4.x-scala2.12'

• Databricks Utilities – Azure Databricks | Microsoft Docs

1. List available utilities
2. Data utility (dbutils.data)
3. File system utility (dbutils.fs)
4. Library utility (dbutils.library)
5. Notebook utility (dbutils.notebook)
6. Secrets utility (dbutils.secrets)
7. Widgets utility (dbutils.widgets)
8. Databricks Utilities API library
9. Limitations

Databricks Utilities – dbutils

• You can access the DBFS through the Databricks Utilities class (and other file IO routines).
• An instance of DBUtils is already declared for us as dbutils.
• For in-notebook documentation on DBUtils you can execute the command dbutils.help().

1. List available utilities

dbutils.help()

List available commands for a utility

dbutils.help?

dbutils.fs.help()

Display help for a command

dbutils.fs.help('cp')

2. Data utility (dbutils.data)

Commands: summarize

dbutils.data.help()

summarize command (dbutils.data.summarize)

Calculates and displays summary statistics of an Apache Spark DataFrame or pandas DataFrame. This command is available for Python, Scala and R.

df = spark.read.format('csv').load(
  '/databricks-datasets/Rdatasets/data-001/csv/ggplot2/diamonds.csv',
  header=True,
  inferSchema=True
)
dbutils.data.summarize(df)

Note that the visualization uses SI notation to concisely render numerical values smaller than 0.01 or larger than 10000.

3. File system utility (dbutils.fs)

Commands: cp, head, ls, mkdirs, mount, mounts, mv, put, refreshMounts, rm, unmount, updateMount

cp command (dbutils.fs.cp)

Copies a file or directory, possibly across filesystems.

dbutils.fs.cp("/FileStore/old_file.txt", "/tmp/new/new_file.txt")

head command (dbutils.fs.head)

Returns up to the specified maximum number bytes of the given file. The bytes are returned as a UTF-8 encoded string.

dbutils.fs.head("/tmp/my_file.txt", 25)

ls command (dbutils.fs.ls)

Lists the contents of a directory.

dbutils.fs.ls("/tmp")

files = dbutils.fs.ls("/mnt/training/")

for fileInfo in files:
  print(fileInfo.path)

print("-"*80)

display(..)

Besides printing each item returned from dbutils.fs.ls(..) we can also pass that collection to another Databricks specific command called display(..).

files = dbutils.fs.ls("/mnt/training/")

display(files)

mkdirs command (dbutils.fs.mkdirs)

Creates the given directory if it does not exist. Also creates any necessary parent directories.

dbutils.fs.mkdirs("/tmp/parent/child/grandchild")

mount command (dbutils.fs.mount)

Mounts the specified source directory into DBFS at the specified mount point.

dbutils.fs.mount(
  source = "wasbs://<container-name>@<storage-account-name>.blob.core.windows.net",
  mount_point = "/mnt/<mount-name>",
  extra_configs = {"<conf-key>":dbutils.secrets.get(scope = "<scope-name>", key = "<key-name>")})

mounts command (dbutils.fs.mounts)

Displays information about what is currently mounted within DBFS.

dbutils.fs.mounts()

mounts = dbutils.fs.mounts()

for mount in mounts:
  print(mount.mountPoint + " >> " + mount.source)

print("-"*80)

mv command (dbutils.fs.mv)

Moves a file or directory, possibly across filesystems. A move is a copy followed by a delete, even for moves within filesystems.

dbutils.fs.mv("/FileStore/my_file.txt", "/tmp/parent/child/grandchild")

put command (dbutils.fs.put)

Writes the specified string to a file. The string is UTF-8 encoded.

dbutils.fs.put("/tmp/hello_db.txt", "Hello, Databricks!", True)

refreshMounts command (dbutils.fs.refreshMounts)

Forces all machines in the cluster to refresh their mount cache, ensuring they receive the most recent information.

dbutils.fs.refreshMounts()

rm command (dbutils.fs.rm)

Removes a file or directory.

dbutils.fs.rm("/tmp/hello_db.txt")

unmount command (dbutils.fs.unmount)

Deletes a DBFS mount point.

dbutils.fs.unmount("/mnt/<mount-name>")

updateMount command (dbutils.fs.updateMount)

Similar to the dbutils.fs.mount command, but updates an existing mount point instead of creating a new one. Returns an error if the mount point is not present.

dbutils.fs.updateMount(
  source = "wasbs://<container-name>@<storage-account-name>.blob.core.windows.net",
  mount_point = "/mnt/<mount-name>",
  extra_configs = {"<conf-key>":dbutils.secrets.get(scope = "<scope-name>", key = "<key-name>")})

4. Library utility (dbutils.library)

The library utility is deprecated.

Utilities for session isolated libraries

Commands: install, installPyPI, list, restartPython, updateCondaEnv

5. Notebook utility (dbutils.notebook)

Commands: exit, run

The notebook utility allows you to chain together notebooks and act on their results. See Notebook workflows.

dbutils.notebook.help()

6. Secrets utility (dbutils.secrets)

Commands: get, getBytes, list, listScopes

The secrets utility allows you to store and access sensitive credential information without making them visible in notebooks. See Secret management and Use the secrets in a notebook. To list the available commands, run dbutils.secrets.help().

dbutils.secrets.help()

7. Widgets utility (dbutils.widgets)

Commands: combobox, dropdown, get, getArgument, multiselect, remove, removeAll, text

The widgets utility allows you to parameterize notebooks. See Widgets. To list the available commands, run dbutils.widgets.help().

combobox command (dbutils.widgets.combobox)

Creates and displays a combobox widget with the specified programmatic name, default value, choices, and optional label.

dbutils.widgets.combobox(
  name='fruits_combobox',
  defaultValue='banana',
  choices=['apple', 'banana', 'coconut', 'dragon fruit'],
  label='Fruits'
)

print(dbutils.widgets.get("fruits_combobox"))

# banana

dropdown command (dbutils.widgets.dropdown)

Creates and displays a dropdown widget with the specified programmatic name, default value, choices, and optional label.

dbutils.widgets.dropdown(
  name='toys_dropdown',
  defaultValue='basketball',
  choices=['alphabet blocks', 'basketball', 'cape', 'doll'],
  label='Toys'
)

print(dbutils.widgets.get("toys_dropdown"))

# basketball

get command (dbutils.widgets.get)

Gets the current value of the widget with the specified programmatic name. This programmatic name can be either:

dbutils.widgets.get('fruits_combobox')

# banana

getArgument command (dbutils.widgets.getArgument)

Gets the current value of the widget with the specified programmatic name. If the widget does not exist, an optional message can be returned.

This command is deprecated. Use dbutils.widgets.get instead.

dbutils.widgets.getArgument('fruits_combobox', 'Error: Cannot find fruits combobox')

# Deprecation warning: Use dbutils.widgets.text() or dbutils.widgets.dropdown() to create a widget and dbutils.widgets.get() to get its bound value.
# Out[3]: 'banana'

multiselect command (dbutils.widgets.multiselect)

Creates and displays a multiselect widget with the specified programmatic name, default value, choices, and optional label.

dbutils.widgets.multiselect(
  name='days_multiselect',
  defaultValue='Tuesday',
  choices=['Monday', 'Tuesday', 'Wednesday', 'Thursday',
    'Friday', 'Saturday', 'Sunday'],
  label='Days of the Week'
)

print(dbutils.widgets.get("days_multiselect"))

# Tuesday

remove command (dbutils.widgets.remove)

Removes the widget with the specified programmatic name.

If you add a command to remove a widget, you cannot add a subsequent command to create a widget in the same cell. You must create the widget in another cell.

dbutils.widgets.remove('fruits_combobox')

removeAll command (dbutils.widgets.removeAll)

Removes all widgets from the notebook.

If you add a command to remove all widgets, you cannot add a subsequent command to create any widgets in the same cell. You must create the widgets in another cell.

dbutils.widgets.removeAll()

text command (dbutils.widgets.text)

Creates and displays a text widget with the specified programmatic name, default value, and optional label.

dbutils.widgets.text(
  name='your_name_text',
  defaultValue='Enter your name',
  label='Your name'
)

print(dbutils.widgets.get("your_name_text"))

# Enter your name

8. Databricks Utilities API library

To accelerate application development, it can be helpful to compile, build, and test applications before you deploy them as production jobs. To enable you to compile against Databricks Utilities, Databricks provides the dbutils-api library. 

9. Limitations

Calling dbutils inside of executors can produce unexpected results or potentially result in errors.

If you need to run file system operations on executors using dbutils, there are several faster and more scalable alternatives available:

• For file copy or move operations, you can check a faster option of running filesystem operations described in Parallelize filesystem operations.
• For file system list and delete operations, you can refer to parallel listing and delete methods utilizing Spark in How to list and delete files faster in Databricks.

For information about executors, see Cluster Mode Overview on the Apache Spark website.