PostgreSQL
PostgreSQL is a database used to store , manage and retrieve structured data. It can be hosted in 2 places
Locally hosted postgres
This database runs on your own computer and can only be accessed through your laptop.
Cloud hosted
In this case the database lives in a server owned by a cloud provider and they offer this access as a service. Examples are like
1.Azure
2.AWS
3.Google Cloud
4.Aiven
5.Digital Ocean
To enable you connect remotely, the cloud provider gives you
1.Host
2.Port
3.Database name
4.Username
5.Password
Using a DBMS
A DBMS is a software that enables one interact with their database either its stored locally or in the cloud.
We usually don't directly interact with PostgreSQL , we need a DBMS to provide an interface to interact with the database and perform DDL, DML, DQL, DCL, DCL, TCL.
Example is DBeaver and you fist choose the database to connect with and in our case is postgreSQL
Then we connect using the details host, port, password of your database.
For local hosted connection details are usually:
Host: 127.0.0.1 /localhost
Port: 5432 and for cloud hosted the provider share the details.
Creating and Importing Data
Once connected to a PostgreSQL database through DBeaver, there are two common ways to populate the database:
Creating tables and inserting data using SQL
Create a schema
Open an SQL script and write SQL commands to create tables.
Define primary keys and foreign keys to establish relationships between the tables.
Insert data into the tables using INSERT statements.
Importing existing data
Create a schema
If the data already exists in files such as CSV or Excel import it into the database.
During the importing the tables are loaded into to the schema.
Connecting to Power Bi
After our database contains the data , we use Power Bi as the reporting tool. PowerBi connect to many tools to get the data e.g
Excel
CSV
Database, in our case PostgreSQL
Local Connection
The server is the host: port , which is 127.0.0.1:5432
SSL CERTIFICATE
Local database usually connect directly because Power BI and PostgreSQL are on the same computer, however for Cloud they don't.
For security purposes we need download an SSL certificate which the cloud provider should give and upload it in our laptops in our certificate manager under Trusted Root Certification Authorities
In this case, Power BI has successfully connected to the Aiven PostgreSQL database. We can transform and clean it using Power Query, create visualizations and develop interactive reports and dashboards.
Top comments (0)