なんだかんだいっても、元データがCSVであることはまあそこそこあると思います。
データベースに取り込まれてしまえばあとは煮るなり焼くなり好きにすれば良いと思うのですが、そこまでになんやかんやで問題が色々おこりがちなのもCSV。
そんなときに私がよく使うシェルスクリプトをまとめてみました。
基本的に自分がよく触る環境がGCPのため、それを前提に書いていますのであしからず。
元データ(CSV)が壊れていないか確認する
1. ヘッダに記載されているカラムの数をカウントする
なんやかんやで元ファイルがCSVでカラム数とか確認したい場面は多いと思います。
grep -o でマッチした文字だけをprintするのがミソです。
head -1 ファイル名 | grep -o ',' | wc -l
2. 列数の違う行番号を抽出する
ヘッダに異常がなくてもデータが途中で壊れていることもまあまああると思います。
そんなときはざっくりとこんな感じで確認できます。
cat {対象のファイル} | awk -F ',' '{print NR, NF}' | grep -v {本来ほしい列数}$
ちなみにこの書き方だと、カラムの中にカンマが含まれるケースは対応できません。
そのときはgawkをインストールした上で以下のようにします。
cat {対象のファイル} | gawk -v FPAT='([^,]+)|(\"[^\"]+\")' '{print NR, NF}' | grep -v {本来ほしい列数}$
FPATで区切りの定義を個別に渡しています。見れば分かる通りダブルクォーテーション内にエスケープされたダブルクォーテーションがあるケースだと動きませんが、そういうケースは基本的に稀と思うので、だいたいこのあたりまでやれば十分だろう、と思ってます。
BigQuery関連
3. テーブルのスキーマを抽出する
例えば、これまでBigQueryに連携されていたcsvがあって、急に最新のデータで落ちたケースなどでとりあえず今の定義で入るか試してみたいと思ったときに同じような構成のテーブルを作りたいときなどにスキーマを抽出したいときはまああるかなと思います。
BigQueryのスキーマはこんな感じに取れるのですが、このままだと使えないので整形をする必要があります。
bq show --format=prettyjson {dataset.table}
例えば、簡易的な形式の column_name:column_type,... の形式で取り出したい場合はこんな感じでいけます
bq show --format=json {dataset.table} | \
jq -r '.schema.fields[] | [.name,.type] | @csv' | \
sed -e 's/"//g' -e 's/,/:/' -e 's/$/,/'
あと作り直すのはよしなにやりましょう。
4. スプレッドシートを外部テーブルとして使っているテーブルを複製したい
複製したいことじたいはあまり多くない気がしますが、例えばセルの範囲を指定している場合などで、あとから変更したいというケースもあると思います。
コピーができないので、シンプルに作り直せば良いのですが、色々と打ち込む項目が多くて面倒です。私はだいたい間違えます。(skip header忘れたり...)
なので、ここをサクッとやれないか考えるわけですが、こんな感じにやると比較的ラクです。
bq show --format=prettyjson ${table_name}| \
jq '{sourceFormat: .externalDataConfiguration.sourceFormat, sourceUris: .externalDataConfiguration.sourceUris, googleSheetsOptions: .externalDataConfiguration.googleSheetsOptions, schema: .schema}' \
> definition.json
bq mk --external_table_definition=definition.json {dataset.table}
5. 定義がずれているカラムが存在しないか確認したい
例えば customer_id が int 型なときに、それ以外の型で入っている部分がないかは以下のように確認できます。
bq ls --format=json {対象データセット} | \
jq -r '.[].tableReference.tableId | .tableReference.datasetId + "." + .tableReference.tableId' | \
xargs -L 1 bq show --format=prettyjson | \
jq -r '{tableId: .tableReference.tableId, schema: .schema.fields[]} | select(.schema.name == 'customer_id' ) | select(.schema.type != "INTEGER")'
運用関連
6. テーブルへのアクセス状況を確認する
例えば移行などで、旧テーブルから新テーブルへの移行状況を確認したいと思ったときに、アクセスをみるということはまあやると思っており、
そんなときは以下のようにします
例えば、sampleという名前のdatasetへのアクセスが見たいなら
gcloud logging read --freshness 1d --format json 'protoPayload.methodName="google.cloud.bigquery.v2.JobService.InsertJob"
protoPayload.resourceName=~"/datasets/sample/"'
で取れます。(table単位で見たければ /datasets/sample/tables/table_name$ とかにすると良いと思います)
jsonで出せればあとはjqで煮るなり焼くなり自由自在ですね！
特定のSAやアカウント経由のものを確認したい場合は
gcloud logging read --freshness 1d --format json 'protoPayload.methodName="google.cloud.bigquery.v2.JobService.InsertJob"
protoPayload.resourceName=~"/datasets/sample/"'
protoPayload.authenticationInfo.principalEmail="xxx@xxx.iam.gserviceaccount.com"
とかにすると良いと思います。
7. スプレッドシート経由のアクセスを検出する
特定システムからの接続を確認するという意味で、コネクテッドシートとして使っているケースもよくあるかと思います。
どのシートからアクセスが有るのかを確認したいときは以下の様にすると一覧で出せます。
gcloud logging read 'protoPayload.metadata.firstPartyAppMetadata.sheetsMetadata.docId != NULL_VALUE' --freshness 1d --format json | jq -r '.[].protoPayload.metadata.firstPartyAppMetadata.sheetsMetadata.docId'