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.
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
COPYcommand 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,email@example.com,A,2020-01-10 Tobe,Lyness,firstname.lastname@example.org,U,2020-02-10 Addie,Mesias,email@example.com,U,2020-03-05 Corene,Kohrsen,firstname.lastname@example.org,U,2020-04-15 Darill,Halsall,email@example.com,U,2020-10-10
- Connect to Database.
psql -U itversity_sms_user \ -h localhost \ -p 5432 \ -d itversity_sms_db \ -W
- Create the
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;
In [ ]: