Writing Long SQL Queries in R

Question

I am learning how to read data from a server directly in a data frame in R. In the past, I wrote SQL queries longer than 50 lines (with all selections and joins). Any tips on how to write long queries in R? Is there a way to write a query elsewhere in R and then paste it into the "sqlQuery" part of the code?

Answer 1

Save long SQL queries in .sql files and read them when using readLines + paste with collapse = '\ n'

my_query <- paste(readLines('your_query.sql'), collapse='\n')
results <- sqlQuery(con, my_query)

Answer 2

SQL- R , + . :

## Connect ot DB
library(RODBC)
con <- odbcConnect('MY_DB')

## Paste the query as is (you can have how many spaces and lines you want)
query <- 
"
  SELECT [Serial Number]
        ,[Series]
        ,[Customer Name]
        ,[Zip_Code]

  FROM [dbo].[some_db]

  where [Is current] = 'Yes' and 
        [Serial Number] LIKE '5%' and
        [Series] = '20'

  order by [Serial Number]

"

## Simply replace the new lines + spaces with a space and you good to go
res <- sqlQuery(con, gsub("\\n\\s+", " ", query))
close(con)

Answer 3

17- SQL- RODBC @arvi1000, , , .sql. collapse . 90 , . RODBC - MySQL ODBC.

, , , , :

channel <- odbcConnect("mysql_odbc", uid="username", pwd="password")
sqlString<-readLines("your_query.sql")           

for (i in 1:length(sqlString)) {
  print(noquote(sqlString[i]))
  sqlQuery(channel, as.name(sqlString[i]))
}

script , , , .., SELECT . .sql , . , , , , -, SELECT .

Answer 4

.sql( sql nosql) , .
-, RStudio ( , ), , . {...} .

:

query <- {'
 SELECT 
  user_id,
  format(date,"%Y-%m") month,
  product_group,
  product,
  sum(amount_net) income,
  count(*) number
FROM People 
WHERE 
  date > "2015-01-01" and
  country = "Canada"
  GROUP BY 1,2,3;'})

Folding a request can even be performed inside a function (folding a long argument) or in other situations when our code extends to inconvenient sizes.