dsq(DataStation Query)是用來查詢資料檔案的SQL工具，使用的是SQLite的SQL語法，支援下列檔案格式：

  • CSV
  • JSON
  • Excel (XLSX與XLS)
  • Apache log (error and access): 尚不完整
  • Nginx Access Logs: 尚不完整
  • ...

[!INFO] 支援格式
https://github.com/multiprocessio/dsq#supported-data-types

1. 安裝

直接到GutHub的Releases下載執行檔，不用安裝。

2. 使用範例

dsq covid19.json "select * from {}"
dsq --pretty covid19.json "select * from {}" | more
dsq --pretty covid19.json "select count(1) from {}"
dsq --pretty covid19.json "select * from {} where Cast(deaths as number) > 100000 order by deaths"

rem csv轉JSON
dsq covid19_global_cases_and_deaths.csv > cases.json

rem Excel
dsq Report2.xlsx "SELECT * FROM {\"Sheet2\"}"

rem 多檔操作 (join)
dsq testdata/join/users.csv testdata/join/ages.json "select {0}.name, {1}.age from {0} join {1} on {0}.id = {1}.id"

 

[!WARNING] 注意

  • 欄位要用小寫
  • from {\"工作表\"}: Windows的SQL敘述裡用到雙引號時要加反斜線

3. 相關鏈接

4. 教學影片

 

＃＃

0
0