Syntax

Copy

1. from_json(jsonStr, schema [, options])

Arguments

• jsonStr: A STRING expression specifying a row of CSV data.
• schema: A STRING literal or invocation of schema_of_json function (Databricks SQL).
• options: An optional MAP<STRING,STRING> literal specifying directives.

Refer documentation for more details,

https://docs.microsoft.com/en-us/azure/databricks/sql/language-manual/functions/from_json

Answer is,

To compare all the rows between both the tables across all the columns using intersect will help us achieve that, an inner join is only going to check if the same column value exists across both the tables on a single column.

INTERSECT [ALL | DISTINCT]

• Returns the set of rows which are in both subqueries.
• If ALL is specified a row that appears multiple times in the subquery1 as well as in subquery will be returned multiple times.
• If DISTINCT is specified the result does not contain duplicate rows. This is the default.

The answer is department = dbutils.widget.get("department")

Refer to additional documentation here

https://docs.databricks.com/notebooks/widgets.html

The answer is readStream, writeStream, once = True.

spark.readStream

 .format("delta")

 .table("events_log")

 .groupBy("customerId")

 .count()

 .writeStream

 .format("delta")

 .outputMode("complete")

 .option("checkpointLocation", "/tmp/delta/eventsByCustomer/_checkpoints/")

 .trigger(once = True)

 .table("target_table")

The answer is trigger(processingTime = "15 Minutes")

Triggers:

• Unspecified
• This is the default. This is equivalent to using processingTime="500ms"
• Fixed interval micro-batches  .trigger(processingTime="2 minutes")
• The query will be executed in micro-batches and kicked off at the user-specified intervals
• One-time micro-batch .trigger(once=True)
• The query will execute a single micro-batch to process all the available data and then stop on its own
• One-time micro-batch.trigger .trigger(availableNow=True) -- New feature a better version of (once=True)
• 
  

Databricks supports trigger(availableNow=True) in Databricks Runtime 10.2 and above for Delta Lake and Auto Loader sources. This functionality combines the batch processing approach of trigger once with the ability to configure batch size, resulting in multiple parallelized batches that give greater control for right-sizing batches and the resultant files.

The answer is, Efficiently process new data incrementally from cloud object storage.

Please note: AUTO LOADER only works on data/files located in cloud object storage like S3 or Azure Blob Storage it does not have the ability to read other data sources, although AUTO LOADER is built on top of structured streaming it only supports files in the cloud object storage. If you want to use Apache Kafka then you can just use structured streaming.

Auto Loader and Cloud Storage Integration

Auto Loader supports a couple of ways to ingest data incrementally

1. Directory listing - List Directory and maintain the state in RocksDB, supports incremental file listing
2. File notification - Uses a trigger+queue to store the file notification which can be later used to retrieve the file, unlike Directory listing File notification can scale up to millions of files per day.

[OPTIONAL]

Auto Loader vs COPY INTO?

Auto Loader

Auto Loader incrementally and efficiently processes new data files as they arrive in cloud storage without any additional setup. Auto Loader provides a new Structured Streaming source called cloudFiles. Given an input directory path on the cloud file storage, the cloudFiles source automatically processes new files as they arrive, with the option of also processing existing files in that directory.

When to use Auto Loader instead of the COPY INTO?

• You want to load data from a file location that contains files in the order of millions or higher. Auto Loader can discover files more efficiently than the COPY INTO SQL command and can split file processing into multiple batches.
• You do not plan to load subsets of previously uploaded files. With Auto Loader, it can be more difficult to reprocess subsets of files. However, you can use the COPY INTO SQL command to reload subsets of files while an Auto Loader stream is simultaneously running.

Refer to more documentation here,

https://docs.microsoft.com/en-us/azure/databricks/ingestion/auto-loader

The default value of maxFilesPerTrigger is 1000 it can be increased to a much higher number but will require a much larger compute to process.

https://docs.databricks.com/ingestion/auto-loader/options.html