{
"cells": [
{
"cell_type": "code",
"execution_count": 1,
"metadata": {},
"outputs": [],
"source": [
"%pip install awswrangler boto3"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "7c608617",
"metadata": {},
"outputs": [],
"source": [
"import awswrangler as wr"
]
},
{
"cell_type": "code",
"execution_count": 2,
"metadata": {
"scrolled": false
},
"outputs": [
{
"data": {
"text/html": [
"
\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" device_id | \n",
" sk | \n",
" timestamp | \n",
" model | \n",
" id | \n",
" e_type | \n",
" battery | \n",
" status | \n",
" evt | \n",
" year | \n",
" month | \n",
" day | \n",
" hour | \n",
"
\n",
" \n",
" \n",
" \n",
" 0 | \n",
" 00158d0006c9d5ed | \n",
" 1641522280000 | \n",
" 2022-01-07 11:24:40 | \n",
" a2_sen_cds_01 | \n",
" 6ae271ef | \n",
" reported | \n",
" 6 | \n",
" 1 | \n",
" 6 | \n",
" 2022 | \n",
" 1 | \n",
" 7 | \n",
" 11 | \n",
"
\n",
" \n",
" 1 | \n",
" 00158d0006c9d5ed | \n",
" 1641513569000 | \n",
" 2022-01-07 08:59:29 | \n",
" a2_sen_cds_01 | \n",
" ea3eb7e5 | \n",
" reported | \n",
" 6 | \n",
" 1 | \n",
" 6 | \n",
" 2022 | \n",
" 1 | \n",
" 7 | \n",
" 8 | \n",
"
\n",
" \n",
" 2 | \n",
" 00158d0006c9d5ed | \n",
" 1641510572000 | \n",
" 2022-01-07 08:09:32 | \n",
" a2_sen_cds_01 | \n",
" cee8ce43 | \n",
" reported | \n",
" 6 | \n",
" 1 | \n",
" 1 | \n",
" 2022 | \n",
" 1 | \n",
" 7 | \n",
" 8 | \n",
"
\n",
" \n",
" 3 | \n",
" 00158d0006c9d5ed | \n",
" 1641509374000 | \n",
" 2022-01-07 07:49:34 | \n",
" a2_sen_cds_01 | \n",
" 1e02ed5c | \n",
" reported | \n",
" 6 | \n",
" 1 | \n",
" 6 | \n",
" 2022 | \n",
" 1 | \n",
" 7 | \n",
" 7 | \n",
"
\n",
" \n",
" 4 | \n",
" 00158d0006c9d5ed | \n",
" 1641504591000 | \n",
" 2022-01-07 06:29:51 | \n",
" a2_sen_cds_01 | \n",
" 2c5ef025 | \n",
" reported | \n",
" 6 | \n",
" 1 | \n",
" 1 | \n",
" 2022 | \n",
" 1 | \n",
" 7 | \n",
" 6 | \n",
"
\n",
" \n",
" 5 | \n",
" 00158d0006c9d5ed | \n",
" 1641504291000 | \n",
" 2022-01-07 06:24:51 | \n",
" a2_sen_cds_01 | \n",
" b27ba489 | \n",
" reported | \n",
" 6 | \n",
" 1 | \n",
" 6 | \n",
" 2022 | \n",
" 1 | \n",
" 7 | \n",
" 6 | \n",
"
\n",
" \n",
" 6 | \n",
" 00158d0006c9d5ed | \n",
" 1641502796000 | \n",
" 2022-01-07 05:59:56 | \n",
" a2_sen_cds_01 | \n",
" 873fa701 | \n",
" reported | \n",
" 6 | \n",
" 1 | \n",
" 1 | \n",
" 2022 | \n",
" 1 | \n",
" 7 | \n",
" 5 | \n",
"
\n",
" \n",
" 7 | \n",
" 00158d0006c9d5ed | \n",
" 1641453725000 | \n",
" 2022-01-06 16:22:05 | \n",
" a2_sen_cds_01 | \n",
" 120ee414 | \n",
" reported | \n",
" 6 | \n",
" 1 | \n",
" 1 | \n",
" 2022 | \n",
" 1 | \n",
" 6 | \n",
" 16 | \n",
"
\n",
" \n",
" 8 | \n",
" 00158d0006c9d5ed | \n",
" 1641447725000 | \n",
" 2022-01-06 14:42:05 | \n",
" a2_sen_cds_01 | \n",
" a4805270 | \n",
" reported | \n",
" 6 | \n",
" 1 | \n",
" 6 | \n",
" 2022 | \n",
" 1 | \n",
" 6 | \n",
" 14 | \n",
"
\n",
" \n",
" 9 | \n",
" 00158d0006c9d5ed | \n",
" 1641439319000 | \n",
" 2022-01-06 12:21:59 | \n",
" a2_sen_cds_01 | \n",
" e3bb3199 | \n",
" reported | \n",
" 6 | \n",
" 1 | \n",
" 6 | \n",
" 2022 | \n",
" 1 | \n",
" 6 | \n",
" 12 | \n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" device_id sk timestamp model \\\n",
"0 00158d0006c9d5ed 1641522280000 2022-01-07 11:24:40 a2_sen_cds_01 \n",
"1 00158d0006c9d5ed 1641513569000 2022-01-07 08:59:29 a2_sen_cds_01 \n",
"2 00158d0006c9d5ed 1641510572000 2022-01-07 08:09:32 a2_sen_cds_01 \n",
"3 00158d0006c9d5ed 1641509374000 2022-01-07 07:49:34 a2_sen_cds_01 \n",
"4 00158d0006c9d5ed 1641504591000 2022-01-07 06:29:51 a2_sen_cds_01 \n",
"5 00158d0006c9d5ed 1641504291000 2022-01-07 06:24:51 a2_sen_cds_01 \n",
"6 00158d0006c9d5ed 1641502796000 2022-01-07 05:59:56 a2_sen_cds_01 \n",
"7 00158d0006c9d5ed 1641453725000 2022-01-06 16:22:05 a2_sen_cds_01 \n",
"8 00158d0006c9d5ed 1641447725000 2022-01-06 14:42:05 a2_sen_cds_01 \n",
"9 00158d0006c9d5ed 1641439319000 2022-01-06 12:21:59 a2_sen_cds_01 \n",
"\n",
" id e_type battery status evt year month day hour \n",
"0 6ae271ef reported 6 1 6 2022 1 7 11 \n",
"1 ea3eb7e5 reported 6 1 6 2022 1 7 8 \n",
"2 cee8ce43 reported 6 1 1 2022 1 7 8 \n",
"3 1e02ed5c reported 6 1 6 2022 1 7 7 \n",
"4 2c5ef025 reported 6 1 1 2022 1 7 6 \n",
"5 b27ba489 reported 6 1 6 2022 1 7 6 \n",
"6 873fa701 reported 6 1 1 2022 1 7 5 \n",
"7 120ee414 reported 6 1 1 2022 1 6 16 \n",
"8 a4805270 reported 6 1 6 2022 1 6 14 \n",
"9 e3bb3199 reported 6 1 6 2022 1 6 12 "
]
},
"execution_count": 2,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"df = wr.athena.read_sql_query(\n",
" sql=\"SELECT * FROM illumination where device_id = '00158d0006c9d5ed' order by timestamp desc limit 10 /*3회의실조도센서*/;\",\n",
" database=\"ambt_b2c\"\n",
")\n",
"df"
]
},
{
"cell_type": "code",
"execution_count": 23,
"metadata": {
"scrolled": true
},
"outputs": [
{
"data": {
"text/html": [
"\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" device_id | \n",
" sk | \n",
" timestamp | \n",
" model | \n",
" id | \n",
" e_type | \n",
" hum | \n",
" temp | \n",
" battery | \n",
" status | \n",
" year | \n",
" month | \n",
" day | \n",
" hour | \n",
"
\n",
" \n",
" \n",
" \n",
" 0 | \n",
" 00158d00028d93d8 | \n",
" 1638146954000 | \n",
" 2021-11-29 09:49:14 | \n",
" a2_sen_th_01 | \n",
" fdc93e9c | \n",
" reported | \n",
" 26 | \n",
" 249 | \n",
" 0 | \n",
" 1 | \n",
" 2021 | \n",
" 11 | \n",
" 29 | \n",
" 9 | \n",
"
\n",
" \n",
" 1 | \n",
" 00158d00028d93d8 | \n",
" 1638146954000 | \n",
" 2021-11-29 09:49:14 | \n",
" a2_sen_th_01 | \n",
" a6fa9718 | \n",
" reported | \n",
" 25 | \n",
" 249 | \n",
" 0 | \n",
" 1 | \n",
" 2021 | \n",
" 11 | \n",
" 29 | \n",
" 9 | \n",
"
\n",
" \n",
" 2 | \n",
" 00158d00028d93d8 | \n",
" 1638146653000 | \n",
" 2021-11-29 09:44:13 | \n",
" a2_sen_th_01 | \n",
" 19295a0d | \n",
" reported | \n",
" 26 | \n",
" 244 | \n",
" 0 | \n",
" 1 | \n",
" 2021 | \n",
" 11 | \n",
" 29 | \n",
" 9 | \n",
"
\n",
" \n",
" 3 | \n",
" 00158d00028d93d8 | \n",
" 1638146362000 | \n",
" 2021-11-29 09:39:22 | \n",
" a2_sen_th_01 | \n",
" 5fc92dc4 | \n",
" reported | \n",
" 27 | \n",
" 240 | \n",
" 0 | \n",
" 1 | \n",
" 2021 | \n",
" 11 | \n",
" 29 | \n",
" 9 | \n",
"
\n",
" \n",
" 4 | \n",
" 00158d00028d93d8 | \n",
" 1638146057000 | \n",
" 2021-11-29 09:34:17 | \n",
" a2_sen_th_01 | \n",
" 6393c2bc | \n",
" reported | \n",
" 27 | \n",
" 234 | \n",
" 0 | \n",
" 1 | \n",
" 2021 | \n",
" 11 | \n",
" 29 | \n",
" 9 | \n",
"
\n",
" \n",
" 5 | \n",
" 00158d00028d93d8 | \n",
" 1638146056000 | \n",
" 2021-11-29 09:34:16 | \n",
" a2_sen_th_01 | \n",
" 5f518e6c | \n",
" reported | \n",
" 28 | \n",
" 234 | \n",
" 0 | \n",
" 1 | \n",
" 2021 | \n",
" 11 | \n",
" 29 | \n",
" 9 | \n",
"
\n",
" \n",
" 6 | \n",
" 00158d00028d93d8 | \n",
" 1638145756000 | \n",
" 2021-11-29 09:29:16 | \n",
" a2_sen_th_01 | \n",
" 87312dd5 | \n",
" reported | \n",
" 28 | \n",
" 227 | \n",
" 0 | \n",
" 1 | \n",
" 2021 | \n",
" 11 | \n",
" 29 | \n",
" 9 | \n",
"
\n",
" \n",
" 7 | \n",
" 00158d00028d93d8 | \n",
" 1638145751000 | \n",
" 2021-11-29 09:29:11 | \n",
" a2_sen_th_01 | \n",
" 78ccec5f | \n",
" reported | \n",
" 29 | \n",
" 227 | \n",
" 0 | \n",
" 1 | \n",
" 2021 | \n",
" 11 | \n",
" 29 | \n",
" 9 | \n",
"
\n",
" \n",
" 8 | \n",
" 00158d00028d93d8 | \n",
" 1638145451000 | \n",
" 2021-11-29 09:24:11 | \n",
" a2_sen_th_01 | \n",
" 2967d0b3 | \n",
" reported | \n",
" 29 | \n",
" 219 | \n",
" 0 | \n",
" 1 | \n",
" 2021 | \n",
" 11 | \n",
" 29 | \n",
" 9 | \n",
"
\n",
" \n",
" 9 | \n",
" 00158d00028d93d8 | \n",
" 1638145150000 | \n",
" 2021-11-29 09:19:10 | \n",
" a2_sen_th_01 | \n",
" 7d5f4627 | \n",
" reported | \n",
" 29 | \n",
" 211 | \n",
" 0 | \n",
" 1 | \n",
" 2021 | \n",
" 11 | \n",
" 29 | \n",
" 9 | \n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" device_id sk timestamp model \\\n",
"0 00158d00028d93d8 1638146954000 2021-11-29 09:49:14 a2_sen_th_01 \n",
"1 00158d00028d93d8 1638146954000 2021-11-29 09:49:14 a2_sen_th_01 \n",
"2 00158d00028d93d8 1638146653000 2021-11-29 09:44:13 a2_sen_th_01 \n",
"3 00158d00028d93d8 1638146362000 2021-11-29 09:39:22 a2_sen_th_01 \n",
"4 00158d00028d93d8 1638146057000 2021-11-29 09:34:17 a2_sen_th_01 \n",
"5 00158d00028d93d8 1638146056000 2021-11-29 09:34:16 a2_sen_th_01 \n",
"6 00158d00028d93d8 1638145756000 2021-11-29 09:29:16 a2_sen_th_01 \n",
"7 00158d00028d93d8 1638145751000 2021-11-29 09:29:11 a2_sen_th_01 \n",
"8 00158d00028d93d8 1638145451000 2021-11-29 09:24:11 a2_sen_th_01 \n",
"9 00158d00028d93d8 1638145150000 2021-11-29 09:19:10 a2_sen_th_01 \n",
"\n",
" id e_type hum temp battery status year month day hour \n",
"0 fdc93e9c reported 26 249 0 1 2021 11 29 9 \n",
"1 a6fa9718 reported 25 249 0 1 2021 11 29 9 \n",
"2 19295a0d reported 26 244 0 1 2021 11 29 9 \n",
"3 5fc92dc4 reported 27 240 0 1 2021 11 29 9 \n",
"4 6393c2bc reported 27 234 0 1 2021 11 29 9 \n",
"5 5f518e6c reported 28 234 0 1 2021 11 29 9 \n",
"6 87312dd5 reported 28 227 0 1 2021 11 29 9 \n",
"7 78ccec5f reported 29 227 0 1 2021 11 29 9 \n",
"8 2967d0b3 reported 29 219 0 1 2021 11 29 9 \n",
"9 7d5f4627 reported 29 211 0 1 2021 11 29 9 "
]
},
"execution_count": 23,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"df = wr.athena.read_sql_query(\n",
" sql=\"SELECT * FROM temperature_humidity where device_id = '00158d00028d93d8' order by timestamp desc limit 10 /*3회의실온습도센서*/;\",\n",
" database=\"ambt_b2c\"\n",
")\n",
"df"
]
},
{
"cell_type": "code",
"execution_count": 31,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" device_id | \n",
" sk | \n",
" timestamp | \n",
" model | \n",
" id | \n",
" e_type | \n",
" battery | \n",
" status | \n",
" evt | \n",
" year | \n",
" month | \n",
" day | \n",
" hour | \n",
"
\n",
" \n",
" \n",
" \n",
" 0 | \n",
" 00158d0002d545b4 | \n",
" 1638250408000 | \n",
" 2021-11-30 14:33:28 | \n",
" a2_sen_mt_01 | \n",
" 72e2d749 | \n",
" reported | \n",
" 0 | \n",
" 1 | \n",
" 1 | \n",
" 2021 | \n",
" 11 | \n",
" 30 | \n",
" 14 | \n",
"
\n",
" \n",
" 1 | \n",
" 00158d0002d545b4 | \n",
" 1638248702000 | \n",
" 2021-11-30 14:05:02 | \n",
" a2_sen_mt_01 | \n",
" 7f6500ba | \n",
" reported | \n",
" 0 | \n",
" 1 | \n",
" 1 | \n",
" 2021 | \n",
" 11 | \n",
" 30 | \n",
" 14 | \n",
"
\n",
" \n",
" 2 | \n",
" 00158d0002d545b4 | \n",
" 1637913814000 | \n",
" 2021-11-26 17:03:34 | \n",
" a2_sen_mt_01 | \n",
" 7374a99b | \n",
" reported | \n",
" 0 | \n",
" 1 | \n",
" 1 | \n",
" 2021 | \n",
" 11 | \n",
" 26 | \n",
" 17 | \n",
"
\n",
" \n",
" 3 | \n",
" 00158d0002d545b4 | \n",
" 1637906976000 | \n",
" 2021-11-26 15:09:36 | \n",
" a2_sen_mt_01 | \n",
" fc6ef1ed | \n",
" reported | \n",
" 0 | \n",
" 1 | \n",
" 1 | \n",
" 2021 | \n",
" 11 | \n",
" 26 | \n",
" 15 | \n",
"
\n",
" \n",
" 4 | \n",
" 00158d0002d545b4 | \n",
" 1637905314000 | \n",
" 2021-11-26 14:41:54 | \n",
" a2_sen_mt_01 | \n",
" 3fff6d7f | \n",
" reported | \n",
" 0 | \n",
" 1 | \n",
" 1 | \n",
" 2021 | \n",
" 11 | \n",
" 26 | \n",
" 14 | \n",
"
\n",
" \n",
" 5 | \n",
" 00158d0002d545b4 | \n",
" 1637904052000 | \n",
" 2021-11-26 14:20:52 | \n",
" a2_sen_mt_01 | \n",
" df21c92d | \n",
" reported | \n",
" 0 | \n",
" 1 | \n",
" 1 | \n",
" 2021 | \n",
" 11 | \n",
" 26 | \n",
" 14 | \n",
"
\n",
" \n",
" 6 | \n",
" 00158d0002d545b4 | \n",
" 1637885207000 | \n",
" 2021-11-26 09:06:47 | \n",
" a2_sen_mt_01 | \n",
" 54b9e9b2 | \n",
" reported | \n",
" 0 | \n",
" 1 | \n",
" 1 | \n",
" 2021 | \n",
" 11 | \n",
" 26 | \n",
" 9 | \n",
"
\n",
" \n",
" 7 | \n",
" 00158d0002d545b4 | \n",
" 1637882804000 | \n",
" 2021-11-26 08:26:44 | \n",
" a2_sen_mt_01 | \n",
" f156880a | \n",
" reported | \n",
" 0 | \n",
" 1 | \n",
" 1 | \n",
" 2021 | \n",
" 11 | \n",
" 26 | \n",
" 8 | \n",
"
\n",
" \n",
" 8 | \n",
" 00158d0002d545b4 | \n",
" 1637882445000 | \n",
" 2021-11-26 08:20:45 | \n",
" a2_sen_mt_01 | \n",
" 622e4ab9 | \n",
" reported | \n",
" 0 | \n",
" 1 | \n",
" 1 | \n",
" 2021 | \n",
" 11 | \n",
" 26 | \n",
" 8 | \n",
"
\n",
" \n",
" 9 | \n",
" 00158d0002d545b4 | \n",
" 1637881650000 | \n",
" 2021-11-26 08:07:30 | \n",
" a2_sen_mt_01 | \n",
" b5c40bab | \n",
" reported | \n",
" 0 | \n",
" 1 | \n",
" 1 | \n",
" 2021 | \n",
" 11 | \n",
" 26 | \n",
" 8 | \n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" device_id sk timestamp model \\\n",
"0 00158d0002d545b4 1638250408000 2021-11-30 14:33:28 a2_sen_mt_01 \n",
"1 00158d0002d545b4 1638248702000 2021-11-30 14:05:02 a2_sen_mt_01 \n",
"2 00158d0002d545b4 1637913814000 2021-11-26 17:03:34 a2_sen_mt_01 \n",
"3 00158d0002d545b4 1637906976000 2021-11-26 15:09:36 a2_sen_mt_01 \n",
"4 00158d0002d545b4 1637905314000 2021-11-26 14:41:54 a2_sen_mt_01 \n",
"5 00158d0002d545b4 1637904052000 2021-11-26 14:20:52 a2_sen_mt_01 \n",
"6 00158d0002d545b4 1637885207000 2021-11-26 09:06:47 a2_sen_mt_01 \n",
"7 00158d0002d545b4 1637882804000 2021-11-26 08:26:44 a2_sen_mt_01 \n",
"8 00158d0002d545b4 1637882445000 2021-11-26 08:20:45 a2_sen_mt_01 \n",
"9 00158d0002d545b4 1637881650000 2021-11-26 08:07:30 a2_sen_mt_01 \n",
"\n",
" id e_type battery status evt year month day hour \n",
"0 72e2d749 reported 0 1 1 2021 11 30 14 \n",
"1 7f6500ba reported 0 1 1 2021 11 30 14 \n",
"2 7374a99b reported 0 1 1 2021 11 26 17 \n",
"3 fc6ef1ed reported 0 1 1 2021 11 26 15 \n",
"4 3fff6d7f reported 0 1 1 2021 11 26 14 \n",
"5 df21c92d reported 0 1 1 2021 11 26 14 \n",
"6 54b9e9b2 reported 0 1 1 2021 11 26 9 \n",
"7 f156880a reported 0 1 1 2021 11 26 8 \n",
"8 622e4ab9 reported 0 1 1 2021 11 26 8 \n",
"9 b5c40bab reported 0 1 1 2021 11 26 8 "
]
},
"execution_count": 31,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"df = wr.athena.read_sql_query(\n",
" sql=\"SELECT * FROM motion_door where device_id = '00158d0002d545b4' order by timestamp desc limit 10 /*3회의실모션센서*/;\",\n",
" database=\"ambt_b2c\"\n",
")\n",
"df"
]
},
{
"cell_type": "code",
"execution_count": 32,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" device_id | \n",
" sk | \n",
" timestamp | \n",
" model | \n",
" id | \n",
" e_type | \n",
" battery | \n",
" status | \n",
" evt | \n",
" year | \n",
" month | \n",
" day | \n",
" hour | \n",
"
\n",
" \n",
" \n",
" \n",
" 0 | \n",
" 00158d0005bb96f3 | \n",
" 1638250388000 | \n",
" 2021-11-30 14:33:08 | \n",
" a2_sen_dor_01 | \n",
" 5eb49bb8 | \n",
" reported | \n",
" 0 | \n",
" 1 | \n",
" 0 | \n",
" 2021 | \n",
" 11 | \n",
" 30 | \n",
" 14 | \n",
"
\n",
" \n",
" 1 | \n",
" 00158d0005bb96f3 | \n",
" 1638146963000 | \n",
" 2021-11-29 09:49:23 | \n",
" a2_sen_dor_01 | \n",
" 0ee044e5 | \n",
" reported | \n",
" 0 | \n",
" 1 | \n",
" 1 | \n",
" 2021 | \n",
" 11 | \n",
" 29 | \n",
" 9 | \n",
"
\n",
" \n",
" 2 | \n",
" 00158d0005bb96f3 | \n",
" 1638145524000 | \n",
" 2021-11-29 09:25:24 | \n",
" a2_sen_dor_01 | \n",
" bced6030 | \n",
" reported | \n",
" 0 | \n",
" 1 | \n",
" 0 | \n",
" 2021 | \n",
" 11 | \n",
" 29 | \n",
" 9 | \n",
"
\n",
" \n",
" 3 | \n",
" 00158d0005bb96f3 | \n",
" 1638145260000 | \n",
" 2021-11-29 09:21:00 | \n",
" a2_sen_dor_01 | \n",
" dcc754bc | \n",
" reported | \n",
" 0 | \n",
" 1 | \n",
" 1 | \n",
" 2021 | \n",
" 11 | \n",
" 29 | \n",
" 9 | \n",
"
\n",
" \n",
" 4 | \n",
" 00158d0005bb96f3 | \n",
" 1638145039000 | \n",
" 2021-11-29 09:17:19 | \n",
" a2_sen_dor_01 | \n",
" 5ee6843e | \n",
" reported | \n",
" 0 | \n",
" 1 | \n",
" 0 | \n",
" 2021 | \n",
" 11 | \n",
" 29 | \n",
" 9 | \n",
"
\n",
" \n",
" 5 | \n",
" 00158d0005bb96f3 | \n",
" 1638145035000 | \n",
" 2021-11-29 09:17:15 | \n",
" a2_sen_dor_01 | \n",
" 274ec676 | \n",
" reported | \n",
" 0 | \n",
" 1 | \n",
" 1 | \n",
" 2021 | \n",
" 11 | \n",
" 29 | \n",
" 9 | \n",
"
\n",
" \n",
" 6 | \n",
" 00158d0005bb96f3 | \n",
" 1638144390000 | \n",
" 2021-11-29 09:06:30 | \n",
" a2_sen_dor_01 | \n",
" ffb89511 | \n",
" reported | \n",
" 0 | \n",
" 1 | \n",
" 0 | \n",
" 2021 | \n",
" 11 | \n",
" 29 | \n",
" 9 | \n",
"
\n",
" \n",
" 7 | \n",
" 00158d0005bb96f3 | \n",
" 1638142790000 | \n",
" 2021-11-29 08:39:50 | \n",
" a2_sen_dor_01 | \n",
" cc1f5fdb | \n",
" reported | \n",
" 0 | \n",
" 1 | \n",
" 1 | \n",
" 2021 | \n",
" 11 | \n",
" 29 | \n",
" 8 | \n",
"
\n",
" \n",
" 8 | \n",
" 00158d0005bb96f3 | \n",
" 1638142526000 | \n",
" 2021-11-29 08:35:26 | \n",
" a2_sen_dor_01 | \n",
" 16e525f4 | \n",
" reported | \n",
" 0 | \n",
" 1 | \n",
" 0 | \n",
" 2021 | \n",
" 11 | \n",
" 29 | \n",
" 8 | \n",
"
\n",
" \n",
" 9 | \n",
" 00158d0005bb96f3 | \n",
" 1637913816000 | \n",
" 2021-11-26 17:03:36 | \n",
" a2_sen_dor_01 | \n",
" 7c29c17d | \n",
" reported | \n",
" 0 | \n",
" 1 | \n",
" 1 | \n",
" 2021 | \n",
" 11 | \n",
" 26 | \n",
" 17 | \n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" device_id sk timestamp model \\\n",
"0 00158d0005bb96f3 1638250388000 2021-11-30 14:33:08 a2_sen_dor_01 \n",
"1 00158d0005bb96f3 1638146963000 2021-11-29 09:49:23 a2_sen_dor_01 \n",
"2 00158d0005bb96f3 1638145524000 2021-11-29 09:25:24 a2_sen_dor_01 \n",
"3 00158d0005bb96f3 1638145260000 2021-11-29 09:21:00 a2_sen_dor_01 \n",
"4 00158d0005bb96f3 1638145039000 2021-11-29 09:17:19 a2_sen_dor_01 \n",
"5 00158d0005bb96f3 1638145035000 2021-11-29 09:17:15 a2_sen_dor_01 \n",
"6 00158d0005bb96f3 1638144390000 2021-11-29 09:06:30 a2_sen_dor_01 \n",
"7 00158d0005bb96f3 1638142790000 2021-11-29 08:39:50 a2_sen_dor_01 \n",
"8 00158d0005bb96f3 1638142526000 2021-11-29 08:35:26 a2_sen_dor_01 \n",
"9 00158d0005bb96f3 1637913816000 2021-11-26 17:03:36 a2_sen_dor_01 \n",
"\n",
" id e_type battery status evt year month day hour \n",
"0 5eb49bb8 reported 0 1 0 2021 11 30 14 \n",
"1 0ee044e5 reported 0 1 1 2021 11 29 9 \n",
"2 bced6030 reported 0 1 0 2021 11 29 9 \n",
"3 dcc754bc reported 0 1 1 2021 11 29 9 \n",
"4 5ee6843e reported 0 1 0 2021 11 29 9 \n",
"5 274ec676 reported 0 1 1 2021 11 29 9 \n",
"6 ffb89511 reported 0 1 0 2021 11 29 9 \n",
"7 cc1f5fdb reported 0 1 1 2021 11 29 8 \n",
"8 16e525f4 reported 0 1 0 2021 11 29 8 \n",
"9 7c29c17d reported 0 1 1 2021 11 26 17 "
]
},
"execution_count": 32,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"df = wr.athena.read_sql_query(\n",
" sql=\"SELECT * FROM motion_door where device_id = '00158d0005bb96f3' order by timestamp desc limit 10 /*3회의실문열림센서*/;\",\n",
" database=\"ambt_b2c\"\n",
")\n",
"df"
]
},
{
"cell_type": "code",
"execution_count": 26,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" device_id | \n",
" sk | \n",
" timestamp | \n",
" model | \n",
" id | \n",
" e_type | \n",
" battery | \n",
" status | \n",
" evt | \n",
" year | \n",
" month | \n",
" day | \n",
" hour | \n",
"
\n",
" \n",
" \n",
" \n",
" 0 | \n",
" 00158d0005a9998b | \n",
" 1638246272000 | \n",
" 2021-11-30 13:24:32 | \n",
" a2_sen_cds_01 | \n",
" 93f9369c | \n",
" reported | \n",
" 0 | \n",
" 1 | \n",
" 9 | \n",
" 2021 | \n",
" 11 | \n",
" 30 | \n",
" 13 | \n",
"
\n",
" \n",
" 1 | \n",
" 00158d0005a9998b | \n",
" 1638244831000 | \n",
" 2021-11-30 13:00:31 | \n",
" a2_sen_cds_01 | \n",
" 601c4eea | \n",
" reported | \n",
" 0 | \n",
" 1 | \n",
" 10 | \n",
" 2021 | \n",
" 11 | \n",
" 30 | \n",
" 13 | \n",
"
\n",
" \n",
" 2 | \n",
" 00158d0005a9998b | \n",
" 1638244822000 | \n",
" 2021-11-30 13:00:22 | \n",
" a2_sen_cds_01 | \n",
" 7abd863e | \n",
" reported | \n",
" 0 | \n",
" 1 | \n",
" 9 | \n",
" 2021 | \n",
" 11 | \n",
" 30 | \n",
" 13 | \n",
"
\n",
" \n",
" 3 | \n",
" 00158d0005a9998b | \n",
" 1638244791000 | \n",
" 2021-11-30 12:59:51 | \n",
" a2_sen_cds_01 | \n",
" ae4e0806 | \n",
" reported | \n",
" 0 | \n",
" 1 | \n",
" 4 | \n",
" 2021 | \n",
" 11 | \n",
" 30 | \n",
" 12 | \n",
"
\n",
" \n",
" 4 | \n",
" 00158d0005a9998b | \n",
" 1638244781000 | \n",
" 2021-11-30 12:59:41 | \n",
" a2_sen_cds_01 | \n",
" 129de4ae | \n",
" reported | \n",
" 0 | \n",
" 1 | \n",
" 5 | \n",
" 2021 | \n",
" 11 | \n",
" 30 | \n",
" 12 | \n",
"
\n",
" \n",
" 5 | \n",
" 00158d0005a9998b | \n",
" 1638244499000 | \n",
" 2021-11-30 12:54:59 | \n",
" a2_sen_cds_01 | \n",
" cc8ff83e | \n",
" reported | \n",
" 0 | \n",
" 1 | \n",
" 4 | \n",
" 2021 | \n",
" 11 | \n",
" 30 | \n",
" 12 | \n",
"
\n",
" \n",
" 6 | \n",
" 00158d0005a9998b | \n",
" 1638244489000 | \n",
" 2021-11-30 12:54:49 | \n",
" a2_sen_cds_01 | \n",
" e821a0ed | \n",
" reported | \n",
" 0 | \n",
" 1 | \n",
" 3 | \n",
" 2021 | \n",
" 11 | \n",
" 30 | \n",
" 12 | \n",
"
\n",
" \n",
" 7 | \n",
" 00158d0005a9998b | \n",
" 1638244479000 | \n",
" 2021-11-30 12:54:39 | \n",
" a2_sen_cds_01 | \n",
" 147ee512 | \n",
" reported | \n",
" 0 | \n",
" 1 | \n",
" 4 | \n",
" 2021 | \n",
" 11 | \n",
" 30 | \n",
" 12 | \n",
"
\n",
" \n",
" 8 | \n",
" 00158d0005a9998b | \n",
" 1638244419000 | \n",
" 2021-11-30 12:53:39 | \n",
" a2_sen_cds_01 | \n",
" b9431dfe | \n",
" reported | \n",
" 0 | \n",
" 1 | \n",
" 3 | \n",
" 2021 | \n",
" 11 | \n",
" 30 | \n",
" 12 | \n",
"
\n",
" \n",
" 9 | \n",
" 00158d0005a9998b | \n",
" 1638244358000 | \n",
" 2021-11-30 12:52:38 | \n",
" a2_sen_cds_01 | \n",
" 7d9eb350 | \n",
" reported | \n",
" 0 | \n",
" 1 | \n",
" 4 | \n",
" 2021 | \n",
" 11 | \n",
" 30 | \n",
" 12 | \n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" device_id sk timestamp model \\\n",
"0 00158d0005a9998b 1638246272000 2021-11-30 13:24:32 a2_sen_cds_01 \n",
"1 00158d0005a9998b 1638244831000 2021-11-30 13:00:31 a2_sen_cds_01 \n",
"2 00158d0005a9998b 1638244822000 2021-11-30 13:00:22 a2_sen_cds_01 \n",
"3 00158d0005a9998b 1638244791000 2021-11-30 12:59:51 a2_sen_cds_01 \n",
"4 00158d0005a9998b 1638244781000 2021-11-30 12:59:41 a2_sen_cds_01 \n",
"5 00158d0005a9998b 1638244499000 2021-11-30 12:54:59 a2_sen_cds_01 \n",
"6 00158d0005a9998b 1638244489000 2021-11-30 12:54:49 a2_sen_cds_01 \n",
"7 00158d0005a9998b 1638244479000 2021-11-30 12:54:39 a2_sen_cds_01 \n",
"8 00158d0005a9998b 1638244419000 2021-11-30 12:53:39 a2_sen_cds_01 \n",
"9 00158d0005a9998b 1638244358000 2021-11-30 12:52:38 a2_sen_cds_01 \n",
"\n",
" id e_type battery status evt year month day hour \n",
"0 93f9369c reported 0 1 9 2021 11 30 13 \n",
"1 601c4eea reported 0 1 10 2021 11 30 13 \n",
"2 7abd863e reported 0 1 9 2021 11 30 13 \n",
"3 ae4e0806 reported 0 1 4 2021 11 30 12 \n",
"4 129de4ae reported 0 1 5 2021 11 30 12 \n",
"5 cc8ff83e reported 0 1 4 2021 11 30 12 \n",
"6 e821a0ed reported 0 1 3 2021 11 30 12 \n",
"7 147ee512 reported 0 1 4 2021 11 30 12 \n",
"8 b9431dfe reported 0 1 3 2021 11 30 12 \n",
"9 7d9eb350 reported 0 1 4 2021 11 30 12 "
]
},
"execution_count": 26,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"df = wr.athena.read_sql_query(\n",
" sql=\"SELECT * FROM illumination where device_id = '00158d0005a9998b' order by timestamp desc limit 10 /*연구소조도센서*/;\",\n",
" database=\"ambt_b2c\"\n",
")\n",
"df"
]
},
{
"cell_type": "code",
"execution_count": 27,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" device_id | \n",
" sk | \n",
" timestamp | \n",
" model | \n",
" id | \n",
" e_type | \n",
" battery | \n",
" status | \n",
" evt | \n",
" year | \n",
" month | \n",
" day | \n",
" hour | \n",
"
\n",
" \n",
" \n",
" \n",
" 0 | \n",
" 00158d0002cab225 | \n",
" 1638248019000 | \n",
" 2021-11-30 13:53:39 | \n",
" a2_sen_mt_01 | \n",
" 1040e7c6 | \n",
" reported | \n",
" 0 | \n",
" 1 | \n",
" 1 | \n",
" 2021 | \n",
" 11 | \n",
" 30 | \n",
" 13 | \n",
"
\n",
" \n",
" 1 | \n",
" 00158d0002cab225 | \n",
" 1638247475000 | \n",
" 2021-11-30 13:44:35 | \n",
" a2_sen_mt_01 | \n",
" 4af86acf | \n",
" reported | \n",
" 0 | \n",
" 1 | \n",
" 1 | \n",
" 2021 | \n",
" 11 | \n",
" 30 | \n",
" 13 | \n",
"
\n",
" \n",
" 2 | \n",
" 00158d0002cab225 | \n",
" 1638246438000 | \n",
" 2021-11-30 13:27:18 | \n",
" a2_sen_mt_01 | \n",
" 32b6aa68 | \n",
" reported | \n",
" 0 | \n",
" 1 | \n",
" 1 | \n",
" 2021 | \n",
" 11 | \n",
" 30 | \n",
" 13 | \n",
"
\n",
" \n",
" 3 | \n",
" 00158d0002cab225 | \n",
" 1638233537000 | \n",
" 2021-11-30 09:52:17 | \n",
" a2_sen_mt_01 | \n",
" da53ea05 | \n",
" reported | \n",
" 0 | \n",
" 1 | \n",
" 1 | \n",
" 2021 | \n",
" 11 | \n",
" 30 | \n",
" 9 | \n",
"
\n",
" \n",
" 4 | \n",
" 00158d0002cab225 | \n",
" 1638231066000 | \n",
" 2021-11-30 09:11:06 | \n",
" a2_sen_mt_01 | \n",
" 472686d4 | \n",
" reported | \n",
" 0 | \n",
" 1 | \n",
" 1 | \n",
" 2021 | \n",
" 11 | \n",
" 30 | \n",
" 9 | \n",
"
\n",
" \n",
" 5 | \n",
" 00158d0002cab225 | \n",
" 1638230419000 | \n",
" 2021-11-30 09:00:19 | \n",
" a2_sen_mt_01 | \n",
" 9018cbfd | \n",
" reported | \n",
" 0 | \n",
" 1 | \n",
" 1 | \n",
" 2021 | \n",
" 11 | \n",
" 30 | \n",
" 9 | \n",
"
\n",
" \n",
" 6 | \n",
" 00158d0002cab225 | \n",
" 1638227099000 | \n",
" 2021-11-30 08:04:59 | \n",
" a2_sen_mt_01 | \n",
" 9b7710a8 | \n",
" reported | \n",
" 0 | \n",
" 1 | \n",
" 1 | \n",
" 2021 | \n",
" 11 | \n",
" 30 | \n",
" 8 | \n",
"
\n",
" \n",
" 7 | \n",
" 00158d0002cab225 | \n",
" 1638217727000 | \n",
" 2021-11-30 05:28:47 | \n",
" a2_sen_mt_01 | \n",
" 4805e5b3 | \n",
" reported | \n",
" 0 | \n",
" 1 | \n",
" 1 | \n",
" 2021 | \n",
" 11 | \n",
" 30 | \n",
" 5 | \n",
"
\n",
" \n",
" 8 | \n",
" 00158d0002cab225 | \n",
" 1638207705000 | \n",
" 2021-11-30 02:41:45 | \n",
" a2_sen_mt_01 | \n",
" de5f7e04 | \n",
" reported | \n",
" 0 | \n",
" 1 | \n",
" 1 | \n",
" 2021 | \n",
" 11 | \n",
" 30 | \n",
" 2 | \n",
"
\n",
" \n",
" 9 | \n",
" 00158d0002cab225 | \n",
" 1638177874000 | \n",
" 2021-11-29 18:24:34 | \n",
" a2_sen_mt_01 | \n",
" fa93d91d | \n",
" reported | \n",
" 0 | \n",
" 1 | \n",
" 1 | \n",
" 2021 | \n",
" 11 | \n",
" 29 | \n",
" 18 | \n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" device_id sk timestamp model \\\n",
"0 00158d0002cab225 1638248019000 2021-11-30 13:53:39 a2_sen_mt_01 \n",
"1 00158d0002cab225 1638247475000 2021-11-30 13:44:35 a2_sen_mt_01 \n",
"2 00158d0002cab225 1638246438000 2021-11-30 13:27:18 a2_sen_mt_01 \n",
"3 00158d0002cab225 1638233537000 2021-11-30 09:52:17 a2_sen_mt_01 \n",
"4 00158d0002cab225 1638231066000 2021-11-30 09:11:06 a2_sen_mt_01 \n",
"5 00158d0002cab225 1638230419000 2021-11-30 09:00:19 a2_sen_mt_01 \n",
"6 00158d0002cab225 1638227099000 2021-11-30 08:04:59 a2_sen_mt_01 \n",
"7 00158d0002cab225 1638217727000 2021-11-30 05:28:47 a2_sen_mt_01 \n",
"8 00158d0002cab225 1638207705000 2021-11-30 02:41:45 a2_sen_mt_01 \n",
"9 00158d0002cab225 1638177874000 2021-11-29 18:24:34 a2_sen_mt_01 \n",
"\n",
" id e_type battery status evt year month day hour \n",
"0 1040e7c6 reported 0 1 1 2021 11 30 13 \n",
"1 4af86acf reported 0 1 1 2021 11 30 13 \n",
"2 32b6aa68 reported 0 1 1 2021 11 30 13 \n",
"3 da53ea05 reported 0 1 1 2021 11 30 9 \n",
"4 472686d4 reported 0 1 1 2021 11 30 9 \n",
"5 9018cbfd reported 0 1 1 2021 11 30 9 \n",
"6 9b7710a8 reported 0 1 1 2021 11 30 8 \n",
"7 4805e5b3 reported 0 1 1 2021 11 30 5 \n",
"8 de5f7e04 reported 0 1 1 2021 11 30 2 \n",
"9 fa93d91d reported 0 1 1 2021 11 29 18 "
]
},
"execution_count": 27,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"df = wr.athena.read_sql_query(\n",
" sql=\"SELECT * FROM motion_door where device_id = '00158d0002cab225' order by timestamp desc limit 10 /*연구소모션센서*/;\",\n",
" database=\"ambt_b2c\"\n",
")\n",
"df"
]
},
{
"cell_type": "code",
"execution_count": 28,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" device_id | \n",
" sk | \n",
" timestamp | \n",
" model | \n",
" id | \n",
" e_type | \n",
" battery | \n",
" status | \n",
" evt | \n",
" year | \n",
" month | \n",
" day | \n",
" hour | \n",
"
\n",
" \n",
" \n",
" \n",
" 0 | \n",
" 00158d0005bb968a | \n",
" 1638247519000 | \n",
" 2021-11-30 13:45:19 | \n",
" a2_sen_dor_01 | \n",
" c4f0d490 | \n",
" reported | \n",
" 0 | \n",
" 1 | \n",
" 0 | \n",
" 2021 | \n",
" 11 | \n",
" 30 | \n",
" 13 | \n",
"
\n",
" \n",
" 1 | \n",
" 00158d0005bb968a | \n",
" 1638247514000 | \n",
" 2021-11-30 13:45:14 | \n",
" a2_sen_dor_01 | \n",
" 24500391 | \n",
" reported | \n",
" 0 | \n",
" 1 | \n",
" 1 | \n",
" 2021 | \n",
" 11 | \n",
" 30 | \n",
" 13 | \n",
"
\n",
" \n",
" 2 | \n",
" 00158d0005bb968a | \n",
" 1638247483000 | \n",
" 2021-11-30 13:44:43 | \n",
" a2_sen_dor_01 | \n",
" a1f435ab | \n",
" reported | \n",
" 0 | \n",
" 1 | \n",
" 0 | \n",
" 2021 | \n",
" 11 | \n",
" 30 | \n",
" 13 | \n",
"
\n",
" \n",
" 3 | \n",
" 00158d0005bb968a | \n",
" 1638247479000 | \n",
" 2021-11-30 13:44:39 | \n",
" a2_sen_dor_01 | \n",
" 26db99c3 | \n",
" reported | \n",
" 0 | \n",
" 1 | \n",
" 1 | \n",
" 2021 | \n",
" 11 | \n",
" 30 | \n",
" 13 | \n",
"
\n",
" \n",
" 4 | \n",
" 00158d0005bb968a | \n",
" 1638246665000 | \n",
" 2021-11-30 13:31:05 | \n",
" a2_sen_dor_01 | \n",
" df9ef5b4 | \n",
" reported | \n",
" 0 | \n",
" 1 | \n",
" 0 | \n",
" 2021 | \n",
" 11 | \n",
" 30 | \n",
" 13 | \n",
"
\n",
" \n",
" 5 | \n",
" 00158d0005bb968a | \n",
" 1638246662000 | \n",
" 2021-11-30 13:31:02 | \n",
" a2_sen_dor_01 | \n",
" 2fa18fc4 | \n",
" reported | \n",
" 0 | \n",
" 1 | \n",
" 1 | \n",
" 2021 | \n",
" 11 | \n",
" 30 | \n",
" 13 | \n",
"
\n",
" \n",
" 6 | \n",
" 00158d0005bb968a | \n",
" 1638246449000 | \n",
" 2021-11-30 13:27:29 | \n",
" a2_sen_dor_01 | \n",
" c8ad680b | \n",
" reported | \n",
" 0 | \n",
" 1 | \n",
" 0 | \n",
" 2021 | \n",
" 11 | \n",
" 30 | \n",
" 13 | \n",
"
\n",
" \n",
" 7 | \n",
" 00158d0005bb968a | \n",
" 1638246447000 | \n",
" 2021-11-30 13:27:27 | \n",
" a2_sen_dor_01 | \n",
" 92f56584 | \n",
" reported | \n",
" 0 | \n",
" 1 | \n",
" 1 | \n",
" 2021 | \n",
" 11 | \n",
" 30 | \n",
" 13 | \n",
"
\n",
" \n",
" 8 | \n",
" 00158d0005bb968a | \n",
" 1638246130000 | \n",
" 2021-11-30 13:22:10 | \n",
" a2_sen_dor_01 | \n",
" ee0271a4 | \n",
" reported | \n",
" 0 | \n",
" 1 | \n",
" 0 | \n",
" 2021 | \n",
" 11 | \n",
" 30 | \n",
" 13 | \n",
"
\n",
" \n",
" 9 | \n",
" 00158d0005bb968a | \n",
" 1638246126000 | \n",
" 2021-11-30 13:22:06 | \n",
" a2_sen_dor_01 | \n",
" 4aaeaed6 | \n",
" reported | \n",
" 0 | \n",
" 1 | \n",
" 1 | \n",
" 2021 | \n",
" 11 | \n",
" 30 | \n",
" 13 | \n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" device_id sk timestamp model \\\n",
"0 00158d0005bb968a 1638247519000 2021-11-30 13:45:19 a2_sen_dor_01 \n",
"1 00158d0005bb968a 1638247514000 2021-11-30 13:45:14 a2_sen_dor_01 \n",
"2 00158d0005bb968a 1638247483000 2021-11-30 13:44:43 a2_sen_dor_01 \n",
"3 00158d0005bb968a 1638247479000 2021-11-30 13:44:39 a2_sen_dor_01 \n",
"4 00158d0005bb968a 1638246665000 2021-11-30 13:31:05 a2_sen_dor_01 \n",
"5 00158d0005bb968a 1638246662000 2021-11-30 13:31:02 a2_sen_dor_01 \n",
"6 00158d0005bb968a 1638246449000 2021-11-30 13:27:29 a2_sen_dor_01 \n",
"7 00158d0005bb968a 1638246447000 2021-11-30 13:27:27 a2_sen_dor_01 \n",
"8 00158d0005bb968a 1638246130000 2021-11-30 13:22:10 a2_sen_dor_01 \n",
"9 00158d0005bb968a 1638246126000 2021-11-30 13:22:06 a2_sen_dor_01 \n",
"\n",
" id e_type battery status evt year month day hour \n",
"0 c4f0d490 reported 0 1 0 2021 11 30 13 \n",
"1 24500391 reported 0 1 1 2021 11 30 13 \n",
"2 a1f435ab reported 0 1 0 2021 11 30 13 \n",
"3 26db99c3 reported 0 1 1 2021 11 30 13 \n",
"4 df9ef5b4 reported 0 1 0 2021 11 30 13 \n",
"5 2fa18fc4 reported 0 1 1 2021 11 30 13 \n",
"6 c8ad680b reported 0 1 0 2021 11 30 13 \n",
"7 92f56584 reported 0 1 1 2021 11 30 13 \n",
"8 ee0271a4 reported 0 1 0 2021 11 30 13 \n",
"9 4aaeaed6 reported 0 1 1 2021 11 30 13 "
]
},
"execution_count": 28,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"df = wr.athena.read_sql_query(\n",
" sql=\"SELECT * FROM motion_door where device_id = '00158d0005bb968a' order by timestamp desc limit 10 /*연구소문열림센서*/;\",\n",
" database=\"ambt_b2c\"\n",
")\n",
"df"
]
},
{
"cell_type": "code",
"execution_count": 29,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" device_id | \n",
" sk | \n",
" timestamp | \n",
" model | \n",
" id | \n",
" e_type | \n",
" hum | \n",
" temp | \n",
" battery | \n",
" status | \n",
" year | \n",
" month | \n",
" day | \n",
" hour | \n",
"
\n",
" \n",
" \n",
" \n",
" 0 | \n",
" 00158d00049b8ff3 | \n",
" 1637558253000 | \n",
" 2021-11-22 14:17:33 | \n",
" a2_sen_th_01 | \n",
" 683fa131 | \n",
" reported | \n",
" 29 | \n",
" 226 | \n",
" 0 | \n",
" 1 | \n",
" 2021 | \n",
" 11 | \n",
" 22 | \n",
" 14 | \n",
"
\n",
" \n",
" 1 | \n",
" 00158d00049b8ff3 | \n",
" 1637557952000 | \n",
" 2021-11-22 14:12:32 | \n",
" a2_sen_th_01 | \n",
" 2ac12530 | \n",
" reported | \n",
" 29 | \n",
" 228 | \n",
" 0 | \n",
" 1 | \n",
" 2021 | \n",
" 11 | \n",
" 22 | \n",
" 14 | \n",
"
\n",
" \n",
" 2 | \n",
" 00158d00049b8ff3 | \n",
" 1637557952000 | \n",
" 2021-11-22 14:12:32 | \n",
" a2_sen_th_01 | \n",
" 42741e86 | \n",
" reported | \n",
" 30 | \n",
" 228 | \n",
" 0 | \n",
" 1 | \n",
" 2021 | \n",
" 11 | \n",
" 22 | \n",
" 14 | \n",
"
\n",
" \n",
" 3 | \n",
" 00158d00049b8ff3 | \n",
" 1637557350000 | \n",
" 2021-11-22 14:02:30 | \n",
" a2_sen_th_01 | \n",
" 24c99ff6 | \n",
" reported | \n",
" 30 | \n",
" 229 | \n",
" 0 | \n",
" 1 | \n",
" 2021 | \n",
" 11 | \n",
" 22 | \n",
" 14 | \n",
"
\n",
" \n",
" 4 | \n",
" 00158d00049b8ff3 | \n",
" 1637556749000 | \n",
" 2021-11-22 13:52:29 | \n",
" a2_sen_th_01 | \n",
" 413bb2a0 | \n",
" reported | \n",
" 30 | \n",
" 228 | \n",
" 0 | \n",
" 1 | \n",
" 2021 | \n",
" 11 | \n",
" 22 | \n",
" 13 | \n",
"
\n",
" \n",
" 5 | \n",
" 00158d00049b8ff3 | \n",
" 1637556147000 | \n",
" 2021-11-22 13:42:27 | \n",
" a2_sen_th_01 | \n",
" 6ea69ba3 | \n",
" reported | \n",
" 29 | \n",
" 228 | \n",
" 0 | \n",
" 1 | \n",
" 2021 | \n",
" 11 | \n",
" 22 | \n",
" 13 | \n",
"
\n",
" \n",
" 6 | \n",
" 00158d00049b8ff3 | \n",
" 1637555846000 | \n",
" 2021-11-22 13:37:26 | \n",
" a2_sen_th_01 | \n",
" 3f9e8043 | \n",
" reported | \n",
" 29 | \n",
" 227 | \n",
" 0 | \n",
" 1 | \n",
" 2021 | \n",
" 11 | \n",
" 22 | \n",
" 13 | \n",
"
\n",
" \n",
" 7 | \n",
" 00158d00049b8ff3 | \n",
" 1637554943000 | \n",
" 2021-11-22 13:22:23 | \n",
" a2_sen_th_01 | \n",
" bb276dda | \n",
" reported | \n",
" 29 | \n",
" 226 | \n",
" 0 | \n",
" 1 | \n",
" 2021 | \n",
" 11 | \n",
" 22 | \n",
" 13 | \n",
"
\n",
" \n",
" 8 | \n",
" 00158d00049b8ff3 | \n",
" 1637554642000 | \n",
" 2021-11-22 13:17:22 | \n",
" a2_sen_th_01 | \n",
" 14445641 | \n",
" reported | \n",
" 29 | \n",
" 225 | \n",
" 0 | \n",
" 1 | \n",
" 2021 | \n",
" 11 | \n",
" 22 | \n",
" 13 | \n",
"
\n",
" \n",
" 9 | \n",
" 00158d00049b8ff3 | \n",
" 1637553739000 | \n",
" 2021-11-22 13:02:19 | \n",
" a2_sen_th_01 | \n",
" 5ccc4310 | \n",
" reported | \n",
" 28 | \n",
" 225 | \n",
" 0 | \n",
" 1 | \n",
" 2021 | \n",
" 11 | \n",
" 22 | \n",
" 13 | \n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" device_id sk timestamp model \\\n",
"0 00158d00049b8ff3 1637558253000 2021-11-22 14:17:33 a2_sen_th_01 \n",
"1 00158d00049b8ff3 1637557952000 2021-11-22 14:12:32 a2_sen_th_01 \n",
"2 00158d00049b8ff3 1637557952000 2021-11-22 14:12:32 a2_sen_th_01 \n",
"3 00158d00049b8ff3 1637557350000 2021-11-22 14:02:30 a2_sen_th_01 \n",
"4 00158d00049b8ff3 1637556749000 2021-11-22 13:52:29 a2_sen_th_01 \n",
"5 00158d00049b8ff3 1637556147000 2021-11-22 13:42:27 a2_sen_th_01 \n",
"6 00158d00049b8ff3 1637555846000 2021-11-22 13:37:26 a2_sen_th_01 \n",
"7 00158d00049b8ff3 1637554943000 2021-11-22 13:22:23 a2_sen_th_01 \n",
"8 00158d00049b8ff3 1637554642000 2021-11-22 13:17:22 a2_sen_th_01 \n",
"9 00158d00049b8ff3 1637553739000 2021-11-22 13:02:19 a2_sen_th_01 \n",
"\n",
" id e_type hum temp battery status year month day hour \n",
"0 683fa131 reported 29 226 0 1 2021 11 22 14 \n",
"1 2ac12530 reported 29 228 0 1 2021 11 22 14 \n",
"2 42741e86 reported 30 228 0 1 2021 11 22 14 \n",
"3 24c99ff6 reported 30 229 0 1 2021 11 22 14 \n",
"4 413bb2a0 reported 30 228 0 1 2021 11 22 13 \n",
"5 6ea69ba3 reported 29 228 0 1 2021 11 22 13 \n",
"6 3f9e8043 reported 29 227 0 1 2021 11 22 13 \n",
"7 bb276dda reported 29 226 0 1 2021 11 22 13 \n",
"8 14445641 reported 29 225 0 1 2021 11 22 13 \n",
"9 5ccc4310 reported 28 225 0 1 2021 11 22 13 "
]
},
"execution_count": 29,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"df = wr.athena.read_sql_query(\n",
" sql=\"SELECT * FROM temperature_humidity where device_id = '00158d0006c9d545' order by timestamp desc limit 10 /*연구소온습도센서*/;\",\n",
" database=\"ambt_b2c\"\n",
")\n",
"df"
]
},
{
"cell_type": "code",
"execution_count": 30,
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"datetime.datetime(2021, 11, 30, 8, 8, 45)"
]
},
"execution_count": 30,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"import datetime\n",
"datetimeobj = datetime.datetime.fromtimestamp(1638227325000/1000)\n",
"datetimeobj"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": []
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": []
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": []
},
{
"cell_type": "code",
"execution_count": 15,
"metadata": {},
"outputs": [],
"source": [
"from datetime import datetime\n",
"datetime.now().hour\n",
"if datetime.now().weekday() < 5 :\n",
" if datetime.now().hour > 8 and datetime.now().hour < 17 :\n",
" print ('working time') "
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": []
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": []
},
{
"cell_type": "code",
"execution_count": 67,
"metadata": {},
"outputs": [],
"source": [
"a = datetime(datetime.now().year, datetime.now().month, datetime.now().day, datetime.now().hour)"
]
},
{
"cell_type": "code",
"execution_count": 68,
"metadata": {},
"outputs": [],
"source": [
"b = datetime(int(df['year'][0]), int(df['month'][0]), int(df['day'][0]), int(df['hour'][0]))"
]
},
{
"cell_type": "code",
"execution_count": 73,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"2021-11-25 13:00:00\n"
]
}
],
"source": [
"print('{}'.format(b))"
]
},
{
"cell_type": "code",
"execution_count": 69,
"metadata": {
"scrolled": false
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"not same\n"
]
}
],
"source": [
"if a!=b:\n",
" print('not same')"
]
},
{
"cell_type": "code",
"execution_count": 27,
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"'13'"
]
},
"execution_count": 27,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"df.head(1)['hour'][0]"
]
},
{
"cell_type": "code",
"execution_count": 30,
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"'2021'"
]
},
"execution_count": 30,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"df['year'][0]"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": []
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": []
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": []
},
{
"cell_type": "code",
"execution_count": 56,
"metadata": {},
"outputs": [],
"source": [
"a = datetime(datetime.now().year, datetime.now().month, datetime.now().day, datetime.now().hour)"
]
},
{
"cell_type": "code",
"execution_count": 57,
"metadata": {},
"outputs": [],
"source": [
"b = datetime(int(df['year'][0]), int(df['month'][0]), int(df['day'][0]), int(df['hour'][0]))"
]
},
{
"cell_type": "code",
"execution_count": 65,
"metadata": {
"scrolled": true
},
"outputs": [],
"source": [
"if a!=b:\n",
" print('same')"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": []
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": []
},
{
"cell_type": "code",
"execution_count": 2,
"metadata": {},
"outputs": [],
"source": [
"import awswrangler as wr\n",
"df = wr.s3.read_parquet(\"s3://hdci-prototyping/motion_door/\", dataset=True)"
]
},
{
"cell_type": "code",
"execution_count": 3,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" device_id | \n",
" sk | \n",
" timestamp | \n",
" model | \n",
" id | \n",
" e_type | \n",
" battery | \n",
" status | \n",
" evt | \n",
" year | \n",
" month | \n",
" day | \n",
" hour | \n",
"
\n",
" \n",
" \n",
" \n",
" 0 | \n",
" 00158d0005bb968a | \n",
" 1633822004000 | \n",
" 2021-10-10 08:26:44 | \n",
" a2_sen_dor_01 | \n",
" b087f0a8 | \n",
" reported | \n",
" 0 | \n",
" 1 | \n",
" 0 | \n",
" 2021 | \n",
" 10 | \n",
" 10 | \n",
" 8 | \n",
"
\n",
" \n",
" 1 | \n",
" 00158d0002cab225 | \n",
" 1634000451000 | \n",
" 2021-10-12 10:00:51 | \n",
" a2_sen_mt_01 | \n",
" c6a1edc2 | \n",
" reported | \n",
" 0 | \n",
" 1 | \n",
" 0 | \n",
" 2021 | \n",
" 10 | \n",
" 12 | \n",
" 10 | \n",
"
\n",
" \n",
" 2 | \n",
" 00158d0002cab225 | \n",
" 1634000825000 | \n",
" 2021-10-12 10:07:05 | \n",
" a2_sen_mt_01 | \n",
" 7b442366 | \n",
" reported | \n",
" 0 | \n",
" 1 | \n",
" 1 | \n",
" 2021 | \n",
" 10 | \n",
" 12 | \n",
" 10 | \n",
"
\n",
" \n",
" 3 | \n",
" 00158d0002cab225 | \n",
" 1634000886000 | \n",
" 2021-10-12 10:08:06 | \n",
" a2_sen_mt_01 | \n",
" ab3c5929 | \n",
" reported | \n",
" 0 | \n",
" 1 | \n",
" 0 | \n",
" 2021 | \n",
" 10 | \n",
" 12 | \n",
" 10 | \n",
"
\n",
" \n",
" 4 | \n",
" 00158d0002cab225 | \n",
" 1634003953000 | \n",
" 2021-10-12 10:59:13 | \n",
" a2_sen_mt_01 | \n",
" 0cdda406 | \n",
" reported | \n",
" 0 | \n",
" 1 | \n",
" 1 | \n",
" 2021 | \n",
" 10 | \n",
" 12 | \n",
" 10 | \n",
"
\n",
" \n",
" ... | \n",
" ... | \n",
" ... | \n",
" ... | \n",
" ... | \n",
" ... | \n",
" ... | \n",
" ... | \n",
" ... | \n",
" ... | \n",
" ... | \n",
" ... | \n",
" ... | \n",
" ... | \n",
"
\n",
" \n",
" 8269 | \n",
" 00158d0006c9d665 | \n",
" 1631145728000 | \n",
" 2021-09-09 09:02:08 | \n",
" a2_sen_dor_01 | \n",
" 2a101ef0 | \n",
" reported | \n",
" 6 | \n",
" 1 | \n",
" 0 | \n",
" 2021 | \n",
" 9 | \n",
" 9 | \n",
" 9 | \n",
"
\n",
" \n",
" 8270 | \n",
" 00158d0005bb968a | \n",
" 1631148819000 | \n",
" 2021-09-09 09:53:39 | \n",
" a2_sen_dor_01 | \n",
" 2838e8cc | \n",
" reported | \n",
" 0 | \n",
" 1 | \n",
" 1 | \n",
" 2021 | \n",
" 9 | \n",
" 9 | \n",
" 9 | \n",
"
\n",
" \n",
" 8271 | \n",
" 00158d0005bb96f3 | \n",
" 1631148863000 | \n",
" 2021-09-09 09:54:23 | \n",
" a2_sen_dor_01 | \n",
" 7af0ae63 | \n",
" reported | \n",
" 0 | \n",
" 1 | \n",
" 1 | \n",
" 2021 | \n",
" 9 | \n",
" 9 | \n",
" 9 | \n",
"
\n",
" \n",
" 8272 | \n",
" 00158d0006c9d665 | \n",
" 1631148869000 | \n",
" 2021-09-09 09:54:29 | \n",
" a2_sen_dor_01 | \n",
" 3d8375e2 | \n",
" reported | \n",
" 6 | \n",
" 1 | \n",
" 0 | \n",
" 2021 | \n",
" 9 | \n",
" 9 | \n",
" 9 | \n",
"
\n",
" \n",
" 8273 | \n",
" 00158d0002d545b4 | \n",
" 1631149065000 | \n",
" 2021-09-09 09:57:45 | \n",
" a2_sen_mt_01 | \n",
" 99fb428f | \n",
" reported | \n",
" 0 | \n",
" 1 | \n",
" 1 | \n",
" 2021 | \n",
" 9 | \n",
" 9 | \n",
" 9 | \n",
"
\n",
" \n",
"
\n",
"
8274 rows × 13 columns
\n",
"
"
],
"text/plain": [
" device_id sk timestamp model \\\n",
"0 00158d0005bb968a 1633822004000 2021-10-10 08:26:44 a2_sen_dor_01 \n",
"1 00158d0002cab225 1634000451000 2021-10-12 10:00:51 a2_sen_mt_01 \n",
"2 00158d0002cab225 1634000825000 2021-10-12 10:07:05 a2_sen_mt_01 \n",
"3 00158d0002cab225 1634000886000 2021-10-12 10:08:06 a2_sen_mt_01 \n",
"4 00158d0002cab225 1634003953000 2021-10-12 10:59:13 a2_sen_mt_01 \n",
"... ... ... ... ... \n",
"8269 00158d0006c9d665 1631145728000 2021-09-09 09:02:08 a2_sen_dor_01 \n",
"8270 00158d0005bb968a 1631148819000 2021-09-09 09:53:39 a2_sen_dor_01 \n",
"8271 00158d0005bb96f3 1631148863000 2021-09-09 09:54:23 a2_sen_dor_01 \n",
"8272 00158d0006c9d665 1631148869000 2021-09-09 09:54:29 a2_sen_dor_01 \n",
"8273 00158d0002d545b4 1631149065000 2021-09-09 09:57:45 a2_sen_mt_01 \n",
"\n",
" id e_type battery status evt year month day hour \n",
"0 b087f0a8 reported 0 1 0 2021 10 10 8 \n",
"1 c6a1edc2 reported 0 1 0 2021 10 12 10 \n",
"2 7b442366 reported 0 1 1 2021 10 12 10 \n",
"3 ab3c5929 reported 0 1 0 2021 10 12 10 \n",
"4 0cdda406 reported 0 1 1 2021 10 12 10 \n",
"... ... ... ... ... ... ... ... .. ... \n",
"8269 2a101ef0 reported 6 1 0 2021 9 9 9 \n",
"8270 2838e8cc reported 0 1 1 2021 9 9 9 \n",
"8271 7af0ae63 reported 0 1 1 2021 9 9 9 \n",
"8272 3d8375e2 reported 6 1 0 2021 9 9 9 \n",
"8273 99fb428f reported 0 1 1 2021 9 9 9 \n",
"\n",
"[8274 rows x 13 columns]"
]
},
"execution_count": 3,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"df"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": []
}
],
"metadata": {
"kernelspec": {
"display_name": "Python 3",
"language": "python",
"name": "python3"
},
"language_info": {
"codemirror_mode": {
"name": "ipython",
"version": 3
},
"file_extension": ".py",
"mimetype": "text/x-python",
"name": "python",
"nbconvert_exporter": "python",
"pygments_lexer": "ipython3",
"version": "3.8.8"
}
},
"nbformat": 4,
"nbformat_minor": 5
}