Directory Tables
When dealing with a Data Lake, you often have millions of files spread across hundreds of S3 buckets or Azure Blob containers. Managing this unstructured data (like PDFs, Images, or Audio files) is difficult because you cannot query an image with SQL.
However, you can query the metadata of that image. This is where Directory Tables come in.
Topics Covered
- What is a Directory Table?
- Enabling a Directory Table on a Stage
- Querying File Metadata
- Auto-Refreshing
- Common Use Cases
1. What is a Directory Table?
A Directory Table is not a separate table you create with CREATE TABLE. It is an implicit, hidden table that exists underneath every Snowflake Stage.
When you query a Directory Table, you do not see the contents of the files. Instead, you see a catalog of the files: their names, sizes, MD5 hashes, and when they were last modified.
2. Hands-On: Enabling and Querying Directory Tables
Let's assume a healthcare company in Chennai stores millions of patient X-Ray images (JPEGs) and doctor notes (PDFs) in an AWS S3 bucket.
Step 1: Enable Directory Tables on the Stage
By default, Directory Tables are disabled to save compute resources. You must explicitly enable them when creating or altering the stage.
(To alter a stage, ensure you have the correct privileges. Verify your role with SELECT CURRENT_ROLE();)
USE ROLE SYSADMIN;
CREATE OR REPLACE STAGE BEEMS_MEDICAL_RECORDS_STAGE
URL='s3://beems-healthcare-lake/records/'
STORAGE_INTEGRATION = AWS_S3_INT
DIRECTORY = (ENABLE = TRUE AUTO_REFRESH = TRUE); -- Enable the Directory Table
Because AUTO_REFRESH = TRUE, whenever a new X-Ray is uploaded to S3, AWS SQS will automatically notify Snowflake to update the Directory Table catalog.
Step 2: Querying the Directory Table
You query the Directory Table using a special syntax: DIRECTORY(@STAGE_NAME).
-- Query the file inventory
SELECT *
FROM DIRECTORY(@BEEMS_MEDICAL_RECORDS_STAGE);
Output Columns:
RELATIVE_PATH: e.g.,xrays/patient_101_chest.jpgSIZE: The size of the file in bytes.LAST_MODIFIED: When the file was uploaded to S3.MD5: The cryptographic hash of the file (used to detect if a file was altered).FILE_URL: A Snowflake-generated URL that authorized users can click to download the file.
Step 3: Analytics on Unstructured Data
Because the Directory Table acts like a normal SQL table, you can run analytical queries on your unstructured data!
-- How much storage is being used by X-Rays vs PDFs?
SELECT
CASE
WHEN RELATIVE_PATH LIKE '%.jpg' THEN 'Image'
WHEN RELATIVE_PATH LIKE '%.pdf' THEN 'Document'
ELSE 'Other'
END AS FILE_TYPE,
COUNT(*) AS TOTAL_FILES,
SUM(SIZE) / 1024 / 1024 / 1024 AS TOTAL_GIGABYTES
FROM DIRECTORY(@BEEMS_MEDICAL_RECORDS_STAGE)
GROUP BY 1;
3. Pre-Signed URLs
If an authorized doctor using a custom web application needs to view patient_101_chest.jpg, they cannot access the S3 bucket directly because it is locked down.
You can use the Directory Table to generate a secure, temporary Pre-Signed URL.
SELECT
RELATIVE_PATH,
GET_PRESIGNED_URL(@BEEMS_MEDICAL_RECORDS_STAGE, RELATIVE_PATH, 3600) AS SECURE_URL
FROM DIRECTORY(@BEEMS_MEDICAL_RECORDS_STAGE)
WHERE RELATIVE_PATH LIKE '%patient_101%';
The GET_PRESIGNED_URL function generates a link that expires in 3600 seconds (1 hour). The application can safely display this image to the doctor without exposing AWS credentials.
4. Directory Tables vs LIST command
You can also view files in a stage by running the LIST @STAGE command. So why use a Directory Table?
LISTis a command, not a query. You cannot useWHERE,GROUP BY, orJOINon the output ofLIST.DIRECTORY()is a table function. You can join it to your relational data. For example, you could join the Directory Table to yourPATIENTStable to find all patients who are missing an X-Ray.
Summary
- A Directory Table provides a SQL-queryable catalog of unstructured files residing in a stage.
- It must be explicitly enabled using
DIRECTORY = (ENABLE = TRUE). - It returns file metadata (
RELATIVE_PATH,SIZE,MD5), not the file contents. - Use
GET_PRESIGNED_URL()to securely serve these files to external applications. - Unlike the
LISTcommand, Directory Tables can be used in complexSELECTqueries with joins and aggregations.