Data Loading Utilities

Let us understand how we can load the data into databases using utilities provided.

• Most of the databases provide data loading utilities.

• One of the most common way of getting data into database tables is by using data loading utilities provided by the underlying datatabase technology.

• We can load delimited files into database using these utilities.

• Here are the steps we can follow to load the delimited data into the table.

  • Make sure files are available on the server from which we are trying to load.

  • Ensure the database and table are created for the data to be loaded.

  • Run relevant command to load the data into the table.

  • Make sure to validate by running queries.

• Let us see a demo by loading a sample file into the table in Postgres database.

user_first_name,user_last_name,user_email_id,user_role,created_dt
Gordan,Bradock,gbradock0@barnesandnoble.com,A,2020-01-10
Tobe,Lyness,tlyness1@paginegialle.it,U,2020-02-10
Addie,Mesias,amesias2@twitpic.com,U,2020-03-05
Corene,Kohrsen,ckohrsen3@buzzfeed.com,U,2020-04-15
Darill,Halsall,dhalsall4@intel.com,U,2020-10-10

psql -U itversity_sms_user 
  -h localhost 
  -p 5432 
  -d itversity_sms_db 
  -W

CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    user_first_name VARCHAR(30) NOT NULL,
    user_last_name VARCHAR(30) NOT NULL,
    user_email_id VARCHAR(50) NOT NULL,
    user_email_validated BOOLEAN DEFAULT FALSE,
    user_password VARCHAR(200),
    user_role VARCHAR(1) NOT NULL DEFAULT 'U', --U and A
    is_active BOOLEAN DEFAULT FALSE,
    created_dt DATE DEFAULT CURRENT_DATE
);

COPY users(user_first_name, user_last_name, 
    user_email_id, user_role, created_dt
) FROM '/data/sms_db/users.csv'
DELIMITER ','
CSV HEADER;

SELECT * FROM users;