## ----include = FALSE----------------------------------------------------------
# Evaluate chunks only where the ducklake DuckDB extension is already
# installed. The probe never downloads anything, so building this vignette
# needs no network access.
ducklake_available <- ducklake::ducklake_extension_available()
knitr::opts_chunk$set(
  collapse = TRUE,
  comment = "#>",
  eval = ducklake_available
)
# Use a unique temp directory for this vignette to avoid conflicts during R CMD check
vignette_temp_dir <- file.path(tempdir(), "ducklake_vignette")
dir.create(vignette_temp_dir, showWarnings = FALSE, recursive = TRUE)
knitr::opts_knit$set(root.dir = vignette_temp_dir)

## ----setup, message=FALSE-----------------------------------------------------
library(ducklake)
library(dplyr)

## ----create, message=FALSE----------------------------------------------------
# Create a data lake in a specific directory
attach_ducklake("my_lake", lake_path = vignette_temp_dir)

## ----attach, eval=FALSE-------------------------------------------------------
# # Attach to an existing lake (creates it if it doesn't exist)
# attach_ducklake("existing_lake", lake_path = "/path/to/data_lake")

## ----attach-postgres, eval=FALSE----------------------------------------------
# # PostgreSQL catalog for multi-client access
# attach_ducklake(
#   "shared_lake",
#   backend = "postgres",
#   catalog_connection_string = "dbname=ducklake_catalog host=localhost",
#   lake_path = "/shared/lake/data/"
# )
# 
# # SQLite catalog for lightweight local multi-client setups
# attach_ducklake(
#   "team_lake",
#   backend = "sqlite",
#   catalog_connection_string = "metadata.sqlite",
#   lake_path = "data_files/"
# )

## ----detach, eval=FALSE-------------------------------------------------------
# # Detach when done (doesn't delete the lake)
# detach_ducklake("my_lake")

## ----load_df------------------------------------------------------------------
with_transaction(
  create_table(mtcars, "cars"),
  author = "Data Engineer",
  commit_message = "Initial car data load"
)

## ----update_cars--------------------------------------------------------------
# Create a second version of the cars table
with_transaction(
  get_ducklake_table("cars") |>
    mutate(kpl = mpg * 0.425144) |>  # Add km/L conversion
    replace_table("cars"),
  author = "Data Engineer",
  commit_message = "Add km/L metric to cars table"
)

## ----load_csv-----------------------------------------------------------------
# First write a sample CSV (in practice, you'd have an existing file)
csv_path <- file.path(vignette_temp_dir, "sample_data.csv")
write.csv(head(iris, 20), csv_path, row.names = FALSE)

# Load the CSV into the data lake
with_transaction(
  create_table(csv_path, "iris_sample"),
  author = "Data Engineer",
  commit_message = "Load iris sample from CSV"
)

## ----load_url, eval=FALSE-----------------------------------------------------
# # ducklake can load data directly from URLs
# with_transaction(
#   create_table("https://example.com/data.csv", "remote_data"),
#   author = "Data Engineer",
#   commit_message = "Load remote dataset"
# )

## ----add_data_files, eval=FALSE-----------------------------------------------
# add_data_files(
#   "readings",
#   c("extracts/jan.parquet", "extracts/feb.parquet"),
#   create = TRUE
# )
# 
# # See which files back a table
# list_ducklake_files("readings")

## ----load_pipeline------------------------------------------------------------
with_transaction(
  mtcars |>
    filter(mpg > 20) |>
    create_table("efficient_cars"),
  author = "Data Analyst",
  commit_message = "Load filtered car data"
)

## ----list_all_tables----------------------------------------------------------
# Every table and view, with its schema and type
list_ducklake_tables()

## ----create_view--------------------------------------------------------------
get_ducklake_table("cars") |>
  filter(mpg > 25) |>
  create_view("v_efficient_cars")

get_ducklake_table("v_efficient_cars") |> collect()

## ----drop_view----------------------------------------------------------------
drop_view("v_efficient_cars")

## ----comments-----------------------------------------------------------------
set_table_comment("cars", "Motor Trend road tests of 1973-74 models")
set_column_comments(
  "cars",
  mpg = "Miles per US gallon",
  wt = "Weight (1000 lbs)"
)

get_table_comments("cars")

## ----labels-------------------------------------------------------------------
df_visits <- data.frame(subject = c("S1", "S2"), sbp = c(128, 141))
attr(df_visits$subject, "label") <- "Subject identifier"
attr(df_visits$sbp, "label") <- "Systolic blood pressure (mmHg)"

create_table(df_visits, "visits")

collected <- get_ducklake_table("visits") |> collect()
attr(collected$sbp, "label")

## ----read_table---------------------------------------------------------------
# Returns a lazy dplyr tbl
cars_data <- get_ducklake_table("cars")

# Use dplyr verbs
cars_data |>
  filter(cyl == 6) |>
  select(mpg, cyl, hp) |>
  head(3)

## ----collect------------------------------------------------------------------
# Fetch all data into a data.frame
cars_df <- get_ducklake_table("cars") |> collect()
head(cars_df, 3)

## ----view_versions------------------------------------------------------------
# See all snapshots for the cars table
list_table_snapshots("cars")

## ----read_version-------------------------------------------------------------
# Query data as it existed at snapshot 1 -- before the kpl column was added
get_ducklake_table_version("cars", version = 1) |>
  select(mpg, cyl, hp) |>
  head(3)

## ----read_timestamp, eval=FALSE-----------------------------------------------
# # Query data as of a specific time (see list_table_snapshots() for times)
# get_ducklake_table_asof("cars", timestamp = "2024-01-15 10:30:00") |>
#   collect()

## ----replace_table------------------------------------------------------------
with_transaction(
  get_ducklake_table("cars") |>
    mutate(hp_per_cyl = hp / as.numeric(cyl)) |>  # Add derived metric
    replace_table("cars"),
  author = "Data Engineer",
  commit_message = "Add horsepower per cylinder metric"
)

## ----list_snapshots-----------------------------------------------------------
list_table_snapshots()

## ----list_table_snapshots, eval=FALSE-----------------------------------------
# list_table_snapshots("cars")

## ----restore------------------------------------------------------------------
# Roll cars back to snapshot 1. The restore is recorded as a new snapshot,
# so nothing is lost -- you can still time-travel to any version.
restore_table_version(
  "cars",
  version = 1,
  author = "Data Engineer"
)

list_table_snapshots("cars")

## ----simple_transaction, eval=FALSE-------------------------------------------
# with_transaction(
#   create_table(my_data, "my_table"),
#   author = "Your Name",
#   commit_message = "What changed and why"
# )

## ----multi_step, eval=FALSE---------------------------------------------------
# with_transaction({
#   # All these operations happen atomically
#   create_table(raw_data, "raw_table")
# 
#   cleaned <- get_ducklake_table("raw_table") |>
#     filter(!is.na(key_field)) |>
#     create_table("clean_table")
# 
#   get_ducklake_table("clean_table") |>
#     mutate(derived_field = calculate_something(x)) |>
#     create_table("analysis_table")
# },
# author = "Data Engineer",
# commit_message = "Full ETL pipeline run"
# )

## ----manual_transaction, eval=FALSE-------------------------------------------
# # For fine-grained control
# begin_transaction()
# 
# create_table(data1, "table1")
# create_table(data2, "table2")
# 
# # Commit or rollback
# commit_transaction(
#   author = "Your Name",
#   commit_message = "Manual transaction commit"
# )
# 
# # Or if something went wrong:
# # rollback_transaction()

## ----show_query---------------------------------------------------------------
get_ducklake_table("cars") |>
  filter(mpg > 25) |>
  select(mpg, cyl, hp) |>
  show_query()

## ----show_ducklake_query------------------------------------------------------
get_ducklake_table("cars") |>
  mutate(mpg = round(mpg)) |>
  show_ducklake_query()

## ----filter_early-------------------------------------------------------------
# Good: Filter before other operations
get_ducklake_table("cars") |>
  filter(cyl == 6) |>
  mutate(kpl = mpg * 0.425144) |>
  head(3)

## ----select_columns-----------------------------------------------------------
# Good: Select only needed columns
get_ducklake_table("cars") |>
  select(mpg, cyl, hp) |>
  filter(mpg > 25)

## ----sorting_partitioning, eval=FALSE-----------------------------------------
# # Sorting suits high-cardinality columns like timestamps or ids
# set_table_sorting("events", "event_time")
# 
# # Partitioning suits low-cardinality columns like year or region
# set_table_partitioning("sales", c("year(order_date)", "region"))

## ----options, eval=FALSE------------------------------------------------------
# # Trade write speed for smaller files
# set_ducklake_option("parquet_compression", "zstd")
# 
# # Require a commit message on every snapshot -- useful for audit discipline
# set_ducklake_option("require_commit_message", TRUE)
# 
# get_ducklake_options()

## ----cleanup------------------------------------------------------------------
# Detach from the lake
detach_ducklake("my_lake")