åããŸããŠãããã«ã¡ã¯ã ã»ãŒãã£ãŒæ ªåŒäŒç€Ÿ ãã©ãããã©ãŒã éçºéšã®å€§åã§ãã ãµãŒããŒãµã€ãåšãã®éçºãæ
åœããŠããŸãã ä»åã¯ãŠãŒã¶ãŒããåãåãããåããã·ã¹ãã å
ã調ã¹ãªããã°ãªããªããšãããµãŒããŒãµã€ãã¯ã©ã®ããã«å¯Ÿå¿ããŠããã®ãïŒãã®äžäŸã玹ä»ããããšæããŸãã ããŒãã¯ãã°è§£æãçšããåãåãã察å¿ã§ãã Amazon Athena ãš ãåãåãã Athenaã®æ³šæç¹ ã»ãŒãã£ãŒã®æ§æ ãããã« Amazon Athena ãš ãåãåãã ä»åã®äž»åœ¹ãšãªããµãŒãã¹ã§ãã ã»ãŒãã£ãŒã§ã¯ãã°è§£æã®äžã€ã« Amazon Athena ã䜿çšããŠããŸãã Athenaã«ã€ããŠç°¡åã«èª¬æããŸããšãS3 ã«ä¿åãããã°ããŒã¿ã«å¯Ÿã㊠SQL ãå®è¡ãããã°ã®è§£æãè¡ãããšãããµãŒãã¹ã«ãªããŸããè§£æããããã°ã®åœ¢åŒã«åããã Athenaã®ããŒãã«ãäœæããŠãããããŒãã«ã«å¯Ÿã㊠SQL ãçºè¡ããããšã§çµæãåŸããããšãåºæ¥ãŸãã ã»ãŒãã£ãŒã§ã¯ããµãŒããŒãã°ã S3 ã«ä¿ç®¡ããŠããã®ã§ãAthenaã¯æã£ãŠã€ãã§ããã äŸãã°ã Safie Viewer ãæäœããŠã«ã¡ã©æ åã®é²èЧã§ãã£ãããã«ã¡ã©èšå®ã倿Žããããããšãè£åŽã§ã¯RESTful APIãå®è¡ãããéœåºŠä»¥äžã®ããã«JSON圢åŒã§å®è¡ãã°ãåºåãããŠããŸãã (éèŠãªã»ãã¥ãªãã£æ
å ±ãå«ãã APIãã©ã¡ãŒã¿ãŒã¯ãã¹ãã³ã°ãæœããŠãããã°åºåããŠããŸãã ãã¹ãã³ã° ãããããŒã¿ã¯ã»ãŒãã£ãŒã§ã確èªã§ããŸããããããŠã以äžã®ãã°ã¯å®å
šãªãããŒã§ãã) {"timestamp": "2020-08-14T11:22:33+09:00", "camera_id": "camera1", "api": "/image", "method": "GET", "parameter": null, "user": "user1", "ua": "chrome"} {"timestamp": "2020-08-14T11:22:34+09:00", "camera_id": "camera2", "api": "/setting", "method": "POST" , "parameter": "name=hoge", "user": "share-user", "ua": "chrome"} {"timestamp": "2020-08-14T11:22:35+09:00", "camera_id": "camera3", "api": "/video", "method": "GET", "parameter": null, "user": "user2", "ua": "chrome"} (äžç¥) {"timestamp": "2020-08-16T20:10:44+09:00", "camera_id": "camera1", "api": "/image", "method": "GET", "parameter": null, "user": "user1", "ua": "chrome"} {"timestamp": "2020-08-16T20:10:45+09:00", "camera_id": "camera2", "api": "/setting", "method": "POST" , "parameter": "name=camera2", "user": "owner-user", "ua": "chrome"} {"timestamp": "2020-08-16T20:10:46+09:00", "camera_id": "camera3", "api": "/video", "method": "GET", "parameter": null, "user": "user2", "ua": "chrome"} camera_idãšããã®ã¯ããŠãŒã¶ãŒãææããŠããã«ã¡ã©ã®åºæIDã«ãªããŸãã ãããæ°ã¥ãããcamera2ã®ååãå€ãã£ãŠããã®ã§å
ã«æ»ããŸããã7æã¯ååã¯å€ãã£ãŠãªãã£ãã§ãããã€ååãå€ãã£ãã®ã調ã¹ãŠæ¬²ãããããšããåãåãããçºçãããAthenaã§ä»¥äžã®SQLãå®è¡ããŸãã SELECT * FROM safie.test_log WHERE dt >= '2020-08-01' AND camera_id='camera2' ãããšãcamera2ã«å¯ŸããŠã®æäœãã°ãååŸã§ããŸãã (dt >= '2020-08-01'㯠2020-08-01 以éã«åºåããããã°ããæ€çŽ¢ãããšããæå³ã«ãªããŸã) æ€çŽ¢ããçµæä»¥äžã®ãã°ãåŒã£æãããŸããã {"timestamp": "2020-08-14T11:22:34+09:00", "camera_id": "camera2", "api": "/setting", "method": "POST" , "parameter": "name=hoge", "user": "share-user", "ua": "chrome"} {"timestamp": "2020-08-16T20:10:45+09:00", "camera_id": "camera2", "api": "/setting", "method": "POST" , "parameter": "name=camera2", "user": "owner-user", "ua": "chrome"} ( ããã§ã/setting ã¯ã«ã¡ã©ã®ååã倿Žã§ããAPIã ãšããŸãã) ååãäºå倿ŽãããŠããŸããããã°ãäžããèŠãŠãããŸãããã ååãå
ã«æ»ãããšã®äºãªã®ã§ãäºä»¶ç®ã® 2020-08-16 20:10:45 ã®æéã«èšé²ãããŠãããã°ã¯ã«ã¡ã©ã®ååãå
ã«æ»ãããã«æäœããå±¥æŽã ãšåãããŸãã倿ŽãããŠãŒã¶ãŒã¯ owner-user ããã§ãã æ¬¡ã«äžä»¶ç®ã® 2020-08-14 11:22:34 ã®æé垯ã«ããã«ã¡ã©åã倿ŽãããŠããŸãã ååã倿Žããã®ã¯ share-user ããã§ãã ã»ãŒãã£ãŒã®ã«ã¡ã©ã¯ ã·ã§ã¢ ããããšãã§ãããããä»åã®åãåããã§ã¯ã«ã¡ã©ãã·ã§ã¢ãããshare-userãããã«ã¡ã©åã倿ŽããããšãåãããŸãã (ã«ã¡ã©ãã·ã§ã¢ããéã«ã¯ãã·ã§ã¢ã®æš©éãèšå®ã§ããŸã)ã ã»ãŒãã£ãŒã§ã®Athenaã䜿ã£ãè§£æã¯ãããŸãã«ãã®ãããªæµãã«ãªããŸãã (å®éçšã§ã¯æ¯å SQL ãæžããŠå®è¡ããã®ã¯é¢åãªã®ã§ã調ã¹ããç®æã ããå
¥åããã æ€çŽ¢ããå¥ToolããããSQLãçŽæ¥æžãããšã¯æ»
å€ã«ãããŸããã) äžèšäŸã§ã¯ãã°ã®ãµã³ãã«ãå°ãªããã䟿å©ãã«çåç¬Šãæ®ããããããŸããã ã»ãŒãã£ãŒã§ã¯Athenaè§£æã«äœ¿ãããšã«ãªããã°ã¯ãäžæ¥èŸºãå§çž®ããŠå¹³å2GBã»ã©æºãŸããŸãããããªãã°ã®å¡ãAthenaã§äžãæåè§£æãè¡ã£ããšããŠããè§£ææéã¯1åçšåºŠãæãããŸããããäžãæåã®ãã°ããã調æ»ã®ããã®ææçŸ©ãªæ
å ±ãåŸããããšèããã°ãAthenaã®åŒ·åããäŒããã®ã§ã¯ãªãã§ããããã Athenaã®æ³šæç¹ Athenaãæ²¢å±±è€ããŠããŸããŸãããã䜿ãäžã§æ°ãä»ããããšããããŸãã ããã¯ã³ã¹ãã§ãã Athenaã¯äœ¿ãã°äœ¿ãã»ã©ãéãåãããåŸé課é ãªã®ã§ããã¡ããš ããŒãã£ã·ã§ã³ ãèšå®ããããŒãã£ã·ã§ã³ã«åãããŠãã°ãä¿åããå¿
èŠããããŸãã ããŒãã£ã·ã§ã³ã®èšå®ãç¡ããšãAthenaã§ã®æ€çŽ¢ã¯éœåºŠå
šãŠã®ãã°ããŒã¿ã«å¯ŸããŠè¡ããããã åžžã«å
šæ€çŽ¢ ãšãªããŸããããã§ã¯ã³ã¹ãã嵩ãã§ããŸããŸãã ããŒãã£ã·ã§ã³ãèšå®ãããšããã€ããïœãã€ãŸã§ ãšæ€çŽ¢ããç¯å²ãçµã蟌ãããšãã§ããŸããçµã蟌ãããšã§çºçããã³ã¹ãã¯æå°éã«æããããŸãã æç« ã ãã ãšäžã
ã€ã¡ãŒãžãæ¹§ããŸããããããŒãã£ã·ã§ã³ç¡ãšæã®æ¯èŒå³ãçšæããŸããã ããŒãã£ã·ã§ã³èšå®ã®æç¡ã§S3ã«ä¿åãããã¹ãè¥å¹²ç°ãªã£ãŠããããšãåãããŸããèšå®æã«ããã dt=2020-08-01 㯠Apache Hive 圢åŒãšåŒã°ããããŒãã£ã·ã§ã³ãå©çšããæã¯ããã®HiveãçšããŠããŒã¿ãä¿ç®¡ããŠããå¿
èŠããããŸããHiveã®dtã¯SQLã®æ¡ä»¶åŒã«æžãããšãã§ããã®ã§ã dt>='2020-08-03â AND dt <= â2020-08-04â ãšãããšã8/3ãš8/4åã®ãã°ã®ã¿ã«çµã£ãŠãè§£æããããšãå¯èœã«ãªãããã§ãã æ¬¡ã®ç« ã§ã¯ãã»ãŒãã£ãŒãã©ã®ããã«ãã°ãS3ãŸã§éãã§ããã®ãã玹ä»ããããšæããŸãã ã»ãŒãã£ãŒã®æ§æ ã»ãŒãã£ãŒã®ãµãŒããŒã¯å
šãŠAWSäžã«æ§ç¯ãããŠããŠåªã«äžæ¡ã®æ°ã®EC2ã€ã³ã¹ã¿ã³ã¹ãç«ã¡äžãã£ãŠããŸãã ãµãŒããŒã«ã沢山ã®çš®é¡ããããã«ã¡ã©ã®å¶åŸ¡ãæ åãæ±ãCamãµãŒã㌠APIãæäŸããAPIãµãŒããŒãåç»ã®é
ä¿¡ãåžãé
ä¿¡ãµãŒããŒçãååšããŸãã (ãŸã ãŸã ãµãŒããŒã¯ååšããŸãããããã§ã¯å²æããŸã) ãã°ãæ±ãã®ã¯ã logãµãŒããŒã«ãªããŸãã 以äžã¯æ§æå³ã§ãã æ§æå³ãèŠããšç¹ã«è€éãªããšã¯ããŠããŸãããã logãµãŒããŒã¯å
šãµãŒããŒãã°ã®Aggregatorã§ãããã°ãåéããS3ã®ä¿åããElastic searchãžã®è»¢éãæ
ã£ãŠããŸããlogãµãŒããŒãåéããŠãããã°ã¯ãAthenaã§åæã§ãããã°ã®ã»ãã«ããåãµãŒããŒã§åäœããŠããå°çšã¢ããªã±ãŒã·ã§ã³ã®ãã°ã«ç£èŠãã°ãªã©ã皌åãã°å
šè¬ã«æž¡ããŸãããããŠããã°åéã«ã¯fluentdãå©çšããŠããŸãã(äžéš fluent bitãååšããŸã) Athenaã®ãã°ã¯ããŒãã£ã·ã§ã³ãåã圢(s3://athena/logs/dt=2020-08-14/app.log.tar.gz.1)ã§ä¿åãããŠãããŸããæ°ããããŒãã£ã·ã§ã³ãå¢ããã(dt=2020-08-15)ãAthenaããŒãã«ã®ããŒãã£ã·ã§ã³æ
å ±ãæŽæ°ããå¿
èŠããããããã»ãŒãã£ãŒã§ã¯CIã®å®æå®è¡ã«ãŠããã è¡ã£ãŠããŸãã(ã³ãã³ã㯠MSCK REPAIR TABLE ã§ã) ãããŸã§ãè¡ããã»ãŒãã£ãŒã§ã¯æ¥é Athenaãåãåããã«æŽ»çšããŠããŸãã ããŒãã£ã·ã§ã³æŽæ°ã¯CIã§ãªããŠããAWS lambda ããæè¿ã§ããš AWS Glueãå©çšããæ¹æ³ããããŸããããããã³ã¹ããçºçããŸãããAWS Glueãè¯ãããã§ãã ãããã« AthenaãæŽ»çšããåãåãã察å¿ã«ã€ããŠã¯ä»¥äžãšãªããŸãã ä»åã¯AthenaãçšããŠAPIã®å®è¡ãã°ãé¡ã£ãŠæ€çŽ¢ããã«ã¡ã©ã«å¯ŸããŠã®æäœå±¥æŽã確èªããããšãåºæ¥ãŸããããã€ã©ããªã£ãç³»ã®åãåããã«ã¯ç¡é¡ã®åŒ·ããçºæ®ããAthenaã§ãããã«ã¡ã©æ åãèµ·å ããåãåãã(æ åãæããããæããããç)ã«ã¯æŽ»çšå Žé¢ãããŸãç¡ãããããŸã§è§£æ±ºããããã®äžçš®ã®ããŒã«ãšããŠå©çšããŠããŸãã(ã«ã¡ã©æ åã«èµ·å ããåãåããã¯ããµããŒãããŒã ã«æ åãã·ã§ã¢ããŠé ããå®éã«æ åãæèŠããäžã§å¯ŸåŠæ¹æ³ãæ¡å
ããŠããŸãã) Athenaã䟿å©ã§ãšãŠã䜿ããããšãåãããšãä»ã§ã¯Athenaã§æ€çŽ¢ããåæã§éçºãè¡ãå Žé¢ãããããŸã ãŸã 瀟å
ã§ã掻èºã®å Žã¯åºããããã§ãã ãããŠæåŸã«ãã»ãŒãã£ãŒã¯åžžã«ããè¯ããµãŒãã¹ã«ããã¹ããæ¥ã
é²åããŠããŸããã»ãŒãã£ãŒãæŽã«è¯ããµãŒãã¹ã«ããããèªåã®ã¹ãã«ã§èª²é¡ã解決ãããããããªæèããæã¡ã®æ¹ããåäžå¿ãé«ãæ¹ãæè¿ããŠããŸãããèå³ã®ããæ¹ã®ãé£çµ¡ããåŸ
ã¡ããŠãŸãã ãããŸã§ãç®éãããã ãæé£ãããããŸããã open.talentio.com