Databricks SQL Tutorial: Create External Tables at Governed Locations

This tutorial guides you through creating external tables in Databricks SQL, specifically focusing on using governed locations. We’ll proceed with a series of scripts, each building upon the previous one to progressively develop your understanding.

Script 1: Initial Setup and Table Creation

This script creates a sample data source and an external table referencing it. It demonstrates the basic syntax for `CREATE EXTERNAL TABLE`.

CREATE EXTERNAL TABLE my_external_table (
    id INT,
    name STRING,
    value DOUBLE
)
FORMAT 'CSV'
LOCATION 'abfss://@/governed_location/my_data_directory';

Explanation:

• `CREATE EXTERNAL TABLE my_external_table`: Defines the external table named `my_external_table`.
• `(id INT, name STRING, value DOUBLE)`: Specifies the schema of the table, including the data types of each column.
• `FORMAT ‘CSV’`: Indicates that the data is stored in CSV format.
• `LOCATION ‘abfss://@/governed_location/my_data_directory’`: Specifies the location of the data files. Replace “ and “ with your actual values. The `governed_location` part is crucial for governance.

Note: Replace the placeholder values with your actual Azure Blob Storage container name and Databricks workspace DNS. The `governed_location` parameter ensures the data is stored within a secured and compliant environment.

To create sample data for this table, we’ll execute the following script:

INSERT INTO my_external_table (id, name, value) VALUES
(1, 'Alice', 10.5),
(2, 'Bob', 20.0),
(3, 'Charlie', 15.75);

Now, let’s validate the table creation and data insertion:

SELECT COUNT() FROM my_external_table;

3

Script 2: Adding a Partition Column

This script demonstrates how to create an external table with a partition column, improving query performance and manageability when querying the data based on the partition.

CREATE EXTERNAL TABLE my_partitioned_table (
    id INT,
    name STRING,
    value DOUBLE,
    partition_date DATE
)
PARTITIONED BY (partition_date DATE)
FORMAT 'CSV'
LOCATION 'abfss://@/governed_location/my_data_directory';

Explanation:

• `PARTITIONED BY (partition_date DATE)`: Adds a partition column named `partition_date` of type `DATE` to the table. This enables partitioning the data based on date.

After running this script, you’ll need to create data files partitioned by date. For demonstration, let’s assume you’ve created the following data files in the specified location, partitioned by date:

• `abfss://@/governed_location/my_data_directory/2023/01/01/my_data.csv`
• `abfss://@/governed_location/my_data_directory/2023/01/02/my_data.csv`

Now, let’s query the table to demonstrate the partition:

SELECT  FROM my_partitioned_table WHERE partition_date = '2023-01-01';

1

Script 3: Selecting a Specific Partition

This script demonstrates selecting data from a specific partition, showcasing the benefit of partitioning.

SELECT  FROM my_partitioned_table WHERE partition_date = '2023-01-02';

1

Explanation:

• This query filters the data to return only the rows where the `partition_date` is ‘2023-01-02’.

Finally, let’s execute a query to count the total number of rows in the table:

SELECT COUNT() FROM my_partitioned_table;

2