Loading CSV data files into MySQL 8 – manual method

In my earlier MySQL 8 post here, I detailed the process for creating a MySQL 8 test environment.  If you have the MySQL 8 environment or some other newly-created MySQL 8 environment, you probably need to create some data in it now.  I wanted to load a decent amount of table data and aside from creating a simulated data set, loading a CSV file seemed to be a convenient method.  I chose to load a census data file in CSV format  from https://data.census.gov/cedsci/.  This data if freely available, sizeable, and lends itself well to analytical queries later on.

Reviewing the process to load the data, many of you will notice the similarities between this process and the Oracle database external file loading process.  However, there is a gotcha that I encountered in MySQL that I will outline here.

First, you need to review your CSV data file to design and create a target table in MySQL.  I won’t cover the basics of starting the mysqld service or logging into the MySQL database, but will only relay the commands I used to created my target table:

CREATE TABLE census (id INT);
ALTER TABLE census ADD COLUMN Year INT;
ALTER TABLE census ADD COLUMN Age INT;
ALTER TABLE census ADD COLUMN Ethic INT;
ALTER TABLE census ADD COLUMN Sex VARCHAR(10);
ALTER TABLE census ADD COLUMN Area VARCHAR(10);

After creating the target table, I copied the CSV data file to a location on my MySQL database server and ensured that it was readable by all parties.

I then tried to load the CSV data file, but encountered the gotcha.

mysql> LOAD DATA LOCAL INFILE '/home/jonadams/Data8277.csv' INTO TABLE census FIELDS TERMINATED BY ',' ENCLOSED BY '"' IGNORE 1 ROWS;
ERROR 3948 (42000): Loading local data is disabled; this must be enabled on both the client and server sides

Through trial and error, I found this setting is the default and that this next process was the easiest method to accomplish changing this setting:

  • Make a backup of the MySQL configuration file.  In my case (RHEL7 and MySQL 8 Community Edition, this file is /etc/my.cnf).  I recommend this step before editing any configuration file.
  • Add local_infile = 1 to the [mysqld] section of the /etc/my.cnf file and restart the mysqld service.
  • Log into mysql and execute the following command to verify the changed setting.
 MySQL localhost:33060+ ssl sakila SQL > SHOW GLOBAL VARIABLES LIKE 'local_infile';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| local_infile | ON |
+---------------+-------+
1 row in set (0.0027 sec)
  • Now the kicker.  If you logged into MySQL using mysqlsh, you need to exit and login using mysql because you will need to use a command line argument that is not accepted by mysqlsh.
  • Here is the log of my session including the load command and results.  Keep in mind that you only need to use the IGNORE 1 ROWS clause if there is a header line in the CSV data file.
root@localhost ~]# mysql -u root -p --local_infile=1
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.19 MySQL Community Server - GPL

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use sakila;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> LOAD DATA LOCAL INFILE '/home/jonadams/Data8277.csv' INTO TABLE census FIELDS TERMINATED BY ',' ENCLOSED BY '"' IGNORE 1 ROWS;

Query OK, 34959672 rows affected (2 min 59.56 sec)
Records: 34959672  Deleted: 0  Skipped: 0  Warnings: 0

If the results of the load process show warnings or errors, you can use the SHOW WARNINGS or SHOW ERRORS command to display the warning or errors messages.  Use the limit option to keep the number of warnings at a manageable level if there are hundreds or thousands.

mysql> show warnings limit 100;

You may be concerned that you are changing the default settings of MySQL that prevent the loading of local data files, but this process only works if you alter the default settings in the configuration file AND use the local_infile settings when logging into MySQL.

If you prefer a graphical method for this process, you can also load data into tables using the MySQL Workbench.   The workbench includes many useful options including performance monitoring, import and export of data, querying and script execution, and data modeling.

There is also a graphical interface for loading data files into MySQL called phpMyAdmin which is browser-based and requires the installation and configuration of PHP and HTTPD (Apache).

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s