postgresで履歴テーブルから１時間毎の集計をSQLで取得する

投稿日時: 2020年7月31日投稿者: bokusui

　metabaseでpostgresデータを可視化するようにした所、履歴データが格納されているテーブルから１時間毎の集計を取りたいって事で、SQL1発で出来るんかいなと試行錯誤。

　今回の履歴テーブルはtimestamp型で開始時間と終了時間がセットされていて、誰が何をいつからいつまで実施していたかが記録されている、ような形になっています。これを１時間単位でその時間帯にどのくらい実施していたかをサマリたいという内容です。

具体的にはこんな感じでデータがあります。

selet * from xx_history ;

id, start_time, end_time, user_id, task_id 10, 2020-07-27 16:15:23, 2020-07-27 18:17:43, 432, 2 11, 2020-07-27 18:43:54, 2020-07-27 19:02:04, 154, 4 12, 2020-07-27 19:14:52, 2020-07-27 19:45:21, 432, 5 13, 2020-07-27 19:02:15, 2020-07-27 19:42:53, 154, 4

　これを下記のように１時間毎に時間を合計するような形で抽出したいという事になります。

時間範囲（XX時台）, 合計時間 2020-07-27 16:00, 00:44:37 2020-07-27 17:00, 01:00:00 2020-07-27 18:00, 00:33:49 2020-07-27 19:00, 01:13:11

　履歴テーブルの開始時間と終了時間に対して、その時間帯が該当するのは、下記４つのパターンがあり、それぞれ時間の取り方が変わってくるのも面倒なトコロです。

・開始時間と終了時間がその時間範囲内
　→　開始時間から終了時間までの時間
・開始時間が時間範囲より過去だが、終了時間が時間範囲内
　→　その時間範囲の開始時刻から終了時間までの時間
・開始時間が時間範囲内だが、終了時間は時間範囲より未来
　→　開始時間からその時間範囲の終了時刻までの時間
・開始時間は時間範囲より過去で、終了時間が時間範囲より未来
　→　1時間固定

　調べているとコチラを発見、postgresにはgenerate_seriesという便利なものがあるようです。

　generate_seriesで１時間毎のレコードを一定期間分生成出来るようなので、履歴テーブルとJoinしてSQLを作ります。

select time,
sum(case when xx_history.start_time >= time and xx_history.end_time <= time + interval '1 hours' then xx_history.end_time - xx_history.start_time
     when xx_history.start_time < time and xx_history.end_time >= time and xx_history.end_time <= time + interval '1 hours' then xx_history.end_time - time
     when xx_history.start_time >= time and xx_history.start_time <= time + interval '1 hours' and xx_history.end_time > time + interval '1 hours' then time + interval '1 hours' - xx_history.start_time
     when xx_history.start_time < time and xx_history.end_time > time + interval '1 hours' then time + interval '1 hours' - time
	 ELSE time - time END)
from generate_series( date_trunc('day', current_timestamp) - interval '2 days' , date_trunc('day', current_timestamp), '1 hours') as time
left join xx_history 
   on (xx_history.start_time >= time and xx_history.end_time <= time + interval '1 hours')
      or (xx_history.start_time < time and xx_history.end_time >= time and xx_history.end_time <= time + interval '1 hours')
      or (xx_history.start_time >= time and xx_history.start_time <= time + interval '1 hours' and xx_history.end_time > time + interval '1 hours')
      or (xx_history.start_time < time and xx_history.end_time > time + interval '1 hours')
group by time
order by time

select time,

sum(case when xx_history.start_time >= time and xx_history.end_time <= time + interval '1 hours' then xx_history.end_time - xx_history.start_time

when xx_history.start_time < time and xx_history.end_time >= time and xx_history.end_time <= time + interval '1 hours' then xx_history.end_time - time

when xx_history.start_time >= time and xx_history.start_time <= time + interval '1 hours' and xx_history.end_time > time + interval '1 hours' then time + interval '1 hours' - xx_history.start_time

when xx_history.start_time < time and xx_history.end_time > time + interval '1 hours' then time + interval '1 hours' - time

ELSE time - time END)

from generate_series( date_trunc('day', current_timestamp) - interval '2 days' , date_trunc('day', current_timestamp), '1 hours') as time

left join xx_history

on (xx_history.start_time >= time and xx_history.end_time <= time + interval '1 hours')

or (xx_history.start_time < time and xx_history.end_time >= time and xx_history.end_time <= time + interval '1 hours')

or (xx_history.start_time >= time and xx_history.start_time <= time + interval '1 hours' and xx_history.end_time > time + interval '1 hours')

or (xx_history.start_time < time and xx_history.end_time > time + interval '1 hours')

group by time

order by time

　whereとCaseの条件は一緒なので、Whereの方はもっとコストをかけない条件に見直しした方がよいですね。
　実際はもっと複雑な形になるのですが、とりあえずSQL1発で出来そうな感じです。

「Twilio」で電話をかけてみた

投稿日時: 2020年7月17日投稿者: bokusui

自動で電話をかけたいな。
という話で、Twilioをトライアルしてみました。
要件としては架電して相手に日本語で要件を連絡、通話できたかを判定して・・・というところです。

Twilioにアカウントを作成し、ダッシュボードで電話番号を取得します。
DOCを参照しながら、とりあえず試すだけなので、Javaで適当に書いていきます。
基本的なところは、下記を参考にさせて頂きました。

Qiita

通話やSMSが送れる無料API「Twilio」 - Qiita

https://qiita.com/mosin_nozomi/items/d30811022f1a19e620e0

CSG夏休みAdventCalendar16日目の記事です。前回はポエミングだったので今回は技術系の記事です。今回はとあるハッカソンでAmazonEchoのスキル開発をした際に知った、通話APIが面白かったのでご紹介したいと思...

SMSの発信や電話をかけるところまでは特に問題ありませんでしたが、上記サイトの内容とは現時点で変わっているのか、なかなか日本語を喋ってくれません。
あと、トライアル着信の「アカウントをアップグレードしてね」文句の後に何か押さなくてはいけないというのも暫くして気が付きました。。

どうしたら日本語を喋ってくれるのかと、ドキュメントを見ながらaliceとかを試してみたりしましたが、何かゴニョゴニョ言ってるけど何を言っているのか不明。。
結果的にダッシュボード左メニューのサービス→Programmable Voice→TwiML→テキスト音声変換のText-to-Speechという所に日本語はMizukiだと書いてあったので、やってみるとやっと喋ってくれました。ありがとうMizukiさん。

あと、TwiMLをインターネット上のURLからPOSTで取得できるようにしておく、という謎の制約もかなり厄介だったのですが、こちらも結果的に下記のようにすると必須では無かったようです。

import com.twilio.Twilio;
import com.twilio.rest.api.v2010.account.Call;
import com.twilio.type.PhoneNumber;
import com.twilio.type.Twiml;

public class Example {
    public static final String ACCOUNT_SID = "ACXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX";
    public static final String AUTH_TOKEN = "your_auth_token";

    public static void main(String[] args) throws Exception {
        Twilio.init(ACCOUNT_SID, AUTH_TOKEN);
        String callText = "お疲れ様です。了解頂けましたら、了解の旨を応答頂けますでしょうか？";
        String TwiMLstr = "<?xml version=\"1.0\" encoding=\"UTF-8\"?><Response><Say language=\"ja-JP\" voice=\"Mizuki\">"
                + callText + "</Say><Pause length=\"10\"/><Say language=\"ja-JP\" voice=\"Mizuki\">それでは宜しくお願いします。</Say></Response>";
        Twiml TwiML = new Twiml(TwiMLstr);

        Call call = Call.creator(new PhoneNumber("+81登録した電話番号"), new PhoneNumber("+取得した電話番号"), TwiML).create();

        String id = call.getSid();
        for(int i = 0 ; i < 10 ; i++){
            Thread.sleep(1000*5);
            call = Call.fetcher(id).fetch();
            if(call.getStatus() == Call.Status.RINGING ||
                call.getStatus() == Call.Status.IN_PROGRESS ||
                call.getStatus() == Call.Status.QUEUED){
                continue;
            }
            else if(call.getStatus() == Call.Status.COMPLETED){
                //着信OK
                System.out.println("Call Completed " + call.getAnsweredBy());
            }
            else{
                break;
            }
        }
        System.out.println(call.toString());
    }
}

import com.twilio.Twilio;

import com.twilio.rest.api.v2010.account.Call;

import com.twilio.type.PhoneNumber;

import com.twilio.type.Twiml;

public class Example {

public static final String ACCOUNT_SID = "ACXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX";

public static final String AUTH_TOKEN = "your_auth_token";

public static void main(String[] args) throws Exception {

Twilio.init(ACCOUNT_SID, AUTH_TOKEN);

String callText = "お疲れ様です。了解頂けましたら、了解の旨を応答頂けますでしょうか？";

String TwiMLstr = "<?xml version=\"1.0\" encoding=\"UTF-8\"?><Response><Say language=\"ja-JP\" voice=\"Mizuki\">"

+ callText + "</Say><Pause length=\"10\"/><Say language=\"ja-JP\" voice=\"Mizuki\">それでは宜しくお願いします。</Say></Response>";

Twiml TwiML = new Twiml(TwiMLstr);

Call call = Call.creator(new PhoneNumber("+81登録した電話番号"), new PhoneNumber("+取得した電話番号"), TwiML).create();

String id = call.getSid();

for(int i = 0 ; i < 10 ; i++){

Thread.sleep(1000*5);

call = Call.fetcher(id).fetch();

if(call.getStatus() == Call.Status.RINGING ||

call.getStatus() == Call.Status.IN_PROGRESS ||

call.getStatus() == Call.Status.QUEUED){

continue;

}

else if(call.getStatus() == Call.Status.COMPLETED){

//着信OK

System.out.println("Call Completed " + call.getAnsweredBy());

}

else{

break;

}

System.out.println(call.toString());

}

とりあえず要件は満たせそうです。
通話判定はもっとケースが必要ですし、判定待ちをもっとましな形にしたいところですが、トライアルなのでここまで。

ランニングコストがOKになって実際に導入すると決まったら、もうちょっと調べないとですね。

Windows環境からLinux環境にしたらJavaプロセスが動かない

投稿日時: 2020年6月18日投稿者: bokusui

　Windows上で動作させていた既存システムの開発環境を、今回Dockerで動かす形に変えていました。
既存システムの構成上、javaプロセスを２つ立ち上げる必要があり、これまでwindows上でバッチファイルで実行していたのを適当にシェルに置き換えて実行してみましたが、下記のようなメッセージが出てプロセスが動作しません。

See http://www.oracle.com/technetwork/java/javase/documentation/index.html for more details. /var/lib/apl/tools/lib/app-XXX-server.jar: line 1: $'PK¥003¥004': command not found /var/lib/apl/tools/lib/app-XXX-server.jar: line 2: $'¥373^¥226P': command not found /var/lib/apl/tools/lib/app-XXX--server.jar: line 3: ??e?y: command not found /var/lib/apl/tools/lib/app-XXX--server.jar: line 4: syntax error near unexpected token )' /var/lib/apl/tools/lib/app-XXX--server.jar: line 4: ?z?z?M)?^)? {,??MLy?x?P]?p?PK'
　なんじゃこりゃ？

JAVA実行コマンドとしては、
java -cp resource;lib/* jp.co.esoro.app-XXX.main
のような感じです。

　暫く試行錯誤していましたが、結果的にクラスパスを複数指定するところのセパレータ「；」はWindowsだけで、Linux環境では「：」じゃないとだめというオチでした。。

ラズパイ４で取ったセンサーデータをAthenaからMetabaseで可視化してみた

投稿日時: 2020年5月5日投稿者: bokusui

　前回の続きです。
　AWS IOTで、ラズパイで取得したセンサーのデータを定期的にPublishするようにしていましたが、実際のベランダに各センサーを配置してみました。
　いろいろ考えた結果、ベランダに電源を引いてラズパイを置くのでは無く、ラズパイは室内に配置、長いリボンコードをエアコンホースの口からベランダへ引くという形になりました。
　リボンコードの加工が面倒でしたが、これならエアコンホースのふたを加工したりしなくても一応大丈夫そうです。

　で、折角データを取得しているんですから、データを可視化してみたくなるものです。AWSにも色々BIサービスがあるようですが、趣味の範囲なので、コストを掛けず導入が容易そうなMetabaseでやってみます。

　MetabaseにAthena用のドライバーは標準では含まれていませんが、有難い事にこちらからJarを取得してプラグインのディレクトリに配置するだけで、簡単にAthenaにアクセス出来るようになりました。

　グラフになると入ってくる情報が違いますね。左軸は温度、湿度、右軸は照度、土壌水分です。温度・湿度センサーの値が取れずゼロになるのが回避できていないのが解ります。照度は明るいと数値が小さく、真っ暗で255になっています。また、湿度は夜高くなり、温度は朝の直射日光で40度になる時間帯があるようです。土壌水分は水やりの都度だいぶ数値的にはぶれるようで扱いが難しそうです。

　Metabaseは簡単に起動させられますが、メモリが１Gは無いと動かない模様、EC2等クラウドで動かすにはコストがネックになるので、結局、ラズパイ自身で動かす事になってしまいました。４Gメモリのラズパイ４なので早くは無いですが、十分動作します。

　こうなると、AWS IOT使っている意味無い気もしますが。。

ラズパイ４で各センサーを試してAWS Iotにpublishしてみた

投稿日時: 2020年4月19日投稿者: bokusui

　前回の続きで、今度はKEYESTUDIOスターターキットに含まれていた各センサーを試してみます。
　まず、温度湿度センサーですが、キット付属のセンサーはDHT11というモノ、サイトのコードはパット見pythonに変換するのが面倒なので、下記を参考にしました。

GitHub

szazo/DHT11_Python

https://github.com/szazo/DHT11_Python

Pure Python library for reading DHT11 sensor on Raspberry Pi - szazo/DHT11_Python

　簡単に温度と湿度の値が取れましたが、たまにゼロのケースがあるようなので、正しく計測するにはちょっと考慮が必要ですね。

　次は土壌水分センサーです。キットのサイトを参考に、I２Cを有効にし、Cのソースをそのままpythonに置き換えてすぐに値が取れるようになりました。

import wiringpi

Address = 0x48
BASE = 64
A0 = BASE + 0
A1 = BASE + 1
A2 = BASE + 2
A3 = BASE + 3

wiringpi.wiringPiSetup()
wiringpi.pcf8591Setup(BASE, Address)
while True:
    value = wiringpi.analogRead(A1)
    print("A１:" + str(value))
    wiringpi.delay(500)

import wiringpi

Address = 0x48

BASE = 64

A0 = BASE + 0

A1 = BASE + 1

A2 = BASE + 2

A3 = BASE + 3

wiringpi.wiringPiSetup()

wiringpi.pcf8591Setup(BASE, Address)

while True:

value = wiringpi.analogRead(A1)

print("A１:" + str(value))

wiringpi.delay(500)

　キット付属の照度センサーとかも指定するpinを変えるだけで取れました。ただ、照度センサーの場合はキット付属の３種の抵抗から１つを使用するのですが、３種の違いが見た目で解らず、配線が厄介です。
　
　使えそうなセンサーの値が取れるようになったので、これらを定期的にAWS Iotにpublishする事で最適な水やりタイミングを分析出来るようにしてみました。データはCSV形式でこんな感じです。

2020-04-19 00:05:11.97121800,19.3,49.0,134,254

　データは、日時、温度、湿度、土壌水位、照度をAWS Athenaのクエリーで取れるようにしておきます。

　Athenaでは、日時をtimestamp型にする場合、ミリ秒を8桁にしないとデータとして認識しないようなので、無理やりゼロを付けてます。ただ、AthenaではタイムゾーンをJSTとして扱うのが面倒な感じなので、無理にtimestamp型にしなくてもよかったかも？

なお、AWS IOTからS3バケットへの格納するキーは、

${clientid()}/${parse_time("yyyyMM", timestamp(), "Asia/Tokyo")}/${parse_time("dd", timestamp(), "Asia/Tokyo")}/${timestamp()}.csv

のように年月と日付をディレクトリにして、あとでログ管理をしやすくしておきます。ま、この程度ならS3もAthenaも当面コストほとんどゼロでいけるでしょう。

　これで各センサーの値によって水やりを制御する形が大体出来てきました。が、最後に肝心な水やり制御方法をどうするかについては、まだ検討中です。。

Springboot Securityのログイン画面がいい感じになっていた

投稿日時: 2020年2月28日投稿者: bokusui

　Springbootで作ったアプリを、そろそろユーザーに渡そうかというタイミングになりましたが、Springbootのバージョンが結構更新されているので、今更ながら最新に入れ替えてみました。
　それまでフレームワークは、spring-boot-starter-parentの2.0.1.RELEASEを指定していましたが、今見ると2.1系の次に2.2系が既に出ています。
　コードに影響があれば、そこまで最新にしなくてもいいかととりあえず2.2.4を試したところ、特に問題が無かったのでチェンジ。
　今回のアプリはsecurityのデフォルトログイン画面を使っていたのですが、更新したところ画面が変わりました。どうやら2.1から既に変わっていたようです。

2.0系はこれ

2.1系overはこれ

2.0系の見ためがしょぼいのでカスタマイズしようか考えましたが、中央寄せのいい感じのUIに変わってました。これならそのままでも違和感無いですね。

アプリの動作確認は終了、最後に、バージョンアップでこんな警告が出るようになったみたいなので、
2020-02-27 14:09:12 WARN JpaBaseConfiguration$JpaWebConfiguration spring.jpa.open-in-view is enabled by default. Therefore, database queries may be performed during view rendering. Explicitly configure spring.jpa.open-in-view to disable this warning

出ないようにapplication.ymlに追加

spring:
   jpa:
     open-in-view: false

spring:

jpa:

open-in-view: false

受け渡し準備OKです。
でもjarのファイルサイズはどんどん肥大化してます。。

Mavenインストールが失敗するようになった

投稿日時: 2020年1月30日投稿者: bokusui

SpiringBootで作成中のツールにちょっと機能が漏れていたので、pom.xmlにライブラリを追加したところ、ダウンロード出来ませんとの事。

[INFO] Scanning for projects...
[INFO] 
[INFO] Using the builder org.apache.maven.lifecycle.internal.builder.singlethreaded.SingleThreadedBuilder with a thread count of 1
[INFO]                                                                         
[INFO] ------------------------------------------------------------------------
[INFO] Building api-tool 0.0.1-SNAPSHOT
[INFO] ------------------------------------------------------------------------
[INFO] Downloading: http://repo.maven.apache.org/maven2/org/springframework/boot/spring-boot-starter-security/2.0.1.RELEASE/spring-boot-starter-security-2.0.1.RELEASE.pom
[INFO] ------------------------------------------------------------------------
[INFO] BUILD FAILURE
[INFO] ------------------------------------------------------------------------
[INFO] Total time: 3.814 s
[INFO] Finished at: 2020-01-29T10:56:41+09:00
[INFO] Final Memory: 15M/159M
[INFO] ------------------------------------------------------------------------
[ERROR] Failed to execute goal on project api-tool: Could not resolve dependencies for project api-tool:api-tool:jar:0.0.1-SNAPSHOT: Failed to collect dependencies at org.springframework.boot:spring-boot-starter-security:jar:2.0.1.RELEASE: Failed to read artifact descriptor for org.springframework.boot:spring-boot-starter-security:jar:2.0.1.RELEASE: Could not transfer artifact org.springframework.boot:spring-boot-starter-security:pom:2.0.1.RELEASE from/to central (http://repo.maven.apache.org/maven2): Failed to transfer http://repo.maven.apache.org/maven2/org/springframework/boot/spring-boot-starter-security/2.0.1.RELEASE/spring-boot-starter-security-2.0.1.RELEASE.pom. Error code 501, HTTPS Required -> [Help 1]
[ERROR] 
[ERROR] To see the full stack trace of the errors, re-run Maven with the -e switch.
[ERROR] Re-run Maven using the -X switch to enable full debug logging.
[ERROR] 
[ERROR] For more information about the errors and possible solutions, please read the following articles:
[ERROR] [Help 1] http://cwiki.apache.org/confluence/display/MAVEN/DependencyResolutionException

[INFO] Scanning for projects...

[INFO]

[INFO] Using the builder org.apache.maven.lifecycle.internal.builder.singlethreaded.SingleThreadedBuilder with a thread count of 1

[INFO]