%pip install awswrangler boto3
Collecting awswrangler Downloading awswrangler-2.14.0-py3-none-any.whl (226 kB) [2K [90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━[0m [32m226.6/226.6 KB[0m [31m7.9 MB/s[0m eta [36m0:00:00[0m [?25hCollecting boto3 Downloading boto3-1.21.25-py3-none-any.whl (132 kB) [2K [90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━[0m [32m132.3/132.3 KB[0m [31m9.1 MB/s[0m eta [36m0:00:00[0m [?25hCollecting pg8000<1.23.0,>=1.16.0 Downloading pg8000-1.22.1-py3-none-any.whl (33 kB) Collecting botocore<2.0.0,>=1.23.17 Downloading botocore-1.24.25-py3-none-any.whl (8.6 MB) [2K [90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━[0m [32m8.6/8.6 MB[0m [31m11.2 MB/s[0m eta [36m0:00:00[0m00:01[0m00:01[0m [?25hCollecting requests-aws4auth<2.0.0,>=1.1.1 Using cached requests_aws4auth-1.1.1-py2.py3-none-any.whl (31 kB) Requirement already satisfied: pandas<1.4.0,>=1.2.0 in /home/ken/anaconda3/lib/python3.8/site-packages (from awswrangler) (1.3.0) Collecting pymysql<1.1.0,>=0.9.0 Using cached PyMySQL-1.0.2-py3-none-any.whl (43 kB) Collecting redshift-connector<2.1.0,>=2.0.889 Downloading redshift_connector-2.0.905-py3-none-any.whl (109 kB) [2K [90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━[0m [32m109.1/109.1 KB[0m [31m7.6 MB/s[0m eta [36m0:00:00[0m [?25hCollecting opensearch-py<2.0.0,>=1.0.0 Downloading opensearch_py-1.1.0-py2.py3-none-any.whl (207 kB) [2K [90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━[0m [32m207.5/207.5 KB[0m [31m11.7 MB/s[0m eta [36m0:00:00[0m [?25hCollecting numpy<2.0.0,>=1.21.0 Downloading numpy-1.22.3-cp38-cp38-manylinux_2_17_x86_64.manylinux2014_x86_64.whl (16.8 MB) [2K [90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━[0m [32m16.8/16.8 MB[0m [31m9.9 MB/s[0m eta [36m0:00:00[0m:00:01[0m00:01[0m [?25hRequirement already satisfied: openpyxl<3.1.0,>=3.0.0 in /home/ken/anaconda3/lib/python3.8/site-packages (from awswrangler) (3.0.7) Collecting pyarrow<6.1.0,>=2.0.0 Downloading pyarrow-6.0.1-cp38-cp38-manylinux_2_17_x86_64.manylinux2014_x86_64.whl (25.6 MB) [2K [90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━[0m [32m25.6/25.6 MB[0m [31m11.0 MB/s[0m eta [36m0:00:00[0m00:01[0m00:01[0m [?25hCollecting jsonpath-ng<2.0.0,>=1.5.3 Using cached jsonpath_ng-1.5.3-py3-none-any.whl (29 kB) Collecting progressbar2<4.0.0,>=3.53.3 Using cached progressbar2-3.55.0-py2.py3-none-any.whl (26 kB) Collecting s3transfer<0.6.0,>=0.5.0 Downloading s3transfer-0.5.2-py3-none-any.whl (79 kB) [2K [90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━[0m [32m79.5/79.5 KB[0m [31m8.2 MB/s[0m eta [36m0:00:00[0m [?25hCollecting jmespath<2.0.0,>=0.7.1 Downloading jmespath-1.0.0-py3-none-any.whl (23 kB) Requirement already satisfied: python-dateutil<3.0.0,>=2.1 in /home/ken/anaconda3/lib/python3.8/site-packages (from botocore<2.0.0,>=1.23.17->awswrangler) (2.8.1) Requirement already satisfied: urllib3<1.27,>=1.25.4 in /home/ken/anaconda3/lib/python3.8/site-packages (from botocore<2.0.0,>=1.23.17->awswrangler) (1.26.4) Requirement already satisfied: decorator in /home/ken/anaconda3/lib/python3.8/site-packages (from jsonpath-ng<2.0.0,>=1.5.3->awswrangler) (5.0.6) Requirement already satisfied: six in /home/ken/anaconda3/lib/python3.8/site-packages (from jsonpath-ng<2.0.0,>=1.5.3->awswrangler) (1.15.0) Requirement already satisfied: ply in /home/ken/anaconda3/lib/python3.8/site-packages (from jsonpath-ng<2.0.0,>=1.5.3->awswrangler) (3.11) Requirement already satisfied: et-xmlfile in /home/ken/anaconda3/lib/python3.8/site-packages (from openpyxl<3.1.0,>=3.0.0->awswrangler) (1.0.1) Requirement already satisfied: certifi in /home/ken/anaconda3/lib/python3.8/site-packages (from opensearch-py<2.0.0,>=1.0.0->awswrangler) (2020.12.5) Requirement already satisfied: pytz>=2017.3 in /home/ken/anaconda3/lib/python3.8/site-packages (from pandas<1.4.0,>=1.2.0->awswrangler) (2021.1) Collecting scramp>=1.4.1 Using cached scramp-1.4.1-py3-none-any.whl (8.5 kB) Collecting python-utils>=2.3.0 Downloading python_utils-3.1.0-py2.py3-none-any.whl (19 kB) Requirement already satisfied: requests<2.27.2,>=2.23.0 in /home/ken/anaconda3/lib/python3.8/site-packages (from redshift-connector<2.1.0,>=2.0.889->awswrangler) (2.25.1) Collecting lxml>=4.6.5 Downloading lxml-4.8.0-cp38-cp38-manylinux_2_17_x86_64.manylinux2014_x86_64.manylinux_2_24_x86_64.whl (6.9 MB) [2K [90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━[0m [32m6.9/6.9 MB[0m [31m11.1 MB/s[0m eta [36m0:00:00[0m00:01[0m00:01[0m [?25hRequirement already satisfied: beautifulsoup4<5.0.0,>=4.7.0 in /home/ken/anaconda3/lib/python3.8/site-packages (from redshift-connector<2.1.0,>=2.0.889->awswrangler) (4.9.3) Requirement already satisfied: packaging in /home/ken/anaconda3/lib/python3.8/site-packages (from redshift-connector<2.1.0,>=2.0.889->awswrangler) (20.9) Requirement already satisfied: soupsieve>1.2 in /home/ken/anaconda3/lib/python3.8/site-packages (from beautifulsoup4<5.0.0,>=4.7.0->redshift-connector<2.1.0,>=2.0.889->awswrangler) (2.2.1) Requirement already satisfied: chardet<5,>=3.0.2 in /home/ken/anaconda3/lib/python3.8/site-packages (from requests<2.27.2,>=2.23.0->redshift-connector<2.1.0,>=2.0.889->awswrangler) (4.0.0) Requirement already satisfied: idna<3,>=2.5 in /home/ken/anaconda3/lib/python3.8/site-packages (from requests<2.27.2,>=2.23.0->redshift-connector<2.1.0,>=2.0.889->awswrangler) (2.10) Requirement already satisfied: asn1crypto>=1.4.0 in /home/ken/anaconda3/lib/python3.8/site-packages (from scramp>=1.4.1->pg8000<1.23.0,>=1.16.0->awswrangler) (1.4.0) Requirement already satisfied: pyparsing>=2.0.2 in /home/ken/anaconda3/lib/python3.8/site-packages (from packaging->redshift-connector<2.1.0,>=2.0.889->awswrangler) (2.4.7) Installing collected packages: scramp, python-utils, pymysql, opensearch-py, numpy, lxml, jsonpath-ng, jmespath, requests-aws4auth, pyarrow, progressbar2, pg8000, botocore, s3transfer, boto3, redshift-connector, awswrangler Attempting uninstall: numpy Found existing installation: numpy 1.19.5 Uninstalling numpy-1.19.5: Successfully uninstalled numpy-1.19.5 Attempting uninstall: lxml Found existing installation: lxml 4.6.3 Uninstalling lxml-4.6.3: Successfully uninstalled lxml-4.6.3 [31mERROR: pip's dependency resolver does not currently take into account all the packages that are installed. This behaviour is the source of the following dependency conflicts. yellowbrick 1.3.post1 requires numpy<1.20,>=1.16.0, but you have numpy 1.22.3 which is incompatible.[0m[31m [0mSuccessfully installed awswrangler-2.14.0 boto3-1.21.25 botocore-1.24.25 jmespath-1.0.0 jsonpath-ng-1.5.3 lxml-4.8.0 numpy-1.22.3 opensearch-py-1.1.0 pg8000-1.22.1 progressbar2-3.55.0 pyarrow-6.0.1 pymysql-1.0.2 python-utils-3.1.0 redshift-connector-2.0.905 requests-aws4auth-1.1.1 s3transfer-0.5.2 scramp-1.4.1 [33mWARNING: You are using pip version 22.0.3; however, version 22.0.4 is available. You should consider upgrading via the '/home/ken/anaconda3/bin/python -m pip install --upgrade pip' command.[0m[33m [0mNote: you may need to restart the kernel to use updated packages.
import awswrangler as wr
df = wr.athena.read_sql_query(
sql="SELECT * FROM illumination where device_id = '00158d0006c9d5ed' order by timestamp desc limit 10 /*3회의실조도센서*/;",
database="ambt_b2c"
)
df
device_id | sk | timestamp | model | id | e_type | battery | status | evt | year | month | day | hour | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|
0 | 00158d0006c9d5ed | 1641522280000 | 2022-01-07 11:24:40 | a2_sen_cds_01 | 6ae271ef | reported | 6 | 1 | 6 | 2022 | 1 | 7 | 11 |
1 | 00158d0006c9d5ed | 1641513569000 | 2022-01-07 08:59:29 | a2_sen_cds_01 | ea3eb7e5 | reported | 6 | 1 | 6 | 2022 | 1 | 7 | 8 |
2 | 00158d0006c9d5ed | 1641510572000 | 2022-01-07 08:09:32 | a2_sen_cds_01 | cee8ce43 | reported | 6 | 1 | 1 | 2022 | 1 | 7 | 8 |
3 | 00158d0006c9d5ed | 1641509374000 | 2022-01-07 07:49:34 | a2_sen_cds_01 | 1e02ed5c | reported | 6 | 1 | 6 | 2022 | 1 | 7 | 7 |
4 | 00158d0006c9d5ed | 1641504591000 | 2022-01-07 06:29:51 | a2_sen_cds_01 | 2c5ef025 | reported | 6 | 1 | 1 | 2022 | 1 | 7 | 6 |
5 | 00158d0006c9d5ed | 1641504291000 | 2022-01-07 06:24:51 | a2_sen_cds_01 | b27ba489 | reported | 6 | 1 | 6 | 2022 | 1 | 7 | 6 |
6 | 00158d0006c9d5ed | 1641502796000 | 2022-01-07 05:59:56 | a2_sen_cds_01 | 873fa701 | reported | 6 | 1 | 1 | 2022 | 1 | 7 | 5 |
7 | 00158d0006c9d5ed | 1641453725000 | 2022-01-06 16:22:05 | a2_sen_cds_01 | 120ee414 | reported | 6 | 1 | 1 | 2022 | 1 | 6 | 16 |
8 | 00158d0006c9d5ed | 1641447725000 | 2022-01-06 14:42:05 | a2_sen_cds_01 | a4805270 | reported | 6 | 1 | 6 | 2022 | 1 | 6 | 14 |
9 | 00158d0006c9d5ed | 1641439319000 | 2022-01-06 12:21:59 | a2_sen_cds_01 | e3bb3199 | reported | 6 | 1 | 6 | 2022 | 1 | 6 | 12 |
df = wr.athena.read_sql_query(
sql="SELECT * FROM temperature_humidity where device_id = '00158d00028d93d8' order by timestamp desc limit 10 /*3회의실온습도센서*/;",
database="ambt_b2c"
)
df
device_id | sk | timestamp | model | id | e_type | hum | temp | battery | status | year | month | day | hour | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
0 | 00158d00028d93d8 | 1638146954000 | 2021-11-29 09:49:14 | a2_sen_th_01 | fdc93e9c | reported | 26 | 249 | 0 | 1 | 2021 | 11 | 29 | 9 |
1 | 00158d00028d93d8 | 1638146954000 | 2021-11-29 09:49:14 | a2_sen_th_01 | a6fa9718 | reported | 25 | 249 | 0 | 1 | 2021 | 11 | 29 | 9 |
2 | 00158d00028d93d8 | 1638146653000 | 2021-11-29 09:44:13 | a2_sen_th_01 | 19295a0d | reported | 26 | 244 | 0 | 1 | 2021 | 11 | 29 | 9 |
3 | 00158d00028d93d8 | 1638146362000 | 2021-11-29 09:39:22 | a2_sen_th_01 | 5fc92dc4 | reported | 27 | 240 | 0 | 1 | 2021 | 11 | 29 | 9 |
4 | 00158d00028d93d8 | 1638146057000 | 2021-11-29 09:34:17 | a2_sen_th_01 | 6393c2bc | reported | 27 | 234 | 0 | 1 | 2021 | 11 | 29 | 9 |
5 | 00158d00028d93d8 | 1638146056000 | 2021-11-29 09:34:16 | a2_sen_th_01 | 5f518e6c | reported | 28 | 234 | 0 | 1 | 2021 | 11 | 29 | 9 |
6 | 00158d00028d93d8 | 1638145756000 | 2021-11-29 09:29:16 | a2_sen_th_01 | 87312dd5 | reported | 28 | 227 | 0 | 1 | 2021 | 11 | 29 | 9 |
7 | 00158d00028d93d8 | 1638145751000 | 2021-11-29 09:29:11 | a2_sen_th_01 | 78ccec5f | reported | 29 | 227 | 0 | 1 | 2021 | 11 | 29 | 9 |
8 | 00158d00028d93d8 | 1638145451000 | 2021-11-29 09:24:11 | a2_sen_th_01 | 2967d0b3 | reported | 29 | 219 | 0 | 1 | 2021 | 11 | 29 | 9 |
9 | 00158d00028d93d8 | 1638145150000 | 2021-11-29 09:19:10 | a2_sen_th_01 | 7d5f4627 | reported | 29 | 211 | 0 | 1 | 2021 | 11 | 29 | 9 |
df = wr.athena.read_sql_query(
sql="SELECT * FROM motion_door where device_id = '00158d0002d545b4' order by timestamp desc limit 10 /*3회의실모션센서*/;",
database="ambt_b2c"
)
df
device_id | sk | timestamp | model | id | e_type | battery | status | evt | year | month | day | hour | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|
0 | 00158d0002d545b4 | 1638250408000 | 2021-11-30 14:33:28 | a2_sen_mt_01 | 72e2d749 | reported | 0 | 1 | 1 | 2021 | 11 | 30 | 14 |
1 | 00158d0002d545b4 | 1638248702000 | 2021-11-30 14:05:02 | a2_sen_mt_01 | 7f6500ba | reported | 0 | 1 | 1 | 2021 | 11 | 30 | 14 |
2 | 00158d0002d545b4 | 1637913814000 | 2021-11-26 17:03:34 | a2_sen_mt_01 | 7374a99b | reported | 0 | 1 | 1 | 2021 | 11 | 26 | 17 |
3 | 00158d0002d545b4 | 1637906976000 | 2021-11-26 15:09:36 | a2_sen_mt_01 | fc6ef1ed | reported | 0 | 1 | 1 | 2021 | 11 | 26 | 15 |
4 | 00158d0002d545b4 | 1637905314000 | 2021-11-26 14:41:54 | a2_sen_mt_01 | 3fff6d7f | reported | 0 | 1 | 1 | 2021 | 11 | 26 | 14 |
5 | 00158d0002d545b4 | 1637904052000 | 2021-11-26 14:20:52 | a2_sen_mt_01 | df21c92d | reported | 0 | 1 | 1 | 2021 | 11 | 26 | 14 |
6 | 00158d0002d545b4 | 1637885207000 | 2021-11-26 09:06:47 | a2_sen_mt_01 | 54b9e9b2 | reported | 0 | 1 | 1 | 2021 | 11 | 26 | 9 |
7 | 00158d0002d545b4 | 1637882804000 | 2021-11-26 08:26:44 | a2_sen_mt_01 | f156880a | reported | 0 | 1 | 1 | 2021 | 11 | 26 | 8 |
8 | 00158d0002d545b4 | 1637882445000 | 2021-11-26 08:20:45 | a2_sen_mt_01 | 622e4ab9 | reported | 0 | 1 | 1 | 2021 | 11 | 26 | 8 |
9 | 00158d0002d545b4 | 1637881650000 | 2021-11-26 08:07:30 | a2_sen_mt_01 | b5c40bab | reported | 0 | 1 | 1 | 2021 | 11 | 26 | 8 |
df = wr.athena.read_sql_query(
sql="SELECT * FROM motion_door where device_id = '00158d0005bb96f3' order by timestamp desc limit 10 /*3회의실문열림센서*/;",
database="ambt_b2c"
)
df
device_id | sk | timestamp | model | id | e_type | battery | status | evt | year | month | day | hour | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|
0 | 00158d0005bb96f3 | 1638250388000 | 2021-11-30 14:33:08 | a2_sen_dor_01 | 5eb49bb8 | reported | 0 | 1 | 0 | 2021 | 11 | 30 | 14 |
1 | 00158d0005bb96f3 | 1638146963000 | 2021-11-29 09:49:23 | a2_sen_dor_01 | 0ee044e5 | reported | 0 | 1 | 1 | 2021 | 11 | 29 | 9 |
2 | 00158d0005bb96f3 | 1638145524000 | 2021-11-29 09:25:24 | a2_sen_dor_01 | bced6030 | reported | 0 | 1 | 0 | 2021 | 11 | 29 | 9 |
3 | 00158d0005bb96f3 | 1638145260000 | 2021-11-29 09:21:00 | a2_sen_dor_01 | dcc754bc | reported | 0 | 1 | 1 | 2021 | 11 | 29 | 9 |
4 | 00158d0005bb96f3 | 1638145039000 | 2021-11-29 09:17:19 | a2_sen_dor_01 | 5ee6843e | reported | 0 | 1 | 0 | 2021 | 11 | 29 | 9 |
5 | 00158d0005bb96f3 | 1638145035000 | 2021-11-29 09:17:15 | a2_sen_dor_01 | 274ec676 | reported | 0 | 1 | 1 | 2021 | 11 | 29 | 9 |
6 | 00158d0005bb96f3 | 1638144390000 | 2021-11-29 09:06:30 | a2_sen_dor_01 | ffb89511 | reported | 0 | 1 | 0 | 2021 | 11 | 29 | 9 |
7 | 00158d0005bb96f3 | 1638142790000 | 2021-11-29 08:39:50 | a2_sen_dor_01 | cc1f5fdb | reported | 0 | 1 | 1 | 2021 | 11 | 29 | 8 |
8 | 00158d0005bb96f3 | 1638142526000 | 2021-11-29 08:35:26 | a2_sen_dor_01 | 16e525f4 | reported | 0 | 1 | 0 | 2021 | 11 | 29 | 8 |
9 | 00158d0005bb96f3 | 1637913816000 | 2021-11-26 17:03:36 | a2_sen_dor_01 | 7c29c17d | reported | 0 | 1 | 1 | 2021 | 11 | 26 | 17 |
df = wr.athena.read_sql_query(
sql="SELECT * FROM illumination where device_id = '00158d0005a9998b' order by timestamp desc limit 10 /*연구소조도센서*/;",
database="ambt_b2c"
)
df
device_id | sk | timestamp | model | id | e_type | battery | status | evt | year | month | day | hour | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|
0 | 00158d0005a9998b | 1638246272000 | 2021-11-30 13:24:32 | a2_sen_cds_01 | 93f9369c | reported | 0 | 1 | 9 | 2021 | 11 | 30 | 13 |
1 | 00158d0005a9998b | 1638244831000 | 2021-11-30 13:00:31 | a2_sen_cds_01 | 601c4eea | reported | 0 | 1 | 10 | 2021 | 11 | 30 | 13 |
2 | 00158d0005a9998b | 1638244822000 | 2021-11-30 13:00:22 | a2_sen_cds_01 | 7abd863e | reported | 0 | 1 | 9 | 2021 | 11 | 30 | 13 |
3 | 00158d0005a9998b | 1638244791000 | 2021-11-30 12:59:51 | a2_sen_cds_01 | ae4e0806 | reported | 0 | 1 | 4 | 2021 | 11 | 30 | 12 |
4 | 00158d0005a9998b | 1638244781000 | 2021-11-30 12:59:41 | a2_sen_cds_01 | 129de4ae | reported | 0 | 1 | 5 | 2021 | 11 | 30 | 12 |
5 | 00158d0005a9998b | 1638244499000 | 2021-11-30 12:54:59 | a2_sen_cds_01 | cc8ff83e | reported | 0 | 1 | 4 | 2021 | 11 | 30 | 12 |
6 | 00158d0005a9998b | 1638244489000 | 2021-11-30 12:54:49 | a2_sen_cds_01 | e821a0ed | reported | 0 | 1 | 3 | 2021 | 11 | 30 | 12 |
7 | 00158d0005a9998b | 1638244479000 | 2021-11-30 12:54:39 | a2_sen_cds_01 | 147ee512 | reported | 0 | 1 | 4 | 2021 | 11 | 30 | 12 |
8 | 00158d0005a9998b | 1638244419000 | 2021-11-30 12:53:39 | a2_sen_cds_01 | b9431dfe | reported | 0 | 1 | 3 | 2021 | 11 | 30 | 12 |
9 | 00158d0005a9998b | 1638244358000 | 2021-11-30 12:52:38 | a2_sen_cds_01 | 7d9eb350 | reported | 0 | 1 | 4 | 2021 | 11 | 30 | 12 |
df = wr.athena.read_sql_query(
sql="SELECT * FROM motion_door where device_id = '00158d0002cab225' order by timestamp desc limit 10 /*연구소모션센서*/;",
database="ambt_b2c"
)
df
device_id | sk | timestamp | model | id | e_type | battery | status | evt | year | month | day | hour | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|
0 | 00158d0002cab225 | 1638248019000 | 2021-11-30 13:53:39 | a2_sen_mt_01 | 1040e7c6 | reported | 0 | 1 | 1 | 2021 | 11 | 30 | 13 |
1 | 00158d0002cab225 | 1638247475000 | 2021-11-30 13:44:35 | a2_sen_mt_01 | 4af86acf | reported | 0 | 1 | 1 | 2021 | 11 | 30 | 13 |
2 | 00158d0002cab225 | 1638246438000 | 2021-11-30 13:27:18 | a2_sen_mt_01 | 32b6aa68 | reported | 0 | 1 | 1 | 2021 | 11 | 30 | 13 |
3 | 00158d0002cab225 | 1638233537000 | 2021-11-30 09:52:17 | a2_sen_mt_01 | da53ea05 | reported | 0 | 1 | 1 | 2021 | 11 | 30 | 9 |
4 | 00158d0002cab225 | 1638231066000 | 2021-11-30 09:11:06 | a2_sen_mt_01 | 472686d4 | reported | 0 | 1 | 1 | 2021 | 11 | 30 | 9 |
5 | 00158d0002cab225 | 1638230419000 | 2021-11-30 09:00:19 | a2_sen_mt_01 | 9018cbfd | reported | 0 | 1 | 1 | 2021 | 11 | 30 | 9 |
6 | 00158d0002cab225 | 1638227099000 | 2021-11-30 08:04:59 | a2_sen_mt_01 | 9b7710a8 | reported | 0 | 1 | 1 | 2021 | 11 | 30 | 8 |
7 | 00158d0002cab225 | 1638217727000 | 2021-11-30 05:28:47 | a2_sen_mt_01 | 4805e5b3 | reported | 0 | 1 | 1 | 2021 | 11 | 30 | 5 |
8 | 00158d0002cab225 | 1638207705000 | 2021-11-30 02:41:45 | a2_sen_mt_01 | de5f7e04 | reported | 0 | 1 | 1 | 2021 | 11 | 30 | 2 |
9 | 00158d0002cab225 | 1638177874000 | 2021-11-29 18:24:34 | a2_sen_mt_01 | fa93d91d | reported | 0 | 1 | 1 | 2021 | 11 | 29 | 18 |
df = wr.athena.read_sql_query(
sql="SELECT * FROM motion_door where device_id = '00158d0005bb968a' order by timestamp desc limit 10 /*연구소문열림센서*/;",
database="ambt_b2c"
)
df
device_id | sk | timestamp | model | id | e_type | battery | status | evt | year | month | day | hour | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|
0 | 00158d0005bb968a | 1638247519000 | 2021-11-30 13:45:19 | a2_sen_dor_01 | c4f0d490 | reported | 0 | 1 | 0 | 2021 | 11 | 30 | 13 |
1 | 00158d0005bb968a | 1638247514000 | 2021-11-30 13:45:14 | a2_sen_dor_01 | 24500391 | reported | 0 | 1 | 1 | 2021 | 11 | 30 | 13 |
2 | 00158d0005bb968a | 1638247483000 | 2021-11-30 13:44:43 | a2_sen_dor_01 | a1f435ab | reported | 0 | 1 | 0 | 2021 | 11 | 30 | 13 |
3 | 00158d0005bb968a | 1638247479000 | 2021-11-30 13:44:39 | a2_sen_dor_01 | 26db99c3 | reported | 0 | 1 | 1 | 2021 | 11 | 30 | 13 |
4 | 00158d0005bb968a | 1638246665000 | 2021-11-30 13:31:05 | a2_sen_dor_01 | df9ef5b4 | reported | 0 | 1 | 0 | 2021 | 11 | 30 | 13 |
5 | 00158d0005bb968a | 1638246662000 | 2021-11-30 13:31:02 | a2_sen_dor_01 | 2fa18fc4 | reported | 0 | 1 | 1 | 2021 | 11 | 30 | 13 |
6 | 00158d0005bb968a | 1638246449000 | 2021-11-30 13:27:29 | a2_sen_dor_01 | c8ad680b | reported | 0 | 1 | 0 | 2021 | 11 | 30 | 13 |
7 | 00158d0005bb968a | 1638246447000 | 2021-11-30 13:27:27 | a2_sen_dor_01 | 92f56584 | reported | 0 | 1 | 1 | 2021 | 11 | 30 | 13 |
8 | 00158d0005bb968a | 1638246130000 | 2021-11-30 13:22:10 | a2_sen_dor_01 | ee0271a4 | reported | 0 | 1 | 0 | 2021 | 11 | 30 | 13 |
9 | 00158d0005bb968a | 1638246126000 | 2021-11-30 13:22:06 | a2_sen_dor_01 | 4aaeaed6 | reported | 0 | 1 | 1 | 2021 | 11 | 30 | 13 |
df = wr.athena.read_sql_query(
sql="SELECT * FROM temperature_humidity where device_id = '00158d0006c9d545' order by timestamp desc limit 10 /*연구소온습도센서*/;",
database="ambt_b2c"
)
df
device_id | sk | timestamp | model | id | e_type | hum | temp | battery | status | year | month | day | hour | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
0 | 00158d00049b8ff3 | 1637558253000 | 2021-11-22 14:17:33 | a2_sen_th_01 | 683fa131 | reported | 29 | 226 | 0 | 1 | 2021 | 11 | 22 | 14 |
1 | 00158d00049b8ff3 | 1637557952000 | 2021-11-22 14:12:32 | a2_sen_th_01 | 2ac12530 | reported | 29 | 228 | 0 | 1 | 2021 | 11 | 22 | 14 |
2 | 00158d00049b8ff3 | 1637557952000 | 2021-11-22 14:12:32 | a2_sen_th_01 | 42741e86 | reported | 30 | 228 | 0 | 1 | 2021 | 11 | 22 | 14 |
3 | 00158d00049b8ff3 | 1637557350000 | 2021-11-22 14:02:30 | a2_sen_th_01 | 24c99ff6 | reported | 30 | 229 | 0 | 1 | 2021 | 11 | 22 | 14 |
4 | 00158d00049b8ff3 | 1637556749000 | 2021-11-22 13:52:29 | a2_sen_th_01 | 413bb2a0 | reported | 30 | 228 | 0 | 1 | 2021 | 11 | 22 | 13 |
5 | 00158d00049b8ff3 | 1637556147000 | 2021-11-22 13:42:27 | a2_sen_th_01 | 6ea69ba3 | reported | 29 | 228 | 0 | 1 | 2021 | 11 | 22 | 13 |
6 | 00158d00049b8ff3 | 1637555846000 | 2021-11-22 13:37:26 | a2_sen_th_01 | 3f9e8043 | reported | 29 | 227 | 0 | 1 | 2021 | 11 | 22 | 13 |
7 | 00158d00049b8ff3 | 1637554943000 | 2021-11-22 13:22:23 | a2_sen_th_01 | bb276dda | reported | 29 | 226 | 0 | 1 | 2021 | 11 | 22 | 13 |
8 | 00158d00049b8ff3 | 1637554642000 | 2021-11-22 13:17:22 | a2_sen_th_01 | 14445641 | reported | 29 | 225 | 0 | 1 | 2021 | 11 | 22 | 13 |
9 | 00158d00049b8ff3 | 1637553739000 | 2021-11-22 13:02:19 | a2_sen_th_01 | 5ccc4310 | reported | 28 | 225 | 0 | 1 | 2021 | 11 | 22 | 13 |
import datetime
datetimeobj = datetime.datetime.fromtimestamp(1638227325000/1000)
datetimeobj
datetime.datetime(2021, 11, 30, 8, 8, 45)
from datetime import datetime
datetime.now().hour
if datetime.now().weekday() < 5 :
if datetime.now().hour > 8 and datetime.now().hour < 17 :
print ('working time')
a = datetime(datetime.now().year, datetime.now().month, datetime.now().day, datetime.now().hour)
b = datetime(int(df['year'][0]), int(df['month'][0]), int(df['day'][0]), int(df['hour'][0]))
print('{}'.format(b))
2021-11-25 13:00:00
if a!=b:
print('not same')
not same
df.head(1)['hour'][0]
'13'
df['year'][0]
'2021'
a = datetime(datetime.now().year, datetime.now().month, datetime.now().day, datetime.now().hour)
b = datetime(int(df['year'][0]), int(df['month'][0]), int(df['day'][0]), int(df['hour'][0]))
if a!=b:
print('same')
import awswrangler as wr
df = wr.s3.read_parquet("s3://hdci-prototyping/motion_door/", dataset=True)
df
device_id | sk | timestamp | model | id | e_type | battery | status | evt | year | month | day | hour | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|
0 | 00158d0005bb968a | 1633822004000 | 2021-10-10 08:26:44 | a2_sen_dor_01 | b087f0a8 | reported | 0 | 1 | 0 | 2021 | 10 | 10 | 8 |
1 | 00158d0002cab225 | 1634000451000 | 2021-10-12 10:00:51 | a2_sen_mt_01 | c6a1edc2 | reported | 0 | 1 | 0 | 2021 | 10 | 12 | 10 |
2 | 00158d0002cab225 | 1634000825000 | 2021-10-12 10:07:05 | a2_sen_mt_01 | 7b442366 | reported | 0 | 1 | 1 | 2021 | 10 | 12 | 10 |
3 | 00158d0002cab225 | 1634000886000 | 2021-10-12 10:08:06 | a2_sen_mt_01 | ab3c5929 | reported | 0 | 1 | 0 | 2021 | 10 | 12 | 10 |
4 | 00158d0002cab225 | 1634003953000 | 2021-10-12 10:59:13 | a2_sen_mt_01 | 0cdda406 | reported | 0 | 1 | 1 | 2021 | 10 | 12 | 10 |
... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
8269 | 00158d0006c9d665 | 1631145728000 | 2021-09-09 09:02:08 | a2_sen_dor_01 | 2a101ef0 | reported | 6 | 1 | 0 | 2021 | 9 | 9 | 9 |
8270 | 00158d0005bb968a | 1631148819000 | 2021-09-09 09:53:39 | a2_sen_dor_01 | 2838e8cc | reported | 0 | 1 | 1 | 2021 | 9 | 9 | 9 |
8271 | 00158d0005bb96f3 | 1631148863000 | 2021-09-09 09:54:23 | a2_sen_dor_01 | 7af0ae63 | reported | 0 | 1 | 1 | 2021 | 9 | 9 | 9 |
8272 | 00158d0006c9d665 | 1631148869000 | 2021-09-09 09:54:29 | a2_sen_dor_01 | 3d8375e2 | reported | 6 | 1 | 0 | 2021 | 9 | 9 | 9 |
8273 | 00158d0002d545b4 | 1631149065000 | 2021-09-09 09:57:45 | a2_sen_mt_01 | 99fb428f | reported | 0 | 1 | 1 | 2021 | 9 | 9 | 9 |
8274 rows × 13 columns