お知らせ
過去にQiitaに投稿した内容のアーカイブです。
Timestreamの検索をSDKを使ってやってみました。
クエリーをする最小のコードはこんな感じ。
import boto3
from botocore.config import Config
query = """
SELECT fleet, truck_id, fuel_capacity, model, load_capacity, make, measure_name, CREATE_TIME_SERIES(time, measure_value::double) as measure
FROM "IoT-sample"."IoT"
WHERE measure_value::double IS NOT NULL
AND measure_name = 'speed'
GROUP BY fleet, truck_id, fuel_capacity, model, load_capacity, make, measure_name
ORDER BY fleet, truck_id, fuel_capacity, model, load_capacity, make, measure_name
LIMIT 2
"""
config = Config(region_name = 'us-east-1')
config.endpoint_discovery_enabled = True
client = boto3.client('timestream-query', config=config)
result = client.query(QueryString=query)
resultはこんな感じ
result['Rows'][0]['Data'][0]に検索結果が入っています。
そのままではグラフにしづらいので、変形しました。timeがISO 8601形式のUTC時刻で扱いづらいので、datetimeに変換しています。
def convert_callback(x):
dt = datetime.fromisoformat(x[:26])
dt = dt.replace(tzinfo= timezone.utc)
return dt
rows = result['Rows']
for row in rows:
measure = row['Data'][7]['TimeSeriesValue']
time = list(map(lambda x: (convert_callback(x['Time'])), measure))
value = list(map(lambda x: (float(x['Value']['ScalarValue'])), measure))
あとはこいつをグラフにします。
register_matplotlib_converters()
fig = plt.figure()
ax = fig.add_subplot(1, 1, 1)
ax.plot(time, value)
daysFmt = mdates.DateFormatter('%m-%d %H:%M')
ax.xaxis.set_major_formatter(daysFmt)
fig.autofmt_xdate()
plt.show()
めでたしめでたし。あまり派手なグラフではありませんが。。
コードの全体はこちら。
import json
from datetime import datetime, timezone
import boto3
import matplotlib.dates as mdates
import matplotlib.pyplot as plt
import pandas as pd
from botocore.config import Config
from pandas.plotting import register_matplotlib_converters
query = """
SELECT fleet, truck_id, fuel_capacity, model, load_capacity, make, measure_name, CREATE_TIME_SERIES(time, measure_value::double) as measure
FROM "IoT-sample"."IoT"
WHERE measure_value::double IS NOT NULL
AND measure_name = 'speed'
GROUP BY fleet, truck_id, fuel_capacity, model, load_capacity, make, measure_name
ORDER BY fleet, truck_id, fuel_capacity, model, load_capacity, make, measure_name
LIMIT 2
"""
def create_client():
config = Config(region_name = 'us-east-1')
config.endpoint_discovery_enabled = True
client = boto3.client('timestream-query', config=config)
return client
def convert_callback(x):
dt = datetime.fromisoformat(x[:26])
dt = dt.replace(tzinfo= timezone.utc)
return dt
if __name__ == "__main__":
client = create_client()
result = client.query(QueryString=query)
rows = result['Rows']
register_matplotlib_converters()
fig = plt.figure()
ax = fig.add_subplot(1, 1, 1)
for row in rows:
measure = row['Data'][7]['TimeSeriesValue']
time = list(map(lambda x: (convert_callback(x['Time'])), measure))
value = list(map(lambda x: (float(x['Value']['ScalarValue'])), measure))
ax.plot(time, value)
daysFmt = mdates.DateFormatter('%m-%d %H:%M')
ax.xaxis.set_major_formatter(daysFmt)
fig.autofmt_xdate()
plt.show()
