1

Suppose you already used checkpoint to update the delta table(external table) with Autoloader. How can I find out its checkpoint location?

I tried running the code below, but it didn't work in my environment.

SELECT * FROM sys.tables WHERE name LIKE 'databricks_streaming_checkpoint%'

Jacek Laskowski
  • 72,696
  • 27
  • 242
  • 420
Saito Mieko
  • 11
  • 3

2 Answers2

1

If you use Delta Live Tables then checkpoints are stored under the storage location specified in the DLT settings. Each table gets a dedicated directory under <storage_location/checkpoints/<dlt_table_name>.

Alex Ott
  • 80,552
  • 8
  • 87
  • 132
  • thank u Alex. I wanted to know the checkpoint location by just pure code. for example, delta table file path. delta_path = ( spark.sql(f"DESC FORMATTED {schema_name.lower()}.{data_name.lower()}") .filter(col("col_name") == "Location") .select("data_type") ).collect()[0][0] – Saito Mieko Mar 12 '23 at 23:34
  • 2
    You can query DLT REST API by code to find the storage location. But outside of DLT, the same Delta table could be written by the multiple streams with multiple checkpoints so there is no 1:1 mapping between a table and checkpoint – Alex Ott Mar 13 '23 at 07:32
1

As @alex_ott has mentioned in the answer, the checkpoints of your Delta Live Tables are under Storage location in Destination of Pipeline settings.

Pipeline setting / Destination / Storage location

The Storage location can be set once and at the creation time of a DLT pipeline. In other words, it can be any location but once defined it cannot be changed ever. Regardless whether it was specified explicitly (you) or implicitly (by the system).

Listing of the files and directories under pipelines directory

enter image description here

enter image description here

Databricks CLI

You can use Delta Live Tables CLI to access the storage location.

(You could also use the Delta Live Tables REST API Endpoint directly)

$ databricks pipelines get --pipeline-id 960da65b-c9df-4cb9-9456-1005ffe103a9 | jq '.spec.storage'
"dbfs:/pipelines/960da65b-c9df-4cb9-9456-1005ffe103a9"
Jacek Laskowski
  • 72,696
  • 27
  • 242
  • 420