エンジニアのはしがき

プログラミングの日々の知見を書き連ねているブログです

ExcelでCSVの集計頑張るのが辛いならSQLiteでSQL書くのもアリ

SQLiteを今まで使ったことが無かったのですが、先日ExcelSQL的なことをしようとして思うように書けず辛かったのでSQLiteへデータを流し込んでSQLのクエリで集計しました。

SQLitePostgreSQL, MySQL等と比較すると気軽にデータを格納・集計できるので、普段ExcelよりSQLを書く機会が多い方は使ってみるのもアリだと思います。

以下でCUIからSQLiteを使う際の基本的な使い方をまとめてみました。

SQLiteとは

RDBMSの1つで、以下のような特徴を持ちます。

  • 1つのデータベースを1ファイルで取り扱う
  • サーバを立てる必要がない
  • メモリ消費量が少ない

SQLiteの準備

今回はWindows(Windows 11 Home (22H2))での利用を想定します。macOSだと嬉しいことにデフォルトでインストールされているようです。

WindowsCUIからインストールする場合、パッケージマネージャーであるChocolateyが便利です。 使い方は以下の公式ページからどうぞ。

chocolatey.org

# ChocolateyでSQLiteのインストール
$ choco install sqlite

インストールされているかどうか、以下のコマンド等で確認しましょう。

$ sqlite3 --version
3.40.0 2022-11-16 12:10:08 89c459e766ea7e9165d0beeb124708b955a4950d0f4792f457465d71b158d318

Chocolateyでインストールする際「管理者として実行する」必要になりますので、コマンドプロンプトやGit Bashは予め「管理者として実行する」ようにしてください。(よくハマりました)

データを流し込む

まずは予めSQLiteに読み込むCSVを用意してください。 今回は例として以下のようなCSVを用意しました。

users.csv

id,name,address
1,yamashita,kyoto
2,tanaka,osaka
3,okamoto,hokkaido

用意ができたら以下のコマンドでSQLite側にCSVデータを読み込みます。

# DBを作成
$ winpty sqlite3 test.sqlite3

# sqlite3に接続する
$ winpty sqlite3

# 区切り文字をカンマに指定する
sqlite> .separator ,
# usersテーブルにCSVをインポートする(usersテーブルは未定義なのでこの時にCREATE TABLEされる)
sqlite> .import users.csv users

# usersテーブルを照会する
sqlite> SELECT * FROM users;
1,yamashita,kyoto
2,tanaka,osaka
3,okamoto,hokkaido

# テーブル定義を確認する
sqlite> .schema users

# sqliteを終了する
sqlite> .exit

# DBがファイル(*.sqlite3)として作成されている
$ ls
test.sqlite3  users.csv

SQLiteではDBが1つのファイルとして扱われます。お手軽ですね。 *.sqlite3が作成されたら、以後はwinpty sqlite3 *.sqlite3で既存のDBに接続して利用できます。

DBに接続してしまえば、あとはSQLでデータを集計できますので、GROUP BYなりWHEREなりを書いて良い塩梅になるようデータを加工しちゃえばOKです。

※Git Bashを使う場合、頭にwinptyを付けないと正しく動作しません。winptyについては下記の記事で少し書きました。 tm-progapp.hatenablog.com

SQLの実行結果を出力する

DBでデータを加工したらそれを出力したい!という場面もあるかもしれません。 その場合は以下のように.outputを使います。

# usersテーブルから条件指定したレコードだけをCSV出力する
sqlite> .separator ,
.headers on
.output output.csv
SELECT * FROM users WHERE name IN ('tanaka', 'okamoto');
# output.csvが出力されている
$ cat output.csv
id,name,address
2,tanaka,osaka
3,okamoto,hokkaido

.output {出力ファイル名}.csv {SQL文}でクエリの実行結果が出力されます。 .headersはヘッダ行が必要な時だけ記述すればOKです。

あとがき

SQLiteSQLで集計したいけどサーバを立てるほどのことでもないな…という時はおすすめです。 自分はエクセルの関数を駆使した集計が得意ではないので、SQLiteの方が書きやすいなーという感想です🤤

InnoDBのlocking read

tm-progapp.hatenablog.com

↑の記事にてInnoDBのロックについてまとめましたが、公式ドキュメントを読み漁る中で「locking read」と「non-locking read」という言い回しがあり、気になったのでまとめてみました。

MySQL :: MySQL 8.0 Reference Manual :: 15.7.2.4 Locking Reads

locking readとは

SELECT ... FOR SHARE, SELECT ... FOR UPDATE時のロックを伴う読み取り処理を指します。 locking readではconsistent read(一貫性読み取り)ではなく常にDBから最新の値を取得します。

non-locking readとは

locking readではない処理のことで通常のSELECTを指します。

InnoDBでは通常のSELECTはconsistent readを行います。consistent readではMVCC(※1)という仕組みによりスナップショットから値を参照しようとします。こちらでは他トランザクションによるレコード増減の影響を受けない為ファントムリード(※2)が起こりません。

  • ※1: MultiVersion Concurrency Controlの略。トランザクション単位でバージョニングする機構。InnoDBでは読み取り時にMVCCが保持するバージョンのスナップショットを参照することでconsistent readが実現されています。
  • ※2: ファントムリードとは、あるトランザクションT1で複数回読み取りをした際、同タイミングに別トランザクションT2でレコードの追加/削除を実施したことにより、T1の読み取り結果が変わってしまうような現象を指します。

メリット

  • 他のトランザクションからの更新処理をロックする為、Lost Updateを防ぐことができる。
  • 読み取り実行時点での正確な最新値が得られる。

デメリット

  • 常にDBから最新の値を取得するという特徴からファントムリードが発生し得る。
  • 処理内容によってはデッドロックが発生する。

参考

漢(オトコ)のコンピュータ道: InnoDBのREPEATABLE READにおけるLocking Readについての注意点

主要RDBMS製品の比較 – 同時実行制御, トランザクション分離レベル | コーソルDatabaseエンジニアのBlog

MySQL の Repeatable Read と RocksDB の楽観的トランザクション解説|技術ブログ|北海道札幌市・宮城県仙台市のVR・ゲーム・システム開発 インフィニットループ

InnoDBのロックについてまとめた

MySQLを実務で触っているのですが、改めてInnoDBのロックの仕様について頭の整理をするためにまとめてみました。

ロックの種類

共有ロックと排他のロックの2種類があり、トランザクション分離レベルによって挙動が変わります。 InnoDBのデフォルトは"REPEATABLE READ"で、SELECT @@transaction_ISOLATIONで確認が可能です。

トランザクション分離レベルについては以下を参照。

MySQL :: MySQL 8.0 Reference Manual :: 15.7.2.1 Transaction Isolation Levels

なお以下の説明は"REPEATABLE READ"での挙動になります。

共有ロック

  • トランザクションからの読み取り(SELECT)を許可し、更新(INSERT, UPDATE, DELETE)は禁止します。
    • SELECTする際にFOR SHARE(MySQL 8.0.0以前の記法だとLOCK IN SHARE MODE)を付与すると共有ロックをかけられます。

排他ロック

  • トランザクションからの読み取り(SELECT)、更新(INSERT, UPDATE, DELETE)の双方を禁止します。
    • INSERT, UPDATE, DELETEする際に排他ロックがかかります。
    • ただ必ずしもすべてのSELECTが禁止されるわけではなく、通常のSELECT(non-locking read / consistent read)では読み取りは許可されます。

ロックの範囲

共有ロック、排他ロックが適用される範囲にはいくつか種類があります。

各ロックの相関関係や扱われ方については、自分で書くよりこちらのブログの説明が視覚的にも分かりやすいので紹介いたします。

レコードロック

  • 特定のレコードに対してのロック。

ギャップロック

  • インデックスの隙間に対してのロック。

ネクスキーロック

  • レコードロックとギャップロックを併用したロック。

テーブルロック

  • 対象のテーブル全体へのロック。

ロックの範囲はどのように決まるのか

  • インデックスが貼られている、またはUNIQUE制約のあるカラムを対象にした場合は「レコードロック」or「ギャップロック」or「ネクスキーロック
  • 上記以外は「テーブルロック」

参考

MySQL :: MySQL 8.0 Reference Manual :: 15.7.1 InnoDB Locking

MySQL|占有ロック(FOR UPDATE)と共有ロック(LOCK IN SHARE MODE) - わくわくBank

MySQL - InnoDBのロック関連まとめ - Qiita

良く分かるMySQL Innodbのギャップロック - Qiita

ネクストキーロックとは | ソフトウェア雑記

主要RDBMS製品の比較 – 同時実行制御, トランザクション分離レベル | コーソルDatabaseエンジニアのBlog

npm linkでローカルのnpmパッケージを参照できた

最近知ったnpm linkというものについて書いてみようと思います。

やりたかったこと

とあるnpmパッケージの実装を変更した後、npm publish前にパッケージをimportしている別プロジェクト側から動作が確認したいというケースがありました。

具体的には、プロジェクト側でimport MyPackage from '@hogefuga/my-package'という形で参照しているという状態において、ローカルでMyPackageを変更した場合の動作確認をしたいという状況でした。

npm linkでローカルのnpmパッケージを参照させる

npm linkの仕様は下記公式に載っています。

docs.npmjs.com

以下の手順でコマンドを叩くとやりたいことが実現できました。

  • ローカルのnpmパッケージのルートパスでnpm linkを実行する。
  • パッケージを参照しているプロジェクトのpackage.jsonの存在するパスでnpm link {パッケージ名}を実行する。

ローカルの参照が不要になった場合

参照側のプロジェクトでnpm installし直すことで、publishされたパッケージを参照するよう戻せます。

無用な混乱を避ける為、動作確認を終えたら戻すように癖付けしておいた方が良いかもしません。

VSCodeでファイルの文字列比較ができることを知った

VSCode便利ですよね。

拡張機能を使いこなすと大体やりたいことができちゃう凄いエディタだという印象を持っていますが、実はデフォルトでも便利な機能が実装されています。

文字列比較した時の表示

例えば以下はVSCodeで異なるテキストファイル同士を比較した場合の表示です。

Gitのdiffのように変更点が強調表示されます。 長い文字列同士を比較して、差分を特定したい時に使えそうです。

比較方法

まず、VSCodeのコマンドパレットを開きます。コマンドパレットは、Windows/Linuxなら「Ctrl+Shift+P」、macOSなら「⌘+Shift+P」で展開できます。

コマンドパレットから「Compare Active File ...」を選択します。選択すると比較した結果が表示されます。なお、比較方法には3種類あります。

  • Compare Active File With...
    • ファイルを指定して現在開いているファイルの文字列と比較します。これを選択すると直後に比較対象ファイルを聞かれますので指定しましょう。
  • Compare Active File with Clipboard
    • クリップボードに記憶している文字列と現在開いているファイルの文字列を比較します。
  • Compare Active File with Saved
    • 保存されているファイルの文字列と未保存のファイルの文字列を比較します。同一ファイルの比較です。

Unityのエディタ拡張でシーンビュー上にシェルスクリプト実行ボタンを置く

Unityのエディタ拡張を使えば、シーンビューにボタンを配置することが出来ます。普段はWindowsで開発をしているので、Git Bashで実行するシェルスクリプトのボタンを置いてみました。

続きを読む

Cloud Watch Insightsのクエリをシェルスクリプトから実行する

Cloud Watch Insightsは、膨大なCloud Watchのログから必要なものをフィルタリングするのに便利です。 AWSコンソール上から利用できるようになっていますが、AWS CLIからも利用ができるとのことで実際に試してみました。

動作環境

# uname -a
Linux b93945d429ec 5.10.102.1-microsoft-standard-WSL2 #1 SMP Wed Mar 2 00:30:59 UTC 2022 x86_64 GNU/Linux

# cat /etc/os-release
PRETTY_NAME="Debian GNU/Linux bookworm/sid"
NAME="Debian GNU/Linux"
ID=debian
HOME_URL="https://www.debian.org/"
SUPPORT_URL="https://www.debian.org/support"
BUG_REPORT_URL="https://bugs.debian.org/"

Cloud Watchにログを用意する

Lambdaからログを吐かせる等して、Cloud Watchにログが存在する状態にしておきます。

AWS CLIのインストール・認証情報の用意

シェルスクリプトからAWS CLIを使いますので、予めAWS CLIをインストールし、アクセスキー・シークレットアクセスキーを用意しておいて下さい。

docs.aws.amazon.com

シェルスクリプトを書く

"/aws/lambda/hoge"というロググループに対してクエリを実行する場合は下記のようになります。

#!/bin/bash -eu

QUERY_ID=`aws logs start-query \
    --log-group-name "/aws/lambda/hoge" \
    --start-time 1648393200 \
    --end-time 1648476000 \
    --query-string \
        "fields @timestamp, @message
        | sort @timestamp desc
        | limit 10" \
    | jq ".queryId" \
    | sed "s/\"//g"`

while :
do
    STATUS=`aws logs get-query-results --query-id $QUERY_ID | jq ".status"`
    if [ $STATUS = '"Complete"' ]; then
        break
    fi
    sleep 1s
done

aws logs get-query-results --query-id $QUERY_ID
  • aws logs start-queryでクエリを実行しています。
    • --log-group-name: ロググループ名
    • --start-time: 対象期間の開始(UNIX time)
    • --end-time: 対象期間の終了(UNIX time)
    • --query-string: Cloud Watch Insightsのクエリ構文を指定
  • aws logs get-query-resultsでクエリの実行結果を取得できます。

ここで注意すべき点は、クエリ実行直後は結果取得ができないのでウェイトを挟む必要があることです。 (while文でstatusが"Complete"になるまでsleepさせているのはその為。)

参考情報

CloudWatch Logs Insights のクエリ構文 - Amazon CloudWatch Logs

start-query — AWS CLI 2.7.29 Command Reference

get-query-results — AWS CLI 2.7.29 Command Reference

Chromeの開発者ツールで捕捉した通信をcurlコマンドでコピーする

デバッグをしていて一部のHTTPリクエストのパラメータだけ変えてcurlしたいなーと思っていたところ、Chromeの開発者ツールで既に便利な機能が用意されていたことを知りました。

やり方

  • Chromeで開発者ツールを開き、「Network」タブを表示する。
  • コピーしたいリクエストの行で右クリックし、「Copy」→「Copy as cURL」を選択する。(Windowsだとbash用とcmd用の2種類から選べる模様)

  • あとは任意のターミナルにペーストして実行するだけ。

実際には下記のようなcurlコマンドがコピーされます。(以下はbash用)

curl 'https://************************************' \
  -H 'authority: ************************************' \
  -H 'accept: application/json, text/plain, */*' \
  -H 'accept-language: ja,de;q=0.9,en-US;q=0.8,en;q=0.7,ja-JP;q=0.6' \
  -H 'cache-control: no-cache' \
  -H 'origin: https://************************************' \
  -H 'pragma: no-cache' \
  -H 'referer: https://************************************/' \
  -H 'sec-ch-ua: "Chromium";v="104", " Not A;Brand";v="99", "Google Chrome";v="104"' \
  -H 'sec-ch-ua-mobile: ?0' \
  -H 'sec-ch-ua-platform: "Windows"' \
  -H 'sec-fetch-dest: empty' \
  -H 'sec-fetch-mode: cors' \
  -H 'sec-fetch-site: same-site' \
  -H 'user-agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/104.0.0.0 Safari/537.36' \
  --compressed

地味に改行してくれているのが見やすくて助かりますね!

React + TypeScript + CSS ModulesでChrome拡張機能の開発環境を構築する

前回、webpack + TypeScriptの構成でChrome拡張の開発環境を構築しました。

tm-progapp.hatenablog.com

ただ、DOMを拡張機能側で動的生成したりするよう場合、TSXで書きたいなァ…🤤という思いから構築してみました。

続きを読む

Chrome拡張の開発環境をWebpack+TypeScriptで構築する

このWebサイトのココをちょっと直したい…そんな思いからChrome拡張の開発を始めました。 ただやっぱりJavaScriptよりTypeScriptが書きたいと思い、環境を構築してみました。

続きを読む