grasys blog

roapiでcsvにsqlでselectしてみる

yusukeh

こんにちはgrasys長谷川です。
最近このYouTube きょう何たべよ / Marco Macri を見ていて、パスタ作りにハマってます。
肉たたき、チーズ削り、細いトング、小さい調理用ピンセット、耐熱ガラスボウル、アルミのフライパン、お玉、パスタ茹で鍋、すり鉢と調理器具を一通り揃えて、、、パスタ作りをしているんですが！！
近所のスーパーでイタリアンパセリとイタリアンバジルが手に入るときと手に入らない時でバラツキがある。。。

とうとう水耕栽培をはじめました🤣
6日くらいでイタリアンバジルが一番最初に🌱しました。

さて本題のblogネタですが、
ちょっと諸事情で社内のデータを調査するのにcsvが使いにくいなと調べていてroapiを見つけて、めちゃくちゃ個人的に好きになったのでここでご紹介します！

roapi

https://github.com/roapi/roapi

ROAPI automatically spins up read-only APIs for static datasets without requiring you to write a single line of code. It builds on top of Apache Arrow and Datafusion. The core of its design can be boiled down to the following:

https://github.com/roapi/roapi

ざっくり説明するといろんなデータ・フォーマットに対応していてアクセスできるデータを束ねて、
HTTP REST APIないしPostgres protocolでデータにアクセスして、
apache arrowとJSONとかに出力できる優れものです。

bigqueryに取り込んでおりゃーみたいなのあるけど、

  • データサイズが大きくない
  • jsonとかcsv

こうなら手元でノーコストでぱっとやっちゃった方が良い
やることあんまり変わらないし！

Install

自分のはmacbook proのM2なんですがbrewで入ります。

brew install roapi

Quick startあるのでそこみると普通に触れます。

Quick startの例をやっても仕方ないので、社内のcsvは見せられないのでkaggleでデータをお借りして簡単なサンプルをやろう！

blog書こうって思ったものの、出せるデータって意外と探すの大変😅

kaggleから適当なデータをDownload

kaggleは登録しないとデータ取れないので、適当に登録してください。
登録は割愛します。

別になんのデータでも良いので、これに決めた！

World’s Top 10 YouTubers in 2022

2022年のYouTuber Top 10みたいです。
808 B（ちっさい！）です。

Downloadしたデータをunzip

Downloadしたデータをunzip

> unzip archive.zip
Archive:  archive.zip
  inflating: Worlds Top 10 YouTubers in 2022.csv

なぜファイル名にスペースを使う・・・
いやいかん、データ使わせてくれてありがとうございます！

ちょっと中身をみてみよう

> bat Worlds\ Top\ 10\ YouTubers\ in\ 2022.csv
───────┬───────────────────────────────────────────────────────────────────────────────
       │ File: Worlds Top 10 YouTubers in 2022.csv
───────┼───────────────────────────────────────────────────────────────────────────────
   1   │ Channel Name,Subscribers,No. of Videos,No. of Views,Category,Audience,Start Date,Email,Current Location
   2   │ Vlad and Niki,87M,487,68381349509,Children Adventure,Children,23-Apr-2018,xxxxx@xxxxxxx.xxx,United States
   3   │ Zee Music Company,88.2M,6968,50353019372,Music,Adults,12-Mar-2014,NA,India
   4   │ WWE,91.1M,64096,71620753947,Wrestling�,Adults and Old age,11-May-2007,NA,United States
   5   │ Like Nastya,101M,417,83976126137,Kids Adventures,Children,6-Dec-2016,xxxxx@xxxxxxx.xxx,United States
   6   │ Kids Diana Show,101M,1021,82096974708,Children Adventure,Children,12-May-2015,xxxxx@xxxxxxx.xxx,United States
   7   │ MrBeast,105M,729,17412655348,Adventure and Charity,Adults,20-Feb-2012,NA,United States
   8   │ PewDiePie,111M,4506,28532763044,Games and adventure,Adults,29-Apr-2010,xxxxx@xxxxxxx.xxx,Japan
   9   │ Set India,143M,95050,1.30043E+11,"Film, Drama and others",All ages,21-Sep-2006,NA,India
  10   │ Cocomelon,143M,801,1.39617E+11,Kids Cartoons,Kids,21-Sep-2006, xxxxx@xxxxxxx.xxx,United States
  11   │ T-Series,225M,17514,2.02819E+11,Music,Adults,13-Mar-2006,,India
───────┴───────────────────────────────────────────────────────────────────────────────

batコマンドはcatみたいなやつです。A cat(1) clone with syntax highlighting and Git integration.

メールアドレスがあるから一応マスキング😎しときました。

ふんふん（オレ誰も知らない・・・）

ファイルネームのスペースを修正

えい！

> exa Worlds\ Top\ 10\ YouTubers\ in\ 2022.csv
Worlds Top 10 YouTubers in 2022.csv

えい！

> fn=$(exa Worlds\ Top\ 10\ YouTubers\ in\ 2022.csv)
> echo $fn
Worlds Top 10 YouTubers in 2022.csv

えい！

> echo ${fn,,}
worlds top 10 youtubers in 2022.csv

> echo ${fn,,} | sd " " "_"
worlds_top_10_youtubers_in_2022.csv

えいえい！

> fn=$(echo ${fn,,} | sd " " "_")

> echo $fn
worlds_top_10_youtubers_in_2022.csv

> cp Worlds\ Top\ 10\ YouTubers\ in\ 2022.csv $fn

普通にmvした方が速い🤣
blogの字数を稼ぎたかっただけです・・・

exaはlsみたいなの exa is a modern replacement for ls.
sdはsedみたいなの sd is an intuitive find & replace CLI.

headerがスペースあったりドットあったりで修正

えい！

> str=$(bat --plain worlds_top_10_youtubers_in_2022.csv | head -1)

> echo $str
Channel Name,Subscribers,No. of Videos,No. of Views,Category,Audience,Start Date,Email,Current Location

えい！

> echo ${str,,} | sd "\s" "_" | sd "no." "number"
channel_name,subscribers,number_of_videos,number_of_views,category,audience,start_date,email,current_location

えいえい！

> echo ${str,,} | sd "\s" "_" | sd "no." "number" > output.csv

> rg --invert-match "^Channel Name" worlds_top_10_youtubers_in_2022.csv >> output.csv

絶対直接書き換えた方が良い😅

rg はgrepの進化版みたいなやつです。

emailとかいらないので消してしまおう！

ここではcsvをいじれるxsvというコマンドを使います。

xsvはここ https://github.com/BurntSushi/xsv

> brew install xsv

普通にinstallできます。

xsv select

> xsv select \
  channel_name,subscribers,category,audience,start_date,current_location \
  output.csv
channel_name,subscribers,category,audience,start_date,current_location
Vlad and Niki,87M,Children Adventure,Children,23-Apr-2018,United States
Zee Music Company,88.2M,Music,Adults,12-Mar-2014,India
WWE,91.1M,Wrestling?,Adults and Old age,11-May-2007,United States
Like Nastya,101M,Kids Adventures,Children,6-Dec-2016,United States
Kids Diana Show,101M,Children Adventure,Children,12-May-2015,United States
MrBeast,105M,Adventure and Charity,Adults,20-Feb-2012,United States
PewDiePie,111M,Games and adventure,Adults,29-Apr-2010,Japan
Set India,143M,"Film, Drama and others",All ages,21-Sep-2006,India
Cocomelon,143M,Kids Cartoons,Kids,21-Sep-2006,United States
T-Series,225M,Music,Adults,13-Mar-2006,India

redirect

xsv select \
  channel_name,subscribers,category,audience,start_date,current_location \
  output.csv > youtubers.csv

これでデータの準備ができました。

roapi –help

helpはこんな感じです

> roapi --help
roapi 0.8.0
QP Hou
Create full-fledged APIs for static datasets without writing a single line of code.

USAGE:
    roapi [OPTIONS]

OPTIONS:
    -a, --addr-http <IP:PORT>
            HTTP endpoint bind address

    -c, --config <config>
            config file path

    -d, --disable-read-only
            Start roapi in read write mode

    -h, --help
            Print help information

    -p, --addr-postgres <IP:PORT>
            Postgres endpoint bind address

    -t, --table <[table_name=]uri[,option_key=option_value]>
            Table sources to load. Table option can be provided as optional setting as part of the
            table URI, for example: `blogs=s3://bucket/key,format=delta`. Set table uri to `stdin`
            if you want to consume table data from stdin as part of a UNIX pipe. If no table_name is
            provided, a table name will be derived from the filename in URI.

    -V, --version
            Print version information

roapi config

https://github.com/roapi/roapi#config-file ここに説明があります。
YAMLで記述するらしい

> nvim roapi.yaml

addr:
  http: 0.0.0.0:8084
  postgres: 0.0.0.0:5433

tables:
  - name: "youtubers"
    uri: "youtubers.csv"
    option:
      format: "csv

youtubers.csvをyoutubersという名前でアクセスするよ
データのフォーマットはcsvだよ
って感じかな

できることをaddr.httpとaddr.postgresがわかりやすく教えてくれてる気がする😁

roapi –config roapi.yaml

起動します。

> roapi --config roapi.yaml
[2022-10-05T10:51:16Z INFO  roapi::context] loading `uri(youtubers.csv)` as table `youtubers`
[2022-10-05T10:51:16Z INFO  roapi::context] registered `uri(youtubers.csv)` as table `youtubers`
[2022-10-05T10:51:16Z INFO  roapi::startup] 🚀 Listening on 0.0.0.0:5433 for Postgres traffic...
[2022-10-05T10:51:16Z INFO  roapi::startup] 🚀 Listening on 0.0.0.0:8084 for HTTP traffic...

psql

psqlでアクセスしてみる

> psql --host 127.0.0.1 --port 5433
psql (14.5 (Homebrew), server 13)
Type "help" for help.

yusukeh=>

え〜〜〜〜〜〜！
ほんとに繋がった🤣

えい！

select * from youtubers;
   channel_name    | subscribers |        category        |      audience      | start_date  | current_location
-------------------+-------------+------------------------+--------------------+-------------+------------------
 Vlad and Niki     | 87M         | Children Adventure     | Children           | 23-Apr-2018 | United States
 Zee Music Company | 88.2M       | Music                  | Adults             | 12-Mar-2014 | India
 WWE               | 91.1M       | Wrestling?             | Adults and Old age | 11-May-2007 | United States
 Like Nastya       | 101M        | Kids Adventures        | Children           | 6-Dec-2016  | United States
 Kids Diana Show   | 101M        | Children Adventure     | Children           | 12-May-2015 | United States
 MrBeast           | 105M        | Adventure and Charity  | Adults             | 20-Feb-2012 | United States
 PewDiePie         | 111M        | Games and adventure    | Adults             | 29-Apr-2010 | Japan
 Set India         | 143M        | Film, Drama and others | All ages           | 21-Sep-2006 | India
 Cocomelon         | 143M        | Kids Cartoons          | Kids               | 21-Sep-2006 | United States
 T-Series          | 225M        | Music                  | Adults             | 13-Mar-2006 | India
(10 rows)

ひ〜〜〜〜データ取れた〜〜！

えい！

select channel_name,subscribers from youtubers;
   channel_name    | subscribers
-------------------+-------------
 Vlad and Niki     | 87M
 Zee Music Company | 88.2M
 WWE               | 91.1M
 Like Nastya       | 101M
 Kids Diana Show   | 101M
 MrBeast           | 105M
 PewDiePie         | 111M
 Set India         | 143M
 Cocomelon         | 143M
 T-Series          | 225M
(10 rows)

普通にSQLでアクセスできるし！！！！
このYouTuberだれも知らない！
オレがオッサンだからか？！

curl

curlでもアクセスできます。

❯ curl -s -X POST -d "select channel_name,subscribers from youtubers" 127.0.0.1:8084/api/sql
[
    {
        "channel_name": "Vlad and Niki",
        "subscribers": "87M"
    },
    {
        "channel_name": "Zee Music Company",
        "subscribers": "88.2M"
    },
    {
        "channel_name": "WWE",
        "subscribers": "91.1M"
    },
    {
        "channel_name": "Like Nastya",
        "subscribers": "101M"
    },
    {
        "channel_name": "Kids Diana Show",
        "subscribers": "101M"
    },
    {
        "channel_name": "MrBeast",
        "subscribers": "105M"
    },
    {
        "channel_name": "PewDiePie",
        "subscribers": "111M"
    },
    {
        "channel_name": "Set India",
        "subscribers": "143M"
    },
    {
        "channel_name": "Cocomelon",
        "subscribers": "143M"
    },
    {
        "channel_name": "T-Series",
        "subscribers": "225M"
    }
]

columnq

roapiにはcolumnq-cliというのが含まれていて、roapiのSQL interfaceだけをコマンドで呼び出してデータを取得できます。

https://github.com/roapi/roapi/tree/main/columnq-cli

install columnq-cli

pip install columnq-cli

columnq –help

❯ columnq --help
Columnq 0.3.0
QP Hou
OLAP the Unix way.

USAGE:
    columnq <SUBCOMMAND>

OPTIONS:
    -h, --help       Print help information
    -V, --version    Print version information

SUBCOMMANDS:
    console    Query tables through an interactive console
    help       Print this message or the help of the given subcommand(s)
    sql        Query tables with SQL

columnq sql

❯ columnq sql --table youtubers=youtubers.csv "select channel_name,subscribers from youtubers;"
+-------------------+-------------+
| channel_name      | subscribers |
+-------------------+-------------+
| Vlad and Niki     | 87M         |
| Zee Music Company | 88.2M       |
| WWE               | 91.1M       |
| Like Nastya       | 101M        |
| Kids Diana Show   | 101M        |
| MrBeast           | 105M        |
| PewDiePie         | 111M        |
| Set India         | 143M        |
| Cocomelon         | 143M        |
| T-Series          | 225M        |
+-------------------+-------------+

columnq sqlをcolumnq consoleにすれば、インタラクティブにSQL打てるプロンプトに入れる！
自分はこれで十分かな

roapiのYAML書いて起動したのはこのblogのため😄

感想

久しぶりに楽しかった！

社内のデータでは普通にgroup byもorder byもいろいろできました。

今回社内で目的ありきで触ったけど、残念ながらjoinする必要がなかったので本当にjoinできるのかわからない。

join beween tables

https://github.com/roapi/roapi/blob/021332de5d75deaff7641b47e60ac224332ca287/README.md#features

って書いてあるんだよなぁ

Google Spreadsheetのデータとmysqlのデータをjoinできちゃうのかな・・・
postgresとmysqlとかs3とかもjoinできちゃうのかな？

grasysのエンジニアの誰かやって〜（データを準備するのがめんどくさい😅）


採用情報
 お問い合わせ    

yusukeh

たまに気まぐれで書きます。 about.me

＼ 合わせて読みたい ／Related article