Unified Tables from CSV, Parquet and Delta Sources

1.Reading raw files with read_files

Unified datasets often start with sources in different formats: a CSV export from a vendor, Parquet files from another system, and Delta tables already in Unity Catalog. To query the files from SQL, Databricks recommends the read_files table-valued function. It's available in Databricks SQL and Databricks Runtime 13.3 LTS and above. It reads files under a path and returns them as a table, so you can use it anywhere a table can go in a query.

-- Reads the headerless CSV files in the given path with the provided schema.
> SELECT * FROM read_files(
    's3://bucket/path',
    format => 'csv',
    schema => 'id int, ts timestamp, event string');

read_files supports JSON, CSV, XML, TEXT, BINARYFILE, PARQUET, AVRO and ORC. It can detect the format and infer one schema across all the files it reads. Options are passed as named parameters (format => 'csv'). The path can contain globs, such as 's3://bucket/path/*.csv', to pick out particular files. read_files also exposes a hidden _metadata column with fields such as file_path and file_name. You have to select it explicitly, because SELECT * doesn't include it. Access is governed too: read_files reads from Unity Catalog external locations or volumes, and you need READ FILES on the external location or READ VOLUME on the volume.

Sources12

2.Turning a query into a table

CREATE TABLE [USING] is the syntax to use when the new table is based on a column list you provide, derived from data at an existing storage location, or derived from a query. In the last case, CREATE TABLE ... AS SELECT (CTAS) builds the table from the query result. If you leave out USING, the table defaults to DELTA.

-- Creates a Delta table and stores the source file path as part of the data
> CREATE TABLE my_avro_data
  AS SELECT *, _metadata.file_path
  FROM read_files('gs://my-bucket/avroData')

Whether a CTAS table is managed or external depends on one clause. Without a LOCATION, the result is a managed table: Databricks lets you create managed tables from query results. Add a LOCATION under an external location and the same query writes an external table to the path you chose.

CREATE TABLE <catalog>.<schema>.<table-name>
 ?  's3://<bucket-path>/<table-directory>'
AS SELECT * FROM <source-table>;

Sources34

3.Joining CSV, Parquet and Delta into one dataset

Now the pieces fit together. In a query, read_files output and a Delta table are both just tables, so you can join them with ordinary SQL. Databricks supports ANSI-standard join syntax, including inner, outer, semi, anti and cross joins. A join in a one-off CTAS is a batch join, which is stateless: the result reflects the source data at the moment the query runs. Wrap the join in CREATE TABLE ... AS SELECT and you save that result as one governed table. With no LOCATION it's a managed Delta table. With a LOCATION it's an external table.

There's another way to build the same dataset. Register the CSV and Parquet files as external tables first (CSV and PARQUET are both allowed for external tables), then join all three by their catalog.schema.table names. That's useful when other tools need to query the raw files as tables too. If the goal is a single production dataset, the joined result is a better fit for a managed table, which Databricks recommends for production workloads and frequently queried data.

Sources5

Exam traps

Each one states something that sounds right. Open it to see what is actually true.

1. 1.A table created with CTAS from CSV or Avro files keeps the source file format.Why is that wrong?

   read_files only parses the source files. Unless USING says otherwise, the new table defaults to Delta.

   Covered in Turning a query into a table

2. 2.CSV or Parquet data can be stored as-is in a Unity Catalog managed table.Why is that wrong?

   Managed tables use only Delta or Apache Iceberg. To keep data in CSV or Parquet you need an external table. Otherwise, convert the data by writing it into a managed Delta table.

   Covered in Joining CSV, Parquet and Delta into one dataset

Practise it for real

Load CSV files with read_files and save them as a managed Delta table that keeps the source file path

1. 1.Run SELECT * FROM read_files('<your-path>', format => 'csv') against a folder of CSV files under an external location or volume you can read.

   Why: This checks that you have READ FILES (or READ VOLUME) and shows the schema read_files infers.

   You should see: Rows from the CSV files, with column names taken from the header.

2. 2.Run the same query with _metadata.file_path added to the select list.

   Why: _metadata is hidden from SELECT * and has to be selected explicitly.

   You should see: An extra column showing which file each row came from.

3. 3.Wrap the query as CREATE TABLE <catalog>.<schema>.<table> AS SELECT *, _metadata.file_path FROM read_files(...), with no LOCATION.

   Why: A CTAS with no LOCATION and no USING creates a managed Delta table.

   You should see: A new table in Catalog Explorer, shown as a managed table in Delta format.

4. 4.Drop the table, then run UNDROP TABLE on the same name.

   Why: Dropped managed tables can be recovered during the recovery period.

   You should see: The table comes back.