Write ZTRAX flat files to SQL database

Reads ZTRAX flat files in chunks, optionally filters rows, and loads into the data into a SQL database. Refer to ztrax_layout for details of each table.

db_write_table_ztrax(.con, data_dir, states, ZType = c("ZTrans",
  "ZAsmt"), TableName, KeepColumns = NULL, FilterColumns = NULL,
  .filter = NULL, chunk_size = 10000, overwrite = FALSE)

Arguments

.con	A DBIConnection object, as returned by `DBI::dbConnect()`.
data_dir	A path to a single directory containing uncompressed folders
states	The name, 2-letter abbreviation, or FIPS code for one or more states.
ZType	Either `"ZTrans"` or `"ZAsmt"`.
TableName	Name of table to be added to the database, as it appears in documentation and raw data file names (`UpperCamelCase`).
KeepColumns	A vector of names of columns to be included in table on the database, as they appear in documentation (`UpperCamelCase`). If not provided all columns in the table are included.
FilterColumns	A vector of names of any columns not already included in `KeepColumns` that are needed for filtering the data before loading to the database, as they appear in documentation (`UpperCamelCase`).
.filter	A function, formula to be passed to `purrr::as_mapper()` and then used to filter the raw data in R before loading into the database. In this function refer to all columns by their names as they appear in documentation (`UpperCamelCase`).
chunk_size	The number of rows to include in each chunk.
overwrite	If `TRUE`, an existing table of the same name will be overwritten. This argument doesn't change behavior if the table does not exist yet.

Examples


library(DBI)
library(dplyr)
#> 
#> Attaching package: ‘dplyr’
#> The following objects are masked from ‘package:stats’:
#> 
#>     filter, lag
#> The following objects are masked from ‘package:base’:
#> 
#>     intersect, setdiff, setequal, union

con <- dbConnect(RSQLite::SQLite(), ":memory:")

db_write_table_ztrax(
  .con = con,
  data_dir = ztrax_example_dir(),
  states = c("FL", "NY"),
  ZType = "ZAsmt",
  TableName = "TaxExemption",
  KeepColumns = c("RowID", "TaxExemptionStndCode"),
  FilterColumns = "FIPS",
  .filter = ~ filter(., FIPS %in% c("12086", "36005")),
  chunk_size = 2,
  overwrite = FALSE
)
#> Creating new table `zasmt_tax_exemption`:
#> CREATE TABLE `zasmt_tax_exemption` (
#>   `row_id` char(36),
#>   `tax_exemption_stnd_code` char(2),
#>   PRIMARY KEY (`row_id`, `tax_exemption_stnd_code`)
#> )
#> Importing Florida
#> Importing New York

tbl(con, "zasmt_tax_exemption")
#> # Source:   table<zasmt_tax_exemption> [?? x 2]
#> # Database: sqlite 3.22.0 [:memory:]
#>   row_id                               tax_exemption_stnd_code
#>   <chr>                                <chr>                  
#> 1 1A284B67-3182-E711-80C2-3863BB43E814 HD                     
#> 2 1E284B67-3182-E711-80C2-3863BB43E814 HD                     
#> 3 1E284B67-3182-E711-80C2-3863BB43E814 SC                     
#> 4 1E284B67-3182-E711-80C2-3863BB43E814 WW                     
#> 5 1D389B67-3182-E711-80C2-3863BB43E813 WW                     
#> 6 1D389B67-3182-E711-80C2-3863BB43E813 HD

Arguments

Examples

Contents