Skip to content

2025

  • in Tech

251127tech duckdb

  • 2025-12-02 補充

DuckDB - 直接用SQL語法篩選資料，免匯入

想收集TBIA所有「紀錄者/採集者」 (recordedBy)的名字，先想到用之前有使用過的XSV，但要安裝時發現已經沒有在維護了，XSV的介紹叫大家改用qsv，跟XSV一樣參數簡單、速度超快。TBIA下載所有的資料，解壓縮完有27.9GB (29,536,885筆資料)，用qsv讀取recordedBy跟拿掉重複輸出，大概幾十秒就做完了。

qsv select recordedBy tbia_{hash}.csv | qsv dedup

但我想到要比對一下人名跟資料集名稱 (datasetName)對映，順便也統計一下筆數，似乎就要寫一點點程式，查一下資料才發現duckdb這套強大的資料庫可以直接用我熟悉的SQL語法處理，甚至不用匯入，直接對CSV欄位名稱下語法就好，速度一樣超快，還有漂亮的輸出，真是相見恨晚。

先簡單列出不重複的人名

duckdb-recordedBy

加上資料集名稱與統計關聯的資料筆數

duckdb -c "SELECT recordedBy, datasetName, count(*) FROM 'tbia_6927dd263f39e705b9843d70.csv' GROUP BY recordedBy, datasetName ORDER BY count(*)"

duckdb-recordedBy-count

好直覺、好快 ~~

DuckDB – An in-process SQL OLAP database management system

補充應用:

篩選出資料集名稱為: "科博典藏 (NMNS Collection)-真菌學門" 的分類資訊`。

duckdb -csv -c " select id, kingdom, kingdom_c, phylum, phylum_c, 'class', class_c, 'order', order_c, family, family_c, genus, genus_c, scientificName, common_name_c from 'tbia_6927dd263f39e705b9843d70.csv' where datasetName='科博典藏 (NMNS Collection)-真菌學門'" > nmns-fungi.csv

-csv 設定 .mode csv，不然的話預設會匯出duckbox (有框線)

GeoPick - 自行定義地理區域範圍的好用工具

在生物多樣性資訊開放資料標準領域，關於地名、地理位置、精確度... 要用什麼格式描述與紀錄，有洋洋灑灑的一篇指引提供參考 Georeferencing Best Practices，但人生已經很難了，沒有好用的工具會更困難。於是有人寫了好用的服務讓這件事變的更簡單、更直覺，就是︰ Geopick。

間單的說只要4個步驟︰

  1. 選取已知或自行定義地理區域範圍
  2. 填寫誰定義的、備註
  3. 驗證 & 產出

就完成了。

詳細來說有兩種使用情境，第一種︰

直接使用已知或常用地名

這邊用「天母」這個含糊的地名來當例子，天母這個地名非正式行政區的名字，不同時期、不同背景，會有不同的範圍，導致有所謂的真正的天母、老天母、天母邊緣、不是天母(北投)，甚至有人開玩笑建商也有一個最廣的範圍定義...

先不管確切範圍，在界面上方的搜尋欄輸入: "天母"，就有下拉選單的資料，地名資料來自於OpenStreetMap的Nominatim，然後就有一個被標注在天母圓環的點位了 (這邊也不討論「真」天母圓環是那一個，那又是另一個故事了，這邊指的是天母東/西路跟中山北路，假日有市集的那個圓環)，然後填上georeferencedBy (誰填的) 這個欄位就自動產生 locality 地名欄位的資訊:

Nominatim: 天母, 士林區, 蘭雅, 臺北市, 11152, 臺灣

有趣的是這個工具沒有使用 DarwinCore常用的 country (國家)、stateProvince (省/州)、county (縣/市/郡)、municipality (地方行政區)... 這樣的欄位，大概是考慮各國系統太複雜了，本國人常常也不一定會完全理解 (隨便問一題，你知道台灣現在有那幾個城市是省轄市嗎)，總之這個locality就填寫完成了，前面還加上" Nominatim:"，交待了來源。

自行定義範圍與名稱

第二種，就是要自行命名一個我自己認定的範圍，也想給一個名稱，直接用地圖上的劃線工具把我要的區域框出來，然猴給locality(地點名稱)、georeferencedBy (誰填的) 跟georeferenceRemarks(說明備註)，就這樣。

然後他就自動產生可以盡可能詳細的放入DarwinCore欄位的資料，直接複製打包帶走，很方便。

示範內容如下圖:

至於這些詳細欄位內容的定義與使用就請移駕Darwin Core Quick Reference Guide - Darwin Core (Location Class)。

原始碼: rtdeb/GeoPick: Simple uncertainty radius calc for lines and polygons

真心感謝無私奉獻的Geopick、OpenStreetMap、Nominatim、ESRI World Imagery與GBIF ~

  • in Tech

我的Debian GNU/Linux 桌面環境

安裝Debian 12、Gnome Desktop，語系選繁體中文。

新的Gnome不習慣，login的畫面點齒輪改成Gnome Classic

(2025.04.16)

基本桌面設定

  • 輸入法: 英文Dvorak、中文新酷音 (設定直接改就好了)
  • 終端機: 換一下配色

Add sudoer

edit /etc/sudoers

%your-username ALL=(ALL:ALL) ALL

logout/login 後才有作用

Caps Lock改成 Ctrl

修改 /etc/default/keyboard

XKBOPTIONS="ctrl:nocaps"

Install Packages/Tools

Update packages

sudo apt update
sudo apt upgrade

Development

sudo apt install vim emacs git zsh tmux curl wget tig sqlite3
sudo apt install build-essential libreadline-dev libsqlite3-dev zlib1g-dev libssl-dev liblzma-dev libbz2-dev tk-dev libffi-dev llvm libncurses5-dev libncursesw5-dev liblzma-dev

Set default SHELL to zsh

chsh -s /bin/zsh
logout/login 後才有作用

Docker

https://docs.docker.com/engine/install/debian/

好用軟體

Command line

  • lazydocker, lazygit
  • fastfetch (好用好看的system info)
  • Alacritty (好用的 Terminal Emulator，標榜OpenGL、速度快)

Desktop

Floorp

到 https://ppa.floorp.app/ 按步驟安裝

Chromium

apt get install chrome

Zotero

Installation Instructions

Debian 12會報錯，要 install libdbus-glib-1-2

系統管理

  • bmon: command-line 看網路狀況
  • in Tech

硬碟壞掉處理

我的Intel Nuc小電腦的電源不知道什麼原因按了都沒反應，本來以為是電源線接觸不良或變壓器壞掉，送檢測結果是硬碟壞掉，但是不知道為什麼連開機BIOS也沒出現。

總之就把硬碟拆下來接外接盒，其他電腦也是讀不到。

fdisk 有看到sdb device，想要手動mount，出現以下錯誤。

sudo mount -t ext4 /dev/sdX1 /mnt/mydisk

mount: /mnt/mydisk: wrong fs type, bad option, bad superblock on /dev/sdb1, ...

以下靠Chat-GPT亂試，至少把資料救回來了。

用 lsblk看也是有device，但是沒有partition。

lsblk -f

用fsck檢查 (忘記這個可以幹嘛)

sudo fsck -b 32768 /dev/sdb1

看仔細

lsblk -o NAME,SIZE,FSTYPE,TYPE,MOUNTPOINT

會有以下幾種狀況

Situation Meaning Action
Blank disk Partition and format Partition and format
Corrupted disk Broken partition table or FS Try recovery
Filesystem directly on disk but not detected Rare, but possible Try force mounting or inspecting

重新請Chat-GPT統整

Step 1: Identify the Device

sudo file -s /dev/sdX

Step 2: Check Filesystem Signature

sudo file -s /dev/sdb

Step 3: Recovery or Format?

=> Recover data

先安裝testdisk這個神奇工具

sudo apt install testdisk

進入文字互動介面

sudo testdisk
testdisk 操作
  • [Create] a new log file
  • Select /dev/sdb
  • Choose partition table type (Intel = MBR, or EFI GPT if it's a newer disk)
  • [Analyse] to find lost partitions
  • [Quick Search] or [Deeper Search] if needed
  • When partitions are found, press P to list files inside
  • If files are there, you can:
  • Copy files (press C)
  • Or [Write] to restore the partition table

reboot 重開機，用lsblk看，partition回來了。testdisk太神了!!!

開機磁區還是壞的，開機抓不到硬碟，但是可以用外接盒讀回其他資料，至少資料有救回來。

重灌作業系統，分割磁區後就又可以正常使用了。

  • in Tech

處理CSV的命令列利器: XSV

在Global Names的Tutorial: Parse names into a CSV file看到一個處理CSV超有效率的工具。

通常拿到一個csv，想快速得到這個csv的欄位有那些，總共幾筆，隨便列出幾筆看看資料長什麼樣子，然後決定只取其中幾個欄位資料，做簡單的條件篩選，最後匯出另一個我要的csv => 這些處裡可以用xsv一行指令做完。

安裝

因為是rust程式寫的，要先裝Cargo。

以Linux為例:

curl https://sh.rustup.rs -sSf | sh

然後就可以用cargo安裝xsv

cargo install xsv

在shell的rc檔加上執行路徑

source "$HOME/.cargo/env""

處理情境: 整理TaiCOL物種清單

TaiCOL下載名錄，解壓縮是78MB，資料筆數233,440，不到10000元的NUC小PC處理完大概最多花不到2秒。

下載: 臺灣物種名錄 Catalogue of Life in Taiwan

列出headers

xsv headers TaiCOL_name_20250221.csv

找出type_name_id跟namecode欄位有值的，並且隨機列出10列 (sample 10)

xsv search -s type_name_id '.+' TaiCOL_name_20250221.csv |xsv search -s namecode '.+' | xsv select name_id,type_name_id,namecode,original_name_id | xsv sample 10

轉換檔案: 篩選出分類階層是種(Species)的

xsv search -s rank 'Species' TaiCOL_name_20250221.csv | xsv select simple_name,name_author,s2_rank,latin_s2,s3_rank,latin_s3,common_name_c,alternative_name_c,kingdom,kingdom_c,phylum,phylum_c,class,class_c,order,order_c,family,family_c,genus,genus_c | xsv fmt -t '\t' > out.csv
  • search -s rank 'Species': 找出rank欄位等於"Species"
  • xsv select simple_name,...: 取用那些欄位
  • xsv fmt -t '\t': 轉成tab分隔 (避免處理雙引號)

篩選出有文獻的學名

xsv search -s protologue '.+' TaiCOL_name_20250221.csv |xsv select name_id,type_name_id,namecode,original_name_id,protologue | wc -l

結論

以前處理這些事情筆數少用Excel/LiberOffice，幾萬筆以上可能要開OpenRefine，不然就要寫script程式處理 (Python的話可能用Pandas之類的套件)或是開jupyter notebook處理，都是很麻煩。XSV速度超快、pipeline處理資料很直覺，適合處理大檔案。

  • in Tech

關於CSV的各種編碼，還有BOM的雜記

各種CSV格式

Windows的Notepad++轉換編碼後輸出，用hexdump看內容結果。

欄位名稱加雙引號

d 的編碼: 64

00000000  22 64 61 74 61 49 44 22  2c 22 65 76 65 6e 74 49  |"dataID","eventI|
00000010  44 22 2c 22 65 76 65 6e  74 44 61 74 65 22 2c 22  |D","eventDate","|
00000020  73 65 61 73 6f 6e 22 2c  22 79 65 61 72 22 2c 22  |season","year","|

欄位名稱沒有雙引號，沒有BOM header

d 的編碼: 64

00000000  64 61 74 61 49 44 2c 65  76 65 6e 74 49 44 2c 65  |dataID,eventID,e|
00000010  76 65 6e 74 44 61 74 65  2c 73 65 61 73 6f 6e 2c  |ventDate,season,|
00000020  79 65 61 72 2c 72 65 67  69 6f 6e 2c 6c 6f 63 61  |year,region,loca|

轉換成UTF-8 BOM

開頭: EF BB BF

d 的編碼: 64 (同ASCII)

00000000  ef bb bf 22 64 61 74 61  49 44 22 2c 22 65 76 65  |..."dataID","eve|
00000010  6e 74 49 44 22 2c 22 65  76 65 6e 74 44 61 74 65  |ntID","eventDate|
00000020  22 2c 22 73 65 61 73 6f  6e 22 2c 22 79 65 61 72  |","season","year|

轉換成UTF-16 LE BOM (Little Endian)

開頭: FF FE (11111111 11111110)

d 的編碼: 64 00

00000000  ff fe 22 00 64 00 61 00  74 00 61 00 49 00 44 00  |..".d.a.t.a.I.D.|
00000010  22 00 2c 00 22 00 65 00  76 00 65 00 6e 00 74 00  |".,.".e.v.e.n.t.|
00000020  49 00 44 00 22 00 2c 00  22 00 65 00 76 00 65 00  |I.D.".,.".e.v.e.|

轉換成UTF-16 BE BOM (Big Endian)

開頭: FE FF (11111110 11111111)

d 的編碼: 00 64

00000000  fe ff 00 22 00 64 00 61  00 74 00 61 00 49 00 44  |...".d.a.t.a.I.D|
00000010  00 22 00 2c 00 22 00 65  00 76 00 65 00 6e 00 74  |.".,.".e.v.e.n.t|
00000020  00 49 00 44 00 22 00 2c  00 22 00 65 00 76 00 65  |.I.D.".,.".e.v.e|

其他:

  • 用LibreOffice、Google Spreadsheet匯出的預設欄位沒有雙引號，也沒加BOM header

(有空再來補各種版本Excel的匯出)

  • in Tech

自然攝影中心 (nc.biodiv.tw)

系統狀態

系統板本: vBulletin 3.6.3 (付費版本) - PHP: 5.6 - MySQL: 5.5

附加檔案: - 磁碟空間: 202,188MB - 檔案數: 1,669,049 (包含.attach跟.thumb)

問題

  • 系統老舊
  • 資安風險 -常被通報
  • index.php, includes目錄等被植入莫名程式碼 (2021年底-2022年初移除)

目標

  • 把內容取出，結構化部分內容 (植物圖鑑 小圖連大圖)
  • 以靜態HTML頁面呈現，查詢
  • 封存並關閉現有網站

方法

  • 從 vBulletin 資料庫擷取"小圖連大圖"的文章串
  • 下載並整理相關的圖片檔案
  • 轉換內容為 11ty 可用的 Markdown 格式
  • 生成靜態 HTML 網站

實際處理紀錄

分析Database Schema

文字內容

  • 主要內容在post資料表裡，thread會關聯很多筆post
  • post有些是科的目錄，有些是種的內容，要區分
  • "小圖連大圖"是從一個threadid: 27653開始，主要是科(family)的連結
  • 大部分的各科的post包含屬(genus)，會有科以下的物種(Species) 學名，少部分比較大的科如豆科菊科會是thread，然後再關聯許多筆post
  • 內容有很多BBCode

照片檔

  • 路徑是/src/html/nc/vbb35data/[1-9]/的目錄
  • 副檔名是.attach跟.thumb
  • 讀取照片是attachment.php處理
  • 路徑規則是user_id位數分開，如: 1234，檔案的路徑就是 ~/vbb35data/1/2/3/4/xxx.attachment (xxx即attachmentid)
MySQL to SQLite

把需要的資料取出來，轉成Sqlite方便處理

  1. Dump vbb35post and encoded as latin1
mysqldump -u root -p nc_biodiv --default-character-set=latin1 nc_biodiv vbb35post > post.sql
mysqldump -u root -p nc_biodiv --default-character-set=latin1 nc_biodiv vbb35attachment > attachment.sql

  1. Change encoding to utf8, edit post.sql and attachment.sql (latin1 -> utf8mb4)

    • !SET NAMES utf8mb4
    • DEFAULT CHARSET utf8mb4

  2. Import back to mysql (utf8 client readable for better check)

    1. create database nc_nice
    2. mysql -u root -p nc_nice < post.sql
    3. mysql -u root -p nc_nice < attachment.sql

  3. Use adminer.php to export csv

  4. import to sqlite3

sqlite> .headers on
sqlite> .mode csv
sqlite> .import post.csv post

Note

  1. 跟 4. 步驟也可以mysql直接轉csv或sql, import sqlite 甚至，python mysql client 直接讀取mysql也可以，但是我比較熟adminer.php，也習慣用adminer.php查看資料，所以才花點力氣轉來轉去。
Transform Data

寫一個script處理: 1-parse-data.py

  1. 從小圖連大圖, 複製貼上產生family.txt，純手工，寫parser效益太低
  2. 這一串thread的post取出所有科跟種的資料

  3. parse物種post的內容

  4. 取得higher taxa (很大概，視情況手工處理細節)

  5. 圖檔列表attach (list)

    • 小圖連大圖系列只需要26635張圖

  6. 產出整理後的資料: eggs.csv (4千多筆)

11ty Static Site Generator for Render

2-make-markdown.py

  • 從整理好的csv轉換成11ty的markdown格式跟metadata
  • 產生所有科跟屬的清單頁面，直接匯出到 11ty_folder/posts/plantae-index
  • 種的頁面匯出到 11ty_folder/posts/plantae
  • 首頁的科清單是用產出的family.txt，手動貼到index.njk裡

11ty build:

11ty配合High Performance Blog template產生最終呈現網頁。

處理參考High Performance Blog的指令

Note

有4千多個檔案，會發生JavaScript heap out of memory error 執行 NODE_OPTIONS="--max-old-space-size=4096" npx eleventy 似乎就好了

待辦事項

New Site (NC2)

  • build all pages (2025-02-03)
  • 11ty deployment to (Cloundflare or S3/Cloudfront?)
  • image transfer and link
  • decorate 11ty layout
  • search (install findpage plugin?)

Legacy Site (nc.biodiv.tw)

  • archive

貢獻

歡迎任何貢獻！

Zotero

管理研究文獻的Zotero真的太好用了，我一直沒有用的原因是自己沒有在做研究，所以覺得用不到，但拿來當筆記軟體，處理輸入的資料也是超級好用的，相用恨晚。 Zotero的好用不是他有多強大花俏的功能，而是簡單到剛剛好就可以！ 之前用過各種筆記軟體，EverNote, OneNote, Dropbox paper, HackMD, LogSeq, Notion… 現在花錢買Heptabase，(有空再來寫使用心得)結果也是建立一堆很難再被看到的垃圾資訊的卡片，也很難走到用視覺化處理、產出。input的東西還是要靠工具或紀律做最低限度的整理，例如，收藏資料要選型別 (blog, video, journal, website…)決定使用的資料欄位，一開始覺得很煩，但後來覺得很重要, 匯出的時候看到自己的垃圾曾經被過去的自己整理過會很感動🥹 然後，匯出方便、格式單純不囉嗦，有手機App版本，桌機也支援Linux，open source 加分… 誠摯推薦 https://www.zotero.org 我沒有做研究，所以我也不知道跟Endnote, Mendelay… 的差別，我也沒用過匯出的引用格式，畢竟我收錄的都是一些廢文、短影音、幹話… 難登大雅之堂。

分類

letter: my words (特有) bill: note (特有) manuscript: 討論

/⭐️