Hatena::ブログ(Diary)

ablog このページをアンテナに追加 RSSフィード Twitter

2018-05-08

別アカウントのS3バケットに対して Athena でクエリを実行する

メモ


aws s3 cp s3://***/data/gz/sh10/sales/ s3://az-swingbench/data/gz/sh10/sales/ --recursive
{
   "Version": "2012-10-17",
   "Id": "MyPolicyID",
   "Statement": [
      {
          "Sid": "MyStatementSid",
          "Effect": "Allow",
          "Principal": {
             "AWS": "arn:aws:iam::012345678901:root"
          },
          "Action": [
             "s3:GetBucketLocation",
             "s3:GetObject",
             "s3:ListBucket",
             "s3:ListBucketMultipartUploads",
             "s3:ListMultipartUploadParts",
             "s3:AbortMultipartUpload",
             "s3:PutObject"
          ],
          "Resource": [
             "arn:aws:s3:::az-swingbench",
             "arn:aws:s3:::az-swingbench/*"
          ]
       }
    ]
 }
CREATE DATABASE IF NOT EXISTS sh10_gz_01;
CREATE EXTERNAL TABLE IF NOT EXISTS sh10_gz_01.sales(
  prod_id DECIMAL(38,0),
  cust_id DECIMAL(38,0),
  time_id TIMESTAMP,
  channel_id DECIMAL(38,0),
  promo_id DECIMAL(38,0),
  quantity_sold DECIMAL(10,2),
  seller INT,
  fulfillment_center INT,
  courier_org INT,
  tax_country VARCHAR(3),
  tax_region VARCHAR(3),
  amount_sold DECIMAL(10,2)
)
ROW FORMAT DELIMITED
  FIELDS TERMINATED BY '\t'
  ESCAPED BY '\\'
  LINES TERMINATED BY '\n'
LOCATION 's3://az-swingbench/data/gz/sh10/sales/'
;
select count(*) from sh10_gz_01.sales;

参考

スパム対策のためのダミーです。もし見えても何も入力しないでください
ゲスト


画像認証

トラックバック - http://d.hatena.ne.jp/yohei-a/20180508/1525790433