2024-01-22

ブリスウェルのAI技術・サービス紹介〜画像分類・物体検出・表情認識・生成AIなど

AI DX 強化学習事業概要

ブリスウェルでは、AIを活用して様々なサービスを提供しています。 AIのみでも利用できますし、業務システムやモバイルアプリなどと連携することも可能です。

それでは紹介していきましょう。

2023-12-20

ローカル環境で画面にQuickSightを使用するダッシュボードを埋め込んでみる。

どうもこんにちは。ブリスウェルのSonです。
最近、ウェブサイトでダッシュボードを埋め込むために、QuickSightをちょっと調査してました。忘れないように、基本的な手順をメモしておきます。
この記事は技術的な内容なので、QuickSightサービスの料金に関する問題を無視します。

言語: PHP (v 7.4)
必要なライブラリ: AWS SDK for PHP (v 3.x)

準備：CSV内容

tenant	city	itemtype	price
tenant1	city1	item1	100
tenant1	city1	item2	200
tenant1	city2	item1	400
tenant1	city2	item2	500
tenant1	city3	item1	700
tenant1	city3	item2	800
tenant2	city1	item1	100
tenant2	city1	item2	200
tenant2	city2	item1	400
tenant2	city2	item2	500
tenant2	city3	item1	700
tenant2	city3	item2	800

依頼内容は
・ダッシュボードを画面に埋めこむ
・特定のユーザーはtenant=tenant2の行のみ閲覧できるように

① datasetについて
上のCSVをインポートして、データセットを作成します。
データセットの作成をしたら、RLS モードを設定する必要があります (行レベルのセキュリティ)
特定のユーザーのみを表示するので、列を「tanent」に設定する必要があり、タグ列を「hogetalent」のような値を設定できます。

データセットからダッシュボードの作成してみます。ダッシュボードを作成したら、こんな状態になります。 RLSを設定しましたので、自分のアカウントでも細かいダッシュボードが見えないです。

② IAMのroleの設定について
ダッシュボードを表示するには、SDKにある「GenerateEmbedUrlForAnonymousUser」を実装する必要があります。
ただし、「GenerateEmbedUrlForAnonymousUser」が実行できるために、次のようなユーザーにロールを与える必要があります。

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "VisualEditor0",
            "Effect": "Allow",
            "Action": [
                "quicksight:GenerateEmbedUrlForAnonymousUser"
            ],
            "Resource": [
                "arn:aws:quicksight:{region}:{account_id}:dashboard/*"
            ],
            "Condition": {
                "ForAllValues:StringEquals": {
                    "quicksight:AllowedEmbeddingDomains": [
                        "http://localhost"
                    ]
                }
            }
        },
 ]
}

注意: 該当する「AllowedDomains」の値を変更する必要があります。
例: https://abc.com

③ Coding
まずは下記のようなコマンドでAWS SDK for PHP (v 3.x)導入するする必要があります。

composer require aws/aws-sdk-php

以下のように必要な情報を指定する必要があります。

define('AWS_REGION', 'XXXXXXXX');
define('AWS_ACCESS_KEY', 'XXXXXXXX');
define('AWS_SECRET_ACCESS_KEY', 'XXXXXXXX');
define('AWS_ACCOUNT_ID', 'XXXXXXXX');

次に、次の内容のphpクラスを作成します。

<?php
require __DIR__. '/vendor/autoload.php';

use Aws\QuickSight\QuickSightClient;
use Aws\Exception\AwsException; 

class QuickSight {
     $this->credentials_ = [
           'version' => 'latest',
           'region' => AWS_REGION,
           'credentials' => [
               'key'    => AWS_ACCESS_KEY,
               'secret' => AWS_SECRET_ACCESS_KEY
           ]
    ];
}

次にgetEmbedUrlの関数を作成します。「generateEmbedUrlForAnonymousUser」の関数を実行するために、$dashboardIdのパラメーターを渡す必要があります。

<?php

public function getEmbedUrl($dashboardId) {
   $params = [
      'AllowedDomains' => [
         'http://localhost'
      ],
      'AwsAccountId' => AWS_ACCOUNT_ID,
      'Namespace' => 'default',
      'SessionLifetimeInMinutes' => 600,
      'AuthorizedResourceArns' => 'arn:aws:quicksight:${AWS_REGION}:{AWS_ACCOUNT_ID}:dashboard/$dashboardId',
      'ExperienceConfiguration' => [
         'Dashboard' => [
            'InitialDashboardId'=> $dashboardId
         ]
      ],
      'SessionTags' => [
         [
            'Key' => 'hogetenant',
            'Value' => 'tenant2',
         ]
      ]
   ];

    try {
      $client = new QuickSightClient($this->credentials_);
      $result = $client->generateEmbedUrlForAnonymousUser($params);
      $embedUrl = $result['EmbedUrl'];
      
      return $embedUrl;
    } catch(Exception $e){
      print $e->getMessage();
    }
}

tenant=tenant2の行のみを表示するので、hogetenantのタグ値をtenant2に指定する必要があります。
注意: 該当する「AllowedDomains」の値を変更する必要があります。
例: https://abc.com

ウェブに表示するダッシュボードの埋め込みリンクを取得したい場合は、次の手順を実行します。

<?php
$quickSight = new QuickSight();
$embedUrl = $quickSight->getEmbedUrl($dashboardId);

実行が成功した場合の $embedUrl の値は次のようになります。
https://ap-northeast-1.quicksight.aws.amazon.com/embed/xxxxxx&identityprovider=quicksight&isauthcode=true

次のようにiframeに $embedUrlを挿入する必要があります。

<!DOCTYPE html>
<html lang="en">
<head>
  <meta charset="UTF-8">
  <meta http-equiv="X-UA-Compatible" content="IE=edge">
  <meta name="viewport" content="width=device-width, initial-scale=1.0">
  <title>Quicksight Demo</title>
</head>
<body>
  <iframe src="<?php echo $embedUrl ?>"></iframe>
</body>
</html>

結果が来ました。

ローカル環境でダッシュボードが正常に表示されました。
もし何か問題がございましたら、お手数ですがコメント欄にご記入いただければ幸いです。

2023-11-25

スポーツ動画のタグ付け

AI スポーツ 🥒

そろそろ年末が近づいてきました。気持ちの良い秋晴れ（もう冬ですかね）の空が広がっています。先日、数年ぶりに体育館で運動をしました。普段PCと向き合ってガチガチの身体がほぐれて良かったです。

今回は久しぶりにAI関連の記事です。CLIPモデルを利用して動画を解析してみます。

CLIPはOpenAIによって開発されたモデルで、画像とその説明（テキスト）の関係を検出します。このモデルは、インターネットから集めた大量の画像とテキストのペアで学習しています。特定のタスク用に追加の学習を必要とせず、多様なシーンで精度を出せるのが魅力ですね。

体育館での運動の合間の一コマです。謎の動きをしていますが、はたしてCLIPモデルを何をしているか理解できるでしょうか。

1. 検出する動き（テキスト）を日本語・英語で定義

{
  "投げる": "throw",
  "歩く": "walk",
  "走る": "run",
  "飛ぶ": "jump",
  "泳ぐ": "swim",
  "踊る": "dance",
  "歌う": "sing",
  "座る": "sit",
  "描く": "draw",
  "寝る": "sleep"
}

2. テキストの特徴量をpickleファイルへ保存

import torch
import clip
import pickle
import json

# CLIPモデルの初期化
device = "cuda" if torch.cuda.is_available() else "cpu"
model, transform = clip.load("ViT-B/32", device=device)

# 事前に準備した日本語と英語の辞書
with open('japanese_to_english_dict.json', 'r', encoding='utf-8') as file:
    japanese_to_english_dict = json.load(file)

# 英語に翻訳されたタグをCLIPモデル用にトークナイズ
translated_tags = list(japanese_to_english_dict.values())
text = clip.tokenize(translated_tags).to(device)

# テキストの特徴量を計算
with torch.no_grad():
    text_features = model.encode_text(text)

# pickleファイルとして保存
with open('text_features.pkl', 'wb') as f:
    pickle.dump(text_features, f)

print("テキスト特徴量を保存しました。")

3. 動画を読み込んで各フレームにタグ付け

import cv2
import torch
import clip
import pickle
from PIL import Image
from collections import Counter
import json
import os
import glob

# 指定されたフォルダ内の画像を削除する関数
def delete_images_in_folder(folder, file_extension="*.jpg"):
    files = glob.glob(os.path.join(folder, file_extension))
    for f in files:
        os.remove(f)

# フレームにテキストを描画する関数
def draw_text_on_frame(frame, text, position, font=cv2.FONT_HERSHEY_SIMPLEX, 
                       font_scale=0.7, font_color=(0, 255, 0), line_type=2):
    cv2.putText(frame, text, position, font, font_scale, font_color, line_type)

# バッチごとにフレームを処理する関数
def process_batch(frame_batch, start_frame_index, model, transform, text_features, 
                  japanese_tags, japanese_to_english_dict, all_tags_for_video, 
                  output_folder, fps):
    # バッチ内の各フレームをRGBに変換
    batch_rgb = [cv2.cvtColor(frame, cv2.COLOR_BGR2RGB) for frame in frame_batch]

    # 変換されたフレームをPyTorchテンソルに変換
    batch_transformed = torch.stack([transform(Image.fromarray(img)) for img in batch_rgb]).to(device)

    # CLIPモデルを使用して画像の特徴量を抽出
    with torch.no_grad():
        image_features = model.encode_image(batch_transformed)
        logits_per_image = (image_features @ text_features.T)
        probs = logits_per_image.softmax(dim=1)
        top_tag_indices_list = probs.topk(N).indices

    # 各フレームごとに最も関連性の高いタグを選択
    for i, top_tag_indices in enumerate(top_tag_indices_list):
        valid_indices = [idx for idx in top_tag_indices if probs[i][idx] > SIMILARITY_THRESHOLD]
        top_tags_for_frame = [(japanese_tags[idx], probs[i][idx].item()) for idx in valid_indices]

        # 日本語タグを英語に変換
        top_tags_for_frame_english = [(japanese_to_english_dict[tag], score) for tag, score in top_tags_for_frame]

        # 現在のフレームのインデックスと時間を計算
        current_frame_index = start_frame_index + i
        current_frame_time = current_frame_index / fps

        # 処理中のフレームとそのタグをコンソールに出力
        print(f"Frame {current_frame_index} (Time: {current_frame_time:.2f} seconds): {top_tags_for_frame_english}")

        # フレームにタグを描画して保存
        for j, (eng_tag, score) in enumerate(top_tags_for_frame_english):
            text = f"{eng_tag}: {score:.2f}"
            draw_text_on_frame(frame_batch[i], text, (10, 30 + j*30))

        frame_filename = f"{output_folder}/frame_{current_frame_index}.jpg"
        cv2.imwrite(frame_filename, frame_batch[i])

        # 抽出されたタグを全タグのリストに追加
        for tag, _ in top_tags_for_frame:
            all_tags_for_video.append(tag)

# メインスクリプトの開始
device = "cuda" if torch.cuda.is_available() else "cpu"
model, transform = clip.load("ViT-B/32", device=device)

# pickleファイルからテキスト特徴量を読み込み
with open('text_features.pkl', 'rb') as f:
    text_features = pickle.load(f).to(device)

# 動画ファイルを読み込み
cap = cv2.VideoCapture('sports-movie.mp4')
fps = int(cap.get(cv2.CAP_PROP_FPS))

# 日本語と英語の辞書を読み込み
with open('japanese_to_english_dict.json', 'r', encoding='utf-8') as file:
    japanese_to_english_dict = json.load(file)

# 日本語のタグリストを作成
japanese_tags = list(japanese_to_english_dict.keys())

# 処理された全フレームのタグを保存するリストを初期化
all_tags_for_video = []

# バッチサイズ、上位N個のタグを選択するための数、類似度のしきい値を設定
BATCH_SIZE = 16
N = 3  # 上位N個のタグを選択
SIMILARITY_THRESHOLD = 0.2  # 類似度のしきい値

# バッチ処理用のフレームリストを初期化
frame_batch = []

# 出力されるフレームを保存するフォルダの設定
output_folder = 'output_frames'
if not os.path.exists(output_folder):
    os.makedirs(output_folder)  # フォルダが存在しない場合は作成
else:
    delete_images_in_folder(output_folder)  # フォルダが存在する場合は中の画像を全て削除

# 動画の各フレームを処理
frame_count = 0
while cap.isOpened():
    ret, frame = cap.read()  # フレームを読み込み
    if not ret:
        break  # フレームがない場合は終了

    frame_batch.append(frame)  # バッチリストにフレームを追加
    # バッチサイズに達したら処理を実行
    if len(frame_batch) == BATCH_SIZE:
        process_batch(frame_batch, frame_count - len(frame_batch) + 1, model, transform, 
                      text_features, japanese_tags, japanese_to_english_dict, 
                      all_tags_for_video, output_folder, fps)
        frame_batch = []  # 処理後はバッチリストをリセット
    frame_count += 1

# 残りのフレームを処理
if frame_batch:
    process_batch(frame_batch, frame_count - len(frame_batch) + 1, model, transform, 
                  text_features, japanese_tags, japanese_to_english_dict, 
                  all_tags_for_video, output_folder, fps)

cap.release()  # 動画の読み込みを終了

# タグの出現回数を集計し、ファイルに出力
tag_counts = Counter(all_tags_for_video)
with open('output_tags.txt', 'w', encoding='utf-8') as f:
    for tag, count in tag_counts.most_common():
        f.write(f"{tag}: {count}\n")  # タグとその出現回数をファイルに書き込み

print("動画の処理が完了しました。")

4. 実行結果と分析

タグとその出現回数は以下となります。

投げる: 832
踊る: 479
走る: 238

いいですね。多くは「投げる」と判断しています。

「踊る」「走る」はどのようなポイントで判断されているのが気になるところです。いくつかピックアップしてみます。

① 走る(run) 86%

まあ確かにこの画像だけを見ると走っているように見えますね。

② 投げる(throw) 50% & 走る(run) 41%

腕の部分は投げている雰囲気を出しています。

③ 投げる(throw) 75%

投げてます！

④ 踊る(dance) 74%

珍妙なダンスですが... 投げても走ってもいないですね。

5. 最後に

動画を読み込んで解析する場合、各フレームの静止画像に対して解析することになるので、上記のようにポイントでは誤った判断をすることがあります。そのため、全体を通してどのタグが一番多く検出されたのかを見ることで最終的な判断とすることがよさそうです。

2023-11-20

Webのプッシュ通知を実装してみました。。。

iOS がよやくWebプッシュ通知を対応したので、PWAのPush通知を実装して色々なフィーチャーを使ってみました。

「Webプッシュ通知」とは、通知を許可したユーザーにWebブラウザ経由でプッシュ通知(受信操作をしなくてもメッセージが自動表示される通知方式)を送信する機能です。ユーザーは「Webプッシュ通知」を許可するだけで受信できるようになります。なお「Webプッシュ通知」はPushAPIとNotificationAPIという2つの仕組みから成り立っています。

今回は vuejs のフロントエンドと nodejs バックエンドでWebプッシュ通知の機能を実装してみました。バックエンド側では web-push というパッケージをインストールする必要があります。

フロントエンドでプッシュ通知の許可をリクエストするボタンを配置する必要があります。なければ iOS でプッシュ通知の許可をリクエストできません。
フロントエンドで ServiceWorkerRegistration.pushManager.subscribe でサブスクリプションを登録します。登録結果はバックエンドのAPIを叩いて、保存します。
```
 serviceWorkerRegistration.pushManager.subscribe({
     userVisibleOnly: true,
     applicationServerKey,
 });
```

バックエンドで WebPush.sendNotification を叩いたらフロントのサービスワーカーへ通知送信できます。

 WebPush.sendNotification(
     subscription,
     JSON.stringify({
         title: body.title,
         options: body.payload,
     }),
     options,
 );

サービスワーカーで通知表示の処理を行います。（通知のボタンを押す時の処理もサービスワーカーで対応できます）

 self.addEventListener('push', (event) => {
     const data = event.data?.json();
     event.waitUntil(
         self.registration.showNotification(data.title, data.options),
     );
 });

Webプッシュ通知の表示は色んなオプションで調整できます。但し、 Windows/MacOS/iOS/Android では表示が異なるので、注意してください。仕様はここで参考してください： https://developer.mozilla.org/ja/docs/Web/API/ServiceWorkerRegistration/showNotification

badge：Androidのみ対応する

icon：iOSは未対応、MacOSではアプリのアイコンの代わり大きい画像として表示される
image：iOS/MacOSは未対応
actions：iOSは未対応、action.iconはWindowsのみ対応する

Windowsのwebプッシュ通知、icon + image + actions をセットしている

Androidのwebプッシュ通知、bade + icon + image + actionsをセットしている

皆さんもWebプッシュ通知を実装してみましたか？何かいい経験があればコメント欄で共有してくれると幸いです。では、今日の記事はここまでです。また後で。。

2023-10-18

【AI展示会に出展します】 2023.10.25（水）〜27（金）@幕張メッセ JAPAN IT WEEK 【ブース番号：52-46】

開催概要

名称	Japan IT Week
会期	2023/10/25(水) ~ 27(金)
開場時間	10:00 ～ 18:00
会場	幕張メッセ https://www.m-messe.co.jp/access/
ブースNo	52-46
招待状・入場券	会場に入場するためには、招待状・入場券が必要です招待状はこちら
ブリスウェルご相談枠の事前予約	当日は混雑が予想されますので、予め枠をご予約ください事前予約はこちら
公式サイト	https://www.japan-it.jp/autumn/ja-jp.html

出展サービス

AI画像解析	・お客様の活用シーンに応じたカスタマイズが可能・AI画像解析エンジンを用途に応じたアプリケーションに組み込み可能・少ない学習データからもモデル構築が可能
AIアナログメーター読み取り	・カメラ搭載のPC1台で撮影からデータ化まで対応可能・デジタルやアナログのメーター両方に対応可能・クラウドシステムと連携することで複数機器のデータ分析が可能
AWS導入コンサルティング	AWSテクノロジーパートナーとして、クラウド上でのシステム構築実績が多数あるため、様々なニーズに対応したAWS導入支援が可能です。
「アイカタ」受発注管理クラウドサービス	・シンプルで拡張性の高いクラウドサービス・クラウドサービスなのでリモートワークにも最適・shopifyやfreeeとの連携が可能 https://ai-cata.com/

お問い合わせ

株式会社ブリスウェル
TEL: 03-6450-4848
Mail: info@briswell.com

商談の事前予約

弊社営業担当者へご連絡頂くか、あるいは当ウェブサイトの問い合わせフォームよりご依頼ください。
お問い合わせ

2023-10-03

VPC内のLambdaからインターネット接続する方法

AI AWS 🥒

今回もさくっとAWS関連です。

AWSのVPC内でLambdaを動作させることは、RDSや他のプライベートリソースへの安全な接続に必要となります。

そのVPC内のLambdaからインターネット接続をするには、NAT Gatewayを利用することで実現できます。しかし、NAT Gatewayのコストが若干気になりますね。

NAT Gatewayの代替策を確認しました。

ENIにパブリックIPを付与

Lambda関数にアタッチされているElastic Network Interface(ENI)にパブリックIPを割り当ててみる。

最初はこの方法を試し、VPC内のLambdaからインターネット接続できることを確認できたのですが、LambdaのプライベートIPが変わると、パブリックIPの割り当てが解除されてしまいました。

安定した接続を維持するのが難しくなるので、現実的な方法ではないですね。

VPC外のLambdaをブリッジとして使用

VPC内のLambda：メインの処理＆RDSとの通信を担当
VPC外のLambda：インターネット接続（メール送信）を担当
VPC内のLambda → VPC外のLambdaを呼び出す

# VPC内LambdaからVPC外Lambdaを呼び出すサンプル
def invoke_send_email_lambda(start_time, end_time, error_message):
    payload = {
        'start_time': start_time.strftime("%Y-%m-%dT%H:%M:%S"),
        'end_time': end_time.strftime("%Y-%m-%dT%H:%M:%S"),
        'error_message': error_message
    }

    response = lambda_client.invoke(
        FunctionName=LAMBDA_FUNCTION_NAME,  # ここにVPC外のLambdaの関数名を記載
        InvocationType='RequestResponse',  # 同期的に呼び出す
        Payload=json.dumps(payload)        
    )

    response_payload = json.loads(response['Payload'].read())
    print("VPC外のLambdaからのレスポンス:", response_payload)

この方法で実現できました。

VPC内のLambdaからVPC外のLambdaを呼び出すためには、VPCエンドポイントの設定が必要です。

Lambdaすごいですね。

2023-09-03

CloudWatch Logsでのログ分析について

AI AWS 🥒

まだまだ日中は暑いですが、夕方になると涼しさを感じるようになってきました。ヒグラシの鳴き声もとても心地よいですね。

今回は、Amazon CloudWatch Logs についてです。

サーバで出力するログファイルを CloudWatch に転送して効率的に分析する方法を探っていきましょう。

1. 概要

Amazon EC2 のアクセスログファイル（JSON形式）を AWS CloudWatch agent を使用して CloudWatch に転送し、CloudWatch Insights を用いてログを分析する手順を説明します。

2. 前提条件

EC2 インスタンスが実行中であること。
アクセスログがJSON形式で出力されていること。

3. AWS CloudWatch agent のインストールと設定

3.1. EC2 インスタンスに SSH 接続

3.2. CloudWatch agent をインストール

$ sudo yum install -y amazon-cloudwatch-agent

3.3. CloudWatch agent の設定ファイルを作成

/opt/aws/amazon-cloudwatch-agent/bin/にconfig.jsonファイルを作成し、以下の内容を書き込みます。

{
        "agent": {
                "run_as_user": "root" 
        },
        "logs": {
                "logs_collected": {
                        "files": {
                                "collect_list": [
                                        {
                                                "file_path": "/path/to/your/access-log.json",
                                                "log_group_name": "EC2_Access_Logs",
                                                "log_stream_name": "{instance_id}",
                                                "retention_in_days": -1
                                        }
                                ]
                        }
                }
        }
}

3.4. CloudWatch agent を起動

$ sudo /opt/aws/amazon-cloudwatch-agent/bin/amazon-cloudwatch-agent-ctl -a fetch-config -m ec2 -s -c file://opt/aws/amazon-cloudwatch-agent/bin/config.json

3.5. CloudWatch agent の自動起動を有効化

$ sudo systemctl enable amazon-cloudwatch-agent.service

4. CloudWatch Insights を使用してログを分析

AWS マネジメントコンソールにログインし、CloudWatchに移動。
左側のナビゲーションペインでロググループをクリック。
EC2_Access_Logsロググループをクリック。
ログストリームタブを開き、対象のログストリームを選択。
ログイベントの上部にあるアクションのLogs Insights で表示をクリックして、ログのインサイトを開く。
クエリ言語を使用してログデータを分析できます。

ログデータ例（JSON形式）

{
    "user": {
        "id": "12345",
        "profile": {
            "name": "John Doe",
            "email": "johndoe@example.com",
            "location": {
                "city": "Tokyo",
                "country": "Japan"
            }
        }
    },
    "action": "login",
    "timestamp": "2023-08-20T10:00:00Z"
}

CloudWatch Logs Insightsでクエリする例を以下に示します。

4.1. 特定のユーザー ID を持つログをフィルタリング

fields @timestamp, @message
| filter user.id = "12345"

4.2. 特定の都市 (Tokyo) からのアクションをフィルタリング

fields @timestamp, @message, user.profile.location.city
| filter user.profile.location.city = "Tokyo"

4.3. 特定の国 (Japan) でのログインアクションをフィルタリング

fields @timestamp, @message, user.profile.location.country, action
| filter user.profile.location.country = "Japan" and action = "login"

4.4. ユーザーのメールアドレスをリスト

fields user.profile.email

5. 比較

AWS CloudWatch Logs Insightsは、JSON形式のログの場合、上記のようなクエリで分析できます。

一方、プレーンテキストのログの場合はどうでしょうか。

ログデータ例（プレーンテキスト）

2023-08-20T10:00:00Z user:12345 name:John Doe email:johndoe@example.com city:Tokyo country:Japan action:login device:mobile browser:chrome
2023-08-20T11:00:00Z user:67890 name:Jane Smith email:jane@example.com city:Osaka country:Japan action:logout device:desktop browser:firefox

Tokyoからログインしたモバイルユーザーをフィルタリングする場合

fields @timestamp, @message
| filter @message like /city:Tokyo/ and @message like /action:login/ and @message like /device:mobile/

Chromeを使用してアクセスしたユーザーのメールアドレスを取得する場合

fields @message
| filter @message like /browser:chrome/
| parse @message "* email:* city:" as email
| display email

このように、プレーンテキストの場合、特定のキーワードやパターンを探すために正規表現やlike演算子を使用する必要があります。クエリの複雑さや誤りを招く要因となりますね。

構造化されたログフォーマット（JSON）を使用することで、CloudWatch Logs Insightsでのログ分析が効率的かつ簡単になります。

2023-08-02

MySQLで自由にソートするテクニック

AI SQL 🥒

毎日暑いですね。今回はサクッとSQLについてです。

やりたいこと

商品マスタテーブル（MySQL）の項目に特定の値がセットされている商品を販売サイトの上位に表示させて利用者におすすめしたい。

商品マスタデータ

この商品マスタのデータについて

ブランドA、B、Cを上位に表示したい。
XXX製、YYY製、ZZZ製を上位に表示したい。
ブランドA → XXX製 → ブランドB → ブランドC → YYY製 → ZZZ製の順に表示したい。
同じ表示順位の場合は、商品ID順に表示したい。

どのように実現するか。

並び順マスタ

以下のような並び順マスタを用意します。

テーブル作成・データ登録のSQL

①商品マスタ（item）テーブルの作成

CREATE TABLE item (
  item_code INT PRIMARY KEY, --商品ID
  item_name VARCHAR(255), --商品名
  brand_code CHAR(1), --ブランド
  category VARCHAR(255), --カテゴリ
  subcategory VARCHAR(255), --サブカテゴリ
  color VARCHAR(255), --色
  item_size CHAR(1), --サイズ
  material VARCHAR(255), --素材
  price INT, --価格
  manufacturer_code VARCHAR(4), --製造元
  stock INT --在庫数
)

②並び順マスタ（sort）テーブルの作成

CREATE TABLE sort (
  id INT PRIMARY KEY, --ID
  sort_type INT, --区分
  code VARCHAR(10), --コード
  name VARCHAR(255), --名称
  sort INT --並び順
)

③商品マスタ（item）へのデータ登録

INSERT INTO item 
(item_code, item_name, brand_code, category, subcategory, color, item_size, material, price, manufacturer_code, stock)
VALUES
(1001, 'VネックTシャツ', 'D', 'Tシャツ', 'VネックTシャツ', '白', 'M', 'コットン', 2000, '0001', 50),
(1002, 'スキニーパンツ', 'E', 'パンツ', 'スキニーパンツ', 'ブラック', 'L', 'デニム', 6000, '0002', 30),
(1003, 'マキシドレス', 'C', 'ドレス', 'マキシドレス', 'レッド', 'S', 'シルク', 15000, '0003', 20),
(1004, 'ボタンダウンシャツ', 'A', 'シャツ', 'ボタンダウンシャツ', '青', 'L', 'コットン', 4000, '0001', 40),
(1005, 'フレアスカート', 'B', 'スカート', 'フレアスカート', 'ピンク', 'M', 'ポリエステル', 3500, '0004', 70),
(1006, 'レザージャケット', 'C', 'ジャケット', 'レザージャケット', 'ブラック', 'M', 'レザー', 20000, '0003', 10),
(1007, 'スリムフィットジーンズ', 'D', 'パンツ', 'スリムフィットジーンズ', 'ブルー', 'S', 'デニム', 7000, '0004', 35),
(1008, 'バンドTシャツ', 'E', 'Tシャツ', 'バンドTシャツ', '白', 'L', 'コットン', 2500, '0001', 60),
(1009, 'プリーツスカート', 'E', 'スカート', 'プリーツスカート', 'グレー', 'M', 'ポリエステル', 4000, '0004', 80),
(1010, 'ダウンジャケット', 'B', 'ジャケット', 'ダウンジャケット', 'カーキ', 'L', 'ナイロン', 12000, '0002', 15);

④並び順マスタ（sort）へのデータ登録

INSERT INTO sort
(id, sort_type, code, name, sort)
VALUES
(1, 1, 'A', 'ブランドA', 1),
(2, 1, 'B', 'ブランドB', 3),
(3, 1, 'C', 'ブランドC', 4),
(4, 2, '0001', 'XXX製', 2),
(5, 2, '0002', 'YYY製', 5),
(6, 2, '0003', 'ZZZ製', 6)

商品データ表示用SQL

・ブランドA、B、Cを上位に表示したい。
・XXX製、YYY製、ZZZ製を上位に表示したい。
・ブランドA → XXX製 → ブランドB → ブランドC → YYY製 → ZZZ製の順に表示したい。
・同じ表示順位の場合は、商品ID順に表示したい。

上記の並び順を実現するSQLは以下となります。

SELECT result.*
FROM (
    SELECT item.*, 
           COALESCE(LEAST(sort_brand.sort, sort_manufacturer.sort), sort_brand.sort, sort_manufacturer.sort, 9999) AS sort_order
    FROM item
    LEFT JOIN sort AS sort_brand ON item.brand_code = sort_brand.code AND sort_brand.sort_type = 1
    LEFT JOIN sort AS sort_manufacturer ON item.manufacturer_code = sort_manufacturer.code AND sort_manufacturer.sort_type = 2
) AS result
ORDER BY result.sort_order, result.item_code;

＜解説＞

サブクエリで全ての商品マスタ（item）のデータを取得します。
各商品データについて、ブランド（brand）と製造元（manufacturer）の並び順（sort）を検索します。
2.1 両方の並び順が存在する場合は、LEAST関数により小さい方（並び順が上位の方）が選ばれます。
2.2 一方だけが存在する場合は、存在する方の並び順が選ばれます。
2.3 どちらも存在しない場合は、9999が選ばれます。
このサブクエリの結果を使って、全ての商品をソートします。ソートは、2で選ばれた並び順（sort_order）で行われ、並び順が同じ商品の場合は、商品ID（item_code）でソートします。これにより、最終的な商品リストが生成されます。

フレキシブルですね。

2023-07-13

CloudFrontとブラウザのキャッシュについて

AI AWS 🥒

暑い日が続きますね。キュウリがとても美味しい季節となりました。
キュウリは、汗と共に失われる水分を補給し体の冷却を助けてくれます。

今回は、キャッシュ（頻繁にアクセスするデータを一時的に保存して次回のアクセスを速くする仕組み）についてです。

なんとなく、効率化という観点でキュウリとキャッシュ似ているような気がします。こじつけかしらん。

tech.briswell.com

前回はCloudFrontの利用メリットについて書きましたが、キャッシュの仕様についても、正しく理解しておかないと、CDNのCloudFrontを最大限に活かすことができません。

CloudFront側のキャッシュ（エッジキャッシュ）
ブラウザ側のキャッシュ

それぞれ見ていきましょう。

1. CloudFront側のキャッシュ

CloudFrontは、以下が同じ場合に同一リクエストと判断し、キャッシュをクライアント側に返却します。

URL
HTTPメソッド
特定の HTTPヘッダー（任意設定)
クエリ文字列（任意設定)
cookie（任意設定)

キャッシュ期間は、TTL設定（最低存続時間、最大存続時間、デフォルトの存続時間）で定義します。

Time to live (TTL) 設定：
キャッシュキーの管理 - Amazon CloudFront

CloudFrontのInvalidation（1か月に送信した無効化パスのうち最初の1,000 件は無料）を実行することで、CloudFrontのキャッシュを手動削除することも可能です。

1-1. キャッシュが存在しない場合

設定された条件（オリジンリクエストポリシー）に従い、オリジンサーバーにリクエストを転送しデータを取得します。
取得したデータをクライアントに返却します。
取得したデータを設定された条件（キャッシュポリシー）に従ってキャッシュします。

1-2. 有効なキャッシュが存在する場合

キャッシュデータをクライアントに返却します。

1-3. 無効な（期限切れ）キャッシュが存在する場合

設定された条件（オリジンリクエストポリシー）に従い、オリジンサーバーにリクエストを転送しデータを取得します。
オリジンサーバーのデータがキャッシュと同じ場合
- キャッシュデータをクライアントに返却します。
- キャッシュデータのTTLを更新します。
オリジンサーバーのデータがキャッシュと違う場合
- 取得したデータをクライアントに返却します。
- 取得したデータを設定された条件（キャッシュポリシー）に従ってキャッシュします。

2. ブラウザ側のキャッシュ

ブラウザ側のキャッシュは、Cache-Controlの設定で制御することができます。

この設定を理解しておかないと...
「オリジンサーバーの画像ファイルを差し替えてCloudFrontのキャッシュも最新のはずなのに、いつまで経ってもブラウザのキャッシュ画像を表示している。CloudFrontにもリクエストしていない。なぜ？」
となります。

AWS CLIを使用してS3（オリジンサーバー）にファイルをアップロードする場合、以下のようにCache-Controlヘッダーを設定することができます。

aws s3 cp /path/to/your/file s3://yourbucket/yourpath --cache-control max-age=3600

設定値について記載します。

no-cache

返却値をキャッシュできますが、キャッシュを使用する前に、オリジンサーバーでの検証が必要です。これにより、古いコンテンツが使用されることを防ぐことができます。

no-store

全てのキャッシュを無効にします。コンテンツがセキュアな情報（パスワードやクレジットカード情報など）を含む場合等に使用されます。

max-age=[seconds]

キャッシュを有効とみなす時間を指定します。
例えば
「3600」を指定すると、3600秒（1時間）がキャッシュ保持期間となります。
「0」を指定すると、最初から既に期限切れの状態なので、毎回変更がないか問合せします。

Cache-Controlヘッダーがない場合

この場合、どのような挙動になるのでしょうか？

RFC 7234 - Hypertext Transfer Protocol (HTTP/1.1): Caching

If the response has a Last-Modified header field (Section 2.2 of [RFC7232]), caches are encouraged to use a heuristic expiration value that is no more than some fraction of the interval since that time. A typical setting of this fraction might be 10%.

Last-Modifiedヘッダーの日時
Dateヘッダーの日時

の差の10%の値をキャッシュの有効期間として定めることが多いとのことです。
コンテンツが変更されない期間が長くなるほど、ブラウザキャッシュの有効期限も長くなる（より長くキャッシュされる）ということですね。

そのため、何らかの事情で、Cache-Controlヘッダーを設定できない場合、
ブラウザ側で古いコンテンツを差し替えて新しいコンテンツを表示するためには...

何らかの操作時にブラウザキャッシュをクリアする処理を入れる
CloudFrontからの画像取得パスにクエリ文字列（例：test.jpg?20230713）を入れる

等の対策が必要になってきます。

キャッシュって奥が深いですね！

2023-06-08

CloudFront + S3 での画像表示

AI AWS 🥒

梅雨シーズン到来です。雨の日も、気持ちだけはスカッと晴れやかにいたいですね。

今回は、「Amazon CloudFront」についてです。その名の通りクラウドのフロントエンド（ユーザに最も近い場所）からコンテンツを提供してくれるサービスです。

以前、ある案件で「Amazon S3」に配置されている大量の画像ファイルをWebページで表示する際に、

S3ダイレクトで画像ファイルを取得する
CloudFront経由でS3の画像ファイルを取得する

どちらの方がいいか悩んだことがありました。

実際にパフォーマンス検証もしてみて、2のCloudFront経由の方が速かったのと、静的Webサイト配信等の事例もあったので、2を採用したのですが、具体的にどのようなメリットがあるのか、改めて振り返ってみます。

1. パフォーマンス

CloudFrontは、コンテンツ配信ネットワーク（CDN）であり、ユーザーに近い場所にあるエッジロケーションにコンテンツをキャッシュします。

特徴 - Amazon CloudFront | AWS

上記の記事によると、エッジロケーションは、東京（ap-northeast-1）リージョンに「20」もあるとのこと。すごいですね。

これによって、ユーザーから物理的に近い場所からコンテンツが提供されるので、画像ファイルの読み取り速度が向上します。つまり、ユーザーはWebページの画像を早く見ることができます。（S3から直接コンテンツを提供する場合は、ユーザーがS3バケットから遠い場所にいると、画像ファイルの読み取り速度が低下します）

CloudFrontは初回アクセス時やキャッシュ失効時にオリジンからコンテンツを取得します。
S3に格納されている画像ファイル（約4MB）について、CloudFrontに「キャッシュなし」の場合、「キャッシュあり」の場合のダウンロード時間を確認してみました。

キャッシュなしの場合：373 ミリ秒

キャッシュありの場合：27 ミリ秒

14倍近く速いですね！

2. セキュリティ

CloudFrontはOAC（Origin Access Control）を利用してS3コンテンツへのアクセス制限が可能で、S3バケットへのアクセスをCloudFrontからのみに限定することができます。これにより、オリジンのS3を保護することができます。

また、AWS WAFをCloudFrontに導入することで、不正なトラフィックやウェブ攻撃から保護するための設定や、リファラーによるアクセス制限等が可能になります。また、CloudFrontはデフォルトでSSL/TLS 証明書を使用することができ、ユーザーとサーバー間の通信が保護され、データが傍受されるリスクが減少します。

3. コスト

料金 - Amazon S3 ｜AWS
料金 - Amazon CloudFront | AWS

＜リクエスト料金（1万件あたり）＞
S3：0.0037 USD（GET、SELECT、他のすべてのリクエスト）
CloudFront：0.012 USD（HTTPSリクエスト）

＜10TBまでのデータ転送料金（1GBあたり）＞
S3：0.114 USD
CloudFront：0.114 USD

また、CloudFrontについて

Amazon Simple Storage Service (S3)、Amazon Elastic Compute Cloud (EC2)、Elastic Load Balancers など、AWS のあらゆるオリジンからのオリジン取得は無料です。

とのことなので、S3の前段にCloudFrontを置いても、コスト的にあまり変わりはないですね。

今後は悩まずにCloudFront導入に振り切れます！