本次練習,要實作一個ETL (Extract-Transform-Load) web service,然後分析Ubike一天的使用情況。何謂ETL?簡單的說,就是去抓別人的資料,經過整理,然後丟進自己的資料庫。透過這樣的做法,可以把資料整理成想要的形式,方便後續的分析以及處裡。

實作分為兩個部分:

1.使用python抓取Ubike資料,存放在mysql資料庫中

2.利用Django網頁框架來呈現資料庫的資料

以下先針對第一部份作說明


Ubike開放資料

Ubike的開放資料可以在台北市政府開放資料平台找到,連結在此

網站上的說明已經很清楚了,透過以下連結可以拿到經gz壓縮的json檔案 (有點不解為何要壓縮…)

http://data.taipei/youbike

下載完後,在windows系統下解壓縮,會發現檔案損毀,無法開啟。

將檔案的附檔名改為txt後,居然就順利打開了@@

雖然網站上面寫

部分瀏覽器如Chrome下載後會自動解壓縮,請留意!

但還是很不解,我平時用Chrome下載壓縮檔都不會自動解壓了,為啥這會自動勒?不管啦,反正在實作的時候是在Linux環境下,不會受到影響。


Ubike資料說明

每筆資料有以下14個欄位,其中有部分是不會變動的資料,如sno、sna、sarea等等,待會放資料庫時會獨立出來放

sno:站點代號
sna:場站名稱(中文)
tot:場站總停車格
sbi:場站目前車輛數量
sarea:場站區域(中文)
mday:資料更新時間
lat:緯度
lng:經度
ar:地(中文)
sareaen:場站區域(英文)
snaen:場站名稱(英文)
aren:地址(英文)
bemp:空位數量
act:全站禁用狀態

接著用json viewer來看看下載下來的json檔案,可以看到資料被包在retVal裡面

ubike1

用python抓取資料

使用urllib來抓檔案,透過gzip解壓縮後,將json資料load出來

#getBike.py 
#!/usr/bin/env python
import urllib
import gzip
import json
#from pprint import pprint
url = "http://data.taipei/youbike"
urllib.urlretrieve(url, "data.gz")
f = gzip.open('data.gz', 'r')
jdata = f.read()
f.close()
data = json.loads(jdata)
for key,value in data["retVal"].iteritems():
    sno = value["sno"]
    sna = value["sna"]
    print "NO." + sno + " " + sna

這邊要注意的是,load出來的data是dict type,所以用iteritems來抓值

可以看到結果如下:

$ ./getBike.py 
NO.0134 捷運芝山站(2號出口)
NO.0135 捷運石牌站(2號出口)
NO.0136 國立臺北護理健康大學
NO.0137 國防大學
NO.0039 南港世貿公園
NO.0038 臺灣師範大學(圖書館)
...

安裝MySQL資料庫

如果已經安裝過MySQL,可以跳過此部分。

這邊為了要加快速度,採用PHPMyAdmin圖形介面來進行資料庫操作,因此直接安裝LAMP,步驟如下:

$ sudo apt-get install apache2
$ sudo apt-get install mysql-server
$ sudo apt-get install php5 libapache2-mod-php5
$ sudo apt-get install phpmyadmin
$ sudo ln -s /etc/phpmyadmin/apache.conf /etc/apache2/conf-enabled/phpmyadmin.conf
$ sudo /etc/init.d/apache2 restart

安裝完後,可以透過以下路徑看到管理介面

http://YOUR_IP/phpmyadmin

資料庫設計

將固定資料放在一起(Table info),會變動的如目前數量、時間等等放一起(Table data),然後利用foreign key做關聯

另外需要注意的是中文問題,這裡我使用UTF-8儲存,後面的python程式也是設定UTF-8編碼

資料表結構 info


CREATE TABLE IF NOT EXISTS `info` (
 `sno` int(4) NOT NULL,
 `sna` varchar(100) CHARACTER SET utf8 NOT NULL,
 `sarea` varchar(20) CHARACTER SET utf8 NOT NULL,
 `lat` varchar(20) CHARACTER SET utf8 NOT NULL,
 `lng` varchar(20) CHARACTER SET utf8 NOT NULL,
 `ar` varchar(100) CHARACTER SET utf8 NOT NULL,
 `sareaen` varchar(20) CHARACTER SET utf8 NOT NULL,
 `snaen` varchar(100) CHARACTER SET utf8 NOT NULL,
 `aren` varchar(100) CHARACTER SET utf8 NOT NULL,
 PRIMARY KEY (`sno`),
 KEY `sno` (`sno`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

資料表結構 data


CREATE TABLE IF NOT EXISTS `data` (
 `index` bigint(20) NOT NULL AUTO_INCREMENT,
 `sno` int(4) NOT NULL,
 `tot` int(3) NOT NULL,
 `sbi` int(3) NOT NULL,
 `bemp` int(3) NOT NULL,
 `act` int(1) NOT NULL,
 `utime` datetime NOT NULL,
 PRIMARY KEY (`index`),
 KEY `sno` (`sno`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=0 ;

由於我直接透過phpMyAdmin建資料庫,上面的SQL是由系統產生,僅供參考

在phpMyAdmin上設定foreign key

進入 結構->關聯清單,就可以看到以下畫面:

ubike2

將data.sno與info.sno關聯,並將型態設為RESTRICT

把資料撈進資料庫

前面我們已經可以成功取得資料,接著利用python的MySQLdb這個套件連接MySQL,把資料丟進去。

透過pip安裝

$ pip install MySQL-python

這裡寫兩隻程式,一隻(getInfo.py)是抓固定資料(只會執行一次),另一隻(getData.py)抓變動資料(每分鐘執行一次)

getInfo.py程式碼如下:

#!/usr/bin/env python
# -*- coding: utf-8 -*-
import urllib
import gzip
import json
import MySQLdb

url = "http://data.taipei/youbike"
print "downloading with urllib"
urllib.urlretrieve(url, "data.gz")
f = gzip.open('data.gz', 'r')
jdata = f.read()
f.close()
data = json.loads(jdata)
conn = MySQLdb.connect(host="localhost", user="root", passwd="123456", db="bike")
c = conn.cursor()
conn.set_character_set('utf8')

for key,value in data["retVal"].iteritems():
	sno = value["sno"]
	sna = value["sna"]
	sarea = value["sarea"]
	lat = value["lat"]
	lng = value["lng"]
	ar = value["ar"]
	sareaen = value["sareaen"]
	snaen = value["snaen"]
	aren = value["aren"]
	
	sql = "INSERT INTO info(sno,sna,sarea,lat,lng,ar,sareaen,snaen,aren) VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s)"
	try:
		c.execute(sql,(sno,sna,sarea,lat,lng,ar,sareaen,snaen,aren) )
		conn.commit()
	except MySQLdb.Error,e:
		print "Mysql Error %d: %s" % (e.args[0], e.args[1])
conn.close()

getData.py程式碼如下:


#!/usr/bin/env python
# -*- coding: utf-8 -*-
import urllib
import gzip
import json
import MySQLdb
from datetime import datetime

url = "http://data.taipei/youbike"
#print "downloading with urllib"
urllib.urlretrieve(url, "data.gz")
f = gzip.open('data.gz', 'r')
jdata = f.read()
f.close()
data = json.loads(jdata)
conn = MySQLdb.connect(host="localhost", user="root", passwd="123456", db="bike")
c = conn.cursor()
conn.set_character_set('utf8')

for key,value in data["retVal"].iteritems():
	sno = value["sno"]
	tot = value["tot"]
	sbi = value["sbi"]
	bemp = value["bemp"]
	act = value["act"]
	
	sql = "INSERT INTO data(sno,tot,sbi,bemp,act,utime) VALUES(%s,%s,%s,%s,%s,%s)"
	try:
		c.execute(sql,(sno,tot,sbi,bemp,act,datetime.now()))
		conn.commit()
	except MySQLdb.Error,e:
		print "Mysql Error %d: %s" % (e.args[0], e.args[1])
		
	sql = "DELETE FROM data WHERE TO_DAYS(NOW()) - TO_DAYS(utime) > 1"
	try:
		c.execute(sql)
		conn.commit()
	except MySQLdb.Error,e:
		print "Mysql Error %d: %s" % (e.args[0], e.args[1])	

conn.close()

因為只記錄一天的資料,所以將大於一天的資料清掉

DELETE FROM data WHERE TO_DAYS(NOW()) - TO_DAYS(utime) > 1

使用crontab讓程式每分鐘執行一次

cron是在root下執行,路徑與目前使用者不同
建立一個script,裏頭會切換到抓資料程式的位置,並執行他

$ vim autoGet.sh
#!/bin/bash
cd /home/user
python getData.py

打開crontab,加入一條規則

$ crontab -e
*/1 * * * * /bin/bash /home/user/autoGet.sh

沒問題的話,就可以等著收資料囉~


繼續閱讀 用python抓取ubike開放資料(顯示篇)

可以參考我的Source code Github source code


參考資料

Django Girls 學習指南

[MySQL] 在phpMyAdmin 設定 foreign key

mysql日期和時間函數不求人