Halloween Sale: Flat 70% + Addl. 25% Off + 30 Days Extra Lab on all Courses | Use Coupon HS25 in Checkout | Offer Expires In

  Enroll Now

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

Loading comments...