Contents

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.

Loading Data

We can use COPY Command using psql to copy the data into the table.

  • Make sure database is created along with the user with right permissions. Also the user who want to use COPY command need to have pg_read_server_files role assigned.

  • Create the file with sample data. In this case data is added to users.csv under /data/sms_db

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

  • Connect to Database.

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

  • Create the users table.

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
);

  • Use copy command to load the data

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

  • Validate by running queries

SELECT * FROM users;
SQL Workbench Features Loading Data - Docker