Log In to continue learning
Data Engineering
Intermediate
Topics
Data Engineering
Data Processing
Deep Learning
Search and Retrieval
In the last lab of this week, you'll be provided with an RDS database that you need to connect to from an EC2 environment, and then create a table inside the database and populate the table with data. Connecting to a database and moving data into it and reading data from it are all super common tasks you'll encounter as a data engineer. And like I said before, having to troubleshoot connection and permission issues in these basic tasks is something you'll run into all the time. So in this lab, when you first attempt to connect to the database, you'll encounter several issues that you'll need to fix. After that, you'll need to download a CSV file from an S3 bucket and then copy the data from the CSV file to the database. To read the file from the S3 bucket, you'll encounter permission issues that you'll need to fix. As I said earlier, I like to set up a scenario like this as part of an interview for a new data engineer to see if they can successfully navigate these common issues. In the upcoming lab, I want you to be 100% successful. So in some sense, you could say that I'm going to spoil all the fun for you by giving you the answers, but I don't want you to be disappointed. So I'll tell you how this is going to go, and you can sort of choose your own adventure, so to speak, and decide just how much troubleshooting you want to engage in versus just following the solution I describe. Here's how it's going to work. If you want the maximum adventure, you can skip this video, jump straight into the lab, and go for it. The lab instructions contain hints about where to look and what to look for to solve the challenges you'll run into. And you can also choose to peek at more explicit instructions on solving the issues if you like. In this video, I'll walk you through how to solve each of the connection and permission issues, but first I'll give you a hint as to where you might look and what to look for to solve the issue. If you like, you can start the lab and follow along with me as you go through this video. And when an issue occurs, I'll be inviting you to pause the video to think about the issue and try to fix it yourself. After that, I'll show you how to fix it. All right, let's get started. Once you start the lab, you can directly check out the database instance that's provided to you. So in the console, I'll search for RDS, click on that resource, then on databases. Here's a database identifier that I'll click on to find the database endpoint. Make sure to save this endpoint because you'll need to use it multiple times to connect to the database while you're troubleshooting the connection issues. You'll connect to the database from an environment running on an EC2 instance that's provided to you. So in the console, I'll look for EC2 and then click on instances. Here, you'll find two instances. Each has different networking settings, which you'll explore in the lab. So first, you'll use the EC2 instance labeled as external bastion host. I'll click on its instance ID and then on connect in the upper panel. Here, you'll get several options that you can use to connect to the EC2 environment. In the lab, you'll go with this first option, EC2 instance connect, which will open a web-based terminal where you'll input the commands to connect to the database. So here, I'll keep these default entries and I'll click on connect. So now I'm in the EC2 terminal. Let's connect to the database. Since the database is a PostgreSQL database, I'll first need to install PSQL, which is the standard command line interface for interacting with a PostgreSQL database. To do that, I'll run this command and wait for the installation to complete. I've given the database username and the password as shown here. So once the installation is complete, I'll use this command to connect to the database. I'll then run this command and type the password. You'll notice that nothing happens. If you press enter, you'll only get empty lines. Getting something like this is tricky. Without an explicit error message, it's hard to know what's actually going wrong. But one thing you can say for sure is that the connection to the database has failed. To exit this attempt, you can type Control-C or Command-C. So can you think of why this connection has failed? Given what you know about networking, can you think of any problems that might be coming from the network setup for the EC2 instance and the database instance? Feel free to pause the video to think about these questions and try to fix this issue. And here's a hint. Go to the console pages for EC2 and RDS and have a look in the network settings at the VPC identifiers for these resources. Any potential issue you see there? How might you resolve it? All right, so here's the solution. The EC2 instance external bastion host is launched inside a VPC, which might be different from the VPC of where the database resides. Resources from different VPCs can't connect if the two VPCs are not configured to talk to each other. So let's verify in which VPC the database is launched. In the console, I'll search for RDS, click on databases. You can scroll over here to see the VPC ID of the RDS instance. Let's now verify the VPC of the EC2 instance. So I'll search for EC2 and then click on instances, then on the instance ID of the external bastion host. Here's the ID of the VPC of the EC2 instance. You can see that the two VPCs are different. That's why the connection has failed. To solve this issue, you can create a connection between the two VPCs, or you need to ensure that your EC2 environment is in the same VPC where the database resides. In the lab, you'll go with the second option. For that, you're provided with another EC2 instance called bastion host that is already created and launched in the same VPC of the database. So I'll close this terminal, and in the console, I'll go to EC2, and then click on the ID of the EC2 bastion host, and then on connect. I'll then launch the EC2 terminal by clicking on connect here. In the terminal of this EC2 instance, I'll repeat the same steps to connect to the database. I'll first install PSQL and then connect to the database. Hmm, the connection is still not working. If the two resources are now in the same VPC, why are you still unable to connect to the database? In a previous video, Morgan mentioned that an RDS instance can be configured to receive certain traffic. So in this case, is the RDS instance configured to receive traffic from the EC2 instance? How can you check, and if there's an issue, how can you fix this configuration? Feel free to pause the video to answer the question and try to explore or fix the issue. Okay, so to verify if the EC2 instance can send traffic to the database instance, you can check the security group associated with the database instance. I'll click on the database identifier in the databases window. Under security, I'll click on VPC security groups, then on the security group ID. Here you can find one inbound rule that allows RDS to accept all traffic, but from a resource that is associated with the same security group. This means that any instance associated with the same security group can communicate freely with the database. Let's verify what security group is associated with the EC2 instance. In another tab, I'll search for EC2 and click on instances. Here's the EC2 instance, Bastion Host. I'll click on the instance ID, scroll down, and then click on the security tab to find the security group ID. You can see here that this is different from the database security group, which means that the EC2 instance can't connect to the database. One way to solve this issue is to add another inbound rule inside the database security group that allows the database to receive connections from the EC2 instance. So to do that, over in the RDS instance security settings, I'll click on the edit inbound rules and then add rule. I'll type 5432 or 5432 for the port number. That's because this is a PostgreSQL database instance and the default port number is 5432. If you're dealing with a different kind of database, like MySQL for example, the port number would be 3306 or 3306. And so depending on the database management system or other resource you're trying to connect to, you might need to look up the port number in the documentation. Now for source, one way I could guarantee that the EC2 can connect to the database is to choose an address here of all zeros, which means that any public traffic is allowed to connect to the database. But don't do that unless you're just troubleshooting a connection issue or there's no sensitive data in the database. In general, opening up your database to all public traffic is something that you should avoid. What you should do instead is to allow the required resources to connect to the database. In this example, you only want the EC2 instance to have access to the database. So to do that, I'll copy the ID of the security group associated with the EC2 instance from the other tab here. Then back in this tab where I'm editing the inbound rules for the RDS instance, I'll paste the security group of the EC2 instance in the source here and finally save the rules. Now I'll go back to the terminal and try to connect again to the database. But oh no, it failed again. So we're still not connected, but now there's an error message saying that password authentication failed. So this is progress. This means that the two resources are talking to each other, but that the password is incorrect. This is also a super common issue, which could happen when passwords are rotated or you miss the notification about a password update. Or you just mistyped the password. This happens all the time. In this case, there's no particular strategy to apply. You just need to get the right password. Let's assume you checked your typing and you didn't simply mistype the password. Then you talked with your team and got the new password as shown here. With the new password, I'll now try to connect again to the database. And here you go. The connection to the database is now established. I'll now exit the connection. Now that you can establish a successful connection to the database, the next step is to create a table inside the database and then populate it with data. First, I'll run this command to download the required files for this part. One of these downloaded files is the file ratings underscore table underscore DDL dot SQL. Let's open this file by running this command. You can see that this file contains a SQL command that creates an empty table that has this schema. Let's run this command to create the table. So first, I'll close this file by pressing Control-X or Command-X to get back to the terminal. Here in the terminal, I'll connect to the database, enter the password, and then run this I command, which reads the SQL statements from the specified file. The table is now created, but it's empty. So if you try to run this select statement, nothing will be returned. I'll now exit the connection. The data that you'll need to copy to the table is provided to you as a CSV file in an S3 bucket. Let's check out the provided S3 bucket by running this command. Here's the name of the bucket. You can also search for the bucket in the console. I'll type S3 in the search bar and then click on that resource. Here's the provided bucket. To download the CSV file from the S3 bucket, you're provided with the script download from S3. To open this file, I'll run this command. Here you can find the instructions that downloads the CSV file from the S3 bucket to a local folder labeled as data. For the bucket underscore name variable here, I'll type the name of the provided bucket. I'll then press Control-O or Command-O, followed by Enter to save the file. I'll then press Control-X or Command-X to get back to the terminal. Here in the terminal, I'll first create the folder data. Then I'll install Boto3 and then run the Python script as shown here. Now I've got an error, which means that I do not have permission to download data from the S3 bucket. So it looks like the S3 bucket is not configured to allow the EC2 instance to read data from the bucket. The hint here is to go to the console and check the permissions in the policy associated with the S3 bucket. And, wink, wink, there's a new policy that's been provided to you in the lab that might be useful here. Again, feel free to pause the video to explore this issue and see if you can fix it. So let's see if we can figure out what's going on here. In the console, I'll click on the bucket name. I'll then select the tab Permissions and scroll down to Bucket Policy. And here I can see that the current policy is configured to deny any resources from reading objects in the S3 bucket. To modify this, I'll click on Edit and then replace this policy with the policy provided in the lab instructions. This policy allows the EC2 instance to read objects from S3. I'll replace your bucket name with the bucket name. Then I'll need to get the IPv4 address of the EC2 instance and paste it here. So in another tab, I'll search for EC2, click on Instances, then on the instance ID of the EC2 bastion host. Here's its IPv4 address. I'll copy and paste it in the policy and then click on Save Changes. Now in the terminal, I'll run the Python script again. And so the script ran successfully with no permission issues. That's awesome. So the data folder should now contain a copy of the CSV file. And I can go ahead and look at the file to verify that it looks like the download was successful. And then all that's left to do is copy the data to the ratings table in the database. For that, you're provided with the SQL file copy data that contains the needed copy command. So in the terminal, I'll connect to the database and then run this I command to populate the table. And finally, I'll run the select statement to verify that the table now contains the data. And that's it. I hope you enjoyed this lab. And I can guarantee you that these basic troubleshooting steps will be very useful in your work as a data engineer. You can refer back to this video if you're running into any trouble applying these steps on your own. After you finish the lab, I'll see you in the next video to wrap up the week.
