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
.csvfile.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:
**Storage:
Databases:** Data is stored on disk, can be large.
Data Frames: Stored in memory, limited in size (though adequate for many problems).
Indexes:
Database tables usually have indexes to find rows efficiently.
Data frames and tibbles lack indexes, but data.tables have them.
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
dbdirargument to designate storage.
Adding Data to Duckdb:
Load datasets from ggplot2 with
DBI::dbWriteTable():Basic usage of
dbWriteTable()requires:
Connection
Table name in DB
Data frame
Recommended: Learn
duckdb_read_csv()andduckdb_register_arrow()for efficient data loading.
Verify Loaded Data:
Use
dbListTables()to list tables.Use
dbReadTable()to retrieve table contents, converting to tibble withas_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:
SELECT: Specifies the columns to retrieve.
FROM: Indicates the data source.
WHERE: Filters rows.
ORDER BY: Specifies row orders.
GROUP BY: Organizes data into groups for aggregation.
Basic Structure of a SQL Query:
Minimum form:
SELECT * FROM tableselects 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()=>SELECTmutate()=> expression inSELECTrename()orrelocate()=> affect column names and ordering inSELECT.Example of Renaming:
R SELECT year AS year_built FROM planes
Filtering rows with
filter()translates toWHEREclause 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.