筆者について
元経理屋です。
経理を17年ほどやった後、3年ほどデータアナリストを経験してきました。
業務でBigqueryやTableauなど使用してきたので、データ周りのスキルが少しパワーアップされました。
プログラミング経験については、2017年から機械学習にかぶれてpythonやRを約9年ほどやっています。
これなに
今回は、経理屋目線で手元に簡易なデータベースを作ってささっとデータをチェック・分析する仕組みについて記事にしてみました。
- 使用OS: windows 11
- 対象ユーザー: プログラミング経験は無いが、Excelの関数やマクロでガシガシデータ分析している人。最近大きなサイズのデータが増えてきて困っている人。かと言ってpythonはちょっとハードル高いなーと思っている人。
- 使用言語: SQL (初歩的なもののみ)
- 使用ツール: duckDB
ことの発端
最近の経理では、システムから自動的に吐き出されてくるデータが増え、中には100MBを超えるようなデータもちらほら出ていていると思います。そのようなデータはExcelでは開けないか、開けたとしても表示が非常に遅くなってしまいます。vlookup( )なんてしたら目も当てられません。ストレスですね。
かと言って、すべてのデータがきちんとした分析用のデータベースにきれいに整理整頓されている企業なんてほんの一握りです。
そこで、大きなデータも自分のPC内でそのままサクサク分析できるようにしてしまおうというコンセプトです。
どうやって
duckDBというツールを使って、SQLでデータを扱えるようにします。
SQLと言われると「うっ」となる人もいるかも知れませんが、難しいことをしようとしなければ非常に簡単なコードだけでデータを取り出せます。難しいことしようとすると難しいです。
今回の用途としては大きなデータの一部を切り出してExcel用のcsvとして保存するとか、簡単なデータの確認をするくらいです。
準備
duckDBのzipファイルをダウンロードしてきて、中からexeファイルを取り出してダブルクリックするだけ。
よく使うなら、スタートにピン留めとかしとくといいですね。
インストール不要なので、社内規定にひっかかりにくそうです。
参考にさせていただいたサイト: Windows 11にDuckDBのexeファイルをダウンロードしてテーブルを作成してみた
サンプルデータ
NYC Taxi Tripデータを使います。
１ヶ月分でcsv換算で300MBくらいあるので、ちょうどExcelで処理できないくらいのデータになります。これを3ヶ月分、csv換算で1GBくらいを準備します。
ちなみにデータはcsvではなくparquetという形式でダウンロードされてきます。parquetは圧縮率が高く、同じデータでもcsvよりかなりデータサイズが小さくなっていいです。話すと長くなるので割愛します。
これを、1つのcsvに変換して使います。1GB超の巨大なcsvになります。
変換方法は後述しています。
yellow_tripdata_2025-01.parquet
yellow_tripdata_2025-02.parquet
yellow_tripdata_2025-03.parquet
フォルダ構成
最初にフォルダだけ作っておきます。duckDBはExcelなどと違って実行する際のフォルダの場所を気にするので、下記のようにしておきます。
Documents\
└── duckdb_taxi_project\
├── data\
│ ├── 01_raw\ # ダウンロードした初期のYellow Taxi ファイル群
│ ├── 02_processed\ # 変換後のcsvや速度比較用のサンプルParquet
│ ├── 03_monthly_in\ # 【定期処理用】毎月送られてくる新しいCSVの配置場所
│ └── 04_archive\ # 【定期処理用】DB取り込みが完了したCSVの退避場所
├── db\ # DuckDBのデータベースファイル (.duckdb)
├── sql\ # 各種処理のSQLスクリプト (.sql)
└── scripts\ # 自動化用のWindowsバッチ(.bat) や PowerShell(.ps1)
└── duckdb.exe
サンプルデータの変換
早速やっていきましょう。
まずはダウンロードしてきたサンプルデータをcsvに変換します。
データの保存場所は下記のようにしておきます。
- Documents
- duckdb_taxi_project/
- data/
- 01_raw/
- yellow_tripdata_2025-01.parquet
- yellow_tripdata_2025-02.parquet
- yellow_tripdata_2025-03.parquet
- 01_raw/
- duckdb.exe
- data/
- duckdb_taxi_project/
次にduckdb.exeをダブルクリックして起動します。
そこに
COPY (SELECT * FROM 'data/01_raw/*.parquet')
TO 'data/02_processed/merged_sample.csv' (HEADER, DELIMITER ',');
と打つ（コピペの場合、複数行の貼り付けの警告が出ますが、強制的に貼り付けでOKです）と、02_processedフォルダに1GB超の巨大なcsvが作成されます。これで完了です。早いですね。
ちなみに'*.parquet'はワイルドカードでファイル名を指定しているので、これ1つでサンプルのparquetデータを3つすべて取り込んでいます。
簡易データベースの作成
ここで一度duckDBを閉じます。
次は、データベースを指定して開くためにバッチファイルを作成します。
duckdb_taxi_project/に、新規ファイルを作成します。ファイル名を'open_db.bat'にします。
それをメモ帳で開いて、下記のようにして保存します。
duckdb.exe db/mydb.duckdb
意味としては、duckDBを開く際にデータベース名を引数としてduckDBに与える感じです。
次に、open_db.batをダブルクリックしてduckDBを開きます。
開いたduckDBの画面で
CREATE TABLE yellow_taxi AS
SELECT * FROM 'data/02_processed/merged_sample.csv';
と打ちます。先程作ったcsvファイルを読み込んで、deckDBのデータベースファイル内にテーブルを作っています。Documents/duckdb_taxi_project/dbフォルダに'mydb.duckdb'という簡易なデータベースができているのではないかと思います。
元となるcsvデータは1GB以上あったのに、duckDBのデータベースにすると1/3以下に圧縮されていることがわかります。
このデータベースの中も確認してみましょう。
SHOW TABLES;
yello_taxiというテーブルができているのがわかります。テーブルの中身も確認してみましょう。先頭5行のデータを見てみます。
SELECT * FROM yellow_taxi LIMIT 5;
先頭5行のデータが見れました。
行数も数えてみましょう。
SELECT count(*) FROM yellow_taxi;
1,119万行あることがわかります。
Excelじゃ絶対に開きたくないですね。
データの一部を切り出し
経理屋の皆さんはExcel大好きな方が多いと思いますので、巨大なデータの一部をcsvとして切り出す方法を見ていきたいと思います。
COPY (
SELECT * FROM yellow_taxi
WHERE CAST(tpep_pickup_datetime AS DATE) = DATE '2025-01-01'
)
TO 'data/02_processed/sample_20250101.csv' (HEADER, DELIMITER ',');
です。
2025/1/1の1日分のデータだけがdata/02_processed/フォルダに出力されます。後はExcelでゴリゴリやっていくだけです。
日本語が文字化けしてしまう場合は、メモ帳で開いてからExcelへコピペしましょう。
もしくは空っぽのExcel開いてからデータ取り込み。
定期的に出力されてくるcsvデータを、データベースへアップロード
経理をやっていると、毎月同じ形式で新しいデータが出力されてきたりするかと思います。それを毎回SQLを打つのではなく、ポチポチで取り込めるようにします。
Documents\
└── duckdb_taxi_project\
├── data\
│ ├── 03_monthly_in\ # 【定期処理用】毎月送られてくる新しいCSVの配置場所
│ └── 04_archive\ # 【定期処理用】DB取り込みが完了したCSVの退避場所
├── db\ # DuckDBのデータベースファイル (.duckdb)
└── scripts\ # 自動化用のWindowsバッチ(.bat) や PowerShell(.ps1)
└── duckdb.exe
- バッチファイルの作成
Documents/duckdb_taxi_project/scripts/フォルダ内に新規ファイルを作成します。ファイル名は'run_monthly_ingest.bat'とします。
ファイルの中身は下記のようにします。
@echo off
chcp 65001 > nul
setlocal
:: プロジェクトルートに移動
cd /d "%~dp0\.."
set DB_FILE=db\taxi_data.duckdb
echo DuckDBへのデータ取り込みを実行します...
:: -c オプションを使って、ダブルクォーテーションの中に直接SQLを記述します
duckdb "%DB_FILE%" -c "INSERT INTO yellow_taxi SELECT * FROM read_csv_auto('data/03_monthly_in/*.csv');"
echo 処理が完了しました！
pause
-
取り込みたいファイルをDocuments/duckdb_taxi_project/data/03_monthly_in/フォルダの中に保存します。
-
上記で作ったrun_monthly_ingest.batをダブルクリックします。
データが取り込まれます。 -
取り込み終わったcsvデータは04_archiveに移動しておきましょう。そのまま03_monthly_in/フォルダに残っていると、次回の取り込みの際にもう一度取り込まれてしまいます。
以上です。
おまけ: pythonとの連携
duckDBのデータベースは、pythonから利用することも簡単です。
今回は説明は省きますが、将来性としてもいいです。
おわりに
経理業務は定型的な作業が多いので、なるべくプログラムで自動化してしまうと吉です。
でも、業務内容自体が複雑すぎることがこの自動化を難しくしていると感じています。なので、システム以前に業務内容の標準化や「こんなに複雑にしても利益上がらないダロ」という思考が必要だと感じています。長くなりそうですね。このへんでやめときましょう。
単純作業はなるべく自動化して、成長につながる仕事に時間を使いたいですね。
というか早く帰りたいですね。