Accessing VM Share Path with service account and password from Synapse Scala Note Book

Question

Accessing VM Share Path with service account and password from Synapse Scala Note Book

AJITH KUMAR RAI 0

Hi Team, We need reference document or code sample to access VM Share path from Azure Synapse notebook Scala. For below mentioned scenario.

Share path location: \AZVMSQL01*EarthFilesPPE*

Username: testdomain\testaccount

password: XXXXX

Under this EarthFilesPPE folder we have multiple .dat files

for example:

countryflowers.dat, countryanimals.dat like this.

By using synapse scala notebook want to read countryflowers.dat as mentioned below.

// Read data from the VM Shared File Path into a DataFrame

val df = spark.read

.format("csv")

.option("header", "true") // If your file has a header

.load(filePath)

// Show the DataFrame

df.show()

Please provide sample related to it by using above information.

QuantumCache 20,356 Reputation points

2024-01-31T19:16:25.8433333+00:00

Hello @AJITH KUMAR RAI Did you mount the file share path to the synapse workspace before accessing the folder and content?

AJITH KUMAR RAI 0

Hi SatishBoddu-MSFT Thank you for response.

No, I didn't mount the file share path only in Linked service we added.

To consume/ access file share path files with Username and password need sample or document reference (read file and store into storage account)

We tried below options but no use.

Could you please provide related code sample or document reference for this.

Tried Option: 1

//VMHOST NAME: VMSQL25
//usernamewithdomain= IND\SERVICEACCOUNT
//PASSWORD: Password of service account

import org.apache.spark.sql.{DataFrame}

spark.conf.set("fs.azure.account.auth.type.VMSQL25.dfs.core.windows.net", "DomainUsername")
spark.conf.set("fs.azure.account.auth.key.VMSQL25.dfs.core.windows.net", password)
spark.conf.set("fs.azure.account.auth.username.VMSQL25.dfs.core.windows.net", usernamewithdomain)

val fileSharePath = """VMSQL25/FilesUAT"""

    // Read .dat files into DataFrame using file:// scheme
val datFilesPath = s"file://$fileSharePath/EmpCostCenter.dat"
 
println(datFilesPath)
val datDataFrame: DataFrame = spark.read.text(datFilesPath)

    // Show the DataFrame content
datDataFrame.show()

Tried option: 2

val smbUrl = s"smb://$domain//$username:$password@VMSQL01/RequirementPlanFilesUAT"

//In this path we are having .dat files which need to processed and to store in to storage account.

mssparkutils.fs.mount(
  source =smbUrl,
  mountPoint = "/mnt/RequirementPlanFilesUAT"
)

QuantumCache 20,356

@AJITH KUMAR RAI I have not used the username and password so far, but try the below snippet...!

// Replace the values with your actual file share name, directory name, and file name
val fileShareName = "your-file-share-name"
val directoryName = "EarthFilesPPE"
val fileName = "countryflowers.dat"

// Replace the values with your actual mount point, username, and password
val mountPoint = "/mnt/" + fileShareName
val username = "your-username"
val password = "your-password"

// Mount the file share to your Synapse workspace
mssparkutils.fs.mount(
  source = s"smb://$username:$password@$fileShareName.file.core.windows.net/$directoryName",
  mountPoint = mountPoint
)

// Read data from the file share path into a DataFrame
val filePath = s"$mountPoint/$fileName"
val df = spark.read
  .format("csv")
  .option("header", "true") // If your file has a header
  .load(filePath)

// Show the DataFrame
df.show()

Reference to read further: https://learn.microsoft.com/en-us/azure/synapse-analytics/spark/synapse-file-mount-api#:~:text=using%20the%20following-,Python%20code%3A,-Python

AJITH KUMAR RAI 0

@SatishBoddu-MSFT with above same sample getting below error.
Could you please provide me information on this.

JFYI: Files are present at VM and it is not related to any storage account(like file)

java.lang.IllegalArgumentException: Invalid scheme smb
  at com.microsoft.spark.notebook.msutils.MSFsBaseUtils.$anonfun$validateSchema$1(MSSparkUtils.scala:65)

  at com.microsoft.spark.notebook.msutils.MSFsBaseUtils.$anonfun$validateSchema$1$adapted(MSSparkUtils.scala:64)

  at scala.collection.IndexedSeqOptimized.foreach(IndexedSeqOptimized.scala:36)

  at scala.collection.IndexedSeqOptimized.foreach$(IndexedSeqOptimized.scala:33)

  at scala.collection.mutable.WrappedArray.foreach(WrappedArray.scala:38)

  at com.microsoft.spark.notebook.msutils.MSFsBaseUtils.validateSchema(MSSparkUtils.scala:64)

  at com.microsoft.spark.notebook.msutils.MSFsBaseUtils.checkIfValidSourceURL(MSSparkUtils.scala:111)

  at com.microsoft.spark.notebook.msutils.impl.mount.MSFsMountUtilsImpl.mount(MSFsMountUtilsImpl.scala:49)

  at com.microsoft.spark.notebook.msutils.impl.MSFsUtilsImpl._mount(MSFsUtilsImpl.scala:186)

  at com.microsoft.spark.notebook.msutils.impl.MSFsUtilsImpl.mount(MSFsUtilsImpl.scala:720)

  at mssparkutils.fs$.mount(fs.scala:52)

AJITH KUMAR RAI 0 Reputation points

2024-02-06T05:41:05.52+00:00

Hi Team,
Could someone provide a solution for this requirement?

Just for information, through the linked service and pipeline, it is working as expected. However, I want to add some functionality for this via notebook

Your answer

QuantumCache 20,356 Reputation points

2024-01-31T19:16:25.8433333+00:00

Hello @AJITH KUMAR RAI Did you mount the file share path to the synapse workspace before accessing the folder and content?
AJITH KUMAR RAI 0 Reputation points

2024-02-01T05:16:15.3833333+00:00

Hi SatishBoddu-MSFT Thank you for response.

No, I didn't mount the file share path only in Linked service we added.

To consume/ access file share path files with Username and password need sample or document reference (read file and store into storage account)

We tried below options but no use.

Could you please provide related code sample or document reference for this.

Tried Option: 1

//VMHOST NAME: VMSQL25 //usernamewithdomain= IND\SERVICEACCOUNT //PASSWORD: Password of service account import org.apache.spark.sql.{DataFrame} spark.conf.set("fs.azure.account.auth.type.VMSQL25.dfs.core.windows.net", "DomainUsername") spark.conf.set("fs.azure.account.auth.key.VMSQL25.dfs.core.windows.net", password) spark.conf.set("fs.azure.account.auth.username.VMSQL25.dfs.core.windows.net", usernamewithdomain) val fileSharePath = """VMSQL25/FilesUAT""" // Read .dat files into DataFrame using file:// scheme val datFilesPath = s"file://$fileSharePath/EmpCostCenter.dat" println(datFilesPath) val datDataFrame: DataFrame = spark.read.text(datFilesPath) // Show the DataFrame content datDataFrame.show()

Tried option: 2

val smbUrl = s"smb://$domain//$username:$password@VMSQL01/RequirementPlanFilesUAT" //In this path we are having .dat files which need to processed and to store in to storage account. mssparkutils.fs.mount( source =smbUrl, mountPoint = "/mnt/RequirementPlanFilesUAT" )
QuantumCache 20,356 Reputation points

2024-02-01T21:52:58.85+00:00

@AJITH KUMAR RAI I have not used the username and password so far, but try the below snippet...!

// Replace the values with your actual file share name, directory name, and file name val fileShareName = "your-file-share-name" val directoryName = "EarthFilesPPE" val fileName = "countryflowers.dat" // Replace the values with your actual mount point, username, and password val mountPoint = "/mnt/" + fileShareName val username = "your-username" val password = "your-password" // Mount the file share to your Synapse workspace mssparkutils.fs.mount( source = s"smb://$username:$password@$fileShareName.file.core.windows.net/$directoryName", mountPoint = mountPoint ) // Read data from the file share path into a DataFrame val filePath = s"$mountPoint/$fileName" val df = spark.read .format("csv") .option("header", "true") // If your file has a header .load(filePath) // Show the DataFrame df.show()

Reference to read further: https://learn.microsoft.com/en-us/azure/synapse-analytics/spark/synapse-file-mount-api#:~:text=using%20the%20following-,Python%20code%3A,-Python
AJITH KUMAR RAI 0 Reputation points

2024-02-02T04:47:29+00:00

@SatishBoddu-MSFT with above same sample getting below error.
Could you please provide me information on this.

JFYI: Files are present at VM and it is not related to any storage account(like file)

java.lang.IllegalArgumentException: Invalid scheme smb at com.microsoft.spark.notebook.msutils.MSFsBaseUtils.$anonfun$validateSchema$1(MSSparkUtils.scala:65) at com.microsoft.spark.notebook.msutils.MSFsBaseUtils.$anonfun$validateSchema$1$adapted(MSSparkUtils.scala:64) at scala.collection.IndexedSeqOptimized.foreach(IndexedSeqOptimized.scala:36) at scala.collection.IndexedSeqOptimized.foreach$(IndexedSeqOptimized.scala:33) at scala.collection.mutable.WrappedArray.foreach(WrappedArray.scala:38) at com.microsoft.spark.notebook.msutils.MSFsBaseUtils.validateSchema(MSSparkUtils.scala:64) at com.microsoft.spark.notebook.msutils.MSFsBaseUtils.checkIfValidSourceURL(MSSparkUtils.scala:111) at com.microsoft.spark.notebook.msutils.impl.mount.MSFsMountUtilsImpl.mount(MSFsMountUtilsImpl.scala:49) at com.microsoft.spark.notebook.msutils.impl.MSFsUtilsImpl._mount(MSFsUtilsImpl.scala:186) at com.microsoft.spark.notebook.msutils.impl.MSFsUtilsImpl.mount(MSFsUtilsImpl.scala:720) at mssparkutils.fs$.mount(fs.scala:52)
AJITH KUMAR RAI 0 Reputation points

2024-02-06T05:41:05.52+00:00

Hi Team,
Could someone provide a solution for this requirement?

Just for information, through the linked service and pipeline, it is working as expected. However, I want to add some functionality for this via notebook

Share via

Accessing VM Share Path with service account and password from Synapse Scala Note Book

Your answer