向資料庫查詢

The world’s most valuable resource is no longer oil, but data.

The Economist

獲取資料在資料科學專案中扮演發起點,如果這個資料科學專案目的是協助我們制定資料驅動的策略(data-driven strategy),而非倚賴直覺,那麼為專案細心盤點資料來源與整理獲取方法,可以為將來的決策奠基穩固的基礎。資料常見的來源包含三種:

  1. 檔案
  2. 資料庫
  3. 網頁資料擷取

載入常見檔案格式中,我們簡介過如何將四種常見的檔案格式:CSV、TXT、Excel 試算表與 JSON 載入 Python 與 R 語言,接著我們要探討另外一種常見的資料來源:資料庫;一但對於資料建立、讀取、更新與刪除(俗稱的 CRUD:Create、Read、Update 與 Delete)需要規模化管理時,資料科學團隊就會建立資料庫來因應。這個小節我們關注常見的雲端 SQL 與 NoSQL 資料庫服務。

如何啟動 Amazon Web Service 的 MySQL

透過下列步驟可以在 Amazon Web Service 啟動一個 MySQL 資料庫。

  1. 前往 Amazon Web Service 的首頁並點選登入按鈕
  2. 使用自己的帳號密碼登入
  3. 點選服務下拉式選單,選擇 Database 底下的 RDS(Relational Database Service)服務
  4. 點選新增一個資料庫元件
  5. 選擇 MySQL 作為資料庫系統後按下一步
  6. 我們使用 Amazon Web Service 提供最低規格資料庫 12 個月的免費試用,因此規格設定都依照預設,只需要輸入資料庫元件的命名、使用者名稱與密碼,記住這裡所輸入的使用者名稱以及密碼,在未來使用 Python 與 R 連線的時候會用到,然後按下一步
  7. 為之後要使用的資料庫命名,這會是一個空的資料庫,裡面的資料表格要稍待由 Python 與 R 建立
  8. 順利啟動 MySQL 資料庫
  9. 檢視資料庫元件詳細資訊,取得連線的資料庫元件位址資訊(Endpoint)
  10. 新增一個寬鬆的 Inbound Rule(0.0.0.0/0)讓後續連線比較簡單(強烈不建議在正式環境中做這樣的設定)

前往 Amazon Web Service 的首頁並點選登入按鈕

使用自己的帳號密碼登入

選擇 Database 底下的 RDS(Relational Database Service)服務

點選新增一個資料庫元件

選擇 MySQL 作為資料庫系統後按下一步

輸入資料庫元件的命名、使用者名稱與密碼

為之後要使用的資料庫命名

順利啟動 MySQL 資料庫

取得連線的資料庫元件位址資訊(Endpoint)

新增一個寬鬆的 Inbound Rule

如何建立資料:MySQL

目前資料庫裡面沒有任何資料表格,於是我們首先要做的事情是透過 Python 與 R 語言與資料庫連線,連線成功以後嘗試將慣常使用的資料框(Data Frame)匯入資料庫中,以供後續查詢使用;接著讓我們將 1995 至 1996 年的芝加哥公牛隊球員名單以及 2007 至 2008 年的波士頓塞爾提克隊球員名單與其基本資訊分別以 Python 和 R 語言在資料庫建立出表格。

Python

我們需要 sqlalchemy 與 pymysql 建立 Python 與 MySQL 資料庫的連結,然後還需要 pandas 來建立資料庫表格。開始之前我們得先在終端機安裝好需要的模組:

pip install --upgrade sqlalchemy pymysql pandas
1

透過 sqlalchemy 的 create_engine() 函數建立 Python 與資料庫的連結引擎,在這個連結引擎中我們指定使用 pymysql 的連接器(connector),接著是透過 pandas 的 to_sql() 方法將資料框建立成為資料庫中的表格。建立連結引擎,最重要的要素是:

  • 資料庫元件位址(host):即資料庫元件位址資訊(Endpoint)
  • 通訊埠(port):MySQL 預設為 3306
  • 資料庫名稱(dbname):在啟動 MySQL 資料庫元件時的設定
  • 使用者名稱(user):在啟動 MySQL 資料庫元件時的設定
  • 使用者密碼(password):在啟動 MySQL 資料庫元件時的設定
import pandas as pd
from sqlalchemy import create_engine

csv_url = "https://storage.googleapis.com/ds_data_import/chicago_bulls_1995_1996.csv"
chicago_bulls = pd.read_csv(csv_url)
host = "YOURHOST" # 輸入自己的 AWS RDS Enpoint 位址
port = 3306
dbname = "YOURDBNAME" # 輸入自己設定的資料庫名稱
user = "YOURUSERNAME" # 輸入自己設定的使用者名稱
password = "YOURPASSWORD" # 輸入自己設定的使用者密碼

engine = create_engine('mysql+pymysql://{user}:{password}@{host}:{port}/{dbname}'.format(user=user, password=password, host=host, port=port, dbname=dbname))
chicago_bulls.to_sql('chicago_bulls', engine, index=False, if_exists='replace')
1
2
3
4
5
6
7
8
9
10
11
12
13

R 語言

使用 DBI 套件提供的 dbConnect() 函數建立 R 與 MySQL 資料庫的連結以及同樣源自 DBI 的 dbWriteTable() 函數將資料框建立成為資料庫中的表格;建立與資料庫的連結同樣也需要資料庫元件位址(host)、通訊埠(port)、資料庫名稱(dbname)、使用者名稱(user)與使用者密碼(password)這幾個重要元素;記得都要將這幾個資訊更換成為自己在設定資料庫元件時所輸入的對應資訊。

#install.packages("RMySQL")
library(DBI)

csv_url <- "https://storage.googleapis.com/ds_data_import/boston_celtics_2007_2008.csv"
boston_celtics <- read.csv(csv_url)

host <- "YOURHOST" # 輸入自己的 AWS RDS Enpoint 位址
port <- 3306
dbname <- "YOURDBNAME" # 輸入自己設定的資料庫名稱
user <- "YOURUSERNAME" # 輸入自己設定的使用者名稱
password <- "YOURPASSWORD" # 輸入自己設定的使用者密碼

engine <- dbConnect(RMySQL::MySQL(),
                    host = host,
                    port = port,
                    dbname = dbname,
                    user = user,
                    password = password
                    )
dbWriteTable(engine, name = 'boston_celtics', value = boston_celtics, overwrite = TRUE)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
## [1] TRUE
1

如何讀取資料:MySQL

Python

讀取資料庫中的表格同樣也需要先利用 sqlalchemy 的 create_engine() 函數建立 Python 與資料庫的連結引擎,接著可以透過 pandas 的 read_sql_table() 讀入整個表格;也可以透過 read_sql_query() 輸入 SQL 查詢語法讀取部分表格資料。

import pandas as pd
from sqlalchemy import create_engine

host = "YOURHOST" # 輸入自己的 AWS RDS Enpoint 位址
port = 3306
dbname = "YOURDBNAME" # 輸入自己設定的資料庫名稱
user = "YOURUSERNAME" # 輸入自己設定的使用者名稱
password = "YOURPASSWORD" # 輸入自己設定的使用者密碼

engine = create_engine('mysql+pymysql://{user}:{password}@{host}:{port}/{dbname}'.format(user=user, password=password, host=host, port=port, dbname=dbname))
chicago_bulls = pd.read_sql_table('chicago_bulls', engine)
chicago_bulls
1
2
3
4
5
6
7
8
9
10
11
12

透過 pandas 的 read_sql_table() 讀入整個表格

import pandas as pd
from sqlalchemy import create_engine

csv_url = "https://storage.googleapis.com/ds_data_import/chicago_bulls_1995_1996.csv"
chicago_bulls = pd.read_csv(csv_url)
host = "YOURHOST" # 輸入自己的 AWS RDS Enpoint 位址
port = 3306
dbname = "YOURDBNAME" # 輸入自己設定的資料庫名稱
user = "YOURUSERNAME" # 輸入自己設定的使用者名稱
password = "YOURPASSWORD" # 輸入自己設定的使用者密碼

engine = create_engine('mysql+pymysql://{user}:{password}@{host}:{port}/{dbname}'.format(user=user, password=password, host=host, port=port, dbname=dbname))
sql_statement = """
  SELECT * 
  FROM chicago_bulls 
  WHERE Player IN ('Michael Jordan', 'Scottie Pippen', 'Dennis Rodman');
"""
trio = pd.read_sql_query(sql_statement, engine)
trio
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

透過 read_sql_query() 輸入 SQL 查詢語法讀取部分表格資料

R 語言

讀取資料庫中的表格同樣也需要先利用 DBI 的 dbConnect() 函數建立 R 語言與資料庫的連結引擎,接著可以透過 DBI 的 dbReadTable() 讀入整個表格;也可以透過 dbGetQuery() 輸入 SQL 查詢語法讀取部分表格資料。

#install.packages("RMySQL")
library(DBI)

host <- "YOURHOST" # 輸入自己的 AWS RDS Enpoint 位址
port <- 3306
dbname <- "YOURDBNAME" # 輸入自己設定的資料庫名稱
user <- "YOURUSERNAME" # 輸入自己設定的使用者名稱
password <- "YOURPASSWORD" # 輸入自己設定的使用者密碼

engine <- dbConnect(RMySQL::MySQL(),
                    host = host,
                    port = port,
                    dbname = dbname,
                    user = user,
                    password = password
                    )
boston_celtics <- dbReadTable(engine, name = 'boston_celtics')
View(boston_celtics)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

透過 DBI 的 dbReadTable() 讀入整個表格

#install.packages("RMySQL")
library(DBI)

host <- "YOURHOST" # 輸入自己的 AWS RDS Enpoint 位址
port <- 3306
dbname <- "YOURDBNAME" # 輸入自己設定的資料庫名稱
user <- "YOURUSERNAME" # 輸入自己設定的使用者名稱
password <- "YOURPASSWORD" # 輸入自己設定的使用者密碼

engine <- dbConnect(RMySQL::MySQL(),
                    host = host,
                    port = port,
                    dbname = dbname,
                    user = user,
                    password = password
                    )
sql_statement <- "SELECT * FROM boston_celtics WHERE Player IN ('Paul Pierce', 'Kevin Garnett', 'Ray Allen');"
gap <- dbGetQuery(engine, statement = sql_statement)
View(gap)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

透過 dbGetQuery() 輸入 SQL 查詢語法讀取部分表格資料

如何啟動 Google Cloud Platform 的 BigQuery

假如需要建構巨量資料的雲端關聯式資料庫,Google 的 BigQuery 服務由於對 Python 與 R 的支援良好,受到許多資料科學團隊的青睞;透過下列步驟就可以在 Google Cloud Platform 啟動 BigQuery 服務。

  1. 前往 Google Cloud Platform 首頁
  2. 登入控制台(Console),點選 API & Services 然後按下憑證
  3. 新增 Service account key
  4. 設定 Service account key 為 BigQuery 管理員後下載
  5. 前往 Google Big Query 首頁,點選檢視控制台
  6. 在選單中選擇 BigQuery
  7. 來到 BigQuery 介面
  8. 建立新的資料集:fav_nba_teams

前往 Google Cloud Platform 首頁

點選 API & Services 然後按下憑證

新增 Service account key

設定 Service account key 為 BigQuery 管理員後下載

前往 Google Big Query 首頁

在選單中選擇 BigQuery

建立新的資料集

建立新的資料集:fav_nba_teams

如何建立資料:BigQuery

接著讓我們將 1995 至 1996 年的芝加哥公牛隊球員名單以及 2007 至 2008 年的波士頓塞爾提克隊球員名單與其基本資訊分別以 Python 和 R 語言匯入資料庫中,以供後續查詢使用。

Python

我們需要 pandas-gbq 與 pandas 來建立 Python 與 BigQuery 資料庫的連結與表格。開始之前我們得先在終端機安裝好需要的模組:

pip install pandas-gbq pandas -U
1

使用 to_gbq() 函數將 chicago_bulls 資料框匯入先前建立空的資料集 fav_nba_teams 之下,並將表格命名為 chicago_bulls,記得將 destination_table、project_id 與 private_key 參數調整成自己專案中的設定。

rom google.cloud import bigquery
import pandas as pd
from pandas_gbq import to_gbq

csv_url = "https://storage.googleapis.com/ds_data_import/chicago_bulls_1995_1996.csv"
chicago_bulls = pd.read_csv(csv_url, header=None, skiprows=1, names=['number', 'player', 'pos', 'ht', 'wt', 'birth_date', 'college'])
to_gbq(chicago_bulls, destination_table='fav_nba_teams.chicago_bulls', project_id='YOURPROJECTID', if_exists='replace', private_key='YOURSERVICEACCOUNT')
1
2
3
4
5
6
7

將 chicago_bulls 資料框匯入先前建立空的資料集 fav_nba_teams 之下

R 語言

使用 DBI 套件提供的 dbConnect() 函數建立 R 與 BigQuery 資料庫的連結以及同樣源自 DBI 的 dbWriteTable() 函數將 boston_celtics 資料框匯入先前建立空的資料集 fav_nba_teams 之下,並將表格命名為 boston_celtics;這裡參數要指定改用 BigQuery 的連接器(Connector)、專案 id 與資料集,在第一次使用 dbWriteTable() 函數時,會透過瀏覽器來進行身份認證,再將認證碼貼回 RStudio 就可以完成表格的寫入。

# install.packages(c("RMySQL", "bigrquery"))
library(DBI)
options("httr_oob_default" = TRUE)

csv_url <- "https://storage.googleapis.com/ds_data_import/boston_celtics_2007_2008.csv"
boston_celtics <- read.csv(csv_url, header = FALSE, skip = 1, col.names = c('number', 'player', 'pos', 'ht', 'wt', 'birth_date', 'college'))

con <- dbConnect(
  bigrquery::bigquery(),
  project = "datainpoint",
  dataset = "fav_nba_teams"
)
dbWriteTable(con, name = "boston_celtics", value = boston_celtics, overwrite = TRUE)
1
2
3
4
5
6
7
8
9
10
11
12
13

透過瀏覽器認證身份

透過瀏覽器認證身份

複製認證碼

將認證碼貼回 RStudio

將 boston_celtics 資料框匯入先前建立空的資料集 fav_nba_teams 之下

如何讀取資料:BigQuery

Python

讀取資料庫中的表格只要透過 pandas_gbq 的 read_gbq() 函數即可輸入 SQL 查詢語法讀取所有或部分表格資料。

from pandas_gbq import read_gbq

project_id = 'YOURPROJECTID'
private_key = 'YOURSERVICEACCOUNT'
sql_statement = "SELECT * FROM fav_nba_teams.chicago_bulls;"
chicago_bulls = read_gbq(sql_statement, project_id=project_id, private_key=private_key)
chicago_bulls
1
2
3
4
5
6
7

透過 pandas_gbq 的 read_gbq() 函數即可輸入 SQL 查詢語法讀取所有表格資料

from pandas_gbq import read_gbq

project_id = 'YOURPROJECTID'
private_key = 'YOURSERVICEACCOUNT'
sql_statement = "SELECT * FROM fav_nba_teams.chicago_bulls WHERE number IN (23, 33, 91);"
trio = read_gbq(sql_statement, project_id=project_id, private_key=private_key)
trio
1
2
3
4
5
6
7

透過 pandas_gbq 的 read_gbq() 函數即可輸入 SQL 查詢語法讀取部分表格資料

R 語言

讀取資料庫中的表格同樣也需要先利用 DBI 的 dbConnect() 函數建立 R 語言與資料庫的連結引擎,接著可以透過 DBI 的 dbReadTable() 函數讀入整個表格;也可以透過 dbGetQuery() 函數輸入 SQL 查詢語法讀取部分表格資料。

# install.packages(c("bigrquery", "RMySQL"))
library(DBI)

con <- dbConnect(
  bigrquery::bigquery(),
  project = "datainpoint",
  dataset = "fav_nba_teams"
)

boston_celtics <- dbReadTable(con, name = 'boston_celtics')
View(boston_celtics)
1
2
3
4
5
6
7
8
9
10
11

透過 DBI 的 dbReadTable() 函數讀入整個表格

# install.packages(c("bigrquery", "RMySQL"))
library(DBI)

con <- dbConnect(
  bigrquery::bigquery(),
  project = "datainpoint",
  dataset = "fav_nba_teams"
)

sql_statement <- "SELECT * FROM fav_nba_teams.boston_celtics WHERE number IN (34, 5, 20);"
gap <- dbGetQuery(con, statement = sql_statement)
View(gap)
1
2
3
4
5
6
7
8
9
10
11
12

透過 dbGetQuery() 函數輸入 SQL 查詢語法讀取部分表格資料

如何啟動 Google Firebase

假如需要以彈性靈活的 JSON 檔案建置 NoSQL 資料庫,已經被 Google 收購的 Firebase 服務不僅能夠以集合(Collections)與文件(Documents)儲存 JSON 檔案,還具備即時(Realtime)更新功能,不僅對 Python 與 R 的支援良好,更同時支援 Web、Android 與 iOS 開發,受到許多新創與資料科學團隊的青睞;透過下列步驟就可以啟動 Google Firebase 服務。

  1. 前往 Google Firebase 首頁,點選開始使用
  2. 點選新增專案
  3. 為專案取名
  4. 點選專案設定
  5. 在專案設定畫面中點選 Service Account,接著點選產生新的私密金鑰
  6. 將私密金鑰下載到電腦中妥善保管
  7. 點選資料庫服務
  8. 點選開始使用 Realtime Database
  9. 選擇測試模式之後開始使用

前往 Google Firebase 首頁,點選開始使用

點選新增專案

為專案取名

點選專案設定

在專案設定畫面中點選 Service Account,接著點選產生新的私密金鑰

下載到電腦中妥善保管

點選資料庫服務

點選開始使用 Realtime Database

選擇測試模式之後開始使用

如何建立資料:Firebase

接著我們將以 JSON 檔案格式儲存的 1995 至 1996 年的芝加哥公牛隊、2007 至 2008 年的波士頓賽爾提克隊基本資訊分別以 Python 和 R 語言匯入 Firebase,以供後續查詢使用。

Python

我們需要使用 firebase_admin 建立 Python 與 Firebase 資料庫的連結以及身份認證,開始之前得先在終端機安裝好需要的模組。

pip install firebase_admin
1

接著是利用先前已經下載好的憑證與 Firebase 網址(顯示於資料庫頁面)啟動連結。

Firebase 網址(顯示於資料庫頁面)

import firebase_admin
from firebase_admin import credentials

cred = credentials.Certificate('PATHTOYOURSERVICEACCOUNT') # 替換成自己的 Service Account 本機位址
firebase_admin.initialize_app(cred, {
    'databaseURL' : 'YOURDATABASEURL' # 替換成自己的 Firebase 網址
})
1
2
3
4
5
6
7
<firebase_admin.App at 0x7fd57ba1cef0>
1

成功啟動以後,就可以匯入以 dict 型別儲存的 1995 至 1996 年的芝加哥公牛隊基本資訊。

from firebase_admin import db
from requests import get

json_url = 'https://storage.googleapis.com/ds_data_import/chicago_bulls_1995_1996.json'
chicago_bulls_dict = get(json_url).json()
root = db.reference()
root.child('chicago_bulls').push(chicago_bulls_dict)
1
2
3
4
5
6
7

匯入以 dict 型別儲存的 1995 至 1996 年的芝加哥公牛隊基本資訊

R 語言

使用 fireData 套件提供的 upload() 函數匯入以 list 型別儲存的 2007 至 2008 年波士頓賽爾提克隊基本資訊,函數中需要輸入 Firebase 網址作為參數。

# install.packages(c("devtools", "jsonlite"))
# devtools::install_github("Kohze/fireData")
library(fireData)
library(jsonlite)

json_url <- "https://storage.googleapis.com/ds_data_import/boston_celtics_2007_2008.json"
boston_celtics_list <- fromJSON(json_url)

projectURL <- "YOURPROJECTURL" # 替換成自己的 Firebase 網址
upload(boston_celtics_list, projectURL = projectURL, directory = "boston_celtics")
1
2
3
4
5
6
7
8
9
10
[1] "boston_celtics/-LDj0KQInS4DDKvCxdDs"
1

匯入以 list 型別儲存的 2007 至 2008 年波士頓賽爾提克隊基本資訊

如何讀取資料:Firebase

Python

以資料庫物件的 .reference()方法取得文件參照,接著以 .get() 方法取得1995 至 1996 年的芝加哥公牛隊基本資訊。

from firebase_admin import db

ref = db.reference('chicago_bulls')
chicago_bulls = ref.get()
chicago_bulls
1
2
3
4
5

以 Python 讀取 Firebase 資料

R 語言

使用 fireData 套件提供的 download() 函數將匯入以 list 型別儲存的 2007 至 2008 年波士頓賽爾提克隊基本資訊,函數中需要輸入 Firebase 網址與文件 id 作為參數。

# install.packages("devtools")
# devtools::install_github("Kohze/fireData")
library(fireData)

projectURL <- "YOURPROJECTURL" # 替換成自己的 Firebase 網址
fileName <- "boston_celtics/-LDj0KQInS4DDKvCxdDs" # 替換成自己的文件 id
boston_celtics_list <- download(projectURL = projectURL, fileName = fileName)
boston_celtics_list
1
2
3
4
5
6
7
8

以 R 讀取 Firebase 資料

小結

在這個小節中我們簡介如何利用 Python 與 R 語言在雲端資料庫服務:Amazon Web Service 的 MySQL、Google Cloud Platform 的 BigQuery 與 Google Firebase 中建立與讀取資料。

各種雲端資料庫服務看似五花八門,操作手冊及文件讓人眼花撩亂,實際上只要確實掌握兩個要點:一是與雲端資料庫服務連結的憑證(如何認證權限者);二是程式語言對應不同雲端資料庫服務所使用的模組或套件,就可以順利使用這些功能強大的服務。

延伸閱讀