20211011のAWSに関する記事は6件です。

AWS CLI プロファイル & IAM 関連コマンド集

目的 最近、異なる AWS アカウントを切り替えて利用することが増えたため、AWS CLI のプロファイルおよび AIM 関連のよく使うコマンドをまとめておきます。 名前付きプロファイルを新規登録 コマンド: aws configure --profile Name $ aws configure --profile Name AWS Access Key ID [None]: XXXXXXXXXX AWS Secret Access Key [None]: XXXXXXXXXX Default region name [None]: ap-northeast-1 Default output format [None]: json デフォルトのプロファイルを設定 or 新規登録 コマンド: aws configure $ aws configure AWS Access Key ID [None]: XXXXXXXXXX AWS Secret Access Key [None]: XXXXXXXXXX Default region name [None]: ap-northeast-1 Default output format [None]: json 現在使用中のプロファイルを表示 コマンド: aws configure list $ aws configure list Name Value Type Location ---- ----- ---- -------- profile Name manual --profile access_key ****************XXXX shared-credentials-file secret_key ****************XXXX shared-credentials-file region ap-northeast-1 config-file ~/.aws/config 指定のプロファイルを表示 コマンド: aws configure list --profile Name $ aws configure list --profile Name Name Value Type Location ---- ----- ---- -------- profile Name manual --profile access_key ****************XXXX shared-credentials-file secret_key ****************XXXX shared-credentials-file region ap-northeast-1 config-file ~/.aws/config プロファイルを変更 # Mac & Linux $ export AWS_PROFILE=Name $ export AWS_PROFILE= # default に設定 # Windows [win] set AWS_PROFILE=Name [win] set AWS_PROFILE= # default に設定 アカウントのすべての IAM ユーザーを一覧表示 コマンド: aws iam list-users $ aws iam list-users { "Users": [ { "Path": "/", "UserName": "Name", "UserId": "XXXXXXXXXX", "Arn": "arn:aws:iam::XXXXXXXXXX", "CreateDate": "Date", "PasswordLastUsed": "Date" } ] } アカウントのすべてのユーザーグループを一覧表示 コマンド: aws iam list-groups $ aws iam list-groups { "Groups": [ { "Path": "/", "GroupName": "GroupName", "GroupId": "XXXXXXXXXX", "Arn": "arn:aws:iam::XXXXXXXXXX", "CreateDate": "Date" } ] } 参考サイト AWS 設定ファイルと認証情報ファイルの設定 AWS 名前付きプロファイル AWS IAM ユーザーの一覧表示 AWS IAM ユーザーグループの一覧表示
  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む

【AWS】Cloud9開発環境構築の流れ(備忘録)

はじめに 自己研鑽では、AWS Educateを使用中。 Cloud9での開発環境構築の手順の整理です。 やること 1.cloud9で新しい環境を作成(「Create environment」から適宜設定) 2.GitHub接続に必要な鍵を作成 ssh-keygen #keyを作成 cat ~/.ssh/id_rsa.pub #中身を表示(表示されたものをコピーしておく) 3.GutHub上で先程のssh-keyを登録 [Setting]>[SSH and GPG keys] title : どのssh-keyなのかわかるように命名(cloud9環境名、リポジトリ名、sshタイトルは同じにしています) body : 先程コピーしたkeyの中身を貼り付け 「Add SSH key」を押して公開鍵を登録 4.GitHubと接続(cloud9操作) ssh git@github.com #この後yes/noを聞かれたらyesと入力し、enter アカウント名が表示されていたら完了 5.GitHubに草を生やす git config --global user.name [githubアカウント名] git config --global user.email [github登録メール] 6.Railsバージョンアップ gem install rails -v 5.2.5 rails -v #この後バーションが正しく表示されていればOK 7.ImageMagickをインストールする ※時間がかかるため、気長に待つ sudo yum -y install libpng-devel libjpeg-devel libtiff-devel gcc cd git clone https://github.com/ImageMagick/ImageMagick.git ImageMagick-7.0.11 cd ImageMagick-7.0.11 ./configure make sudo make install convert -version #バージョン名が正しく表示されていればOK 8.あとはrails newでアプリケーションを作成し、githubのリポジトリ作成後の手順に沿ってpushするだけ 終わりに 基礎的な部分ですが流れを忘れやすいな、と。
  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む

AWS ACM DNS検証 CNAMEレコードを登録する際に気になったこと

AWS ACMのDNS検証について AWS ACMにはパブリックドメインの証明書を発行する際、DNS検証という仕組みがある。 DNS検証の仕組みを用いると証明書の更新が自動的に行われるため運用が楽になる。 ※証明書の有効期限はどんどん短くなって現状は397日=約13ヶ月。 CNAMEレコードを登録する場合は以下の2パターン。 ①Route53にてドメイン管理を使用している場合→Route53のホストゾーンに登録。 ②外部のレジストラでドメイン管理している場合→ドメインレジストラ(お名前.comとか)の管理のページでCNAMEレコードを登録。 CNAMEレコードを登録するときに気になったこと 例えば*.hogehoge.comというパブリックドメインがあるとして、 上の名前ですでにDNS検証が行われていた場合、同じドメインで新たに発行したCNAMEレコードを追加しても 問題ないのか気になったのでサポートに問い合わせてみた。 Aアカウント .hogehoge.com DNS検証済み。 Bアカウント .hogehoge.com DNS検証(新規)。 サポートの回答によると「アカウント毎に新しいレコードが発行されるため問題ない」とのこと。 CNAMEは新規追加になるので既存のレコードが変わるわけではないので大丈夫! よくよく調べてみると以下のページに記載があった。 そもそもアカウントやリージョンをまたいで同じ証明書は使えない。 なのでアカウントやリージョンをまたいで同じドメインで証明書を発行したい場合は都度、 以下の①~③を実施する必要がある。 ①発行したいアカウント+リージョンを選択。 ②ACM上で対象ドメインの証明書をリクエスト。 ③DNS検証のCNAMEレコードをRoute53のホストゾーンまたはレジストラに登録。 https://aws.amazon.com/jp/premiumsupport/knowledge-center/acm-export-certificate/
  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む

作業先のS3バケットがLambdaのトリガーとなっていないかを確認する方法

背景 S3へのオブジェクト作成をトリガーを利用してLambdaを活用することは多いと思います。 そうすると、S3にファイルを作成する処理を書くときに 「あれ、このバケットのこの場所、トリガーになってないよね、、、?」 と思うことが出てきました。 さっと確認できると安心感も増すものです。 解決方法 API レベル (s3api) コマンドにこれを満たす get-bucket-notification-configuration というものがあります。 参考:API レベル (s3 api) コマンド 具体的な方法 せっかくなので ・S3の条件(Prefixとか) ・S3のパス ・Lambda関数のARN が1行で一覧で出力できるようなjqを書いてみました(あと一歩スマートな書き方があるかもです) aws s3api get-bucket-notification-configuration --bucket ★バケット名★ |jq '.LambdaFunctionConfigurations[] |[.Filter[].FilterRules[].Name,.Filter[].FilterRules[].Value,.LambdaFunctionArn]|@tsv' -r | sort -k 2,2 結果 関数ごとにエイリアスが設定して、エイリアスごとにS3トリガーが異なる場合でも上手く一覧化できました。 Prefix testdesu/testtmp/testlogtmp/ arn:aws:lambda:ap-northeast-1:XXXXXXXXXXXX:function:testFunctionA:aliasAAA Prefix testdesu/testtmp/testlogtmp_aliasDDD/ arn:aws:lambda:ap-northeast-1:XXXXXXXXXXXX:function:testFunctionA:aliasAAA Prefix testdesu/testtmp/testlog/aliasBBB/ arn:aws:lambda:ap-northeast-1:XXXXXXXXXXXX:function:testFunctionA:aliasBBB Prefix testdesu/testtmp/testlog/aliasCC/ arn:aws:lambda:ap-northeast-1:XXXXXXXXXXXX:function:testFunctionA:aliasCCC Prefix testdesu/testtmp/testztmp/ arn:aws:lambda:ap-northeast-1:XXXXXXXXXXXX:function:testFunctionB:aliasDDD Prefix testdesu/testtmp/testztmp_aliasDDD/ arn:aws:lambda:ap-northeast-1:XXXXXXXXXXXX:function:testFunctionB:aliasDDD Prefix testdesu/testtmp/testztmp_aliasAAA/ arn:aws:lambda:ap-northeast-1:XXXXXXXXXXXX:function:testFunctionB:aliasAAA Prefix testdesu/testtmp/testzz/aliasBBB/ arn:aws:lambda:ap-northeast-1:XXXXXXXXXXXX:function:testFunctionB:aliasBBB Prefix testdesu/testtmp/testzz/aliasCC/ arn:aws:lambda:ap-northeast-1:XXXXXXXXXXXX:function:testFunctionB:aliasCCC その他 マネジメントコンソールから確認する場合、バケットの「プロパティ」から「イベント通知」という項目で、同様の内容を確認することができます。 URLで言うと2021.10.11では以下でアクセス可能です。 https://s3.console.aws.amazon.com/s3/bucket/(バケット名)/property/notification jq職人をやり終えた後で念のため調べたら画面からみられるじゃん!となった訳ですがCLIでも解決できることが分かったから良いのです
  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む

別AWSアカウントのEC2インスタンスをコピーする手順

別のAWSアカウントで作成されたEC2インスタンスを、自分のアカウントにコピーする機会があったので、手順を残しておきます。 コピー元の作業 コピー元のAWSアカウントで、AWSマネジメントコンソールにログインします。 イメージの作成 コピーしたいインスタンスを選択し、アクションから「イメージを作成」をクリックします。 任意のイメージ名を入力します。 このとき、サーバを止めたくなければ「再起動しない」にチェックを入れましょう。 「イメージを作成」をクリックします。 イメージの画面で、作成したAMIのステータスが pending から available に変わるまで待ちます。 available になったら、アクションから「イメージパーミッションの変更」をクリックします。 次の通り設定します。 「プライベート」にチェック コピー先のAWSアカウント番号を入力 ボタン「アクセス許可の追加」をクリック 「パーミッションを作成するときは次の関連付けられた…」にチェック ボタン「保存」をクリックすると、このイメージはコピー先に共有された状態になります。 コピー先の作業 コピー先のAWSアカウントで、AWSマネジメントコンソールにログインします。 イメージのコピー イメージの画面で「プライベートイメージ」をクリックします。 コピー元と共有しているイメージにチェックし、アクションから「AMIのコピー」をクリックします。 リージョンを選択し、任意のイメージ名を入力したら、ボタン「AMIのコピー」をクリックします。 確認 コピーされた新しいイメージは、所有者が自分のアカウントになっているはずです。 インスタンス起動 コピーしたイメージからインスタンスを起動します。 コピー元のキーペアを持っている場合、キーペアを新たに作る必要はありません。 無事に起動できればOKです。 コピーに失敗した場合 この手順通りにやってもエラーになることがあります。 AWS Marketplace から取得したAMIをコピーしようとすると、Images from AWS Marketplace cannot be copied to another AWS account. というエラーになります。 ライセンスを取得しましょう。 サブスクライブ(購入)する製品コードを、イメージの詳細から確認します。 次のURLでMarketplace にアクセスします。******** は製品コードに置き換えて下さい。 https://aws.amazon.com/marketplace/pp?sku=******** 「Continue to Subscribe」をクリックします。 「Accept Terms」をクリックすると完了です。
  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む

Athenaで基礎からしっかり入門 分析SQL(Pythonコード付き) #5

今まで複雑なデータ操作・分析などはPythonでやっており、SQLは普通のアプリ開発程度のライトなものしか触って来なかったのですが、やはり分析用の長いSQLなども書けた方がやりとり等で便利・・・という印象なので、復習も兼ねて記事にしておきます。 また、SQLに加えて検算も兼ねてPythonやPandasなどを使ったコードもSQLと併記していきます(Pythonで書くとどういった記述が該当するのかの比較用として使います)。 ※長くなるのでいくつかの記事に分割します。本記事は5記事目となります。 他のシリーズ記事 ※過去の記事で既に触れたものは本記事では触れません。 #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: コメント関係・配列操作全般・ラムダ式など。 この記事で触れること 辞書関係全般(STRUCT、MAP、ROW、JSON) 環境の準備 以下の#1の記事でS3へのAthena用のデータの配置やテーブルのCREATE文などのGitHubに公開しているものに関しての情報を記載していますのでそちらをご参照ください。 特記事項 AthenaのクエリエディタのUIが新しいバージョンのものにアクセスができるようになったため、本記事からはお試しで新しいUIのものを使っていっています。 辞書の操作 以降の節では辞書について全体的に色々と触れていきます。 STRUCT型とMAP型とROW型とJSON型 まず型についてですが、辞書に近い挙動をするものも含めるとAthena(Presto)上にはSTRUCTとMAPとROWという型があります。 STRUCT型はキー名と値の型が固定された辞書となります。例えばSTRUCTでxというキーを整数、yというキーを文字列を定義したとすると{"x": 10, "y": "allegro"}といったような辞書の値を扱えるようになります。ただし定義されていないキーを使用することはできません。例えば{"x": 10, "y": "allegro", "z": true}といったように定義されていないzというキーは扱うことができません。 MAP型はキーの型と値の型が固定されます。型は固定されるものの、キー名自体は固定されません。例えばキーに武器のマスタID、値に攻撃力を持つ辞書・・・といったように、キー名がログの行ごとに様々なようなケースで使えます。例えば{"10": 1200, "13": 2500}みたいなログに向いています。 ROW型は辞書のようにキーと値のペアを持つ型となりますが、STRUCTやMAP型などとはCASTの方法や値への参照の書き方などが変わってきます。テーブル自体の定義では基本的にSTRUCTかMAPを使い、ROW型はAthena(Presto)の関数の返却値などで出てきます。例えば前回の配列関係の記事で触れたZIP関数などが該当します。 STRUCTなどのカラムに対してSELECTで表示した場合そのカラムの型はROWとなっています(ROW関係の制御が指定できます。この辺りは後々の節で触れます)。 また、JSON型というデータも存在します。こちらはJSON形式の文字列が特定のカラムに含まれている・・・といった場合に変換を挟んで利用したりします。 新しく扱う辞書用のテーブルについて 本記事から辞書関係のデータを扱っていく上で以下のテーブルが追加になっています。それぞれSTRUCT(キーが固定な辞書)用、MAP(キーが様々な辞書)用、STRUCTを格納したARRAY(辞書を格納した配列)用として設定しています。 user_pvp_status テーブル user_pvp_status テーブルはユーザーのPVPごとのステータス情報を格納するテーブルです。基本的なカラムに加えて自身のPVP時点でのステータスのカラム(self_status)と相手のPVP時点でのステータスのカラム(enemy_status)を持っています。ステータスのカラムはSTRUCT型で、user_id, attack, defence, speed, luckというキーを持っています。 SELECT * FROM athena_workshop.user_pvp_status LIMIT 10; user_materials_count テーブル user_materials_count テーブルはユーザーの日次の所持している素材アイテム(合成素材的なものをイメージしてください)の所持数のデータを格納するテーブルです。基本的なカラムに加えてキーに対象のアイテムのマスタID、値に所持数を格納した辞書のカラム(materials_count)を持っています。対象のカラムはMAP型でキーも値も整数で設定してあります。 SELECT * FROM athena_workshop.user_materials_count LIMIT 10; user_weapon テーブル user_weapon テーブルはユーザーの所持している武器データの配列をする格納するテーブルです。基本的なカラムに加えてweaponsというカラム名で武器データの辞書を格納した配列が設定されています。辞書には武器のマスタIDのmst_idキー、攻撃力のattackキー、レベルのlevelキーが設定されています。 SELECT * FROM athena_workshop.user_weapon LIMIT 10; user_weapon_json_str テーブル user_weapon_json_str テーブルは内容はほぼ user_weapon テーブルと同じですが、武器のデータのカラムのみ配列ではなくJSONの文字列になっています。後の節でJSON変換などの説明のために使っていきます。 SELECT * FROM athena_workshop.user_weapon_json_str LIMIT 10; STRUCTのカラムの値はSELECTで表示するとROW型となる STRUCTのカラムの値に関してですが、SELECTで値を表示すると結果はROW型として表示されます。UI上の表示としては{user_id=3642, attack=905, ...}といったように辞書のような表示となり、ただしキー名(フィールド名)と値の間がイコールで繋がれる形となります。また、対象のカラムの型を表示するTYPEOF関数を使うとROW型として表示されることが分かります(row(user_id bigint, attack bigint, ...)といったような表示になります)。 SELECT TYPEOF(self_status) AS self_status_type, self_status FROM athena_workshop.user_pvp_status LIMIT 10; STRUCTのカラム内の特定の値にアクセスする STRUCTのカラムの値はSELECTするとROW型となるため、ROW型と同じようにカラム内の特定の値にアクセスすることができます。 ROW型は<対象のカラム名>.<フィールド名>といった形でドット区切りで指定することで特定の値にアクセスすることができます。なおカラム名はSTRUCT内のフィールド名が自動で設定されるようです。ASとかを指定しなくても該当の名前が表示されています。 SELECT self_status.user_id, self_status.attack, self_status FROM athena_workshop.user_pvp_status LIMIT 10; MAPのカラムの値はSELECTで表示してもMAPのままとなる STRUCT型と異なりMAP型の方はSELECTで表示してもそのままMAP型で扱われます。TYPEOF関数で表示してみるとmap(bigint, bigint)といったように表示されることが確認できます。値の表示はROW型と同じように、{{16=37, 18=24, 10=13}}といったようにキーと値の間がイコールで繋がれる形となります。 SELECT TYPEOF(materials_count) AS map_type, materials_count FROM athena_workshop.user_materials_count LIMIT 10; MAPのカラムの特定のキーの値にアクセスする MAP型はSTRUCT型やROW型のデータとは特定のキー(フィールド)へのアクセスの仕方が変わり、<カラム名>[<キーの値>]といったように[]の括弧を使ってアクセスしていきます。 MAP型はSTRUCTとは異なりキーの値が固定されない(行ごとに様々)ため、指定したキーが存在しない行に関しては表示が-といったようにハイフンになります。 以下のSQLでは[15]といったように指定してキーが15の値を表示しています。 SELECT materials_count[15] AS material_id_15_count, materials_count FROM athena_workshop.user_materials_count WHERE dt = '2021-01-01' LIMIT 10; ROW型の値の固定値の作り方 ROW型の固定値は()の括弧内にコンマ区切りで値を指定することで作ることができます。 SELECT ('a', 1) AS result_row これだけだとフィールド名(キー名)がfield0, field1, ...と割り振られます。もしフィールド名の指定をしたい場合には以下のようにROW関数とCASTによる変換処理を指定することで対応ができます。 SELECT CAST(ROW('a', 2) AS ROW(name VARCHAR, age BIGINT)) AS result_row MAP型のデータの様々な関数 MAP型に関しては追加でいくつかの関数が用意されています。以降の節ではそれらの各関数について触れていきます。 2つの配列からMAPの値を生成する: MAP MAP関数では2つの配列を渡すことで、それぞれがキーと値として使われる形でMAPの値を取得することができます。 SELECT MAP(ARRAY['a', 'b', 'c'], ARRAY[1, 2, 3]) AS result_map Pythonでの書き方 ビルトインの辞書の生成処理などはごく基礎的なのでスキップします。 Pandasのシリーズであれば色々書き方はありますが一例として以下のようにしてキーと値のセットを作ることができます。 import pandas as pd sr: pd.Series = pd.Series(data={'a': 1, 'b': 2, 'c': 3}) print(sr) a 1 b 2 c 3 dtype: int64 ROW型を格納した配列からMAPの値を生成する: MAP_FROM_ENTRIES MAP_FROM_ENTRIES関数ではROW型の値を格納した配列からMAPの値を生成します。ROW型の値部分には最初のフィールドにはキー名、2番目の値には値の2件のみを格納した形式が必要になります。 SELECT MAP_FROM_ENTRIES(ARRAY[('a', 1), ('b', 2), ('c', 3)]) AS result_map 値が配列となるMAPの値を生成する: MULTIMAP_FROM_ENTRIES MULTIMAP_FROM_ENTRIES関数はMAP_FROM_ENTRIES関数と似ていますが、MAPの値が配列になって返ってきます。また、同名のキー名が複数ある場合にも対応しています。 以下のSQLではMAPのaのキーが複数存在する条件を指定しています。 SELECT MULTIMAP_FROM_ENTRIES(ARRAY[('a', 1), ('b', 2), ('a', 3)]) AS result_map MAPからROW型の値を格納した配列を取得する: MAP_ENTRIES MAP_ENTRIESではMAP_FROM_ENTRIES関数などとは逆にMAPの値からROWの値を格納した配列を取得します。ROWのフィールド名は順番にfield0, field1, ...と設定されていきます。 SELECT MAP_ENTRIES(materials_count) AS materials_count_rows, materials_count FROM athena_workshop.user_materials_count LIMIT 10 キーと値のペアの値を作る: MAP_AGG MAP_AGG関数では第一引数にキー、第二引数に値を指定することで単一の値のMAPの値を取得することができます。 SELECT MAP_AGG('a', 1) AS result_map なお、このMAP_AGG関数ですがデータの縦持ち・横持ちの変換に使われるケースもあります。そちらに関してはリンクを貼っておきます。 特定のキーの値を取得する: ELEMENT_AT []による添え字でのアクセスとほぼ同じような挙動をしますが、ELEMENT_AT関数でも特定のキー名の部分のMAPの値を取得することができます。第一引数には対象のMAPのカラムもしくはMAPの固定値、第二引数にはキーを指定します。 SELECT ELEMENT_AT(materials_count, 2) AS element_2, materials_count FROM athena_workshop.user_materials_count LIMIT 10 Pythonでの書き方 Pythonのビルトインの辞書では[]の括弧を使うか、もしくは存在しないキーでもエラーにせずにNoneを返却するようにしたければgetメソッドなどを使います。 from typing import Dict, Optional dict_val: Dict[str, int] = {'a': 1, 'b': 2, 'c': 3} value: Optional[int] = dict_val.get('b') print(value) 2 Pandasのシリーズであれば[]の括弧を使います。 import pandas as pd sr: pd.Series = pd.Series(data={'a': 1, 'b': 2, 'c': 3}) print(sr['b']) 2 データフレームであれば[]の括弧を2個繋げたりilocやloc、atなど色々書き方があります。atを使うと以下のような書き方になります。 import pandas as pd df: pd.DataFrame = pd.DataFrame(data=[{'a': 1, 'b': 2}, {'a': 3, 'b': 4}]) print(df.at[0, 'a']) 1 MAPのキーの配列を取得する: MAP_KEYS MAP_KEYS関数ではMAPの値のキーの値の配列を返却します。なお、元のMAPの値の順番は担保されないようなので注意が必要です。Python3.6や3.7辺りから標準の挙動となった順序付きの辞書的な挙動はしません。 SELECT MAP_KEYS(materials_count) AS keys, materials_count FROM athena_workshop.user_materials_count LIMIT 10 Pythonでの書き方 ビルトインの辞書であればkeysメソッドが用意されています。値がリストで欲しい場合はlist関数でキャストします。 from typing import Dict dict_val: Dict[str, int] = {'a': 1, 'b': 2, 'c': 3} print(list(dict_val.keys())) ['a', 'b', 'c'] Pandasのシリーズもしくはデータフレームであればindex属性でアクセスができます。 import pandas as pd sr: pd.Series = pd.Series(data={'a': 1, 'b': 2, 'c': 3}) print(sr.index) Index(['a', 'b', 'c'], dtype='object') リストが欲しい場合にはtolistメソッドで変換が効きます。 import pandas as pd sr: pd.Series = pd.Series(data={'a': 1, 'b': 2, 'c': 3}) print(sr.index.tolist()) ['a', 'b', 'c'] MAPの値の配列を取得する: MAP_VALUES MAP_VALUES関数ではMAPの値の配列を取得することができます。こちらもMAP_KEYS関数と同様に値の順番は担保されない(元のMAPの値と同じにはならない)ので注意が必要です。 SELECT MAP_VALUES(materials_count) AS keys, materials_count FROM athena_workshop.user_materials_count LIMIT 10 Pythonでの書き方 ビルトインの辞書の場合はvaluesメソッドで値が取れます。こちらもリストが欲しい場合にはlist関数でキャストする必要があります。 from typing import Dict dict_val: Dict[str, int] = {'a': 1, 'b': 2, 'c': 3} print(list(dict_val.values())) [1, 2, 3] PandasのシリーズやNumPyのndarrayなどであればvalues属性で取れます。リストが欲しい場合にはtolistメソッドが必要になります。 import pandas as pd sr: pd.Series = pd.Series(data={'a': 1, 'b': 2, 'c': 3}) print(sr.values.tolist()) [1, 2, 3] 複数のMAPを統合する: MAP_CONCAT MAP_CONCAT関数は複数のMAPの値を連結します。各引数には各MAPのカラムもしくはMAPの固定値を指定します。 以下のSQLでは第一引数にMAP関数から生成したMAPの固定値(キーに100と101、値に1と2という設定をしてあります)、第二引数にMAPのカラムを指定しています。結果のMAPのキーに100や101が含まれていることが確認できます。 SELECT MAP_CONCAT(MAP(ARRAY[100, 101], ARRAY[1, 2]), materials_count) AS result_map FROM athena_workshop.user_materials_count LIMIT 10 この関数の引数は可変長なので、第三引数、第四引数・・・と必要に応じてMAPの引数を増やしていくこともできます。 注意点として、以下のようなものがあります。 キーの順番は担保されません。第一引数のMAPの値が結果のMAPの先に来たりはしません。 各引数でキーが被った場合は、後の引数の値によって上書きされます。 各MAPのキーと値の型は一致していないとエラーになります。 キーが被るケースを少し試してみます。以下のSQLでは第一引数のMAPの固定値のキーを1と2、値を3と4としています。 SELECT MAP_CONCAT(MAP(ARRAY[1, 2], ARRAY[3, 4]), materials_count) AS result_map FROM athena_workshop.user_materials_count LIMIT 10 1と2というキーは第二引数のカラムでも持っていることが多いので、1=97とか1=75といったように第二引数の値で上書きされて第一引数の値は消えていることが確認できます。 また、各引数でMAPの型が一致していないケースを試してみます。materials_countカラムはmap<bigint, bigint>の型を持つため、第一引数はmap<string, bigint>の型で試してみます。 SELECT MAP_CONCAT(MAP(ARRAY['a', 'b'], ARRAY[3, 4]), materials_count) AS result_map FROM athena_workshop.user_materials_count LIMIT 10 実行してみると以下のようにエラーとなります。 Pythonでの書き方 ビルトインの辞書であればupdateメソッドで別の辞書の値を加えることができます。こちらもキーが重複している場合には引数で指定された辞書の方の値が優先されます。 from typing import Dict dict_val_1: Dict[str, int] = {'a': 1, 'b': 2, 'c': 3} dict_val_2: Dict[str, int] = {'c': 4, 'd': 5} dict_val_1.update(dict_val_2) print(dict_val_1) {'a': 1, 'b': 2, 'c': 4, 'd': 5} Pandasのシリーズなどであればconcat関数などで複数のシリーズ(やデータフレーム)を統合することができます。ちなみにシリーズやデータフレームは同じインデックスを複数持つことができるので、Athena(Presto)やビルトインの辞書と異なりインデックスが被った場合でもそれぞれの値が残ります(以下のコードではcというキーの値が2つ残っています)。 import pandas as pd sr_1: pd.Series = pd.Series(data={'a': 1, 'b': 2, 'c': 3}) sr_2: pd.Series = pd.Series(data={'c': 4, 'd': 5}) result_sr: pd.Series = pd.concat([sr_1, sr_2]) print(result_sr) a 1 b 2 c 3 c 4 d 5 dtype: int64 MAPの値を特定条件でフィルタリングする: MAP_FILTER MAP_FILTER関数はMAPのキーと値をラムダ式で参照して、条件を満たすもののに結果のMAPに残すという挙動をします。 ラムダ式に関しては以前の記事で触れたので詳しい書き方などはそちらをご確認ください。 ラムダ式の引数にはキーと値の2つが渡されます。この記事ではキーの引数名をk、値の引数名をvとして扱っていきます。 返却値は真偽値で設定し、trueであれば結果に残りfalseであれば結果から除外されます。 以下のSQLでは1, -2, 5という3つの値を持っているMAPに対して、値が0以上(v >= 0)の値のみ結果のMAPに残しています。 SELECT MAP_FILTER(MAP(ARRAY['a', 'b', 'c'], ARRAY[1, -2, 5]), (k, v) -> v >= 0) AS result_map Pythonでの書き方 ビルトインの辞書であればループを回すか、filter関数とラムダ式などを組み合わせるか、もしくは内包記法などを使うかといった方法があります。 filter関数は以下のような内容になっています。 filterは第1引数に与えられた関数を第2引数で与えられたシーケンス1つ1つに適応し、Trueになったシーケンスのみを返す関数です。 【Python】dictをfilterする from typing import Dict dict_val: Dict[str, int] = {'a': 1, 'b': -2, 'c': 5} dict_val = dict(filter(lambda item: item[1] >= 0, dict_val.items())) print(dict_val) {'a': 1, 'c': 5} キーに調整を加える: TRANSFORM_KEYS TRANSFORM_KEYS関数はラムダ式を使って各キーを調整したMAPのデータを取得することができます。第一引数にはMAPのカラムもしくはMAPの固定値、第二引数にはラムダ式を指定します。 ラムダ式の引数にはキー(k)と値(v)の2つの引数を受け付け、返却値にはキーの値の1つの値が必要になります。 以下のSQLではキー名をキー_値といったようにアンダースコアを挟んでキーと値を連結しています(結果のキーはa_1とかc_1などになります)。 SELECT TRANSFORM_KEYS(MAP(ARRAY['a', 'b', 'c'], ARRAY[1, 2, 1]), (k, v) -> k || '_' || CAST(v AS VARCHAR)) AS result_map Pythonでの書き方 色々書き方があると思いますが、ビルトインの辞書であれば内包記法とかがシンプルかもしれません。 from typing import Dict dict_val: Dict[str, int] = {'a': 1, 'b': 2, 'c': 1} renamed_dict_val: Dict[str, int] = { f'{k}_{v}': v for k, v in dict_val.items()} print(renamed_dict_val) {'a_1': 1, 'b_2': 2, 'c_1': 1} ※f-stringsとかも使っています。 値に調整を加える: TRANSFORM_VALUES TRANSFORM_VALUES関数はTRANSFORM_KEYS関数の値版です。値に対してラムダ式を反映したMAPを取得します。 以下のSQLはTRANSFORM_KEYSの時とほぼ同じ内容ですが、結果は値の方がa_1とかになっていることを確認できます。 SELECT TRANSFORM_VALUES(MAP(ARRAY['a', 'b', 'c'], ARRAY[1, 2, 1]), (k, v) -> k || '_' || CAST(v AS VARCHAR)) AS result_map Pythonでの書き方 キーの時とほぼ同じで内包記法で対応しています。 from typing import Dict dict_val: Dict[str, int] = {'a': 1, 'b': 2, 'c': 1} result_dict_val: Dict[str, str] = { k: f'{k}_{v}' for k, v in dict_val.items()} print(result_dict_val) {'a': 'a_1', 'b': 'b_2', 'c': 'c_1'} 2つのMAPに対して同じキーの値同士で計算を行う: MAP_ZIP_WITH MAP_ZIP_WITH関数は2つのMAPの値に対して、同じキーを持つ値同士で処理を行うことができます。結果もMAPの値となります。 第一引数には1つ目のMAPのカラムもしくはMAPの固定値、第二引数には2つ目のMAPのカラムもしくはMAPの固定値、第三引数にはラムダ式が必要になります。 ラムダ式にはキー名(k)、1つ目のMAPの該当のキーの値(v1)、2つ目のMAPの該当のキーの値(v2)が渡され、返却値としてはMAPの値として1つの値が必要になります。 以下のSQLでは2つのMAPの値に対して、各値を合算したMAPをMAP_ZIP_WITH関数を使って取得しています。 SELECT MAP_ZIP_WITH( MAP(ARRAY['a', 'b', 'c'], ARRAY[1, 2, 3]), MAP(ARRAY['a', 'b', 'c'], ARRAY[4, 5, 6]), (k, v1, v2) -> v1 + v2 ) AS result_map なお、片方にしか無いキーが存在するとそちらの値はNULLになるので注意が必要です(欠損値の制御が必要になります)。 Pythonでの書き方 色々書き方がありますが、ビルトインの辞書であれば一例として以下のコードでは一旦辞書のkeysメソッドやset関数などを使って2つの辞書の一意なキーの配列を取得して、それを使って内包記法(もしくは普通のループを回す形でも良いと思います)で処理をしています。 from typing import Dict, List dict_val_1: Dict[str, int] = {'a': 1, 'b': 2, 'c': 3} dict_val_2: Dict[str, int] = {'a': 4, 'b': 5, 'c': 6} keys: List[str] = list(set([*dict_val_1.keys(), *dict_val_2.keys()])) result_dict: Dict[str, int] = {k: dict_val_1[k] + dict_val_2[k] for k in keys} print(result_dict) {'a': 5, 'b': 7, 'c': 9} MAPのデータのカーディナリティのサイズを取得する: CARDINALITY CARDINALITY関数ではカーディナリティのサイズを取得できます。カーディナリティってなんだ・・・という感じですが、MAP内で異なる値が多い(バリエーションが豊富)な行ほどサイズが大きくなる・・・という値のようです。 カーディナリティとは、数学で基数あるいは濃度という意味の用語。ITの分野では、リレーショナルデータベースにおいてあるテーブルの同一の列(カラム)に含まれる異なる値の数(バリエーション)のことを指すことが多い。 カーディナリティ 【cardinality】 引数は第一引数のMAPのカラムもしくはMAPの固定値のみです。 SELECT CARDINALITY(materials_count) AS cardinality_size, materials_count FROM athena_workshop.user_materials_count LIMIT 10 MAPの値を行列に変換する MAPの値をキーと値で各列に展開し、行列として扱うと便利なことがあります。そういった場合はUNNESTを使うと行列に変換できます。 以前の配列の以下の記事で詳しく触れましたが、書き方としてはFROM <対象テーブル>, UNNEST(対象テーブルのMAPのカラム) AS <展開後のテーブル名>(展開後のキー名, 展開後のカラム名)となります。 配列の時と比べてASの後の()の括弧内がキーと値で2つ必要になっている点には注意が必要です。また、配列の記事で書きましたがその後のデータでさらに連結など色々と操作をする場合CROSS JOINを一緒に使ってUNNESTする必要があるケースもあります。詳しくはリンク先をご確認ください。 以下のSQLではMAPの値を行列に展開しています。キーはmaterials_id, 値はcountという名前にしています。 SELECT user_id, materials_id, count, materials_count FROM athena_workshop.user_materials_count, UNNEST(athena_workshop.user_materials_count.materials_count) AS unnested_table(materials_id, count) LIMIT 100 STUCTの値を行列に変換する 1次元のSTRUCTの場合はSELECT結果はROW型となるため、そのままダイレクトに使うキーの値をドットで繋いで展開すれば行列として扱えます。 SELECT self_status.user_id AS self_user_id, self_status.attack AS self_attack, self_status.defence AS self_defence FROM athena_workshop.user_pvp_status LIMIT 100 STRUCTを格納した配列を行列に変換する STRUCT型の辞書を格納した配列といった具合に2次元でデータが格納されているケースもよくあります。そういったデータを行列に変換してみます。 まず配列に対しては以下の過去の記事で触れたようにUNNESTで展開できます。 SELECT user_id, weapon, weapons FROM athena_workshop.user_weapon, UNNEST(athena_workshop.user_weapon.weapons) AS unnested_weapons(weapon) LIMIT 100; 展開後はROW型で{mst_id=360, attack=1092, level=9}といったような値で1次元になっていることが確認できます。 あとはSTRUCTの行列変換の節で触れたような形で必要な各キーをSELECT句の部分に指定すれば行列に展開できます。 SELECT user_id, weapon.mst_id, weapon.attack, weapon.level, weapons FROM athena_workshop.user_weapon, UNNEST(athena_workshop.user_weapon.weapons) AS unnested_weapons(weapon) LIMIT 100; Pythonでの書き方 他の行列変換の処理も同様なのですが、この手の変換は以前記事を書いたのでそちらをご確認ください。 JSONのデータを扱う ログによっては配列などではなくJSON形式の文字列で入っている・・・といったケースなどがあります。この節以降ではそういったJSONデータに対して触れていきます。 値をJSONへ変換する JSONへは真偽値・整数値・文字列(VARCHAR)・配列・辞書(MAPもしくはROW)や欠損値(NULL)の値を変換できます。配列などで入れ子にする場合には内部に格納する値もこれらの値である必要があります。 また、MAPの辞書などであればキーは文字列になっている必要があります(整数とかになっていると文字列に変換されます)。配列に関しても複数の型を格納していると変換がうまくいきません(例えば文字列と整数が混在している配列など)。それらは事前に型変換などをかけておく必要があります。 変換処理はCAST関数で行うことができます。CASTのAS部分にはJSONと指定します。表示上はTYPEOFなどで型を表示しない限り違いが分かりづらいですが、TYPEOF関数などで表示される型はJSONとなります。 整数をJSONに変換する例 SELECT CAST(10 AS JSON) AS json_value, TYPEOF(CAST(10 AS JSON)) AS type 配列をJSONに変換する例 SELECT CAST(ARRAY[10, 20, 30] AS JSON) AS json_value, TYPEOF(CAST(ARRAY[10, 20, 30] AS JSON)) AS type キーが整数のMAPを指定してみると、キーにダブルクォーテーションが追加されていることから分かるようにキーが文字列に変換されることが分かります(エラーにはなりません)。 キーが整数のMAPをJSONに変換する例 SELECT CAST(MAP(ARRAY[10], ARRAY[20]) AS JSON) AS json_value 配列に複数の型が含まれている場合、例えば整数と文字列が含まれている場合などには変換しようとしてもエラーになります。 複数の型を含む配列をJSONに変換するとエラーになる例 SELECT CAST(ARRAY[10, 'cat'] AS JSON) AS json_value ※JSON形式の文字列をJSONに変換する・・・という場合はCASTによる変換ではなく後述するJSON_PARSEを使う必要があります。CASTでそういった文字列に対して処理を反映すると単純にその文字列のJSONの単一値となり、階層構造などは展開されません。 JSONから別の型に値を変換する こちらもCAST関数で任意のJSONからの変換がサポートされている型であれば変換ができます(CAST(<JSONの値> AS <変換したい型>)といった具合に指定します)。 JSONの値から整数に変換する例 SELECT CAST(CAST(10 AS JSON) AS BIGINT) AS result, TYPEOF(CAST(CAST(10 AS JSON) AS BIGINT)) AS type JSONの文字列をJSON型に変換する: JSON_PARSE JSONの文字列をJSON型に変換したい場合JSON_PARSEを使います。第一引数に対象のJSONの文字列のカラムを指定します。 SELECT JSON_PARSE(weapons) AS weapons FROM athena_workshop.user_weapon_json_str LIMIT 10; 結果はSTRUCTなどと異なりROW型のような表記にはならずにJSONの表示そのままになります。 TYPEOFで型を見てみると確かにVARCHARなどではなくJSONの型になっています。 SELECT TYPEOF(JSON_PARSE(weapons)) AS weapons FROM athena_workshop.user_weapon_json_str LIMIT 10; Pythonでの書き方 jsonモジュールのloads関数でJSON形式の文字列をPythonのデータ(辞書など)に変換できます。 import json from typing import Dict dict_val: Dict[str, int] = json.loads('{"a": 10, "b": 20}') print(dict_val) {'a': 10, 'b': 20} PandasなどでそういったJSON形式の文字列のが含まれている場合には、データフレームなどでのapplyメソッドでloads関数を指定すれば対応ができます。 import json import pandas as pd df: pd.DataFrame = pd.read_json( 'https://github.com/simon-ritchie/athena_workshop/blob/main/workshop/user_weapon_json_str/dt%3D2021-01-01/data.json.gz?raw=true', lines=True, orient='records', compression='gzip') df['weapons'] = df['weapons'].apply(json.loads) print(df.head()) print('type:', type(df.at[0, 'weapons'])) user_id date device_type weapons 0 23475 2021-01-01 2 [{'mst_id': 1700, 'attack': 300, 'level': 2}, ... 1 23826 2021-01-01 2 [{'mst_id': 1672, 'attack': 1709, 'level': 15}... 2 2496 2021-01-01 2 [{'mst_id': 655, 'attack': 1238, 'level': 12},... 3 1192 2021-01-01 1 [{'mst_id': 56, 'attack': 871, 'level': 6}, {'... 4 26195 2021-01-01 1 [{'mst_id': 1427, 'attack': 836, 'level': 9}, ... type: <class 'list'> JSONの値をJSONの文字列に変換する: JSON_FORMAT JSON_FORMAT関数はJSONの値をJSON形式の文字列へと変換します。JSON_PARSE関数と逆の挙動をします。 SELECT JSON_FORMAT(CAST(weapons AS JSON)) AS json_str, TYPEOF(JSON_FORMAT(CAST(weapons AS JSON))) AS type FROM athena_workshop.user_weapon LIMIT 10; 実行結果の型がVARCHARになっていることが確認できます。 ※CASTで文字列に変換しただけだと正確なJSONのフォーマットにならないケースが結構ある(JSONに合わせたフォーマットではなくSQLに合わせたフォーマットになったりする)ため、その辺りでエラーなどを避けるためにもCASTではなくJSON_FORMAT関数の利用が推奨されます。 JSONの値を配列に変換する JSON_PARSEでJSON型にした値は前節までで触れたようにCAST関数で他の型に変換することができます。今回のJSONは配列になっているため配列にキャストしてみます。 辞書を含んだ配列・・・といったデータであれば、CAST(<JSONの値> AS ARRAY(ROW(<辞書のフォーマット>)))といったように、ARRAY関数にROW関数を入れ子にすることで表現できます。ROW関数はROW(<キー名> 値の型, <キー名> 値の型)といったように書きます(例 : ROW(a BIGINT, b VARCHAR))。 SELECT CAST(JSON_PARSE(weapons) AS ARRAY(ROW(mst_id VARCHAR, attack BIGINT, level BIGINT))) AS weapons FROM athena_workshop.user_weapon_json_str LIMIT 10; これでARRAYとSTRUCTを扱った時と同じような値となるので、STRUCT関係の節で触れたような必要な行列変換などを入れれば色々操作が行えます。 もしくはキーが多すぎて個別に型を定義するのが難しい・・・といった場合には、JSONの型を含んだ配列(ARRAY(JSON))といったように指定するのが楽なケースもあります。こちらの場合は行列変換などはJSONのフォーマットによる参照方法などが必要になってきます(後の節で触れます)。 SELECT CAST(JSON_PARSE(weapons) AS ARRAY(JSON)) AS weapons FROM athena_workshop.user_weapon_json_str LIMIT 10; 特定の位置のJSONの値を抽出する: JSON_EXTRACT と JSON_EXTRACT_SCALAR JSONの値の特定の位置の値を抽出するにはJSON_EXTRACT関数を使います。第一引数には対象のJSONのカラムもしくは固定値、第二引数には対象の位置の指定用の文字列を設定します。 第二引数の位置指定用の文字列はJSONPath - XPath for JSONの資料に書かれているものが使えるようです。全ては触れませんが良く使いそうなものをこの節で触れていきます。 まずは$の記述です。これはJSONのルート(一番上の階層)を示します。$を単体で指定した場合は一番上のフォルダから中身の全てのファイルが参照されるように、JSONのデータ全体の参照となるためJSON_EXTRACTを使わない場合と同じ結果になります。 SELECT JSON_EXTRACT(JSON_PARSE(weapons), '$') AS result FROM athena_workshop.user_weapon_json_str WHERE dt = '2021-01-01' LIMIT 10; JSON内の特定の位置のデータを抽出したい場合ルートのデータから子の要素にアクセスしていく必要があります。そういった場合には.のドット記号かもしくは[]の記号を使ってインデックスの指定をする形で参照します。 例えば今回扱っているデータだとルート部分が配列になっているので、配列の先頭の要素にアクセスしたい場合ドット記号を使うと.0といったように書きます。ルートの位置からその配列の先頭のデータ・・・とするため、ルートの$と合わせて$.0といったように指定します。ちなみにAthena(Presto)のSQL上だと最初のインデックスは1から始まるケースが大半ですが、JSONの場合0から始まります。最初のデータが0となり1ではないため注意が必要です。 SELECT JSON_EXTRACT(JSON_PARSE(weapons), '$.0') AS result FROM athena_workshop.user_weapon_json_str WHERE dt = '2021-01-01' LIMIT 10; JSONの配列の先頭のデータのみを抽出することができました。ドット記号を使うのと同じように[]の括弧を使って参照することもできます。例えば配列の先頭のインデックスにアクセスしたい場合には[0]といった書き方でもアクセスすることができます。 SELECT JSON_EXTRACT(JSON_PARSE(weapons), '$[0]') AS result FROM athena_workshop.user_weapon_json_str WHERE dt = '2021-01-01' LIMIT 10; 配列の先頭のデータの辞書部分でさらに特定のキーにアクセスしたいとします。例えばattackキーのデータだけ欲しい・・・といった場合を考えます。 そういった場合は子の要素には整数だけでなくキー名なども指定できるため、ドットを使う場合は$.0.attackといったように書くことで子要素にアクセスすることができます。 SELECT JSON_EXTRACT(JSON_PARSE(weapons), '$.0.attack') AS result FROM athena_workshop.user_weapon_json_str WHERE dt = '2021-01-01' LIMIT 10; []の括弧を使うこともできます。キー名で[]の括弧を使う場合にはダブルクォーテーションで囲んで書いてもダブルクォーテーションを省略してもどちらでも動くようです(JSONだとダブルクォーテーションで囲む方が自然ですが、特に必要というわけではないようです)。 SELECT JSON_EXTRACT(JSON_PARSE(weapons), '$[0][attack]') AS result FROM athena_workshop.user_weapon_json_str WHERE dt = '2021-01-01' LIMIT 10; その他にもリンク先の資料だと全てを表すアスタリスクや複数条件の[]の括弧内のコンマの記述やスライス記法なども書かれていますが、なんだかAthena上ではエラーになる?ようで、Athenaではまだ使えないのかもしれません。 JSON内の配列の全データを展開したい・・・といった場合はこの関数ではなくUNNESTなどで各行を辞書などで行列変換し、その後JSON_EXTRACT関数で各キーの値を展開する・・・とすると良いかもしれません。 なお、JSON_EXTRACT関数では抽出結果の値は全てJSONの型となります。試しに以下のようにルートの配列部分や配列内の辞書、さらにその辞書の特定のキーの値の型をそれぞれ表示してみても全てjsonと表示されます。 SELECT TYPEOF(JSON_EXTRACT(JSON_PARSE(weapons), '$')) AS array_type, TYPEOF(JSON_EXTRACT(JSON_PARSE(weapons), '$[0]')) AS dict_type, TYPEOF(JSON_EXTRACT(JSON_PARSE(weapons), '$[0][attack]')) AS scalar_type FROM athena_workshop.user_weapon_json_str WHERE dt = '2021-01-01' LIMIT 10; 一方でJSON_EXTRACT_SCALAR関数は文字列で値が返ってくるようです。数値などの他の型であってもVARCHARに結果はなります(文字列以外はどのみちCASTが必要になりそうなので、あまり使い道は無い・・・?かもしれません)。 SELECT JSON_EXTRACT_SCALAR(JSON_PARSE(weapons), '$[0][attack]') AS attack, TYPEOF(JSON_EXTRACT_SCALAR(JSON_PARSE(weapons), '$[0][attack]')) AS attack_type FROM athena_workshop.user_weapon_json_str WHERE dt = '2021-01-01' LIMIT 10; Pythonでの書き方 json.lodasで文字列からリストに変換した後に、追加で特定の位置の値を抽出する関数(今回はextract_attack_from_weaponsという関数名にしてあります)を設けてそちらもapplyメソッドで反映すれば抽出できます。 import json from typing import Any, Dict, List import pandas as pd def extract_attack_from_weapons(weapons: List[Dict[str, Any]]) -> int: """ 指定された武器データの配列から、先頭の武器の攻撃力の値を抽出する。 Parameters ---------- weapons : list of dict 武器データを格納した配列。 Returns ------- attack : int 抽出された攻撃力。空のリストの場合には0が設定される。 """ if not weapons: return 0 return weapons[0]['attack'] df: pd.DataFrame = pd.read_json( 'https://github.com/simon-ritchie/athena_workshop/blob/main/workshop/user_weapon_json_str/dt%3D2021-01-01/data.json.gz?raw=true', lines=True, orient='records', compression='gzip') df['weapons'] = df['weapons'].apply(json.loads) df['attack'] = df['weapons'].apply(extract_attack_from_weapons) print(df[['attack', 'weapons']].head()) attack weapons 0 300 [{'mst_id': 1700, 'attack': 300, 'level': 2}, ... 1 1709 [{'mst_id': 1672, 'attack': 1709, 'level': 15}... 2 1238 [{'mst_id': 655, 'attack': 1238, 'level': 12},... 3 871 [{'mst_id': 56, 'attack': 871, 'level': 6}, {'... 4 836 [{'mst_id': 1427, 'attack': 836, 'level': 9}, ... 値がJSONで扱えるスカラー値かどうかの真偽値を取得する: IS_JSON_SCALAR IS_JSON_SCALAR関数では特定の値がJSONで扱える単一のスカラー値(単一の数値、文字列、真偽値、NULLなど)かどうかの真偽値を返します。単一の数値などであればtrue、そうではなく配列などの複数の値を含むデータなどであれはfalseとなります。 以下のSQLでは整数・文字列・真偽値・配列の固定値に対してIS_JSON_SCALAR関数を実行していますが、配列のみfalseになっていることが確認できます。 SELECT IS_JSON_SCALAR(CAST(10 AS JSON)) AS int_is_json_scalar, IS_JSON_SCALAR(CAST('Hello' AS JSON)) AS string_is_json_scalar, IS_JSON_SCALAR(CAST(true AS JSON)) AS bool_is_json_scalar, IS_JSON_SCALAR(CAST(ARRAY[1, 2] AS JSON)) AS array_is_json_scalar JSONの配列内に特定の値がふくまれているかの真偽値を取得する: JSON_ARRAY_CONTAINS JSON_ARRAY_CONTAINS関数はJSONの配列内に特定の値が含まれているかどうかの真偽値を取得することができます。存在すればtrue、存在しなければfalseとなります。通常の配列でもこの辺の関数はありますが、JSONのまま扱いたい場合などに使用します。 第一引数には対象のJSONの配列、第二引数には検索対象の値を指定します。 SELECT JSON_ARRAY_CONTAINS(CAST(ARRAY[1, 2, 3] AS JSON), 2) AS result ちなみにですが、JSONの配列の形式の文字列であればキャストやJSON_PARSE関数などを通さなくても文字列のまま処理が流せるようです。例えば以下のように文字列を直接指定してもtrueが返ります。 SELECT JSON_ARRAY_CONTAINS('[1, 2, 3]', 2) AS result Pythonでの書き方 正直この辺はJSON変換が必要になる以外は配列関係に触れた以前の記事のままなのでリンクだけ貼って割愛します。inキーワードで対応ができます。 JSONの配列の長さを取得する: JSON_ARRAY_LENGTH JSON_ARRAY_LENGTH関数ではJSONの配列の長さ(値の件数)を取得することができます。 以下のSQLでは3件の値の配列なので3が返っています。 SELECT JSON_ARRAY_LENGTH(CAST(ARRAY[1, 2, 3] AS JSON)) AS length こちらもJSON形式の文字列であればキャストやJSON_PARSE関数などを省いても動くようです。 SELECT JSON_ARRAY_LENGTH('[1, 2, 3]') AS length Pythonでの書き方 json.loads関数でJSONをリストに変換した後に、len関数でリストの値の件数が取れるのでそちらをapplyメソッドで反映すれば取れます。 import json from typing import Any, Dict, List import pandas as pd df: pd.DataFrame = pd.read_json( 'https://github.com/simon-ritchie/athena_workshop/blob/main/workshop/user_weapon_json_str/dt%3D2021-01-01/data.json.gz?raw=true', lines=True, orient='records', compression='gzip') df['weapons'] = df['weapons'].apply(json.loads) df['length'] = df['weapons'].apply(len) print(df[['length', 'weapons']].head()) length weapons 0 61 [{'mst_id': 1700, 'attack': 300, 'level': 2}, ... 1 44 [{'mst_id': 1672, 'attack': 1709, 'level': 15}... 2 60 [{'mst_id': 655, 'attack': 1238, 'level': 12},... 3 26 [{'mst_id': 56, 'attack': 871, 'level': 6}, {'... 4 77 [{'mst_id': 1427, 'attack': 836, 'level': 9}, ... 特定位置のJSONの要素の数を取得する: JSON_SIZE JSON_SIZE関数は特定の位置のJSONの要素の数を取得します。JSON_ARRAY_LENGTH関数とは異なり第二引数に位置の指定が必要になります。第二引数の位置の指定にはJSON_EXTRACT関数の節で触れたようなJSONPathの記法の文字列が必要になります。 対象は配列もしくは辞書の位置でのみ正常な値が取れます。それ以外のスカラー値の位置では返却値は0となります。 以下のSQLでは2次元配列で配列の先頭の位置($.0)の配列の値([1, 2, 3])の件数を取得しています。 SELECT JSON_SIZE('[[1, 2, 3], [4]]', '$.0') AS length 結果は3となります。 参考文献・参考サイトまとめ The Applied SQL Data Analytics Workshop: Develop your practical skills and prepare to become a professional data analyst, 2nd Edition Data Types Map Functions and Operators 【Python】dictをfilterする カーディナリティ 【cardinality】 SQL で縦横変換まとめ(pivot と unpivot) PrestoのArray, Mapの使い方調査 [Amazon Athena]一見json配列に見えるvarcharのデータをパースして集計できる形式に変換する JSON Functions and Operators JSONPath - XPath for JSON
  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む