MySQL Setup for Data Engineering Learning

Till now, we were solving all the problems using PostgreSQL but going forward we will be also using MySQL. So here we will be setting up our system for the same.

As part of Data Engineering Suite, we have already installed and configured MySQL. (If you have not use below video or follow blog)

Open MySQL Workbench by passing MySQL server details.

First, we will create one schema for our use.

CREATE SCHEMA DATAENG;

Use table creation script and create table.

USE DATAENG;
CREATE TABLE `transaction` (
  `id` int NOT NULL,
  `user_id` int DEFAULT NULL,
  `item` varchar(45) DEFAULT NULL,
  `created_at` date DEFAULT NULL,
  `revenue` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

We have a table created which we can see under Schema and table node.

In the next step, we will load data into the table.

For that right click on table -> select Table Data Import Wizard

On that wizard, it will give the option to select csv file, we will select file for our problem.

Click on next and now select table in which we want to load data.

On next screen check mapping of columns from file and SQL table is correct, if not from drop down change it and click on next.

Once you click on confirm, it will start importing data into the table. And also show below logs.

Now, we will go and check the table for data. Write below query for that.

SELECT * FROM transaction;

In each MySQL problem solving we use this for creating tables and importing data before solving problems.

Conclusion

We learned below topics

• How to create SQL Schema
• How to create SQL Table using SQL Script
• How to import data into SQL from CSV file (or flat file)
• How to display data from SQL

Video Explanation