Solutions – Loading Data into Postgres Tables

Getting Started¶

Here is the exercise related to getting started module.

Exercise – Loading Data¶

As part of this exercise, you need to take care of loading data using COPY Command.

• You can connect to the database using following details in the environment provided by us.
  • Host: localhost
  • Port: 5342
  • Database Name: YOUR_OS_USER_hr_db
  • User Name: YOUR_OS_USER_hr_user
  • Password: YOUR_OS_USER_PASSWORD (provided by us).
• If you are using your own environment, make sure to create database for storing HR Data.
  • Database Name: hr_db
  • User Name: hr_user
  • You can create user with password of your choice.

CREATE DATABASE hr_db;
CREATE USER hr_user WITH PASSWORD 'hr_password';
GRANT ALL ON DATABASE hr_db TO hr_user;
GRANT pg_read_server_files TO hr_user;

• Create table using this script.

CREATE TABLE employees
   ( employee_id INTEGER
   , first_name VARCHAR(20)
   , last_name VARCHAR(25)
   , email VARCHAR(25)
   , phone_number VARCHAR(20)
   , hire_date DATE
   , job_id VARCHAR(10)
   , salary NUMERIC(8,2)
   , commission_pct NUMERIC(2,2)
   , manager_id INTEGER
   , department_id INTEGER
   ) ;
CREATE UNIQUE INDEX emp_emp_id_pk
         ON employees (employee_id) ;
ALTER TABLE employees ADD
   PRIMARY KEY (employee_id);

• Understand data.
  • Check for delimiters (record as well as field).
  • Check whether header exists or not.
  • Ensure number of fields for the table and data being loaded are same or not.
• Load data into the table using COPY Command.
• Validate by running these queries. You can also use SQL Workbench to run the queries to validate whether data is loaded successfully or not.

SELECT * FROM employees LIMIT 10;
SELECT count(1) FROM employees;

Solution¶

Here is the solution for the exercise.

• Connect to psql CLI.

  psql -h localhost \
    -p 5432 \
    -d itversity_hr_db \
    -U itversity_hr_user \
    -W
  

• Make sure table is created using the script provided as part of the question.

TRUNCATE TABLE employees;

COPY employees(employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct,
manager_id, department_id
) FROM '/data/hr_db/employees/part-m-00000'
DELIMITER E'\t'
CSV;