DEV Community: Dendi Handian

Generating Table Schema from AWS Glue Table

Dendi Handian — Fri, 05 Dec 2025 07:56:44 +0000

Rather than copying the schema columns from the aws console or even from Athena sidebar, you could generate the schema from existing table using query:

SELECT 
 column_name
, ordinal_position
, column_default
, is_nullable
, data_type
, comment
, extra_info
FROM information_schema.columns
WHERE table_schema = 'TABLE_DATABASE' AND table_name='TABLE_NAME'

The above query will return all the necessary information about the table columns and you can copy them as CSV value from the athena result copy button.

Simulating Paralel Processing in Python using AsyncIO

Dendi Handian — Thu, 24 Jul 2025 06:21:02 +0000

Here is a simple simulation of how to run multiple process (or simply a function) in paralel using AsyncIO

from time import sleep, time
import asyncio

def delay(seconds):
  """
  A normal function that simulates a blocking operation.
  """
  print(f"Starting delay for {seconds} seconds.")
  sleep(seconds)
  print(f"Finished delay for {seconds} seconds.")
  return f'sleep for {seconds} second(s)'


async def main():
  start_time = time()
  loop = asyncio.get_running_loop()

  # Schedule the blocking functions to run in the default thread pool executor
  # run_in_executor returns a future that we can await
  tasks = [loop.run_in_executor(None, delay, seconds) for seconds in [3, 6, 9]]

  # Now we await all the tasks to run concurrently
  print("Waiting for all tasks to complete...")
  results = await asyncio.gather(*tasks)

  print(f'results: ', str(results))

  end_time = time()
  print("\n--- All tasks are done! ---")
  print(f"Results: {results}")
  print(f"Total time: {end_time - start_time:.2f} seconds")

if __name__ == "__main__":
    asyncio.run(main())

Hashing in Presto/Athena

Dendi Handian — Fri, 11 Apr 2025 03:46:57 +0000

The following hashing function require any parameter string to be casted into varbinary first. The you can apply any hashing function and transform it into hex to be a string and lower it.

SHA512

SELECT LOWER(TO_HEX(SHA512(CAST('your_string' AS VARBINARY))))

-- 7be98ae8208cafe0392b5b54ad86d40f5de3934bb82228c19d9697682785ac0a4fb9522bf28c6ec9db835318f21a9f7dde2855f0f4a907868074b5729792b6c4

SHA256

SELECT LOWER(TO_HEX(SHA256(CAST('your_string' AS VARBINARY))))

-- e10207f241d66a8c5f8202ed9fc22b94c031850dd54d8b03fdd40db4cbde5bfc

MD5

SELECT LOWER(TO_HEX(MD5(CAST('your_string' AS VARBINARY))))

-- 1f656879d9228b4b16e139d3c758f766

URL Functions in Presto/Athena

Dendi Handian — Tue, 18 Mar 2025 04:27:52 +0000

Extract Host

SELECT url_extract_host('https://example-subdomain.example-host.com/path/to/detail?order=desc&page=10')

-- example-subdomain.example-host.com

Extract Parameter

SELECT url_extract_parameter('https://example-subdomain.example-host.com/path/to/detail?order=desc&page=10', 'page')

-- 10

Extract Path

SELECT url_extract_path('https://example-subdomain.example-host.com/path/to/detail?order=desc&page=10')

-- /path/to/detail

Extract Port

SELECT url_extract_port('https://example-subdomain.example-host.com:8088/path/to/detail?order=desc&page=10')

-- 8088

Extract Protocol

SELECT url_extract_protocol('https://example-subdomain.example-host.com:8088/path/to/detail?order=desc&page=10')

-- https

Extract Query

SELECT url_extract_query('https://example-subdomain.example-host.com:8088/path/to/detail?order=desc&page=10')

-- order=desc&page=10

Extract Fragment

SELECT url_extract_fragment('https://example-subdomain.example-host.com:8088/path/to/detail?order=desc&page=10#header1')

-- header1

Capture Browser XHR/Fetch API Response Automatically into JSON Files

Dendi Handian — Thu, 12 Sep 2024 07:43:14 +0000

I wonder if we can capture the response of any browser's XHR/Fetch or simply API response into JSON files automatically or anything close to the method. This would be game-changing for data ingestion or ELT process and the only thing I just need to do is automate the browser using tools like Automa

There is a stackoverflow question/thread for this: https://stackoverflow.com/questions/68745624/automatically-export-fetch-xhr-response-chrome

Simple Python JSON Exporter & Importer

Dendi Handian — Wed, 24 Jan 2024 10:57:30 +0000

Exporter Function

import json

def export_json(filename, obj):
    with open(filename, 'w') as writer:
        writer.write(json.dumps(obj, indent=2))

Importer Function

import json

def import_json(filename):
    obj = {}
    with open(filename, 'r') as reader:
        obj = reader.read()
    return json.loads(obj)

Export and Import Dictionary (JSON object)

sample_dict = {
    "maps": [
        {'a': 1},
        {'b': 2},
        {'c': 3},
        {'d': 4},
    ]
}

export_json('maps.json', sample_dict)
obj = import_json('maps.json')
print(type(obj))
print(obj)

Export and Import List (List of JSON Objects)

sample_list = [
    {'a': 1},
    {'b': 2},
    {'c': 3},
    {'d': 4},
]

export_json('maps.json', sample_list)
obj = import_json('maps.json')
print(obj)
print(type(obj))

VS Code Extensions for Data Engineering - Part 1

Dendi Handian — Wed, 17 Jan 2024 22:16:40 +0000

This is my opinionated list for Visual Studio Code Extension that helped me during data wrangling, cleaning, processing, checks, and other utilization.

Rainbow CSV

CSV probably the most used format for storing datasets and it can read in plain text. When you reading csv file, do you face problem to distinguish each columns value? Rainbow CSV will help you to solve it.

Parquet-Viewer

Parquet is column-oriented data format for storage and data retrieval efficiency. You may find this format often in datalake storage system. You can view and read it using an interactive query tools, but not readable by text editor. Parquet-Viewer will provide you the view for the data.

Since parquet mostly store a huge amount of data, opening it will depend on your machine capability.

SQLite Viewer

Someone gave you a sqlite database. You probably started to think to install a program like sqlitebrowser, but only to view and read the data. Don't bother, you can use this extension instead.

Text Transformer for VSCode

VSCode only come with UPPERCASE and lowercase text transformation in the default installation. You may need the other text cases like snake_case , camelCase , PascalCase . This extension will enable them.

Increment Selection

We can duplicate lines of number in VSCode, but not incrementaly. This problem happened when we want to label each lines with sequential number. Increment Selection will increment our blocked lines automatically and you can start the sequence from any number you want.

Jump and Select

This extension is very useful to me when I'm dealing with CSV and processing multiply rows. The problem was traversing between commas to get you to the designated column.

This extension may come in handy for different case.

Hope the list helps you to process the data faster.

Alpine Linux in WSL

Dendi Handian — Wed, 29 Mar 2023 15:15:24 +0000

After trying Debian in WSL, now I'm trying for something lighter, the Alpine distro.

Requirements

Having WSL (WSL2) activated

Installing Alpine WSL

Open Microsoft Store and search for Alpine WSL. Install and Open after it installed.

When you launch it at first, it will perform the installation setup and wait for it.

If your installation froze up to the last line like in the image above for like more than few minutes, try to close it and reopen alpine linux. Somehow the installation was actually completed.

Checking the installed Alpine version

Use the cat /etc/os-release command to see the release information.

Common APK commands for Alpine

Alpine is powered with command tools named apk, stands for Alpine Package Keeper. You can confirm it's installed by typing apk and you will see the apk version and usage information.

Updating the repository indexes

To download and update the packages information to the latest, use this command:



apk update

Installing packages with `apk`

You can browse for the available packages at pkgs.alpinelinux.org/packages or you can search it using CLI. Since Alpine Linux doesn't come with built-in editor like nano in ubuntu, we have to install it as the demo.



apk search nano

Above commands will list available packages that has nano on it's name.

But the straightforward to install it, just type this:



apk add nano

Check if the nano version actually matches the version in the latest available packages.



nano --version

Now you can try to edit a file using nano. But maybe you have to create it first using touch sample.txt and then you can nano sample.txt afterward.

Testing Python server in Alpine WSL

You can install python3 using apk command:



apk add python3

Confirm the python installation using python3 --version. Just like what we've tried in Debian WSL, you can start a simpl python server using



python3 -m http.server 8000

And open http://localhost:8000 in your browser to confirm it.

Debian in WSL

Dendi Handian — Sat, 18 Feb 2023 10:01:10 +0000

This post is my journal on trying debian in WSL and playing around with it as starter.

Requirements

Having WSL (WSL2) activated

Installing Debian WSL

Open Microsoft Store and search for Debian. Get to install and Open after it installed.

It will launch debian program window. Fill the new user and password to use it.

Download Package Information



sudo apt-get update

Checking Debian Version



sudo apt-get install lsb-release



lsb_release -a

Simple Python Server Test in Debian WSL

Installing Python



sudo apt-get install python3

Verifying Python Installation



python3 --version

Starting a Simple Webserver

Here is the command to start a simple web server in python



python3 -m http.server 8000

Open the http://localhost:8000 in your browser to confirm if it accessible.

Installing Pip (For Further Python Development)



sudo apt-get install python3-pip

Browse Files in Visual Studio Code

Install the WSL extension

After it installed, you can click the arrow-like icon at bottom-left of VSCode

And it will open the menu for SSH connections, pick the Connect to WSL using Distro and choose Debian

It will open a new VS Code window and wait a while for the server connection and then you can browse and manage the files inside the WSL Debian through VS Code.

You can close the connection to the WSL later by clicking the same arrow-like icon and choose close connection.

Installing SQLite3 on Windows

Dendi Handian — Sun, 12 Feb 2023 14:29:30 +0000

Downloading SQLite binary

Go to sqlite.org/download.html, choose sqlite-tools-win32-x86-*******.zip to download.

Copying binary to installation folder

Create a folder inside C:\Program Files named sqlite. Extract the downloaded zip and put all three binary files in this folder.

Adding path for sqlite binary

Hit Windows key and find for edit the system environment variables control panel shortcut and open it.

Go to environment variables, chose Path variable and add C:\Program Files\sqlite to the path.

Verifying the Installation

Open new command prompt (cmd) and execute this to check sqlite3 version and confirm the installation:

sqlite3 --version

Import and Export Large SQL File in Laragon

Dendi Handian — Wed, 16 Nov 2022 02:22:10 +0000

Importing or Exporting large SQL file through PHPMyAdmin or Adminer may not be a good idea, since there is a chance of getting timeout or it's just taking to long to wait. A more powerful way to import large SQL file is through MySQL CLI. Laragon has its own environment terminal where all MySQL CLI command are executable from any directory. Let's try it out.

Checking the MySQL CLI command

Open the laragon terminal (Cmder), the working directory should be started at C:\laragon\www by default.

Try to check the mysql version to confirm the command is available in the current directory or anywhere:



mysql --version

Creating a demo database (MySQL)

The completed credential example for this demo are:

host: localhost
user: myuser
password: mypassword
database: my_large_db

For the demo, you may want to create a new database named my_large_db using web UI (phpMyAdmin or Adminer) or happily using this mysql cli command:



mysql -hlocalhost -umyuser -pmypassword -e "CREATE DATABASE my_large_db"

Importing Large SQL file (MySQL)

Let's say your large SQL file is located in the Downloads folder and it's named my_large_db.sql. Let's go the directory by terminal:



cd C:\Users\yourusername\Downloads

Then we can import the file from the directory using this command:



mysql -hlocalhost -umyuser -pmypassword my_large_db < my_large_db.sql

This import will take time but it's should faster than importing from a web UI.

Exporting Large DB (MySQL)

You can go to any directory you want using laragon terminal (Cmder) and that directory is where you will put the database dump file. Let's say we're gonna exporting the same datbase but into another sql file named my_large_db_backup.sql:



mysql -hlocalhost -umyuser -pmypassword my_large_db > my_large_db_backup.sql

or alternatively using mysqldump command:



mysqldump -hlocalhost -umyuser -pmypassword my_large_db > my_large_db_backup.sql

Tell me if this article is so helpful and helped you solve your problem in the comment below. Have fun exploring laragon.

Updating Adminer in Laragon for PHP 8

Dendi Handian — Fri, 28 Oct 2022 08:43:49 +0000

In Laragon, when you switching to PHP 8 and accessing adminer at http://localhost/adminer, you will find this error:

the errors are because of the existing adminer app only supported in PHP 7. If you're currently using PHP 8 for now and the future, We need to update the adminer.

Downloading the latest Adminer app

Go to https://www.adminer.org/#download, and find the download link for the latest adminer app. Adminer is a single-file php application, so you will find a single php file like adminer-4.8.1.php downloaded for example.

Updating the existing Adminer to the latest

Go to C:\laragon\etc\apps\adminer (or adjust to your laragon directory path) and you will find a single php file index.php. You may want to delete or backup this file and replace it with adminer-4.8.1.php and rename it into index.php

Accessing the new Adminer

With PHP 8 running, now the new adminer app will be accessible in the same url and you can start to login into a database.

as per adminer 4.6.3 or newer, you cannot login into a database with a blank password (ref), unlike the phpMyAdmin. So to use adminer, maybe you need to create a new database admin user with a password through mysql cli or phpmyadmin.

Good thing when you switch back to PHP 7.4, the latest adminer 4.8.1 still supported.

DEV Community: Dendi Handian

Generating Table Schema from AWS Glue Table

Simulating Paralel Processing in Python using AsyncIO

Hashing in Presto/Athena

SHA512

SHA256

MD5

URL Functions in Presto/Athena

Extract Host

Extract Parameter

Extract Path

Extract Port

Extract Protocol

Extract Query

Extract Fragment

Capture Browser XHR/Fetch API Response Automatically into JSON Files

Simple Python JSON Exporter & Importer

Exporter Function

Importer Function

Export and Import Dictionary (JSON object)

Export and Import List (List of JSON Objects)

VS Code Extensions for Data Engineering - Part 1

Rainbow CSV

Parquet-Viewer

SQLite Viewer

Text Transformer for VSCode

Increment Selection

Jump and Select

Alpine Linux in WSL

Requirements

Installing Alpine WSL

Checking the installed Alpine version

Common APK commands for Alpine

Updating the repository indexes

Installing packages with apk

Testing Python server in Alpine WSL

Debian in WSL

Requirements

Installing Debian WSL

Download Package Information

Checking Debian Version

Simple Python Server Test in Debian WSL

Installing Python

Verifying Python Installation

Starting a Simple Webserver

Installing Pip (For Further Python Development)

Browse Files in Visual Studio Code

Installing SQLite3 on Windows

Downloading SQLite binary

Copying binary to installation folder

Adding path for sqlite binary

Verifying the Installation

Import and Export Large SQL File in Laragon

Checking the MySQL CLI command

Creating a demo database (MySQL)

Importing Large SQL file (MySQL)

Exporting Large DB (MySQL)

Updating Adminer in Laragon for PHP 8

Downloading the latest Adminer app

Updating the existing Adminer to the latest

Accessing the new Adminer

Installing packages with `apk`