Explore the database

From the previous course, we :

created a postgres database;
logged into the database using pgcli;
used jupyter and iterative chunks to ingest data into the database.

Now, we can explore the database by using some SQL command lines. Here is an example :

# Get some statistics 
SELECT max(tpep_pickup_datetime), min(tpep_pickup_datetime), max(total_amount) FROM yellow_taxi_data;

But, to work more efficiency with the database, we can use pgAdmin.

pgAdmin

pgAdmin is a powerful open-source administration and development platform for PostgreSQL that provides a graphical interface to make database management easier. With pgAdmin, we can write and execute queries, manage tables and indexes, and visualize our data through its intuitive interface.

To run the container pgAdmin with Docker, we can do like that :

docker run -it \\
  -e PGADMIN_DEFAULT_EMAIL="[email protected]" \\
  -e PGADMIN_DEFAULT_PASSWORD="root" \\
  -p 8080:80 \\
  dpage/pgadmin4

where -e PGADMIN_DEFAULT_EMAIL and -e PGADMIN_DEFAULT_PASSWORD are environment variables used to set up the admin credentials for accessing pgAdmin. -p 8080:80 maps port 80 inside the container to port 8080 on our local machine, allowing us to access pgAdmin through a web browser dpage/pgadmin4 is the official Docker image for pgAdmin 4

Then, on the web browser, we can access pgAdmin by navigating to localhost:8080. After logging in with our admin credentials, we'll need to set up a connection to our PostgreSQL database. This connection will allow us to manage our database through the pgAdmin interface.