Log In to continue learning
Introduction to Data Engineering
17h36m
Topics
Data Science
In this video, I'll give you a preview of what you'll be doing in the lab. As a reminder, in the previous video, we went through the lab environment that looked like this, where you have all the files needed for the lab in this folder structure here on the left, and you've opened the Markdown file, C1W2Assignment, that contains the detailed instructions for the rest of the lab exercise. In these instructions, you'll find some introduction here at the top, and then your work begins in Section 2, exploring the source system. As I mentioned before, the source system database is already instantiated for you. You can view the details of this database in the AWS console. To do that, you can go to the console and type RDS, and then select that service. On the left here, I'll click on Databases. Here's the instance of the source database. If I click on the identifier, I get all the details of this database, such as the endpoint or the URL that allows you to access the database, the port number, and other networking info, such as the VPC and subnet where the RDS instance is set up. To explore the database and the table stored within this instance, you'll need this endpoint. You can also get this endpoint through the terminal by running this command. One thing to note in the VS Code terminal is that the usual shortcuts for paste, like Control or Command-V, will not work here. Please refer to this link instead to see what will work in your OS. In my case, it's Command-Shift-V. Now, there's the same endpoint you saw in the console. You need to establish a connection to this database before you can see its contents. Since the database is a MySQL database, you can connect to it by typing MySQL, then specifying the host or the endpoint of the database, the username, which is admin in this case, the password, and finally the port, which is 3306. Now that you have the connection established, you can choose the database you want to explore with the command Use, and then specify the name of the database, which is Classic Models. You can view the tables inside this database by typing Show Tables. If you're curious, you can also view the script that was used to populate this database by going up here to the Data folder and opening this file called MySQLSampleDatabase.sql. Here, you can see some information about where the data came from and a bit of version history. If you scroll down, you'll find a bunch of SQL code. First, there's a line to create the database itself. Then there's a whole series of create table commands that are populating each of the tables in the database. For now, you won't need to worry about all this SQL code. In the next course, you'll get a chance to read, write, and interpret SQL commands. Finally, to exit the database connection and continue with the lab instructions, you'll type Exit. Next, let's create the resources for the data pipeline, namely the AWS Glue instance and the Glue crawler. For that, you're provided with Terraform files that contain code for creating and configuring those resources. If you click on the Terraform folder here on the left, all these files here that end with the .tf extension are Terraform files. So, for instance, the Glue.tf file contains the configurations for the AWS Glue instance. The other Terraform files contain networking and permission settings that you'll learn more about in Course 2. For now, I'll quickly skim through the Glue.tf file to show you the resources declared in this file. So, here's the AWS Glue data catalog, and we're going to define a connection between AWS Glue and the RDS source database. Here's another resource that defines a Glue crawler, which will be used to crawl the S3 bucket. And finally, here's the Glue job, where we specify the connection to the source system, the location of the script that contains the transformation code, and the target location for the transformed data. You can find the script under the Asset folder, which consists of the code that extracts the data, transforms it, and finally loads it into S3. We'll get further into the details of AWS Glue in Course 4. If you're curious, you can also have a look at the transformations that will be applied to the data by opening up this Python script called GlueJob.py in the Terraform slash Assets folder. But you don't need to worry about the details here. What you're doing with the script is taking the data in a so-called normalized form and modeling it as a star schema of fact and dimension tables for easier analytics. And this is a very common practice in data engineering of tabular data for analytics and use cases. To actually create the resources for this pipeline, you'll need to run the Terraform files. In the terminal here, I'll first run this command to define the environment variables that will be used by the Terraform files. After that, we'll navigate to the Terraform directory and start with the command terraform init. Then you can create the Terraform plan command, which shows you the list of resources that Terraform is planning to create. Finally, you can run the Terraform apply command to create the resources. At this point, you'll see the plan again and have to confirm that you want Terraform to perform these actions. I'll type yes and wait for the resources to be created. Once the resources are created, you can see them through the AWS Management Console. So let's go to the console and type AWS Glue in the search bar. Click on the service, and on the left, click on ETL jobs. Here, you'll see the instance that was just created. You can also search for the S3 bucket. Here's the bucket that will contain the transformed data. Now that the resources have been created, let's run the Glue job. I'll copy this command from the lab instructions and paste it here in the terminal to start the Glue job. To monitor the status of the job, let's go to the console and again search for AWS Glue. You'll navigate to the ETL jobs section and then click on the job name. In the runs tab, you'll see the status of the job, which is now running. You might need to wait two or three minutes for the job to be done. You can refresh your browser to see the latest status. Once the Glue job is done and the status shows succeeded, you should have the transformed data in the S3 bucket. Let's verify that by accessing the contents of this S3 bucket. Here you can find the folders that contain the tables of the transformed data. In the last part of this lab, you'll explore how the datasets can be queried by the data analyst. I'll click on the Jupyter notebook, which contains cells that perform analytical queries on the transformed data. In this first cell, the package AWS Wrangler imported here allows you to use Amazon Athena to extract data from the S3 bucket. Here's the first query statement that extracts all products from the dim products table. The next cell contains a query that finds the total sales per country. The following cell contains a query that further groups the total number of sales by country, product lines, order date, product line, and product name. If the code in these SQL query statements don't look familiar to you, don't worry. You'll get hands-on experience with writing and interpreting queries in courses two and three. And finally, you can run this interactive dashboard to further explore the data. Once you're done with the steps of this lab, be sure to click on submit here. Keep in mind that the lab environment will expire after two hours, so be sure to submit before your time runs out. If you got a passing score, you should return to the Deep Learning AI learning platform and click on sync results to record your grade. Now that you have a better idea of what you'll be doing in this week's lab, it's your turn to try it out. Please follow the lab instructions carefully, and you can always revisit these videos if you get stuck at any point. After you've completed this lab, I'll see you back here for a quick summary of the week.