RubyのURIクラスを使ってハッシュをクエリ文字列にする、URLからクエリ文字列をハッシュ化する

あんまり直球な情報がないのでメモがてら

環境

$ ruby -v
ruby 2.6.3p62 (2019-04-16 revision 67580) [universal.x86_64-darwin19]

やり方

URLからハッシュ化

target_url_with_query = "https://example.com?equal_string=ZXhhbXBsZXNleG1hcGxlcw%3D%3D&example=huga"
query_hash = URI.decode_www_form(URI.parse(target_url_with_query).query).to_h

ハッシュからクエリストリング生成

url = "https://example.com"
query_hash = {"equal_string"=>"ZXhhbXBsZXNleG1hcGxlcw==", "example"=>"huga"}
target_url_with_hash = "#{url}?#{URI.encode_www_form(query_hash)}"

余談

decode_www_form はデコード時にURLエスケープがかかっていると自動的に戻してくれる。あまりないユースケースだがそのままパラメータ文字列をkeyとvalueに分割したい場合は以下のように愚直にバラすしかない

target_url_with_query = "https://example.com?equal_string=ZXhhbXBsZXNleG1hcGxlcw%3D%3D&example=huga&hiragana=44Gy44KJ44GM44Gq5paH5a2X5YiXCC4%3D"
query_hash = target_url_with_query.split("/").last.split("?").last.split("&").each_with_object({}) do |key_value_set,result|result[key_value_set.split("=")[0]] = key_value_set.slice(key_value_set.split("=")[0].size+1..-1) end
# => {"equal_string"=>"ZXhhbXBsZXNleG1hcGxlcw%3D%3D", "example"=>"huga", "hiragana"=>"44Gy44KJ44GM44Gq5paH5a2X5YiXCC4%3D"}

関連リンク

• URI.encode_www_form (Ruby 3.0.0 リファレンスマニュアル)
• URI#escape と URI#unescape は Ruby 3.0.0 から削除された - 緑の扉

BigQueryでJOIN時に各々のテーブルに_TABLE_SUFFIXを適用する方法

イマイチすぐに出てこないのでメモがてら

前提条件

• STANDARD SQLです（記載タイミングでレガシーのほうを使っているほうが稀かなと思いますが念の為）

そもそも _TABLE_SUFFIX とは

こちらに関しては別記事で書いたのでそちらを参考のこと

書き方

カラム名に関してテーブル名を指定するのと同じ書き方でできる。以下は別々のテーブルに対してTABLE_SUFFIXを当てている書き方

SELECT hoge FROM `example-project.example-dataset.example_table_2010*` AS table_a
LEFT JOIN `example-project.example-dataset.join_table_2010*` AS table_b
WHERE table_a._TABLE_SUFFIX = "0801"
AND table_b._TABLE_SUFFIX = "0801"

例

GoogleAnalyticsのBigQueryExportサンプルをJOINして表現しています（いい例がなかったので同じテーブルをJOINしています…）

SELECT original_table.visitStartTime , original_table.fullVisitorID 
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_2016*` AS original_table
LEFT JOIN `bigquery-public-data.google_analytics_sample.ga_sessions_2016*` AS join_table ON original_table.fullVisitorID = join_table.fullVisitorID
WHERE original_table.fullVisitorId = "0001363886612345162"
AND original_table._TABLE_SUFFIX = "0803" 
AND join_table._TABLE_SUFFIX BETWEEN "0801" AND "0831"

参考リンク

• sql - _TABLE_SUFFIX on multiple joins - Stack Overflow

BigQueryで_TABLE_SUFFIXを適用して複数のテーブルを取る

別のことを書きたかったが前提の話題としてまずは切り出して書く。

前提条件

• STANDARD SQLです（記載タイミングでレガシーのほうを使っているほうが稀かなと思いますが念の為）

使い所とやり方

BigQueryには同じテーブルでもテーブル名の末尾に日付をつけて保存をするという手法（シャーディング）がある。

誰でも見れるサンプルデータで例えると、 bigquery-public-dataプロジェクトの google_analytics_sample　データセットに ga_sessions_ というテーブルがあるが、これは日付ごとに保存されていて同じカラム構成だが ga_sessions_20160801 , ga_sessions_20160802 , ga_sessions_20160803 ... のように連々と日付別のテーブルが作られている。

ログ系のテーブルでこの手法が使われるのだが、例えば「2016年の8月にfullVisitorIdが0001363886612345162だったログのdateがほしい」といったときに

SELECT date FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20160801`
WHERE fullVisitorId = "0001363886612345162"

のようなクエリ ga_sessions_20160801 の部分を変えて8月の日付分実行する必要が出てくるのでかなり大変。

そこで利用するのが _TABLE_SUFFIX で、 * を使って複数のテーブルにまたいでクエリを実行できる。以下例

SELECT date FROM `bigquery-public-data.google_analytics_sample.ga_sessions_2016*`
WHERE fullVisitorId = "0001363886612345162"
AND _TABLE_SUFFIX BETWEEN "0801" AND "0831"

上記の例のようにテーブル名で可変に指定したい部分に * を指定して、そこに当てはめたい文字をWHERE句を使って設定する。上記の場合は BETWEEN を使って指定している。もちろん _TABLE_SUFFIX = "0803" のような書き方も可能。

注意点

BigQueryではこのような日付別テーブルでデータを分ける方法の他にMySQLなどにもあるパーティションテーブルの方法も用意されており、パフォーマンス観点ではパーティションテーブルのほうが上なので、使い所には気をつける。

参考URL

• ワイルドカード テーブルを使用した複数テーブルに対するクエリ  |  BigQuery  |  Google Cloud

なぜhttpがだめなのかをざっくりまとめる

わりと当たり前の話なんだけど、当たり前すぎて感覚に近いレベルで認識しているので書き留める

インターネットはバケツリレー

大前提、クライアントとサーバがデータのやりとりをするインターネットの機構は各データがクライアントから各種ルータなどを経由して、サーバまで到達する。このデータがバケツリレーで行われるため、何もしないとデータそのものは中継地点の第三者には見えるようになっている。

https通信では何を行っているか

httpsのsはSecureを表しており、このバケツリレーを安全な通信をできるようのための仕組み。技術的にはSSLが採用されている。

大まかに表現すると実データの通信前に以下の工程が行われる

1. クライアント(ブラウザ)とサーバ間で暗号化の情報をやりとりする
2. サーバからクライアント証明書（公開鍵A）を受け取る
3. クライアントは共通鍵Bを作り、鍵Bを鍵Aで暗号化してサーバへ送る
4. サーバは公開鍵Aと対になる秘密鍵Cで暗号を解いて鍵Bを取得する
5. 以後の通信は鍵Bで暗号化しお互い暗号化/復号化して通信を行う

このようにクライアント側とサーバ側の2者でしかわかり得ない鍵を使って通信する内容自体を暗号化する。

httpだとどうなり、何が駄目なのか

httpsはクライアント側で暗号化したデータは中継地点のルータなどではデータを復号化できないため、それ自体は意味を知ることができない。だがhttpの場合はデータがそのまま見えるのでそのデータを見ればクライアントとサーバの間でどのようなデータがやりとりしているのかが丸裸になる。

そのため、httpの通信は第三者の中継地点を挟むので、本当に公開されているデータ以外が流通すると、盗み見される危険性があることが最大の問題となる。

参考リンク

• SSLって何？意味や仕組みをわかりやすく解説！ | さくらのSSL
• 【図解】https(SSL/TLS)の仕組みとシーケンス,パケット構造 〜暗号化の範囲, Encrypted Alert, ヘッダやレイヤについて～ | SEの道標
• いまさら聞けない、SSLサーバ証明書とルート証明書の関係

BigQueryで実行時の内容をYYYYMMDDで取得する方法

割とかんたんな部類なんだけど、毎度ググってしまうのでメモ的なもので。

前提条件

• STANDARD SQLです（記載タイミングでレガシーのほうを使っているほうが稀かなと思いますが念の為）

やり方

FORMAT_TIMESTAMP("%Y%m%d" ,CURRENT_TIMESTAMP())

動作原理

CURRENT_TIMESTAMP() を使うと実行時時点の TIMESTAMP 型の情報が取得できるのでそれを FORMAT_TIME_STAMP で整形しているだけ。

なお、括弧書きを抜いた `CURRENT_TIMESTAMP でも動きはするのだが、current_timestampという名前のカラム名がある場合は意図した挙動にならない恐れがあるため明示的に書いている。

応用

定時処理などの場合は前日の情報が欲しくなる場合があるので、その場合は減産してあげればいい。

FORMAT_TIMESTAMP("%Y%m%d" ,DATE_SUB(CURRENT_TIMESTAMP(),INTERVAL 1 DAY))

参考リンク

• 標準 SQL のタイムスタンプ関数  |  BigQuery  |  Google Cloud

RubyのCSVクラスでheaders:trueにするとき、空白文字列の扱いに気をつける

要因がわかれば自明だけどわかりづらい、自分の引いたバッドノウハウのメモ。

環境

ruby -v
ruby 3.0.1p64 (2021-04-05 revision 0fb782ee38) [x86_64-darwin19]

どういうことか

headerの文字列一致は空白も含めて行われる、そしてheaderの文字列が存在しないときにエラーなどは発生しないので気をつける。

例

下記のようなcsvがあるとする

$ cat test.csv
id,name ,age
1,taro,20
2,ziro,19
3,saburo,17

このcsvと同じディレクトリで以下のコードを sample1.rb として作る

require "csv"
CSV.foreach("test.csv",headers: true){ |row| p row["age"] }

結果としては以下

$ ruby sample1.rb
"20"
"19"
"17"

そして以下のコードを sample2.rb として作る

require "csv"
CSV.foreach("test.csv",headers: true){ |row| p row["name"] }

結果は以下のようになる

ruby sample2.rb
nil
nil
nil

このように完全に一致するヘッダがない場合はnilを返却する。正しくは以下のようなコード

require "csv"
CSV.foreach("test.csv",headers: true){ |row| p row["name "] }

ruby sample3.rb
"taro"
"ziro"
"saburo"

今回csvの列の2列目に含まれているため自明だが、末尾の列のheaderに空白が入っていると目視判断はつきづらい。例えば下記は age ではなく age だがエディタの助けがない限り判断はつかない

$ cat test.csv
id,name,age 
1,taro,20
2,ziro,19
3,saburo,17

まとめ

• headerの行に関しては CSV.parse_line の挙動に従いパースされるので、添字に文字列を使う場合は空白文字列も含めて認識される
• csv（さらにはtsv）の場合、末尾の空白は目視がしにくいので気をつける

関連リンク

• class CSV (Ruby 3.0.0 リファレンスマニュアル)

URIでパーセントエンコーディングが推奨されない文字を整理する

原理を知っていれば納得な話なんですが、仕様に基づいた整理。

出典

RFC 3986 - Uniform Resource Identifier (URI): Generic Syntax

どういうことか

RFC3986では予約文字(Reserved Character)と非予約文字(Unreserved Character)という表現で利用できる文字が定義されている。

非予約文字は「利用可能な文字」を表すもので、対象としては以下の文字が定義されている。

unreserved  = ALPHA / DIGIT / "-" / "." / "_" / "~"

• ALPHA（アルファベットA-Z,a-z）
• DIGIT（数字0-9）
• -（ハイフン）
• .（コロン）
• _（アンダーバー）
• ~（波線）

これらの文字はそのまま利用可能な文字なので、逆に考えるとこの文字はパーセントエンコーディングするべきではない文字ということになる。

ただ、エンコーディングを完全にしてはいけないというわけではなく、ちゃんと意味が通るようにエンコーディングされていればよい。

たとえば A0あ0という文字列をURLエンコーディングさせたい場合は、通常は

A0%E3%81%820

となるが、0を %30 とエンコードして

A0%E3%81%82%30

としても問題はない。

関連リンク

• URLエンコードについておさらいしてみた - Qiita
• 情報セキュリティ技術動向調査（2009 年下期）：IPA 独立行政法人 情報処理推進機構