3.0.0 `gapindex::get_data` function triggers space quota issues

[EmilyMarkowitz-NOAA]

Issue Description

TLDR: The new 3.0.0 gapindex::get_data function triggers space quota issues. Tagging in @sowasser for awareness.

Steps to Reproduce

When I run the species complex example on gapindex, everything runs great! (yay!)

However, in running the download data script for the Bering Sea data report, pulling through the GAP_PRODUCTS schema, I experienced this issue:

And when we try running this through a personal schema (here, @sowasser's personal oracle schema) the same error occurs much sooner:

So I tried running the get_data function line by line, and got Oracle error 1536 indicating a the space quota issue. As described in this StackOverflow issue, this issue is due to:

"Cause: The space quota for the segment owner in the tablespace has been exhausted and the operation attempted the creation of a new segment extent in the tablespace."
"Action: Either drop unnecessary objects in the tablespace to reclaim space or have a privileged user increase the quota on this tablespace for the segment owner."

This explains why the GAP_PRODCUTS and a personal schema would trigger space errors at different sections of the code - GAP_PRODCUTS schema has more space!

Steps to Reproduce

Here is the excerpt of code from the Bering Sea data report download data script:

# Load libraries and connect to Oracle
library(gapindex)
channel <- gapindex::get_connected()

# Species Covered
googledrive::drive_download(file = googledrive::as_id("10Pn3fWkB-Jjcsz4iG7UlR-LXbIVYofy1yHhKkYZhv2M"),
                            type = "csv",
                            overwrite = TRUE,
                            path = paste0(dir_out_rawdata, "/species-local-names"))

# identify which species complexes you need
report_spp <- readr::read_csv(file = paste0(dir_out_rawdata, "/species-local-names.csv"), 
                              skip = 1, 
                              show_col_types = FALSE) %>%  
  dplyr::filter(grepl(x = species_code, pattern = "c(", fixed = TRUE)) 

temp1 <- data.frame()
for (i in 1:nrow(report_spp)){
  temp2 <- eval(expr = parse(text = report_spp$species_code[i]))
  temp1 <- dplyr::bind_rows(temp1, 
                            dplyr::bind_cols(GROUP_CODE = report_spp$print_name[i], 
                                             SPECIES_CODE = eval(expr = parse(text = report_spp$species_code[i]))))
}

## Pull data. 
production_data <- gapindex::get_data(
  year_set = 1982:maxyr,
  survey_set = "EBS"
  spp_codes = temp1,
  pull_lengths = TRUE, 
  haul_type = 3, 
  abundance_haul = "Y", 
  taxonomic_source = "GAP_PRODUCTS.TAXONOMIC_CLASSIFICATION", # same thing happens with "RACEBASE.SPECIES"
  channel = channel)

Suggested solution

I'm not sure exactly what the solution is, but maybe there is a way to increase the space when we run the get_data function? This code worked in the last version of gapindex, and the function doesn't look that different, so I'm not sure what changed.

Thanks for your help, @zoyafuso-NOAA!

[zoyafuso-NOAA]

Thanks for bringing this up and it's an interesting problem. The space problem is occurring because the previous development version (now 3.0.0) of gapindex writes temporary tables to Oracle to help with queries. I recommend two workflow changes to avoid this error and possibly speed up the data pull:

1. Filter the temp1 dataframe to only existing SPECIES_CODE values. This reduces temp1 from 283075 records to 10347 records.

# filter temp1 for exisiting species codes in GAP_PRODUCTS.TAXONOMIC_CLASSIFICATION
# note. maybe gp_taxa is already called somewhere but directly pulling here..
gp_taxa <- 
  RODBC::sqlQuery(channel = channel, 
                  query = "SELECT * FROM GAP_PRODUCTS.TAXONOMIC_CLASSIFICATION 
                           WHERE SURVEY_SPECIES = 1 ORDER BY SPECIES_CODE")
temp1 <- subset(x = temp1,
                subset = SPECIES_CODE %in% gp_taxa$SPECIES_CODE)

2. Have two separate data pulls. In the first data pull, you would query catch and effort data for all records in temp1 (pull_lengths = FALSE) and then calculate CPUE and biomass only. Then, in a second data pull, query catch, effort, and size data (pull_lengths = TRUE) for just single taxa and calculate length compositions. In this second call, do you need length compositions for all survey years or just the most recent one(s)? If so, you can further speed up the call by reducing the year set, e.g., year_set = maxyr or year_set = maxyr:(maxyr - 1). You can also replace this second call with a query to AKFIN_SIZECOMP if it's just species.

This could speed up your data pull because size data are not being collated for taxon complexes and size data are only being pulled for species-level taxon codes. Let me know if either of these works, especially when on a personal account. I hope at least some of this helps.

[EmilyMarkowitz-NOAA]

Thanks for the quick and helpful response, @zoyafuso-NOAA !

1. Your suggestions made the run much faster (really great suggestion!) but it still buckled at the same spot (using the GAP_PRODUCTS Oracle schema login).
   

2. In troubleshooting this and looking through your suggestion, I actually realized that I don't need sizecomp data for these complexes at all... so I simply set pull_lengths = FALSE and it completed!
   

I tried running these with my personal Oracle schema and it also worked. @sowasser can you try the script with your oracle schema and see if it will finish for you? The code is in my em branch: afsc-gap-products/gap_bs_data_report@6806653.

[sowasser]

I am running into the same error as before.

[zoyafuso-NOAA]

After further discussion it seems that there are a couple of lingering issues but a way forward.

• @sowasser's Oracle account has a small tablespace quota compared to most Oracle users at AFSC, possibly due to how the account was set up. Using GAP_PRODUCTS credentials seems to circumvent this issue.
• The next release in gapindex should involve temporary instead of permanent table creation within the intermediate steps in the get_data() function to avoid having to use permanent table space, if my understanding of Oracle tablespaces is correct.

[EmilyMarkowitz-NOAA]

Sounds like a good plan, @zoyafuso-NOAA! For clarification, I think that the most people are given @sowasser's table space when they are first given an Oracle account. I think we (my account, your account, and GAP_PRODUCTS) all have more because we requested more space, which is different from the norm, and that most users will experience this issue. Let us know if we can test anything for you!

[zoyafuso-NOAA]

I don't remember asking for an increase in my quota, but maybe I was defaulted a larger space when my account was created five years ago? I'm just noticing now that @sowasser get_data() call stops at the first query when the survey-level data are being queried, which is a very small table (on the order of dozens). So yes, if most folks have similarly low levels of space then most will also get this issue. Sophia mentioned that she received her account while she was a postdoc so maybe OFIS has different onboarding defaults for AFSC folks vs non-AFSC folks? I'll investigate offline further. Thanks again

[zoyafuso-NOAA]

Alternatively, it could be because the recycle bin is too full. When tables are dropped, they're moved there and over time they could be accumulating enough space to trigger a space error. I'm toying around with this as well

RODBC::sqlQuery(channel = channel, query = "select * from USER_TS_QUOTAS;" )
RODBC::sqlQuery(channel = channel, query = "PURGE RECYCLEBIN;" )
RODBC::sqlQuery(channel = channel, query = "select * from USER_TS_QUOTAS;" )

Purging the recycle bin may free up some space.

[sowasser]

Thank you for this, @zoyafuso-NOAA. The gapindex::get_data() example from the gapindex vignette worked once I cleared my recycle bin.

Unfortunately, when trying to run the data download for the Bering Sea data report, I get the same error as before.

[sowasser]

Hi @zoyafuso-NOAA,

I'm trying to pull the pollock length data so I can get age comps and I'm running into this error again.

I have just reinstalled gapindex, so I'm working with version 3.0.2, and I've tried clearing my Oracle recycle bin.

Do you have a sense of what else I could be doing wrong here?

Thanks!!