Artificial Intelligence (AI) & Machine Learning Course for Managers

 Enroll Now

SQL Tutorial

58 / 59
   

Database Import

Importing Data with LOAD DATA

  • LOAD DATA is used to import the data from the file created using the export method of SELECT ... INTO OUTFILE statement or using method of mysqldump .

  • Target tables should be pre-created.

Example:

LOAD DATA LOCAL INFILE 'emp_dump.txt' INTO TABLE table1;

LOCAL -> This keyword means dump file will be searched for on current client server. If this LOCAL keyword is omitted, then dump file will be searched for on MySQL host server.

  • Default expected format of input file -> Lines terminated by linefeeds (newlines) and data values within a line separated by tabs.

  • Custom options can be given according to the format of file.

For example, if the file contains values separated by colons and lines terminated by carriage returns and newline character, then use below command.

LOAD DATA LOCAL INFILE 'emp_dump.txt' INTO TABLE emp FIELDS TERMINATED BY ':' LINES TERMINATED BY '\r\n';
  • LOAD DATA expects that columns order in the file is same as in target table.
  • If the order is not same, the correct list can be specified in the command.
  • If your table has columns a, b, and c, but successive columns in the data file correspond to columns b, c, and a.

Example:

LOAD DATA LOCAL INFILE 'emp_dump.txt' INTO TABLE mytbl (b, c, a);

Importing Data with mysqlimport

  • mysqlimport is a wrapper around LOAD DATA.

  • Target tables should be pre-created.

Example:

mysqlimport -u sqoopuser -p --local db_name dump_emp.txt

You can also specify format.

mysqlimport -u sqoopuser -p --local --fields-terminated-by = ":" --lines-terminated-by = "\r\n"  db_name emp_dump.txt

You can specify columns order too here.

mysqlimport -u sqoopuser -p --local --columns=b,c,a db_name dump.txt

Importing Data with mysql

If the file has table create and insert statements, then use below.

mysql -u sqoopuser -p < dump.txt