Pre-requisites

Movies Dataset: Download the dataset from Kaggle: Kaggle Movies Dataset.

MySQL Installed and Running on macOS: Ensure MySQL is installed on your Mac.

Terminal Access: We’ll use the macOS terminal (zsh) for commands.

Step 1: Create the movies_db Database

CREATE DATABASE movies_db;

Step 2: Create the movies Table

After creating the database, define a table structure to store the movies data.

USE movies_db;

CREATE TABLE movies(
name VARCHAR(255),
rating VARCHAR(255),
genre VARCHAR(255),
year INT,
released VARCHAR(255),
score FLOAT,
votes FLOAT,
director VARCHAR(255),
writer VARCHAR(255),
star VARCHAR(255),
country VARCHAR(255),
budget FLOAT,
gross FLOAT,
company VARCHAR(255),
runtime FLOAT
)

Step 3: Enable and Use LOAD DATA LOCAL INFILE

Start MySQL with Local Infile Option

mysql -u root -p --local-infile

Execute Commands to Import Data

At the SQL Query Editor in Azure Data Studio , execute the following commands step by step:

command 1:
USE movies_db;

command 2:
SHOW VARIABLES LIKE ‘local_infile’; # By default, it will be OFF

command 3:
SET GLOBAL local_infile = 1;

command 4:
SHOW VARIABLES LIKE ‘local_infile’;

At the SQL prompt via zsh shell , execute the following commands step by step:

command 1:
USE movies_db;

command 2:
LOAD DATA LOCAL INFILE ‘/Users/vamsi/Downloads/movies.csv’
INTO TABLE movies
FIELDS TERMINATED BY ‘,’
ENCLOSED BY ‘”‘
LINES TERMINATED BY ‘\n’
IGNORE 1 ROWS;

Step 4: Verify the Data Import

SELECT COUNT(*) FROM movies_db.movies;

Conclusion

Congratulations! You’ve successfully imported the Kaggle movies dataset into your MySQL database on macOS. You can now query and analyze the data directly within MySQL. Whether you’re a data enthusiast or a budding data engineer, mastering this workflow will make you more efficient in handling real-world datasets.

For more such guides, stay tuned to manyamdatalabs.com!