How to import a 7M+ rows data set into PostgreSQL? (SQL for beginners)

Continue with my SQL tutorial vids: https://www.youtube.com/playlist?list=PLHS1p0ot3SViZMNfr2jVjAcutht0g3Ep7
Or with the articles: https://data36.com/sql-for-data-analysis-tutorial-beginners/

-----------------------------------------------
This is an extra video to my SQL for Data Analysis - episode 2 (about SQL WHERE clause) article. Full article here: https://data36.com/sql-where-clause-tutorial-beginners-ep2/

Or start with SQL for Data Analysis - episode 1:
https://data36.com/sql-for-data-analysis-tutorial-beginners/

Data Coding 101 - Install Python, R, SQL and bash: https://data36.com/data-coding-101-install-python-sql-r-bash/

How to install SQL Workbench for postgreSQL:
https://data36.com/install-sql-workbench-postgresql/

-----------------------------------------------
STEPS:
1. Open Terminal and login (ssh) to your dataserver!

2. Download the “flight delays” data!
wget 46.101.230.157/sql_tutorial/2007.csv.bz2

3. Set up dtrx! That’s a command line tool for unzipping stuff!
(Note: that this might have been already set up, if it’s so, skip this step!)
sudo apt-get install dtrx

4. Unzip the csv file!
dtrx 2007.csv.bz2
Note: It will take around ~60 seconds to process the whole file, so don’t worry, your Terminal is not freezing, it just needs some time.

5. Format your data!
cat 2007.csv |cut -d',' -f1,2,3,4,5,7,10,11,14,15,16,17,18,19 | grep -v ',NA' (angle_bracket) sql_ready.csv

6. Now we have to give permission to our postgreSQL user to create tables and load data into them. This will need multiple steps. Here’s a gif first (note: my username is “dataguy” - your’s might be something

6.1. First sudo to the user called “postgres”:
sudo -u postgres -i

6.2. Then start postgreSQL:
psql

6.3. The prompt will change to this one postgres=#! Type:
alter user [your_user_name] superuser;

6.4. This turns your original user into a super user! Go back to your user!
Exit from postgreSQL:
\q
Then exit from the user called “postgres”:
exit

6.5. Finally access your original user’s postgreSQL database from the command line:
psql -d postgres

7. Now all you need to do is create the table by simply copy-pasting this lines into your terminal:

CREATE TABLE flight_delays (
year INTEGER,
month INTEGER,
dayofmonth INTEGER,
dayofweek INTEGER,
deptime INTEGER,
arrtime INTEGER,
flightnum INTEGER,
tailnum VARCHAR,
airtime INTEGER,
arrdelay INTEGER,
depdelay INTEGER,
origin VARCHAR,
dest VARCHAR,
distance INTEGER);

8. And finally copy the data from the csv file you have just downloaded!
COPY flight_delays FROM '/home/[USERNAME]/sql_ready.csv' DELIMITER ',' CSV HEADER;

9. Go back to SQL Workbench and make a simple SELECT statement… but make sure, that you use the LIMIT clause too. Now you have over 7.000.000 rows of data - even though postgreSQL can handle it easily, your computer might be frozen, if you try print all that data on your screen. So try something like this first:
SELECT * FROM test_test LIMIT 10;

-----------------------------------------------
Check my website: https://data36.com
For more similar content, subscribe to my newsletter: https://data36.com/newsletter-subscription/
Find me on Twitter: https://twitter.com/data36_com