inst/doc/join-plan.R

In DBmaps: A Metadata-Driven Framework for Streamlining Database Joins

## ----include = FALSE----------------------------------------------------------
knitr::opts_chunk$set(
  collapse = TRUE,
  comment = "#>"
)
library(data.table)
library(DBmaps)

## ----setup_metadata-----------------------------------------------------------
# Define customer metadata
customers_meta <- table_info(
  table_name = "customers",
  source_identifier = "customers.csv",
  identifier_columns = "customer_id",
  key_outcome_specs = list(
    list(OutcomeName = "CustomerCount", 
         ValueExpression = 1, 
         AggregationMethods = list(
           list(AggregatedName = "CountByRegion",
                AggregationFunction = "sum",
                GroupingVariables = "region")
         )
    )
  )
)

# Define transaction metadata
transactions_meta <- table_info(
  "transactions", 
  "t.csv", 
  "tx_id",
  key_outcome_specs = list(
    list(OutcomeName = "Revenue", 
         ValueExpression = quote(price * quantity),
         AggregationMethods = list(
           list(AggregatedName = "RevenueByCustomer",
                AggregationFunction = "sum", 
                GroupingVariables = "customer_id")
         )
  ))
)

# Combine metadata
master_metadata <- create_metadata_registry()
master_metadata <- add_table(master_metadata, customers_meta)
master_metadata <- add_table(master_metadata, transactions_meta)

## ----run_simple, message=TRUE-------------------------------------------------
user_selections <- list(
  customers = "region",
  transactions = "RevenueByCustomer"
)
plan <- create_join_plan(
  base_table = "customers",
  selections = user_selections,
  metadata_dt = master_metadata
)

print(plan)

## ----setup_invalid------------------------------------------------------------
# Add product metadata for this example
products_meta <- table_info("products", "p.csv", "product_id", list(list(OutcomeName="x",ValueExpression=1,AggregationMethods=list(list(AggregatedName="y",AggregationFunction="z",GroupingVariables="category")))))
transactions_meta_v2 <- table_info("transactions", "t.csv", "trans_id", list(
  list(OutcomeName="Revenue", ValueExpression=quote(price*qty), AggregationMethods=list(
    # This aggregation is by product_id, not customer_id
    list(AggregatedName="RevenueByProduct", AggregationFunction="sum", GroupingVariables="product_id")
  ))
))
invalid_metadata <- rbindlist(list(customers_meta, products_meta, transactions_meta_v2))

# The invalid request
invalid_selections <- list(
  customers = "customer_id",
  transactions = "RevenueByProduct"
)

## ----run_invalid, error=TRUE--------------------------------------------------
try({
create_join_plan(
  base_table = "customers",
  selections = invalid_selections,
  metadata_dt = invalid_metadata
)
})