AWS Data Engineering Bootcamp Explained Simply (with Diagrams and Real Code)

-- Step 1: Configure bucketing when creating the table
-- Replace user_id with your high-cardinality column
CLUSTERED BY (user_id) INTO 8 BUCKETS

-- Complete example with 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) with partitioning
-- CSV → Parquet conversion WITH partitioning
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;

-- After adding data, discover the new partitions
MSCK REPAIR TABLE taxidata.yellow_partitioned;

-- Manually add a specific partition
ALTER TABLE taxidata.yellow_partitioned
ADD PARTITION (year='2017', month='02')
LOCATION 's3://taxi-datalake/yellow-partitioned/year=2017/month=02/';

-- Query WITHOUT partitioning: scan the ENTIRE table
SELECT COUNT(*), SUM(fare) AS total_fares
FROM taxidata.yellow
WHERE YEAR(pickup) = 2017 AND MONTH(pickup) = 1;
-- → Scan: 100% of the data (e.g. 1 TB)

-- Query WITH partitioning: scan only the relevant partition
SELECT COUNT(*), SUM(fare) AS total_fares
FROM taxidata.yellow_partitioned
WHERE year = 2017 AND month = 1;
-- → Scan: ~1/36 of the data (if 3 years × 12 months)
-- → ~97% reduction in scanned data!

-- Create the taxidata database in AWS Glue Data Catalog
CREATE DATABASE taxidata;

-- Create the external yellow table on S3 data
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');

-- Preview the first 10 rows
SELECT * FROM taxidata.yellow LIMIT 10;

-- Count the total number of trips
SELECT COUNT(*) AS total_trips FROM taxidata.yellow;

-- Check available payment types
SELECT DISTINCT paytype, COUNT(*) AS nb_trips
FROM taxidata.yellow
GROUP BY paytype
ORDER BY nb_trips DESC;

-- Create a table for January 2017 data only
-- Faster for testing and validation
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');

-- Create a view for credit card payments
-- paytype = '1' means credit card payment
CREATE VIEW taxidata.cctrips AS
SELECT
    vendor,
    SUM(fare)  AS total_fares_cc,
    COUNT(*)   AS nb_trips_cc,
    AVG(fare)  AS avg_fare_cc
FROM taxidata.yellow
WHERE paytype = '1'
GROUP BY vendor;

-- Use the view
SELECT * FROM taxidata.cctrips;

-- Create a view for cash payments
-- paytype = '2' means cash payment
CREATE VIEW taxidata.cashtrips AS
SELECT
    vendor,
    SUM(fare)  AS total_fares_cash,
    COUNT(*)   AS nb_trips_cash,
    AVG(fare)  AS avg_fare_cash
FROM taxidata.yellow
WHERE paytype = '2'
GROUP BY vendor;

-- Use the view
SELECT * FROM taxidata.cashtrips;

-- Comparison view combining credit card and cash
-- Using CTE (Common Table Expressions) with 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;

-- Query the view
SELECT * FROM taxidata.comparepay ORDER BY ratio_cc_cash DESC;

-- List available views
SHOW VIEWS IN taxidata;

-- View the definition of a view
SHOW CREATE VIEW taxidata.comparepay;

-- Replace an existing view
CREATE OR REPLACE VIEW taxidata.cctrips AS
SELECT
    vendor,
    SUM(fare) AS total_fares_cc,
    COUNT(*) AS nb_trips_cc
FROM taxidata.yellow
WHERE paytype = '1'
GROUP BY vendor;

-- Drop a view
DROP VIEW taxidata.cashtrips;

AWSTemplateFormatVersion: 2010-09-09
Description: "Athena named query for NYC taxi analysis"

Resources:
  AthenaNamedQuery:
    Type: AWS::Athena::NamedQuery
    Properties:
      Database: "taxidata"
      Description: "Select trips whose amount exceeds $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: "ID of the created Athena named query"
    Value: !Ref AthenaNamedQuery