21  Cơ sở dữ liệu

21.1 Giới thiệu

Một lượng dữ liệu khổng lồ nằm trong các database, vì vậy bạn cần biết cách truy cập chúng. Đôi khi bạn có thể nhờ ai đó tải một bản sao dữ liệu vào file .csv cho bạn, nhưng cách này nhanh chóng trở nên phiền phức: mỗi khi bạn cần thay đổi gì đó, bạn lại phải liên lạc với người khác. Bạn muốn có khả năng truy cập trực tiếp vào database để lấy dữ liệu mình cần, khi mình cần.

Trong chương này, trước tiên bạn sẽ học những kiến thức cơ bản về package DBI: cách sử dụng nó để kết nối tới database và sau đó truy xuất dữ liệu bằng một truy vấn (query) SQL1. SQL, viết tắt của structured query language (ngôn ngữ truy vấn có cấu trúc), là ngôn ngữ chung của các database, và là một ngôn ngữ quan trọng mà mọi nhà khoa học dữ liệu cần học. Tuy nhiên, chúng ta sẽ không bắt đầu với SQL, mà thay vào đó sẽ dạy bạn dbplyr, package có thể dịch mã dplyr của bạn sang SQL. Chúng ta sẽ dùng cách này để dạy bạn một số tính năng quan trọng nhất của SQL. Bạn sẽ không trở thành bậc thầy SQL khi kết thúc chương, nhưng bạn sẽ có thể nhận biết các thành phần quan trọng nhất và hiểu chúng làm gì.

21.1.1 Điều kiện tiên quyết

Trong chương này, chúng ta sẽ giới thiệu DBI và dbplyr. DBI là một giao diện cấp thấp (low-level interface) kết nối tới database và thực thi SQL; dbplyr là một giao diện cấp cao (high-level interface) dịch mã dplyr của bạn sang các truy vấn SQL rồi thực thi chúng bằng DBI.

21.2 Kiến thức cơ bản về database

Ở mức đơn giản nhất, bạn có thể hình dung database như một tập hợp các data frame, được gọi là bảng (table) trong thuật ngữ database. Giống như data frame, một bảng database là một tập hợp các column có tên, trong đó mọi giá trị trong cùng một column đều cùng kiểu dữ liệu. Có ba điểm khác biệt lớn giữa data frame và bảng database:

  • Bảng database được lưu trữ trên đĩa và có thể lớn tùy ý. Khung dữ liệu được lưu trong bộ nhớ, và về cơ bản bị giới hạn kích thước (dù giới hạn này vẫn đủ lớn cho nhiều bài toán).

  • Bảng database hầu như luôn có chỉ mục (index). Giống như mục lục của một cuốn sách, chỉ mục database giúp tìm nhanh các row cần thiết mà không phải duyệt qua từng row một. Khung dữ liệu và tibble không có chỉ mục, nhưng data.table thì có, đó là một trong những lý do khiến chúng rất nhanh.

  • Hầu hết các database truyền thống được tối ưu hóa cho việc thu thập dữ liệu nhanh chóng, không phải phân tích dữ liệu có sẵn. Các database này được gọi là hướng hàng (row-oriented) vì dữ liệu được lưu trữ theo từng row, thay vì theo từng column như R. Gần đây, đã có nhiều phát triển về database hướng cột (column-oriented) giúp việc phân tích dữ liệu có sẵn nhanh hơn nhiều.

Cơ sở dữ liệu được vận hành bởi các hệ quản trị database (viết tắt là DBMS), có ba dạng cơ bản:

  • DBMS client-server chạy trên một máy chủ trung tâm mạnh mẽ, mà bạn kết nối từ máy tính của mình (client). Chúng rất phù hợp để chia sẻ dữ liệu với nhiều người trong tổ chức. Các DBMS client-server phổ biến bao gồm PostgreSQL, MariaDB, SQL Server, và Oracle.
  • DBMS đám mây (cloud), như Snowflake, Amazon’s RedShift, và Google’s BigQuery, tương tự DBMS client-server, nhưng chạy trên đám mây. Điều này có nghĩa là chúng có thể dễ dàng xử lý các tập dữ liệu cực lớn và tự động cung cấp thêm tài nguyên tính toán khi cần.
  • DBMS trong tiến trình (in-process), như SQLite hay duckdb, chạy hoàn toàn trên máy tính của bạn. Chúng rất phù hợp để làm việc với tập dữ liệu lớn khi bạn là người dùng chính.

21.3 Kết nối tới database

Để kết nối tới database từ R, bạn sẽ sử dụng một cặp package:

  • Bạn sẽ luôn dùng DBI (database interface) vì nó cung cấp một tập hợp các function tổng quát để kết nối tới database, tải dữ liệu lên, chạy truy vấn SQL, v.v.

  • Bạn cũng sẽ dùng một package được thiết kế riêng cho DBMS mà bạn đang kết nối. Gói mở rộng này dịch các lệnh DBI tổng quát thành các chi tiết cụ thể cần thiết cho từng DBMS. Thường mỗi DBMS có một package riêng, ví dụ RPostgres cho PostgreSQL và RMariaDB cho MySQL.

Nếu bạn không tìm được package cụ thể cho DBMS của mình, bạn thường có thể dùng package odbc thay thế. Gói mở rộng này sử dụng giao thức ODBC được nhiều DBMS hỗ trợ. odbc yêu cầu thêm một chút thiết lập vì bạn cũng cần cài đặt driver ODBC và cho package odbc biết nơi tìm nó.

Cụ thể, bạn tạo kết nối database bằng DBI::dbConnect(). Đối số đầu tiên chọn DBMS2, sau đó argument thứ hai và các argument tiếp theo mô tả cách kết nối tới nó (tức là nó ở đâu và thông tin xác thực bạn cần để truy cập). Đoạn mã sau đây cho thấy vài ví dụ điển hình:

con <- DBI::dbConnect(
  RMariaDB::MariaDB(),
  username = "foo"
)
con <- DBI::dbConnect(
  RPostgres::Postgres(),
  hostname = "databases.mycompany.com",
  port = 1234
)

Chi tiết chính xác của kết nối khác nhau rất nhiều giữa các DBMS nên tiếc là chúng tôi không thể trình bày hết ở đây. Điều này có nghĩa là bạn sẽ cần tự tìm hiểu thêm một chút. Thường bạn có thể hỏi các nhà khoa học dữ liệu khác trong nhóm hoặc nói chuyện với DBA (database administrator – quản trị viên database) của bạn. Việc thiết lập ban đầu thường cần mày mò một chút (và có thể phải tìm kiếm trên Google), nhưng bạn thường chỉ cần làm một lần.

21.3.1 Trong cuốn sách này

Việc thiết lập DBMS client-server hoặc đám mây sẽ khá phiền phức cho cuốn sách này, nên thay vào đó chúng ta sẽ dùng DBMS trong tiến trình nằm hoàn toàn trong một package R: duckdb. Nhờ sự kỳ diệu của DBI, điểm khác biệt duy nhất giữa việc dùng duckdb và bất kỳ DBMS nào khác là cách bạn kết nối tới database. Điều này khiến nó rất phù hợp để dạy học vì bạn có thể dễ dàng chạy mã này cũng như áp dụng những gì đã học vào nơi khác.

Kết nối tới duckdb đặc biệt đơn giản vì các giá trị mặc định tạo ra một database tạm thời sẽ bị xóa khi bạn thoát R. Điều này rất tốt cho việc học vì nó đảm bảo bạn luôn bắt đầu từ đầu mỗi khi khởi động lại R:

con <- DBI::dbConnect(duckdb::duckdb())

duckdb là một database hiệu năng cao được thiết kế rất phù hợp cho nhu cầu của nhà khoa học dữ liệu. Chúng ta dùng nó ở đây vì rất dễ bắt đầu, nhưng nó cũng có khả năng xử lý row gigabyte dữ liệu với tốc độ cao. Nếu bạn muốn dùng duckdb cho một dự án phân tích dữ liệu thực tế, bạn cũng cần cung cấp argument dbdir để tạo database lâu dài và cho duckdb biết nơi lưu nó. Giả sử bạn đang dùng dự án (project) (Chương 6), việc lưu nó trong thư mục duckdb của dự án hiện tại là hợp lý:

con <- DBI::dbConnect(duckdb::duckdb(), dbdir = "duckdb")

21.3.2 Tải dữ liệu

Vì đây là database mới, chúng ta cần bắt đầu bằng việc thêm dữ liệu vào. Ở đây chúng ta sẽ thêm các tập dữ liệu mpg và diamonds từ ggplot2 bằng DBI::dbWriteTable(). Cách dùng đơn giản nhất của dbWriteTable() cần ba argument: một kết nối database, tên bảng cần tạo trong database, và một data frame chứa dữ liệu.

dbWriteTable(con, "mpg", ggplot2::mpg)
dbWriteTable(con, "diamonds", ggplot2::diamonds)

Nếu bạn đang dùng duckdb trong một dự án thực tế, chúng tôi rất khuyến khích bạn tìm hiểu về duckdb_read_csv() và duckdb_register_arrow(). Chúng cung cấp cho bạn những cách mạnh mẽ và hiệu quả để tải dữ liệu trực tiếp vào duckdb, mà không cần phải tải vào R trước. Chúng ta cũng sẽ trình bày một kỹ thuật hữu ích để tải nhiều file vào database trong Phần 26.4.1.

21.3.3 Kiến thức cơ bản về DBI

Bạn có thể kiểm tra xem dữ liệu đã được tải đúng chưa bằng một vài function DBI khác: dbListTables() liệt kê tất cả các bảng trong database3 và dbReadTable() truy xuất nội dung của một bảng.

dbListTables(con)
#> [1] "diamonds" "mpg"

con |>
  dbReadTable("diamonds") |>
  as_tibble()
#> # A tibble: 53,940 × 10
#>   carat cut       color clarity depth table price     x     y     z
#>   <dbl> <fct>     <fct> <fct>   <dbl> <dbl> <int> <dbl> <dbl> <dbl>
#> 1  0.23 Ideal     E     SI2      61.5    55   326  3.95  3.98  2.43
#> 2  0.21 Premium   E     SI1      59.8    61   326  3.89  3.84  2.31
#> 3  0.23 Good      E     VS1      56.9    65   327  4.05  4.07  2.31
#> 4  0.29 Premium   I     VS2      62.4    58   334  4.2   4.23  2.63
#> 5  0.31 Good      J     SI2      63.3    58   335  4.34  4.35  2.75
#> 6  0.24 Very Good J     VVS2     62.8    57   336  3.94  3.96  2.48
#> # ℹ 53,934 more rows

dbReadTable() trả về một data.frame nên chúng ta dùng as_tibble() để chuyển đổi nó thành tibble để in ra đẹp hơn.

Nếu bạn đã biết SQL, bạn có thể dùng dbGetQuery() để lấy kết quả của việc chạy một truy vấn trên database:

sql <- "
  SELECT carat, cut, clarity, color, price
  FROM diamonds
  WHERE price > 15000
"
as_tibble(dbGetQuery(con, sql))
#> # A tibble: 1,655 × 5
#>   carat cut       clarity color price
#>   <dbl> <fct>     <fct>   <fct> <int>
#> 1  1.54 Premium   VS2     E     15002
#> 2  1.19 Ideal     VVS1    F     15005
#> 3  2.1  Premium   SI1     I     15007
#> 4  1.69 Ideal     SI1     D     15011
#> 5  1.5  Very Good VVS2    G     15013
#> 6  1.73 Very Good VS1     G     15014
#> # ℹ 1,649 more rows

Nếu bạn chưa từng thấy SQL trước đây, đừng lo! Bạn sẽ tìm hiểu thêm về nó ngay thôi. Nhưng nếu đọc kỹ, bạn có thể đoán rằng nó chọn năm column của tập dữ liệu diamonds và tất cả các row mà price lớn hơn 15.000.

21.4 Kiến thức cơ bản về dbplyr

Bây giờ chúng ta đã kết nối tới database và tải lên một số dữ liệu, chúng ta có thể bắt đầu tìm hiểu về dbplyr. dbplyr là một backend của dplyr, nghĩa là bạn vẫn viết mã dplyr nhưng backend thực thi nó theo cách khác. Ở đây, dbplyr dịch sang SQL; các backend khác bao gồm dtplyr dịch sang data.table, và multidplyr thực thi mã của bạn trên nhiều lõi.

Để dùng dbplyr, trước tiên bạn phải dùng tbl() để tạo một đối tượng đại diện cho một bảng database:

diamonds_db <- tbl(con, "diamonds")
diamonds_db
#> # Source:   table<diamonds> [?? x 10]
#> # Database: DuckDB 1.5.1 [root@Darwin 25.3.0:R 4.5.3/:memory:]
#>   carat cut       color clarity depth table price     x     y     z
#>   <dbl> <fct>     <fct> <fct>   <dbl> <dbl> <int> <dbl> <dbl> <dbl>
#> 1  0.23 Ideal     E     SI2      61.5    55   326  3.95  3.98  2.43
#> 2  0.21 Premium   E     SI1      59.8    61   326  3.89  3.84  2.31
#> 3  0.23 Good      E     VS1      56.9    65   327  4.05  4.07  2.31
#> 4  0.29 Premium   I     VS2      62.4    58   334  4.2   4.23  2.63
#> 5  0.31 Good      J     SI2      63.3    58   335  4.34  4.35  2.75
#> 6  0.24 Very Good J     VVS2     62.8    57   336  3.94  3.96  2.48
#> # ℹ more rows

Có hai cách phổ biến khác để tương tác với database. Thứ nhất, nhiều database doanh nghiệp rất lớn nên bạn cần một hệ phân cấp để tổ chức tất cả các bảng. Trong trường hợp đó, bạn có thể cần cung cấp một schema, hoặc một catalog và một schema, để chọn bảng bạn muốn:

diamonds_db <- tbl(con, in_schema("sales", "diamonds"))
diamonds_db <- tbl(con, in_catalog("north_america", "sales", "diamonds"))

Những lúc khác bạn có thể muốn dùng truy vấn SQL riêng làm điểm bắt đầu:

diamonds_db <- tbl(con, sql("SELECT * FROM diamonds"))

Đối tượng này là lười (lazy); khi bạn dùng các function dplyr trên nó, dplyr không thực hiện công việc nào: nó chỉ ghi lại string thao tác mà bạn muốn thực hiện và chỉ thực thi khi cần thiết. Ví dụ, hãy xem pipeline sau:

big_diamonds_db <- diamonds_db |>
  filter(price > 15000) |>
  select(carat:clarity, price)

big_diamonds_db
#> # Source:   SQL [?? x 5]
#> # Database: DuckDB 1.5.1 [root@Darwin 25.3.0:R 4.5.3/:memory:]
#>   carat cut       color clarity price
#>   <dbl> <fct>     <fct> <fct>   <int>
#> 1  1.54 Premium   E     VS2     15002
#> 2  1.19 Ideal     F     VVS1    15005
#> 3  2.1  Premium   I     SI1     15007
#> 4  1.69 Ideal     D     SI1     15011
#> 5  1.5  Very Good G     VVS2    15013
#> 6  1.73 Very Good G     VS1     15014
#> # ℹ more rows

Bạn có thể nhận biết đối tượng này đại diện cho một truy vấn database vì nó in tên DBMS ở trên cùng, và mặc dù nó cho bạn biết số column, nhưng thường không biết số row. Đó là vì việc tìm tổng số row thường yêu cầu thực thi toàn bộ truy vấn, điều mà chúng ta đang cố tránh.

Bạn có thể xem mã SQL được tạo ra bằng function dplyr show_query(). Nếu bạn biết dplyr, đây là cách tuyệt vời để học SQL! Viết mã dplyr, để dbplyr dịch sang SQL, rồi cố gắng tìm hiểu xem hai ngôn ngữ khớp nhau như thế nào.

big_diamonds_db |>
  show_query()
#> <SQL>
#> SELECT carat, cut, color, clarity, price
#> FROM diamonds
#> WHERE (price > 15000.0)

Để lấy tất cả dữ liệu trở lại R, bạn gọi collect(). Phía sau, nó tạo SQL, gọi dbGetQuery() để lấy dữ liệu, rồi chuyển kết quả thành tibble:

big_diamonds <- big_diamonds_db |>
  collect()
big_diamonds
#> # A tibble: 1,655 × 5
#>   carat cut       color clarity price
#>   <dbl> <fct>     <fct> <fct>   <int>
#> 1  1.54 Premium   E     VS2     15002
#> 2  1.19 Ideal     F     VVS1    15005
#> 3  2.1  Premium   I     SI1     15007
#> 4  1.69 Ideal     D     SI1     15011
#> 5  1.5  Very Good G     VVS2    15013
#> 6  1.73 Very Good G     VS1     15014
#> # ℹ 1,649 more rows

Thông thường, bạn sẽ dùng dbplyr để chọn dữ liệu bạn cần từ database, thực hiện lọc và tổng hợp cơ bản bằng các phép dịch được mô tả bên dưới. Sau đó, khi bạn sẵn sàng phân tích dữ liệu với các function chỉ có trong R, bạn sẽ collect() dữ liệu để lấy tibble trong bộ nhớ, và tiếp tục công việc với mã R thuần túy.

21.5 SQL

Phần còn lại của chương sẽ dạy bạn một chút SQL thông qua lăng kính của dbplyr. Đây là cách giới thiệu SQL khá phi truyền thống nhưng chúng tôi hy vọng nó sẽ giúp bạn nhanh chóng nắm được kiến thức cơ bản. May mắn thay, nếu bạn hiểu dplyr, bạn đang ở vị trí tuyệt vời để nhanh chóng tiếp thu SQL vì rất nhiều khái niệm giống nhau.

Chúng ta sẽ khám phá mối quan hệ giữa dplyr và SQL bằng một vài người bạn cũ từ package nycflights13: flights và planes. Các tập dữ liệu này dễ dàng đưa vào database học tập vì dbplyr có sẵn một function sao chép các bảng từ nycflights13 vào database của chúng ta:

dbplyr::copy_nycflights13(con)
#> Creating table: airlines
#> Creating table: airports
#> Creating table: flights
#> Creating table: planes
#> Creating table: weather
flights <- tbl(con, "flights")
planes <- tbl(con, "planes")

21.5.1 Kiến thức cơ bản về SQL

Các thành phần cấp cao nhất của SQL được gọi là câu lệnh (statement). Các câu lệnh phổ biến bao gồm CREATE để định nghĩa bảng mới, INSERT để thêm dữ liệu, và SELECT để truy xuất dữ liệu. Chúng ta sẽ tập trung vào câu lệnh SELECT, còn được gọi là truy vấn (query), vì đây gần như là thứ duy nhất bạn sẽ dùng với tư cách nhà khoa học dữ liệu.

Một truy vấn được tạo thành từ các mệnh đề (clause). Có năm mệnh đề quan trọng: SELECT, FROM, WHERE, ORDER BY, và GROUP BY. Mọi truy vấn đều phải có mệnh đề SELECT4 và FROM5 và truy vấn đơn giản nhất là SELECT * FROM table, chọn tất cả các column từ bảng được chỉ định . Đây là những gì dbplyr tạo ra cho một bảng chưa được xử lý :

flights |> show_query()
#> <SQL>
#> SELECT *
#> FROM flights
planes |> show_query()
#> <SQL>
#> SELECT *
#> FROM planes

WHERE và ORDER BY kiểm soát row nào được bao gồm và cách sắp xếp chúng:

flights |>
  filter(dest == "IAH") |>
  arrange(dep_delay) |>
  show_query()
#> <SQL>
#> SELECT flights.*
#> FROM flights
#> WHERE (dest = 'IAH')
#> ORDER BY dep_delay

GROUP BY chuyển truy vấn thành tóm tắt, khiến phép tổng hợp (aggregation) xảy ra:

flights |>
  group_by(dest) |>
  summarize(dep_delay = mean(dep_delay, na.rm = TRUE)) |>
  show_query()
#> <SQL>
#> SELECT dest, AVG(dep_delay) AS dep_delay
#> FROM flights
#> GROUP BY dest

Có hai điểm khác biệt quan trọng giữa các function dplyr và mệnh đề SELECT:

  • Trong SQL, chữ hoa chữ thường không quan trọng: bạn có thể viết select, SELECT, hoặc thậm chí SeLeCt. Trong cuốn sách này, chúng ta sẽ theo quy ước phổ biến là viết các từ khóa SQL bằng CHỮ HOA để phân biệt chúng với tên bảng hoặc tên biến.
  • Trong SQL, thứ tự quan trọng: bạn phải luôn viết các mệnh đề theo thứ tự SELECT, FROM, WHERE, GROUP BY, ORDER BY. Gây nhầm lẫn là, thứ tự này không khớp với thứ tự thực thi thực tế, đó là FROM trước, rồi WHERE, GROUP BY, SELECT, và ORDER BY.

Các phần sau sẽ khám phá chi tiết hơn từng mệnh đề.

Lưu ý rằng mặc dù SQL là một tiêu chuẩn, nó cực kỳ phức tạp và không có database nào tuân theo nó chính xác hoàn toàn. Mặc dù các thành phần chính mà chúng ta tập trung trong cuốn sách này rất giống nhau giữa các DBMS, vẫn có nhiều khác biệt nhỏ. May mắn thay, dbplyr được thiết kế để xử lý vấn đề này và tạo ra các bản dịch khác nhau cho các database khác nhau. Nó không hoàn hảo, nhưng liên tục được cải thiện, và nếu bạn gặp vấn đề, bạn có thể tạo issue trên GitHub để giúp chúng tôi làm tốt hơn.

21.5.2 SELECT

Mệnh đề SELECT là trụ column của các truy vấn và thực hiện cùng công việc với select(), mutate(), rename(), relocate(), và, như bạn sẽ học trong phần tiếp theo, summarize().

select(), rename(), và relocate() có các bản dịch rất trực tiếp sang SELECT vì chúng chỉ ảnh hưởng đến vị trí xuất hiện của column (nếu có) cùng với tên của nó:

planes |>
  select(tailnum, type, manufacturer, model, year) |>
  show_query()
#> <SQL>
#> SELECT tailnum, "type", manufacturer, model, "year"
#> FROM planes

planes |>
  select(tailnum, type, manufacturer, model, year) |>
  rename(year_built = year) |>
  show_query()
#> <SQL>
#> SELECT tailnum, "type", manufacturer, model, "year" AS year_built
#> FROM planes

planes |>
  select(tailnum, type, manufacturer, model, year) |>
  relocate(manufacturer, model, .before = type) |>
  show_query()
#> <SQL>
#> SELECT tailnum, manufacturer, model, "type", "year"
#> FROM planes

Ví dụ này cũng cho bạn thấy cách SQL thực hiện đổi tên. Trong thuật ngữ SQL, đổi tên được gọi là đặt bí danh (aliasing) và được thực hiện bằng AS. Lưu ý rằng khác với mutate(), tên cũ nằm bên trái và tên mới nằm bên phải.

:: callout-note Trong các ví dụ trên, lưu ý rằng "year" và "type" được bao trong dấu nháy kép. Đó là vì chúng là các từ dành riêng (reserved word) trong duckdb, nên dbplyr đặt chúng trong nháy kép để tránh nhầm lẫn giữa tên cột/bảng và toán tử SQL.

Khi làm việc với các database khác, bạn có thể thấy mọi tên biến đều được đặt trong nháy kép vì chỉ một số ít package client, như duckdb, biết tất cả các từ dành riêng, nên chúng đặt nháy kép cho mọi thứ để an toàn.

SELECT "tailnum", "type", "manufacturer", "model", "year"
FROM "planes"

Một số hệ database khác dùng dấu backtick thay vì dấu nháy kép:

SELECT `tailnum`, `type`, `manufacturer`, `model`, `year`
FROM `planes`

:::

Các bản dịch cho mutate() cũng tương tự trực tiếp: mỗi biến trở thành một biểu thức mới trong SELECT:

flights |>
  mutate(
    speed = distance / (air_time / 60)
  ) |>
  show_query()
#> <SQL>
#> SELECT flights.*, distance / (air_time / 60.0) AS speed
#> FROM flights

Chúng ta sẽ quay lại bản dịch của các thành phần riêng lẻ (như /) trong Phần 21.6.

21.5.3 FROM

Mệnh đề FROM định nghĩa nguồn dữ liệu. Nó sẽ khá đơn điệu trong một lúc, vì chúng ta chỉ dùng các bảng đơn lẻ. Bạn sẽ thấy các ví dụ phức tạp hơn khi chúng ta tới các function nối (join).

21.5.4 GROUP BY

group_by() được dịch sang mệnh đề GROUP BY6 và summarize() được dịch sang mệnh đề SELECT:

diamonds_db |>
  group_by(cut) |>
  summarize(
    n = n(),
    avg_price = mean(price, na.rm = TRUE)
  ) |>
  show_query()
#> <SQL>
#> SELECT cut, COUNT(*) AS n, AVG(price) AS avg_price
#> FROM diamonds
#> GROUP BY cut

Chúng ta sẽ quay lại bản dịch của n() và mean() trong Phần 21.6.

21.5.5 WHERE

filter() được dịch sang mệnh đề WHERE:

flights |>
  filter(dest == "IAH" | dest == "HOU") |>
  show_query()
#> <SQL>
#> SELECT flights.*
#> FROM flights
#> WHERE (dest = 'IAH' OR dest = 'HOU')

flights |>
  filter(arr_delay > 0 & arr_delay < 20) |>
  show_query()
#> <SQL>
#> SELECT flights.*
#> FROM flights
#> WHERE (arr_delay > 0.0 AND arr_delay < 20.0)

Có một vài chi tiết quan trọng cần lưu ý ở đây:

  • | trở thành OR và & trở thành AND.
  • SQL dùng = để so sánh, không phải ==. SQL không có phép gán, nên không có khả năng nhầm lẫn ở đây.
  • SQL chỉ dùng '' cho string, không phải "". Trong SQL, "" được dùng để xác định biến, giống như `` trong R.

Một toán tử SQL hữu ích khác là IN, rất giống với %in% trong R:

flights |>
  filter(dest %in% c("IAH", "HOU")) |>
  show_query()
#> <SQL>
#> SELECT flights.*
#> FROM flights
#> WHERE (dest IN ('IAH', 'HOU'))

SQL dùng NULL thay vì NA. NULL hoạt động tương tự như NA. Sự khác biệt chính là mặc dù chúng “lây lan” trong các phép so sánh và phép tính, chúng bị bỏ qua một cách im lặng khi tóm tắt. dbplyr sẽ nhắc bạn về hành vi này lần đầu tiên bạn gặp nó:

flights |>
  group_by(dest) |>
  summarize(delay = mean(arr_delay))
#> Warning: Missing values are always removed in SQL aggregation functions.
#> Use `na.rm = TRUE` to silence this warning
#> This warning is displayed once every 8 hours.
#> # Source:   SQL [?? x 2]
#> # Database: DuckDB 1.5.1 [root@Darwin 25.3.0:R 4.5.3/:memory:]
#>   dest  delay
#>   <chr> <dbl>
#> 1 CLT    7.36
#> 2 MSY    6.49
#> 3 XNA    7.47
#> 4 ORF   10.9 
#> 5 ROC   11.6 
#> 6 ABQ    4.38
#> # ℹ more rows

Nếu bạn muốn tìm hiểu thêm về cách NULL hoạt động, bạn có thể thích bài viết “The Three-Valued Logic of SQL” của Markus Winand.

Nói chung, bạn có thể làm việc với NULL bằng các function mà bạn dùng cho NA trong R:

flights |>
  filter(!is.na(dep_delay)) |>
  show_query()
#> <SQL>
#> SELECT flights.*
#> FROM flights
#> WHERE (NOT((dep_delay IS NULL)))

Truy vấn SQL này minh họa một trong những nhược điểm của dbplyr: mặc dù SQL đúng, nó không đơn giản như khi bạn tự viết bằng tay. Trong trường hợp này, bạn có thể bỏ dấu ngoặc đơn và dùng một toán tử đặc biệt dễ đọc hơn:

WHERE "dep_delay" IS NOT NULL

Lưu ý rằng nếu bạn filter() một biến mà bạn đã tạo bằng summarize(), dbplyr sẽ tạo ra mệnh đề HAVING, thay vì mệnh đề WHERE. Đây là một trong những điều đặc biệt của SQL: WHERE được đánh giá trước SELECT và GROUP BY, nên SQL cần một mệnh đề khác được đánh giá sau đó.

diamonds_db |>
  group_by(cut) |>
  summarize(n = n()) |>
  filter(n > 100) |>
  show_query()
#> <SQL>
#> SELECT cut, COUNT(*) AS n
#> FROM diamonds
#> GROUP BY cut
#> HAVING (COUNT(*) > 100.0)

21.5.6 ORDER BY

Sắp xếp row là một bản dịch trực tiếp từ arrange() sang mệnh đề ORDER BY:

flights |>
  arrange(year, month, day, desc(dep_delay)) |>
  show_query()
#> <SQL>
#> SELECT flights.*
#> FROM flights
#> ORDER BY "year", "month", "day", dep_delay DESC

Lưu ý cách desc() được dịch thành DESC: đây là một trong nhiều function dplyr có tên được lấy cảm hứng trực tiếp từ SQL.

21.5.7 Truy vấn con

Đôi khi không thể dịch một pipeline dplyr thành một câu lệnh SELECT duy nhất và bạn cần dùng truy vấn con (subquery). Một truy vấn con (subquery) đơn giản là một truy vấn được dùng làm nguồn dữ liệu trong mệnh đề FROM, thay vì bảng thông thường.

dbplyr thường dùng truy vấn con để khắc phục các giới hạn của SQL. Ví dụ, các biểu thức trong mệnh đề SELECT không thể tham chiếu đến các column vừa mới được tạo. Điều đó có nghĩa là pipeline dplyr (ngớ ngẩn) sau đây cần xảy ra trong hai bước: truy vấn đầu tiên (bên trong) tính year1 rồi truy vấn thứ hai (bên ngoài) có thể tính year2.

flights |>
  mutate(
    year1 = year + 1,
    year2 = year1 + 1
  ) |>
  show_query()
#> <SQL>
#> SELECT q01.*, year1 + 1.0 AS year2
#> FROM (
#>   SELECT flights.*, "year" + 1.0 AS year1
#>   FROM flights
#> ) q01

Bạn cũng sẽ thấy điều này nếu cố filter() một biến mà bạn vừa tạo. Nhớ rằng, mặc dù WHERE được viết sau SELECT, nó được đánh giá trước, nên chúng ta cần truy vấn con trong ví dụ (ngớ ngẩn) này:

flights |>
  mutate(year1 = year + 1) |>
  filter(year1 == 2014) |>
  show_query()
#> <SQL>
#> SELECT q01.*
#> FROM (
#>   SELECT flights.*, "year" + 1.0 AS year1
#>   FROM flights
#> ) q01
#> WHERE (year1 = 2014.0)

Đôi khi dbplyr sẽ tạo truy vấn con khi không cần thiết vì nó chưa biết cách tối ưu bản dịch đó. Khi dbplyr cải thiện theo thời gian, các trường hợp này sẽ ít đi nhưng có lẽ không bao giờ biến mất hoàn toàn.

21.5.8 Nối bảng

Nếu bạn quen thuộc với các phép nối (join) của dplyr, các phép nối SQL rất giống. Đây là một ví dụ đơn giản:

flights |>
  left_join(planes |> rename(year_built = year), join_by(tailnum)) |>
  show_query()
#> <SQL>
#> SELECT
#>   flights.*,
#>   planes."year" AS year_built,
#>   "type",
#>   manufacturer,
#>   model,
#>   engines,
#>   seats,
#>   speed,
#>   engine
#> FROM flights
#> LEFT JOIN planes
#>   ON (flights.tailnum = planes.tailnum)

Điều chính cần lưu ý ở đây là cú pháp: các phép nối SQL dùng các mệnh đề con của mệnh đề FROM để đưa thêm bảng vào, dùng ON để định nghĩa cách các bảng liên kết với nhau.

Tên các function dplyr liên kết chặt chẽ với SQL đến mức bạn có thể dễ dàng đoán SQL tương đương cho inner_join(), right_join(), và full_join():

SELECT flights.*, "type", manufacturer, model, engines, seats, speed
FROM flights
INNER JOIN planes ON (flights.tailnum = planes.tailnum)

SELECT flights.*, "type", manufacturer, model, engines, seats, speed
FROM flights
RIGHT JOIN planes ON (flights.tailnum = planes.tailnum)

SELECT flights.*, "type", manufacturer, model, engines, seats, speed
FROM flights
FULL JOIN planes ON (flights.tailnum = planes.tailnum)

Bạn có thể sẽ cần nhiều phép nối khi làm việc với dữ liệu từ database. Đó là vì các bảng database thường được lưu ở dạng chuẩn hóa (normalized) cao, trong đó mỗi “sự kiện” được lưu ở một nơi duy nhất và để có tập dữ liệu đầy đủ cho phân tích, bạn cần điều hướng qua một mạng lưới phức tạp các bảng được kết nối bằng khóa chính (primary key) và khóa ngoại (foreign key). Nếu bạn gặp tình huống này, package dm, bởi Tobias Schieferdecker, Kirill Muller, và Darko Bergant, là cứu cánh. Nó có thể tự động xác định các kết nối giữa các bảng bằng các ràng buộc mà DBA thường cung cấp, visualization các kết nối để bạn thấy được bức tranh toàn cảnh, và tạo ra các phép nối bạn cần để kết nối bảng này với bảng khác.

21.5.9 Các function khác

dbplyr cũng dịch các function khác như distinct(), slice_*(), và intersect(), cùng ngày càng nhiều function tidyr như pivot_longer() và pivot_wider(). Cách dễ nhất để xem list đầy đủ những gì hiện có là truy cập trang web dbplyr: https://dbplyr.tidyverse.org/reference/.

21.5.10 Bài tập

  1. distinct() được dịch thành gì? Còn head() thì sao?

  2. Giải thích mỗi truy vấn SQL sau đây làm gì và thử tạo lại chúng bằng dbplyr.

    SELECT *
FROM flights
WHERE dep_delay < arr_delay

SELECT *, distance / (air_time / 60) AS speed
FROM flights

21.6 Dịch function

Cho đến giờ chúng ta đã tập trung vào bức tranh toàn cảnh về cách các function dplyr được dịch sang các mệnh đề của truy vấn. Bây giờ chúng ta sẽ phóng to một chút và nói về cách dịch các function R làm việc với từng column riêng lẻ, ví dụ, chuyện gì xảy ra khi bạn dùng mean(x) trong summarize()?

Để giúp thấy rõ điều gì đang xảy ra, chúng ta sẽ dùng một vài function trợ giúp nhỏ chạy summarize() hoặc mutate() và hiển thị SQL được tạo ra. Điều đó sẽ giúp dễ dàng hơn trong việc khám phá một vài biến thể và xem sự khác biệt giữa tóm tắt và biến đổi.

summarize_query <- function(df, ...) {
  df |>
    summarize(...) |>
    show_query()
}
mutate_query <- function(df, ...) {
  df |>
    mutate(..., .keep = "none") |>
    show_query()
}

Hãy bắt đầu với một số phép tóm tắt! Nhìn vào đoạn mã bên dưới, bạn sẽ thấy rằng một số function tóm tắt, như mean(), có bản dịch tương đối đơn giản trong khi các function khác, như median(), phức tạp hơn nhiều. Độ phức tạp thường cao hơn cho các phép toán phổ biến trong thống kê nhưng ít phổ biến trong database.

flights |>
  group_by(year, month, day) |>
  summarize_query(
    mean = mean(arr_delay, na.rm = TRUE),
    median = median(arr_delay, na.rm = TRUE)
  )
#> `summarise()` has grouped output by "year" and "month". You can override
#> using the `.groups` argument.
#> <SQL>
#> SELECT
#>   "year",
#>   "month",
#>   "day",
#>   AVG(arr_delay) AS mean,
#>   MEDIAN(arr_delay) AS median
#> FROM flights
#> GROUP BY "year", "month", "day"

Bản dịch của các function tóm tắt trở nên phức tạp hơn khi bạn dùng chúng bên trong mutate() vì chúng phải trở thành cái gọi là function cửa sổ (window function). Trong SQL, bạn biến một function tổng hợp thông thường thành function cửa sổ bằng cách thêm OVER sau nó:

flights |>
  group_by(year, month, day) |>
  mutate_query(
    mean = mean(arr_delay, na.rm = TRUE),
  )
#> <SQL>
#> SELECT
#>   "year",
#>   "month",
#>   "day",
#>   AVG(arr_delay) OVER (PARTITION BY "year", "month", "day") AS mean
#> FROM flights

Trong SQL, mệnh đề GROUP BY được dùng riêng cho các phép tóm tắt nên ở đây bạn có thể thấy nhóm đã chuyển từ mệnh đề GROUP BY sang OVER.

Function cửa sổ bao gồm tất cả các function nhìn về phía trước hoặc phía sau, như lead() và lag() lần lượt nhìn vào giá trị “tiếp theo” hoặc “trước đó”:

flights |>
  group_by(dest) |>
  arrange(time_hour) |>
  mutate_query(
    lead = lead(arr_delay),
    lag = lag(arr_delay)
  )
#> <SQL>
#> SELECT
#>   dest,
#>   LEAD(arr_delay, 1, NULL) OVER (PARTITION BY dest ORDER BY time_hour) AS lead,
#>   LAG(arr_delay, 1, NULL) OVER (PARTITION BY dest ORDER BY time_hour) AS lag
#> FROM flights
#> ORDER BY time_hour

Ở đây điều quan trọng là phải arrange() dữ liệu, vì các bảng SQL không có thứ tự cố định. Thực tế, nếu bạn không dùng arrange(), bạn có thể nhận được các row theo thứ tự khác nhau mỗi lần! Lưu ý rằng đối với function cửa sổ, thông tin sắp xếp được iterate lại: mệnh đề ORDER BY của truy vấn chính không tự động áp dụng cho function cửa sổ.

Một function SQL quan trọng khác là CASE WHEN. Nó được dùng làm bản dịch của if_else() và case_when(), function dplyr mà nó trực tiếp truyền cảm hứng. Đây là một vài ví dụ đơn giản:

flights |>
  mutate_query(
    description = if_else(arr_delay > 0, "delayed", "on-time")
  )
#> <SQL>
#> SELECT CASE WHEN (arr_delay > 0.0) THEN 'delayed' WHEN NOT (arr_delay > 0.0) THEN 'on-time' END AS description
#> FROM flights
flights |>
  mutate_query(
    description =
      case_when(
        arr_delay < -5 ~ "early",
        arr_delay < 5 ~ "on-time",
        arr_delay >= 5 ~ "late"
      )
  )
#> <SQL>
#> SELECT CASE
#> WHEN (arr_delay < -5.0) THEN 'early'
#> WHEN (arr_delay < 5.0) THEN 'on-time'
#> WHEN (arr_delay >= 5.0) THEN 'late'
#> END AS description
#> FROM flights

CASE WHEN cũng được dùng cho một số function khác không có bản dịch trực tiếp từ R sang SQL. Một ví dụ điển hình là cut():

flights |>
  mutate_query(
    description =  cut(
      arr_delay,
      breaks = c(-Inf, -5, 5, Inf),
      labels = c("early", "on-time", "late")
    )
  )
#> <SQL>
#> SELECT CASE
#> WHEN (arr_delay <= -5.0) THEN 'early'
#> WHEN (arr_delay <= 5.0) THEN 'on-time'
#> WHEN (arr_delay > 5.0) THEN 'late'
#> END AS description
#> FROM flights

dbplyr cũng dịch các function thao tác string và ngày giờ phổ biến, mà bạn có thể tìm hiểu trong vignette("translation-function", package = "dbplyr"). Các bản dịch của dbplyr chắc chắn không hoàn hảo, và có nhiều function R chưa được dịch, nhưng dbplyr làm tốt đáng ngạc nhiên trong việc bao phủ các function mà bạn sẽ dùng thường xuyên nhất.

21.7 Tóm tắt

Trong chương này, bạn đã học cách truy cập dữ liệu từ database. Chúng ta tập trung vào dbplyr, một “backend” của dplyr cho phép bạn viết mã dplyr quen thuộc, và nó tự động được dịch sang SQL. Chúng ta đã dùng bản dịch đó để dạy bạn một chút SQL; điều quan trọng là học một ít SQL vì nó là ngôn ngữ được dùng phổ biến nhất để làm việc với dữ liệu và biết một ít sẽ giúp bạn dễ dàng giao tiếp hơn với những người làm dữ liệu khác không dùng R.

Nếu bạn đã hoàn thành chương này và muốn tìm hiểu thêm về SQL, chúng tôi có hai gợi ý:

  • SQL for Data Scientists của Renee M. P. Teate là phần giới thiệu SQL được thiết kế đặc biệt cho nhu cầu của nhà khoa học dữ liệu, và bao gồm các ví dụ về loại dữ liệu liên kết chặt chẽ mà bạn có thể gặp trong các tổ chức thực tế.
  • Practical SQL của Anthony DeBarros được viết từ góc nhìn của một nhà báo dữ liệu (một nhà khoa học dữ liệu chuyên kể các câu chuyện hấp dẫn) và đi sâu hơn vào việc đưa dữ liệu vào database và vận hành DBMS riêng của bạn.

Trong chương tiếp theo, chúng ta sẽ tìm hiểu về một backend dplyr khác để làm việc với dữ liệu lớn: arrow. Arrow được thiết kế để làm việc với các file lớn trên đĩa, và là sự bổ sung tự nhiên cho database.

  1. SQL được phát âm là “S”-“Q”-“L” hoặc “sequel”.↩︎

  2. Thường đây là function duy nhất bạn sẽ dùng từ package client, nên chúng tôi khuyên bạn dùng :: để lấy riêng function đó, thay vì tải toàn bộ package bằng library().↩︎

  3. Ít nhất là tất cả các bảng mà bạn có quyền xem.↩︎

  4. Gây nhầm lẫn là, tùy thuộc vào ngữ cảnh, SELECT có thể là câu lệnh hoặc mệnh đề. Để tránh nhầm lẫn này, chúng ta thường sẽ dùng truy vấn SELECT thay vì câu lệnh SELECT.↩︎

  5. Về mặt kỹ thuật, chỉ có SELECT là bắt buộc, vì bạn có thể viết truy vấn như SELECT 1+1 để thực hiện các phép tính cơ bản. Nhưng nếu bạn muốn làm việc với dữ liệu (như bạn luôn làm!) thì bạn cũng cần mệnh đề FROM.↩︎

  6. Đây không phải trùng hợp: tên function dplyr được lấy cảm hứng từ mệnh đề SQL.↩︎