--- title: "roapiでcsvにsqlでselectしてみる | grasys blog" url: "https://blog.grasys.io/post/yusukeh/roapi/" description: "こんにちはgrasys長谷川です。 最近このYouTube きょう何たべよ / Marco Macri を見ていて、パスタ作りにハマってます。 肉たたき、チーズ削り、細いトング、小さい調理用ピンセット、耐熱ガラスボウル、アルミのフライパン、お玉、パスタ茹で鍋、すり鉢と調理器具を一通り揃えて、、、パスタ作りをしているん…" --- # roapiでcsvにsqlでselectしてみる - ![](/_astro/noicon.CTHOhNiB_1HMs6A.webp)[yusukeh](/authors/yusukeh/) - 公開日：2022年10月6日 - カテゴリー：[Tech](/categories/tech/) - タグ：[#roapi](/tags/roapi/)[#CSV](/tags/csv/)[#MySQL](/tags/mysql/)[#PostgreSQL](/tags/postgresql/)[#SQLite](/tags/sqlite/) ![roapiでcsvにsqlでselectしてみる](/_astro/ogp.Ba8Ouiww_Z1pMtbz.webp) こんにちはgrasys長谷川です。 最近このYouTube [きょう何たべよ / Marco Macri](https://www.youtube.com/channel/UCQnsCgHXnXR4y8fecFCJQcw) を見ていて、パスタ作りにハマってます。 肉たたき、チーズ削り、細いトング、小さい調理用ピンセット、耐熱ガラスボウル、アルミのフライパン、お玉、パスタ茹で鍋、すり鉢と調理器具を一通り揃えて、、、パスタ作りをしているんですが！！ 近所のスーパーでイタリアンパセリとイタリアンバジルが手に入るときと手に入らない時でバラツキがある。。。 とうとう水耕栽培をはじめました🤣 6日くらいでイタリアンバジルが一番最初に🌱しました。 さて本題のblogネタですが、 ちょっと諸事情で社内のデータを調査するのにcsvが使いにくいなと調べていてroapiを見つけて、めちゃくちゃ個人的に好きになったのでここでご紹介します！ ## roapi [https://github.com/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](https://github.com/apache/arrow) and [Datafusion](https://github.com/apache/arrow-datafusion). The core of its design can be boiled down to the following: > > [https://github.com/roapi/roapi](https://github.com/roapi/roapi) ざっくり説明するといろんなデータ・フォーマットに対応していてアクセスできるデータを束ねて、 HTTP REST APIないしPostgres protocolでデータにアクセスして、 apache arrowとJSONとかに出力できる優れものです。 bigqueryに取り込んでおりゃーみたいなのあるけど、 - データサイズが大きくない - jsonとかcsv こうなら手元で**ノーコスト**でぱっとやっちゃった方が良い やることあんまり変わらないし！ ## Install 自分のはmacbook proのM2なんですがbrewで入ります。 Plain textcontent\_copy ``` brew install roapi ``` [Quick start](https://github.com/roapi/roapi#quick-start)あるのでそこみると普通に触れます。 Quick startの例をやっても仕方ないので、社内のcsvは見せられないので[kaggle](https://www.kaggle.com/)でデータをお借りして簡単なサンプルをやろう！ blog書こうって思ったものの、出せるデータって意外と探すの大変😅 ## kaggleから適当なデータをDownload [kaggle](https://www.kaggle.com/)は登録しないとデータ取れないので、適当に登録してください。 登録は割愛します。 別になんのデータでも良いので、これに決めた！ [World’s Top 10 YouTubers in 2022](https://www.kaggle.com/datasets/moazzimalibhatti/worlds-top-10-youtubers-in-2022) 2022年のYouTuber Top 10みたいです。 808 B（ちっさい！）です。 ## Downloadしたデータをunzip Downloadしたデータをunzip Plain textcontent\_copy ``` > unzip archive.zip Archive: archive.zip inflating: Worlds Top 10 YouTubers in 2022.csv ``` なぜファイル名にスペースを使う・・・ いやいかん、データ使わせてくれてありがとうございます！ ちょっと中身をみてみよう Plain textcontent\_copy ``` > 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.](https://github.com/sharkdp/bat) メールアドレスがあるから一応マスキング😎しときました。 ふんふん（オレ誰も知らない・・・） ## ファイルネームのスペースを修正 えい！ Plain textcontent\_copy ``` > exa Worlds\ Top\ 10\ YouTubers\ in\ 2022.csv Worlds Top 10 YouTubers in 2022.csv ``` えい！ Plain textcontent\_copy ``` > fn=$(exa Worlds\ Top\ 10\ YouTubers\ in\ 2022.csv) > echo $fn Worlds Top 10 YouTubers in 2022.csv ``` えい！ Plain textcontent\_copy ``` > echo ${fn,,} worlds top 10 youtubers in 2022.csv > echo ${fn,,} | sd " " "_" worlds_top_10_youtubers_in_2022.csv ``` えいえい！ Plain textcontent\_copy ``` > 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_.](https://github.com/ogham/exa) sdはsedみたいなの [sd is an intuitive find & replace CLI.](https://github.com/chmln/sd) ## headerがスペースあったりドットあったりで修正 えい！ Plain textcontent\_copy ``` > 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 ``` えい！ Plain textcontent\_copy ``` > echo ${str,,} | sd "\s" "_" | sd "no." "number" channel_name,subscribers,number_of_videos,number_of_views,category,audience,start_date,email,current_location ``` えいえい！ Plain textcontent\_copy ``` > echo ${str,,} | sd "\s" "_" | sd "no." "number" > output.csv > rg --invert-match "^Channel Name" worlds_top_10_youtubers_in_2022.csv >> output.csv ``` 絶対直接書き換えた方が良い😅 [rg](https://github.com/BurntSushi/ripgrep) はgrepの進化版みたいなやつです。 ## emailとかいらないので消してしまおう！ ここではcsvをいじれるxsvというコマンドを使います。 xsvはここ [https://github.com/BurntSushi/xsv](https://github.com/BurntSushi/xsv) Plain textcontent\_copy ``` > brew install xsv ``` 普通にinstallできます。 ### xsv select Plain textcontent\_copy ``` > 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 Plain textcontent\_copy ``` xsv select \ channel_name,subscribers,category,audience,start_date,current_location \ output.csv > youtubers.csv ``` これでデータの準備ができました。 ## roapi –help helpはこんな感じです Plain textcontent\_copy ``` > 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 HTTP endpoint bind address -c, --config config file path -d, --disable-read-only Start roapi in read write mode -h, --help Print help information -p, --addr-postgres 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](https://github.com/roapi/roapi#config-file) ここに説明があります。 YAMLで記述するらしい Plain textcontent\_copy ``` > 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 起動します。 Plain textcontent\_copy ``` > 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でアクセスしてみる Plain textcontent\_copy ``` > psql --host 127.0.0.1 --port 5433 psql (14.5 (Homebrew), server 13) Type "help" for help. yusukeh=> ``` え〜〜〜〜〜〜！ ほんとに繋がった🤣 えい！ Plain textcontent\_copy ``` 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) ``` ひ〜〜〜〜データ取れた〜〜！ えい！ Plain textcontent\_copy ``` 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でもアクセスできます。 Plain textcontent\_copy ``` ❯ 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](https://github.com/roapi/roapi/tree/main/columnq-cli) ### install columnq-cli Plain textcontent\_copy ``` pip install columnq-cli ``` ### columnq –help Plain textcontent\_copy ``` ❯ columnq --help Columnq 0.3.0 QP Hou OLAP the Unix way. USAGE: columnq 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 Plain textcontent\_copy ``` ❯ 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](https://github.com/roapi/roapi/blob/021332de5d75deaff7641b47e60ac224332ca287/README.md#features) って書いてあるんだよなぁ Google Spreadsheetのデータとmysqlのデータをjoinできちゃうのかな・・・ postgresとmysqlとかs3とかもjoinできちゃうのかな？ grasysのエンジニアの誰かやって〜（データを準備するのがめんどくさい😅） ## この記事を書いた人 [![](/_astro/yusukeh.BvUd2yKE_1mQ6Fp.webp)](/authors/yusukeh/) ### [yusukeh](/authors/yusukeh/) Fly high. Think deep. Move fast. Go far.🦅 - [X](https://x.com/yusukeh "X") - [GitHub](https://github.com/yusukeh "GitHub") - [Medium](https://medium.com/@yusukeh "Medium") - [Facebook](https://www.facebook.com/yusuke.exzm "Facebook") - [Instagram](https://www.instagram.com/yusukehasegawa/ "Instagram") - [LinkedIn](https://www.linkedin.com/in/hasegawa-yusuke-091b4164/ "LinkedIn") [プロフィールと記事一覧](/authors/yusukeh/)