book

Data Engineering with AWS - Second Edition

by Gareth Eagar

October 2023

Intermediate to advanced

636 pages

17h 2m

English

Packt Publishing

Read now

Unlock full access

Who this book is forWhat this book coversTo get the most out of this bookGet in touch
Technical requirementsThe rise of big data as a corporate assetThe challenges of ever-growing datasetsThe role of the data engineer as a big data enablerUnderstanding the role of the data engineerUnderstanding the role of the data scientistUnderstanding the role of the data analystUnderstanding other common data-related rolesThe benefits of the cloud when building big data analytic solutionsHands-on – creating and accessing your AWS accountCreating a new AWS accountAccessing your AWS accountSummary
Technical requirementsThe evolution of data management for analyticsDatabases and data warehousesDealing with big, unstructured dataCloud-based solutions for big data analyticsA deeper dive into data warehouse concepts and architectureDimensional modeling in data warehousesUnderstanding the role of data martsDistributed storage and massively parallel processingColumnar data storage and efficient data compressionFeeding data into the warehouse – ETL and ELT pipelinesAn overview of data lake architecture and conceptsData lake logical architectureThe storage layer and storage zonesCatalog and search layersIngestion layerThe processing layerThe consumption layerData lake architecture summaryBringing together the best of data warehouses and data lakesThe data lake house approachNew data lake table formatsFederated queries across database enginesHands-on – using the AWS Command Line Interface (CLI) to create Simple Storage Service (S3) bucketsAccessing the AWS CLIUsing AWS CloudShell to access the CLICreating new Amazon S3 bucketsSummary
Technical requirementsAn overview of AWS services for ingesting dataAmazon Database Migration Service (DMS)Amazon Kinesis for streaming data ingestionAmazon Kinesis AgentAmazon Kinesis FirehoseAmazon Kinesis Data StreamsAmazon Kinesis Data AnalyticsAmazon Kinesis Video StreamsAmazon MSK for streaming data ingestionAmazon AppFlow for ingesting data from SaaS servicesAWS Transfer Family for ingestion using FTP/SFTP protocolsAWS DataSync for ingesting from on premises and multicloud storage servicesThe AWS Snow family of devices for large data transfersAWS Glue for data ingestionAn overview of AWS services for transforming dataAWS Lambda for light transformationsAWS Glue for serverless data processingServerless ETL processing AWS Glue DataBrewAWS Glue Data CatalogAWS Glue crawlersAmazon EMR for Hadoop ecosystem processingAn overview of AWS services for orchestrating big data pipelinesAWS Glue workflows for orchestrating Glue componentsAWS Step Functions for complex workflowsAmazon Managed Workflows for Apache Airflow (MWAA)An overview of AWS services for consuming dataAmazon Athena for SQL queries in the data lakeAmazon Redshift and Redshift Spectrum for data warehousing and data lakehouse architecturesOverview of Amazon QuickSight for visualizing dataHands-on – triggering an AWS Lambda function when a new file arrives in an S3 bucketCreating a Lambda layer containing the AWS SDK for pandas libraryCreating an IAM policy and role for your Lambda functionCreating a Lambda functionConfiguring our Lambda function to be triggered by an S3 uploadSummary
Technical requirementsThe many different aspects of data governanceData security, access, and privacyCommon data regulatory requirementsCore data protection conceptsPersonally identifiable information (PII)Personal dataEncryptionAnonymized dataPseudonymized data/tokenizationAuthenticationAuthorizationPutting these concepts togetherData quality, data profiling, and data lineageData qualityData profilingData lineageBusiness and technical data catalogsImplementing a data catalog to avoid creating a data swampBusiness data catalogsTechnical data catalogsAWS services that help with data governanceThe AWS Glue/Lake Formation technical data catalogAWS Glue DataBrew for profiling datasetsAWS Glue Data QualityAWS Key Management Service (KMS) for data encryptionAmazon Macie for detecting PII data in Amazon S3 objectsThe AWS Glue Studio Detect PII transform for detecting PII data in datasetsAmazon GuardDuty for detecting threats in an AWS accountAWS Identity and Access Management (IAM) serviceUsing AWS Lake Formation to manage data lake accessPermissions management before Lake FormationPermissions management using AWS Lake FormationHands-on – configuring Lake Formation permissionsCreating a new user with IAM permissionsTransitioning to managing fine-grained permissions with AWS Lake FormationActivating Lake Formation permissions for a database and tableGranting Lake Formation permissionsSummary
Technical requirementsApproaching the data pipeline architectureArchitecting houses and pipelinesWhiteboarding as an information-gathering toolConducting a whiteboarding sessionIdentifying data consumers and understanding their requirementsIdentifying data sources and ingesting dataIdentifying data transformations and optimizationsFile format optimizationsData standardizationData quality checksData partitioningData denormalizationData catalogingWhiteboarding data transformationLoading data into data martsWrapping up the whiteboarding sessionHands-on – architecting a sample pipelineDetailed notes from the project “Bright Light” whiteboarding meeting of GP Widgets, IncMeeting notesSummary
Technical requirementsUnderstanding data sourcesData varietyStructured dataSemi-structured dataUnstructured dataData volumeData velocityData veracityData valueQuestions to askIngesting data from a relational databaseAWS DMSAWS GlueFull one-off loads from one or more tablesInitial full loads from a table, and subsequent loads of new recordsCreating AWS Glue jobs with AWS Lake FormationOther ways to ingest data from a databaseDeciding on the best approach to ingesting from a databaseThe size of the databaseDatabase loadData ingestion frequencyTechnical requirements and compatibilityIngesting streaming dataAmazon Kinesis versus Amazon Managed Streaming for Kafka (MSK)Serverless services versus managed servicesOpen-source flexibility versus proprietary software with strong AWS integrationAt-least-once messaging versus exactly once messagingA single processing engine versus niche toolsDeciding on a streaming ingestion toolHands-on – ingesting data with AWS DMSDeploying MySQL and an EC2 data loader via CloudFormationCreating an IAM policy and role for DMSConfiguring DMS settings and performing a full load from MySQL to S3Querying data with Amazon AthenaHands-on – ingesting streaming dataConfiguring Kinesis Data Firehose for streaming delivery to Amazon S3Configuring Amazon Kinesis Data Generator (KDG)Adding newly ingested data to the Glue Data CatalogQuerying the data with Amazon AthenaSummary
Technical requirementsOverview of how transformations can create valueCooking, baking, and data transformationsTransformations as part of a pipelineTypes of data transformation toolsApache SparkHadoop and MapReduceSQLGUI-based toolsCommon data preparation transformationsProtecting PII dataOptimizing the file formatOptimizing with data partitioningData cleansingCommon business use case transformationsData denormalizationEnriching dataPre-aggregating dataExtracting metadata from unstructured dataWorking with Change Data Capture (CDC) dataTraditional approaches – data upserts and SQL viewsModern approaches – Open Table Formats (OTFs)Apache IcebergApache HudiDatabricks Delta LakeHands-on – joining datasets with AWS Glue StudioCreating a new data lake zone – the curated zoneCreating a new IAM role for the Glue jobConfiguring a denormalization transform using AWS Glue StudioFinalizing the denormalization transform job to write to S3Create a transform job to join streaming and film data using AWS Glue StudioSummary

Technical requirementsUnderstanding the impact of data democratizationA growing variety of data consumersHow a data mesh helps data consumersMeeting the needs of business users with data visualizationAWS tools for business usersA quick overview of Amazon QuickSightMeeting the needs of data analysts with structured reportingAWS tools for data analystsAmazon AthenaAWS Glue DataBrewRunning Python or R in AWSMeeting the needs of data scientists and ML modelsAWS tools used by data scientists to work with dataSageMaker Ground TruthSageMaker Data WranglerSageMaker ClarifyHands-on – creating data transformations with AWS Glue DataBrewConfiguring new datasets for AWS Glue DataBrewCreating a new Glue DataBrew projectBuilding your Glue DataBrew recipeCreating a Glue DataBrew jobSummary
Technical requirementsExtending analytics with data warehouses/data martsCold and warm dataCold dataWarm dataAmazon S3 storage classesHot dataWhat not to do – anti-patterns for a data warehouseUsing a data warehouse as a transactional datastoreUsing a data warehouse as a data lakeStoring unstructured dataRedshift architecture review and storage deep diveData distribution across slicesRedshift Zone Maps and sorting dataDesigning a high-performance data warehouseProvisioned versus Redshift Serverless clustersSelecting the optimal Redshift node type for provisioned clustersSelecting the optimal table distribution style and sort keySelecting the right data type for columnsCharacter typesNumeric typesDatetime typesBoolean typeHLLSKETCH typeSUPER typeSelecting the optimal table typeLocal Redshift tablesExternal tables for querying data in Amazon S3 with Redshift SpectrumTemporary staging tables for loading data into RedshiftData caching using Redshift materialized viewsMoving data between a data lake and RedshiftOptimizing data ingestion in RedshiftAutomating data loads from Amazon S3 into RedshiftExporting data from Redshift to the data lakeExploring advanced Redshift featuresData sharing between Redshift clustersMachine learning capabilities in Amazon RedshiftRunning Redshift clusters across multiple Availability ZonesRedshift Dynamic Data MaskingZero-ETL between Amazon Aurora and Amazon RedshiftResizing a Redshift clusterHands-on – deploying a Redshift Serverless cluster and running Redshift Spectrum queriesUploading our sample data to Amazon S3 IAM roles for RedshiftCreating a Redshift clusterQuerying data in the sample databaseUsing Redshift Spectrum to directly query data in the data lakeSummary
Technical requirementsUnderstanding the core concepts for pipeline orchestrationWhat is a data pipeline, and how do you orchestrate it?What is a directed acyclic graph?How do you trigger a data pipeline to run?Using manifest files as pipeline triggersHow do you handle the failures of a step in your pipeline?Common reasons for failure in data pipelinesPipeline failure retry strategiesExamining the options for orchestrating pipelines in AWSAWS Data Pipeline (now in maintenance mode)AWS Glue workflows to orchestrate Glue resourcesMonitoring and error handlingTriggering Glue workflowsApache Airflow as an open-source orchestration solutionCore concepts for creating Apache Airflow pipelinesAWS Step Functions for a serverless orchestration solutionA sample Step Functions state machineDeciding on which data pipeline orchestration tool to useHands-on – orchestrating a data pipeline using AWS Step FunctionsCreating new Lambda functionsUsing a Lambda function to determine the file extensionUsing Lambda to randomly generate failuresCreating an SNS topic and subscribing to an email addressCreating a new Step Functions state machineConfiguring our S3 bucket to send events to EventBridgeCreating an EventBridge rule for triggering our Step Functions state machineTesting our event-driven data orchestration pipelineSummary
Technical requirementsAn introduction to Amazon AthenaTips and tricks to optimize Amazon Athena queriesCommon file format and layout optimizationsTransforming raw source files to optimized file formatsPartitioning the datasetOther file-based optimizationsWriting optimized SQL queriesSelecting only the specific columns that you needUsing approximate aggregate functionsReusing Athena query resultsExploring advanced Athena functionalityQuerying external data sources using Athena Federated QueryPre-built connectors and custom connectorsUsing Apache Spark in Amazon AthenaWorking with open table formats in Amazon AthenaProvisioning capacity for queriesManaging groups of users with Amazon Athena workgroupsManaging Athena costs with Athena workgroupsPer query data usage controlAthena workgroup data usage controlsImplementing governance controls with Athena workgroupsHands-on – creating an Amazon Athena workgroup and configuring Athena settingsHands-on – switching workgroups and running queriesSummary
Technical requirementsRepresenting data visually for maximum impactBenefits of data visualizationPopular uses of data visualizationsTrends over timeData over a geographic areaHeat maps to represent the intersection of dataUnderstanding Amazon QuickSight’s core conceptsStandard versus Enterprise editionSPICE – the in-memory storage and computation engine for QuickSightManaging SPICE capacityIngesting and preparing data from a variety of sourcesPreparing datasets in QuickSight versus performing ETL outside of QuickSightCreating and sharing visuals with QuickSight analyses and dashboardsVisual types in Amazon QuickSightAutoGraph for automatic graphingLine, geospatial, and heat mapsBar chartsKey performance indicatorsTables as visualsCustom visual typesOther visual typesUnderstanding QuickSight’s advanced featuresAmazon QuickSight ML InsightsAmazon QuickSight autonarrativesML-powered anomaly detectionML-powered forecastingAmazon QuickSight Q for natural language queriesGenerative BI dashboarding authoring capabilitiesQuickSight Q TopicsFine-tuning your QuickSight Q TopicsAmazon QuickSight embedded dashboardsEmbedding for registered QuickSight usersEmbedding for unauthenticated usersGenerating multi-page formatted reportsHands-on – creating a simple QuickSight visualizationSetting up a new QuickSight account and loading a datasetCreating a new analysisPublishing our visual as a dashboardSummary
Technical requirementsUnderstanding the value of AI and ML for organizationsSpecialized AI projectsMedical clinical decision support platformEarly detection of diseasesMaking sports saferEveryday use cases for AI and MLForecastingPersonalizationNatural language processingImage recognitionExploring AWS services for MLAWS ML servicesSageMaker in the ML preparation phaseSageMaker in the ML build phaseSageMaker in the ML training and tuning phaseSageMaker in the ML deployment and management phaseExploring AWS services for AIAI for unstructured speech and textAmazon Transcribe for converting speech into textAmazon Textract for extracting text from documentsAmazon Comprehend for extracting insights from textAI for extracting metadata from images and videoAmazon RekognitionAI for ML-powered forecastsAmazon ForecastAI for fraud detection and personalizationAmazon Fraud DetectorAmazon PersonalizeBuilding generative AI solutions on AWSUnderstanding the foundations of generative AI technologyBuilding on foundational models using Amazon SageMaker JumpStartBuilding on foundational models using Amazon BedrockCommon use cases for LLMsHands-on – reviewing reviews with Amazon ComprehendSetting up a new Amazon SQS message queueCreating a Lambda function for calling Amazon ComprehendAdding Comprehend permissions for our IAM roleAdding a Lambda function as a trigger for our SQS message queueTesting the solution with Amazon ComprehendSummary
Technical requirementsWhat does it mean for a data lake to be transactional?Limitations of Hive-based data lakesHigh-level benefits of open table formatsACID transactionsRecord level updatesSchema evolutionTime travelOverview of how open table formats workApproaches used by table formats for updating tablesCOW approach to table updatesMOR approach to table updatesChoosing between COW and MORAn overview of Delta Lake, Apache Hudi, and Apache IcebergDeep dive into Delta LakeAdvanced features available in Delta LakeDeep dive into Apache HudiHudi Primary KeysFile groupsCompactionRecord level indexDeep dive into Apache IcebergIceberg Metadata fileThe manifest list fileThe manifest filePutting it togetherMaintenance tasks for Iceberg tablesAWS service integrations for building transactional data lakesOpen table format support in AWS GlueAWS Glue crawler supportAWS Glue ETL engine supportOpen table support in AWS Lake FormationOpen table support in Amazon EMROpen table support in Amazon Redshift Open table support in Amazon AthenaHands-on – Working with Apache Iceberg tables in AWSCreating an Apache Iceberg table using Amazon AthenaAdding data to our Iceberg table and running queriesModifying data in our Iceberg table and running queriesIceberg table maintenance tasksOptimizing the table layoutReducing disk space by deleting snapshotsSummary
Technical requirementsWhat is a data mesh?Domain-oriented, decentralized data ownershipData as a productSelf-service data infrastructure as a platformFederated computational governanceData producers and consumersChallenges that a data mesh approach attempts to resolveBottlenecks with a centralized data teamThe “Analytics is not my problem” problemNo organization-wide visibility into datasets that are availableThe organizational and technical challenges of building a data meshChanging the way that an organization approaches analytical dataChanges for the centralized data & analytics teamChanges for line of business teamsTechnical challenges for building a data meshIntegrating existing analytical toolsCentralizing dataset metadata in a single catalog and building automationCompromising on integrationsAWS services that help enable a data mesh approachQuerying data across AWS accountsSharing data with AWS Lake FormationAmazon DataZone, a business data catalog with data mesh functionalityDataZone conceptsDataZone componentsA sample architecture for a data mesh on AWSArchitecture for a data mesh using AWS-native servicesArchitecture for a data mesh using non-AWS analytic servicesAutomating the sharing of data in SnowflakeUsing query federation instead of data sharingHands-on – Setting up Amazon DataZoneSetting up AWS Identity CenterEnabling and configuring Amazon DataZoneAdding a data source to our DataZone projectAdding business metadataCreating a project for data analysisSearch the data catalog and subscribe to dataApproving the subscription requestSummary
Technical requirementsGoals of a modern data platformA flexible and agile platformA scalable platformA well-governed platformA secure platformAn easy-to-use, self-serve platformDeciding whether to build or buy a data platformChoosing to buy a data platformWhen to buy a data platformChoosing to build a data platformWhen to build a data platformA third way – implementing an open-source data platformThe Serverless Data Lake Framework (SDLF)Core SDLF conceptsDataOps as an approach to building data platformsAutomation and observability as a key for DataOpsAutomating infrastructure and code deploymentAutomating observabilityAWS services for implementing a DataOps approachAWS services for infrastructure deploymentAWS code management and deployment servicesHands-on – automated deployment of data platform components and data transformation codeSetting up a Cloud9 IDE environmentSetting up our AWS CodeCommit repositoryAdding a Glue ETL script and CloudFormation template into our repositoryAutomating deployment of our Glue code Automating the deployment of our Glue job Testing our CodePipelineSummary
Technical requirementsUnderstanding the complexities of real-world data environmentsExamining examples of real-world data pipelinesA decade of data wrapped up for Spotify usersIngesting and processing streaming files at Netflix scaleEnriching VPC Flow Logs with application informationWorking around Amazon SQS quota limitsImagining the future – a look at emerging trendsIncreased adoption of a data mesh approachRequirement to work in a multi-cloud environmentMigration to open table formatsManaging costs with FinOpsThe merging of data warehouses and data lakesThe application of generative AI to business intelligence and analyticsThe application of generative AI to building transformationsHands-on – cleaning up your AWS accountReviewing AWS Billing to identify the resources being charged forClosing your AWS accountSummary

Content preview from Data Engineering with AWS - Second Edition

11 Ad Hoc Queries with Amazon Athena

In Chapter 8, Identifying and Enabling Varied Data Consumers, we explored a variety of data consumers. Now in this chapter, we will start examining the AWS services that some of these different data consumers may want to use, starting with those that need to use SQL to run ad hoc queries on data in the data lake.

SQL syntax is widely used for querying data in a variety of databases, and there is a large number of people that know SQL, making it a skill that is fairly easy to find. As a result, there is significant demand from various data consumers for the ability to query data that is in the data lake using SQL, without having to first move the data into a dedicated traditional database.

Amazon Athena is ...