Working with MySQL/MariaDB

1. Install using the official repo:

sudo yum install -y mariadb-server

sudo systemctl start mariadb

sudo systemctl enable mariadb

sudo mysql_secure_installation

1. Log in to the server:

mysql -u root -p

2. Then, create the database and the table:

create database city_data;

use city_data

create table city_info(
LatD int,
LatM int,
LatS int,
NS char,
LonD int,
LonM int,
LonS int,
EW char,
City varchar(50),
State varchar(50)
) ;

1. Copy the cities.csv file into the mysql user's home directory:

sudo cp ./data/cities.csv /var/lib/mysql

2. Perform the bulk insert:

LOAD DATA INFILE '/var/lib/mysql/cities.csv' INTO table city_info Fields terminated by ',' ENclosed by '"' Lines terminated by '\n' Ignore 1 ROWS;

3. Validate the data:

select count(city) from city_info where state='KS';