表題通り。
We now generally consider Moment to be a legacy project in maintenance mode. It is not dead, but it is indeed done. - 現在では、Momentはメンテナンスモードに入ったレガシープロジェクトであると考えています。死んだわけではありませんが、確かに終わったのです。
上記のページに加えてに説明があり
などがあり、案内としてはかなり丁寧な説明となっている。
いろんなテーブルをまたいで利用するときに似たようなクエリをつくるのでその時のための機能
定数定義のような機構はこの記事を書いている時点では存在しないので値だけを返す一時的なユーザー定義関数を作る。
ユーザー定義関数は CREATE FUNCTION を利用する、今回利用する範囲での最低限の文法としては以下
# 関数定義 CREATE TEMPORARY FUNCTION {{一時定義の関数名}}() AS ( {{返却したい処理}} )
もっとオプションがあるが、そちらに関しては参考リンクのドキュメントをご覧いただければと思います。
実行時のYYYYMMDDの値をとってクエリに活用する。
以下の例は dateが実行日と一致するものかつ、last_update_date内に実行日のYYYYMMDDが含まれているものを探すクエリ
CREATE TEMPORARY FUNCTION QUERY_EXECUTE_NOW_DATE() AS( FORMAT_TIMESTAMP("%Y%m%d" ,CURRENT_TIMESTAMP) ); SELECT * FROM `example-project.example-dataset.example_table_20100801` WHERE data = QUERY_EXECUTE_NOW_DATE() AND last_update_date = QUERY_EXECUTE_NOW_DATE()
あんまり直球な情報がないのでメモがてら
$ ruby -v ruby 2.6.3p62 (2019-04-16 revision 67580) [universal.x86_64-darwin19]
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"}
イマイチすぐに出てこないのでメモがてら
_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"
別のことを書きたかったが前提の話題としてまずは切り出して書く。
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などにもあるパーティションテーブルの方法も用意されており、パフォーマンス観点ではパーティションテーブルのほうが上なので、使い所には気をつける。
わりと当たり前の話なんだけど、当たり前すぎて感覚に近いレベルで認識しているので書き留める
大前提、クライアントとサーバがデータのやりとりをするインターネットの機構は各データがクライアントから各種ルータなどを経由して、サーバまで到達する。このデータがバケツリレーで行われるため、何もしないとデータそのものは中継地点の第三者には見えるようになっている。
httpsのsはSecureを表しており、このバケツリレーを安全な通信をできるようのための仕組み。技術的にはSSLが採用されている。
大まかに表現すると実データの通信前に以下の工程が行われる
このようにクライアント側とサーバ側の2者でしかわかり得ない鍵を使って通信する内容自体を暗号化する。
httpsはクライアント側で暗号化したデータは中継地点のルータなどではデータを復号化できないため、それ自体は意味を知ることができない。だがhttpの場合はデータがそのまま見えるのでそのデータを見ればクライアントとサーバの間でどのようなデータがやりとりしているのかが丸裸になる。
そのため、httpの通信は第三者の中継地点を挟むので、本当に公開されているデータ以外が流通すると、盗み見される危険性があることが最大の問題となる。
割とかんたんな部類なんだけど、毎度ググってしまうのでメモ的なもので。
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))
