DockerコンテナからAWSにアクセスする時、AWSのcredentialsが必要になる。 AWS上でコンテナを動かす場合IAMロールを付与すればいいが、ローカルで動かす時はそうもいかない。 そこで、ここではローカルのAWS認証情報(~/.aws/)をコンテナに渡す方法をまとめた。 docker-compose.yaml AWS SDK for JavaScriptを使ったWebアプリケーションをDockerコンテナで動かす体で見て欲しい。 docker-compose.yaml から一部抜粋する。 docker-compose.yaml : app: environment: - AWS_SDK_LOAD_CONFIG - AWS_PROFILE - NODE_ENV=development volumes: - ~/.aws/:/root/.aws:ro : environment 環境変数は値を書かなければローカルの環境変数がパススルーされるので、必要に応じてお好みで設定する。だいたい .env に書いている。 ちなみに AWS_SDK_LOAD_CONFIG を true に設定すると、SDKが自動的に ~/.aws/config を読み込むようになる。不要であれば別途 AWS_REGION を設定する必要がある。 プロファイルを default から変更したい時も AWS_PROFILE を設定する必要がある。 environment: - AWS_SDK_LOAD_CONFIG - AWS_PROFILE volumes ローカルの ~/.aws を、Dockerコンテナの /root/.aws にマウントする（念の為、読み取り専用にしている）。これでDockerコンテナからAWSの認証情報を参照できるようになる。 ここでは root ユーザーを実行ユーザーにしているが、実際は専用ユーザーを作成したほうがいいと思う。 volumes: - ~/.aws/:/root/.aws:ro

AWSサービスLightsail Amazon Lightsailは、AWSが提供しているVPS（Virtual Private Server:仮想プライベートサーバー）サービスです。 データベースの操作方法 Linux/Unix OSとLAMP(PHP 7)アプリのインスタンスを作成している前提です。 まずは、インスタンスにアクセスします。 ターミナルでもいいしLightsailの管理画面でもOKです。 そこで、下記のコマンドを実行して、データベースのパスワードを調べます。 出てきたパスワードをコピーします。 コマンド cat bitnami_application_password パスワードがわかったら、mysqlコマンドを使ってアクセスします。 コマンド mysql -u root -p 先ほど、入手したパスワードをペーストします。 入れない場合は入力して見てください。 rootパスワードを変更する方法 コマンド MariaDB [(none)]> update mysql.user set password=password('新しいパスワード') where user = 'root'; flush privileges; ユーザーを作成する方法 「wordpress」というユーザーを「mypassword」というパスワードで作成します。 コマンド MariaDB [(none)]> create user　'wordpress'@'localhost' IDENTIFIED BY　'mypassword'; データベース作成方法 入ったら、下記のコマンドを実行して作成します。 「DB_NAME」はデータベースの名前です。 コマンド MariaDB [(none)]> create database DB_NAME; データベース確認方法 下記のコマンドでデータベース一覧が確認出来ます。 コマンド MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | DB_NAME 　　 | | test | +--------------------+ データベース権限設定方法 コマンド MariaDB [ (none)]> grant all privileges on DB_NAME.* to 'wordpress'@'localhost' FLUSH PRIVILEGES;

はじめに こちらは、 Managing your machine learning lifecycle with MLflow and Amazon SageMakerを参考にして実際に作成したものです。 ただし、このブログの紹介で作成しているのは、MLflowをECS上にDeployし、S3とRDSをそれぞれArtifact store、backendストアにするところまでなので、 SageMakerもまとめて作りたい場合はこちらを! MLflow初であれば→MLflow 基礎 準備 AWSアカウント → まだ無い場合はSign Up aws-cdkをインストール＆設定 詳細はこちらを参照 Docker https://github.com/aws-samples/amazon-sagemaker-mlflow-fargate をClone 1. MLflowをFarget上にCloudFormationStackを用いてDeploy 上記のレポCloneしたディレクトリへ移動 cd amazon-sagemaker-mlflow-fargate 必要なものをインストール python3 -m venv .venv source .venv/bin/activate pip3 install -r requirements.txt デプロイ (25分くらいかかる) AWSの特定のprofileを使用している場合は、 --profile <profile名>をつける cdk deploy --parameters ProjectName=mlflow --require-approval never 結果詳細 cdk deploy --parameters ProjectName=mlflow --require-approval never --profile naka-personal ... ✅ DeploymentStack Outputs: DeploymentStack.LoadBalancerDNS = Deplo-MLFLO-xxx-xxxx.elb.ap-northeast-1.amazonaws.com DeploymentStack.MLFLOWLoadBalancerDNSAEFB7E43 = Deplo-MLFLO-xxxx-xxxxx.elb.ap-northeast-1.amazonaws.com Stack ARN: arn:aws:cloudformation:ap-northeast-1:xxxxx:stack/DeploymentStack/798f4a90-313e-11ec-a179-0aa3345c497f Docker起動していないとエラーになる Dockerを起動していないと以下のようなエラーになるので注意 cdk deploy --parameters ProjectName=mlflow --require-approval never --profile naka-personal [Warning at /DeploymentStack/MLflow/Container/AssetImage] DockerImageAsset.repositoryName is deprecated. Override "core.Stack.addDockerImageAsset" to control asset locations DeploymentStack: deploying... [0%] start: Publishing 8f0fed36a7179b1d96d969db07932d3497d1d1a297b54c7b1eb5dd77dc952f06:current Cannot connect to the Docker daemon at unix:///var/run/docker.sock. Is the docker daemon running? [100%] fail: docker build --tag cdkasset-8f0fed36a7179b1d96d969db07932d3497d1d1a297b54c7b1eb5dd77dc952f06 . exited with error code 1: Cannot connect to the Docker daemon at unix:///var/run/docker.sock. Is the docker daemon running? ❌ DeploymentStack failed: Error: Failed to publish one or more assets. See the error messages above for more information. at Object.publishAssets (/usr/local/lib/node_modules/aws-cdk/lib/util/asset-publishing.ts:25:11) at Object.deployStack (/usr/local/lib/node_modules/aws-cdk/lib/api/deploy-stack.ts:252:3) at CdkToolkit.deploy (/usr/local/lib/node_modules/aws-cdk/lib/cdk-toolkit.ts:189:24) at initCommandLine (/usr/local/lib/node_modules/aws-cdk/bin/cdk.ts:225:9) Failed to publish one or more assets. See the error messages above for more information. このコマンドでデプロイされたもの (app.pyにかかれている) (CloudFormation Stack DeploymentStack でも詳細確認可): IAM ROLE DeploymentStack-MLflowExecutionRoleXXXXXXXXXXX SECRET dbPasswordがSecretManagersに作成される VPC Vpc: DeploymentStack/VPC: 10.0.0.0/24 <- このCidrがすでに使われている場合はエラるかも Subnets: DeploymentStack/VPC/PublicSubnet1 DeploymentStack/VPC/PrivateSubnet1 DeploymentStack/VPC/DBSubnet2 DeploymentStack/VPC/PrivateSubnet2 DeploymentStack/VPC/PublicSubnet2 DeploymentStack/VPC/DBSubnet1 Others: RouteTable, InterneteGateway, ElasticIP S3 BUCKET mlflow-artifacts-<account id> DATABASE 名前: ランダム文字列, size: db.t2.small, Engine: MySQL Community FARGATE SERVICE ECS cluster using Fargate: mlflow Service: mlflow ECR: <account id>.dkr.ecr.ap-northeast-1.amazonaws.com/aws-cdk/assets MLflow UIの確認 アウトプットのDeploymentStack.LoadBalancerDNSに書いてあるリンクを開く。 ※ただし、LBがPublicで、MLflowはuser access controlの機能を提供してないので、実際に使う場合には、PrivateLinkなどを使ってPrivateにする必要がある 2. SageMakerからの連携 ブログ内ではSageMakerの作成部分は書かれていないため省略… (予想外。) 　最後にに比較を書いたので、SageMakerも含めて作成したい場合は、そちらも参考に。 SageMakerの中でのプロジェクト管理をMLflowのRemoteURIを用いて行えるようになる import mlflow mlflow.set_tracking_uri('<YOUR LOAD BALANCER URI>') 3. Farget上に作成したMLｆlowの削除 (20分ほどかかる) 作成したものの削除 cdk destroy --parameters ProjectName=mlflow --require-approval never --profile naka-personal Are you sure you want to delete: DeploymentStack (y/n)? y DeploymentStack: destroying... ... ✅ DeploymentStack: destroyed 比較 & まとめ 関連したCDKがいくつかあり試したので、一応所感を書いておく。 aws-mlflow-sagemaker-cdk: 2021-10-14 に作られた最新のもので、SageMakerとMLflowをまとめて構築できる最強のパック。 TypescriptっぽいのでPythonに慣れてる人にとってはちょっとマイナス。 MLｆlowのEndpointがinternalロードバランサーで作成されるので、デフォルトではMLflow UIへのアクセス方法がない‥ → Client VPNを使って試した記事はこちら amazon-sagemaker-mlflow-fargate: こちらは今回紹介したもの コードはPython MLflowのEndpointがInternet facingとなるので、セキュリティ上そのままは使えない 名前からはSageMakerとMLflow両方デプロイするように見えて実は、MLflowをFargate上にデプロイしているだけなので、SageMakerは別途自分で作成する必要がある amazon-sagemaker-cdk-examples: SageMakerのCDKサンプルが沢山入っているもの MLflowは関係ない 一番使いたかったのは、 notebook-teamsだが、こちらはVPCやSubnetまで作成するので、今回紹介した MLflowをFargate上に構築するのとあまり相性が良くない。別々でVPCが作成されたあとのPrivateLinkなどの設定を別途作成する必要がある。→ それなら一番上のaws-mlflow-sagemaker-cdkが一番楽そう。 これらは、どれもVPCの構築からしてくれるので、全く何もないところにPOCで構築して試すにはもってこいである。ただし、既存のVPCの上に構築したい場合には、他のものを探したり自分で書き直す必要がありそう。 Reference aws-samples/aws-mlflow-sagemaker-cdk aws-samples/amazon-sagemaker-mlflow-fargate aws-samples/amazon-sagemaker-cdk-examples Managing your machine learning lifecycle with MLflow and Amazon SageMaker MLflow cdk コマンドの機能を 実際に叩いて理解する 【 AWS CDK Command Line Interface 】 AWS Cloud Development Kit(CDK)(Python)を使って「VPC+Sagemaker+Lambda」環境構築してみた(Sagemaker編)

Amazon S3 ファイルゲートウェイをterraformで構築しました。 下記記事を参考に（SMBによるファイル共有）、NFSとしてstorage gateway及びファイル共有をterraformで作成しました。 先に結論から色々書いてしまうと、まずterraformで構築する利点はあまりないなと感じました。 今回storage gateway VMの移行があり、対象ファイル共有も多数あったのでterraformで一気に作っちゃいましょうという流れでした。 terraformで構築する利点として、ファイル共有の増減が容易に出来る。 stoage gateway本体をterraformで構築するメリットはあまりない（アクティベートキーの取得部分）用に感じました。 また、storage gatawayによるNFSファイル共有は双方向ではないことに注意が必要です。 マウント先で設置したファイルはS3にアップロードされますが、S3に直接アップロードしたファイルはマウント先には反映されません。 今回vpcやらkey/secやらproviderやらの設定は割愛します。 この辺の記事を参考にしていただけたら幸いです。 ではいきましょう。 $ terraform version Terraform v1.0.9 on darwin_amd64 + provider registry.terraform.io/hashicorp/aws v3.62.0 作成するもの ・storage gateway用インスタンス一台（m4.xlarge） ・ファイル共有 ・ファイル共有用のIAM role及びpolicy 今回はprivate subnet上に構築するため、gatewayをアクティベートするためのキーは別途取得する必要があります。 つまり、アクティベートキーの取得前と後でapplyは二回叩く必要があるということです。 ※というかstorage gatewayをpublic上に置く機会自体あまりなさそう まずは一回目のapplyでstorage gatewayで使用するEC2インスタンスの立ち上げます。 storage gatewayのcache用に、root以外に別途EBSをぶら下げてます。 ※rootでもいいっちゃいいけど外付けで切り離せるようにしておいたほうが後々楽そうなので。 storage用S3バケットは今回terraform外で作成済みです。 AMIに関してはstorage gateway用のAMIがあるので引っ張ってきてます。 インスタンスのサイズは公式が推奨しているサイズ以上のものを用意してあげましょう。 ec2_sgw.tf data "aws_ssm_parameter" "sgw_ami" { name = "/aws/service/storagegateway/ami/FILE_S3/latest" } resource "aws_instance" "sgw_test" { ami = data.aws_ssm_parameter.sgw_ami.value vpc_security_group_ids = ["sg-00000000"] subnet_id = "subnet-00000000" key_name = "hoge-key" instance_type = "m4.xlarge" tags = { Name = "sgw_test" } root_block_device { volume_type = "gp3" iops = 3000 throughput = 125 volume_size = 80 delete_on_termination = true encrypted = false tags = { Name = "sgw_root" } } ebs_block_device { device_name = "/dev/sdf" volume_type = "gp3" iops = 3000 throughput = 125 volume_size = 150 delete_on_termination = true encrypted = false tags = { Name = "sgw_cache" } } } このEC2自体にstorage gatewayに関する IAM roleは不要です。 後に作成するファイル共有に必要となってきます。 とりあえずインスタンス一台だけ書けたらapply。 上がったらIP addressを控えておきましょう。 次に作成したインスタンスをstorage gatewayとして使用するためにアクティベートキーを取得します。 実行コマンドは以下 curl 'ec2_instance_ip_address/?activationRegion=activation_region&no_redirect' 先に立てたインスタンスはprivate subnet内に立ててるので、別のインスタンスかどっか別の場所から叩く必要があります。 今回はpublic上にある別インスタンスがsgw用インスタンスと疎通ができるので、そこから以下コマンドを実行します。 IP addressとregionはsgw用インスタンスのものを指定しましょう。 $ curl '10.10.116.97/?activationRegion=us-west-2&no_redirect' 疎通が取れる別インスタンスから実行した結果 [root@ip-10-10-7-106 ec2-user]# ping 10.10.116.97 PING 10.10.116.97 (10.10.116.97) 56(84) bytes of data. 64 bytes from 10.10.116.97: icmp_seq=1 ttl=255 time=0.347 ms 64 bytes from 10.10.116.97: icmp_seq=2 ttl=255 time=0.303 ms 64 bytes from 10.10.116.97: icmp_seq=3 ttl=255 time=0.332 ms ^C --- 10.10.116.97 ping statistics --- 3 packets transmitted, 3 received, 0% packet loss, time 2031ms rtt min/avg/max/mdev = 0.303/0.327/0.347/0.023 ms [root@ip-10-10-7-106 ec2-user]# curl '10.10.116.97/?activationRegion=us-west-2&no_redirect' IF1FV-UMTIJ-00000-00000-00000[root@ip-10-10-7-106 ec2-user]# IF1FV-UMTIJ-00000-00000-00000[ 無事アクティベーションキーが発行されました。 このキーを使って2回目のapplyをしていきます。 ファイル共有に必要なIAM roleとpolicyを作成します。 policyをtfファイル内に直書きするのは個人的に見にくいので別途jsonファイルにしてfile関数で参照する書き方をしています。 書き方についてはこのあたりを参考にどうぞ。 iam.tf # Policy resource "aws_iam_role_policy" "s3_policy_01" { name = "s3_sgw-test1" role = aws_iam_role.s3_role.id policy = file("./s3_sgw-test1.json") } # Role resource "aws_iam_role" "s3_role" { name = "sgw_s3_role" assume_role_policy = <<EOF { "Version": "2012-10-17", "Statement": [ { "Action": "sts:AssumeRole", "Principal": { "Service": "storagegateway.amazonaws.com" }, "Effect": "Allow", "Sid": "" } ] } EOF } s3_sgw-test1.json { "Version": "2012-10-17", "Statement": [ { "Action": [ "s3:GetAccelerateConfiguration", "s3:GetBucketLocation", "s3:GetBucketVersioning", "s3:ListBucket", "s3:ListBucketVersions", "s3:ListBucketMultipartUploads" ], "Resource": "arn:aws:s3:::sgw-test1", "Effect": "Allow" }, { "Action": [ "s3:AbortMultipartUpload", "s3:DeleteObject", "s3:DeleteObjectVersion", "s3:GetObject", "s3:GetObjectAcl", "s3:GetObjectVersion", "s3:ListMultipartUploadParts", "s3:PutObject", "s3:PutObjectAcl" ], "Resource": "arn:aws:s3:::sgw-test1/*", "Effect": "Allow" } ] } sgwをアクティベートする設定とファイル共有を作成します。 以下の欄に先に取得したアクティベーションキーを転記します。 resource "aws_storagegateway_gateway" "sgw" { activation_key = "IF1FV-UMTIJ-00000-00000-00000" timezoneの書き方がGMTを基準にプラスマイナスで記載しなければいけないことに注意してください。 sgw.tf resource "aws_storagegateway_gateway" "sgw" { activation_key = "IF1FV-UMTIJ-00000-00000-00000" gateway_name = "terraform-test" gateway_timezone = "GMT+9:00" gateway_type = "FILE_S3" } data "aws_storagegateway_local_disk" "sgw" { gateway_arn = aws_storagegateway_gateway.sgw.arn disk_node = "/dev/sdf" } resource "aws_storagegateway_cache" "sgw" { gateway_arn = aws_storagegateway_gateway.sgw.arn disk_id = data.aws_storagegateway_local_disk.sgw.id } resource "aws_storagegateway_nfs_file_share" "share01" { client_list = ["0.0.0.0/0"] gateway_arn = aws_storagegateway_gateway.sgw.arn location_arn = "arn:aws:s3:::sgw-test1" role_arn = aws_iam_role.s3_role01.arn object_acl = "bucket-owner-full-control" nfs_file_share_defaults { directory_mode = "0777" file_mode = "0666" group_id = "65534" owner_id = "65534" } } 上の設定でsgwのアクティベートと、ファイル共有が作成可能になりました。 ココまで来たらterraformでの作業は終了です。 コンソールからファイル共有が作成されていることを確認します。 コマンド例にmountコマンドが記載されているので、適当なサーバーにマウントしてファイルを置いてみましょう。 S3へアップロードができていれば無事完了です。 お疲れさまでした。

はじめに 本記事ではAmazon Lightsailで起動済みのRedMineにガントチャートを使いやすくしてくれるeasy ganttプラグインを導入して、ユーザーが使えるようになるところまで設定します プラグイン導入の手順 easy redmineからEasyGanttFree-4.x.zipをダウンロードし、サーバーの任意のディレクトリ(今回は/tmp/)にアップロード /opt/bitnami/apps/redmine/htdocs/plugins/にプラグインを展開 // zipをプラグイン用ディレクトリにコピーする cp -p /tmp/EasyGanttFree-4.x.zip /opt/bitnami/apps/redmine/htdocs/plugins/ // プラグイン用ディレクトリに移動 cd /opt/bitnami/apps/redmine/htdocs/plugins/ // zipを解凍 unzip EasyGanttFree-4.x.zip // zipを削除 rm EasyGanttFree-4.x.zip Ruby on RAILSの設定 // root権限に変更 sudo su // ディレクトリを移動 cd /opt/bitnami/apps/redmine/htdocs/ // gemをインストール bundle install --no-deployment // ※上記でインストールできなかったら下記を実行 bundle update rails bundle update railsinstall --no-deployment // DBを指定してマイグレート bundle exec rake db:migrate RAILS_ENV=production - RedMineをリスタート touch tmp/restart.txt RedMineのプロジェクトとロールの設定変更 こちらの記事にしたがってプロジェクトへの設定とメンバーへの権限付与するとEasyGanttを使えるようになります https://qiita.com/moriyuki/items/431640812578106a85b3 その他参考記事 https://qiita.com/suzuki_sh/items/84806ce231ed9b0f2e6d https://knight-3000.hatenablog.jp/entry/2018/08/11/004822 easy ganttの使い方のおすすめ記事

はじめに AWS認定ソリューションアーキテクト　アソシエイト試験に合格したので、以下のことについて記載します。 １．受験のきっかけ ２．学習方法 ３．当日のこと 皆さんのご参考になれば幸いです。 1.受験のきっかけ 業務でAWSを使っていて1年経つしそろそろ受けてみるか～っていうのが１つ。 そして他のサービスのことも知りたいな～って思うようになったのが１つっていう感じです。 2.学習方法 正直みなさんが気になるのはここからだと思います。 わたしの学習方法としては主に以下の流れです。 ①参考書で知識習得 改訂新版 徹底攻略 AWS認定 ソリューションアーキテクト − アソシエイト教科書［SAA-C02］対応 徹底攻略シリーズ いわゆる黒本ってやつです。 AWS認定資格試験テキスト　AWS認定ソリューションアーキテクト - アソシエイト　改訂第2版 こっちはいわゆるオレンジ本ってやつ（？） ②問題集で演習 AWS WEB問題集で学習しよう ③BlackBeltを見る AWS サービス別資料 これだけです。 色々なところで以下のハンズオンがおすすめされていて私も購入しましたが、少しだけ見てすぐやめました。 これだけでOK！ AWS 認定ソリューションアーキテクト – アソシエイト試験突破講座（SAA-C02試験対応版） もちろんハンズオンをやるに越したことはないのですが、私の場合は、以下の理由でやりませんでした。 　・試験まで1カ月しかなく、十分な時間を作れなかった 　・講師の説明などが台本を読んでいるだけっぽく聞こえ、内容が全く頭に入ってこなかった ハンズオンやるだけならこの講座は、まぁアリかもしれませんが、 これだけで合格とかは厳しいと思うので、この講座の評価は個人的には低いです。 「買ったのに全部やらなかったら勿体ない…」と思うかもしれませんが、自分合わないものを無理にやる必要はないので別のことをしましょう。 2-1.参考書 参考書での学習の流れは以下の通りです。 オレンジ本を1周して「サービスごとの役割」について学ぶ ⇒黒本で不足していたサービスの部分や理解が曖昧なところを図などを見て復習 ＆AWS Well-Architected フレームワークとの紐づけをする ⇒（～問題集やりながら間違えたサービスのところを読む） 私の場合、参考書を２つ利用していましたが、実際１つだけで十分だと思います。 以下にそれぞれの特徴を記載しておきます。 ・改訂新版 徹底攻略 AWS認定 ソリューションアーキテクト − アソシエイト教科書［SAA-C02］対応 徹底攻略シリーズ いい点 ・AWS Well-Architected フレームワークに沿って記載されているので、この観点を常に意識しながら知識をつけることができる ・ところどころ注釈がページ下についている（特に私はRoute53のレコードのところが助かった） ・挿入されている図がわかりやすい イマイチな点 ・一部、簡易的にしか記載されていないサービスなどもあるので、詳しく知りたいときは都度調べることになる ・AWS認定資格試験テキスト　AWS認定ソリューションアーキテクト - アソシエイト　改訂第2版 いい点 ・かなり優しく書いてある ・コンピューティング、ストレージ…などサービスごとにまとまっているため、辞書的に使うことも可能ではある イマイチな点 ・上記の黒本よりも情報量が少ない ・Aurora Serverlessなどの情報が載っていない どちらも使ってみた感想としては、基本は黒本でよいと思います。 IT未経験とかAWS超初心者の方とかはオレンジ本から始めるとよいです。 知識を付けるうえで意識した方がいいこと そのサービスで何ができるかだけではなく、このサービスはグローバルなのかリージョンなのかそれともAZなのかということは意識した方がいいです。 これをわかっているだけで選択肢を絞ることもできます。 2-2.問題集で演習 学習方法としては ひたすら問題を解く→わからないところは参考書とかで調べる これだけです。 私の場合、どの問題集がいいんだろう…とかなり迷っていましたが、以下の問題集をやっておけば大丈夫です。 AWS WEB問題集で学習しよう 特徴を記載しておきます。 いい点 　・問題文が本番試験とかなり似ている 　・1問ごとに正解と解説を見ることができるので便利 　・問題量がかなり多い 　・小分けでも演習ができるし、本番と同じように65問の模擬演習もできる イマイチな点 　・AWSのドキュメントをそのまま解説に持ってくることもあるので、わかりにくかったら自分で調べる必要がある（勉強方法としては普通ですが） 参考書とかについている問題とは難易度が違い過ぎるので、それに慣れている人は最初はボロボロだと思います。 とにかく問題解きまくって知識を定着させましょう！ 2-3.BlackBeltを見る 中にはBlackBeltまで見なくていいよ～とかいう方もいると思いますが、自分は一定の知識ついたら見ることをお勧めします。 何も知らない状態で見ると「（？。？）」状態だったんですが、ある程度知識がついたときに見たらスルスル入ってきました。 また、参考書ではカバーしきれていないところも説明があるので、詳しく知っておきたい人、ばっちり対策したい人はおススメです。 （私はELBやRoute53あたりが参考書だけでは足りないと思ったので見ました） 3.当日のこと 当日は田町のテストセンターで受験しました。 静かな中でテストを受けれたので集中できました。 試験を受けるうえでのアドバイス トイレには事前にいっておきましょう 試験時間は長いし、基本途中でトイレはいけないです。 ※カフェインが入っているドリンクの飲みすぎ注意！ ちなみに私は最近頻尿気味です（25歳なんだけどな・・・） 最初のガイドはちゃんと読もう 重要なことも書いてあります。読み逃さないようにしましょう。 わからない問題はいったん飛ばそう！ わからない問題を長く考えて、後ろの問題で時間が足りなくなって落ちた！とかなったら元も子もないです。 色々な合格体験記で「最初の10問が難しかった」と記載があり、 実際、私の時も最初の5問は選択肢が絞り切れなかったので、すぐに飛ばしました。 最初の10問くらい難しかったらそういうものと思いましょう。 問題文はちゃんと読もう！ 一答なのか多答なのかちゃんと確認しましょう。 「ん？」と思ったら問題文を英語にして！！！（重要） 左上だか右上だかに英語の問題文を表示するようなボタンがあります。 私が受けた試験では一答なのに日本語翻訳だと合っている選択肢が２つ同時に存在している問題がありました。 （もともと英語の試験を日本語に翻訳しているのでしょうがないかな） ちゃんと勉強してなかったら気づけなかっただろうな～って思います。 このようなこともあるので、合っている選択肢が1番はじめにあっても、他の選択肢も絶対に確認しましょう！ 見直しはしっかりしよう これまた当たり前ですが、ちゃんと見直しましょう。 すべて見直して「イケる！」と思ったら最後にアンケートに答えて終了です。 アンケートの回答が終わったら画面に合否が出ます。 （自分はアンケート答えてるときめちゃくちゃドキドキしました。） おわりに 試験日までは業務もあるし、空いた時間はすべて勉強に回していたので正直カラダがきつかったです。 ですが、この試験に向けて勉強したおかげで苦手だったNWの知識が少しずつついてきたり、何より AWSにさらに興味を持つことができたのがとてもよかったと思います。 これからAWSを使う方、AWSに興味がある方はぜひ取得を目指してみてほしいです。 記事の中で何かわからないことがあれば、コメントいただければと思います。

勉強前イメージ サービス名から内容があまりわからない・・・ 調査 AWS Outposts とは ローカル環境でのデータ処理や移行、低レイテンシーを維持したいという目的で 専用のハードウェア一式をオンプレの環境に設置して、オンプレにAZが増えたイメージで扱うサービスになります。 AWSのオンプレミス版のイメージの製品になります。 AWS Outpostsを注文すると以下のものがラックに組み立てられてが輸送・搬入されるようです。 - 42Uのラック - パッチパネル - サーバホスト - ネットワークSW - 電源一式を 画像は こちら をごらんください 日本でも注文は出来ます！ また、outpostsを利用するには エンタープライズサポートプラン に入る必要があります。 AWS Outposts のメリット 自社DCでAWSサービスを使用できる OutpostsはAWSから送られてきた物理サーバを使ったサービスで 設置から運用・管理までフルマネージドのサービスになります。 AWSサービスを自社のDCで実行できます。 例えば以下のようなサービスを自社のDCで使用することができます。 ・ EC2 ・ EBS ・ s3 ・ EMR オンプレとクラウドで一貫して使用できる Outpostsのメリットは自社DCという独自性を保ちながら サーバ・ラック、その他ネットワーク機器の保守等はAWSが行うため、実質クラウドと変わらない環境で利用できます。 勉強後イメージ AWSサービスを利用したいけど、 色々制限があって自社内に閉じたところしかだめ！みたいなところだったらすごいいいと思う エンタープライズ入らないといけないってことはお金が結構掛かりそう 参考 AWS Outposts AWS Outpostsとは？ハードウェアでAWS機能を利用できる AWS Outpostsをあらゆる場所に！1U、2UサイズのOutpostsが登場します #reinvent

はじめに AWS プロファイルを指定するだけでさくっと ECR ログインを実現する手段が欲しかったのでスクリプトを用意した。 背景 AWS CLI で ECR ログインする際に利用されていた get-login は AWS CLI v1 では非推奨、AWS CLI v2 ではそもそもコマンド自体が使えなくなっている。代わりに aws ecr get-login-password を利用するようにとのこと。 で、get-login-password を利用して ECR ログインするコマンドを素直に書くと以下のようになる。 $ aws ecr get-login-password | docker login --username AWS --password-stdin <registry url> しかし <registry url> が厄介で、デフォルトのプライベートレジストリ URL は https://<aws_account_id>.dkr.ecr.<region>.amazonaws.com である。 そう、AWS プロファイルが複数あってログイン先を必要に応じて切り替えたいとき、レジストリ URL はプロファイル毎に基本違うわけで aws ecr の方だけ --profile <profile> オプションを指定しても意味がない。 対応方法 aws sts コマンドでアカウントIDを取得できるので、これを組み合わせて以下のような簡単なスクリプトを用意した。 ecr-login.sh #!/bin/bash set -eu readonly aws_profile=$1 readonly aws_account_id=`aws sts get-caller-identity --profile=${aws_profile} | awk ' $1 == "\"Account\":" { gsub(/\",*/, ""); print $2 } '` aws ecr get-login-password --profile ${aws_profile} | docker login --username AWS --password-stdin https://${aws_account_id}.dkr.ecr.ap-northeast-1.amazonaws.com これで $ ./ecr-login.sh <aws_profile_name> とかを実行すればプロファイル指定で ECR ログインできるようになる。function 化して .rc に書くのもよいかもしれない。

はじめに 以下のブログを参考に SAM を Terraform に組み込んでみたところ、 色々苦労することになったので備忘録を残します 実行環境 Terraform: 1.0.6 Terraform providers: aws: 3.58.0 local: 2.1.0 null: 3.1.0 SAM: 1.24.1 背景 AWS Lambda のデプロイに昔は apex を使っていましたが、メンテナンスされなくなってしまいました 昔は他に便利なツールもなかったため、 Docker コンテナで依存ライブラリをインストールしてから圧縮するスクリプトを自前で組みました それを Terraform から呼んで関数毎にビルド・デプロイする方法です 自前実装で長く運用してきたものの、 やはり Lambda 周りが無駄に複雑になってしまっているため、 公式の Lambda Layer や SAM を使って正しくデプロイしようと考えました 長さ制限 最初にぶち当たったのは長さ制限でした 既存システムの Lambda 関数は数が非常に多く、まずそれらを SAM 用に書き下すだけでも大変でした ようやく全て書いて実行すると、早速エラーが発生 template_body に指定できる長さの上限が 51,200 バイトだったために、実行できなかったのです そこで、まずは API Gateway から呼び出される Lambda とそれ以外（定時実行など）を分離しました それでもやはり API の関数が多い、、、 元は GET や POST などのメソッド毎に別関数にしていたのですが、 パス毎、メソッド毎だと、どうしても多くなってしまいます というわけで、 GET でも POST でも同じパスに来たら同じ関数で処理するようにして、 関数内でメソッドを判別して処理を振り分けるようにしました def handle(event, _): if event["httpMethod"] == "GET": ... if event["httpMethod"] == "POST": ... これで関数が少なくなり、とりあえず SAM の実行まではできるようになりましたが、、、 Common Layer 元々、データベースアクセスやロギングなどの共通関数は ビルド時に各関数のディレクトリー内にコピーしていました 最初は「これはしょうがないか、、、」と思ってシェルスクリプトでコピーを組んでいたのですが、 Lambda Layer なるものの存在を初めて知りました しかも、依存パッケージもこちらに入れられるという、、、 テンプレートに以下のように Layer を定義し、各関数から参照します Layer 用のディレクトリ（以下の例では ../sam-common/） に 共通関数の各 Python ファイルと requirements.txt を入れておけば、 あとは SAM が上手くやってくれます CommonLayer: Type: AWS::Serverless::LayerVersion Properties: LayerName: !Sub ${ServiceName}-api-common-layer Description: common layer for API ContentUri: "../sam-common/" CompatibleRuntimes: - python3.8 Metadata: BuildMethod: python3.8 XXXFunction: Type: AWS::Serverless::Function Properties: FunctionName: !Sub ${ServiceName}-xxx CodeUri: functions/xxx/ Role: !GetAtt XXXFunctionRole.Arn Layers: - !Ref CommonLayer 昔からこれがあれば苦労しなかったのに、、、 同時実行エラー 上記の問題により、 SAM のテンプレートは API と Others に分かれました しかし、それぞれの SAM を個別に実行するとエラーにならないのに、 なぜか Terraform で実行すると謎のエラーが、、、 OSError: [Errno 66] Directory not empty:... しばらくはさっぱり理解できませんでしたが、実行状況を眺めてようやく理解できました Terraform はできるだけ効率的に処理するため、 並列実行可能な箇所は同時に動かしてしまうのです null_resource から SAM のビルドを実行しているため、 Terraform からは並列実行できないものとは見なされません SAM が API 用と Others 用で同時に動いた結果、 同じキャッシュディレクトリーを参照し、エラーになっていたのです 並列実行させないためには、依存性を入れて必ず順序を守らせるしかありません Others 用が終わってから、 API 用が動くようにしました resource "null_resource" "sam-api-build" { ... depends_on = [ null_resource.sam-others-build, ... ] } ただし、これには問題があって、 Others 用の変更があった場合、 API 用も無駄に動いてしまうのです どうしても、これは回避できません 本番用とステージング用がある場合、ステージング用を本番用に依存させることで、ステージング適用時はステージングのみ、本番適用時は本番＋ステージングが動くようになります 逆にするとステージング適用時に本番の方も動いてしまうので注意 おわりに 他にも細かいエラーが色々あり、結構時間がかかってしまいました 依存性を入れたり、結局、むしろ前より複雑になってしまったのでは、、、、 SAM は単体で運用するならベストですが、 Terraform との相性はあまり良くなさそうです Layer に関しては1番の収穫だったので、今後は SAM はともかく Layer は必ず使います

Fargateスケジュールタスクとは Amazon EventBridgeのスケジューラを使って、ECS(Fargate)のタスクを定期実行する仕組みです。 [参考] https://docs.aws.amazon.com/ja_jp/AmazonECS/latest/userguide/scheduled_tasks.html 結論 2021年10月現在、タスク起動時に環境変数をS3から取得するようにタスク定義を設定している場合は、タスクの実行に失敗することがあります。 （今後AWS側に修正してもらえる可能性はあります。） なお、調査の余地がありますが、 VPCエンドポイント・NAT・パブリックIPの有無などの環境の違いで発生頻度に差がある可能性があります。 詳細 一つずつ、原因を探っていきます。 RunTaskが実行されているか確認 CloudTrailにてRunTaskイベントを確認 CloudTrailをオンにしている場合、各APIの実行履歴を取得できます。 コンソール画面の場合、イベント履歴からイベント名「RunTask」でフィルターしましょう。 もしイベントが記録されている場合、タスク自体は起動されたことがわかります。 CloudWatchメトリクスでInvocationを確認 EventBridgeのメトリクスで、スケジュールタスクが呼ばれていたかどうかを確認します。 こちらも、Invocationsが記録されている場合、タスク自体は起動されたことがわかります。 タスク停止理由をログに記録する ここまで、特にアプリケーションが実行されなかった理由は見当たりませんでした。 しかし、スケジュールタスクのアプリケーションは間違いなく実行されていません。 つまり、タスクを起動したが、アプリケーションの実行前に停止したことが考えられます。 そこで、タスク停止ログを記録するためのCloudWatchLogsを作成します。 CloudFormationにてタスク停止ログを記録する仕組みを構築 以下のCloudFormationテンプレートを使用して仕組みを構築します。 https://github.com/aws-samples/amazon-ecs-stopped-tasks-cwlogs 以下の記事も参考にさせていただきました。(ありがとうございます！) https://dev.classmethod.jp/articles/tsnote-ecs-how-can-i-store-the-reason-for-stopping-ecs-tasks-in-cloudwatch-logs/ タスク停止理由にてエラーログを確認 停止ログが記録されるようになったので、実際に確認します。 すると… ResourceInitializationError: failed to download env files: file download command: non empty error stream: RequestCanceled: request context canceled caused by: context deadline exceeded 停止理由（stoppedReason）にてエラーメッセージを確認できました。 タスク起動時に環境変数をS3から取得するように設定しており、稀に取得できないことがあるようです。 コンソール画面でのタスク定義箇所はこちらです。 タスク定義ファイルでいうと以下の部分が該当します。 "containerDefinitions": [ { "dnsSearchDomains": null, "environmentFiles": [ { "value": "arn:aws:s3:::xxxxxxxxxx/.env", "type": "s3" } ], ... 備考 2021年10月23日現在、以下のIssueが発行されており、続報が待たれています。 https://github.com/aws/aws-cli/issues/6470

MFA認証が必要なAWSアカウントに対してWEB用SDKのS3Clientでアクセスする方法です。 AWSリソース間でのアクセスはRoleでの権限設定が推奨されていますが、例えばローカル開発環境からテストでS3にアクセスするとき、AWS_SESSION_TOKENの設定が必要になります。 対策はaws_session_tokenを配列に指定してオブジェクトを生成するだけです。 $s3 = new S3Client([ 'aws_access_key' => env('AWS_ACCESS_KEY'), 'aws_secret_key' => env('AWS_SECRET_ACCESS_KEY'), 'aws_session_token' => env('AWS_SESSION_TOKEN'), 'region' => env('AWS_DEFAULT_REGION'), 'version' => '2006-03-01' ]); session tokenの取得はcliから。 https://aws.amazon.com/jp/premiumsupport/knowledge-center/authenticate-mfa-cli/

作ってみたもの 作ったWebアプリは Graphyee と名付けました。 技術と技術は関連しているものなので、「スキルマップをグラフ構造で表現できたら分かりやすくて面白いかな」と思いついたのが経緯です。 まだβ版としていますが、一旦使える感じになったのでノリと勢いで公開してみました。 こんな感じで サンプル は誰でも使えます。 サンプルは実績を入力しても保存できないのでご注意下さい。 ユーザ認証すると、より細かい スキルマップ が使えます。 ユーザ認証していただければ、入力した実績が保存されます。 上記リンクからだとAuth0の認証画面へのリダイレクトにやや時間がかかりますが、そのうち認証画面が出てきます。 技術構成 せっかく作ったので、どうやって作ったかを簡単にご紹介します。 フロントエンド Vue 2.6.11 Vuetify 2.4.0 Vuex 3.6.2 axios 0.21.1 Cytoscape.js 3.18.1 Auth0 VueでSPAを作り、UI周りのデザインはVuetifyを活用しました。バックエンドのAPI呼び出しはaxiosです。 Cytoscape.jsを使ってグラフ構造を実現しています。jsonでnodeとedgeを定義するとグラフが描画されます。 sample_node [{"id":"1","name":"node1"},{"id":"2","name":"node2"},{"id":"3","name":"node3"}] sample_edge [{"source":"1","target":"2"},{"source":"2","target":"3"}] 認証機能は、アプリから切り離したかったのでAuth0で実現しています。 Silent AuthenticationやRefresh Token Rotationはこのサイトが理解しやすかったです。 バックエンド Go 1.16 Gin 1.7.1 GORM 1.21.11 フロントエンドから呼び出されるAPIは、GoとGinで実装し、GORMでDBと接続しています。 Goはパッケージ管理の考え方がまだ模索中なのかな？って印象ですが、そこさえちゃんと理解できれば早く立ち上がるので気に入ってます。Ginも簡単なWebアプリ作るなら一瞬だったので、いい感じです。世の中にサンプルも多そうなでキャッチアップしやすい印象です。 GORMはいわゆるORMのクセみたいなのはありましたけど、普通に使う分には問題なく使えました。 最終的には、生のSQLを書きたくなるんですけどね。 インフラ インフラはAWSで構築しました。コンテナ化や触ったことないサービスを使ってみようかなとも思いましたが、個人的に一番立ち上がりが早いサービスを選択しています。 ALB＋EC2＋RDSというとてもシンプルな構成です。 ここらへんを見ながら何使うか一瞬悩みましたが、また今度挑戦しようかなと思います。 なぜ作ろうと思ったのか IT業界で働いていて、「結局、具体的に何をどこまで経験してきて、何が不足しているのか？」を常々考えていたのが根底にあります。さらに、ある技術を網羅的に学んで仕事に活かすことが、自分にとって「塗り絵」をしていくイメージだったのも強く影響しています。 ただ、いちいち思い出して次は何しようかを考えたり、実績を誰かに伝えたりすることは中々面倒くさいのです。そこで、スキルマップを分かりやすく管理できればいいのでは？と思い立ちました。 使えそうな場面 例えばこんなシーンで使えるのかなと想像しています。是非、継続的に使ってみてください。 特にこれから勉強しようとしている人、勉強中の人向けに使えるのかなと想像しています。 学習や経験が網羅的にできているか知りたい。 学習や経験を計画したり、振り返ったりしたい。 今後どんなことを学習・経験すべきか知りたい。 自分や誰かの足りない部分を発見したい。 使ってみた方へお願い 求められるものが何なのか、今後作り続ける価値があるのか知りたいので、よろしければアドバイスいただけると嬉しいです！ 今後 やりたいことや検証してみたいことはたくさんあります。 もし良いフィードバックをいただけたら、下記以外にも色々試してみたいと思います。 コンテンツがJavaだけで寂しいので追加したい。 認知度を向上して色々フィードバックをもらいたい。 誰かのスキルマップとの比較機能を付けたらありがたがられるのか。 API Gateway＋コンテナ（Lambda/Fargate）にしたら嬉しいのか。 Github ActionsでCI/CDしたら楽になるのか。 ランニングコストぐらい回収できる感じにしたい。 課金して使える機能と課金の仕組みを入れてみたい。 最後に 作っていて記事になりそうなネタがあればまた投稿するので、その時にまたお会いしましょう。最後までお読みいただき、ありがとうございます！

概要 EC2のインスタンスをterraformで立てます。 立てて、落として、また立ててもすぐに利用できるようにします。 terraform どんなものかについては下記を参照。 https://qiita.com/yamanashi7474/items/d12961780ad296335ed2 事前確認 terraformをローカル環境にインストールします。 https://docs.uipath.com/installation-and-upgrade/lang-ja/docs/installing-terraform 上記手順をそのままなぞるとバージョン0.12.3を落とせますが、現在0.14.Xまでバージョンが上がってます。 下記を参考にしてください。 $ wget https://releases.hashicorp.com/terraform/0.14.10/terraform_0.14.10_linux_amd64.zip $ sudo apt-get install unzip $ unzip terraform_0.14.10_linux_amd64.zip $ sudo mv terraform /usr/local/bin/ yamanashi@DESKTOP-9KCV2CQ:~/studymyself$ terraform -version Terraform v0.14.10 Your version of Terraform is out of date! The latest version is 1.0.7. You can update by downloading from https://www.terraform.io/downloads.html versionの確認が出来れば導入okです。 作業ディレクトリの作成 terraformの運用にディレクトリ構成はかなり重要なポイントになります。 適当な運用をすると意図せぬ変更を意図していない環境に当ててしまいかねないので分けておきましょう。 今回は下記構成のmake_ec2_01ディレクトリを作業ディレクトリとします。 このページでは以後は何か記載しない限り常に作業ディレクトリで作業を行っている前提とします。 yamanashi@DESKTOP-9KCV2CQ:~/studymyself$ tree ./ ./ ├── study_ec2.pem └── terraform └── aws └── make_ec2_01 3 directories, 1 file .tfファイルの作成 .tfファイルは命名規則などの縛りは有りません。 膨大なリソースを一つのファイルに記載しても、細かく切り分けて細分化させても問題ありません。 リソース管理の観点からリソースごとに.tfファイルを分けると使いやすいと思います。 .tfファイルの書き方は非常に簡単です。terraformに何を作るのかを伝える為のデータシートですので、鍵括弧の閉じ方やデータの渡し方にルールはありますが基本的にはAWSの各種リソース(EC2インスタンス/vpc/セキュリティグループ等)単位で書きます。 resourceで始まるブロックが一つの塊です。 resource　"AWSリソース名" "任意の名前" { 設定1 = "設定値" 設定2 = 設定値 設定3 = [ "aaa","bbb" ] ブロック { 設定4 = 設定値 } tags = { Name = "test-tag" } } AWSリソース名というのは、AWSで作成できる様々なものを指す固有のワードです。後述してますがvpcならaws_vpc、ec2インスタンスならaws_instanceと決められた固有のコードが入ります。 作りたいもののリソース名は公式サイトで探せば確認できます。google検索をかけるだけでも普通に出てきます。 設定Xは設定項目の事を差します。設定値は然りです。 注意としては設定値の渡し方についてはいくつか種類があります。これも公式サイトで調べると設定項目ごとに渡せる設定値と渡すときのルールが記載されています。 設定値の中には作成したリソースのidなどもあります。terraformでは下記のような書き方をすることでterraformで作成したリソースの情報を渡すことができます。 AWSリソース名.任意の名前.設定名 例：aws_vpc.test-vpc.id ブロックは複数の設定をくくる箱のようなものです。ブロックに対して直接設定値を設定することはありませんが、ブロックの中に書く必要のある設定があります。 tagについてはAWSの多くのリソースにつけることができるtagです。上記の例だとNameというtagを作ってそれにtest-tagという文字列を入れています。よくある使い方なので記載しました。 このresourceを必要な分だけ記載したものが.tfファイルになります。 記載するresourceの順番や、resource内の設定の順番などは気にする必要はありません。とにかく.tfファイルに書いてさえあればterraformコマンドを実行した際に読み取られます。 基本的なterraformコマンド (fmt / plan / apply / destroy) 具体的な.tfファイルの作成に入る前に、よく使うerraformコマンド(terraform fmt / terraform plan / terraform apply / terraform destroy)について記載します。 terraform fmt terraform fmtは.tfファイルのインデントを整えてくれます。 before resource "aws_vpc" "test-vpc" { cidr_block = "10.40.0.0/16" instance_tenancy = "default" enable_dns_support = true enable_dns_hostnames = true tags = { Name = "test-main" } } after resource "aws_vpc" "test-vpc" { cidr_block = "10.40.0.0/16" instance_tenancy = "default" enable_dns_support = true enable_dns_hostnames = true tags = { Name = "test-main" } } 整形されたファイルはプロンプトにファイル名のみ出力されます。 yamanashi@DESKTOP-9KCV2CQ:~/studymyself/terraform/aws/make_ec2_01$ terraform fmt vpc.tf terraform plan terraform planはterraformが作業ディレクトリの.tfファイルを読み取り、作成するリソースの一覧を画面に表示します 長いので例をここに記載しませんが、.tfファイルを基に作成されるリソースを出力するだけで何か変更が行われるという事はありません。作成前に確認する目的で実行します。 terraform apply terraform planで作成予定のリソースに問題が無ければ、terraform applyでリソースを作成します。 実行するとplanで見たのと同じ作成されるリソースの設定一覧と、最後に下記のように作成するかしないか入力を求められます。 yesを入れれば作成が開始され、それ以外では作成されず終了します。 ~snip~ Do you want to perform these actions? Terraform will perform the actions described above. Only 'yes' will be accepted to approve. Enter a value: これにyesを入力した場合、暫くの時間経過の後に下記の様に作成(added)、変更(changed)、削除(destroyed)をそれぞれいくつ実行したのかという結果が出力されます。 Apply complete! Resources: 2 added, 0 changed, 0 destroyed. .tfファイルのリソースの記載が間違っていた場合、エラーが出た箇所とある程度の原因が出力されますので確認の上.tfファイルを修正してください。 -targetオプションを用いて.tfファイルに記載したリソースの一部のみを作成することも可能です。 terraform destroy terraform destroyは読んで字のごとくリソースを削除します。 注意しなくてはならない点として、terraform destroyをそのまま実行した際に削除対象となるのはterraform applyで作成した全リソースが対象となります。 全てを削除する意図は無く、特定のリソースのみを削除する際には-targetオプションを用いて対象を限定してください。 また、destroyは-targetで対象を指定したとしてもその対象に紐づけられて作成されたリソースはまとめて削除されることがあります。実行前に何が消えるのかよく確認した上でyesを入力してください。 .tfファイルを作成する それではterraformを使い始める準備が整いましたので、早速AWSのリソースを作成していきます。 provider.tfの作成 provider.tfはterraformの対象になるクラウド環境の情報が必要です。今回はAWSの情報と、使用するアカウント情報を書き込みます。 [yamanashi@ip-172-31-46-224 test01]$ cat provider.tf provider "aws" { version = "~> 2.0" region = "ap-northeast-1" access_key = "AKIXXXXXXXXXXXXXXXX" secret_key = "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx" } access_keyとsecret_keyは各々で取得する必要があります。 awscliの設定を入れた際に調べたアクセスキーとシークレットアクセスキーに置き換えてください。 terraformの初期設定 (terraform init) terraformの初期設定はterraform initを実行すれば完了します。 terraform initは初回に実行するコマンドでして、これを実行するとprovider.tfを読み取って必要なコンポーネントを自動で取得してくれます。今回はAWSを触ることを宣言してますのでawsを操作するのに必要なコンポーネントを自動で取得してくれます。 一度実行したらそのフォルダの.tfファイルを更新しても再実行する必要はありません。ただしprovider.tfを更新して使用するコンポーネントを追加したりした場合には実行する必要があります。複数回実行しても問題は起きません。 このterraform initを作成したprovider.tfが存在するディレクトリで実行してください。 yamanashi@DESKTOP-9KCV2CQ:~/studymyself/terraform/aws/make_ec2_01$ terraform init Initializing the backend... Initializing provider plugins... - Finding hashicorp/aws versions matching "~> 2.0"... - Installing hashicorp/aws v2.70.0... - Installed hashicorp/aws v2.70.0 (self-signed, key ID 34365D9472D7468F) ~snip~ Terraform has been successfully initialized! You may now begin working with Terraform. Try running "terraform plan" to see any changes that are required for your infrastructure. All Terraform commands should now work. If you ever set or change modules or backend configuration for Terraform, rerun this command to reinitialize your working directory. If you forget, other commands will detect it and remind you to do so if necessary. Terraform has been successfully initialized!が表示されればokです。 これでprovider.tf以外の.tfファイルの作成を開始できます。 vpc.tf vpcはAWSの仮想ネットワークを指します。ec2インスタンスを立てるために作成します。 また、AWSではvpcのネットワーク直下にec2インスタンスを置くことはできません。サブネットも必要になりますのでvpc.tfに二つとも記載します。 vpc.tfを作成し、下記のコードを入力してください。 resource "aws_vpc" "test-vpc" { cidr_block = "10.40.0.0/16" instance_tenancy = "default" enable_dns_support = true enable_dns_hostnames = true tags = { Name = "test-main" } } resource "aws_subnet" "test-subnet-a" { vpc_id = aws_vpc.test-vpc.id cidr_block = "10.40.0.0/24" availability_zone = "ap-northeast-1a" map_public_ip_on_launch = true tags = { Name = "test-subnet-a" } } igw.tf vpcをインターネットに接続するためにインターネットゲートウェイが必要です。 igw.tfを作成し、下記のコードを入力してください。 resource "aws_internet_gateway" "test-igw" { vpc_id = aws_vpc.test-vpc.id tags = { Name = "main-igw" } } vpc_idには先ほど作成したvpcのidを入れる必要があります。 前述しましたが、上記の書き方をすると作成したtest-vpcのidを渡すことができます。 rt.tf 外向けの通信が発生した際に、それをインターネットゲートウェイに向けるためのルートテーブルが必要です。 rt.tfを作成し、下記のコードを入力してください。 resource "aws_route_table" "test-routetable" { vpc_id = aws_vpc.test-vpc.id tags = { Name = "test-routetable" } } resource "aws_route" "route-to-igw" { route_table_id = aws_route_table.test-routetable.id gateway_id = aws_internet_gateway.test-igw.id destination_cidr_block = "0.0.0.0/0" } resource "aws_route_table_association" "rt_association" { subnet_id = aws_subnet.test-subnet-a.id route_table_id = aws_route_table.test-routetable.id } これ、terraformのめんどくさいポイントなんですがaws_route_tableというルートテーブルを作成した後にaws_routeでルートを作成してます。さらにaws_route_table_associationはルートテーブルとサブネットを紐づけるリソースです。 つまりaws_routeやaws_route_table_associationはイメージしやすい実体を持っていません。 これがaws_route_table これがaws_route これがaws_route_table_association こういうリソースの書き方を求められるものは他にもあります。そして多くの場合、調べ辛いです。 sg.tf セキュリティグループは通信のルールそのものです。中に入る通信(inbound)と外に出る通信(outbound)の二つがありますがそれぞれに何の通信を許可するか記載したルールそのものです。 今回はec2インスタンスに使うセキュリティグループを作成します。 sg.tfを作成し、下記のコードを入力してください。 resource "aws_security_group" "test-sg" { name = "test-sg" vpc_id = aws_vpc.test-vpc.id description = "test sg" } resource "aws_security_group_rule" "inbound_ssh" { type = "ingress" from_port = 22 to_port = 22 protocol = "tcp" cidr_blocks = [ "0.0.0.0/0" ] security_group_id = aws_security_group.test-sg.id } resource "aws_security_group_rule" "outbound_all" { type = "egress" from_port = 0 to_port = 0 protocol = "-1" cidr_blocks = [ "0.0.0.0/0" ] security_group_id = aws_security_group.test-sg.id } test-sgというセキュリティグループを作成し、それに1つのinboundと1つのoutboundのルールを作成しています。 外部からsshで接続するためのルール：aws_security_group_rule.inbound_ssh 内部から外部に接続するためのルール：aws_security_group_rule.outbound_all かなりガバガバなセキュリティグループですが、一旦これで進めます。 ec2.tf ec2の主役、インスタンスを作成します。外部から接続するためのElasticIPアドレスを取得して作成したインスタンスに紐付けもします。 ec2.tfを作成し、下記のコードを入力してください。 resource "aws_instance" "terraform-ec2-01" { ami = "ami-02892a4ea9bfa2192" availability_zone = "ap-northeast-1a" instance_type = "t2.micro" key_name = "study_ec2" subnet_id = aws_subnet.test-subnet-a.id vpc_security_group_ids = [aws_security_group.test-sg.id] associate_public_ip_address = false private_ip = "10.40.0.10" root_block_device { volume_type = "gp2" volume_size = 8 delete_on_termination = true } tags = { "Name" = "terraform-ec2-01" } lifecycle { ignore_changes = all } } resource "aws_eip" "ec2-eip" { instance = aws_instance.terraform-ec2-01.id vpc = true } aws_eipはAWSで取得できるElasticIPです。無いとは思いますが取得だけして使用しないと支払いが発生します。 key_nameで接続に使用するキーペアを指定しています。これは下記ページで作成したキーペアを指定していますが任意の物に変えてもらって結構です。事前に作っておく必要がありますので、作成していない方は次に進む前にキーペアを作成してください。 リソースを作成する .tfファイルの作成が完了しましたので、terraformで作っていきます。 現時点で6つの.tfファイルが存在しているはずです。 yamanashi@DESKTOP-9KCV2CQ:~/studymyself/terraform/aws/make_ec2_01$ tree . ├── ec2.tf ├── igw.tf ├── provider.tf ├── rt.tf ├── sg.tf └── vpc.tf 0 directories, 6 files まずterraform planを実行してください。 リソース名や設定名に何か誤記があればここで弾かれます。適宜修正してください。 問題が無ければterraform applyを実行しましょう。 yamanashi@DESKTOP-9KCV2CQ:~/studymyself/terraform/aws/make_ec2_01$ terraform apply ~snip~ Plan: 11 to add, 0 to change, 0 to destroy. Warning: Version constraints inside provider configuration blocks are deprecated on provider.tf line 2, in provider "aws": 2: version = "~> 2.0" Terraform 0.13 and earlier allowed provider version constraints inside the provider configuration block, but that is now deprecated and will be removed in a future version of Terraform. To silence this warning, move the provider version constraint into the required_providers block. Do you want to perform these actions? Terraform will perform the actions described above. Only 'yes' will be accepted to approve. Enter a value: yes ~snip~ Apply complete! Resources: 11 added, 0 changed, 0 destroyed. 11個のリソースを作成して完了です。 作成したインスタンスに接続する 少しブラウザでの作業を含みます。 AWSマネジメントコンソール -> EC2 -> インスタンスの順に移動し、作成したインスタンスを選択して右上の接続を押してください。 SSHクライアントタブを選択し、下部のssh接続コマンドをコピーしてください。 コピーしたコマンドをキーペアを格納したディレクトリで実行すれば作成したEC2にログインできます。 yamanashi@DESKTOP-9KCV2CQ:~/studymyself$ ls study_ec2.pem terraform yamanashi@DESKTOP-9KCV2CQ:~/studymyself$ ssh -i "study_ec2.pem" ec2-user@ec2-54-150-79-122.ap-northeast-1.compute.amazonaws.com The authenticity of host 'ec2-54-150-79-122.ap-northeast-1.compute.amazonaws.com (54.150.79.122)' can't be established. ECDSA key fingerprint is SHA256:9u4mfukp5J4XzjmVhrE8JcLY9GiELAVbG+OByyHJ4vA. Are you sure you want to continue connecting (yes/no/[fingerprint])? yes Warning: Permanently added 'ec2-54-150-79-122.ap-northeast-1.compute.amazonaws.com,54.150.79.122' (ECDSA) to the list of known hosts. __| __|_ ) _| ( / Amazon Linux 2 AMI ___|\___|___| https://aws.amazon.com/amazon-linux-2/ 12 package(s) needed for security, out of 42 available Run "sudo yum update" to apply all updates. [ec2-user@ip-10-40-0-10 ~]$ [ec2-user@ip-10-40-0-10 ~]$ ログインできました。 再作成してもう一度ログインする ログイン出来たら、今度はこのインスタンスをterraformで削除して再度作成して見ましょう。 やり方は作業ディレクトリでterraform destroyを実行し、全てを削除し終わったら再度terraform applyを実行します。割り振られるDNSは変わるのでsshコマンドはもう一度取得するか適宜修正してください。 このようにterraformでは基盤の削除も作成もワンタッチでできるようにする事で、問題が起きた際のリカバリーを迅速に行うことができます。 規模は小さいですが、同じことを提供するシステムの基盤でやれるようにするのがterraformを用いた運用の目標の一つだと思います。

はじめに 環境は以下 Next.js CloudFront+S3 pages/index.tsxとpages/new.tsxを持つ Next.js を静的ビルドすると、ビルド結果は/index.htmlと/new/index.htmlが生成される URL の/にアクセスした時は、CloudFront で Default Root Object に index.html を指定しておけば、https://.../index.htmlを返してくれるので普通に表示されるが、/newにアクセスすると CloudFront は 403 エラーを返す この場合、/newのアクセスに対して、https://.../new/index.htmlを返すようにしてあげれば、問題なくリソースを表示することができるようになる これは CloudFront の Edge Location において、/newという URL を/new/index.htmlに書き換えるスクリプトを実行してあげることで解決ができる これは調べるとよく紹介されているやり方で、AWS の Edge 関数のコード例にも記載されている https://docs.aws.amazon.com/ja_jp/AmazonCloudFront/latest/DeveloperGuide/example-function-add-index.html ※ この 403 エラー問題の解決方法でいうと URL 書き換え以外に、「CloudFront ではなく、別の CDN サービスを使う」や「S3 の Static Website Hosting を設定する」など別の解決方法もある 今回実現したいことは以下になる CloudFront Functions で URL の書き換えを行う Function は GitHub でコード管理したい Function は TypeScript で書きたい CI で Function をデプロイしたい CloudFront Functions について 参考（AWS ブログ）: https://aws.amazon.com/jp/blogs/news/introducing-cloudfront-functions-run-your-code-at-the-edge-with-low-latency-at-any-scale/ 画像は上記の AWS ブログから引用 CloudFront Functions は、Lambda@Edge よりクライアントに近い Edge Location でスクリプトを実行できるので、より速いレスポンスを期待できる 具体的には、以下のことが実現できる Edge Location に index.html がキャッシュされる Edge Location 上の CloudFront Functions で URL を/newから/new/index.htmlに書き換える キャッシュにヒットし、index.html がクライアントにレスポンスされる 上記の AWS ブログ内に CloudFront Functions と Lambda@Edge のスペック比較表があるが、Functions の制限は厳しめ また、他の CDN サービスと比較しても Functions の制限はかなりきつい（Netlify と Vercel は AWS Lambda だけど） そして、言語サポートが JavaScript（ECMAScript 5.1 準拠）なので、普通に実装すると開発体験がかなり悪いので、TypeScript で実装して tsc や webpack（ts-loader）などのトランスパイラー環境を構築する必要が出てくると思う CloudFront Functions 用の npm 管理プロジェクトを作成 mkdir functions # ディレクトリ名は適当 cd functions npm init # package.jsonを作成 とりあえず以下みたいな感じで、依存モジュールは後で入れる package.json { "name": "function-add-index", "private": true, "scripts": { "build": "tsc" } } TypeScript の設定 npm i -D typescript ./node_modules/.bin/tsc --init # tsconfig.jsonを作成 tsconfig.json で重要なのは、targetオプションになる Function は JavaScript（ECMAScript 5.1 準拠）のみで動く tsconfig.json { "compilerOptions": { "target": "es5", "module": "commonjs", "lib": ["es2015"], "outDir": "./dist", ... }, "include": ["src/**/*.ts"] } libは後述のendsWithやincludesを使うために必要 moduleは指定しなくても良い（targetがes5であればデフォルトでcommonjsになる） Function の実装 型定義があるので、インストール npm i -D @types/aws-cloudfront-function 以下はこちらのサンプルコードを TS 化したもの src/index.ts function handler(event: AWSCloudFrontFunction.Event): AWSCloudFrontFunction.Request { const { request } = event if (request.uri.endsWith('/')) { request.uri += 'index.html' } else if (!request.uri.includes('.')) { request.uri += '/index.html' } return request } CI（CircleCI 2.1）の実装 Function のデプロイ周りを全部 aws-cli 使って書くと、以下のような感じになるかなと思う （そのうちこの辺の CircleCI Orb ができたら、置き換える前提で） 処理の流れは aws cloudfront list-functionsでfunction-add-indexが存在するか判断 存在しない場合は、新規作成（aws cloudfront create-function） 存在する場合は、更新（aws cloudfront update-function） ETag を取得（aws cloudfront describe-function） 公開する（aws cloudfront publish-function） version: 2.1 orbs: node: circleci/node@4.7.0 aws-cli: circleci/aws-cli@2.0.3 jobs: deploy: executor: node/default steps: - checkout - node/install-packages: app-dir: ./functions - run: name: Build working_directory: ./functions command: npm run build - aws-cli/setup - run: name: Deploy working_directory: ./functions command: | FN_NAME="function-add-index" FN_LIST=$(aws cloudfront list-functions | jq --arg fn_name $FN_NAME '.FunctionList.Items[] | select(.Name==$fn_name)') if [ -z "$FN_LIST" ]; then aws cloudfront create-function \ --name $FN_NAME \ --function-config Comment="Add index.html string to url",Runtime="cloudfront-js-1.0" \ --function-code fileb://dist/index.js else ETAG_CURRENT=$(aws cloudfront describe-function --name $FN_NAME | jq -r '.ETag') aws cloudfront update-function \ --name $FN_NAME \ --function-config Comment="Add index.html string to url",Runtime="cloudfront-js-1.0" \ --function-code fileb://dist/index.js \ --if-match $ETAG_CURRENT fi ETAG_PUBLISH=$(aws cloudfront describe-function --name $FN_NAME | jq -r '.ETag') aws cloudfront publish-function \ --name $FN_NAME \ --if-match $ETAG_PUBLISH 上記は Function を作成・更新した後にすぐ Publish してるので、本当は公開前にテストした方が良い（一応aws cloudfront test-functionってのもある） 必要な IAM ポリシー aws-cli/setupに設定する IAM ユーザーのポリシーは以下をアタッチする { "Version": "2012-10-17", "Statement": [ { "Sid": "VisualEditor0", "Effect": "Allow", "Action": [ "cloudfront:DescribeFunction", "cloudfront:ListFunctions", "cloudfront:PublishFunction", "cloudfront:UpdateFunction", "cloudfront:CreateFunction" ], "Resource": "*" } ] }

はじめに 内容はこちらのページを参考にしたもの　Getting started with the AWS CDK ステップ 1. aws cdkのインストール (※Supportされているnodeのバージョンがあるので注意) 今回はその中の v16.3.0を使用: node -v v16.3.0 インストール npm install -g aws-cdk バージョンチェック (インストール時によって異なる) cdk --version 1.128.0 (build 1d3883a) 2. aws cliの設定 (すでにしてあればスキップ) aws cliのインストールはこちら:　AWS CLI バージョン 2 のインストール、更新、アンインストール profileを分けて管理する場合は --profile <任意のプロファイル名>を指定. ACCESS_KEYとACCESS_SECRETが聞かれるので入力 aws configure 3. CDK Bootstrap Account IDの取得 aws sts get-caller-identity { "UserId": "xxxxxxxxxx", "Account": "xxxxxx", "Arn": "arn:aws:iam::xxxxxx:user/naka" } Regionの取得 aws configure get region ap-northeast-1 Bootstrap cdk bootstrap　aws://ACCOUNT-NUMBER/REGION 結果: cdk bootstrap aws://ACCOUNT-NUMBER/ap-northeast-1 --profile naka-personal ⏳ Bootstrapping environment aws://ACCOUNT-NUMBER/ap-northeast-1... CDKToolkit: creating CloudFormation changeset... ✅ Environment aws://ACCOUNT-NUMBER/ap-northeast-1 bootstrapped. コンソールから確認: S3 BucketとS3 Bucket Policyが作成された これで設定完了で、 cdk deploy というコマンドで、Deployできるようになった！ 参考 https://docs.aws.amazon.com/cdk/latest/guide/getting_started.html https://dev.classmethod.jp/articles/cdk-workshop-typescript/

DeepRacerを始めようと考えている DeepRacer ってどんなものなの？ DeepRacer を行うことで得られるメリットを知りたい AWSやAIの知識がないのでハードルが高そう 上記のような疑問に答えていきます。 本記事を読むことで以下のようなことが分かります。 DeepRacerとは DeepRacerを行うメリット３つ AWSやAIの知識が0でも参加できる では解説していきます。 AWS DeepRacerとは AWS DeepRacer はAWSのAI学習サービス AWS DeepRacer はAWSが開発したAIを楽しく学ぶためのツールです。 下記は2019年の記事ですが、分かりやすく参考になるので、貼っておきます。 第1回　強化学習が楽しく学べる自律走行レーシングカー「AWS DeepRacer」とは？ 上記の記事にもありますが、AWSは「機械学習に興味を持つエンジニアや開発者を増やしていきたい」と考えています。 AWSからは機械学習を楽しく学ぶためのツールとして DeepRacer（自動運転） DeepComposer（自動作曲） DeepLens（動画分析） の３つのツールが出ています。 各ツールのページのメニューを見てみるとDeepRacerが一番充実しているのが分かります。 リーグがあって明確に勝負ができるという点が盛り上がっている要因かなと思います。 どれをやるか迷ったらDeepRacer一択で良いと思います。 ・DeepLensのメニュー ・DeepRacerのメニュー ・DeepComposerのメニュー DeepRacerを行うメリット３つ ①AIの知識がつく 第４次産業革命といったことが政府から報じられるようになり、 エンジニアがAIを学ぶ必要性は確実に高まっています。 例えば筆者はインフラエンジニアですが、インフラエンジニアがAIを学ぶと MLOpsという職種を目指せるようになります。 上記のようなことを別記事で詳細に解説しましたので、リンクを貼っておきます。 インフラエンジニアのAI（ML）入門→MLOpsへ【AWS DeepRacerで学ぶ】 ②夢がある（DeepRacerリーグ優勝の恩恵） DeepRacerリーグについての詳細は下記をご覧いただければと思いますが、なかなか魅力的な商品だと思います。 ※上位者はAWS最大のイベントであるre:Inventへの参加券を獲得できます。 （７泊８日くらいのイベントなので総額が50万円程度かかるらしい。無料でラスベガス、、行きたいですね。。） ここからはさらに夢がある話ですが、 AWS DeepRacerリーグで優勝するとmynaviなどの大きな媒体に取り上げられるくらいの知名度になります。 又、下記は2020年 AWS SummitでのDNP社長の方のインタビュー動画ですが、１１分のところで 「優勝したことで数千万※の投資が回収できたと思っている」と発言されています。 CUS-01：DNP のデジタル人材育成 ～楽しみながら学ぶ仕掛けつくり～ ※動画閲覧にはアカウント登録必要です ※数千万とは言ってなかったけど、やったこと聞いてたらそれくらいはかかっているはず。 投資回収の要因は 「AI知名度向上によるAI人材の獲得」 「AIプロジェクトへの参画」 「メディアへの露出や社外への発信」 と発言されています。 ③AI研修として利用も可能 会社のAI研修などにDeepRacerが活用できます。 優勝したら数千万以上の投資効果がある上に前提知識が不要（詳細は後述）だからです。 会社としてAIを伸ばしていくためにDeepRacerを研修に取り入れている会社がいくつかありました。 代表的なのはやはりDNPさんですが、 QiitaでDeepRacerの記事を見ていると他の会社さんも同様の取り組みを行っていることが見て取れます。 （QiitaのOrganizationを使って会社の取り組みとして行っていると思われる） 前提知識不要なので新人研修にもおすすめです。 ※個人で本格的にやると高額なお遊びになってしまうので、 　会社の取り組みとしてやれればよいですね。 AWSやAIの知識が0でも参加できる 下記の記事を見ていただければわかるのですが、前提知識不要で始めることができます。 ※始めるにあたり最低限のAWS知識はつけておいてほしいので、 　それが得られる構成にしています。 【AI知識不要】AWS DeepRacer の始め方【無料で機械学習エンジニアの仕事を疑似体験】 https://qiita.com/toma_shohei/items/a79abd8d71a2e284273f さいごに 機械学習エンジニア不足が叫ばれる中、少しでも機械学習エンジニアを目指したいと思っていただける方が増えればと思い、 本記事を執筆しました。 DeepRacerだけで現場で使えるAI知識が身につくとは思えませんが、 興味付けには最適かなと思います。 DeepRacerは筆者も本格的に始めたのは2021/10です。 経過をブログで書きますので、こちらもご参考にしていただければさいわいです。

今まで複雑なデータ操作・分析などはPythonでやっており、SQLは普通のアプリ開発程度のライトなものしか触って来なかったのですが、やはり分析用の長いSQLなども書けた方がやりとり等で便利・・・という印象なので、復習も兼ねて記事にしておきます。 また、SQLに加えて検算も兼ねてPythonやPandasなどを使ったコードもSQLと併記していきます（Pythonで書くとどういった記述が該当するのかの比較用として使います）。 ※長くなるのでいくつかの記事に分割します。本記事は6記事目となります。 他のシリーズ記事 Athenaとはなんぞやという方はこちらをご確認ください: ※過去の記事で既に触れたものは本記事では触れません。 #1: 用語の説明・SELECT、WHERE、ORDER BY、LIMIT、AS、DISTINCT、基本的な集計関係（COUNTやAVGなど）、Athenaのパーティション、型、CAST、JOIN、UNION（INTERSECTなど含む）など。 #2: GROUP BY・HAVING・サブクエリ・CASE・COALESCE・NULLIF・LEAST・GREATEST・四則演算などの基本的な計算・日付と日時の各操作など。 #3: 文字列操作全般・正規表現関係など。 #4: コメント関係・配列操作全般・ラムダ式など。 #5: 辞書（STRUCT, MAP, ROW）やJSON関係全般。 この記事で触れること 窓関数関係全般 環境の準備 以下の#1の記事でS3へのAthena用のデータの配置やテーブルのCREATE文などのGitHubに公開しているものに関しての情報を記載していますのでそちらをご参照ください。 窓関数 この節以降では窓関数（Window Functions）について全体的に触れていきます。窓関数はGROUP BYなどに少し似ていますが、特定の条件でデータをセットにして集計用などの関数を通したり、任意の日数分で行をひとまとめに扱って計算を行ったりすることができます。例えば移動平均値などを出す場合などに使います。 窓関数の基本 基本的な書き方は<対象の集計などの関数> OVER(<データの区切りの設定> <ORDER BYの設定> <フレーム設定>)となります。OVER内の各指定は省略されたりもします。それぞれ詳細は後述する各節で順番に触れていきます（ここでは基本のみ触れます）。 データの区切りはPARTITION BY <カラム名>といったように書きます。 例えば日付区切りにして（PARTITION BY dt）、salesカラムに対して合計値を出したい場合にはSUM(sales)といったように書き、SQLは以下のようになります。 SELECT device_type, dt, sales, SUM(sales) OVER(PARTITION BY dt) AS summed_sales FROM athena_workshop.total_sales_per_device_daily ORDER BY dt, device_type 同一の日付のデータの売り上げの合計値のカラムをsummed_salesという名前で追加することができました（今回アクセスしたテーブルではdevice_typeというカラムの端末種別値ごとに各日で2行ずつ存在します）。 こうしてみるとGROUP BYに少し似ているように思えます。一方で、GROUP BYと異なりSELECTで指定できるカラムに制約が少ないですし、他にも独特な機能が色々あります（少しずつ触れていきます）。 Pythonでの書き方 Python（Pandas）で書きたい場合には基本的な書き方は以下のようになります。 シリーズなどでrollingメソッドを使うとRollingオブジェクトが返ってくるので、そのオブジェクトでさらにsumなどの集計関数を使うことで計算が行えます。 別カラムによるデータ区切り等に関してはPandasでスライスを行うことで対応ができます。 rollingメソッドにはさらに第一引数に窓のデータの個数の指定が必要になります。例えば3を指定すれば3行ずつのデータ区切りとなります。 また、集計関数などを挟んだ際に必要なデータ件数が確保できない行に関しては欠損値になったりします。例えば3行ずつのデータ区切りとした場合には1行目と2行目は必要な行数が確保できないので欠損値として表示されます。 from typing import List import pandas as pd date_range = pd.date_range(start='2021-01-01', end='2021-01-05') dfs: List[pd.DataFrame] = [] for date in date_range: df: pd.DataFrame = pd.read_json( 'https://github.com/simon-ritchie/athena_workshop/blob/main/workshop/total_sales_per_device_daily/' f"dt%3D{date.strftime('%Y-%m-%d')}/data.json.gz?raw=true", lines=True, compression='gzip') dfs.append(df) df = pd.concat(dfs, ignore_index=True) ios_df: pd.DataFrame = df[df['device_type'] == 1] rolling = ios_df['sales'].rolling(window=3) summed = rolling.sum() print(summed) 0 NaN 2 NaN 4 1038831.0 6 962325.0 8 963163.0 必要な行数が確保できないものの、少ない行数でも計算をしてしまいたい場合にはmin_periods引数に任意の整数を指定します。この整数以上の行であれば欠損値ではなく計算が実行されるようになります。 以下の例ではmin_periods=1と指定しているため、1行あれば計算されるので欠損値が無くなります。 from typing import List import pandas as pd date_range = pd.date_range(start='2021-01-01', end='2021-01-05') dfs: List[pd.DataFrame] = [] for date in date_range: df: pd.DataFrame = pd.read_json( 'https://github.com/simon-ritchie/athena_workshop/blob/main/workshop/total_sales_per_device_daily/' f"dt%3D{date.strftime('%Y-%m-%d')}/data.json.gz?raw=true", lines=True, compression='gzip') dfs.append(df) df = pd.concat(dfs, ignore_index=True) ios_df: pd.DataFrame = df[df['device_type'] == 1] rolling = ios_df['sales'].rolling(window=3, min_periods=1) summed = rolling.sum() print(summed) 0 368880.0 2 766560.0 4 1038831.0 6 962325.0 8 963163.0 窓関数での集計関数について OVERの前に記述する集計関数（SUMやCOUNTなど）は一通り利用することができるそうです。 集計関数の他にもランキング関数や個別の値に対する関数などが窓関数では利用することができます。これらは別途後々の節で触れていきます。 Pythonでの集計関数について Python（Pandas）での窓関数での集計関数はcountやsum、meanなど基本的なものに加えて他にも様々な関数が用意されていたり、applyで独自の関数やラムダ式などを指定できるようです。一覧に関して詳しくは以下のPandasのドキュメントをご確認ください。 Rolling window functions PARTITION BYによるデータの区切り設定 PARTITION BYはGROUP BYによるグループ化のカラム指定と似たような挙動をします。両方ともキーワードの後にカラム名を指定します(例 : PARTITION BY date)。窓関数の処理はここで指定され、データ区切りが指定されたカラムに対して実行されます。 例えば端末種別（device_typeカラム）ごとに処理をしたい場合は以下のようにPARTITION BY device_typeと書きます。 SELECT device_type, dt, sales, SUM(sales) OVER(PARTITION BY device_type) AS summed_sales_2 FROM athena_workshop.total_sales_per_device_daily ORDER BY dt, device_type 省略した場合は全行が1つのまとまりとして扱われます。例えば以下のようにOVER内の記述を省略してSUM関数を使うと全行の合計値を取得することができます。通常の集計関数やGROUP BYなどを絡めた場合と異なり他のカラムなども同時に表示することができます。 SELECT device_type, dt, sales, SUM(sales) OVER() AS summed_sales_2 FROM athena_workshop.total_sales_per_device_daily ORDER BY dt, device_type Pythonでの書き方 他にも色々やり方はあると思いますが、Pandasでやる場合は一例としてスライスしてしまうのがシンプルかもしれません。 特定カラム（シリーズ）のuniqueメソッドで一意な値が取れるので以下のコードではそちらでスライスを行っています。 from typing import List import pandas as pd import numpy as np date_range = pd.date_range(start='2021-01-01', end='2021-01-05') dfs: List[pd.DataFrame] = [] for date in date_range: df: pd.DataFrame = pd.read_json( 'https://github.com/simon-ritchie/athena_workshop/blob/main/workshop/total_sales_per_device_daily/' f"dt%3D{date.strftime('%Y-%m-%d')}/data.json.gz?raw=true", lines=True, compression='gzip') dfs.append(df) df = pd.concat(dfs, ignore_index=True) dfs: List[pd.DataFrame] = [] unique_vals: np.ndarray = df['device_type'].unique() for unique_val in unique_vals: dfs.append(df[df['device_type'] == unique_val]) print(dfs[0]) print(dfs[1]) date device_type sales 0 2021-01-01 1 368880 2 2021-01-02 1 397680 4 2021-01-03 1 272271 6 2021-01-04 1 292374 8 2021-01-05 1 398518 date device_type sales 1 2021-01-01 2 399620 3 2021-01-02 2 430820 5 2021-01-03 2 307029 7 2021-01-04 2 497826 9 2021-01-05 2 288582 ORDER BYによるデータ区切り内でのソート条件の指定 OVER内ではORDER BYでソートの指定を行うことができます。使い方は通常のORDER BYと同じような感じで、ORDER BY <カラム名>といったように指定していきます。降順にしたい場合には最後にDESCと付ける点も同様です。ただし通常のORDER BYと異なり、PARTITION BYによって区切られたデータの単位ごとにソートが実行されます。 以下のSQLでは日付別のデータの区切り（PARTITION BY dt）ごとに売り上げの降順でソート（ORDER BY sales DESC）を行っています。結果はFIRST_VALUEというデータの区切りの中で先頭の値を取得する関数を使っています（この関数については後の節で触れます）。 SELECT device_type, dt, sales, FIRST_VALUE(sales) OVER(PARTITION BY dt ORDER BY sales DESC) AS first_value FROM athena_workshop.total_sales_per_device_daily ORDER BY dt, device_type 実行してみると、各日付内で一番高い売り上げの値がfirst_valueカラムとして設定されていることを確認することができます。 Pythonでの書き方 前節くらいのソートであれば、スライスを行う形でデータ区切りを設定する場合は事前にsort_valuesメソッドなどでソートするなどで対応ができます。元のデータフレームをそのまま保持したければコピーを取ったり、もしくはデータ区切り数が少なければスライス後のデータフレームに対してソートする形でもいいかもしれません。 from typing import List import pandas as pd import numpy as np date_range = pd.date_range(start='2021-01-01', end='2021-01-05') dfs: List[pd.DataFrame] = [] for date in date_range: df: pd.DataFrame = pd.read_json( 'https://github.com/simon-ritchie/athena_workshop/blob/main/workshop/total_sales_per_device_daily/' f"dt%3D{date.strftime('%Y-%m-%d')}/data.json.gz?raw=true", lines=True, compression='gzip') dfs.append(df) df = pd.concat(dfs, ignore_index=True) df.sort_values(by='sales', ascending=False, inplace=True) dfs: List[pd.DataFrame] = [] unique_vals: np.ndarray = df['device_type'].unique() for unique_val in unique_vals: dfs.append(df[df['device_type'] == unique_val]) print(dfs[0]) date device_type sales 7 2021-01-04 2 497826 3 2021-01-02 2 430820 1 2021-01-01 2 399620 5 2021-01-03 2 307029 9 2021-01-05 2 288582 フレームの設定 フレームの指定は（PARTITION BYによる）データ区切りを設定した範囲のデータで、どの行を対象とするか・・・といった追加の条件を設定することができます。例えばデータ区切り内のデータで2行目～5行目を対象とするとか、数値が50～100の値のみを対象にするとか、もしくは対象行の値の前後2日間に該当する行のみを対象にする・・・といったような細かい制御ができます。 フレームの指定はORDER BYなどの後に記述します。基本的に行範囲の算出などの都合で行の順番が大切になってきたりもするのとAthenaではORDER BYを指定しないとクエリの度に行の順番が変わったりするためフレームを使用する場合はOVER内でORDER BYの指定を省略せずに指定する形が無難かと思われます。例えば<集計などの関数> OVER(PARTITION BY <カラム名> ORDER BY <カラム名> <フレーム設定>)といったように書きます。 フレーム設定には行範囲（例 : 対象のデータ区切り内の2行目から5行目など）を設定するROWSと値の範囲（例 : 前日～翌日の範囲を満たす行全てなど）を設定するRANGEの2種類が存在します。 ROWSもしくはRANGEの後には、開始値の設定のみを行う場合もしくは開始値と終了値の範囲の指定の2パターンが存在します。 例えば開始値側のみをROWSで設定する場合にはROWS <開始値の設定>といったように書き、開始値と終了値の範囲を指定する場合にはROWS BETWEEN <開始値の設定> AND <終了値の設定>といったようにBETWEENとANDが必要になります。 開始値と終了値の指定に関しては以下のように5パターンが存在します。 UNBOUNDED PRECEDING: 対象のデータ区切り内の先頭の行 <n行> PRECEDING: 現在の行からn行分前の行（ROWSの場合のみ使用可） CURRENT ROW: 現在行 <n行> FOLLOWING: 現在の行からn行分前の行（ROWSの場合のみ使用可） UNBOUNDED FOLLOWING: 対象のデータ区切り内の最後の行 <n行> PRECEDINGと<n行> FOLLOWINGという書き方は他のDB（MySQLやOracleなど）ではRANGEでも利用ができると書かれている記事があり恐らく使えるのですが、Athena（Presto）ではROWSでのみ使えるという記述がPrestoのドキュメントにあるためRANGEでは使えません。 色々要素が多いので、以降の節でSQLを実行しつつ個別に細かく見ていきます。 Pythonでの書き方 この辺のフレーム制御に関しては・・・色々やり方があってどんな対応だとシンプルになるか自信がありませんが、シンプルにn行ずつの処理が必要というケースであれば前節までで触れたrollingメソッドの引数で対応ができます。 他の要件が必要な場合にはapplyやループを回したりなど他の制御を使って色々と対応する必要が出てくるかもしれません。要件次第かなと思います。 現在行の値をCURRENT ROWで取ってみる 試しにCURRENT ROWを指定して現在行の値を取ってみます。現在行の値は窓関数関係を使わなくても取れるので以下のSQL自体は何かに使える・・・というものではないのですが、挙動の確認用となります。 なお、このCURRENT ROW単体での指定（BETWEENなどを使わない指定）では「開始値の位置」の指定となるため、先頭の行（現在行）のみを取得するためFIRST_VALUE関数を使っています。 SELECT device_type, dt, sales, FIRST_VALUE(sales) OVER(PARTITION BY device_type ORDER BY dt ROWS CURRENT ROW) AS current_row_sales FROM athena_workshop.total_sales_per_device_daily ORDER BY dt, device_type 現在行の値（salesカラム）と窓関数で取った現在行の値（current_row_salesカラム）の値が一致していることが確認できます。 ROWS ... PRECEDINGを使って2行前の値を取得してみる 今度はROWS ... PRECEDINGを使って2行前の値を取得してみます。2行前の値を取りたいのでFIRST_VALUE関数とROWS 2 PRECEDINGという書き方を組み合わせています。 SELECT device_type, dt, sales, FIRST_VALUE(sales) OVER(PARTITION BY device_type ORDER BY dt ROWS 2 PRECEDING) AS window_func_sales FROM athena_workshop.total_sales_per_device_daily ORDER BY device_type, dt 2行前の参照ができない1行目と2行目はどうなるのだろう・・・と思いましたが、どうやらそのような行ではNULLなどにはならずに遡れる位置の行の値が設定されるようです。例えば1行目であれば1行目の値、2行目であれば1行目の値、3行目であれば1行目の値、4行目であれば2行目の値・・・といったようになるようです。 ROWS UNBOUNDED PRECEDING で最初の行の値を取得してみる こちらも特に何か分析に使う・・・というものでもありませんが、挙動の確認用にUNBOUNDED PRECEDINGでデータ区切り内の最初の行の値を取得してみます。 SELECT device_type, dt, sales, FIRST_VALUE(sales) OVER(PARTITION BY device_type ORDER BY dt ROWS UNBOUNDED PRECEDING) AS window_func_sales FROM athena_workshop.total_sales_per_device_daily ORDER BY device_type, dt この処理は特に違和感が無く、window_func_salesカラムの各行の値が最初の行の368880という値になっていることが確認できます。 UNBOUNDED FOLLOWING はBETWEENとセットで使わないとエラーになる UNBOUNDED FOLLOWINGはBETWEENと一緒に使わないとエラーになってしまいます。正確には範囲の終了値側（BETWEEN ... AND ...のANDの後）で使わないとエラーになります。 SELECT device_type, dt, sales, FIRST_VALUE(sales) OVER(PARTITION BY device_type ORDER BY dt ROWS UNBOUNDED FOLLOWING) AS window_func_sales FROM athena_workshop.total_sales_per_device_daily ORDER BY device_type, dt ROWS BETWEEN で前後の行も含めた合計を出してみる ROWS BETWEEN ... AND ...で指定した行の範囲の合計を計算して挙動を確認してみます。この節のサンプルでは現在行に加えて前の1行分と後の1行分を対象とし、合計3行で計算するようにしてみます。 1行前を対象とするにはANDの前に1 PRECEDINGという記述が必要になり、1行後を対象とするにはANDの後に1 FOLLOWINGという記述が必要になります。また、合計を出すため関数部分をSUM(sales)としています。 SELECT device_type, dt, sales, SUM(sales) OVER(PARTITION BY device_type ORDER BY dt ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS window_func_sales FROM athena_workshop.total_sales_per_device_daily ORDER BY device_type, dt 1行目を見てみると、1行目と2行目のみの合算となっており1行目のwindow_func_salesカラムの値だけ低くなっています。2行目からは1行目・2行目・3行目…といったように3行分の合計にちゃんとなっているようです。平均などであればあまり問題にはならなそうですが、PRECEDINGを使いつつ合計などを計算する場合には最初の方の行は要件によっては少し注意する必要がありそうです。 フレームのデフォルトの挙動 最初誤解していたのですが、ORDER BYを使った場合フレームの指定を省略した場合データ区切りの最初（UNBOUNDED PRECEDING）～現在の行（CURRENT ROW）という挙動になるそうです。データ区切りの終わりは最後の行にはならず現在の行で止まってしまうのでフレームの指定を省略する場合には注意が必要です。 紛らわしいことに窓関数内でORDER BYを使わなかった場合にはデータ区切りの最初（UNBOUNDED PRECEDING）～データ区切りの最後の行（UNBOUNDED FOLLOWING）になる・・・と挙動が変わるようです。うっかりしているとミスしそうなので毎回フレームを明示する・・・とかでもいいかもしれません。 試しにCOUNT関数で各データ区切り内の行数をカウントしつつフレームの指定を省略した場合、以下のようにPARTITION BYで区切った範囲でも各行数が一致せず下にいくほど行数が増えていっています。 SELECT device_type, dt, sales, COUNT(*) OVER(PARTITION BY device_type ORDER BY dt) AS window_func_sales FROM athena_workshop.total_sales_per_device_daily ORDER BY device_type, dt PARTITION BYで指定した区切りごとに各行数を統一したい場合（データ区切り全体で統一したい場合）はBETWEENによるUNBOUNDED PRECEDINGとUNBOUNDED FOLLOWINGでのフレームの指定が必要になります。 SELECT device_type, dt, sales, COUNT(*) OVER(PARTITION BY device_type ORDER BY dt ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS window_func_sales FROM athena_workshop.total_sales_per_device_daily ORDER BY device_type, dt また、ORDER BYを窓関数内で使用していない場合には行数はフレームの指定を省略してもデータ区切り全体が対象になってくれます。 SELECT device_type, dt, sales, COUNT(*) OVER(PARTITION BY device_type) AS window_func_sales FROM athena_workshop.total_sales_per_device_daily ORDER BY device_type, dt 移動平均を計算してみる ここまでに学んだことを利用して移動平均を計算してみます。対象の日付に対して前方方向に7日分を対象として平均を取ります。例えば2021-01-07の日付であれば2021-01-01～2021-01-07の7日間の平均で計算されるようにします。 7日分の行を確保するには6日前～現在の行の日付という指定が必要になるためROWS BETWEEN ...を使い、開始行は6 PRECEDING、終了行はCURRENT ROWとなります。 SELECT device_type, dt, sales, AVG(sales) OVER(PARTITION BY device_type ORDER BY dt ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS window_func_sales FROM athena_workshop.total_sales_per_device_daily ORDER BY device_type, dt 2021-01-07の日付の部分を見てみると361299.0になっています。2021-01-01～2021-01-07で計算してみると(368880 + 397680 + 272271 + 292374 + 398518 + 425370 + 374000) / 7 = 361299.0となるので合っていそうです。 Pythonでの書き方 古いPandasバージョンではrolling_mean関数があったようですが、0.18.0のバージョン以降では切り落としになっているようです。 rolling(n).mean()とする必要があります。 from typing import List import pandas as pd import numpy as np date_range = pd.date_range(start='2021-01-01', end='2021-01-14') dfs: List[pd.DataFrame] = [] for date in date_range: df: pd.DataFrame = pd.read_json( 'https://github.com/simon-ritchie/athena_workshop/blob/main/workshop/total_sales_per_device_daily/' f"dt%3D{date.strftime('%Y-%m-%d')}/data.json.gz?raw=true", lines=True, compression='gzip') dfs.append(df) df = pd.concat(dfs, ignore_index=True) ios_df: pd.DataFrame = df[df['device_type'] == 1].copy() ios_df.sort_values(by='date', inplace=True) mean: pd.Series = ios_df['sales'].rolling(window=7, min_periods=1).mean() print(mean) 0 368880.000000 2 383280.000000 4 346277.000000 6 332801.250000 8 345944.600000 10 359182.166667 12 361299.000000 14 354562.857143 16 343387.857143 ... RANGEでの<n> PRECEDINGや<n> FOLLOWINGはエラーになる 前節で少し触れたように、Athena（Presto）ではRANGEでは<n> PRECEDINGや<n> FOLLOWINGといったような数値や日付範囲の指定による書き方がサポートされていません。結構便利そうなのですが・・・。今後のPrestoなどのアップデートに期待です。 UNBOUNDED PRECEDINGやCURRENT ROW、UNBOUNDED FOLLOWINGなどはサポートされていますが、一方でそれらを使う場合はROWSを使っても同じことでできる？気がするので現状RANGEを使うケースがあまり無い・・・？感じも少ししています（ぱっと浮かばないだけで、良く考えたら何かRANGEじゃないと対応ができないケースがあるかもしれませんが・・・）。 たとえば以下のようにRANGEと<n> PRECEDINGなどを組み合わせて使ってみるとエラーになることを確認できます。 SELECT total_sales, power, AVG(total_sales) OVER(PARTITION BY power ORDER BY power RANGE BETWEEN 1000 PRECEDING AND 1000 FOLLOWING) FROM athena_workshop.user_total_sales_and_power WHERE total_sales != 0 ORDER BY power 現状で特定の値の範囲でデータ区切りをしたい場合は、事前にWITH句や一時テーブル（中間テーブル）を作ったりして区切り用の種別のようなカラムを設けておいて、そちらのカラムをPARTITION BYで指定する・・・といった対応になりそうです。 窓関数で追加で使える関数 窓関数では前節までで色々触れてきたように集計用の関数（COUNTやSUM）などは一通り使えます。加えて窓関数専用のランキング関数（Ranking Functions）や単一値用の関数（Value Functions）が用意されています。この節以降ではそれらの各関数について触れていきます。 データ区切り内の先頭の値を取得する: FIRST_VALUE 前節まででも何度か先行して使っていましたが、FIRST_VALUE関数ではデータ区切り内で先頭の値を取得できます。 以下のSQLでは端末種別（device_type）ごとの売り上げのデータ区切りで、先頭の行の売り上げの値をFIRST_VALUE関数で取得しています。window_func_salesカラムの各値が最初の行の368880になっていることを確認できます。 SELECT device_type, dt, sales, FIRST_VALUE(sales) OVER(PARTITION BY device_type ORDER BY dt) AS window_func_sales FROM athena_workshop.total_sales_per_device_daily ORDER BY device_type, dt Pythonでの書き方 rollingメソッドの返却値はapplyメソッドを持っているのでそちらを利用して対応ができます。applyメソッドへは窓関数のデータ区切りごとのシリーズが渡されるので、x.values[0]といった記述で先頭の値が取れます（処理が遅いかもしれませんがx.iloc[0]などでも同じような制御にはなります）。 from typing import List import pandas as pd import numpy as np date_range = pd.date_range(start='2021-01-01', end='2021-01-10') dfs: List[pd.DataFrame] = [] for date in date_range: df: pd.DataFrame = pd.read_json( 'https://github.com/simon-ritchie/athena_workshop/blob/main/workshop/total_sales_per_device_daily/' f"dt%3D{date.strftime('%Y-%m-%d')}/data.json.gz?raw=true", lines=True, compression='gzip') dfs.append(df) df = pd.concat(dfs, ignore_index=True) ios_df: pd.DataFrame = df[df['device_type'] == 1].copy() ios_df.sort_values(by='date', inplace=True) first_vals: pd.Series = ios_df['sales'].rolling( window=7, min_periods=1).apply(lambda x: x.values[0]) print(first_vals) window=7としているので7行目までは同じ値（先頭の値が同じ）で、8行目から値が変わっていることを確認することができます。 0 368880.0 2 368880.0 4 368880.0 6 368880.0 8 368880.0 10 368880.0 12 368880.0 14 397680.0 16 272271.0 18 292374.0 データ区切り内の最後の値を取得する: LAST_VALUE LAST_VALUE関数はFIRST_VALUE関数とは逆にデータ区切りの中の最後の行の値を取得できます。前の節で触れたように、窓関数内でORDER BYを指定した場合にはフレームの指定が無いとデータ区切りが最後の行までになってくれないのでその辺のフレームの指定（ROWS BETWEEN ...）を行っています。 SELECT device_type, dt, sales, LAST_VALUE(sales) OVER(PARTITION BY device_type ORDER BY dt ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS window_func_sales FROM athena_workshop.total_sales_per_device_daily ORDER BY device_type, dt Pythonでの書き方 FIRST_VALUEの節のPythonコードのapplyメソッド部分のインデックス参照を0から-1に変更するだけです。 from typing import List import pandas as pd import numpy as np date_range = pd.date_range(start='2021-01-01', end='2021-01-10') dfs: List[pd.DataFrame] = [] for date in date_range: df: pd.DataFrame = pd.read_json( 'https://github.com/simon-ritchie/athena_workshop/blob/main/workshop/total_sales_per_device_daily/' f"dt%3D{date.strftime('%Y-%m-%d')}/data.json.gz?raw=true", lines=True, compression='gzip') dfs.append(df) df = pd.concat(dfs, ignore_index=True) ios_df: pd.DataFrame = df[df['device_type'] == 1].copy() ios_df.sort_values(by='date', inplace=True) last_vals: pd.Series = ios_df['sales'].rolling( window=7, min_periods=1).apply(lambda x: x.values[-1]) print(last_vals) 0 368880.0 2 397680.0 4 272271.0 6 292374.0 8 398518.0 10 425370.0 12 374000.0 14 321727.0 16 319455.0 18 283038.0 データ区切り内の任意の位置の値を取得する: NTH_VALUE NTH_VALUE関数は引数に指定されたn番目の窓関数のデータ区切り内の値を取得します。第一引数には対象のカラム、第二引数に位置の整数を指定します。第二引数は1からスタートします。 以下のSQLでは第二引数に3を指定しているため、3行目の272271の値にwindow_func_salesカラムの値がなっています。 SELECT device_type, dt, sales, NTH_VALUE(sales, 3) OVER(PARTITION BY device_type ORDER BY dt ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS window_func_sales FROM athena_workshop.total_sales_per_device_daily ORDER BY device_type, dt Pythonでの書き方 FIRST_VALUEやLAST_VALUE関数の節のコードのインデックス参照でのインデックス指定を任意の整数値（n）に書き換えることで対応ができます。 例えば3番目の値であればPython（NumPy）ではインデックスが0からスタートするのでインデックスには2を指定します。 また、n番目の値が存在しなければnanを返却するようにしています。 from typing import Any, List import pandas as pd import numpy as np date_range = pd.date_range(start='2021-01-01', end='2021-01-10') dfs: List[pd.DataFrame] = [] for date in date_range: df: pd.DataFrame = pd.read_json( 'https://github.com/simon-ritchie/athena_workshop/blob/main/workshop/total_sales_per_device_daily/' f"dt%3D{date.strftime('%Y-%m-%d')}/data.json.gz?raw=true", lines=True, compression='gzip') dfs.append(df) df = pd.concat(dfs, ignore_index=True) ios_df: pd.DataFrame = df[df['device_type'] == 1].copy() ios_df.sort_values(by='date', inplace=True) def nth_value(x: pd.Series) -> Any: n: int = 3 if len(x) < n: return np.nan return x.values[n - 1] nth_vals: pd.Series = ios_df['sales'].rolling( window=7, min_periods=1).apply(nth_value) print(nth_vals) 0 NaN 2 NaN 4 272271.0 6 272271.0 8 272271.0 10 272271.0 12 272271.0 14 292374.0 16 398518.0 18 425370.0 現在の行から任意の行数だけ後の位置の値を取得する: LEAD LEAD関数はNTH_VALUE関数に少し似ていますが、基準位置がデータ区切りの先頭ではなく現在の行になり、現在の行から第二引数に指定された整数分の後の行の値を取得することができます。 引数はNTH_VALUEと同じように第一引数が対象のカラム、第二引数がオフセット分の整数（いくつ後の行を参照するかの整数）となります。第二引数は0からスタートで、0を指定した場合は現在の行の値がそのまま返却されます。 以下のSQLではデータ区切り内で1行後の行の値を取得しています。 SELECT device_type, dt, sales, LEAD(sales, 1) OVER(PARTITION BY device_type ORDER BY dt ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS window_func_sales FROM athena_workshop.total_sales_per_device_daily ORDER BY device_type, dt また、第二引数を省略した場合にはデフォルト値として1が設定されます（1行後の値が取得されます）。 SELECT device_type, dt, sales, LEAD(sales) OVER(PARTITION BY device_type ORDER BY dt ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS window_func_sales FROM athena_workshop.total_sales_per_device_daily ORDER BY device_type, dt 結果は第二引数に1を指定した時と同じになります。 第三引数には値が取れない行に対するデフォルト値を指定することもできます（例 : 最後の行は次の行の値が存在しないためNULLとなるため、第三引数を指定しているとその値でNULLが補正されます）。この辺は次のLAG関数で説明がしやすいので触れていきます。 Pythonでの書き方 rollingを使わずにスライスでPARTITION BYのような制御をしたとして、例のごとく色々書き方はありますが数行後の値を取りたい場合の書き方の一例としては以下のコードでは データフレームのカラムにインデックスを設定する（事前にreset_indexでインデックスをリセットしておく） インデックスのカラムでapplyメソッドを使い、且つapplyメソッドではキーワード引数なども指定できるので対象の配列を指定する n行後の行が存在すればその値、存在しなければnanを返却というようにapplyメソッドで指定した関数内を実装する といった形で対応しています。 from typing import Any, List import pandas as pd import numpy as np date_range = pd.date_range(start='2021-01-01', end='2021-01-10') dfs: List[pd.DataFrame] = [] for date in date_range: df: pd.DataFrame = pd.read_json( 'https://github.com/simon-ritchie/athena_workshop/blob/main/workshop/total_sales_per_device_daily/' f"dt%3D{date.strftime('%Y-%m-%d')}/data.json.gz?raw=true", lines=True, compression='gzip') dfs.append(df) df = pd.concat(dfs, ignore_index=True) ios_df: pd.DataFrame = df[df['device_type'] == 1].copy() ios_df.sort_values(by='date', inplace=True) def lead(index: int, values: np.ndarray) -> Any: n: int = 3 if len(values) - 1 < n + index: return np.nan return values[n + index] sales_vals: np.ndarray = ios_df['sales'].values ios_df.reset_index(drop=True, inplace=True) ios_df['index'] = ios_df.index lead_vals: pd.Series = ios_df['index'].apply(lead, values=sales_vals) print('original df:\n', ios_df) print('lead values:\n', lead_vals) original df: date device_type sales index 0 2021-01-01 1 368880 0 1 2021-01-02 1 397680 1 2 2021-01-03 1 272271 2 3 2021-01-04 1 292374 3 4 2021-01-05 1 398518 4 5 2021-01-06 1 425370 5 6 2021-01-07 1 374000 6 7 2021-01-08 1 321727 7 8 2021-01-09 1 319455 8 9 2021-01-10 1 283038 9 lead values: 0 292374.0 1 398518.0 2 425370.0 3 374000.0 4 321727.0 5 319455.0 6 283038.0 7 NaN 8 NaN 9 NaN 現在の行から任意の行数だけ前の位置の値を取得する: LAG LAG関数はLEADとは逆の挙動をする関数で、現在の行から第二引数で指定された整数分前の行の値を取得できます。 他の引数の構成や挙動はLEAD関数と同様です。第一引数は対象のカラム、第二引数に何行前の値を参照するかの値（デフォルト値は1で省略可）、第三引数は参照する前の行が存在しない場合の欠損値（NULL）の場合の補正値としてのデフォルト値となります。 以下のSQLでは第二引数に2を指定して、2行前の値を取得しています。 SELECT device_type, dt, sales, LAG(sales, 2) OVER(PARTITION BY device_type ORDER BY dt ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS window_func_sales FROM athena_workshop.total_sales_per_device_daily ORDER BY device_type, dt 2行前の行が存在しない場合にはNULLとなるため、1行目と2行目の値がNULL（UI上では空）になっていることが確認できます。 値が取れない行に対してNULLではなく別の値を指定したい場合には第三引数に値を設定するとその値で補正されます。以下のSQLでは値が取れない行に関しては0を設定しています。 SELECT device_type, dt, sales, LAG(sales, 2, 0) OVER(PARTITION BY device_type ORDER BY dt ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS window_func_sales FROM athena_workshop.total_sales_per_device_daily ORDER BY device_type, dt Pythonでの書き方 LEAD関数の節と似たような感じですが、こちらはn行分前にインデックスをした際に対象インデックスが0未満になった場合にはnanを返却する形にapplyで指定した関数の内容を調整しています。 from typing import Any, List import pandas as pd import numpy as np date_range = pd.date_range(start='2021-01-01', end='2021-01-10') dfs: List[pd.DataFrame] = [] for date in date_range: df: pd.DataFrame = pd.read_json( 'https://github.com/simon-ritchie/athena_workshop/blob/main/workshop/total_sales_per_device_daily/' f"dt%3D{date.strftime('%Y-%m-%d')}/data.json.gz?raw=true", lines=True, compression='gzip') dfs.append(df) df = pd.concat(dfs, ignore_index=True) ios_df: pd.DataFrame = df[df['device_type'] == 1].copy() ios_df.sort_values(by='date', inplace=True) def lag(index: int, values: np.ndarray) -> Any: n: int = 3 if len(values) - 1 < n + index: return np.nan return values[n + index] sales_vals: np.ndarray = ios_df['sales'].values ios_df.reset_index(drop=True, inplace=True) ios_df['index'] = ios_df.index lead_vals: pd.Series = ios_df['index'].apply(lag, values=sales_vals) print('original df:\n', ios_df) print('lead values:\n', lead_vals) original df: date device_type sales index 0 2021-01-01 1 368880 0 1 2021-01-02 1 397680 1 2 2021-01-03 1 272271 2 3 2021-01-04 1 292374 3 4 2021-01-05 1 398518 4 5 2021-01-06 1 425370 5 6 2021-01-07 1 374000 6 7 2021-01-08 1 321727 7 8 2021-01-09 1 319455 8 9 2021-01-10 1 283038 9 0 292374.0 1 398518.0 2 425370.0 3 374000.0 4 321727.0 5 319455.0 6 283038.0 7 NaN 8 NaN 9 NaN データ区切り内の行番号を取得する: ROW_NUMBER ROW_NUMBER関数はデータ区切りの中の行数を取得します。引数は特に無く、返却される行数は1以降で設定されます。 SELECT device_type, dt, sales, ROW_NUMBER() OVER(PARTITION BY device_type ORDER BY dt) AS row_number FROM athena_workshop.total_sales_per_device_daily ORDER BY device_type, dt Pythonでの書き方 PandasではSQLと異なり行番号の割り振りなどはインデックスを参照するだけなので、PARTITION BY的なことをしたければスライス → 行番号を取りたければreset_indexでインデックスを割り振り直した後にindex属性にアクセスすれば連番が取れます（SQLと異なり0からスタートします）。カラムに設定したければdf['<カラム名>'] = df.indexとすれば設定することができます。 from typing import Any, List import pandas as pd import numpy as np date_range = pd.date_range(start='2021-01-01', end='2021-01-05') dfs: List[pd.DataFrame] = [] for date in date_range: df: pd.DataFrame = pd.read_json( 'https://github.com/simon-ritchie/athena_workshop/blob/main/workshop/total_sales_per_device_daily/' f"dt%3D{date.strftime('%Y-%m-%d')}/data.json.gz?raw=true", lines=True, compression='gzip') dfs.append(df) df = pd.concat(dfs, ignore_index=True) ios_df: pd.DataFrame = df[df['device_type'] == 1].copy() ios_df.sort_values(by='date', inplace=True) ios_df.reset_index(drop=True, inplace=True) ios_df['row_number'] = ios_df.index print(ios_df) date device_type sales row_number 0 2021-01-01 1 368880 0 1 2021-01-02 1 397680 1 2 2021-01-03 1 272271 2 3 2021-01-04 1 292374 3 4 2021-01-05 1 398518 4 データ区切り内のランク（順位）を取得する: RANK, DENSE_RANK RANK関数は窓関数内のORDER BYで指定した順番でのランキングを割り振ります。ROW_NUMBERに似た挙動をし、結果は1から割り振られます。ただし全体の行の順番（窓関数ではない最後の方のORDER BY）の指定によって、窓関数内の順番と結果の順番が異なっていてもランキングの順番を計算することができます。 SELECT device_type, dt, sales, RANK() OVER(PARTITION BY device_type ORDER BY sales DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS ranking FROM athena_workshop.total_sales_per_device_daily ORDER BY device_type, sales DESC RANKに似た関数としてDENSE_RANKという関数も存在します。基本的な使い方は同じなのですが、RANKに関しては同値の複数の行があった場合にはそれらのランキングは同じになり、その次の行のランキングが同値の行数分飛んだ値になります。 例えば1位と2位が100で同値、3位が80だった場合には1位と2位は両方ともRANK関数の値は1となり、3位のRANK関数の値は3位となります。2位の行は欠落します。 一方でDENSE_RANK関数の方は2位の行が欠落したりせずに、1位と2位が1となり3位が2となります。間の欠落した順位が詰められる形となります。 Pythonでの書き方 Pandasでランキングの値を取る場合にはrankメソッドを使うとシンプルです。RANK関数に合わせるなら引数にmethod='min'と指定します。また、降順で計算するにはascending=Falseと引数を指定します。 ※以下のコードでは動作確認のサンプルとして2行目と4行目を同じ値（350000）にしています。 from typing import List import pandas as pd date_range = pd.date_range(start='2021-01-01', end='2021-01-05') dfs: List[pd.DataFrame] = [] for date in date_range: df: pd.DataFrame = pd.read_json( 'https://github.com/simon-ritchie/athena_workshop/blob/main/workshop/total_sales_per_device_daily/' f"dt%3D{date.strftime('%Y-%m-%d')}/data.json.gz?raw=true", lines=True, compression='gzip') dfs.append(df) df = pd.concat(dfs, ignore_index=True) ios_df: pd.DataFrame = df[df['device_type'] == 1].copy() ios_df.reset_index(drop=True, inplace=True) ios_df.at[1, 'sales'] = 350000 ios_df.at[3, 'sales'] = 350000 ios_df['rank'] = ios_df['sales'].rank(method='min', ascending=False) print(ios_df) date device_type sales rank 0 2021-01-01 1 368880 2.0 1 2021-01-02 1 350000 3.0 2 2021-01-03 1 272271 5.0 3 2021-01-04 1 350000 3.0 4 2021-01-05 1 398518 1.0 DENSE_RANK関数に合わせたい場合にはmethod='dense'と引数に指定します。 from typing import List import pandas as pd date_range = pd.date_range(start='2021-01-01', end='2021-01-05') dfs: List[pd.DataFrame] = [] for date in date_range: df: pd.DataFrame = pd.read_json( 'https://github.com/simon-ritchie/athena_workshop/blob/main/workshop/total_sales_per_device_daily/' f"dt%3D{date.strftime('%Y-%m-%d')}/data.json.gz?raw=true", lines=True, compression='gzip') dfs.append(df) df = pd.concat(dfs, ignore_index=True) ios_df: pd.DataFrame = df[df['device_type'] == 1].copy() ios_df.reset_index(drop=True, inplace=True) ios_df.at[1, 'sales'] = 350000 ios_df.at[3, 'sales'] = 350000 ios_df['dense_rank'] = ios_df['sales'].rank(method='dense', ascending=False) print(ios_df) date device_type sales dense_rank 0 2021-01-01 1 368880 2.0 1 2021-01-02 1 350000 3.0 2 2021-01-03 1 272271 4.0 3 2021-01-04 1 350000 3.0 4 2021-01-05 1 398518 1.0 ランクの比率値を取得する: PERCENT_RANK PERCENT_RANK関数はRANK関数で取れるランキングの値を使ったパーセンテージを取得することができます。関数内容としては以下のような計算になります。分子側はRANK関数で取れる値となります。 \frac{関数によるランキングの値 - 1}{データ区切り全体の件数 - 1} SELECT device_type, dt, sales, RANK() OVER(PARTITION BY device_type ORDER BY sales DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS ranking, PERCENT_RANK() OVER(PARTITION BY device_type ORDER BY sales DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS percent_ranking FROM athena_workshop.total_sales_per_device_daily ORDER BY device_type, sales DESC Pythonでの書き方 rankメソッドでpct=Trueと引数を指定することでパーセンテージ表示となります。シンプルで良いですね。 from typing import List import pandas as pd date_range = pd.date_range(start='2021-01-01', end='2021-01-05') dfs: List[pd.DataFrame] = [] for date in date_range: df: pd.DataFrame = pd.read_json( 'https://github.com/simon-ritchie/athena_workshop/blob/main/workshop/total_sales_per_device_daily/' f"dt%3D{date.strftime('%Y-%m-%d')}/data.json.gz?raw=true", lines=True, compression='gzip') dfs.append(df) df = pd.concat(dfs, ignore_index=True) ios_df: pd.DataFrame = df[df['device_type'] == 1].copy() ios_df['percent_rank'] = ios_df['sales'].rank(ascending=False, pct=True) print(ios_df) date device_type sales percent_rank 0 2021-01-01 1 368880 0.6 2 2021-01-02 1 397680 0.4 4 2021-01-03 1 272271 1.0 6 2021-01-04 1 292374 0.8 8 2021-01-05 1 398518 0.2 指定された件数ごとにデータ区切り内のデータを振り分ける: NTILE NTILE関数はデータ区切りごとのデータグループを引数に指定されたn個分にデータを振り分ける関数です。 例えば特定のデータ区切りに100個データがあってnを10とした場合、10個ずつのグループに分けられます（グループの番号のカラムが設定されます）。別のデータ区切りで50個のデータがあれば5個ずつのグループに分けられます。 第一引数にはnの整数が必要になり、返却値も整数となります（1以降で順番に設定されていきます）。 以下のSQLではnの引数に18を設定しています。データ区切りには90個の時系列データがあるので1グループ辺り5件となり、1, 1, 1, 1, 1, 2, 2, 2, 2, 2, 3, 3, ...という値になっていきます。 SELECT device_type, dt, sales, NTILE(18) OVER(PARTITION BY device_type ORDER BY sales DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS tile FROM athena_workshop.total_sales_per_device_daily ORDER BY device_type, sales DESC Pythonでの書き方 Pandasのqcut関数で近いことができます。q引数にNTILE関数のnに該当する個数を指定し、且つlabels=Falseと引数を指定します（文字列のラベルではなくFalseを指定すると連番が割り振られます）。 xの引数には今回はデータフレームのインデックスを指定しています。qcut関数では指定されたシリーズなどの昇順などで順番に割り振られるようなので、今回は行の順番通りに割り振りたかったためインデックスを指定しています。 from typing import List import pandas as pd date_range = pd.date_range(start='2021-01-01', end='2021-01-10') dfs: List[pd.DataFrame] = [] for date in date_range: df: pd.DataFrame = pd.read_json( 'https://github.com/simon-ritchie/athena_workshop/blob/main/workshop/total_sales_per_device_daily/' f"dt%3D{date.strftime('%Y-%m-%d')}/data.json.gz?raw=true", lines=True, compression='gzip') dfs.append(df) df = pd.concat(dfs, ignore_index=True) ios_df: pd.DataFrame = df[df['device_type'] == 1].copy() ios_df.reset_index(drop=True, inplace=True) ios_df['ntile'] = pd.qcut(x=ios_df.index, q=5, labels=False) print(ios_df) date device_type sales ntile 0 2021-01-01 1 368880 0 1 2021-01-02 1 397680 0 2 2021-01-03 1 272271 1 3 2021-01-04 1 292374 1 4 2021-01-05 1 398518 2 5 2021-01-06 1 425370 2 6 2021-01-07 1 374000 3 7 2021-01-08 1 321727 3 8 2021-01-09 1 319455 4 9 2021-01-10 1 283038 4 データ区切り内での累積分布を取得する: CUME_DIST CUME_DIST関数は累積分布（cumulative distribution）を得ることができる関数です。 ここでは累積分布自体には説明は引用程度で詳しくは省きます。必要な方は検索などで色々記事がヒットしますのでそちらをご参照ください。 累積分布関数（るいせきぶんぷかんすう、英: cumulative distribution function, CDF）や分布関数（ぶんぷかんすう、英: distribution function）とは、確率論において、実数値確率変数 X が x 以下になる確率の関数のこと。連続型確率変数では、負の無限大から x まで確率密度関数を定積分したもの。 累積分布関数 - Wikipedia 引数は特に無く、DOUBLE型の浮動小数点数が返却されます。 SELECT date, device_type, sales, CUME_DIST() OVER(PARTITION BY device_type ORDER BY sales DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS cume_dist FROM athena_workshop.total_sales_per_device_daily ORDER BY device_type, sales DESC Pythonでの書き方 そのままプロット・・・するのであればPandasのシリーズのhistメソッドでcumulative=True, density=1, bins=100と引数を指定するとシンプルそうです。 数値がデータフレームで欲しい場合はcumsumメソッドなどで何ステップか計算を挟む必要がありそうです。ここではリンクだけ貼って省きますので必要な場合はリンク先をご確認ください。 参考文献・参考サイトまとめ The Applied SQL Data Analytics Workshop: Develop your practical skills and prepare to become a professional data analyst, 2nd Edition Window Functions 分析関数（ウインドウ関数）をわかりやすく説明してみた 累積分布関数 - Wikipedia pandasで窓関数を適用するrollingを使って移動平均などを算出 Rolling window functions module 'pandas' has no attribute 'rolling_mean' pandas.DataFrame, Seriesを順位付けするrank pandasのcut, qcut関数でビニング処理（ビン分割） Plotting CDF of a pandas series in python