Hands-On End-to-End Big Data Projects

Explore an ETL architecture using S3 and PostgreSQL via RDS as sources; transform customer and order data with DB2, and visualize insights via Informatica cloud service.

Explore two datasets, customers and orders, with customer.csv in an S3 bucket and order.csv in a database, connected by customer_id and containing fields like zip code, city, and state code.

Create an S3 bucket named project three in the AWS management console, then upload the customer.csv file to store data in S3.

Set up PostgreSQL on AWS using RDS by creating a standard PostgreSQL database, configuring a single instance with public access, and creating an initial database such as orders DB.

Register an AWS RDS PostgreSQL server in pgAdmin, configure the endpoint and port 5432, and set the maintenance database. Review databases and the public schema, noting no tables yet.

Create a PostgreSQL schema named app and a table app.order with columns for order_id, customer_id, order_status, and multiple timestamp fields, then import order data from a csv via pgadmin.

Sign up for a 30-day free trial of Informatica Cloud. Verify your email and set up cloud-based data integration with S3 and Postgres.

Learn to set up an Amazon S3 connection in Informatica Cloud by adding S3 connector, configuring runtime environment, and entering access and secret keys for the Mumbai project three bucket.

Create and save an S3 customer data connection in Informatica Cloud, then modify its details to maintain a successful S3 integration.

add a postgres connector in informatica cloud, create a new postgres order connection with host, port 5432, data db, and neuron_user, test the connection and save.

Set up the data warehouse destination by creating the vendor_data schema and vendor_data.customer table in PostgreSQL, preparing for ETL from S3 to this table using Informatica Cloud.

Learn to extract data from S3 to a PostgreSQL data warehouse using a data integration tool, build a data pipeline with mappings and transformations, and monitor the job end-to-end.

Create a new schema named operation and a table operation.order with columns order_id, customer_id, order_status, order_purchase_timestamp, and order_estimated_delivery_date. Load data from app.order to operation.order to enable the data flow.

Extract and load data from app.order to operation.order using an ICS mapping, align and map fields, run and monitor the jobs, and explore optional transformation with DB2.

Create a dbt Cloud account by signing up, verifying your email, and initializing a warehouse with Postgres. This video walks through the signup, verification, and initial configuration.

Set up a dbt cloud project, configure a Postgres warehouse, enter host, port, database, and schema, provide development credentials, test the connection, and initialize the project with dbt files.

Create dbt models from staging data using select statements to transform raw data into final tables. Preview and compile code before creating the model.

Learn to structure a dbt workflow by creating a transformation branch, building staging models for customers and orders, validating with schema, and creating a final customer_order model from PostgreSQL data.

Create a customer order model using ref to link staging customer and staging order, derive the order status, and generate a schema.html to validate the order id is not null.

Dbt part 5 guides building five models, running 11 tests, validating accepted values for stage_orders, delivered, status, and shipped, then fixing errors and re-running the run.

Debug and validate dbt models for staging customer and staging order, compare DB2 and Postgres data, resolve a failing test, and fix the customer order transformation.

Run the dbt project to build models like staging.customer, stage_order, and customer_order, then verify the final customer_order table with joins and delivery indicators.

Learn how dbt handles transformation within a PostgreSQL data warehouse by building staging and final models, validating with tests, and previewing results before connecting to Tableau.

Connect Tableau to a PostgreSQL warehouse using the given endpoint, port 5432, database data db, and credentials; explore staging tables, drag fields into canvas, and prepare dashboards with BI tools.

learn to build charts in Tableau by linking staging customer and staging orders on customer id, create status and sales fields, and use maps, line charts, area charts with PostgreSQL.

Explore the data architecture for a big data pipeline, detailing NiFi for extracting and pre-processing, publishing to Kafka, with raw data in HDFS and processed data in Hadoop.

Launch an EC2 instance in AWS using Amazon Linux 2 AMI and a t2 large; create a key pair, configure storage and network, and set SSH inbound rules.

Connect to an EC2 instance via ssh using the pem key and log in as ec2-user. The video shows using the ssh client and logs in to the instance.

Set up a docker environment on an EC2 machine by installing docker and docker compose, configuring access, and starting and stopping the docker service.

Copy the docker_exp folder from local to an EC2 instance, set permissions, and initialize airflow with docker compose to prepare dags, logs, and plugins for a big data workflow.

start a local docker environment with docker compose up, then forward ports to access jupyter lab, airflow, spark master, and niFi on your machine.

Run and monitor a multi-service environment with docker compose up, access nifi, spark master, jupyter lab via port forwarding, and enter container bash shells with docker exec -it.

Upload and import the NiFi template, then explore the components and flow files to trace data movement from API retrieval through encryption, pre-processing, and outputs to Kafka and HDFS.

Explore data extraction with NiFi by configuring a flow from generate flow file, via http client processor, to fetch json data, evaluate json path, and split json into flow files.

Learn data encryption in a NiFi workflow by selecting attributes to encrypt, hashing with sha-256, and constructing an encryption template, then converting json to csv.

Push data into hdfs with the put hdfs processor and Hadoop configuration, automatically creating directories, while data also moves to kafka in parallel.

Explore retrieving data from HDFS and publishing to Kafka, verifying the topic designated as desired_data_csv, and using a Kafka console consumer to view messages.

Validate data flow from a csv topic to Hdfs and publish to Kafka, by reconnecting, accessing the Kafka batch, and using a bootstrap server.

Process streaming data from Kafka to PySpark, define a schema, convert CSV to JSON, and publish pre-processed data to Kafka_out for downstream NiFi and Hadoop workflows.

Demonstrate a PySpark streaming workflow by deploying test.py to the Spark master, granting permissions, and pushing processed data into a Kafka topic named 'desired out', then verify the topic output.

Demonstrates streaming data from Kafka through NiFi for pre-processing, converts CSV to JSON, writes to HDFS, and sets up for a Hive external table.

Move data from HDFS to Hive by creating external tables in Hive, linking corona data and processed data stored in HDFS via PySpark preprocessing and docker-managed Hive server.

Connect to Hive, create external corona and processed data tables, point to the corona path and Kafka output, then verify data and outline the orchestration flow for the next video.

Verify execution by inspecting logs, query flow_hive to see records 1 and 2 inserted, and demonstrate creating flow_hive tables and scheduling scripts to run daily.

Connect Tableau to Hive data using Hive connectors, enter server name, port, username, and authentication, then drag databases and tables to build visuals.

Connect to Hive, drag and drop columns, and build charts or tables to visualize totals by country; data analysts present visuals to managers using Tableau, Power BI, Metabase, or QuickSight.

Explore a simple EC2 based project architecture using docker compose with MySQL as the data source, and move data to Hive via Scoop for landing and consumption layers.

Create a new EC2 instance in the AWS management console, using Amazon Linux and a t2.xlarge image, generate a key pair, and enable SSH via inbound custom ports.

Learn how to ssh into an EC2 instance using a direct ssh command, including specifying the public DNS and the key file path, with optional PuTTY steps.

Move the docker exp folder and docker-compose.yml to your EC2 instance using scp, then install docker, configure permissions, and run docker compose up from the correct path.

Update your environment by preparing the Docker setup, adding necessary files, and running Docker compose up to download dependencies; the process may take several minutes.

Set up and verify Docker containers for HDFS, Hive, and MySQL, access Jupyter Lab via port forwarding, and practice entering containers with Docker exec.

Learn essential hdfs commands inside a Hadoop container, including listing, viewing, and creating directories, changing permissions, and copying data to and from local.

This lecture shows how to import and export data with Sqoop, including a docker-based Hadoop and MySQL setup, importing the employee table with a single mapper from the demo database.

Learn to import data with sqoop using basic import, target directories, and incremental loads using the last value to capture only new records, and see results in hdfs and mysql.

Master Sqoop export commands to move data from HDFS to SQL, using username and password to export to the MySQL target table.

Load Walmart store sales data from a csv into a MySQL container, then create a Hive table and analyze the dataset through a Docker-based Hadoop workflow.

Navigate docker containers and mysql to load the Walmart sales dataset, create a retail database and Walmart_sales table, and import data with proper local files settings.

Load data into a MySQL table, fix a file, and verify about 600435 records with store IDs 1–4; then prepare to import using scoop in the next video.

Learn scoop import from MySQL to hive, with incremental loads and last value tracking, and how to create scoop jobs executed via beeline for hive tables.

Create and validate Hive tables from raw sales with Sqoop, cast data types during load from MySQL to Hive, and analyze store sales, holidays, and growth.

Identify the stores with the highest and lowest sales by summing weekly sales per store, rounding to two decimals, and ranking with dense_rank over total in descending order.

Analyze which store shows the maximum standard deviation by querying weekly sales, grouping by store, and applying the standard deviation function to measure data spread.

Learn how to compute the coefficient of mean to standard deviation using sales data, analyze the data with a simple query, and validate the results.

Identify which Walmart store shows strong quarterly growth in 2012 by calculating quarterly sales from Q2 and Q3, filtering by date, and performing a union of queries to compare stores.

Identify holidays with sales above the non-holiday mean using sql in a hive workflow, while avoiding cartesian products and mastering beeline queries across rdbms to Hive, with incremental loads.

Explore the project architecture tying AWS services (EC2, Kinesis Firehose, S3) with Apache Airflow and Snowflake to move, transform, and load streaming data from landing to processed stages.

Create an iam role and attach cloudwatch full access, cloudwatch logs full access, cloudwatch events full access, and amazon kinesis firehose full access to enable an upcoming ec2 deployment.

Launch an ec2 instance in the cloud console with Amazon Linux free tier, attach an iam role and key pair, then ssh in via PuTTY.

Create two Kinesis Firehose delivery streams to load customers and orders data into the Snowflake Data Pipeline S3 bucket, using direct put as the source and landing and error prefixes.

Install the kinesis agent on an EC2 instance and configure it to forward orders.csv and customers.csv data to Firehose via delivery streams.

Start the Kinesis agent on an EC2 instance, modify the buffer interval to 60 seconds for both the customers and orders streams, and verify delivery to S3 via Firehose.

Create a snowflake account, start a 30-day free trial, verify via email, activate the account with your AWS region, and explore worksheets, dashboards, databases, warehouses, and queries.

Explore Snowflake setup by creating warehouses, databases, and schemas; use worksheets to write queries, create tables with columns, preview data, and manage users, roles, and permissions.

Create an AWS IAM role to connect Snowflake with S3, set the external ID, and attach S3 full access, then review the trust relationship before configuring a stage.

Set up a Snowflake external storage integration with S3, enable the integration, configure the external ID and ARN, and create stages and a CSV file format for copying data.

Set up Snowflake stages and file formats, create an S3 integration, and load data from S3 into Snowflake tables using copy into, then validate results with selects and batch IDs.

Learn to define an Airflow dag with bash and Snowflake operators, configure default arguments, and move data from landing to processing to processed in s3 and Snowflake.

Learn how an Airflow dag coordinates data from landing to processing to processed using the snowflake operator and copy into queries for orders and customers.

Create and configure an Airflow cluster, selecting a VPC and subnets, setting a public security group, defining CPU and worker limits, enabling logs, and preparing IAM roles for environment setup.

Update the Airflow role by attaching S3 policies and configuring a Snowflake connection to run the dag and move data from landing to processing to processed.

Fix table naming and account configuration in Snowflake, run the dag, and validate end-to-end data flow from S3 landing to processing, with logs and verifications.

Test the end-to-end data flow from Kinesis firehose to S3 and into Snowflake with Airflow triggering DAGs, then clean up by truncating customers and orders and validating batch IDs.

Fix a path error in a dag for the big data projects course that prevents data from moving from landing to processing, ensuring the pipeline runs and data reaches Snowflake.

Create a snowflake data transformation by joining customers and orders, computing the total price per customer and date, and loading results into a new table via a dag workflow.

Update the dag by adding a new snowflake query for customer orders, perform a transformation, and insert into the orders table, then verify the updated workflow in Apache Airflow.

Learn how to run an airflow dag with data transformation, fix SQL errors, and verify results through logs, table creation, and a data build tool workflow.