---
title: "Getting started"
vignette: >
  %\VignetteIndexEntry{Getting started}
  %\VignetteEngine{quarto::html}
  %\VignetteEncoding{UTF-8}
---

```{r setup}
#| include: false
show_tree <- function(dir) {
  withr::with_dir(fs::path_dir(dir), {
    fs::dir_tree(fs::path_file(dir))
  })
}
```

fastreg aims to make working with Danish registers simpler and faster by
providing functionality to convert the SAS register files (`.sas7bdat`)
into [Parquet](https://parquet.apache.org/) and read the resulting
Parquet files. A *register* in this context refers to a collection of
related data files that belong to the same dataset, typically with
yearly snapshots (e.g., `bef2020.sas7bdat`,`bef2021.sas7bdat`).

::: callout-note
We use package prefixes (`fastreg::`) throughout the documentation
rather than `library()` calls, to make the package origin of each
function explicit and avoid naming conflicts.
:::

## Why Parquet?

[Parquet](https://parquet.apache.org/) is a columnar storage file format
optimised for analytical workloads. Compared to
[SAS](https://fileinfo.com/extension/sas7bdat) files (and row-based
formats like CSV), Parquet offers:

- **Smaller file size**: Efficient compression significantly reduces
  disk space, especially for large datasets.
- **Faster queries**: The columnar layout speeds up analytical queries
  that only need a subset of columns.
- **Wide tool support**: Parquet is supported across data processing
  frameworks in [R](https://www.r-project.org/),
  [Python](https://www.python.org/), and beyond, making it easy to
  integrate into modern workflows.

## Configuring paths

Many of fastreg's functions depend on the locations of the original SAS
files and the eventual Parquet files, including the conversion, writing,
and reading functions. You can configure these paths via two options:
`fastreg.project_rawdata_dir` and `fastreg.project_workdata_dir`. You
can set these `options()` at the top of your R script or Quarto
document, in your R Project's `.Rprofile`, or in your user-level
`.Rprofile`. To add to the file, at the top of an R script, write (using
a temporary directory here for these examples):

```{r options}
options(
  # With a fake project ID and the temporary directory.
  # Uses `E` rather than `E:` because Windows has issues with a colon in the
  # path when using a temporary location.
  fastreg.project_rawdata_dir = fs::path_temp("E/rawdata/701020/"),
  fastreg.project_workdata_dir = fs::path_temp("E/workdata/701020/parquet-registers/")
)
```

If you want to set those exact same options in the R Project's
`.Rprofile`, run the following line in your Console to open up the
`.Rprofile` file for the project:

```{r options-project-profile}
#| filename: "Console"
#| eval: false
usethis::edit_r_profile("project")
```

You can then add the same `options()` as shown in the R script example
above to that file and save it. The next time you open the project,
those options will be set.

If you want to set these options for all of your R projects and
sessions, you can add them globally in your user-level `.Rprofile`. To
open the `.Rprofile`, run:

```{r options-user-profile}
#| filename: "Console"
#| eval: false
usethis::edit_r_profile("user")
```

## Setup

For the examples below, we've simulated SAS register data for two
registers, `bef` and `lmdb`:

```{r prepare}
rawdata_dir <- getOption("fastreg.project_rawdata_dir")
workdata_dir <- getOption("fastreg.project_workdata_dir")

registers_tbl <- fastreg::simulate_registers_with_paths(
  c("bef", "lmdb"),
  c("", "1999", "1999_1", "2020", "2021"),
  n = 1000,
  output_dir = rawdata_dir
)

sas_paths <- registers_tbl |>
  purrr::pwalk(fastreg::write_to_sas) |>
  dplyr::pull(output_path)
```

```{r setup-tree}
#| echo: false
# Show how files look relative to "E" to mimic DST
rawdata_dir |>
  # Get to the `E` directory.
  fs::path_dir() |>
  fs::path_dir() |>
  show_tree()
```

## Converting a single file

Converting one file from SAS to Parquet in fastreg isn't a simple change
of file extension. We make use of Parquet's Hive partitioning to
organise the output by year, for easier querying and management. So the
output Parquet file is written to a subdirectory named after the year
extracted from the file name. Use the `convert()` function to convert a
single SAS file to a year-partitioned Parquet format:

```{r convert-file}
fastreg::convert(
  path = sas_paths[1],
  output_dir = workdata_dir
)
```

`convert()` reads files in chunks (to be able to handle
larger-than-memory data) with a default of reading 1 million rows,
extracts 4-digit years from filenames for partitioning, and lowercases
column names. See `?convert` for more details.

::: callout-note
When a SAS file contains more rows than the `chunk_size`, multiple
Parquet files will be created from it. This doesn't affect how the data
is loaded with `read_register()` (see
[Reading a Parquet register](#reading-a-parquet-register) below), you
may see multiple Parquet files per source SAS file.
:::

::: callout-warning
If you're handling very large SAS files, please refer to the
[When SAS files become too big](#sec-large-sas-files) section below.
:::

Even though this only converts a single file, the output is partitioned
by the year extracted from the file name as seen below:

```{r output-tree-file}
#| echo: false
workdata_dir |>
  # Get to the `E` directory.
  fs::path_dir() |>
  fs::path_dir() |>
  fs::path_dir() |>
  show_tree()
```

## Converting multiple registers in parallel

For many or large files, fastreg provides a
[targets](https://docs.ropensci.org/targets/) pipeline template that
parallelises conversion across CPU cores. By default it uses 10 workers,
but that can be adjusted in the pipeline in the `_targets.R` file to not
consume too many cores on a shared server.

To create the pipeline file, you can use the `use_template()` function,
which creates two files: a `_targets.R` pipeline and a
`conversion-log.qmd` Quarto document. When running the pipeline, the
conversion log is written to a PDF, named
`conversion-log-<timestamp>.pdf`, by default.

In this example, we're outputting the template to a temporary directory.

```{r use-targets}
pipeline_dir <- fs::path(workdata_dir, "conversion_pipeline")
fs::dir_create(pipeline_dir)

fastreg::use_template(path = pipeline_dir)
```

Once the `_targets.R` file is created, open it and edit the `config`
section:

```{r config}
config <- list(
  sas_paths = fastreg::list_sas_files(rawdata_dir),
  output_dir = workdata_dir
)
```

The `sas_paths` is a vector of paths to the SAS files to convert, found
recursively via `list_sas_files()`. This can span different registers.
The `output_dir` is where the Parquet files will be written.

After you've updated the `config` section, you can run the pipeline:

```{r tar-make}
#| eval: false
targets::tar_make()
```

```{r}
#| label: "manual-conversion"
#| include: false
fastreg::list_sas_files(rawdata_dir) |>
  purrr::walk(\(path) {
    fastreg::convert(path, workdata_dir)
  })
```

The pipeline will find all SAS files from `input_dir` and convert each
file into a Parquet file, all done in parallel. Re-running `tar_make()`
only re-converts registers whose source files have changed or if the
pipeline itself has been edited.

::: callout-warning
If you're handling very large SAS files, please refer to the
[When SAS files become too big](#sec-large-sas-files) section below.
:::

## Listing available Parquet files and datasets

To list what Parquet files or datasets are available, use the
`list_parquet_files()` and `list_parquet_datasets()` functions. These
look in the `fastreg.project_workdata_dir` and
`fastreg.project_rawdata_dir` directories (set with `options()`) for any
Parquet files following a specific pattern.

You can use them interactively in the Console (which are shown in the
temporary directory when rendered on the website):

```{r list-files}
#| filename: "Console"
# For individual files
fastreg::list_parquet_files()
# For datasets (registers with all years).
fastreg::list_parquet_datasets()
```

## Reading a Parquet register

The final function reads the converted Parquet register data into R,
returning a [DuckDB](https://duckdb.org/) table. Using a DuckDB table is
a powerful way to query and process large data without loading it all
into memory.

A quick way of reading a register is with the `read_register()`
function. This function looks for a given name of a register in either
the `fastreg.project_workdata_dir` or `fastreg.project_rawdata_dir`
directories (set with `options()`) and reads it as a DuckDB table. You
can also use the more specific `read_parquet_dataset()` or
`read_parquet_file()` functions to read from a specific directory or
file path.

```{r read-file}
bef <- fastreg::read_register("bef")
bef
```

Or directly with `read_parquet_dataset()` or `read_parquet_file()`:

```{r read-partition}
fastreg::list_parquet_datasets()[1] |>
  fastreg::read_parquet_dataset()

# Or a single file
fastreg::list_parquet_files()[1] |>
  fastreg::read_parquet_file()
```

The resulting DuckDB table can be filtered and transformed with `dplyr`.
For example, you can filter the data:

```{r filter-file}
bef |>
  dplyr::filter(koen == 2) |>
  dplyr::compute()
```

After the query, we execute it with `dplyr::compute()`. This saves the
result as a temporary table inside DuckDB, without loading it into R
memory.

Notice the `??` in the first line of the output. This shows us that the
total number of matching rows is not yet known because the data isn't
loaded into memory.

::: callout-note
If you need to load the data into memory in R, you can use
`dplyr::collect()`. However, for large registers this can take a long
time, so only do this when it's absolutely necessary and make sure to
filter the data before collecting.
:::

## When SAS files become too big (> 2,147,483,647 rows) {#sec-large-sas-files}

When developing this package, we encountered an issue on Windows where
[`haven::read_sas()`](https://haven.tidyverse.org/reference/read_sas.html)
fails when the `skip` parameter is set to more than 2,147,483,647 (the
32-bit integer limit, 2^31 − 1). Instead of reading the expected rows,
the function reads the first part of the SAS file again.

fastreg currently handles this by stopping the conversion after
2,147,483,647 rows and returning a warning. This means that
**fastreg does not convert the rest of the data in that file**.

You can get around this by handling the original SAS file in two ways:

1. Split the large SAS file into two. Name them something like
   `<register_name>01.sas7bdat`,`<register_name>02.sas7bdat`, etc.
   (avoid extra `_` as they are kept as a part of the name of the
   partitioned Parquet register). If you use the `_targets.R` pipeline,
   add the paths of these files in the `sas_paths` (see section on
   converting multiple registers in parallel above).

2. Convert the large SAS file to another format, e.g., `.csv`, and
   convert it separately, as shown in the code block below. You can
   either keep this as a separate script or add it to your targets
   pipeline.

```{r convert-large-csv}
#| eval: false
large_csv_path <- fs::path("path/to/large_register2020.csv")
output_dir <- fs::path("path/to/output/dir")

arrow::open_dataset(large_csv_path, format = "csv") |>
  dplyr::rename_with(tolower) |>
  arrow::write_dataset(
    path = fs::path(output_dir, "large_register", "year=2020"),
    format = "parquet"
  )
```

To follow the default Parquet partitioning name, use
`"year=__HIVE_DEFAULT_PARTIION__"` if the file doesn't contain a year in
the name.