Skip to content

Latest commit

 

History

History
71 lines (62 loc) · 4.46 KB

README.md

File metadata and controls

71 lines (62 loc) · 4.46 KB

Zero to Hero: DATAQUEST's Become a Data Engineer

Here's how to get DataQuest's Data Engineering Track missions' content to work on your localhost. Using data from my Valenbisi ARIMA modeling project, I will walk through steps using PostgreSQL, Postico, and the Command Line to get our DataQuest exercises running out of a Jupyter Notebook.

This will not be a repeat of the many resources I used, so be sure to look out for any links I include if it seems I've skipped a few steps.

Getting started with PostgreSQL and Postico:

PostgreSQL download
Postico download

1. Configure Postgres
I found this source incredibly helpful.
It walks through installation, creating users, and connecting to a local database.

This Repository will be covering how to do almost all the exercises out of a Jupyter Notebook. However, examples of how to complete some exercises in the CL will also be covered. To access the CLI, where you can create users, manage permissions, and create your first table: click the database created, "valenbisi2018", for this example.


Each line will start with whatever you named your database, so for me it's valenbisi2018#=

Here are the points I found challenging, so they are documented below.
1a. How to fill a database with a csv file:
First create the database:

valenbisi2018#= CREATE TABLE vbstatic (id BIGSERIAL PRIMARY KEY, update VARCHAR(255), available INT, 
                                       free INT, total INT, name VARCHAR(255), long NUMERIC, lat NUMERIC);

Notice I made column update into data type VARCHAR. This is because when working with CSVs, DateTime Objects sometimes get converted to strings. Postgres cannot handle data type misgivings, so it was simplest to do this. Here is a guide to all the different Postgres data types you can encounter.

Then fill the database with data from a csv file containing only the columns you created in your table.

valenbisi2018#= \copy vbstatic(id,update,available,free,name,long,lat,total) 
FROM '~/Documents/Repos/data_quest_data_eng/postgres_mission/vb_table.csv' 
DELIMITER ',' 
CSV HEADER

Note that I use \copy, not COPY

The syntax for \COPY is slightly different: (a) being a psql command, it is not terminated by a semicolon (b) file paths are relative the current working directory.

Source: One of the answers to this StackOverflow Question, which linked to here.

1b. How to give permissions to your user [vbuser]

valenbisi2018#= GRANT SELECT
ON ALL TABLES IN SCHEMA public
TO vbuser;

Source: How to Grant Permissions in PostgreSQL

Alright, now you're ready to follow along in my first Jupyter Notebook, 01_postgres_mission
Some additional notes to keep in mind:

  1. Make sure when you are loading in your data using a csv, that all the columns in the csv are in the same order as in your CREATE TABLE statement
  2. If you need to delete a table, enter your Postgres CLI and type:
valenbisi2018#= DROP TABLE table_name;
  1. To see a description of tables, type into the CLI: \dt, short for 'describe tables'

Optimizing Your Postgres Database

For Non-Commercial Use Only

I highly reccommend participating in this course as a member of DATAQUEST. A summary of the curriculum is outlined below.

  • Postgres for Data Engineers
  • Optimizing Postgres Databases
  • Processing Large Datasets in Pandas
  • Optimizing Code performance on Large Datasets
  • Algorithms and Data Structures
  • Recursion Trees
  • Building a Data Pipeline