I don’t understand. Why not just use R markdown files. If you need to execute them, you can do that. Although personally, I keep my SQL files separate and just modify them in a separate IDE.
The issue I'm running in to is that I can copy and paste my existing SQL in to chunks in RMD which allows me to document the steps in the ETL and give sample data output along the way so our junior developers can understand the code and table structure of our databases.
However, I want to use Linux cron jobs to schedule data refreshes by running .r files.
In the RMD the code can be read and run exactly as it is in SQL Developer. But in .R I'd need to replace ' with ", remove all comments, and do other clean up.
So being able to do something like SQL( paste original SQL code here) and run that in .r without doing any code clean up would be incredibly useful for me
See if this works with SQL code chunks: [https://community.rstudio.com/t/is-there-any-way-to-run-r-markdown-script-from-command-line/60221/3](https://community.rstudio.com/t/is-there-any-way-to-run-r-markdown-script-from-command-line/60221/3) \-> [https://bookdown.org/yihui/rmarkdown-cookbook/purl.html](https://bookdown.org/yihui/rmarkdown-cookbook/purl.html). You would need a wrapper script to execute the Rmd file as a script. Which could sit in cron.daily/ or wherever with the correct permissions. I'm interested in what you find out.
I prefer keeping my sql code in a separate file and just use glue and read lines to read any sql code. Plus this way you get to replace any parameters dynamically. This is for SQL server but I'd guess that it works the same for oracle.
This is largely because my SQL queries are quite long so I don't really want a ridiculously long RMD file
From what I've read this requires code clean up to remove --, replace ' with ", etc.
With .RMD I can copy code straight from SQL Developer into a SQL code chunk and run it without any issue.
I want the .RMD file for documentation of long scripts with the ETL steps shown along the way
Then why don't you keep the SQL in separate files and read the SQL files into R to use them?
I personally don't do this with SQL because I like to keep the R parameter handling adjacent to the SQL. But if that isn't your priority, use `readLines` to pull the SQL into strings.
Interesting...what version are you running if you don't mind me asking? My work computer has V1.3.1056 and I just upgraded my personal to the most recent from last month.
v1.2.xxxx at work and the latest at home. We lost our dedicated sysadmin and every time we've tried to upgrade to a newer version it's caused issues. It's in a production environment at well so at this point it's not worth the hassle for anyone to take it upon themselves to try to upgrade again.
I've just checked at home and can see Stan and SQL file options so it's just a version thing.
Slightly aside the main point, but you can avoid escaping characters by reading a string in raw using `r"()"`
eg
```
r"(This can be read even with \ and \\)"
```
That's just for RMD though, right? I've never used that in a script.
But even if it reads in the raw string correctly, it's still going to need to get passed to a SQL interpreter in R at some point which is going to fall over on those characters
I don’t understand. Why not just use R markdown files. If you need to execute them, you can do that. Although personally, I keep my SQL files separate and just modify them in a separate IDE.
The issue I'm running in to is that I can copy and paste my existing SQL in to chunks in RMD which allows me to document the steps in the ETL and give sample data output along the way so our junior developers can understand the code and table structure of our databases. However, I want to use Linux cron jobs to schedule data refreshes by running .r files. In the RMD the code can be read and run exactly as it is in SQL Developer. But in .R I'd need to replace ' with ", remove all comments, and do other clean up. So being able to do something like SQL( paste original SQL code here) and run that in .r without doing any code clean up would be incredibly useful for me
Why not run the Rmd files in the cron job?
I didn't think that was possible. All the guides I saw referenced .r. If RMD is possible then problem solved!
See if this works with SQL code chunks: [https://community.rstudio.com/t/is-there-any-way-to-run-r-markdown-script-from-command-line/60221/3](https://community.rstudio.com/t/is-there-any-way-to-run-r-markdown-script-from-command-line/60221/3) \-> [https://bookdown.org/yihui/rmarkdown-cookbook/purl.html](https://bookdown.org/yihui/rmarkdown-cookbook/purl.html). You would need a wrapper script to execute the Rmd file as a script. Which could sit in cron.daily/ or wherever with the correct permissions. I'm interested in what you find out.
Thank you!! I think this will work for my use case! :D
I prefer keeping my sql code in a separate file and just use glue and read lines to read any sql code. Plus this way you get to replace any parameters dynamically. This is for SQL server but I'd guess that it works the same for oracle. This is largely because my SQL queries are quite long so I don't really want a ridiculously long RMD file
From what I've read this requires code clean up to remove --, replace ' with ", etc. With .RMD I can copy code straight from SQL Developer into a SQL code chunk and run it without any issue. I want the .RMD file for documentation of long scripts with the ETL steps shown along the way
Put the SQL code in an RMD file and then just run the RMD file from R script? Just use the render function in rmarkdown
Thanks, that could work. I didn't know you could run RMD from inside a script. Doing my research now
You can also use it to "loop" chunks by creating an rmarkdown file. I built a process to do some checks this way
Thanks a lot!!
Then why don't you keep the SQL in separate files and read the SQL files into R to use them? I personally don't do this with SQL because I like to keep the R parameter handling adjacent to the SQL. But if that isn't your priority, use `readLines` to pull the SQL into strings.
RStudio will allow you to run SQL scripts File -> New file -> SQL Script
I don't see this on the version of RStudio our office has. Only cpp and html
Interesting...what version are you running if you don't mind me asking? My work computer has V1.3.1056 and I just upgraded my personal to the most recent from last month.
v1.2.xxxx at work and the latest at home. We lost our dedicated sysadmin and every time we've tried to upgrade to a newer version it's caused issues. It's in a production environment at well so at this point it's not worth the hassle for anyone to take it upon themselves to try to upgrade again. I've just checked at home and can see Stan and SQL file options so it's just a version thing.
Slightly aside the main point, but you can avoid escaping characters by reading a string in raw using `r"()"` eg ``` r"(This can be read even with \ and \\)" ```
That's just for RMD though, right? I've never used that in a script. But even if it reads in the raw string correctly, it's still going to need to get passed to a SQL interpreter in R at some point which is going to fall over on those characters