Registering pre-existing DeltaLake data as a table in watsonx.data catalog

Importing Delta Lake tables into Metadata Service using a single API

(This blog is written jointly with my colleague Althaf Salim)

Overview

Did you know that you can import / register a Delta Lake data into WXD using the Register Delta Table API? Well, if you didn’t, here’s how you can go about it!

There may be use cases where you have data or table that is already created using some external systems (say using pyspark) into some storage (S3, IBM COS, ADLS, GCS) and you want to construct a catalogued table out of it that allows you to run SQL queries against it. And you want that table showing up in watsonx.data so that you can query from presto or spark.

If this is the scenario, you can register the table in watsonx.data using a REST API instead of having the Spark overhead to create the tables. This API has been created along lines of Iceberg REST API — Register Table

Pre created Delta Format Table

So here I have Delta format table that I created previously and the location of the metadata folder is as below in the ADLS blob container.

abfss://pyspark@asdfadsf.dfs.core.windows.net/mrmadira_adls_skema/mrmadira_adls_tab/_delta_log

💡Side Note: While exploring for this blog — I discovered this handy ADLS Storage Explorer You can download the explorer and browse through the container contents after configuring it with your ADLS credentials. If you were not aware of this earlier, well, you are welcome :)

Register Storage and Create Catalog

This step is standard starting point for working with storage data in watsonx.data. You can add a storage of type ADLS and associate with catalog of type Delta Lake as shown below

Press enter or click to view image in full size

Register Delta Table API

Execute the API against your instance in cloud or in CPD / onprem Software. This example is against the SAAS instance.

Get Mrudula Madiraju’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

Authorization can be Basic or Bearer — your choice.
In this example we have used Basic : base64 encoding of <saas_Account_emailID: APIKey>

curl -X 'POST' 'https://us-south.lakehouse.cloud.ibm.com/lakehouse/api/v2/catalogs/unity_delta_catalog/schemas/mrmadira_adls_skema/register' 
-H "Content-Type: application/json" 
-H "AuthInstanceId: $crn" 
-H "Authorization: Basic $basicAuth"  
-d '{"metadata_location" : "abfss://asdfasdf@asdfadsf.dfs.core.windows.net/mrmadira_adls_skema/mrmadira_adls_tab/_delta_log","table_name":"mrmadira_adls_tab"}'

results in response :

results in : 
{"response":{"message":"Register table","message_code":"Success"}}

Query / Browse data from Query Workspace or Data Manager

You will see the data automatically as below.

How this works

• The table_name attribute from the payload is used to create the table in Metadata Services component of watsonx.data
• From the path parameter of the API, it picks the schema name from the string that comes after /schemas
• Note that schema with that name is created if it does not exist already
• Also note that the metadata_location path can be pointing to any location. It just so happens that the path is exactly matching to schema and table we are using here.
• In this example, we just happened to take delta tables that are created in ADLS container. You can also have delta tables in other storages like S3, GCS, IBM COS, this API would work just as well.

Load Table Metadata API

There is also a corresponding GET or LOAD Table Metadata API that gives you the location of the table.

curl 'https://us-south.lakehouse.cloud.ibm.com/lakehouse/api/v2/catalogs/unity_delta_catalog/schemas/mrmadira_adls_skema/tables/mrmadira_adls_tab/metadata'  
-H "Content-Type: application/json" 
-H "AuthInstanceId: $crn" 
-H "Authorization: Basic $basicAuth" | jq

results in

{
  "metadata_location": "abfss://asdf@asdf.dfs.core.windows.net/mrmadira_adls_skema/mrmadira_adls_tab/_delta_log",
  "table_path": "abfss://asdf@asdf.dfs.core.windows.net/mrmadira_adls_skema/mrmadira_adls_tab"
}

In this case the metadata_location seems be intuitive because the location happens to be aligned to schema and table — but as mentioned, it can be any random location on the storage.

In conclusion…

Hope this has been useful read! If you like it, show your appreciation by clapping on the blog and sharing with your colleagues! :)

At the time of this writing, we can only register one table at a time. Would you like a feature that can automatically discover all tables in a location and register it? We can do that — Reach out to us and let us know!

Apart from Delta, you can also get pre existing Iceberg and Hudi tables into watsonx.data. To know more, read: https://cloud.ibm.com/docs/watsonxdata?topic=watsonxdata-sync_log https://cloud.ibm.com/apidocs/watsonxdata#register-table