Best practices for working with external locations where many files arrive constantly

[pernilak]

I have an Azure Function that receives files (not volumes) and dumps them to cloud storage. One-five files are received approx. per second. I want to create a partitioned table in Databricks to work with. How should I do this? E.g.: register the container as an external location and create a bundle that creates a table and continuously trigger on arrival of new files and adds this data into databricks? What would such code look like - or are there something else I should do. I need something that runs continuously. (It is not an option to move the logic from the Azure Function into Databricks). Should an external or managed table be created?

I also have a similar case, with a lot less data - so partitioning is not required. Should then a managed table, external table or a view be created? What are the pros/cones for each in this case.

I would be very happy if someone could provide code - especially if that code works in a continuous job in Databricks (through bundles).

[Kaniz_Fatma]

Hi @pernilak, 

• Since you’re dealing with a high volume of files arriving approximately every second, creating a partitioned table is a good idea. Partitioning helps optimize query performance and manage large datasets efficiently.

• Here’s how you can achieve this in Azure Databricks:

-- Create an external table pointing to your cloud storage (e.g., Azure Blob Storage)
CREATE TABLE my_external_table
USING PARQUET
LOCATION 'abfss://<container-name>@<storage-account-name>.dfs.core.windows.net/<path-to-files>';

-- Define the partition columns (e.g., date, hour, etc.)
ALTER TABLE my_external_table
ADD PARTITION (date STRING, hour STRING);

-- Continuously ingest new files into the table
-- You can use Databricks jobs or notebooks to periodically run the following command:
MSCK REPAIR TABLE my_external_table;

• • If you have less data and partitioning isn’t necessary, consider using a managed table or a view.

  • Here’s how you can create each:

• Managed Table:

-- Create a managed table (data will be stored in Databricks)
CREATE TABLE my_managed_table
(file_name STRING, content STRING)
USING PARQUET;