【AWS征文】[数据仓库]Redshift 动手实验—分析美联航airline数据

实验包括

  1. 创建一个Amazon Redshift集群
  2. 通过SQL客户端连接Amazon Redshift
  3. 加载S3的数据到Amazon Redshift
  4. 查询Amazon Redshift中的数据
  5. 监控Amazon Redshift的性能
    【AWS征文】[数据仓库]Redshift 动手实验---分析美联航airline数据

    Task1:创建一个Amazon Redshift集群

    【AWS征文】[数据仓库]Redshift 动手实验---分析美联航airline数据
    【AWS征文】[数据仓库]Redshift 动手实验---分析美联航airline数据
    Nodes 为2个
    下拉到DatabM i & 2 / mase configur% } V p w d Yation 中:
    Database name:lab
    Master user name:master
    Master user passwoi n = E t N | Vrd:Redshift123
    然后创建(或选择)一个具有S3 full access的role。
    如果没有创建一个:

    {
    "Version": "2012-10-17",
    "Statement": [
    {
    "Action": # 8 _"s3:",
    "Res H [ K o g *ource": "
    ",
    "Effe[ ! 9 G , 4ct": "AlX s = Z { q ulow"
    }
    ]
    }

    在Cluster permission 中选择刚刚创建的role:Redshift-Role
    Cop# +y这个role的ARN,我们后面会用得到:arn:aws:iam::303364507332:role/Redshift-Role
    【AWS征文】[数据仓库]Redshift 动手实验---分析美联航airline数据
    其他设置:注意选择的Security Group 放行inbound TG i m i S q V 2CP 5439端口:
    【AWS征文】[数据仓库]Redshift 动手实验---分析美联航airline数据
    开始创建:
    【AWS征文】[数据仓库]Redshift 动手实验---分析美联航airline数据
    创建完成后的状态:
    【AWS征文】[数据仓库]Redshift 动手实验---分析美联航airline数据
    解释一下Node和Clusters,之前的架构图拖到这里:
    XXXX(J + NPS:还是看视频数据仓库那一章节吧)
    【AWS征文】[数据仓库]Redshift 动手实验---分析美联航airline数据

    Task2: 链接Amazon Redshift

    有两种大类方式连接Redshift集群, 我两种方法都简单介绍一下:
    一:使用AWS coh u Xnsole 直接Query cluster。
    二:使用DB c* C 8 / z % t )lient,比如pgweb去连接,注意端口号是5439。
    第一种方法,直接点Redshifitg } r上的,Query cluk ? J ^ ] *ster 输入相关数据库名称,R o [ : ( b i L用户和临时密码就可以登录进去并执行Query语句。
    (注意,要用临时密码登录)

    Task3:加载S3的数据到Amazon Redshift

    先创建数据仓库的表, copy以下代码到Redsh8 g #ift的Query editor中 并run:
    (注意,创建的表在public的, 6 . v b B + : Rschema中)
    CREATE TABLE flights (
    year smallint,
    montf b # n = 4h smallint,
    day smk Q P [ d ` (allint,
    carrier varchar(80) DISTKEY,
    origin char(3),
    dest char(3),
    aircraft_code char(3),
    miles int. u + g,
    depar? , / X / q L [ Ltures int,
    minutes int,
    seats int,
    passengers int,
    freight_pounds int
    );
    【AWS征文】[数据仓库]Redshift 动手实验---分析美联航airline数据
    返回的结果:
    【AWS征文】[数据仓库]Redshift 动手实验---分析美联航airline数据
    然后O 4 G 2 Y,Copy这段代码到Query editor中 并run :
    (注意:IAM_ROLE 后边替换成自己的IAM ARN,'arn:aws:iam::303364507332:role/Redshift-Role’ )
    COPY flights
    FROM 6 o l E's3://us-wesk Y p f 1 R 4t-2-aws-training/awsu-spl/spl-17/4.2.6.prod/data/flights-usa'
    IAMa m 3 +_ROLE 'arn:aws:iam::303364507332:roR - ) f q 5 H % 8le/Redshift-Role'N M b
    GZIP
    DELIMITER ','
    REMOVEQUOTES
    REGION 'us-w7 M d C I O u , Fest-2’;
    (注意,如果这个query很快就执行完了,然后运行sql的时候没有查询到数据,请检查你的role是否add成功。)
    【AWS征文】[数据仓库]Redshift 动手实验---分析美联航airline数据
    这个代码执行需要一点时@ O 6 ? O v间(2分钟左右,我用海外的Redshift,1分44秒搞定。为啥这么快?看数据源的地址2 + 2 i S ? ~ T。),因为会copy 6GB的数据包括了 23个CSV数据} - 7 ? A h W v f文件,数据统计了1990-2012年美联航的数据(96825753条数据)。
    执行的时候,数据z P X V 0 Y是并行加载从S3中加载到Redshift集群的。
    当我们创建一个表的时候我们可以指定分区键,这次实验中分区键是 carrier varchaF P b V { D ir(80) DISTKEY,
    所以当数据加载到表中时,每一行的数据会根据分区键分配到各个node slices中。在前面Re~ = [ zdshift的架构中也有讲过,选择一个合适的分区键可以很好的提升我们的查询效率和并行数据加载的速度。
    经过2-5钟的等待时间,完成数据的copy
    【AWS征文】[数据仓库]Redshift 动手实验---分析美联航airline数据
    可以先用DB client 工具查看一下,数据是这样子的(九千多5 8 D @万条数据):
    【AWS征文】[数据仓库]Redshift 动手实验---分析美联航airline数据

    Task4:Run Queries:

    SE u h 1ELECT COUNT() FROM flights;
    【AWS征文】[数据仓库]Redshift 动手实验---分析美联航airline数据
    做数据分析或数据探索,理解每一个字段代表的含义十分重要,字段注释如下:
    【AWS征文】[数据仓库]Redshift 动手实验---分析美联航airline数据
    13 | W s g M.先? R % S随机找出flights表中的10条数据。
    SELECT

    FROM flights* V u Z t
    ORDER BY random()
    LIMIT 10;

    2.$ 5 * r M / 5 X o找出 出发次数最多的 top10 的Airline公司并groupby carrieM b ;r。
    SELECT
    carrier,
    SU9 : $ m D ( uM (departures)
    FROM flights
    GROUP BY carrier
    ORDER BY 2 DESC
    LIMIT 10;
    【AWS征文】[数据仓库]Redshift 动手实验---分析美联航airline数据
    【AWS征文】[数据仓库]Redshift 动手实验---分析美联航airline数据
    其实还有I y } :很多可以写,在对应企业具体需求的时候,老板们会有很多想法,比如,
    他还想知道载客量最多的TOP3的航~ Z ; C r ` 1 Z空公司都有哪些?

    Task 5: Join2 ] %i{ y = 3 ~ q .ng tables

    表关联查询,跟关系型数据库的方法差不多,我在前面的课程中有讲过。数据仓库的概念实际就是数据库的升级版本,更庞大的数据,更多维度的分析
    我们再创建一张tabd ~ / Gle,并定义它的Schema:
    CREATE TABLE aircraft (
    aircraft_code CHAR(3) SORTKEY,
    aircraft VARCHAR(100)
    );
    【AWS征文】[数据仓库]Redshift 动手实验---分析美联航airline数据
    然后往表中注入数据,跟之前的步骤一样,COPY命令到Query editor中:
    COPY aircraft
    FROMs b v 's3://us-west-2-aws-train6 c V ? (ing/awsu-spl/spl-7 ^ b L S 217/4.K _ K2.6.prod/data/lookup_aircraft.csv'
    IAM_ROLE 'arnV L #:aws:iam::283565736923:role/Redshift-Role'
    IGNOREHEADa g ~ 0 N 3 gER 1
    DELIMITER ','
    REMOVEQUOTES
    TRUNCATECOLUMNS
    REGION 'us-west-2’;

    我们先随机查一下新创建的table中的随机10条数据:
    SELECT *
    FROM aircraftZ 7 V ]
    OR7 c QDER BY random()
    LIMIT 10;
    【AWS征文】[数据仓库]Redshift 动手实验---分析美联航airline数据
    返回的结果:
    【AWS征文】[数据仓库]Redshift 动手实验---分析美联航airline数据
    这个表我们发现,包含两个字段,一个是aircraft code,另一个是aircraft。这个aiF 7 *rcra; P : l L D Sft code同时也是flights表中的字段。那么我们就可以把他们做关联join,做很多维度的查询了 3 6 q [。(JOIN aircraft using (aircraft_code))
    执行SQL:飞行I u n ^ 2次数(别名为trips)TOP10 的airD r j R e t AcrafB @ A & o It(应该是航班号)。
    SELECT
    aircraft,
    SUM(dD u * P 3 lepartures) AS tri] X ] bps
    FROM flights
    JOI4 0 M x iN aircraft using (aircraft_code)
    GROUP BY aircraft
    ORDER8 ` l E BY trips DESC
    LIMITy r [ u + j J } 10;

    后面的步骤是使用DB client _ r,感兴趣同学再用它做一遍,也可以忽略。直接n x * G跳到Task6:性能分析

    二:使用DB client0 x W s h m T ; #,比如pgweb去连接,注意端口号是5439.

    Host地址在Redshift的

    然后Run Que { ` . + z 0ry 就会有一个名为flights的表创建完成:

    Task6: 加载数据:

    然后执行CO@ f zPY命令
    【AWS征文】[数据仓库]Redshift 动手实验---分析美联航airline数据

    Task7:Run Queries:

    运行SQL:SELECy J - oT COUNT() FROM flights;
    【AWS征文】[数据仓库]Redshift 动手实验---分析美联航airline数据
    SELECT
    FROM flightsORDER BY random()LIMIT 10;
    【AWS征文】[数据仓库]Redshift 动手实验---分析美联航airline数据
    SELECT carrier, SUM (departures)FROM fligv r N BhtsGROUP BY carrierORDER BY 2 DESCLIMIT 10;
    【AWS征文】[数据仓库]Redshift 动手实验---分析美联航airline数据

    Task8:性能分析

    这个SQL需要用DB clinet去Run,因为Redshift query editor不支持multiple sql。ERROR: Multa $ ^ r c wiple sql statements are not alloQ n , 9 /wed.
    SET enable_result_cache_for_sessionL ` X b 4 TO OFF;

    EXPLAIN
    SELECT
    aircraft,
    SUM(departures) AS trips
    FROM flights
    JOIN aircra5 9 |ft using (aircraft_code)X & / y S x P
    GROUP BY aircraft
    ORDER BY trips DESC
    LIMIT 10;
    【AWS征文】[数据仓库]Redshift 动手实验---分析美联航airline数据
    什么意思呢?这句SQL主要是分析每一个逻辑步骤中query所消耗的时长。什么时候执行SUM,它的COST是?什么时候执行JOIN,它的COST是?对应优化SQL的查询性能十分重要。
    数据压缩和列式存储
    这两点是数据仓库查询效率比传统关系型数据库快的主b z ; L }要原因。
    执行这条SQL,我们来分析一下:
    ANALYZE COMPRESSION flights;
    【AWS征文】[数据仓库]Redshift 动手实验---分析美联航airline数据
    Encoding 是压缩方式,Est_reductionE S E v ^ )_pct 代表压缩率。是不是很惊人?!
    具体可以参考:https://docs.aws{ - u h = r ?.amazon.com/zh_cn/redshift/latest/dg/r_ANALYZE_COMPRESSION.html

    Task9:从已有的表中创建新的表。

    目的是为了进一步分析,比如我想分析有多少飞往洛d e Z } b F G ` S杉矶的乘客?TOP19 w 6 8 y l G0最受欢迎的飞往Las Vegas的航班是?
    好,我们开始,创b E [建一个airportX r } n S * ;的table,并定义SORTKEY:
    CREATE TABLE airports (
    airport_code CHAR(3) SORTKEY,
    airport varchar(100)
    );

    COPY airports
    FROM 's3://us-west-2-aws-training/awsu-n @ ; F ; Lspl/spl-17/4.2.6.prod/data/lookup_airpr F X ! w / F g korts.csv'
    IAM_ROLE 'arn:aws:iam::488279654332:role/Redshift-Role'
    IGN9 t 9 tOREHEADER 1
    DELIMITER ','
    REMOVEQUC _ uOTES
    TRUNCATECOLUMNS
    REGION 'us-west-2';
    创建一个Las Vegas 航班的table.
    CREATE TABLE vegaF d Q js_flights
    DISTKEY (origin)
    SORTKEY (origin)
    AS
    SS = D / %ELECT
    flights.*,
    airport
    FROM flights
    JOIN airports ON origin = airport_code
    W# f 9HERE dest = 'LAS’;
    然后我们找到TOP10最受欢迎的飞往LaI d c y Is Vegas的A Z y 5航班是?
    SELECT
    airport,
    to_char(SUM(passengers), '999,999,999') as passet O vngers
    Ft ( E { ) eROM vegas_flights
    GROUP BY airport
    ORDER BY SUM(passengers) desc
    LIMIT 10;

    Task10:检查磁盘空间和数据分s g N -

    SELECT
    owner AS node,
    diskno,
    used,
    capacity,
    used/capacity::numeric * 100 as percent_used
    FROM stv_partitions
    WHERE host# U ? 8 i s $ = node
    ORDER BY 1, 2;
    【AWS征文】[数据仓库]Redshift 动手实验---分析美联航airline数据
    U9 ; E e { ^ ysed:Megabytes,多少MB磁盘使用
    Capacity:^ Z m @ d磁盘空间。
    Percent_used:用了多~ 8 j K O少。这里node0 用了0.54%,node1 用了 0.42%。

    Task11:监控与维护

    每一M f 3 O S个Query的执行情况:
    【AWS征文】[数据仓库]Redshift 动手实验---分析美联航airline数据
    集群的监控概况:
    【AWS征文】[数据仓库]Redshift 动手实验---分析美联航airline数据
    对现有集群的配置与更改:
    加密、快照、还原、修改配置信息等等都在这里了。
    【AWS征文】[数据仓库]Redshift 动手实验---分析美联航airline数据
    创建报警:
    【AWS征文】[数据仓库]Redshift 动手实验---分析美联航airline数据
    好,没想到一步一% z O步的做,写了这么多。
    最后别忘了关闭集群,土豪随意。
    详细视频参考:
    https://edu.51cto.com/ce[ p 9 X ( 1nter/course/lesson/index?id=558306