R/stackDataFilesDuck.R

In neonUtilities: Utilities for Working with NEON Data

##############################################################################################
#' @title Align data files into a duckdb dataset

#' @author
#' Claire Lunch \email{clunch@battelleecology.org}

#' @description Takes a set of paths pointing to data files and a variables file and attempts to unify them into a duckdb dataset.
#'
#' @param urls The set of urls to be combined into a dataset.
#' @param varset A list of urls pointing to the set of variables files relevant to the url set.
#' @param tabl The table name of the table the url set represents.
#' @param package Basic or expanded data package?
#' @param all.string T or F, should all fields be set to data type = string? Defaults to FALSE; should generally only be used if schema from variables file is failing and inferring the schema is introducing errors.
#' 
#' @return A duckdb dataset for the input data paths.

#' @export

#' @references
#' License: GNU AFFERO GENERAL PUBLIC LICENSE Version 3, 19 November 2007

# Changelog and author contributions / copyrights
#   2026-04-21 Claire Lunch: Adapted from workflow in datasetQuery().
#   
##############################################################################################

stackDataFilesDuck <- function(urls,
                               varset,
                               tabl,
                               package,
                               all.string=FALSE) {
  
  # start with variables file returned by queryFiles
  trystring <- all.string
  onevar <- FALSE
  
  # check for inconsistencies in variables files
  if(length(varset)>1 & isFALSE(trystring)) {
    
    # check for differences in fieldNames and dataTypes for the relevant table
    varFieldDiff <- checkVarFields(variableSet=varset, tableName=tabl)
    if(isTRUE(varFieldDiff)) {
      
      # if there are inconsistencies, infer schema
      message(paste("Differences in variables files detected for table ", tabl, 
                    ". Schema will be inferred. If this causes errors, try querying released and provisional data separately. Alternatively, all data fields can be set to string type using all.string=TRUE.", sep=""))
      ds <- try(duckdbfs::open_dataset(sources=urls, 
                                       unify_schemas=TRUE,
                                       filename=TRUE,
                                       format="csv"), silent=TRUE)
      if(inherits(ds, "try-error")) {
        trystring <- TRUE
      }
        
    } else {
      # if fieldNames and dataTypes match across files, use first variables file
      varend <- arrow::read_csv_arrow(varset[[1]], col_names=TRUE, skip=0)
      onevar <- TRUE
    }
  }
  
  if(length(varset)==1 & isFALSE(trystring)) {
    varend <- arrow::read_csv_arrow(varset[[1]], col_names=TRUE, skip=0)
  }
  
  if(length(varset)==1 | isTRUE(onevar) & isFALSE(trystring)) {
    tableschema <- schemaFromVarDuck(varend,
                                 tab=tabl,
                                 package=package)
    ds <- try(duckdbfs::open_dataset(sources=urls, 
                                     parser_options = c(
                                       columns=tableschema[[1]],
                                       header=TRUE,
                                       filename=TRUE,
                                       timestampformat=tableschema[[2]]
                                     ),
                                      format="csv"), silent=TRUE)
    if(inherits(ds, "try-error")) {
      trystring <- TRUE
    }
  }
  
  # if making dataset via the paths above failed, try a string schema
  if(isTRUE(trystring)) {
    if(isFALSE(all.string)) {
      message(paste("Data retrieval using variables file and/or inference to generate schema failed for table ", tabl, 
                    ". All fields will be read as strings. This can usually be avoided by excluding provisional data.", sep=""))
    }
    ds <- try(duckdbfs::open_dataset(sources=urls, 
                                     unify_schemas=TRUE,
                                     parser_options = c(
                                       all_varchar=TRUE,
                                       header=TRUE,
                                       filename=TRUE
                                     ),
                                     format="csv"), silent=TRUE)
    if(inherits(ds, "try-error")) {
      message(paste("Reading data as strings failed for table ", tabl, 
                    ". Try excluding provisional data, and contact NEON if unable to resolve.", sep=""))
      return(invisible())
    }
  }
  
  return(ds)
  
}