DEV Community: Jady Nekena

I discovered the largest files in my Windows C disk

Jady Nekena — Wed, 17 Aug 2022 15:15:31 +0000

Motivation

Have you ever wondered what are the largest files in your local disk ? Well, I also did. But at the same time, I had two constraints on mind :

I didn't want to use any third party tool to process the disk scan.
I was absolutely not going to scan it manually.

This article will show you step by step how I did it. But before we're diving in, let me show you the final Tableau data vizualisations, which are quite satisfying !

Dataviz results

First dataviz


Extension total sizes, grouped by usefulness

Insights

There are a lot of files without any extension (light blue on the left-hand side).
The ucas files from Unreal Engine archives actually make sense, as I do play Fortnite.
The vsix files are some visual code extensions. I still wonder how they came into my computer, I only use Sublime text as main editor...
I didn't realize how big my png photos were until this chart showed it up.

Second dataviz


Extensions with their total sizes and number of files, grouped by usefulness

Insights

On average, OS files are bigger than non-OS ones.
There are more than 150k files without any extension (I assumed they are for the OS but who knows?).
There are only 171 ucas files, which means that 1 ucas file is larger than the average.
I honestly should remove the useless 2Gb used by vsix files.

Third dataviz


Number of files per folder depth

Insights

There are 24 levels of folders, where the first one is the disk itself C:/.
Most used directories are generally between 4th and 12th depth.
6th level don't contain a lot of files : there must be only subdirectories in this folder depth.

Fourth dataviz


Folders depths grouped by usefulness
1 dot = 1 file
1 color = 1 folder
Y axis = folder depth starting with 1, from top to bottom

Insights

The far we go down (to greater directories depth), the less are the amount of files.
Empty spaces that are created in non-OS files stand for exclusive OS folders.
Among OS files, those large lined-up areas stand for Microsoft Services files : ![os-folder-1][os-folder-1] ![os-folder-2][os-folder-2]
Among non-OS files, the large pink and green lines stands for %AppData% subfolders, where all caching processes are happening and stored : ![pink-line][pink-line]

How did I do it

Gathering files details

Before having the above final vizualisation, the first step is obviously to gather datas. I just used the following two lines code from my cmd terminal :

cd C:/
where "*.*" /r . /t > f:\list-of-c-files.txt

Note that the output file is stored out of the scanned disk so that it doesn't interfer while scanning.

Initial output

The output will look like shown below :

Quite ugly, right ? Let's do some cleaning.

Data cleaning

This step can be done in any software or programming language that you like. In my case, I directly used Tableau Software.

I import the initial file as a text file with a random non-used character as delimiter. From this way, I can customize all new calculated fields from raw datas manually. In my case, I used ^ as the seen in this (french version) screenshot from Tableau Software Desktop :
I create all the new calculated fields and hide the single raw column src_all :
I preview final output datas to make sure everything fits to what I expected :

And that's it, we are ready to dataviz !

If you want to preview your own files...

Just ping me on Twitter and I will be glad to give you the Tableau template to get started quickly !

Feel free to tell me what are your thoughts on this side-project of mine on the comments section below.

Retrieve datas from postgreSQL to Google Sheets with Heroku for free

Jady Nekena — Sat, 26 Jun 2021 05:29:20 +0000

Motivation

Connecting postgreSQL to Google Sheets might be tricky, as there is no explicit solution to do so, unless you use third party tools such as Zapier, Kloud.io or Blend.co. Moreover, some of those solutions are not forever free.

As a developer, I’d rather create my own things, and share them with people who might need them for their own projects. Let’s see how it goes.

Why doing this?

The main goal is to retrieve datas from a place to another : basically for better monitoring. As you may know, Google Sheets is available from Desktop and mobile, and it’s free to use. You will see some scripts on Google Appscripts that will empower your app easily. You can also connect your sheets to Tableau Public for some magical Data Viz, and refresh datas daily without any effort from you. You can find how to do so here.

Some assumptions before we begin

The following code is assuming that you’re using the same dbname-username-password for all the databases that you want to monitor. Of course, if this assumption is not true, you should write down a new way to handle all dbnames-users-passwords, by changing the variables DB_NAME, DB_USERNAME and DB_PASSWORD.

Let’s do it in 5 Steps

1. Create few accounts

You will need to register to the following tools:

Google Drive of course
Github for creating a postgreSQL database on Supabase and hosting your future python code
Heroku for your python server, using flask

Once you’re done signing up to these 3 platforms, let’s go to step 2.

2. (Optional) PostgreSQL database on Supabase in less than 2 minutes

If you already have a postgreSQL database to monitor, discard this step and go to step 3 directly.

The database

If you never heard about Supabase, it’s the open source firebase alternative, using postgreSQL. They are offering a free tier pricing for 3 hobby projects, that you may use for the purpose we have here. To get started, just sign up with your Github account, and allow all required credentials.

Once logged in, click on New Project > Your org’s name.

Provide the project name, the database password, and the region closest to your current location. In this example, I’m going to use the following details:

Name : my-super-project

Database password : hellothisisapasswordandihopeitsstrongenough2021

Region : West EU (London)

Just let the magic process for 1 minute or 2.

The table that will be fetched

Once your database is ready, use the button on the left side bar: to run the following SQL query. It will create the table you will retrieve datas from later on.

CREATE TABLE public.conv
(
    id uuid NOT NULL DEFAULT uuid_generate_v4(),
    sender character varying COLLATE pg_catalog."default",
    receiver character varying COLLATE pg_catalog."default",
    content text COLLATE pg_catalog."default",
    datetime timestamp with time zone DEFAULT now(),
    CONSTRAINT conv_pkey PRIMARY KEY (id)
) TABLESPACE pg_default;

COMMENT ON COLUMN public.conv.sender
    IS 'the one who sends the message';

COMMENT ON COLUMN public.conv.receiver
    IS 'the one supposed to receive the message';

COMMENT ON COLUMN public.conv.content
    IS 'the actual message';

COMMENT ON COLUMN public.conv.datetime
    IS 'the date time the sender sends the content to the receiver';

ALTER TABLE ONLY public.conv ADD CONSTRAINT conv_pkey PRIMARY KEY (id);

Now, let’s insert some datas in it.

d5f67084-c0ca-4ae5-8957-348237ba51be    A   B   Hey! It's been a while. How you're doing?   2021-06-26 12:03:22+00
5287170c-b6f6-47fe-ad0c-9f724ef60acb    B   A   Hey.\nThanks for your message. I'm doing fine, thanks!\nWhat's up?  2021-06-26 12:03:31+00
1c927c51-dd2f-4f1c-9ead-609f71b7747e    A   B   Nothing much. Wanna hang out tonight?   2021-06-26 12:04:10+00
f243838c-1596-46d1-86d5-304def0840db    B   A   Sure, I must be free at 7pm. What was the name of the bar we used to hang out in?   2021-06-26 12:04:50+00
a3ce9c80-8333-4fc7-8d27-7c19c31326d4    A   B   Foo. The Foo Bar.   2021-06-26 12:05:45+00
8f990cc9-35e7-4fa8-baac-d87bcf9f213e    B   A   Oh, yeah. How could I forget something that is so obvious. LOL  2021-06-26 12:06:14+00
02c18b5f-098e-479e-bcf5-42946cf34fe1    A   B   See you there btw, is 7:30pm ok for you?    2021-06-26 12:07:02+00
6fb02446-263d-4bf9-bdc1-51ad7f03c40c    B   A   Perfect. See you!   2021-06-26 12:07:23+00

Your supabase table must now look like this:

3. Create your python server hosted in Github and deployed on Heroku

The connection string

Your database connection string should look like this: postgres(ql)://user:password@your-host-location.com:portnumber/dbname.

If you created a database from Supabase, you can find it in Settings > Database > Connection string. The actual connection string will look like this:

Now that you have this connection string, let’s code!

Fork the github project

Directly from the repo.

The python server

Our goal here is to create the controller that receives a SQL statement (such as SELECT * FROM NAMETABLE), and gives back the datas as JSON. It’s actually a REST API controller.

Important cautions :

The following method is not secure. You will write down your postgreSQL database password inside your code, so be sure to host the file in a private repo on github.
If your datas are sensitive, you should consider implementing some authentication layer before accessing to your Heroku app.

Now create a local directory, named python-server for example. From now on, you will need to create 5 files. 2 of those 5 files will be auto-generated , you will read more about it later, just right here

app.py

The code is here.

cert.pem and key.pem

Those 2 files are auto-generated, generally from openssl in terminal, or an easier solution like this website that provides self-signed certificates. Once you provide the Common Name, you can generate both files and have some kind of texts like this:

In your root directory (python-server in our example), copy and paste the :

Certificate in a file named cert.pem
Private Key in a file named key.pem

requirements.txt

The code is here.

It’s telling you (and Heroku right before deployment) the useful dependancies for your project.

Procfile

The code is here.

It’s starting the server while on Heroku.

I promise we’re almost done! You can run it locally to verify that everyhing works fine. Run python app.py then go to:

https://127.0.0.1:5000/<YOUR-DB-HOST>/<YOUR-SELECT-STATEMENT>

In my case, I have this JSON:

Now, push all those files in your forked and private repo on github.

Once you’re done, go to your Heroku account, we’re going to create your app.

4. The Heroku project

Init the project

After logging in, go to your Heroku dashboard and click on Create new app.

For my example, I will use the same name as from the repo: python-server-heroku.

Use the closest location to where you’re from. Then click on Create app.

Deploy from github

Go on Deploy tab > Deployment method > Connect to Github. Authorize credentials.

You will then have a “App connected to GitHub” section. Write down the repo-name with your own. In this example, mine is still python-server-heroku. Once the repo is found, click on Connect.

Automatic Deploys

Go on Deploy tab > Deployment method > Automatic deploys. Just click on Enable Automatic Deploys.

How does it work on Heroku

The Procfile file will help your app to run on the web and scale:

web: gunicorn app:app
heroku ps:scale worker=1

Test your Heroku App

On the up right corner of your Heroku App Dashboard, click on Open app. If the Hello World statement is displayed, your app is running. Try now to go to <YOUR-HEROKU-APP>.herokuapp.com/<YOUR-DB-HOST>/<YOUR-SELECT-STATEMENT> and see the datas displaying as JSON object.

5. Fetch datas into Google Sheets

Create the Spreadsheet

Go to drive.google.com and create a new Google Sheet file. This will lead you to a new tab. Rename the file to whatever you want. Name the first sheet with the same name as your target table. In my case, I will name it conv. Open a new tab for the next and very last step.

Create the fetching script

Go to script.google.com. Create a new script and save it (the name still doesn’t matter). Paste the following code in Code.gs file:

const URL_OF_THE_SPREADSHEET = "https://docs.google.com/spreadsheets/d/1QMEr6ABZpLqo8d0h_nY4BCu3hdE0Bkbo-Zj7qgp37Qw/edit#gid=0" //to be changed
const URL_OF_HEROKU_APP = "https://python-server-heroku.herokuapp.com/db.ehbnuyfpyplmhgjshzmd.supabase.co/SELECT%20*%20FROM%20conv" //to be changed
const ss = SpreadsheetApp.openByUrl(URL_OF_THE_SPREADSHEET)

function go(){
  update_my_sheet("conv")
}

function getJSONDataFromUrl(url,params) {
  console.log(url)

  if(!params) params = {}

  params['muteHttpExceptions'] = true
  params['method'] = "GET"

  var response = UrlFetchApp.fetch(url, params);
  var parsedJsonData = JSON.parse(response.getContentText());
  return parsedJsonData;

}

function update_my_sheet(sheet_name){
  var url_to_be_fetched = URL_OF_HEROKU_APP
  var initial_results = getJSONDataFromUrl(url_to_be_fetched)

  //clear everything at the beginning
  var sheet = ss.getSheetByName(sheet_name);
  sheet.getRange(1, 1, sheet.getMaxRows(), sheet.getMaxColumns()).clear({contentsOnly: true});

  //no datas found  
  if(initial_results.length === 0) return false

  var results = initial_results.map(it => Object.values(it)) 
  var headers = Object.keys(initial_results[0])

  var rows_number = Number(results.length)
  var columns_number = results[0].length

  set_headers(sheet_name, headers)

  //2nd row cause we have the headers in 1st row
  var range = sheet.getRange(2,1,rows_number, columns_number);
  range.setValues(results);


}

function set_headers(sheet_name, headers){
  var last_column_address = columnToLetter(headers.length)
  ss.getSheetByName(sheet_name).getRange("A1:"+last_column_address+"1").setValues([headers]);
}

function columnToLetter(column)
{
  var temp, letter = '';
  while (column > 0)
  {
    temp = (column - 1) % 26;
    letter = String.fromCharCode(temp + 65) + letter;
    column = (column - temp - 1) / 26;
  }
  return letter;
}

IMPORTANT You will need to change URL_OF_THE_SPREADSHEET and URL_OF_HEROKU_APP from the code above.

Click on go function from the dropdown menu: Run it.

Accept all authorizations.

Go back to your spreadsheet tab, and see the magic!

(Optional) Enable a trigger to retrieve datas automatically

Instead of running the go function manually, you can setup a trigger. Go on the left-side pannel > Triggers. Then configure the parameters as follows, if you want a per-minute refresh for instance:

And that’s it ! Enjoy your datas from Google Sheets directly.