topic Re: how to load structured stream data into delta table whose location is in ADLS Gen2 in Data Engineering

how to load structured stream data into delta table whose location is in ADLS Gen2

Harun — Wed, 15 Mar 2023 15:55:28 GMT

Hi All,

I am working on a streaming data processing. As a intial step i have read the data from azure eventhub using readstream. now i want to writestream this into a delta table.

My requirement is, The data should present in external location (adls gen2) and the table should be available in my metastore.

When i tried the below code

Code_Snippet:

ext_table_location = "adls path"

autoloader_df.writeStream \

.format("delta") \

.outputMode("append") \

.option("checkpointLocation", checkpoint_directory) \

.option("mergeSchema", "true") \

.option("path",ext_table_location) \

.table(ext_table_location)

It is failing. Is there any standard approach for streaming data for this kind of scenario?

Thanks in Advance!

Re: how to load structured stream data into delta table whose location is in ADLS Gen2

Anonymous — Wed, 15 Mar 2023 16:29:52 GMT

There are a couple ways to connect to ADLS Gen2. Please refer to below doc. For instance, if you decide to go by service principal method, you need to add below storage account configurations details to the cluster or notebooks. Same goes for storage for SAS token and storage account keys.

service_credential = dbutils.secrets.get(scope="<scope>",key="<service-credential-key>")

spark.conf.set("fs.azure.account.auth.type.<storage-account>.dfs.core.windows.net", "OAuth")

spark.conf.set("fs.azure.account.oauth.provider.type.<storage-account>.dfs.core.windows.net", "org.apache.hadoop.fs.azurebfs.oauth2.ClientCredsTokenProvider")

spark.conf.set("fs.azure.account.oauth2.client.id.<storage-account>.dfs.core.windows.net", "<application-id>")

spark.conf.set("fs.azure.account.oauth2.client.secret.<storage-account>.dfs.core.windows.net", service_credential)

spark.conf.set("fs.azure.account.oauth2.client.endpoint.<storage-account>.dfs.core.windows.net", "https://login.microsoftonline.com/<directory-id>/oauth2/token")

https://learn.microsoft.com/en-us/azure/databricks/getting-started/connect-to-azure-storage

https://learn.microsoft.com/en-us/azure/databricks/storage/azure-storage

Re: how to load structured stream data into delta table whose location is in ADLS Gen2

Harun — Thu, 16 Mar 2023 03:24:46 GMT

Connection is fine, I need to know how to write the stream data to adls gen2 path and the same time have the delta table registered to the metastore as well.

Re: how to load structured stream data into delta table whose location is in ADLS Gen2

-werners- — Thu, 16 Mar 2023 08:08:43 GMT

can you try to use <database>.<tablename> for the .table option instead of a path?

Re: how to load structured stream data into delta table whose location is in ADLS Gen2

Harun — Thu, 16 Mar 2023 09:37:00 GMT

Hi @werners, i tried that option as well. But it will create a managed table. But i want a external table. So now i created a external table prior to the streaming part.

What i understand from the research i did is, we cannot write to external table in writestream query.

Re: how to load structured stream data into delta table whose location is in ADLS Gen2

-werners- — Thu, 16 Mar 2023 09:55:13 GMT

If you write in delta format to a path, and create an unmanaged table on that path, that should work.