For Loop For Set Weeks from Function in R

Question

I am using Databricks to retrieve data from Snowflake. Currently the function being used in R works as is. However, my dataset is very large (100B rows) and would like to call my dataset in the form of a for loop to lower the memory. How would I retrieve the dataset like below in terms of a for loop?

## Call Libraries to retrieve dataset from Snowflake
library(rJava)
library(RJDBC)
library(DBI)

## Connect to Snowflake

jdbcDriver <- JDBC(driverClass="com.snowflake.client.jdbc.SnowflakeDriver",
                  classPath="dbfs:/FileStore/jars/ced63da8_4613_4687_9051_1ea61dbf3b76- 
snowflake_jdbc_3_12_12-30836.jar")

jdbcConnection <- dbConnect(jdbcDriver,
                           "jdbc:snowflake://xxx.us-east-1.snowflakecomputing.com/",
                           "xxxx",
                           dbutils.secrets.get(scope="xxx", key="xxx"))

Below is where I would like the for loop to be implemented to retrieve weekly data instead of whole dataset and append results to one another. The goal is to create a new table in Snowflake from these results but curious how to create the for loop first.

## Run SQL Query to retrieve dataset from Snowflake in the form of an R function
sq <- function(query){
 dbGetQuery(jdbcConnection, knitr::knit_expand(text=query))
}


show1 <- function(a) 
  sq(
    paste(
"  
select
data_source,
media_id, 
sum(activity_count)
from", a, "
where START_DATE_EST between '2019-11-01' and '2021-11-14' "
)

)
activity_by_month<-show1("SCHEMA.TABLE_NAME")

If I were to run this it would take days and it will timeout due to the amount of data. I want to run this function in the format of a for loop where I collect the data by week and append the results together. Anyone ever do this before?

The ideal approach would be to run this like this:

start_date_vector <- c('2019-11-01', '2019-11-09', etc.)
end_date_vector <- c('2019-11-08', '2019-11-16', etc.)

for(i in start_date_vector) {
  for(j in end_date_vector) {
    show1}}

rbind(show1)

The SQL query above would look something like this:

where START_DATE_EST between", start_date_vector," and",end_date_vector "

And then write a SQL query that will union the results together and save a table.

user_16850750 · Answer

Given the dataset is large there are likely a couple of better and much faster options than retrieving the data via JDBC for use in a Spark (Databricks) environment, where you presumably want to parallelise further processing once you've extracted the data.

Snowflake Spark Connector
The connector is available and preloaded in Databricks so you should be able to use it in your environment. It is specifically designed to handle direct large scale data transfer between Snowflake and a Spark cluster and uses bulk data transfer techniques under the covers. It can be used to retrieve data from Snowflake into a Spark dataframe, which could then be persisted to storage if needed.

COPY to partitioned PARQUET files
Snowflakes COPY INTO command supports partitioned file unload of data to PARQUET files. You can specify the columns/expressions that you want used to partition the files, similarly to the nested for loop approach you have in R e.g. partition by ('date=' || to_varchar(START_DATE_EST), 'YYYY-MM') || '/week=' || to_varchar(week(START_DATE_EST))).

You can unload to an internal Snowflake stage and then GET the files from there to your local R environment. If you have access to cloud storage (e.g. AWS S3 Bucket) you can unload there via a Snowflake External stage which could also be made directly accessible from your Spark environment with the right permissions. The COPY INTO and GET commands are supported from the JDBC and ODBC drivers, so you can form the desired SQL code programmatically and execute it from R.

For Loop For Set Weeks from Function in R

1 Answers