动手实践Amazon Athena

下面的动手的部分是基于东京区域的服务：

步骤 1：准备查询的数据

通过以下网址下载美国交通事故开放数据https://catalog.data.gov/dataset/traffic-collision-data-from-2010-to-present
打开Amazon S3控制台https://console.aws.amazon.com/s3/。
在东京区域创建一个S3存储桶取一个具有唯一性的名字例如athena-demo-tokyo-trafficdata-<当前日期>
在桶内创建一个文件夹traffic-collision-one-file并上传源数据

步骤 2：创建一张Athena表并查询

2.1使用DDL SQL语句创建您的 Amazon Athena表

(1)通过以下网址打开 Athena控制台：https://console.aws.amazon.com/athena/。（登陆的IAM user需要有AmazonAthenaFullAccess策略）

(2)通过在查询栏输入“create database demo；” 并且点击run query创建一个新database。

(3)Database在下拉栏里选择新建的demo，然后在查询栏里输入create table DDL创建一个新表。把S3存储桶换成步骤1中新建的S3路径。

SQL

CREATE EXTERNAL TABLE traffic_collision_data(
  dr_number string , 
  date_reported string , 
  date_occurred string , 
  time_occurred string , 
  area_id string , 
  area_name string , 
  reporting_district string , 
  crime_code string , 
  crime_code_description string , 
  mo_codes string , 
  victim_age string , 
  victim_sex string , 
  victim_descent string , 
  premise_code string , 
  premise_description string , 
  address string , 
  cross_street string , 
  location string , 
  zip_codes string , 
  census_tracts string , 
  precinct_boundaries string , 
  la_specific_plans string , 
  council_districts string , 
  neighborhood_councils string)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde' 
WITH SERDEPROPERTIES ('skip.header.line.count'='1','serialization.format' = ',',
  'quoteChar' = '"',
  'field.delim' = ',' ) 
LOCATION 's3://athena-demo-tokyo-trafficdata-<当前日期>/traffic-collision-one-file/';

(4)在查询栏里输入“select * from traffic_collision_data limit 10”来观察数据含义。点击Save as 给查询命名，以便日后重复使用。

2.2建立逻辑视图来方便终端用户或展示工具查询

(1)统计每天发生的交通事故数量，在查询栏查询下列SQL

SQL

SELECT date_reported, count(*) as num_accident
FROM traffic_collision_data 
group by date_reported
order by date_reported;

(2)点击Create，选择create view from query并取名为daily_accident_count_report。成功创建后查询栏会出现创建视图的DDL语句，日后也可通过DDL SQL语句创建。

(3)查询视图的内容 “select * from daily_accident_count_report”

2.3把数据从单一CSV文件转化为带分区的parquet格式

(1)在查询栏里键入一下create table with SQL语句。把external_location换成步骤1中的S3桶。此语句会创建一张新表和新S3地址，并且新S3地址会根据area_id做分区键，新数据会存为压缩的parquet格式可以极大的减小数据量。

SQL

CREATE TABLE traffic_collision_data_partitioned
  WITH (format='parquet', 
  external_location='s3://athena-demo-tokyo-trafficdata-<当前日期>/traffic-collision-partitioned/',
  partitioned_by= ARRAY['area_id'],
  parquet_compression = 'SNAPPY') AS
SELECT
  DR_Number,
  date_parse(date_reported,'%Y-%m-%dT%T.000') as date_reported, 
  time_occurred,
  area_name,
  reporting_district,
  crime_code,
  crime_code_description,
  mo_codes,
  victim_age,
  victim_sex,
  victim_descent,
  premise_description,
  address,
  location,
  date_parse(date_occurred,'%Y-%m-%dT%T.000') as date_occurred,
  zip_codes,
  area_id
FROM
  traffic_collision_data;

(2)可以看到新建的表是一个具有分区的表，并且新的S3路径带有分区列信息。

步骤3:利用workgroups管理查询资源

1.切换到workgroup面板，点击create workgroup

2.创建一个新的workgroup。

3.点击切换workgroup。

4.点击view details可以更改workgroup设置，选择Data Usage Controls面板，添加每条查询扫过的数据量上限。

5.添加针对每个workgroup指定时间段扫过数据量的上限。

6.创建一个只有权限使用adhoc workgroup的IAM用户。用该用户登陆AWS控制台然后尝试切换成primary workgroup然后查询。可以看到查询报没有权限的错误。

把以下IAM策略赋予该用户，并把其中的<AWS账号>替换成自己的AWS账号（12位数字）

Json

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": [
                "glue:GetDatabases",
                "glue:GetTables",
                "glue:GetTable",
                "glue:GetPartitions"
            ],
            "Resource": [
                "arn:aws:glue:*:<AWS账号>:catalog",
                "arn:aws:glue:*:<AWS账号>:database/*",
                "arn:aws:glue:*:<AWS账号>:table/*"
            ]
        },
        {
            "Effect": "Allow",
            "Action": [
                "athena:ListWorkGroups"
            ],
            "Resource": [
                "*"
            ]
        },
        {
            "Effect": "Allow",
            "Action": [
                "athena:StartQueryExecution",
                "athena:GetQueryResults",
                "athena:DeleteNamedQuery",
                "athena:GetNamedQuery",
                "athena:ListQueryExecutions",
                "athena:StopQueryExecution",
                "athena:GetQueryResultsStream",
                "athena:ListNamedQueries",
                "athena:CreateNamedQuery",
                "athena:GetQueryExecution",
                "athena:BatchGetNamedQuery",
                "athena:BatchGetQueryExecution",
                "athena:GetWorkGroup"
            ],
            "Resource": [
                "arn:aws:athena:*:<AWS 账号>:workgroup/adhoc"
            ]
        },
        {
            "Effect": "Allow",
            "Action": [
                "s3:List*",
                "s3:Get*",
                "s3:Put*"
            ],
            "Resource": [
                "*"
            ]
        }
    ]
}

扩展阅读：

Amazon Athena产品文档：https://docs.aws.amazon.com/zh_cn/athena/latest/ug/what-is.html

Amazon Athena Demo视频：https://aws.amazon.com/athena/getting-started/

作者介绍：

!

### 贺浏璐

AWS解决方案架构师，负责AWS云计算方案的咨询和架构设计，同时致力于大数据方面的研究和应用。曾担任亚马逊大数据团队数据工程师，在大数据架构，数据管道业务处理，和Business Intelligence方面有丰富的实操经验。

本文转载自AWS技术博客。

原文链接：https://amazonaws-china.com/cn/blogs/china/big-data-analysis-with-serverless-architecture-via-amazon-athena/

创作场景

通过 Amazon Athena 进行无服务器架构的大数据分析（二）