GCP: Bigquery 関数でGROUP BYしようとするとエラーがでる --- bq

• Goal
  Bigqueryで、Timestamp のデータをdate(日付)にグループ化＆集計し、日付の新しいもの順にソートをしたい
• How
  例１）　グループ化して集計は、OK
  SELECT date(timestamp),count(1)
  FROM xxxxxxxxxxx
  GROUP BY date(timestamp)
  
  例２）　日付の新しいもの順に並べようとしたら。。。
  SELECT date(timestamp),count(1)
  FROM xxxxxxxxxxx
  GROUP BY date(timestamp)
  ORDER BY date(timestamp) DESC
  
  実行すると、エラー。。。SELECT list expression references column timestamp which is neither grouped nor aggregated at [1:13]
  
  例３）　別名(alias)付けると、エラーはでない。
  SELECT date(timestamp) as TARGET_DATE, count(1)
  FROM xxxxxxxxxxx
  GROUP BY TARGET_DATE
  ORDER BY TARGET_DATE DESC
• 編集後記
  しょうもない事で、時間を潰してしまったので、メモ。。。
  GROUP BYだけならOKで、ORDER BYを付けるとNG。
  これって結構常識なんでしょうか？しらんかった。

GCP: IoTデータ分析環境立ち上げ on GCP

Goal：ローカルPCをIoTGateway端末と見立てて、Bigqueryに、気温データをストリーミングインサートする。利用するサービスは、IoT Core --- Pub/Sub --- Dataflow --- Bigquery と連係する。
Gateway側には、データをPushする処理が必要だが、それ以外は、コーディング不要！

How
0.　準備：仮想IoTGateway として、気温/計測時間/デバイスIDを送るプログラムを作成
https://github.com/GoogleCloudPlatform/python-docs-samples/tree/master/iot/api-client/mqtt_example
をローカルPCにクローンして、Pythonの環境などをセットする。
cloudiot_mqtt_example.py　を加工。変更箇所は、以下の通り。


１.　Pub/Subのトピックを作成
(1)APIを有効にする
(2)トピックを作成する
トピックとは、メールボックスみたいなもの

2.　デバイスをIoT Coreに登録する
(1)APIを有効にする
(2)端末レジストリを作成する
端末レジストリとは、デバイスのグループを作成すること
トピックの選択が必要。１で作成したトピックを選択する。
そうすると、この端末レジストリ内のデバイスから送られてきたメッセージは、そのトピックに投げ込まれる。
(3)端末を作成する(デバイスを登録する)
公開鍵の登録は一旦せずにデバイスを作成する。
公開鍵の種類は、　RS256_X509　とする。

3.　認証鍵の作成と登録
(1)デバイス側で、Google root certificate を取得する
wget https://pki.goog/roots.pem
あるいは、
curl https://pki.goog/roots.pem > roots.pem
で取得する
(2)鍵のペアを作成する
openssl req -x509 -newkey rsa:2048 -keyout rsa_private.pem -nodes -out rsa_cert.pem -subj "/CN=unused"
(3)公開鍵を、IoT Core のデバイスに貼り付ける
(4)private key は、ローカルマシンに保存

4.　サブスクライバーのを登録する
デバイスが、パブリッシャーなので、そのトピックの購読者を登録する。
ここでは、Bigqueryにセンサーの値を書き出すので、エクスポート機能を使う。
登録すると、自動的にCloud DataflowのJobが立ち上がる。
(1)Bigqueryに、データを登録するためのテーブルを登録する

(2)トピックのエクスポート登録を行う

5.　テスト実行!!
デバイスを稼働させて、データがBigqueryに流れ込むのを確認する。

$ python iotdemo.py --cloud_region=us-central1 --project_id=iotdemo1 --algorithm=RS256 --private_key_file=rsa_private.pem --registry_id=demo-reg --device_id=iotdemo

Bigquery側は。。。。

成功！！
実に簡単に、分析環境の構築が可能。