Databricks and abfss - Tables not picking up data

Question

Databricks and abfss - Tables not picking up data

John Aherne 156

We are in the process of changing our processes to use direct abfss connections to adls via service principal instead of mount points, as recommended by Databricks. We have run into a strange problem though.

We can read and write just fine with python. However, any partitioned tables created that point to the location of the data do not pick up anything. A query on the table returns zero rows.
Tried using refresh table, which has no effect.
Tried using msck repair table which returns an error about the key config to adls.

The tables do work with mount points, as we can use msck repair on those.

Any thoughts on what I might be missing?

HimanshuSinha 19,527 Reputation points Microsoft Employee Moderator

2022-09-20T03:25:58.28+00:00

Hello @John Aherne ,

Thanks for using Microsoft Q&A . Can you please the code ?
That will help us look into the issue .

Thanks
Himanshu
John Aherne 156 Reputation points

2022-09-26T19:36:03.123+00:00

Apologies for the delay. We set up the connection to storage using these instructions:
https://docs.databricks.com/data/data-sources/azure/azure-storage.html#access-azure-data-lake-storage-gen2-or-blob-storage-using-oauth-20-with-an-azure-service-principal

It's runs those configs at the start of the notebook.
After processing, we write out using something like this:
df.write.partitionBy("a", "b").mode("overwrite").option("partitionOverwriteMode", "dynamic").parquet(output_file_location)

The location is an abfss// url.

We have a partitioned external table that points to the location (again, abfss url). However a query on it returns zero rows.
If we try to run msck repair table <some table> we get a key error in the same notebook.

If we set up the table using a mount point, we can run the msck repair table, and the data appears.

NOTE
After some testing, we found that if the configs are set up on the cluster itself, we can run msck repair table against tables with abfss locations. The error only happens when the configs are run just within the notebook.

Putting the configs at the cluster level is not ideal, because we were planning to share job clusters between projects, and having the configs in the notebooks would give us some isolation.

Thanks for any tips you might have!
HimanshuSinha 19,527 Reputation points Microsoft Employee Moderator

2022-09-28T22:22:28.243+00:00

Hello @John Aherne ,

We have a partitioned external table that points to the location (again, abfss url). However a query on it returns zero rows.

I am assuming that the extrenal table is pointing to output_file_location location , is that correct ? Can you please see if the external files created have any rows there ?

i am just trying to check if the issues is when the SPARK writes the files or its on the creation of EXTERNAL table ?

Thanks
Himanshu
John Aherne 156 Reputation points

2022-09-29T03:56:58.843+00:00

Yes, rows are created.
If I put the service principal configs in a notebook, it does not pick up anything in the partitions, and I get an fs key error if I try to run msck repair table.
If I put the service principal configs on the cluster, it still does not pick up the partitions, but I can run msck repair table on it and it works. Querying the table returns the rows.

I have no problem with running msck repair table, but I would rather have the credentials in the notebook so I can run the code on jobs clusters/pools.
I don't know how to add those configs for the pools because there are no options like there are for an interactive cluster.
HimanshuSinha 19,527 Reputation points Microsoft Employee Moderator

2022-10-11T19:05:35.473+00:00

Hello @John Aherne ,
Thanks for the patience , unfortunately we are not getting the kind of response from the team here . if you have a support plan you may file a support ticket, else could you please send an email to azcommunity@microsoft.com with the below details, so that we can create a one-time-free support ticket for you to work closely on this matter.
Subscription ID:
Subject : Attn Himanshu
Please let me know once you have done the same.
Thanks
Himanshu

1 answer

Your answer

HimanshuSinha 19,527 Reputation points Microsoft Employee Moderator

2022-09-20T03:25:58.28+00:00

Hello @John Aherne ,

Thanks for using Microsoft Q&A . Can you please the code ?
That will help us look into the issue .

Thanks
Himanshu
John Aherne 156 Reputation points

2022-09-26T19:36:03.123+00:00

Apologies for the delay. We set up the connection to storage using these instructions:
https://docs.databricks.com/data/data-sources/azure/azure-storage.html#access-azure-data-lake-storage-gen2-or-blob-storage-using-oauth-20-with-an-azure-service-principal

It's runs those configs at the start of the notebook.
After processing, we write out using something like this:
df.write.partitionBy("a", "b").mode("overwrite").option("partitionOverwriteMode", "dynamic").parquet(output_file_location)

The location is an abfss// url.

We have a partitioned external table that points to the location (again, abfss url). However a query on it returns zero rows.
If we try to run msck repair table <some table> we get a key error in the same notebook.

If we set up the table using a mount point, we can run the msck repair table, and the data appears.

NOTE
After some testing, we found that if the configs are set up on the cluster itself, we can run msck repair table against tables with abfss locations. The error only happens when the configs are run just within the notebook.

Putting the configs at the cluster level is not ideal, because we were planning to share job clusters between projects, and having the configs in the notebooks would give us some isolation.

Thanks for any tips you might have!
HimanshuSinha 19,527 Reputation points Microsoft Employee Moderator

2022-09-28T22:22:28.243+00:00

Hello @John Aherne ,

We have a partitioned external table that points to the location (again, abfss url). However a query on it returns zero rows.

I am assuming that the extrenal table is pointing to output_file_location location , is that correct ? Can you please see if the external files created have any rows there ?

i am just trying to check if the issues is when the SPARK writes the files or its on the creation of EXTERNAL table ?

Thanks
Himanshu
John Aherne 156 Reputation points

2022-09-29T03:56:58.843+00:00

Yes, rows are created.
If I put the service principal configs in a notebook, it does not pick up anything in the partitions, and I get an fs key error if I try to run msck repair table.
If I put the service principal configs on the cluster, it still does not pick up the partitions, but I can run msck repair table on it and it works. Querying the table returns the rows.

I have no problem with running msck repair table, but I would rather have the credentials in the notebook so I can run the code on jobs clusters/pools.
I don't know how to add those configs for the pools because there are no options like there are for an interactive cluster.
HimanshuSinha 19,527 Reputation points Microsoft Employee Moderator

2022-10-11T19:05:35.473+00:00

Hello @John Aherne ,
Thanks for the patience , unfortunately we are not getting the kind of response from the team here . if you have a support plan you may file a support ticket, else could you please send an email to azcommunity@microsoft.com with the below details, so that we can create a one-time-free support ticket for you to work closely on this matter.
Subscription ID:
Subject : Attn Himanshu
Please let me know once you have done the same.
Thanks
Himanshu

Answer 1

John Aherne 156

Just an update on this:

There is some sort of bug, but no eta on a fix.

However, as a workaround, you can set the cluster configuration on the linked service. you can even use the Databricks secret scope and secrets as if you were setting them on a cluster.

Since the storage configs are set on the cluster at startup, msck and other commands work.

Share via

Databricks and abfss - Tables not picking up data

1 answer

Your answer