Inserting data from csv to a mysql database table

[#1]

Hi,

Hoping someone can help.

I am trying to insert data from a csv file stored on the sandbox (within /root) to a mysql database table. Below I have bullet pointed the steps I have taken and the error I am getting.

- I have a file called countrycodes.csv stored in the /root folder of the sandbox. This contains the data that I want to insert into the database.

- I have created a mysql database called forHadoop.

- I have granted privileges to user hue by:

Grant all privileges on forHadoop.* to hue@localhost identified by '1111';

- I can log into this database as hue by typing:

mysql -u hue -p

Then type the password 1111 and I am in.

- I have created a table called countrycodes within the database forHadoop.

- I want to populate the table countrycodes with the data from /root/countrycodes.csv. To do this I type:

mysql forHadoop --local_infile=1 -u hue -p

Then type my password 1111

- But when I type the below load statement I get an error message.

LOAD DATA LOCAL INFILE ‘/root/countrycodes.csv’ into table forHadoop.countrycodes fields terminated by ‘,’ LINES TERMINATED BY ‘\r\n’;

- The error message is: ERROR 1044 (42000): Access denied for user 'hue'@'localhost' to database 'n’;'

Any idea as to what I am doing wrong? Why is access denied fro user hue?

[#2 · Accepted solution]

Your user may not have the privilege

This is what you could do

a) Login as mysql using root

b) change database to forHadoop

c) Run your Load Script.

I have a tutorial for Atlas, where I create 2 mysql tables in a newly created DB instance. Take a look

https://github.com/shivajid/atlas/tree/master/tutorial

Thanks

[#3]

Thanks Shivaji, I managed to get it working...