SparkR save to table question

[JoeCrozier]

I'm sure this is basic, but I've tried googling etc and Im lost:

My go-to programming language is R and I'd love to begin using R in Spark R notebooks and spark job definitions. So far I've written some R code in a notebook that:

• -loads some packages (had to figure out how to pre-install them in a custom environment)
• -connects to a redcap database via api
• -grabs some data from that database
• -saves it as a csv in a lakehouse in the "files"
• Manually (not with R), I've clicked the csv to "load to tables" and made it a table
• I then connected a power bi dashboard to it.

Thats great, but really what I'd like to do is incorporate this R script into a dataflow that saves the data directly into a table (so i dont have to do that clicking).

Am I understanding correctly that "tables" in Fabric are "parquet" tables? And if so, can they be saved directly?

My R code looks roughly like this:

dataFromRedcap<-redcap_read(redcap_uri = url,token = token)

data2<-dataFromRedcap$data

From there, I've saved it to csv with

temp_csv_api <- "/lakehouse/default/Files/redcap.csv"

readr::write_csv(data2,temp_csv_api)

but is there a comparable step I could do that would save it to tables?

[v-gchenna-msft · Accepted solution]

Hi @JoeCrozier ,

Thanks for using Fabric Community.
Unfortunately I am unable to find any way to save a dataframe as table using spark R even after searching every where in google. I am not sure whether we can do this directly or not.

But I found an alternative way inorder to avoid manual step, you can use below code snippet in pyspark to load the files from csv to table in lakehouse.

Code Snippet -

df = spark.read.format("csv").option("header","true").load("Files/year/month/date/sales.csv")
# df now is a Spark DataFrame containing CSV data from "Files/year/month/date/sales.csv".
display(df)

df.write.format("delta").save("Tables/actual_weather")

 
Above code can be executed along with your existing code, but make sure that above code is written in pyspark not in spark R.

Hope this is helpful. Please let me know incase of further queries.

[JoeCrozier]

Thank you.  Yea I posted this on reddit as well and got the same answer.  He seemed to suggest "you can do all this in R" but then didnt know specifics and said to use python anyway.  

[v-gchenna-msft]

Glad to know your query got resolved. Please continue using Fabric Community for your further queries.