DEV Community: Zachary Becker

Querying CSV Files Using Postgres

Zachary Becker — Mon, 04 Sep 2023 04:54:33 +0000

While reading PostgreSQL: Up and Running, I noticed a few features where it is easy to import data from a CSV file into postgres. However, the example in the book required a complex CREATE TABLE query that was specific to the CSV file that they were loading.

I figured it would be cool if we could load an arbitrary CSV files, and automatically create a table based on the first line of the CSV file which often contains the column names for the data included in the file.

Sample Dataset

To test this out, I used a free data set (Electric Vehicle Population Data) from the State of Washington which contains data about registered electric vehicles in the state.

You can easily grab this data set with the following shell command:

wget \
  https://data.wa.gov/api/views/f6w7-q2d2/rows.csv?accessType=DOWNLOAD \
  -O Electric_Vehicle_Population_Data.csv

Load Column Data

This CSV file contains the expected header row

VIN (1-10),County,City,State,Postal Code,Model Year,Make,Model,Electric Vehicle Type,Clean Alternative Fuel Vehicle (CAFV) Eligibility,Electric Range,Base MSRP,Legislative District,DOL Vehicle ID,Vehicle Location,Electric Utility,2020 Census Tract

In order to generate a CREATE TABLE command to create a table based on these columns, we first need to load the column names into PostgreSQL.

However, since we don't care about persisting this information past the task of creating the table of electric vehicle data, we can load it into a temporary table.

From a psql prompt, I created this temporary table and loaded it with data with the following commands

CREATE TEMP TABLE "csv_columns" ( "name" TEXT );
\copy "csv_columns" FROM PROGRAM 'head -n 1 Electric_Vehicle_Population_Data.csv | sed ''s/,/\n/g'''

This command reads the first line of our csv file, and replaces the commands with newlines. The copy command takes each line of our output, and puts it into our csv_columns table.

Loading CSV Data

With psql, we can construct a query with a SELECT statement, based on our columns table, and execute that query with the \gexec command.

We employ the built in functions array_to_string which is like strings.Join in main languages, and array_agg which aggregates values into a single value.

We can create the table based on the loaded column information using the following command

SELECT
    'CREATE TABLE "public"."electric_vehicles" (' ||
        array_to_string(array_agg('"' || "name" || '" TEXT'), ',') ||
    ');'
FROM "csv_columns" \gexec

This effectively executes the SQL query

CREATE TABLE "public"."electric_vehicles" (
    "VIN (1-10)" TEXT,
    "County" TEXT,
    "City" TEXT,
    "State" TEXT,
    "Postal Code" TEXT),
    "Model Year" TEXT,
    "Make" TEXT,
    "Model" TEXT,
    "Electric Vehicle Type" TEXT,
    "Clean Alternative Fuel Vehicle (CAFV) Eligibility" TEXT,
    "Electric Range" TEXT,
    "Base MSRP" TEXT,
    "Legislative District" TEXT,
    "DOL Vehicle ID" TEXT,
    "Vehicle Location" TEXT,
    "Electric Utility" TEXT,
    "2020 Census Tract" TEXT
);

Once we have our table, we can load the CSV data with the copy command.

\copy "public"."electric_vehicles" FROM 'Electric_Vehicle_Population_Data.csv' CSV

Querying CSV Data in PostgreSQL

We can now query our CSV data as a SQL table, for example, this gets the top 5 makes of electric vehicles registered in Washington State.

SELECT
    "Make",
    count(*) AS "Count"
FROM "public"."electric_vehicles"
GROUP BY "Make" 
ORDER BY "Count" DESC
LIMIT 5;

Which outputs

   Make    | Count
-----------+-------
 TESLA     | 65552
 NISSAN    | 13317
 CHEVROLET | 11816
 FORD      |  7307
 BMW       |  6209
(5 rows)

Conclusions

I found this a really interesting way to learn PostgreSQL better, and perhaps a way I can grok CSV data in the future.

Code

Here is a full psql script to load and query the Electric Vehicle Population Data CSV file. I named it create_ev_dataset.sql, and execute it with psql -f create_ev_dataset.sql. It assumes the CSV is in the directory that you ran psql from.

-- create a temporary table to load the column names into
-- this table only exists for the duration of the connection
CREATE TEMP TABLE "csv_columns" ( "name" TEXT );

-- split the first line of the csv file to get the column names on individual lines, and copy them into our temporary table
\copy "csv_columns" FROM PROGRAM 'head -n 1 Electric_Vehicle_Population_Data.csv | sed ''s/,/\n/g'''

-- use our temporary table of column names to construct a table to store csv data in
SELECT
    'CREATE TABLE "public"."electric_vehicles" (' ||
        array_to_string(array_agg('"' || "name" || '" TEXT'), ',') ||
    ');'
FROM "csv_columns" \gexec

-- copy csv data into the table
\copy "public"."electric_vehicles" FROM 'Electric_Vehicle_Population_Data.csv' CSV

-- query csv data using postgres
SELECT
    "Make",
    count(*) AS "Count"
FROM "public"."electric_vehicles"
GROUP BY "Make" 
ORDER BY "Count" DESC
LIMIT 5;

A Minimal Node.js and Express Server Setup and Deployment

Zachary Becker — Thu, 23 Aug 2018 03:04:21 +0000

As a software engineer I often forget the complexities of how my code is deployed. When I run the code I write, typically it is done on my local development environment, and then I push my code to a repository where through continuous deployment magic my code becomes available to my end users.

While I reap many benefits from this complexity, I often forget why it is there, and when things don't work, I am simply annoyed by the complexity.

In this article I put together a very minimal Node.js and Express application and deploy it manually onto an AWS instance. The goal of this exercise is to evaluate the advantages and disadvantages to doing my deployment like this.

Note: It's worth mentioning by minimal I mean fewest number of pieces, not necessarily fewest number of steps. Something like Heroku will likely get you up and running much quicker.

The Application

The application I have in mind is a simple "Hello World!" Express application. First I run a few commands in bash to set up my envrionment.

mkdir express_hello_world
cd express_hello_world
npm init -y
npm install express

Then I create server.js with the following JavaScript code.

const express = require("express")
const app = express()
app.get("/", function(req, res) {
    res.send("Hello World")
})
app.listen(3000)

Now if I run my code with node server.js, I should be able to go to http://localhost:3000/ and see the message "Hello World" in my browser.

Great! Now commit and push, and move onto the next thing right? Oh wait, we are deploying manually, we got some work to do.

The Server

We need a computer of some sort to run our code on the web. Especially since my laptop isn't online 24/7, and we wouldn't want anyone to not be able to access our greeting. I head to the AWS console.

Click on "EC2"
Click on "Launch Server"
Select "Amazon Linux 2 AMI"
Click "Review and Launch"
Click "Launch"
In dropdown select "Create a new key pair"
Enter "express_hello_world" as my "Key pair name"
Click "Download Key Pair"
Click "Launch Instances"

Whew! That was a few steps, most of which I didn't really take the time to evaluate the best options, but now we have some sort of "default" server to run our code on. However, we still need to install Node.js on the server, and upload our "Hello World" application to the server, and then make sure it is running.

Connecting to the Server

The key pair we downloaded was a "pem" file which is a private key. We can use this key to connect to the server via an SSH client. We also need the public IP address of our AWS server we created. From the "EC2 Dashboard" click "Instances" on the left sidebar, and find the column "IPv4 Public IP" which has the IP adress for the instance we just created.

Now we can connect to the server.

mkdir -p ~/Documents/AWS/keys/express_hello_world/
cd ~/Documents/AWS/keys/express_hello_world/
cp ~/Downloads/express_hello_world.pem ./
chmod 400 express_hello_world.pem
ssh -i "express_hello_world.pem" ec2-user@<PUBLIC_IP_ADDRESS_OF_INSTANCE>

You should now be at a bash prompt on your AWS EC2 instance.

Note: If you are using Windows, you should probably follow the instructions for Connecting to Your Linux Instance from Windows Using PuTTY. However, you can use "Windows Subsystem from Linux" (WSL). If you use WSL, make sure you put express_hello_world.pem in a WSL folder or chmod 400 express_hello_world.pem will not work.

Installing Node.js

From the Node.js home page the installation instructions for the yum package manager which is what our server instance using are as follows.

curl --silent --location https://rpm.nodesource.com/setup_8.x | sudo bash -
sudo yum -y install nodejs

You can type node --version to check if it is installed and you are on the correct version. The current LTS version was 8.11.4 when this article was written. Anything greater than that should work fine.

Creating a User

We will create a user for running our application. This prevents us from having to worry about what our application can do as root, or even potentially with sudo abilities as ec2-user.

sudo useradd express_hello_world

Copying Application to Server

Exit from our current ssh session by typing exit. Find the directory where we originally put our server.js and package.json files. We will copy that entire directory to the ec2-user home directory on our EC2 server instance.

After we accomplish that, we want to set the permissions so the owner is the express_hello_world user we just created, and move the application to the express_helo_world home directory.

rm -rf <EXPRESS_APP_DIRECTORY>/node_modules
scp -r -i "express_hello_world.pem" \
    <EXPRESS_APP_DIRECTORY> \
    ec2-user@<PUBLIC_IP_ADDRESS_OF_INSTANCE>:express_hello_world_app
ssh -i "express_hello_world.pem" ec2-user@<PUBLIC_IP_ADDRESS_OF_INSTANCE>
sudo chmod 600 ./express_hello_world_app/*
sudo chown -R express_hello_world:express_hello_world ./express_hello_world_app
sudo mv ./express_hello_world_app /home/express_hello_world/express_hello_world_app

Note: For Windows users it probably makes the most sense to use a program like FileZilla to copy the files to the server. Although with WSL you can use the scp method described here. Again make sure your private key is in a WSL directory with the correct permissions (400).

Checkpoint 1: Testing the Application Locally

It is a good time to do a sanity check. We should be able to run the application from the express_hello_world user, and use curl to get our "Hello World" message.

Become the express_hello_world user and install Express dependency. We also test here to make sure we get a "Connection Refused" since the server should not be running.

sudo su - express_hello_world
cd ./express_hello_world_app
npm install
curl localhost:3000  # Expected output "Connection Refused"

Now we should be able to start the server and get the "Hello World" response from it.

node server.js &
SERVER_PID=$!
curl localhost:3000  # Expected output "Hello World"

After we verified we get our "Hello World" response. Kill the server, and check that we are no longer accepting connections.

kill $SERVER_PID
curl localhost:3000  # Expected output "Connection Refused"

Finally type exit to return to the ec2-user user.

Exposing the Application to the World

Since we are running our application as a unprivledged user, we cannot host it on port 80 (or any port below 1024). However, we can create a firewall rule to forward that port to the port we are hosting on.

iptables -t nat -A PREROUTING -p tcp --dport 80 -j REDIRECT --to 3000

We also need to setup our AWS security rules to allow traffic on port 80. From the EC2 Dashboard. Click on Instaces in the left sidebar. Right click on the instance we created, go to "Networking", then "Change Security Groups". Look at the "Security Group ID" we currently have selected, and remember it. Click "Cancel".

On the left sidebar, click "Security Groups". Right click on the row with the "Security Group ID" we found in the last step. And click "Edit inbound rules". Click "Add Rule" and from the drop down, select "HTTP". Then click "Save".

Checkpoint 2: Testing the Application from the Web

In your browser try going to http://<PUBLIC_IP_ADDRESS_OF_INSTANCE>. You should get a "This site can't be reached" message. This is because we stopped running the server in the previous step.

Go ahead and launch the server again. Run the following commands.

sudo su - express_hello_world
cd ./express_hello_world_app
node server.js &
SERVER_PID=$!

Now if we go to the previous URL, we should get a "Hello World" message in the browser.

Awesome! We are now live!

To shutdown the server we can kill the application like we did last time.

kill $SERVER_PID
exit  # To exit the express_hello_world user prompt

Running the Server as a Daemon Process

We have a working server, but it would be much better if we didn't have the server process tied to our current SSH session. We can use the systemd process for doing this. It will also handle making sure the server is restarted if it fails, and automatically started when the server boots up.

Create a file express_hello_world.service in /etc/systemd/system/.

sudo vim /etc/systemd/system/express_hello_world.service

Add the following to that file. These are the settings for our daemon process. You can control things like the command we use to start the process with "ExecStart". the User/Group, what environment variables are set, and the current Working Directory.

[Unit]
Description=Express Hello World Application
After=network.target

[Service]
ExecStart=/usr/bin/node server.js
Restart=always
User=express_hello_world
Group=express_hello_world
Environment=PATH=/usr/bin:/usr/local/bin
Environment=NODE_ENV=production
WorkingDirectory=/home/express_hello_world/express_hello_world_app

[Install]
WantedBy=multi-user.target

To start our job, we use systemctl. We can use the status subcommand to check that status of our daemon process too.

sudo systemctl start express_hello_world.service
sudo systemctl status express_hello_world.service

Debugging

After running status, we should see "Active: active (running)". If you don't see that, something obviously went wrong. Use sudo journalctl --unit express_hello_world.service to examine the error log of the node service.js command.

One likely cause of the job failing is port 3000 is in use. This can happen when you forgot to kill the server in one of the checkpoints (This happened to me, haha). Do ps aux | grep node, and find the process that still is running, note it's PID, then do sudo kill <PID>. After this the daemon process should start without a problem.

Rebooting

Lastly to make sure the application starts when the server is booted, run the command

sudo systemctl enable express_hello_world.service

Persist iptables between reboots

If you reboot now, the iptable rule that we defined before will not be restored on boot. We can also create a systemd service that does that. Create the file /etc/systemd/service/express_hello_world_iptables.service and put the following systemd configuration.

[Unit]
Description=Forward port 80 to port 3000

[Service]
Type=oneshot
ExecStart=/bin/sh -c "iptables -t nat -A PREROUTING -p tcp --dport 80 -j REDIRECT --to 3000"
ExecStop=/bin/sh -c "iptables -t nat -D PREROUTING -p tcp --dport 80 -j REDIRECT --to 3000"
RemainAfterExit=yes

[Install]
WantedBy=multi-user.target

Then delete the rule we already created and start the service we just defined (this recreates the rule).

iptables -t nat -D PREROUTING -p tcp --dport 80 -j REDIRECT --to 3000
sudo systemctl start express_hello_world_iptables.service
sudo systemctl enable express_hello_world_iptables.service

Note: It feels like there is a better way to handle this iptables stuff, but I couldn't find anything that felt very minimal. This doesn't seem like something that should be very complex.

Checkpoint 3: Testing our Daemon Process

In your browser go to http://<PUBLIC_IP_ADDRESS_OF_INSTANCE> again. We should see the message "Hello World" if our last step succeeded.

Run the following command to stop our application.

sudo systemctl stop express_hello_world.service

And refresh the browser to make sure the server is no longer responding.

Now reboot the server.

sudo reboot

Wait for the server to come back up, and check the website. We should get the message "Hello World" again.

Assuming everything went smoothly, we now have a functional server hosting our nodejs application!

Reflections

This was a lot of steps to set everything up. However, most of it could easily be automated. Even provisioning the AWS service can be automated through the AWS Command Line Interface.

Re-Deploy

To deploy a new version of our app, we would just need to copy the files to our server, and restart the daemon. Another step that should be fairly easy to automate.

Disadvantages

The application runs exactly as we set it up. To change anything, we really have to know Linux very well. I can manage ok through Googling basic commands, but doing some simple things seem like that might be a pain, for example:

Set up HTTPS (On Heroku this is a one click setup pretty much)
Move to another service. (With something like docker this might be easier, a lot of what I did I think was specific to Amazon Linux 2).
Scaling. (Something like docker would make it easy to run more instances of my app).

iptables

My understanding is that iptables is deprecated, but as a novice to Linux system administration, it is unclear to me what the available replacements are. A lot of the information I could find refered to commands that just didn't exist on my system.

Next Steps

I might try to write some scripts that automates this process to see how hard it is to deploy different apps on different servers. I certainly have some hard coded values in my systemd that I would have to deal with.

All in all it was fun to get this up and running.