ANALYSING & VISUALISING DATA With PostgreSQL and Arctype SQL CLIENT

In this article, we’ll learn how to analyse data using PostgreSQL and an SQL client known as Arctype.

Data analytics is significant since it aids in the performance optimisation of business. Companies can assist cut costs by locating more effective ways to do business by incorporating it into their business strategy. Additionally, a corporation can use data analytics to improve business decisions and track consumer preferences and trends to develop fresh, improved goods and services.

It is necessary for companies and startups that want to be at the top of their game to employ data analytics because it helps businesses understand the problems they are facing and to explore data in several meaningful ways.

Data Analysis? What is it?

Data analysis is the process of organizing, analyzing, and displaying data with the aim of gaining insightful knowledge and influencing more informed business decisions.

Whether you're looking at quantitative or qualitative data will affect the strategies you employ to examine it.

What is PostgreSQL?

PostgreSQL is a free and open source relational database management system used as a primary data storage for many web apps, mobile apps, etc.

Arctype is a collaborative SQL client for devs and teams. It is a platform where you can collaborate with teams, manage data, and create dashboards using your own queries and logic. Arctype workspaces are straightforward to share, and their granular access control makes it easy to invite users and manage access restrictions.

SETTING Up The ARCTYPE SQL CLIENT

This is how Arctype homepage looks like

Arctype application is available for download for macOS, Linux, and Windows operating systems. Download, install and open it up. Follow the prompts by creating an account and get the client ready.

SETTING Up PostgreSQL

To download Postgres, navigate to the postgres page . Postgres is available for macOS, Linux and Windows operating systems. After downloading and installing Postgres, run this command to open the Postgres command prompt

sudo -u postgres psql postgres

This opens up the command prompt and demands that you authenticate the command prompt with a password. Enter your password. Next, create a database, say “library”, in PostgreSQL where data from the library CSV file will be stored by running the command below and hitting ‘enter’ on the keyboard

CREATE DATABASE library;

To know if your database has been created successfully, run this command.

\l

The library database is highlighted in red.

MERGING Postgres With the Arctype SQL client

Open Arctype SQL client after installation and creating your account, Now select your database (PostgreSQL)

After choosing PostgreSQL as the database, key in the credentials.

Name should be the name of the database. In this case, ‘library’. Ensure that the port is exactly the same as the port on which the server is running on Postgres. Key in the password of your computer and enter the name of the database just as you named it in the terminal. In this case ‘library’.

You can know whether or not the credentials entered are correct by clicking on the “Test Connection” button at the bottom of the window. Click “Save” to save the details.

This is how it looks after saving up the information

Now, the next step is to head to kaggle and download the library dataset CSV file. Locate “Tables” at the top left part of the archetype, click on the three dots beside the table search field. Select “import CSV to table”

Select the downloaded dataset file and click ‘open’. Click “accept” on the pop-up window. This window will take you to a final window where you will be required to select where you want the data to be uploaded to and in what schema (framework) you would want.

Click the “import CSV” button to import the CSV file. After importing successfully, click on the ‘refresh’ button at the top right, next to the ‘export’ button. This is how it should look.

USING ARCTYPE to EXECUTE SQL Queries

Arctype can be used to run SQL queries, display databases, or even make changes, etc. Displaying data means data can be presented in forms of graphs, barcharts, to visualize the specific data.

Suppose you want to know the number of items in the MTS library. MTS Library provides ebooks, etc. An SQL query can be run to know, say, user reading statistics; item ID, book, genre, author, year of publication, etc.

To run an SQL query, click on the ‘Create query’ button. Name your query. Mine will be titled "analyzed." Now, write the query for the analysis from the MTS library’s data. Write the following SQL query

SELECT id,year FROM items_1

The query should create a table with two columns named id and year.

PRESENTING VISUALS/CHARTS Using ARCTYPE

On the right side of the screen, you can select the type of chart you want. Here, we select “Scatter Chart.” Then below the “Select Chart Type” option, you have the “Specify Columns For The Chart" option. In this section, drag the year column to the X-axis column and the id column to the Y-axis column. Arctype should help you come up with a chart similar to the one shown here

Conclusion

We have learned how to analyze and visualize data using postgresql and Arctype SQL client. Try different data sets and enjoy the journey!