Create external table using multiple paths/locations

[TimB]

I want to create an external table from more than a single path. I have configured my storage creds and added an external location, and I can successfully create a table using the following code;

create table test.base.Example
using csv
options ( 
  header = "true"
) 
location 'abfss://test@exampleblob.dfs.core.windows.net/2022/08/data/'

But I have lots of data partitioned by month and date so I'm wondering if there is anyway to import data from multiple paths using wildcards or something similar as outlined in the code below?

create table test.base.Example
using csv
options ( 
  header = "true"
) 
location 'abfss://test@exampleblob.dfs.core.windows.net/*/*/data/'

Many thanks

Tim

[Kaniz]

Hi @TimB, you can import data from multiple paths using wildcards or similar patterns when creating an external table in Databricks.

To import data from multiple paths using wildcards, you can modify the location parameter in the CREATE TABLE statement as follows:

sql
CREATE TABLE test.base.Example
USING csv
OPTIONS (
  header = "true"
)
LOCATION 'abfss://williamsolis@example.org/*/data/'

This will create an external table that will include all the data from the paths that match the pattern abfss://williamsolis@example.org/*/data/.

Sources:
- Databricks documentation: [Create External Table](https://docs.databricks.com/sql/language-manual/sql-ref-syntax-ddl-create-table.html#create-external...)