Syntax

Question 1

Search for database

Answer 1

show databases like ‘*name*’

Question 2

Search for table

Answer 2

show tables in attribution like ‘*tablename*’

Question 3

Query files under table

Answer 3

select distinct input_file_name() from database.tablename

Question 4

Query table metadata (2 types)

Answer 4

describe table [extended] database.tablename

describe detail database.tablename

Question 5

Get table history

Answer 5

describe history database.tablename

Question 6

Get Point in time for table

Answer 6

select * from database.table timestamp as of ‘2023-04-12T13:43:30’

Question 7

Search file system

Answer 7

dbutils.fs.ls(“/”)

Question 8

Return X number of records in sql query

Answer 8

Select * from database.tablename Limit X

Question 9

Use data frame to build temporary view

Answer 9

df.createOrReplaceTempView(“database.viewname”

Question 10

Move file data into dataframe

Answer 10

df = (
spark.read
.format(“csv”)
.option(“delimter”,”,”)
.option(“header”,”true”)
.load(“/mnt/folder”)
)

Question 11

List secrets in scope

Answer 11

dbutils.secrets.list(“scopename”)

Question 12

View all scopes

Answer 12

dbutils.secrets.listscopes()

Question 13

Filter rows in data frame

Answer 13

df = df.filter(
(df.city == “Ki”) & (df.province == “Zu”)
)

Question 14

Execute notebook with widgets

Answer 14

dbutils.notebook.run( path=”/Users/”,
timeout_seconds=12,
{
“mountPoint”:”/mnt/”
}
)

Question 15

Activate intellisense

Answer 15

Tab

Question 16

Run cell

Answer 16

Ctr + Enter

Question 17

Run cell & create new cell

Answer 17

Shift + enter

Question 18

Create parameter

Answer 18

dbutils.widgets.text(
name=”mountPoint”,
defaultValue=””
)

Question 19

Use parameter

Answer 19

dbutils.widgets.get(
“mountPoint”
)

Question 20

Create Generated Surrogate key on delta table

Answer 20

personId as bigint GENERATED ALWAYS AS IDENTITY
(
START WITH 0
AND
INCREMENT BY 1
)

Question 21

3 Ways to create unique key in databricks

Answer 21

1) Monotonically_increasing_id()
2) Window function
3) Generated Identity column

Question 22

Set up Database

Answer 22

CREATE DATABASE database_name LOCATION (‘/mnt/gold/’)

Question 23

Create Parquet File

Answer 23

df.write.format(“parquet”).mode(“append”).save(“/mnt/table_name/)

Question 24

Delete table data from file storage

Answer 24

Dbutils.fs.rm(“/mnt/db/tablename”,True)

Question 25

Create Parquet file and hive table

Answer 25

Df.write.format(“parquet”).option(“path”,”/mnt/database/table_name”).saveAsTable(“database.filename”)

Question 26

sql function to load every file only once into table

Answer 26

Copy into database.table
From “/mnt/database/tablename/”
Fileformat = csv

Question 27

Explain checkpointing

Answer 27

Changes 1-9 generate a separate json file in delta log. When Json file 10 is written a checkpoint parquet file is also written

Question 28

Create Data Frame from List with metadata and data

Answer 28

df = createDataFrame(<list>).Collect()</list>

Question 29

Loop through data frame

Answer 29

for row in df:
print(row[“<column_name>"])</column_name>