Create a Delta Table

INPUT_PATH = '/databricks-datasets/nyctaxi/taxizone/taxi_rate_code.csv'
DELTALAKE_PATH = 'dbfs:/mnt/datalake/book/ch3/createDeltaTableWithDataFrameWrite'

# Read the Dataframe from the input path
df_rate_codes = spark.read.format("csv").option("inferSchema", True).option("header", True).load(INPUT_PATH)

# Save our DataFrame as a managed Hive table
df_rate_codes.write.format("delta").saveAsTable('taxidb.rate')

Read from a Delta Table

%sql
SELECT COUNT(*) FROM taxidb.rate

Read a Table with PySpark

df = spark.read.format("delta").table("taxidb.YellowTaxis")
print(f"Number of records: {df.count():,}")

# Perform the GROUP BY, average (AVG), HAVING and order by equivalents in pySpark
results = df.groupBy("CabNumber").agg(avg("FareAmount").alias("AverageFare")).filter(col("AverageFare") > 50).sort(col("AverageFare")).desc()).take(5)

Write to a Delta Table

%sql
INSERT INTO taxidb.yellowtaxis
(Rideid, VendorId, PickupTime, DropTime,
 PickupLocationId, DropLocationId, CabNumber,
 DriverLicenseNumber, PassengerCount, TripDistance,
 RatecodeId, PaymentType, TotalAmount,
 FareAmount, Extra, MtaTax, TipAmount,
 TollsAmount, ImprovementSurcharge)
 VALUES(9999995, 1, '2019-11-01T00:00:00.000Z',
            '2019-11-01T00:02:23.573Z', 65, 71, 'TAC304',
            '453987', 2, 4.5, 1, 1, 20.34, 15.0, 0.5,
            0.4, 2.0, 2.0, 1.1)

# materialize the table
embeddings_table_name = "crimedata_incident.crimedata_incident_narratives_embeddings"
df_emd.write.mode("overwrite").saveAsTable(embeddings_table_name)

Delta Table History

%sql
DESCRIBE HISTORY taxidb.YellowTaxis

Transaction Log

$sh
 ls /dbfs/mnt/datalake/book/chapter04/YellowTaxisDelta/_delta_log/*.json
 
 %sh
    grep \"add\" /dbfs/.../chapter04/YellowTaxisDelta/_delta_log/..0000.json |
      sed -n 1p > /tmp/commit.json
    python -m json.tool < /tmp/commit.json

Load Previous Version of Delta Table

Delta Lake time travel allows you to also perform read operations on a table’s previous versions without needing to restore them.

Remember, the data itself is not physically deleted from the underlying storage, it is just logically removed. Logical removal rather than physical deletion means that time travel not only allows you to restore a table to a specific point in time, but you can easily query previous versions of a table directly, without restoring.