Chapter 3: Basic Operations on Delta Tables

Creating a Delta Table

• A Delta table can be created using standard SQL DDL commands in Spark SQL.
• The notation for creating a Delta table is CREATE TABLE ... USING DELTA LOCATION '/path/to/table'.
• This notation can be tedious to use, especially when working with long filepaths.

Using Catalogs

• Catalogs allow you to register a table with a database and table name notation, making it easier to refer to the table.
• Creating a database and table using a catalog notation simplifies the process of creating a Delta table.
• For example, creating a database named taxtdb and a table named rateCard using the catalog notation: CREATE TABLE taxtdb.rateCard (...) USING DELTA LOCATION '/mnt/datalake/book/chapter03/rateCard'.

Metadata and Schema

• Delta Lake writes the schema of the table to the transaction log entry, along with auditing and partitioning information.
• The schema is stored in JSON format and can be accessed using the grep command and python -m json.tool.

DESCRIBE Statement

• The SQL DESCRIBE command can be used to return the basic metadata for a Parquet file or Delta table.
• Dropping an existing table and recreating it with the DESCRIBE statement can be used to verify that the data has been loaded correctly.

Creating a Delta Table with the DeltaTableBuilder API

• The DeltaTableBuilder API offers fine-grained control when creating a Delta table.
• It allows users to specify additional information such as column comments, table properties, and generated columns.
• The API is designed to work with Delta tables and offers more control than the traditional DataFrameWriter API.

Partitioning and Files

• Partitioning a Delta table can lead to the creation of multiple files, which can lead to the "small file problem".
• The number of files created is the product of the cardinality of both columns, which can lead to a large number of small Parquet part files.
• Alternative solutions, such as Z-ordering, can be more effective than partitioning for certain use cases.

Checking if a Partition Exists

• To check if a partition exists in a table, use the statement SELECT COUNT(*) > 0 FROM ... WHERE ....
• If the partition exists, the statement returns true.

Creating a Delta Table

• A Delta table can be created using standard SQL DDL commands in Spark SQL.
• The notation for creating a Delta table is CREATE TABLE...USING DELTA LOCATION '/path/to/table'.
• Catalogs can be used to register a table with a database and table name notation, making it easier to refer to the table.

Metadata and Schema

• Delta Lake writes the schema of the table to the transaction log entry.
• The schema is stored in JSON format and can be accessed using the grep command and python -m json.tool.
• Auditing and partitioning information are also stored in the transaction log entry.

DESCRIBE Statement

• The SQL DESCRIBE command can be used to return the basic metadata for a Parquet file or Delta table.
• The DESCRIBE statement can be used to verify that the data has been loaded correctly by dropping an existing table and recreating it.

DeltaTableBuilder API

• The DeltaTableBuilder API offers fine-grained control when creating a Delta table.
• The API allows users to specify additional information such as column comments, table properties, and generated columns.
• The DeltaTableBuilder API is designed to work with Delta tables and offers more control than the traditional DataFrameWriter API.

Partitioning and Files

• Partitioning a Delta table can lead to the creation of multiple files, which can lead to the "small file problem".
• The number of files created is the product of the cardinality of both columns.
• Alternative solutions, such as Z-ordering, can be more effective than partitioning for certain use cases.

Checking if a Partition Exists

• To check if a partition exists in a table, use the statement SELECT COUNT(*) > 0 FROM...WHERE....
• If the partition exists, the statement returns true.