R İle PostgreSQL + SSL Bağlantısı

Bu yazıda süreci bir de R (Rlang) ile farklı paketler üzerinden ele alacağım.

R İle PostgreSQL Erişimi

R programlama dili ile daha önce veritabanı erişim işlemlerinden bahsetmemiştim. İlk konumuz PostgreSQL erişimi olsun. Bu amaçla kullanabileceğimiz birkaç paket bulunuyor. Bu paketler arasından öne çıkanlar ise RpostgreSQL¹ ve RPostgres². Aşağıda kullanımlarına ve farklarına ayrıca değineceğim.

RpostgreSQL Paketi İle PostgreSQL Erişimi

DigitalOcean üzerinden bir PostgreSQL veritabanımızı oluşturup kullanıcı adı, şifre, veritabanı adı, host ve port bilgilerini kayıt edebiliriz. Daha önceki örneklerde Elephantsql kullanmıştım. Ancak, ücretsiz erişimde işlem sayısı sınırlı olduğu için bu yazı akışında bir süre sonra bağlantı problemleri söz konusu olacaktı. Bu nedenle tavsiyem birden fazla instance oluşturmanız.

library(RPostgreSQL)

pgsql <- dbDriver( 'PostgreSQL' )
con = dbConnect( pgsql,
                 user = '',
                 password = '',
                 dbname = '',
                 host = '',
                 port = ''
)

RPostgreSQL paketimizi çalışmamıza sahip edip dbDriver ve dbConnect ile bağlantımızı sağlayabiliriz. Maksimum eşzamanlı bağlantı sayısını (max.con) ve her fetch işleminde işlenecek kayıt sayısını (fetch.default.rec) da belirleyebiliriz.

Bağlantının başarıyla gerçekleştirilmesinin ardından dbListTables(con) ile tabloları listeleyebiliriz. Ancak, veritabanını yeni oluşturduğumuzu unutmayalım. Bu nedenle şu an listeleyebileceğimiz verilerimiz yok (: O halde biraz veriye ihtiyacımız var. mtcars şu an için oldukça verimli bir kaynak olacaktır.

data('mtcars')
sendNewData <- data.frame(carname = rownames(mtcars), mtcars, row.names = NULL)
sendNewData$carname <- as.character(sendNewData$carname)

Evet, mtcars içeriğini artık DigitalOcean altında oluşturduğumuz veritabanına iletebiliriz. Ayrı bir sekmede bu verileri görüntülemek isterseniz View(sendNewData) ile içeriği yeni bir sekmede açabilirsiniz.

dbWriteTable ile sendNewData içeriğini aktarmaya başlayabiliriz.

dbWriteTable(con, name='cars', value=sendNewData, overwrite=TRUE)

Bu işlemin ardından TRUE dönüşünü almamız tabloya yazma işleminin tamamlandığını göstermektedir. rm(mtcars) ile bilgisayarımızdaki object içeriğini (environment altında görebilirsiniz) kaldırabiliriz.

Şimdi dbReadTable(con, 'cars') ile sunucudaki tablomuzun içeriğini alalım. Evet, işlemlerimiz bu kadar. Görüldüğü üzere komutun uygulanmasının ardından ilettiğimiz tablo bize sunucu üzerinden dönecektir. Şimdide, dbSendQuery ile 6 ve üzeri silindir (cyl), 5 ve üzeri vitese (gear) sahip arabaları (carname) listeleyelim.

que <- dbSendQuery(con, 'select carname, cyl, gear from cars where cyl >= 6 and gear >= 5;')  
rs <- fetch(que)

Dönecek içerik aşağıdaki gibi olacaktır.

         carname cyl gear
1 Ford Pantera L   8    5
2   Ferrari Dino   6    5
3  Maserati Bora   8    5

Son olarak, dbClearResult(que) ile getirdiğimiz verileri temizleyelim ve dbDisconnect(con) ile bağlantımızı sonlandıralım. Hepsi bu kadar.

Bir sonraki örnekte SSL bağlantısı sağlayarak yukarıdaki işlemleri kısmen yineleyeceğim. İki farklı paket kullanmış olmamın nedeni RpostgreSQL'in SSL desteğine sahip olmaması. Paket ile ilgili detaylara Package ‘RPostgreSQL üzerinden ve GitHub reposundan ulaşabilirsiniz ³.

RPostgres Paketi İle PostgreSQL Erişimi

Bu paket incelemesinde DigitalOcean üzerinden ilerleyecek ve veritabanı erişimi sonrasında yukarıdaki işlemleri tekrarlayacağım. Bu paket örneğinde özellikle dikkat etmenizi tavsiye ettiğim aşama sertifika oluşturma ve yükleme süreci.

Öncelikle, DigitalOcean üzerinden PostgreSQL veritabanı oluşturduğunuzda bağlantı için sslmode = require bilgisi de size iletilecektir. Ben veritabanı içerisinde bunu verify-full olarak düzenledim. Şayet sertifika olmadan bağlantı kurulmaya çalışırsak şu uyarı dönecektir.

Error: root certificate file "/Users/<kullanici-adi>/.postgresql/root.crt" does not exist
Either provide the file or change sslmode to disable server certificate verification.

DigitalOcean veritabanı ayarlarının alt bölümünde CA (Certificate Authority) certificate bağlantısını sunmakta. Bağlantıyı tıklayarak crt uzantılı sertifikayı bilgisayarınıza indirebilirsiniz. İndirme işleminin tamamlanmasının ardından sertifikanın indirildiği dizine geçip şu komutu uygulayabilirsiniz.

mkdir /Users/<kullanici-adi>/.postgresql
mv '/Users/<kullanici-adi>/Downloads/ca-certificate.crt' '/Users/<kullanici-adi>/.postgresql/root.crt'

Peki, bu işlemin amacı nedir? Şöyle açıklayayım. Veritabanı bağlantısı gerçekleştirmek istediğimizde ilk olarak /Users//.postgresql yoluna ve bu dizin içerisinde root.crt adında bir dosya olup olmadığına bakılır. Eğer ilgili dosya mevcut değilse az önce yukarıda da belirttiğim hata dönecektir3.

Bir diğer yöntem ise sslrootcert ile sertifika yolunun belirtilmesi.

sslrootcert = "/Users/<kullanici-adi>/Desktop/ca-certificate.crt"

Metotlarımız aynı. Bu defa kısa şekilde yukarıdaki örnek işlemi tekrarlayacağım.

library(RPostgres)
con = dbConnect( Postgres(),
                 user = 'doadmin',
                 password = 'kqmpc4a5g28kpc25',
                 dbname = 'defaultdb',
                 host = 'db-postgresql-fra1-45477-do-user-3034089-0.a.db.ondigitalocean.com',
                 port = 25060,
                 sslmode = 'verify-full'
)

Bağlantı işleminin ardından instance içeriğindeki tabloları alalım.

dbListTables(con)

Yine mtcars içeriğini örnek olarak yükleyeceğim ve son durumu görmek adına yine tabloları listeleyeceğim.

dbWriteTable(con, "mtcars", mtcars)
dbListTables(con)

Aşağıdaki gibi bir dönüş almış olmalıyım.

[1] "mtcars"

Tablonun içeriğini görüntüleyelim.

dbReadTable(con, "mtcars")

İşlem yapmak istediğimiz tablo var ise silip içeriğini yeniden yüklemek istediğimizi varsayalım.

sendNewData <- data.frame(carname = rownames(mtcars), mtcars, row.names = NULL)
sendNewData$carname <- as.character(sendNewData$carname)

if(dbExistsTable(con, "mtcars")){
  dbRemoveTable(con, "mtcars")
  dbWriteTable(con, "mtcars", sendNewData)
}

Buraya kadar sorunsuz ulaşabildiğimizi düşünüyorum. Örneği bir SELECT işlemi ile sonlandırabiliriz. Bu defa fetching işlemini yukarıdaki örneğin tersi şekilde ele alacağım. Yalnız, öncelikle sütunların adlarını bir kontrol edelim.

dbListTables(con)

Şimdi SELECT işlemini gerçekleştirebiliriz.

res <- dbSendQuery(con, "SELECT carname FROM mtcars WHERE cyl < 6 and gear < 5")
dbFetch(res)

Sorgular arasında dbClearResult(res) ile sorgu sonucunu temizlemeyi unutmamalısınız. İşlemlerimiz tamam ise dbDisconnect(con) ile bağlantımızı da sonlandırabiliriz. Paket ile ilgili daha detaylı bilgiye Package ‘RPostgres' üzerinden ve/veya GitHub reposundan ulaşmak mümkün ² ⁴.

R İle PostgreSQL + SSL Bağlantısı

R İle PostgreSQL Erişimi

RpostgreSQL Paketi İle PostgreSQL Erişimi

RPostgres Paketi İle PostgreSQL Erişimi

İleri Okumalar