无法从azure blob存储容器将xlsx文件读取到pyspark数据框

Question

我正在尝试将数据从Azure存储容器加载到Azure Databricks中的Pysark数据帧。当我阅读txt或CSV文件时，它正在工作。但当我尝试读取.xlsx文件时，我会遇到以下问题。

Apache Spark 3.2.0，Scala 2.12

以下是我正在执行的步骤

spark.conf.set("fs.azure.account.key.teststorage.blob.core.windows.net",
"**********************")

它正在工作

df = spark.read.format("csv").option("header", "true") \
  .option("inferSchema", "true") \
  .load("wasbs://[e-mail]/data/samplefile.txt")

不工作

df = spark.read.format("com.crealytics.spark.excel") \
  .option("header", "true").option("inferSchema","true") \
  .load("wasbs://[e-mail]/data/samplefile.xlsx")

加载xlsx文件时出现以下错误:

: shaded.databricks.org.apache.hadoop.fs.azure.AzureException: shaded.databricks.org.apache.hadoop.fs.azure.AzureException: Container producer in account teststorage.blob.core.windows.net not found, and we can't create it using anoynomous credentials, and no credentials found for them in the configuration.
    at shaded.databricks.org.apache.hadoop.fs.azure.AzureNativeFileSystemStore.createAzureStorageSession(AzureNativeFileSystemStore.java:1063)
    at shaded.databricks.org.apache.hadoop.fs.azure.AzureNativeFileSystemStore.initialize(AzureNativeFileSystemStore.java:512)
at shaded.databricks.org.apache.hadoop.fs.azure.NativeAzureFileSystem.initialize(NativeAzureFileSystem.java:1384)
at org.apache.hadoop.fs.FileSystem.createFileSystem(FileSystem.java:3469)
at org.apache.hadoop.fs.FileSystem.get(FileSystem.java:537)
at com.crealytics.spark.excel.WorkbookReader$.readFromHadoop$1(WorkbookReader.scala:35)
at com.crealytics.spark.excel.WorkbookReader$.$anonfun$apply$2(WorkbookReader.scala:41)
at com.crealytics.spark.excel.DefaultWorkbookReader.$anonfun$openWorkbook$1(WorkbookReader.scala:49)
at scala.Option.fold(Option.scala:251)
at com.crealytics.spark.excel.DefaultWorkbookReader.openWorkbook(WorkbookReader.scala:49)
at com.crealytics.spark.excel.WorkbookReader.withWorkbook(WorkbookReader.scala:14)
at com.crealytics.spark.excel.WorkbookReader.withWorkbook$(WorkbookReader.scala:13)
at com.crealytics.spark.excel.DefaultWorkbookReader.withWorkbook(WorkbookReader.scala:45)
at com.crealytics.spark.excel.ExcelRelation.excerpt$lzycompute(ExcelRelation.scala:31)
at com.crealytics.spark.excel.ExcelRelation.excerpt(ExcelRelation.scala:31)
at com.crealytics.spark.excel.ExcelRelation.headerColumns$lzycompute(ExcelRelation.scala:102)
at com.crealytics.spark.excel.ExcelRelation.headerColumns(ExcelRelation.scala:101)
at com.crealytics.spark.excel.ExcelRelation.$anonfun$inferSchema$1(ExcelRelation.scala:163)
at scala.Option.getOrElse(Option.scala:189)
at com.crealytics.spark.excel.ExcelRelation.inferSchema(ExcelRelation.scala:162)
at com.crealytics.spark.excel.ExcelRelation.<init>(ExcelRelation.scala:35)
at com.crealytics.spark.excel.DefaultSource.createRelation(DefaultSource.scala:35)
at com.crealytics.spark.excel.DefaultSource.createRelation(DefaultSource.scala:13)
at com.crealytics.spark.excel.DefaultSource.createRelation(DefaultSource.scala:8)
at org.apache.spark.sql.execution.datasources.DataSource.resolveRelation(DataSource.scala:385)
at org.apache.spark.sql.DataFrameReader.loadV1Source(DataFrameReader.scala:355)
at org.apache.spark.sql.DataFrameReader.$anonfun$load$2(DataFrameReader.scala:322)
at scala.Option.getOrElse(Option.scala:189)
at org.apache.spark.sql.DataFrameReader.load(DataFrameReader.scala:322)
at org.apache.spark.sql.DataFrameReader.load(DataFrameReader.scala:235)
at sun.reflect.GeneratedMethodAccessor338.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at py4j.reflection.MethodInvoker.invoke(MethodInvoker.java:244)
at py4j.reflection.ReflectionEngine.invoke(ReflectionEngine.java:380)
at py4j.Gateway.invoke(Gateway.java:295)
at py4j.commands.AbstractCommand.invokeMethod(AbstractCommand.java:132)
at py4j.commands.CallCommand.execute(CallCommand.java:79)
at py4j.GatewayConnection.run(GatewayConnection.java:251)
at java.lang.Thread.run(Thread.java:748)

注意：我能够从dbfs和挂载点读取。

Answer 1

这个问题是由于blob存储容器的公共访问级别引起的。当容器具有私有或Blob公共访问级别时，excel文件也会发生同样的错误。但是，当使用＜code＞Container＜/code＞公共访问级别blob存储容器时，您将能够无错误地读取excel文件。这就是我在试图重现这个问题时得到的。

一个简单的解决方案是将容器的公共访问级别更改为＜code＞container＜/code＞，或者将blob存储帐户装载到Databricks文件系统（这对您来说很有用）。如果您选择更改容器的公共访问级别，请转到blob存储中的容器，您将找到选项“更改访问级别”，您可以在其中选择容器级别。

• 导航到存储帐户的容器并更改访问级别。

• 返回Databricks，再次运行Dataframe read，该操作无任何错误

df2 = spark.read.format("com.crealytics.spark.excel") \
      .option("header", "true").option("inferSchema","true") \
      .load("wasbs://<container>@<storage_acc>.blob.core.windows.net/data.xlsx")

请参考以下文档，了解有关使用数据块访问blob存储帐户的更多信息。

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