The purpose of this lecture is to answer the question how can I view the data in my data warehouse?
In our case, there are two ways that this can be done.
Can either go inside the Postgres container and use SQL, or you can set up connection using a database
management tool like Dbeaver or Datagrid, which provides a more user friendly UI over the SQL syntax.
We'll go over both in this section.
So starting off with the SQL to access the Docker container, you can run the following command, which
we have seen in previous lectures.
Docker exec t going inside the Postgres container.
And now we can start writing the actual SQL commands to connect to the Postgres instance.
So we can write psql minus u which defines the username.
In this case we have our username is YouTube underscore API underscore user.
And also we specify the database which is ellt underscore db.
This as you know we can find in the dot env These two should agree with these two over here.
Let's click enter.
And here we have the interface with the actual database.
L't underscore db.
If we were to run a simple SQL command to get all the data from the course schema inside the YouTube
API table, we would write this simple SQL, press enter and as you can see the data is here.
And you can continue to interact with the table using SQL CLI.
Just as we saw with the Select statement.
If you want to exit the statement, just press Q.
Some other psql commands.
This is good to know r backslash view.
So this gives users.
For this connection I'll remind you that we are connected as the YouTube API user.
There is backslash L.
This gives a list of databases.
Here you can see the metadata backend database, the salary and the ones we are connected to right now.
The rest are gated by default.
Backslash n gives the schemas.
Here we see the core and the staging schemas.
And there's also default public one.
So this took a while to actually write.
What I wanted to show here is that we can find all the tables under schema by writing the backslash
TW command and then specifying the schema in question.
So here we want to have the YouTube API table.
Obviously there are other SQL commands that you can use here.
We just went over the more common ones.
You can research other commands in your own time.
On a final note, if you want to exit from this database connection, just write backslash q.
Now that we have gone over the CLI, let's direct our attention to the UI.
For this we need to install an SQL client.
And in my case I use Dbeaver.
I will leave a link in the appendix of this section where you can install the beaver, and you can go
right ahead and install Dbeaver community, which is the open source version.
Once you have installed it, you have this UI.
You simply need to go to database need database connection.
Since we have and we are using Postgres on Postgres, and from here we can start filling in the information
needed for host name or host.
In our case, since we have the container running on our local machine, we can set host to localhost
the ports.
We can leave it as a default of five volts.
This is the port on our laptop that maps to the container's ports, which we had set so default of 5432
as well.
Username and password which you see here we can get from the EMV.
So I will get those right now.
We can also press the show all databases which will give all databases for this connection.
You can also change this to LTE underscore DB which will give us the database that contains the LTE
data.
So make sure that you have inputted all of these credentials correctly and you can press the test connection
button so you can see that we have successfully connected.
That's a very good sign.
And we can press finish.
Here we have the connection that we just set up.
And if we open it up you will find that we will have the ELT database that we just connected to, but
also the other databases for the airflow metadata database and the salary as well.
If you want to change the connection name, you can simply go on the connection edit connection and
go on general.
Here you can change this connection to something like Airflow connection YouTube.
And you can also change the connection type.
Let's change this to test for example.
I will press okay and we will reconnect again.
Now in terms of exploring if we have to go inside the database, we will look and find the schemas that
we built in the previous lectures.
If we were to go, for example, inside core, we will find the YouTube API table.
And same goes for staging.
Now, if we wanted to do the same SQL that we did in the SQL, using the CLI we can do is simply right
click on the database connection SQL editor and open SQL script.
From here we can run the same command that we ran before.
So select all from core.
Let's use your API.
Let's press run.
And here you will see the same data that we saw on SQL.
And this is all the videos that we have up until today's date that we have run the ELT for.
Feel free to also run different queries to for example, filter for video types that are only of the
type shorts.
So this you can simply just change to where video type equals shorts.
Shirts.
As you can see, all of the video types are shirts.
And here, if we had to order the sending the maximum value, the duration should be of one minute,
and it is in fact the case you can continue to explore in your own time.
You can also explore the other databases that we have, and you will see the associated tables that
are present here.
If you go, for example, under the table you can see the columns.
This is all auto generated by airflow and you can find the guides, the if it's active, if the Dag
is paused and all of these other parameters in terms of seeing the actual data, we will probably get
a permission denied.
As you can see here, because we need to reconnect using the airflow metadata database credentials that
we have for the specific connection.
So in reality, what we can do here is to change this connection name to only be for database and right
the other two connections to the other two databases, and that is what I will be doing next.
Let's remove this from here.
So as you can see now I have each connection set up with the appropriate credentials.
And now we will have to repeat the same exercise as before.
Going inside the public schema and going inside the deck table, we should be able to see the actual
contents of the table.
And as I was explaining before, here you can see the data which we produced ourselves, and other data
and parameters related to the particular diving question like the description when it is set to run,
and many more parameters you can continue to explore in your own time.
But this gives you a good starting point in terms of setting up connections and viewing data in the
tables inside your databases.
Congratulations!
You made it to the end of Data Warehouse section.
In the next section we will explore testing and we will start off with data quality tests.
I will see you then.