DEV Community: István Lantos

Super simple and fast delimited CSV data normalization with AWK

István Lantos — Wed, 25 Jul 2018 20:01:06 +0000

There was a discussion here on dev.to which programming language or tool more powerful than people realize?

I shared my AWK snippet, where I normalize a 8GB sized delimited CSV data source for database import, only with AWK, as fast as a Go counterpart program. This task completes in 7 minutes on my home machine, same as my Go code:

#!/usr/bin/awk -f

# This block only runs once at the start, before the first line
# Use this to print CSV header on the top
BEGIN {
    FS="|"; # input field separator
    OFS="|"; # output field separator
}

# This block runs at every line
{
    # We will order a new named variable to every column
    line = $0; # variable `$0` stores the entire line
    url = $1;
    title = $2;
    body = $3
    tags = $4;

    if (line ~ /^$/) next; # if line is blank, then skip it
    if (NF != 4) next; # if column count is not equal to 4, then skip the line

    # Skip any line where tags column contains the word "cars"
    if (index(tags, "cars") != 0) { next; }

    # Normalize the url column with regex by only keeping the article id
    # Example input: <a href="https://example.com/article/foo123456">Hello</a>
    gsub(/.*example\.com\/article\/|[\042].*/, "", url); # outputs: foo123456

    # Skip lines that has non-alphanumeric characters in url column (like <>#&@)
    # Skip lines that has empty url column (after gsub normalization)
    # Skip lines where url starts with foo or bar
    if (url !~ /[[:alnum:]]/ ||
        length(url) == 0 ||
        url ~ /^foo|^bar/) {
        next;
    }

    # Replace multiple ; with one (needed for errorless CSV import in Postgres)
    gsub(/[\073]+/, ";", tags);

    # Print the line with OFS, aka: profit! :)
    print url, title, body, tags;
}

Then you should run this with:

$ zcat my-csv-file.zip | ./normalize.awk | sort --field-separator="|" --key=1,1 > normalized-sorted.csv

Pro tip: combine this with tail command when updating DB later on. ;)

Maybe you find this snippet useful and saves you countless time to develop similar solution in Python or Go. You're welcome!

If you have anything to add or modify, then shoot me!

How to import a big delimited datatable into AWS DynamoDB without opening your piggy bank?

István Lantos — Tue, 24 Apr 2018 14:35:50 +0000

I'm working on a serverless stack.

I have a delimited CSV file with 5-6 million lines. I want to import this into AWS DynamoDB.

Is it possible somehow to import this into AWS DynamoDB, without burning my monthly salary down the toilet? How to solve this on the cheap?

This data dump will be constantly updated and I want to import the new lines into DB. I think the most efficient way would be to make a data dump from my DynamoDB, find a key, compare it with the new third-party data dump's key (this key needs to be extracted from a column by regex), and only import the new lines into my DB. Do you have some recommendation (big data framework or some AWS service) which can help me with this? Or I'm open if there's a shell or a Go script to do the same thing.

Thank You!

Give me a quick 101 in the Graphcool ecosystem (like Prisma)

István Lantos — Sun, 01 Apr 2018 14:46:11 +0000

I found this forum post which explaining the differences between Prisma and Graphcool framework.

But I still don't know which is the more feature packed from these and which has the most love in active development?

I want to self-hosting everything, including the GraphQL BAAS server which connecting to my self hosted database (Dockerised approach) and I want to query the data in a React based single page application. Authentication is also important (with given user roles) and I don't want any third-party auth provider.

If I want to use GraphQL based BAAS, looks like I don't have any other alternative to choose right now, only Graphcool (which is not a problem at all, I just can't figure out which is the ultimate environment from these).

Or self-hosted authentication is not possible, the only alternative is Apache Usergrid?

If I want a hosted BAAS, I would go with Firebase, because of the simple setup with React and also handling auth.

How do you protect your backend API in your microservice if you use a Single Page Application on the frontend?

István Lantos — Mon, 19 Mar 2018 18:19:59 +0000

I currently working on a pet project where I intend to learn a "simple" microservice architecture, meaning no universal rendering (or I thought it would be simple).

I have a backend REST API written in Go, which is proxying a third-party API. Later on it will be replaced with a Postgres database. Right now serving content under 127.0.0.1:8080/api/v1.

I have a static fileserver written in Go for SPA, which is serving a Single Page Application written in Vue.js, generated by vue-cli. This fileserver serving content under 127.0.0.1:8081. This vue app querying data from the API server with axios, fecthing AJAX requests.

This is where I hit the first problem: if I not whitelisting the address of the static fileserver on the API server, so the server doesn't have any CORS header presented, like:

Access-Control-Allow-Origin: http://127.0.0.1:8081
# or
Access-Control-Allow-Origin: *

The request got ignored by the browser.

Another problem (well, not really a problem) that I had to drop the relative urls in axios (because they are not on the same server anymore), meaning replacing:

return this.$http.get(`/api/v1/${this.id}/`, {

with:

return this.$http.get(`//127.0.0.1:8080/api/v1/${this.id}/`, {

in the vue app.

Am I doing something wrong here? How should I make request to the API server from the SPA from another server?

How can I protect my backend API from people to using it without restrictions?

I guess I have to implement a rate limiter for sure. I plan to use HAProxy for load balancing and probably this is the point where it should limit the requests from users who wants to execute DDOS attacks or use the database as a free fountain for their sites.

But there are no other way of limiting the API accessing it from the browser bar, if they type in the address of the API server? What about GraphQL? Same situation?

This site will not feature any authentication, user management, or restricted access at the start. But if I try to implement such feature in the future, how can I implement user authentication safely, not shooting myself in the leg? What are the best practices to do this, if I completely want to ditch server-side or universal rendering?

I want to have an SPA and an API server which will be a good start for web and mobile apps, too.

Your unfinished pet projects are letdown or a gain?

István Lantos — Wed, 21 Feb 2018 19:12:16 +0000

How do you consider them? Did they give you anything or it's just was a waste of time?

Whenever I looking my repo, I see unfinished projects:

I wanted to build my next photographer portfolio in Node.js + Express, instead I went with Wordpress again. But in return, I learnt Node.js.

I wanted to create a website, which listing companies of my country with public data. This stage, I'm an unverified identity to get access for this official database from the government, so I built a web scraper in Go able to start the development and actually come up with a product. The project still hangs in this state. I never finished it, but now I know how to implement this in Go, how to work with files on the disk and use the standard library.

What are your letdowns, that hadn't payed you anything, but you learnt something new?

What are your UNIX pipeline commands that saved you from lot of coding/time?

István Lantos — Fri, 27 Oct 2017 20:07:46 +0000

This question came to my mind last day when I wanted to free up some space on my HDDs and when it comes to what to delete (feel the pain), I wanted to know without lot of right-clicks which folders are the fattiest. As a good Windows user, I installed Total Commander, because some random Google result told me to do so.

Then I realised, heck, I have an entire UNIX environment on my PC (MSYS2), so maybe there is an utterly simple one liner command for achieve this. And guess what, it has: :)

$ du -sh * | sort -h

Life hack, place this in your .bashrc file:

# Within the current directory, list all folders and files
# and print their size, then sort it from smallest to largest:
alias du='du -sh * | sort -h'

What are your UNIX pipeline commands, where you can combine some program's standard output and creating something wicked simple time saver?

Hi, I'm IstvÃ¡n Lantos

István Lantos — Tue, 25 Apr 2017 20:27:30 +0000

I have been coding for [number] years.

You can find me on GitHub as DJviolin

I live in [city].

I work for [company]

I mostly program in these languages: [languages].

I am currently learning more about [topic].

Nice to meet you.

PS.: Is it okay to leave everything at default state for you? :)