MySQL Databases and tables in the lab

Here is the information about mysql database

Hostname: nn01.itversity.com
Username: retail_dba
Password; itversity

Available databases
retail_db - read only
retail_import
retail_export

Here is the syntax to connect from gw01.itversity.com mysql -u retail_dba -h nn01.itversity.com -p
It will prompt you for the password.

Feel free to create tables and insert data to explore sqoop import and export. If there are any issues, please respond to this topic.

1 Like

Hi,

I am trying to create table in retail_db using ID retail_dba however it is giving me access error.

mysql> create table order_item_export as select * from order_item where 1 = 2;

ERROR 1142 (42000): INSERT, CREATE command denied to user ‘retail_dba’@‘gw01.itversity.com’ for table ‘order_item_export’.

Can you please help me on this.

  1. How to check access level
  2. How we can create users
  3. How we can modify access level of user.

Thank you,
Raghuvirsinha Jadeja

@Raghuvirsinha_Jadeja - Please use the database "retail_export " to create the tables and import the data.

“retail_db” database we are using for import purpose, which will have only read access.

There are 3 databases - retail_db for read only purpose, retail_import to create your own tables and import data using sqoop import, retail_export to export data using sqoop export.

1 Like

I was trying to do sqoop export by exporting my hive table to mysql.In order to do so , I created a table called retail_export.cat_sujith and ran the below sqoop command. However it fails because the username and password for this belongs to retail_dba and does not seem to be able to connect to sqoop_export.

The command fails with the error Table ‘retail_db.retail_export.cat_sujith’ doesn’t exist

sqoop export --connect “jdbc:mysql://nn01.itversity.com:3306/retail_db” --username=retail_dba --password=itversity --table retail_export.cat_sujith --export-dir=/apps/hive/warehouse/sujith_hive_import.db/categories -m 2 --batch

Subsequently i removed the database name retail_export from the sqoop command and it failed with the error Table ‘retail_db.cat_sujith’ doesn’t exist.

mysql> select * from retail_export.cat_sujith;
Empty set (0.00 sec)

As you can see above ,the table retail_export.cat_sujith does exist , show I be using a different username password to be able to connect to the retail_export.cat_sujith and export the data

Please advice as to how to get the sqoop export to recognize the schema and table

JDBC url is incorrect. It should be retail_export not retail_db.

Hi Team,

I tried connecting to mysql but it throws me an error, please help me on this one.

@Sudheer_228

mysql -u retail_dba -h nn01.itversity.com -p
enter password “itversity”

Its throwing me an error :

[shyamlesh@gw01 ~]$ mysql -u retail_dba -h gw01.itversity.com:8080 -p
Enter password: itverisity

ERROR 2005 (HY000): Unknown MySQL server host ‘gw01.itversity.com:8080’ (0)

ok got it only mysql -u retail_dba -h nn01.itversity.com -p worked fine

thanks

Not able to login mySql using retail_export and with password itversity or YES
mysql -u retail_export -h nn01.itversity.com -p
Enter password:
ERROR 1045 (28000): Access denied for user ‘retail_export’@‘gw01.itversity.com’ (using password: YES)

Any one looking at this issue, 2+ hours past…

@deepak_kotla - You have to use retail_dba as user. Refer earlier in the same post.

After login
show databases;
use retail_export;

Not able to export the data from HDFS to mySQL table
below is the command i have used…
sqoop export
–connect jdbc:mysql://nn01.itversity.com:3306/retail_export
–username retail_dba
–password itversity
–table youtube_mostviewed
–export-dir /user/deepak_kotla/op11
-m 4

========== getting below error ===========
17/07/17 14:07:30 INFO mapreduce.Job: Job job_1500292512979_0106 failed with state FAILED due to: Task failed task_1500292512979_0106_m_000002
Job failed as tasks failed. failedMaps:1 failedReduces:0
17/07/17 14:07:30 INFO mapreduce.Job: Counters: 12
Job Counters
Failed map tasks=1
Killed map tasks=3
Launched map tasks=4
Data-local map tasks=4
Total time spent by all maps in occupied slots (ms)=36204
Total time spent by all reduces in occupied slots (ms)=0
Total time spent by all map tasks (ms)=18102
Total vcore-milliseconds taken by all map tasks=18102
Total megabyte-milliseconds taken by all map tasks=27804672
Map-Reduce Framework
CPU time spent (ms)=0
Physical memory (bytes) snapshot=0
Virtual memory (bytes) snapshot=0
17/07/17 14:07:30 WARN mapreduce.Counters: Group FileSystemCounters is deprecated. Use org.apache.hadoop.mapreduce.FileSystemCounter instead
17/07/17 14:07:30 INFO mapreduce.ExportJobBase: Transferred 0 bytes in 20.132 seconds (0 bytes/sec)
17/07/17 14:07:30 INFO mapreduce.ExportJobBase: Exported 0 records.
17/07/17 14:07:30 ERROR mapreduce.ExportJobBase: Export job failed!
17/07/17 14:07:30 ERROR tool.ExportTool: Error during export: Export job failed!

Thanks,
Deepak

Any update on this issue, it is almost 3+ Hours.

Thanks,
Deepak

@gnanaprakasam - Can you try adding --input-fields-terminated-by ‘\001’ / check the table what delimited used during import.

sqoop export
–connect jdbc:mysql://nn01.itversity.com:3306/retail_export
–username retail_dba
–password itversity
–staging-table youtube_mostviewed_stg
–clear-staging-table
–table youtube_mostviewed
–export-dir /user/deepak_kotla/op11/part-r-00000
–fields-terminated-by ‘\001’
-m 4

still same error…
I’m trying to export the data from HDFS to mySQL table as the command says…

Please reply at the earliest… why this error is coming…

@deepak_kotla - After previewing your data, it’s “tab” delimited file

hadoop fs -cat /user/deepak_kotla/op11/part-r-00000

You have to use --input-fields-terminated-by for export, only during import we will use fields-terminated-by

For your reference.
sqoop export
–connect jdbc:mysql://nn01.itversity.com:3306/retail_export
–username retail_dba
–password itversity
–staging-table youtube_mostviewed_stg
–clear-staging-table
–table youtube_mostviewed
–export-dir /user/deepak_kotla/op11/part-r-00000
–input-fields-terminated-by ‘\t’
-m 4

After import into retail_export database

mysql> select * from youtube_mostviewed limit 10;
±------------±---------+
| video_id | view_cnt |
±------------±---------+
| -08YZF87OBQ | 11564 |
| -17Zbw0e6zs | 2198 |
| -2kiDt7BwvQ | 46965 |
| -55AA3zfoRk | 3877 |
| -5vW9TUwiVo | 1365 |
| -5wGZumPdbs | 9434 |
| -6M41YqM_xk | 2817 |
| -8BwggJEPZQ | 4956 |
| -ab5GQpaAWA | 6205 |
| -AdZ6wRHsrE | 1349 |
±------------±---------+
10 rows in set (0.00 sec)