How to share images from ADLS with URL "generated" in a table from databricks

Question

Hi, I need to share to reporter engineer one table in Databricks which has a column like "image URL". In PowerBI, the reporter needs to take this URL and render it in dashboard, therefore I ingested images from SharePoint to ADLS but I need share like a URL instead of a base 64 string. Is it possible?

Answer 1

To share images stored in Azure Data Lake Storage (ADLS) as URLs in a table from Databricks for rendering in Power BI, you need to ensure the following steps are completed:

1. Ensure Public Access to the Images (Optional)

• By default, files in ADLS are private. To make them accessible via URL, you'll need to either:
  • Generate Shared Access Signatures (SAS) for the files or
    • Set the container's access level to "Blob" or "Container" if public access is acceptable.
    • Generating a SAS token provides controlled access to specific resources for a defined time range.

2. Generate Image URLs in Databricks

• Use Databricks to construct the URLs for your images. Here's an example Python code snippet that generates the URLs for images stored in ADLS:

from pyspark.sql.functions import lit, concat

# Base URL for your ADLS account
storage_account_name = "<your_storage_account_name>"
container_name = "<your_container_name>"
sas_token = "<your_sas_token>"  # Optional: Use if not publicly accessible

# Example image file paths
data = [("image1.jpg",), ("image2.jpg",), ("image3.jpg",)]
columns = ["file_name"]
df = spark.createDataFrame(data, columns)

# Construct the URL
base_url = f"https://{storage_account_name}.blob.core.windows.net/{container_name}/"
df_with_urls = df.withColumn("image_url", concat(lit(base_url), df["file_name"], lit(f"?{sas_token}")))

# Display or save the table
df_with_urls.show()

• Replace <your_storage_account_name>, <your_container_name>, and <your_sas_token> with your specific values.

3. Export the Table to Power BI

• Save the table to a format Power BI can read, such as CSV or Parquet. You can save the table back to ADLS or other storage locations accessible to Power BI.

output_path = f"abfss://{container_name}@{storage_account_name}.dfs.core.windows.net/output/image_urls/"
df_with_urls.write.mode("overwrite").csv(output_path)

4. Connect Power BI to the Table

• In Power BI, connect to the table containing the image URLs. Use the "Web URL" data category for the image URL column:
  • Select the column with the image URLs.
    • Go to the "Modeling" tab.
      • Change the "Data Category" to "Image URL."

5. Verify Image Rendering

• Power BI will render the images in visuals such as tables or matrices as long as the URLs are valid and accessible.

Additional Notes:

• Ensure your ADLS has the appropriate IAM roles assigned to users if SAS tokens are used.
• For private blobs, generate SAS tokens dynamically based on the user or session accessing the data.

Let me know if you need help implementing any of these steps!To share images stored in Azure Data Lake Storage (ADLS) as URLs in a table from Databricks for rendering in Power BI, you need to ensure the following steps are completed:

1. Ensure Public Access to the Images (Optional)

• By default, files in ADLS are private. To make them accessible via URL, you'll need to either:
  • Generate Shared Access Signatures (SAS) for the files or
    • Set the container's access level to "Blob" or "Container" if public access is acceptable.
    • Generating a SAS token provides controlled access to specific resources for a defined time range.

2. Generate Image URLs in Databricks

• Use Databricks to construct the URLs for your images. Here's an example Python code snippet that generates the URLs for images stored in ADLS:

from pyspark.sql.functions import lit, concat

# Base URL for your ADLS account
storage_account_name = "<your_storage_account_name>"
container_name = "<your_container_name>"
sas_token = "<your_sas_token>"  # Optional: Use if not publicly accessible

# Example image file paths
data = [("image1.jpg",), ("image2.jpg",), ("image3.jpg",)]
columns = ["file_name"]
df = spark.createDataFrame(data, columns)

# Construct the URL
base_url = f"https://{storage_account_name}.blob.core.windows.net/{container_name}/"
df_with_urls = df.withColumn("image_url", concat(lit(base_url), df["file_name"], lit(f"?{sas_token}")))

# Display or save the table
df_with_urls.show()

• Replace <your_storage_account_name>, <your_container_name>, and <your_sas_token> with your specific values.

3. Export the Table to Power BI

• Save the table to a format Power BI can read, such as CSV or Parquet. You can save the table back to ADLS or other storage locations accessible to Power BI.

output_path = f"abfss://{container_name}@{storage_account_name}.dfs.core.windows.net/output/image_urls/"
df_with_urls.write.mode("overwrite").csv(output_path)

4. Connect Power BI to the Table

• In Power BI, connect to the table containing the image URLs. Use the "Web URL" data category for the image URL column:
  • Select the column with the image URLs.
    • Go to the "Modeling" tab.
      • Change the "Data Category" to "Image URL."

Power BI will render the images in visuals such as tables or matrices as long as the URLs are valid and accessible.