SQL Tutorial

51 / 68
Previous Index Next  

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, then you need to specify the column order.

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

No hints are availble for this assesment

Answer is not availble for this assesment

Previous Index Next  
Please login to comment

3 Comments

Saswat Ray
 4 years ago 

Hi,

It would be better to make a video and shows couple of operations.

 1  Upvote    Share
Shikha Sharma
 5 years ago 

How I can import .xml file on my desktop.To mysql tables? These files are very large. Contains one field which I am not getting. What datatype can be used?

CreationTime has value = 2011-09-12 T21:14:50:325

I have created my folder in Hue file browser and uploaded all xml files. These files are in /user/myuid/ ... i.e in HDFS.
Are they uploaded in HDFS or I need to use copyFromLocal as well. After this how to upload these xml into mysql.

  Upvote    Share
CloudxLab
 5 years ago 

Hello Disqus,

Thanks for contacting CloudxLab!

This automatic reply is just to let you know that we received your message and we’ll get back to you with a response as quickly as possible. During business hours (9am-5pm IST, Monday-Friday) we do our best to reply within a few hours. Evenings and weekends may take us a little bit longer.

If you have a general question about using CloudxLab, you’re welcome to browse our below Knowledge Base for walkthroughs of all of our features and answers to frequently asked questions.

- Tech FAQ <https: cloudxlab.com="" faq="" support="">
- General FAQ <https: cloudxlab.com="" faq=""/>

If you have any additional information that you think will help us to assist you, please feel free to reply to this email. We look forward to chatting soon!

Cheers,
The CloudxLab Team

  Upvote    Share