Reading 2: Database

21.1 Introduction

  • A huge amount of data resides in databases; essential to know how to access it.

  • Approaches to get data:

    • Request someone to download a snapshot into a .csv file.

    • This can be cumbersome; requires communication for every change.

  • Objective:

    • Enable direct access to the database to retrieve data as needed.

  • Chapter Overview:

    • Learn basics of the DBI package to connect to databases and retrieve data with SQL.

    • Introduction of SQL (Structured Query Language) as an essential language for data scientists.

  • Focus on dbplyr:

    • A tool that translates your dplyr code to SQL.

    • Aim to learn components and features of SQL, but won’t master SQL by chapter end.

  • Key Interfaces:

    • DBI: Low-level interface for database connection and SQL execution.

    • dbplyr: High-level interface that translates dplyr code into SQL queries and executes them via DBI.

21.2 Database Basics

  • Definition of Database:

    • Think of a database as a collection of data frames, termed tables.

  • Characteristics of Database Tables vs. Data Frames:

    1. **Storage:

    • Databases:** Data is stored on disk, can be large.

    • Data Frames: Stored in memory, limited in size (though adequate for many problems).

    1. Indexes:

    • Database tables usually have indexes to find rows efficiently.

    • Data frames and tibbles lack indexes, but data.tables have them.

    1. Database Optimization:

    • Classical databases tend to focus on fast data collection rather than analysis.

    • These databases are often row-oriented; however, column-oriented databases have emerged for faster analysis.

  • Types of Database Management Systems (DBMS):

    • Client-Server DBMS: Runs on a central server (e.g., PostgreSQL, MariaDB).

    • Cloud DBMS: (e.g., Snowflake, Amazon RedShift) handles large datasets and resource demands.

    • In-Process DBMS: (e.g., SQLite, duckdb) runs locally, suitable for individual users.

21.3 Connecting to a Database

  • To connect from R, use two packages:

    • DBI: Provides generic functions for database tasks (connect, upload data, run SQL).

    • A specific package per DBMS (e.g., RPostgres for PostgreSQL, RMariaDB for MySQL).

    • If unavailable, use the odbc package with the ODBC protocol (requires setup of ODBC driver).

  • Creating a Database Connection:

    • Use DBI::dbConnect():

    • First Argument: Select the DBMS

    • Subsequent Arguments: Specify connection details (location and credentials).

  • Connection Code Examples:

    • Typical examples of creating connections:
      R con <- DBI::dbConnect(RMariaDB::MariaDB(), username = "foo") con <- DBI::dbConnect(RPostgres::Postgres(), hostname = "databases.mycompany.com", port = 1234)

  • Conclusively, set up with duckdb for teaching:

    • Duckdb is simple and allows creating a temporary database deleted upon quitting R.

    • For more persistent database for projects, supply the dbdir argument to designate storage.

  • Adding Data to Duckdb:

    • Load datasets from ggplot2 with DBI::dbWriteTable():

    • Basic usage of dbWriteTable() requires:

    1. Connection

    2. Table name in DB

    3. Data frame

    • Recommended: Learn duckdb_read_csv() and duckdb_register_arrow() for efficient data loading.

  • Verify Loaded Data:

    • Use dbListTables() to list tables.

    • Use dbReadTable() to retrieve table contents, converting to tibble with as_tibble() for formatting.

  • Example SQL Query:

    • If familiar, use dbGetQuery() to execute SQL queries.

21.4 dbplyr Basics

  • Connect to Database and Load Data:

    • Use tbl() to create an object that references a database table.

  • Organization of Large Corporate Databases:

    • Use schemas or catalogs for managing tables.

  • Example Connection:

  con <- DBI::dbConnect(duckdb::duckdb())
  con <- DBI::dbConnect(duckdb::duckdb(), dbdir = "duckdb")
  • Loading Sample Data:

  dbWriteTable(con, "mpg", ggplot2::mpg)
  dbWriteTable(con, "diamonds", ggplot2::diamonds)
  • DBI Basics & Data Validation:

  dbListTables(con)
  con |> dbReadTable("diamonds") |> as_tibble()

21.5 SQL Introduction

  • Aim: Teach SQL through dbplyr; introducing concepts for quick learning.

  • Top-Level Components of SQL - Statements:

    • CREATE: Define new tables.

    • INSERT: Add data.

    • SELECT: Retrieve data - main focus.

  • Essential Clauses of a SQL Query:

    1. SELECT: Specifies the columns to retrieve.

    2. FROM: Indicates the data source.

    3. WHERE: Filters rows.

    4. ORDER BY: Specifies row orders.

    5. GROUP BY: Organizes data into groups for aggregation.

  • Basic Structure of a SQL Query:

    • Minimum form: SELECT * FROM table selects every column from specified table.

  • Example Usage:

  diamonds_db <- tbl(con, sql("SELECT * FROM diamonds"))
  big_diamonds_db <- diamonds_db |> filter(price > 15000) |> select(carat:clarity, price)
  • Query Representation:

    • Lazy evaluation when utilizing dplyr verbs: stores operations until execution.

  • Final Data Retrieval:

    • Use collect() to execute query and retrieve data into R.

21.5.1 SQL Basics

  • Exploration of Clauses:

    • SELECT with filtering:

  flights |> filter(dest == "IAH") |> arrange(dep_delay) |> show_query()
  • Understanding SQL Query Evaluation Order:

    • Clause order matters in execution:

    • Evaluation order = FROM > WHERE > GROUP BY > SELECT > ORDER BY.

  • Differences between dplyr and SQL:

    • SQL is case-insensitive but the conventions use uppercase for keywords.

    • SQL syntax requires specific order of clauses.

  • Example of GROUP BY:

  flights |> group_by(dest) |> summarize(dep_delay = mean(dep_delay, na.rm = TRUE)) |> show_query()

21.5.2 SELECT Clause and Functions

  • Translating dplyr functions:

    • select() => SELECT

    • mutate() => expression in SELECT

    • rename() or relocate() => affect column names and ordering in SELECT.

    • Example of Renaming:
      R SELECT year AS year_built FROM planes

  • Filtering rows with filter() translates to WHERE clause in SQL.

21.5.3 SQL Expressions and NULL Handling

  • Comparing NULL in SQL with R’s NA:

    • NULLs are dealt with using specific SQL functions.

    • Learn from external resources about SQL logic handling NULLs.

21.5.4 and onward - Learn about Subqueries, Joins, other Function translations within SQL context in the next segments

  • Details on each function, handling parameters, indexing mechanisms, and strategies on connecting SQL's capabilities with R language constructs.