21  Veritabanları

21.1 Giriş

Çok büyük miktarda veri, veritabanlarında yaşar; dolayısıyla bunlara nasıl erişeceğinizi bilmeniz çok önemlidir. Bazen birinden verilerin anlık görüntüsünü sizin için bir .csv dosyasına indirmesini isteyebilirsiniz, ancak bu yaklaşım kısa sürede zahmetli hâle gelir: Her değişiklik gerektiğinde başka biriyle iletişim kurmanız gerekir. İhtiyaç duyduğunuz verileri, ihtiyaç duyduğunuz anda doğrudan veritabanından alabilmelisiniz.

Bu bölümde önce DBI paketinin temellerini öğreneceksiniz: Bir veritabanına bağlanmak ve ardından bir SQL1 sorgusuyla veri almak için DBI’ı nasıl kullanacağınızı göreceksiniz. SQL, İngilizce structured query language (yapılandırılmış sorgu dili) ifadesinin kısaltmasıdır; veritabanlarının ortak dilidir ve tüm veri bilimcilerin öğrenmesi gereken önemli bir dildir. Bununla birlikte işe SQL ile başlamayacağız; önce dplyr kodunuzu SQL’e çevirebilen dbplyr’ı öğreteceğiz. Bu çeviriyi, SQL’in en önemli özelliklerinden bazılarını öğretmenin bir yolu olarak kullanacağız. Bölümün sonunda bir SQL uzmanı olmayacaksınız, ancak en önemli bileşenleri tanıyabilecek ve ne yaptıklarını anlayabileceksiniz.

21.1.1 Ön koşullar

Bu bölümde DBI ve dbplyr paketlerini tanıtacağız. DBI, veritabanlarına bağlanıp SQL çalıştıran düşük düzeyli bir arayüzdür; dbplyr ise dplyr kodunuzu SQL sorgularına çevirip bunları DBI ile çalıştıran yüksek düzeyli bir arayüzdür.

21.2 Veritabanı temelleri

En basit düzeyde bir veritabanını, veritabanı terminolojisinde tablo olarak adlandırılan veri çerçevelerinin bir koleksiyonu gibi düşünebilirsiniz. Bir veri çerçevesi gibi veritabanı tablosu da adlandırılmış sütunlardan oluşur ve her sütundaki tüm değerler aynı türdedir. Veri çerçeveleriyle veritabanı tabloları arasında üç temel fark vardır:

  • Veritabanı tabloları diskte saklanır ve istenildiği kadar büyük olabilir. Veri çerçeveleri bellekte saklanır ve temelde sınırlı boyuttadır (yine de bu sınır pek çok problem için oldukça büyüktür).

  • Veritabanı tablolarının neredeyse her zaman dizinleri vardır. Bir kitabın dizini gibi veritabanı dizini de her satıra tek tek bakmadan ilgilenilen satırları hızla bulmayı sağlar. Veri çerçeveleri ve tibble’ların dizinleri yoktur; ancak data.table’ların vardır ve bu da çok hızlı olmalarının nedenlerinden biridir.

  • Klasik veritabanlarının çoğu mevcut verileri çözümlemek için değil, verileri hızla toplamak için eniyilenmiştir. Veriler R’daki gibi sütun sütun değil, satır satır saklandığından bu veritabanlarına satır yönelimli denir. Son yıllarda mevcut verilerin çok daha hızlı çözümlenmesini sağlayan sütun yönelimli veritabanları konusunda önemli ilerlemeler kaydedilmiştir.

Veritabanları, üç temel biçimde karşımıza çıkan veritabanı yönetim sistemleri (kısaca DBMS) tarafından çalıştırılır:

  • İstemci-sunucu DBMS’leri, bilgisayarınızdan (istemciden) bağlandığınız güçlü bir merkezî sunucuda çalışır. Bir kuruluştaki birden fazla kişiyle veri paylaşmak için idealdir. Yaygın istemci-sunucu DBMS’leri arasında PostgreSQL, MariaDB, SQL Server ve Oracle bulunur.
  • Snowflake, Amazon Redshift ve Google BigQuery gibi bulut DBMS’leri istemci-sunucu DBMS’lerine benzer, ancak bulutta çalışır. Bu sayede son derece büyük veri kümelerini kolayca işleyebilir ve gerektiğinde otomatik olarak daha fazla hesaplama kaynağı sağlayabilirler.
  • SQLite veya duckdb gibi işlem içi DBMS’ler bütünüyle bilgisayarınızda çalışır. Başlıca kullanıcısının siz olduğunuz büyük veri kümeleriyle çalışmak için idealdir.

21.3 Bir veritabanına bağlanma

R’dan bir veritabanına bağlanmak için iki paket kullanacaksınız:

  • Veritabanına bağlanma, veri yükleme ve SQL sorguları çalıştırma gibi işler için genel fonksiyonlar sağladığından DBI’ı (database interface) her zaman kullanacaksınız.

  • Ayrıca bağlandığınız DBMS’ye özel bir paket kullanacaksınız. Bu paket, genel DBI komutlarını ilgili DBMS’nin gerektirdiği özel komutlara çevirir. Genellikle her DBMS için bir paket vardır; örneğin PostgreSQL için RPostgres, MySQL için RMariaDB.

DBMS’nize özel bir paket bulamazsanız çoğu zaman bunun yerine odbc paketini kullanabilirsiniz. Bu paket, birçok DBMS’nin desteklediği ODBC protokolünü kullanır. Bir ODBC sürücüsü kurmanız ve odbc paketine bu sürücünün konumunu bildirmeniz gerektiğinden odbc biraz daha fazla kurulum gerektirir.

Somut olarak, DBI::dbConnect() ile bir veritabanı bağlantısı oluşturursunuz. İlk argüman DBMS’yi seçer2; ikinci ve sonraki argümanlarsa ona nasıl bağlanılacağını (yani nerede bulunduğunu ve erişim için gereken kimlik bilgilerini) açıklar. Aşağıdaki kod birkaç tipik örnek gösterir:

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

Bağlantının ayrıntıları DBMS’den DBMS’ye büyük ölçüde değiştiği için ne yazık ki hepsini burada ele alamıyoruz. Bu nedenle kendi başınıza biraz araştırma yapmanız gerekecek. Genellikle ekibinizdeki diğer veri bilimcilere danışabilir veya DBA’nızla (database administrator, veritabanı yöneticisi) konuşabilirsiniz. İlk kurulumu doğru yapmak çoğu zaman biraz uğraş (ve belki biraz Google araması) gerektirir, ancak bunu genellikle yalnızca bir kez yaparsınız.

21.3.1 Bu kitapta

Bu kitap için bir istemci-sunucu ya da bulut DBMS’si kurmak zahmetli olacağından, bütünüyle bir R paketi içinde yaşayan işlem içi bir DBMS kullanacağız: duckdb. DBI’ın sağladığı soyutlama sayesinde duckdb ile başka bir DBMS’yi kullanmak arasındaki tek fark, veritabanına nasıl bağlandığınızdır. Bu özellik duckdb’yi öğretim için çok uygun kılar: Kodu kolayca çalıştırabilir, öğrendiklerinizi de başka ortamlara kolayca uygulayabilirsiniz.

Varsayılan ayarlar R’dan çıktığınızda silinen geçici bir veritabanı oluşturduğundan duckdb’ye bağlanmak özellikle kolaydır. R’ı her yeniden başlattığınızda temiz bir sayfadan başlamanızı garanti ettiği için bu, öğrenme açısından çok kullanışlıdır:

con <- DBI::dbConnect(duckdb::duckdb())
#> duckdb keeps downloaded extensions and secrets in a temporary directory:
#> ℹ /tmp/RtmpU5RzXt/duckdb
#> This is removed when the R session ends.
#> • Extensions are re-downloaded each session.
#> • Secrets are lost.
#> ℹ Run duckdb(shared_home = TRUE) (or create ~/.duckdb) to keep them (suitable for most users).
#> ℹ Run duckdb(shared_home = FALSE) to accept the temporary directory (and silence this message).
#> ℹ See ?duckdb_storage for details and alternatives.

duckdb, veri bilimcilerin ihtiyaçları gözetilerek tasarlanmış yüksek performanslı bir veritabanıdır. Burada onu kullanıyoruz çünkü başlamak çok kolaydır; ayrıca gigabaytlarca veriyi büyük bir hızla işleyebilir. duckdb’yi gerçek bir veri analizi projesinde kullanmak istiyorsanız kalıcı bir veritabanı oluşturmak ve duckdb’ye bunu nereye kaydedeceğini söylemek için dbdir argümanını da sağlamalısınız. Bir proje kullandığınızı varsayarsak (Bölüm 6), veritabanını geçerli projenin duckdb dizininde saklamak mantıklıdır:

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

21.3.2 Biraz veri yükleme

Bu yeni bir veritabanı olduğundan işe biraz veri ekleyerek başlamamız gerekir. Burada DBI::dbWriteTable() ile ggplot2 paketindeki mpg ve diamonds veri kümelerini ekleyeceğiz. dbWriteTable() fonksiyonunun en basit kullanımı üç argüman gerektirir: veritabanı bağlantısı, veritabanında oluşturulacak tablonun adı ve verileri içeren bir veri çerçevesi.

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

duckdb’yi gerçek bir projede kullanıyorsanız duckdb_read_csv() ve duckdb_register_arrow() fonksiyonlarını öğrenmenizi önemle öneririz. Bu fonksiyonlar, verileri önce R’a yüklemek zorunda kalmadan doğrudan duckdb’ye hızlıca aktarmak için güçlü ve yüksek performanslı yöntemler sağlar. Bölüm 26.4.1 bölümünde birden çok dosyayı veritabanına yüklemeye yarayan kullanışlı bir teknik de göstereceğiz.

21.3.3 DBI temelleri

Verilerin doğru yüklendiğini birkaç başka DBI fonksiyonuyla denetleyebilirsiniz: dbListTables() veritabanındaki tüm tabloları listeler3, dbReadTable() ise bir tablonun içeriğini getirir.

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() bir data.frame döndürür; düzgün yazdırılması için bunu as_tibble() ile tibble’a dönüştürürüz.

SQL’i zaten biliyorsanız veritabanında bir sorgu çalıştırmanın sonuçlarını almak için dbGetQuery() kullanabilirsiniz:

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

SQL’i daha önce hiç görmediyseniz endişelenmeyin! Birazdan onun hakkında daha fazlasını öğreneceksiniz. Ancak sorguyu dikkatle okursanız diamonds veri kümesinden beş sütunu ve price değerinin 15.000’den büyük olduğu tüm satırları seçtiğini tahmin edebilirsiniz.

21.4 dbplyr temelleri

Bir veritabanına bağlanıp biraz veri yüklediğimize göre dbplyr’ı öğrenmeye başlayabiliriz. dbplyr bir dplyr arka ucudur; yani siz dplyr kodu yazmaya devam ederken arka uç bu kodu farklı biçimde çalıştırır. dbplyr kodu SQL’e çevirir; diğer arka uçlar arasında kodu data.table’a çeviren dtplyr ve kodunuzu birden çok çekirdekte çalıştıran multidplyr bulunur.

dbplyr’ı kullanmak için önce tbl() ile bir veritabanı tablosunu temsil eden nesne oluşturmalısınız:

diamonds_db <- tbl(con, "diamonds")
diamonds_db
#> # A query:  ?? x 10
#> # Database: DuckDB 1.5.5 [unknown@Linux 6.17.0-1022-azure:R 4.6.1/: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

Bir veritabanıyla etkileşim kurmanın yaygın iki yolu daha vardır. Birincisi, birçok kurumsal veritabanı çok büyüktür; bu nedenle tüm tabloları düzenli tutmak için bir hiyerarşiye ihtiyaç duyarsınız. Bu durumda ilgilendiğiniz tabloyu seçmek için bir şema ya da katalog ve şema sağlamanız gerekebilir:

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

Bazen de başlangıç noktası olarak kendi SQL sorgunuzu kullanmak isteyebilirsiniz:

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

Bu nesne tembeldir; üzerinde dplyr fiillerini kullandığınızda dplyr herhangi bir işlem yapmaz: Yalnızca gerçekleştirmek istediğiniz işlemlerin sırasını kaydeder ve bunları gerektiğinde çalıştırır. Örneğin aşağıdaki işlem hattını ele alın:

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

big_diamonds_db
#> # A query:  ?? x 5
#> # Database: DuckDB 1.5.5 [unknown@Linux 6.17.0-1022-azure:R 4.6.1/: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

Üstte DBMS adını yazdırdığından ve sütun sayısını belirtmesine karşın genellikle satır sayısını bilmediğinden, bu nesnenin bir veritabanı sorgusunu temsil ettiğini anlayabilirsiniz. Bunun nedeni, toplam satır sayısını bulmanın genellikle tüm sorguyu çalıştırmayı gerektirmesidir; biz de tam olarak bundan kaçınmaya çalışıyoruz.

dplyr’ın show_query() fonksiyonuyla üretilen SQL kodunu görebilirsiniz. dplyr’ı biliyorsanız bu, SQL öğrenmenin harika bir yoludur! Biraz dplyr kodu yazın, dbplyr’ın bunu SQL’e çevirmesini sağlayın ve ardından iki dilin nasıl eşleştiğini anlamaya çalışın.

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

Tüm verileri yeniden R’a almak için collect() fonksiyonunu çağırırsınız. Bu işlem arka planda SQL’i üretir, verileri almak için dbGetQuery() fonksiyonunu çağırır ve ardından sonucu bir tibble’a dönüştürür:

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

Genellikle veritabanından istediğiniz verileri seçmek ve aşağıda açıklanan çevirilerle temel süzme ve toplulaştırma işlemlerini yapmak için dbplyr’ı kullanırsınız. Ardından verileri R’a özgü fonksiyonlarla çözümlemeye hazır olduğunuzda, bellekte bir tibble elde etmek için collect() çağırır ve çalışmanıza doğrudan R koduyla devam edersiniz.

21.5 SQL

Bu bölümün geri kalanı, dbplyr açısından bakarak size biraz SQL öğretecek. Bu, SQL’e oldukça alışılmadık bir giriş olsa da temel bilgileri hızla edinmenizi sağlayacağını umuyoruz. Neyse ki dplyr’ı anlıyorsanız SQL’i çabucak öğrenmek için iyi bir konumdasınız; çünkü iki dildeki kavramların pek çoğu aynıdır.

nycflights13 paketinden iki eski dostu, flights ve planes veri kümelerini kullanarak dplyr ile SQL arasındaki ilişkiyi inceleyeceğiz. dbplyr, nycflights13 tablolarını veritabanımıza kopyalayan bir fonksiyonla geldiği için bu veri kümelerini eğitim veritabanımıza aktarmak kolaydır:

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 SQL temelleri

SQL’in en üst düzey bileşenlerine deyim denir. Yaygın deyimler arasında yeni tablolar tanımlamak için CREATE, veri eklemek için INSERT ve veri almak için SELECT bulunur. Bir veri bilimci olarak neredeyse yalnızca bunları kullanacağınız için sorgu olarak da adlandırılan SELECT deyimlerine odaklanacağız.

Bir sorgu tümceciklerden oluşur. Beş önemli tümcecik vardır: SELECT, FROM, WHERE, ORDER BY ve GROUP BY. Her sorguda SELECT4 ve FROM5 tümcecikleri bulunmalıdır. En basit sorgu, belirtilen tablodaki tüm sütunları seçen SELECT * FROM table sorgusudur. dbplyr, üzerinde işlem yapılmamış bir tablo için tam olarak bunu üretir:

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

WHERE ve ORDER BY, hangi satırların dahil edileceğini ve nasıl sıralanacağını denetler:

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

GROUP BY, sorguyu bir özete dönüştürerek toplulaştırma yapılmasını sağlar:

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

dplyr fiilleriyle SELECT tümcecikleri arasında iki önemli fark vardır:

  • SQL’de büyük-küçük harf fark etmez: select, SELECT, hatta SeLeCt yazabilirsiniz. Bu kitapta SQL anahtar sözcüklerini tablo veya değişken adlarından ayırmak için büyük harfle yazma geleneğine uyacağız.
  • SQL’de sıra önemlidir: Tümcecikleri her zaman SELECT, FROM, WHERE, GROUP BY, ORDER BY sırasıyla yazmalısınız. Kafa karıştırıcı biçimde bu sıra, tümceciklerin gerçekte değerlendirilme sırasıyla aynı değildir; değerlendirme sırası FROM, WHERE, GROUP BY, SELECT ve ORDER BY biçimindedir.

Aşağıdaki bölümlerde her tümceciği daha ayrıntılı inceleyeceğiz.

SQL bir standart olsa da son derece karmaşıktır ve hiçbir veritabanı bu standardı eksiksiz izlemez. Bu kitapta odaklanacağımız temel bileşenler DBMS’ler arasında oldukça benzer olsa da birçok küçük farklılık vardır. Neyse ki dbplyr bu sorunu ele alacak biçimde tasarlanmıştır ve farklı veritabanları için farklı çeviriler üretir. Kusursuz değildir ancak sürekli geliştirilmektedir; bir sorunla karşılaşırsanız daha iyi hâle gelmesine yardımcı olmak için GitHub’da bir konu açabilirsiniz.

21.5.2 SELECT

SELECT tümceciği sorguların yükünü taşıyan temel bileşendir; select(), mutate(), rename(), relocate() ve bir sonraki bölümde öğreneceğiniz üzere summarize() ile aynı işi yapar.

select(), rename() ve relocate() yalnızca bir sütunun görünüp görünmediğini, görünüyorsa nerede ve hangi adla göründüğünü etkilediğinden SELECT karşılıkları oldukça doğrudandır:

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

Bu örnek SQL’de yeniden adlandırmanın nasıl yapıldığını da gösterir. SQL terminolojisinde yeniden adlandırmaya takma ad verme denir ve AS ile yapılır. mutate() fonksiyonundan farklı olarak eski adın solda, yeni adın sağda olduğuna dikkat edin.

Yukarıdaki örneklerde "year" ve "type" ifadelerinin çift tırnak içine alındığına dikkat edin. Bunun nedeni bunların duckdb’de ayrılmış sözcükler olmasıdır; dbplyr sütun/tablo adlarıyla SQL işleçleri arasında oluşabilecek karışıklığı önlemek için bunları tırnak içine alır.

Başka veritabanlarıyla çalışırken tüm değişken adlarının tırnak içine alındığını görebilirsiniz. Çünkü duckdb gibi yalnızca birkaç istemci paketi ayrılmış sözcüklerin tümünü bilir; diğerleri güvenli olmak için her şeyi tırnak içine alır.

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

Bazı veritabanı sistemleri çift tırnak yerine ters tırnak kullanır:

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

mutate() çevirileri de benzer biçimde doğrudandır: Her değişken SELECT içinde yeni bir ifadeye dönüşür:

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

Tek tek bileşenlerin (/ gibi) çevirisine Bölüm 21.6 bölümünde geri döneceğiz.

21.5.3 FROM

FROM tümceciği veri kaynağını tanımlar. Şimdilik yalnızca tek tablolar kullandığımızdan bir süre pek ilginç olmayacak. Birleştirme fonksiyonlarına geldiğimizde daha karmaşık örnekler göreceksiniz.

21.5.4 GROUP BY

group_by() fonksiyonu GROUP BY6 tümceciğine, summarize() ise SELECT tümceciğine çevrilir:

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

n() ve mean() fonksiyonlarının çevirisinde neler olduğuna Bölüm 21.6 bölümünde geri döneceğiz.

21.5.5 WHERE

filter() fonksiyonu WHERE tümceciğine çevrilir:

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

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

Burada dikkat edilmesi gereken birkaç önemli ayrıntı vardır:

  • |, OR; & ise AND olur.
  • SQL karşılaştırma için == değil = kullanır. SQL’de atama olmadığından burada karışıklık olasılığı yoktur.
  • SQL karakter dizileri için yalnızca '' kullanır; "" kullanmaz. SQL’de "", R’daki `` gibi değişkenleri tanımlamak için kullanılır.

Bir başka kullanışlı SQL işleci, R’daki %in% işlecine çok benzeyen IN’dir:

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

SQL, NA yerine NULL kullanır. NULL değerleri NA değerlerine benzer davranır. Temel fark, karşılaştırma ve aritmetik işlemlerinde “bulaşıcı” olmalarına karşın özetleme sırasında sessizce çıkarılmalarıdır. dbplyr bu davranışla ilk karşılaştığınızda size onu hatırlatır:

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.
#> # A query:  ?? x 2
#> # Database: DuckDB 1.5.5 [unknown@Linux 6.17.0-1022-azure:R 4.6.1/:memory:]
#>   dest  delay
#>   <chr> <dbl>
#> 1 SFO    2.67
#> 2 GSP   15.9 
#> 3 SJU    2.52
#> 4 DCA    9.07
#> 5 SNA   -7.87
#> 6 GSO   14.1 
#> # ℹ more rows

NULL değerlerinin nasıl çalıştığı hakkında daha fazla bilgi edinmek isterseniz Markus Winand’ın “The Three-Valued Logic of SQL” yazısını okuyabilirsiniz.

Genel olarak NULL değerleriyle, R’da NA değerleri için kullandığınız fonksiyonlarla çalışabilirsiniz:

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

Bu SQL sorgusu dbplyr’ın dezavantajlarından birini gösterir: Üretilen SQL doğru olsa da elle yazacağınız kadar basit değildir. Bu durumda parantezleri kaldırıp okunması daha kolay özel bir işleç kullanabilirsiniz:

WHERE "dep_delay" IS NOT NULL

Özetleme sırasında oluşturduğunuz bir değişkeni filter() ile süzerseniz dbplyr’ın WHERE yerine HAVING tümceciği üreteceğine dikkat edin. Bu, SQL’in kendine özgü özelliklerinden biridir: WHERE, SELECT ve GROUP BY öncesinde değerlendirilir; dolayısıyla SQL daha sonra değerlendirilen başka bir tümceciğe ihtiyaç duyar.

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

Satırları sıralamak, arrange() fonksiyonundan ORDER BY tümceciğine doğrudan bir çeviri gerektirir:

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

desc() fonksiyonunun DESC olarak çevrildiğine dikkat edin: Bu, adı doğrudan SQL’den esinlenen birçok dplyr fonksiyonundan biridir.

21.5.7 Alt sorgular

Bazen bir dplyr işlem hattını tek bir SELECT deyimine çevirmek mümkün olmaz ve bir alt sorgu kullanmanız gerekir. Alt sorgu, FROM tümceciğinde olağan tablo yerine veri kaynağı olarak kullanılan bir sorgudur.

dbplyr genellikle SQL’in sınırlamalarını aşmak için alt sorgular kullanır. Örneğin SELECT tümceciğindeki ifadeler yeni oluşturulmuş sütunlara başvuramaz. Bu nedenle aşağıdaki (yapay) dplyr işlem hattının iki adımda gerçekleşmesi gerekir: İlk (iç) sorgu year1 değerini hesaplar, ardından ikinci (dış) sorgu year2 değerini hesaplayabilir.

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

Yeni oluşturduğunuz bir değişkeni filter() ile süzmeye çalışırsanız bunu yine görürsünüz. Unutmayın: WHERE, SELECT sonrasında yazılsa da ondan önce değerlendirilir; dolayısıyla bu (yapay) örnekte bir alt sorguya ihtiyacımız vardır:

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

Bazen dbplyr çeviriyi nasıl eniyileyeceğini henüz bilmediği için gerekmediği hâlde bir alt sorgu oluşturur. dbplyr zamanla geliştikçe bu durumlar seyrekleşecek, ancak muhtemelen hiçbir zaman bütünüyle ortadan kalkmayacaktır.

21.5.8 Birleştirmeler

dplyr birleştirmelerine aşinaysanız SQL birleştirmeleri de oldukça benzerdir. İşte basit bir örnek:

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)

Burada dikkat edilmesi gereken temel nokta sözdizimidir: SQL birleştirmeleri ek tabloları dahil etmek için FROM tümceciğinin alt tümceciklerini, tabloların nasıl ilişkili olduğunu tanımlamak içinse ON kullanır.

dplyr’daki bu fonksiyonların adları SQL ile öylesine yakından ilişkilidir ki inner_join(), right_join() ve full_join() fonksiyonlarının SQL karşılıklarını kolayca tahmin edebilirsiniz:

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)

Bir veritabanındaki verilerle çalışırken çok sayıda birleştirmeye ihtiyaç duymanız olasıdır. Bunun nedeni, veritabanı tablolarının çoğu zaman yüksek ölçüde normalleştirilmiş biçimde saklanmasıdır: Her “olgu” tek bir yerde tutulur ve çözümleme için eksiksiz bir veri kümesi oluşturmak üzere birincil ve yabancı anahtarlarla bağlanmış karmaşık bir tablo ağında ilerlemeniz gerekir. Bu durumla karşılaşırsanız Tobias Schieferdecker, Kirill Müller ve Darko Bergant’ın dm paketi hayat kurtarıcıdır. Paket, DBA’ların sıklıkla sağladığı kısıtlamaları kullanarak tablolar arasındaki bağlantıları otomatik olarak belirleyebilir, neler olduğunu görebilmeniz için bağlantıları görselleştirebilir ve bir tabloyu diğerine bağlamak için gereken birleştirmeleri oluşturabilir.

21.5.9 Diğer fiiller

dbplyr ayrıca distinct(), slice_*() ve intersect() gibi başka fiillerin yanı sıra pivot_longer() ve pivot_wider() gibi giderek artan sayıda tidyr fonksiyonunu da çevirir. Şu anda kullanılabilenlerin tam listesini görmenin en kolay yolu dbplyr web sitesini ziyaret etmektir: https://dbplyr.tidyverse.org/reference/.

21.5.10 Alıştırmalar

  1. distinct() neye çevrilir? Peki ya head()?

  2. Aşağıdaki SQL sorgularının her birinin ne yaptığını açıklayın ve bunları dbplyr ile yeniden oluşturmaya çalışın.

    SELECT * 
FROM flights
WHERE dep_delay < arr_delay

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

21.6 Fonksiyon çevirileri

Şimdiye kadar dplyr fiillerinin bir sorgunun tümceciklerine nasıl çevrildiğinin genel görünümüne odaklandık. Şimdi biraz daha yakından bakacak ve tek tek sütunlarla çalışan R fonksiyonlarının çevirisini ele alacağız. Örneğin bir summarize() içinde mean(x) kullandığınızda ne olur?

Neler olduğunu görmeyi kolaylaştırmak için summarize() ya da mutate() çalıştırıp üretilen SQL’i gösteren birkaç küçük yardımcı fonksiyon kullanacağız. Bu, bazı çeşitleri incelemeyi ve özetlerle dönüşümlerin nasıl farklılaşabildiğini görmeyi kolaylaştıracak.

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

Bazı özetlerle başlayalım! Aşağıdaki koda baktığınızda mean() gibi bazı özet fonksiyonlarının görece basit, median() gibi diğerlerininse çok daha karmaşık çevirilere sahip olduğunu fark edeceksiniz. Karmaşıklık genellikle istatistikte yaygın, ancak veritabanlarında daha az kullanılan işlemlerde daha yüksektir.

flights |> 
  group_by(year, month, day) |>  
  summarize_query(
    mean = mean(arr_delay, na.rm = TRUE),
    median = median(arr_delay, na.rm = TRUE)
  )
#> ! Grouped output by "year" and "month".
#> ℹ Override behaviour and silence this message with the `.groups` argument.
#> ℹ Or use `.by` instead of `group_by()`.
#> <SQL>
#> SELECT
#>   "year",
#>   "month",
#>   "day",
#>   AVG(arr_delay) AS mean,
#>   MEDIAN(arr_delay) AS median
#> FROM flights
#> GROUP BY "year", "month", "day"

Özet fonksiyonlarını mutate() içinde kullandığınızda çevirileri daha karmaşık hâle gelir; çünkü pencere fonksiyonu denen fonksiyonlara dönüşmeleri gerekir. SQL’de sıradan bir toplulaştırma fonksiyonunun sonuna OVER ekleyerek onu pencere fonksiyonuna dönüştürürsünüz:

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

SQL’de GROUP BY tümceciği yalnızca özetler için kullanılır; dolayısıyla burada gruplamanın GROUP BY tümceciğinden OVER içine taşındığını görebilirsiniz.

Pencere fonksiyonları ileriye veya geriye bakan tüm fonksiyonları kapsar; örneğin lead() ve lag() sırasıyla “sonraki” ve “önceki” değere bakar:

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

SQL tablolarının kendiliğinden bir sırası olmadığından burada verileri arrange() ile sıralamak önemlidir. Hatta arrange() kullanmazsanız satırları her seferinde farklı bir sırada geri alabilirsiniz! Pencere fonksiyonlarında sıralama bilgisinin yinelendiğine dikkat edin: Ana sorgunun ORDER BY tümceciği pencere fonksiyonlarına otomatik olarak uygulanmaz.

Bir başka önemli SQL fonksiyonu CASE WHEN’dir. if_else() fonksiyonunun ve doğrudan kendisinden esinlenen dplyr fonksiyonu case_when()’in çevirisi olarak kullanılır. İşte birkaç basit örnek:

flights |> 
  mutate_query(
    description = if_else(arr_delay > 0, "gecikmeli", "zamanında")
  )
#> <SQL>
#> SELECT CASE WHEN (arr_delay > 0.0) THEN 'gecikmeli' WHEN NOT (arr_delay > 0.0) THEN 'zamanında' END AS description
#> FROM flights
flights |> 
  mutate_query(
    description = 
      case_when(
        arr_delay < -5 ~ "erken",
        arr_delay < 5 ~ "zamanında",
        arr_delay >= 5 ~ "geç"
      )
  )
#> <SQL>
#> SELECT CASE
#> WHEN (arr_delay < -5.0) THEN 'erken'
#> WHEN (arr_delay < 5.0) THEN 'zamanında'
#> WHEN (arr_delay >= 5.0) THEN 'geç'
#> END AS description
#> FROM flights

CASE WHEN, R’dan SQL’e doğrudan çevirisi olmayan bazı başka fonksiyonlar için de kullanılır. cut() bunun iyi bir örneğidir:

flights |> 
  mutate_query(
    description =  cut(
      arr_delay, 
      breaks = c(-Inf, -5, 5, Inf), 
      labels = c("erken", "zamanında", "geç")
    )
  )
#> <SQL>
#> SELECT CASE
#> WHEN (arr_delay <= -5.0) THEN 'erken'
#> WHEN (arr_delay <= 5.0) THEN 'zamanında'
#> WHEN (arr_delay > 5.0) THEN 'geç'
#> END AS description
#> FROM flights

dbplyr yaygın karakter dizisi ve tarih-saat işleme fonksiyonlarını da çevirir; bunlar hakkında vignette("translation-function", package = "dbplyr") ile bilgi edinebilirsiniz. dbplyr çevirileri elbette kusursuz değildir ve henüz çevrilmeyen birçok R fonksiyonu vardır; yine de dbplyr çoğu zaman kullanacağınız fonksiyonları şaşırtıcı derecede iyi kapsar.

21.7 Özet

Bu bölümde veritabanlarındaki verilere nasıl erişeceğinizi öğrendiniz. Alışık olduğunuz dplyr kodunu yazmanızı ve bunun otomatik olarak SQL’e çevrilmesini sağlayan bir dplyr “arka ucu” olan dbplyr’a odaklandık. Bu çeviriyi size biraz SQL öğretmek için kullandık. SQL öğrenmek önemlidir; çünkü verilerle çalışmak için en yaygın kullanılan dildir ve biraz SQL bilmek, R kullanmayan diğer veri çalışanlarıyla iletişim kurmanızı kolaylaştırır.

Bu bölümü bitirdiyseniz ve SQL hakkında daha fazla bilgi edinmek istiyorsanız iki önerimiz var:

  • Renée M. P. Teate’in SQL for Data Scientists kitabı, özellikle veri bilimcilerin ihtiyaçları için tasarlanmış bir SQL girişidir ve gerçek kuruluşlarda karşılaşmanız olası, birbiriyle yoğun biçimde bağlantılı veri örnekleri içerir.
  • Anthony DeBarros’un Practical SQL kitabı bir veri gazetecisinin (etkileyici öyküler anlatma konusunda uzmanlaşmış bir veri bilimcinin) bakış açısından yazılmıştır; verilerinizi bir veritabanına aktarma ve kendi DBMS’nizi çalıştırma konularını daha ayrıntılı ele alır.

Bir sonraki bölümde büyük verilerle çalışmak için kullanılan başka bir dplyr arka ucunu öğreneceğiz: arrow. Arrow, diskteki büyük dosyalarla çalışmak üzere tasarlanmıştır ve veritabanlarının doğal bir tamamlayıcısıdır.

  1. SQL İngilizcede harf harf “ess-cue-ell” ya da “sequel” biçiminde okunur.↩︎

  2. Genellikle istemci paketinden kullanacağınız tek fonksiyon budur. Bu nedenle paketin tamamını library() ile yüklemek yerine yalnızca bu fonksiyonu :: ile çağırmanızı öneririz.↩︎

  3. En azından görme izniniz olan tüm tabloları.↩︎

  4. Kafa karıştırıcı biçimde, bağlama göre SELECT bir deyim ya da tümcecik olabilir. Bu karışıklığı önlemek için genellikle SELECT deyimi yerine SELECT sorgusu ifadesini kullanacağız.↩︎

  5. Teknik olarak yalnızca SELECT zorunludur; çünkü temel hesaplamalar yapmak için SELECT 1+1 gibi sorgular yazabilirsiniz. Ancak verilerle çalışmak istiyorsanız (ki her zaman istersiniz!) bir FROM tümceciğine de ihtiyacınız vardır.↩︎

  6. Bu bir rastlantı değildir: dplyr fonksiyonunun adı SQL tümceciğinden esinlenmiştir.↩︎