AWS Data Engineering Bootcamp explicado de forma simples (com diagramas e código real)

-- Etapa 1 : Configurar o bucketing durante a criação da tabela
-- Substitua user_id pela sua coluna de alta cardinalidade
CLUSTERED BY (user_id) INTO 8 BUCKETS

-- Exemplo completo com CTAS (Create Table As Select)
CREATE TABLE taxidata.transactions_bucketized
WITH (
    format = 'PARQUET',
    external_location = 's3://mon-bucket/transactions-bucketized/',
    bucketed_by = ARRAY['user_id'],
    bucket_count = 8
) AS
SELECT *
FROM taxidata.transactions_source;

-- CTAS (Create Table As Select) com particionamento
-- Conversão CSV → Parquet COM particionamento
CREATE TABLE taxidata.yellow_partitioned
WITH (
    format = 'PARQUET',
    external_location = 's3://taxi-datalake/yellow-partitioned/',
    partitioned_by = ARRAY['year', 'month']
) AS
SELECT
    vendor, pickup, dropoff, count,
    distance, ratecode, storeflag, pulocid, dolocid,
    paytype, fare, extra, mta_tax, tip, tolls, surcharge, total,
    YEAR(pickup) AS year,
    MONTH(pickup) AS month
FROM taxidata.yellow;

-- Após adicionar dados, descobrir as novas partições
MSCK REPAIR TABLE taxidata.yellow_partitioned;

-- Adicionar manualmente uma partição específica
ALTER TABLE taxidata.yellow_partitioned
ADD PARTITION (year='2017', month='02')
LOCATION 's3://taxi-datalake/yellow-partitioned/year=2017/month=02/';

-- Consulta SEM particionamento : scan de TODA a tabela
SELECT COUNT(*), SUM(fare) AS total_fares
FROM taxidata.yellow
WHERE YEAR(pickup) = 2017 AND MONTH(pickup) = 1;
-- → Scan : 100% dos dados (ex: 1 TB)

-- Consulta COM particionamento : scan apenas da partição relevante
SELECT COUNT(*), SUM(fare) AS total_fares
FROM taxidata.yellow_partitioned
WHERE year = 2017 AND month = 1;
-- → Scan : ~1/36 dos dados (se 3 anos × 12 meses)
-- → Redução de ~97% dos dados escaneados !

-- Criar o banco de dados taxidata no AWS Glue Data Catalog
CREATE DATABASE taxidata;

-- Criar a tabela externa yellow sobre os dados S3
CREATE EXTERNAL TABLE IF NOT EXISTS taxidata.yellow (
   `vendor`    string,
   `pickup`    timestamp,
   `dropoff`   timestamp,
   `count`     int,
   `distance`  int,
   `ratecode`  string,
   `storeflag` string,
   `pulocid`   string,
   `dolocid`   string,
   `paytype`   string,
   `fare`      decimal,
   `extra`     decimal,
   `mta_tax`   decimal,
   `tip`       decimal,
   `tolls`     decimal,
   `surcharge` decimal,
   `total`     decimal
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'
WITH SERDEPROPERTIES (
   'serialization.format' = ',',
   'field.delim' = ','
) LOCATION 's3://aws-tc-largeobjects/CUR-TF-200-ACDSCI-1/Lab2/yellow/'
TBLPROPERTIES ('has_encrypted_data'='false');

-- Visualizar as 10 primeiras linhas
SELECT * FROM taxidata.yellow LIMIT 10;

-- Contar o número total de viagens
SELECT COUNT(*) AS total_trajets FROM taxidata.yellow;

-- Verificar os tipos de pagamento disponíveis
SELECT DISTINCT paytype, COUNT(*) AS nb_trajets
FROM taxidata.yellow
GROUP BY paytype
ORDER BY nb_trajets DESC;

-- Criar uma tabela para os dados de janeiro de 2017 apenas
-- Mais rápida para testes e validação
CREATE EXTERNAL TABLE IF NOT EXISTS taxidata.jan (
   `vendor`    string,
   `pickup`    timestamp,
   `dropoff`   timestamp,
   `count`     int,
   `distance`  int,
   `ratecode`  string,
   `storeflag` string,
   `pulocid`   string,
   `dolocid`   string,
   `paytype`   string,
   `fare`      decimal,
   `extra`     decimal,
   `mta_tax`   decimal,
   `tip`       decimal,
   `tolls`     decimal,
   `surcharge` decimal,
   `total`     decimal
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'
WITH SERDEPROPERTIES (
   'serialization.format' = ',',
   'field.delim' = ','
) LOCATION 's3://aws-tc-largeobjects/CUR-TF-200-ACDSCI-1/Lab2/January2017/'
TBLPROPERTIES ('has_encrypted_data'='false');

-- Criar uma view para pagamentos com cartão de crédito
-- paytype = '1' significa pagamento com cartão
CREATE VIEW taxidata.cctrips AS
SELECT
    vendor,
    SUM(fare)  AS total_fares_cc,
    COUNT(*)   AS nb_trajets_cc,
    AVG(fare)  AS tarif_moyen_cc
FROM taxidata.yellow
WHERE paytype = '1'
GROUP BY vendor;

-- Usar a view
SELECT * FROM taxidata.cctrips;

-- Criar uma view para pagamentos em dinheiro
-- paytype = '2' significa pagamento em dinheiro
CREATE VIEW taxidata.cashtrips AS
SELECT
    vendor,
    SUM(fare)  AS total_fares_cash,
    COUNT(*)   AS nb_trajets_cash,
    AVG(fare)  AS tarif_moyen_cash
FROM taxidata.yellow
WHERE paytype = '2'
GROUP BY vendor;

-- Usar a view
SELECT * FROM taxidata.cashtrips;

-- View de comparação combinando cartão de crédito e dinheiro
-- Uso de CTE (Common Table Expressions) com WITH
CREATE VIEW taxidata.comparepay AS
WITH
    cc AS (
        SELECT
            vendor,
            SUM(fare) AS cctotal,
            COUNT(*)  AS nb_cc
        FROM taxidata.yellow
        WHERE paytype = '1'
        GROUP BY vendor
    ),
    cash AS (
        SELECT
            vendor,
            SUM(fare) AS cashtotal,
            COUNT(*)  AS nb_cash
        FROM taxidata.yellow
        WHERE paytype = '2'
        GROUP BY vendor
    )
SELECT
    cc.vendor,
    cc.cctotal,
    cc.nb_cc,
    cash.cashtotal,
    cash.nb_cash,
    ROUND(cc.cctotal / cash.cashtotal, 2) AS ratio_cc_cash
FROM cc
JOIN cash ON cc.vendor = cash.vendor;

-- Consulta na view
SELECT * FROM taxidata.comparepay ORDER BY ratio_cc_cash DESC;

-- Listar as views disponíveis
SHOW VIEWS IN taxidata;

-- Ver a definição de uma view
SHOW CREATE VIEW taxidata.comparepay;

-- Substituir uma view existente
CREATE OR REPLACE VIEW taxidata.cctrips AS
SELECT
    vendor,
    SUM(fare) AS total_fares_cc,
    COUNT(*) AS nb_trajets_cc
FROM taxidata.yellow
WHERE paytype = '1'
GROUP BY vendor;

-- Excluir uma view
DROP VIEW taxidata.cashtrips;

AWSTemplateFormatVersion: 2010-09-09
Description: "Requête Athena nommée pour l'analyse des taxis NYC"

Resources:
  AthenaNamedQuery:
    Type: AWS::Athena::NamedQuery
    Properties:
      Database: "taxidata"
      Description: "Sélection des trajets dont le montant dépasse 100 $"
      Name: "FaresOver100DollarsUS"
      QueryString: >
        SELECT
            distance,
            paytype,
            fare,
            tip,
            tolls,
            surcharge,
            total
        FROM yellow
        WHERE total >= 100.0
        ORDER BY total DESC

Outputs:
  AthenaNamedQuery:
    Description: "L'ID de la requête nommée Athena créée"
    Value: !Ref AthenaNamedQuery