Support Questions

Find answers, ask questions, and share your expertise

tutorial exercise 2: no tables

avatar
New Contributor

I do not see any tables when querying in Impala Editor, it says this operation has no results, and I've seen somebody has this same issue, I tried all the suggestions still the same and it doesn't seem the tables are in the Hive either.

 

Thanks

1 ACCEPTED SOLUTION

avatar
Guru
So Sqoop is trying to do things, that might seem surprising if you're new
to Hadoop: first, it's going to copy files containing the raw data to
/user/hive/warehouse/, and then second, it's going to execute a
CREATE TABLE (similar to what you may have used in SQL databases before)
with Hive to recreate the metadata that goes with those files.

In the output you showed me, it says the /user/hive/warehouse/
directory for the categories table already exists, and it's not expecting
it to. It seems that a previous run has failed for a different reason, and
we should clean it up before trying again.

To get rid of the raw data files, run: 'sudo -u hdfs hadoop fs -rm -r
/user/hive/warehouse/\*' (I'm assuming you don't have any other data in the
cluster you care about).

To get rid of the metadata, start the impala shell with 'impala-shell' (you
don't need any other arguments because you're on the Quickstart VM and the
defaults all happen to be correct). Run 'invalidate metadata;', and then
'show tables;'. For any tables you see, run 'drop table ;'.
Then rerun the Sqoop job and it *should* succeed, but if it doesn't the
output should give us the root cause of the real problem...

View solution in original post

4 REPLIES 4

avatar
Guru
Can you check the output of Sqoop and any CREATE TABLE commands (depending
on the version of the tutorial you're working from) for errors? Seems to me
one of them has failed, but there's no way to know what or why without more
information.

avatar
New Contributor

Hi,

 

When I ran the command this is what it showed on the screen

 

[cloudera@quickstart ~]$ sqoop import-all-tables -m 1 --connect jdbc:mysql://quickstart:3306/retail_db --username=retail_dba --password=cloudera --compression-codec=snappy --as-avrodatafile --warehouse-dir=/user/hive/warehouse
Warning: /usr/lib/sqoop/../accumulo does not exist! Accumulo imports will fail.
Please set $ACCUMULO_HOME to the root of your Accumulo installation.
15/10/26 14:53:41 INFO sqoop.Sqoop: Running Sqoop version: 1.4.5-cdh5.4.2
15/10/26 14:53:41 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead.
15/10/26 14:53:42 INFO manager.MySQLManager: Preparing to use a MySQL streaming resultset.
15/10/26 14:53:43 INFO tool.CodeGenTool: Beginning code generation
15/10/26 14:53:43 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM `categories` AS t LIMIT 1
15/10/26 14:53:43 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM `categories` AS t LIMIT 1
15/10/26 14:53:43 INFO orm.CompilationManager: HADOOP_MAPRED_HOME is /usr/lib/hadoop-mapreduce
Note: /tmp/sqoop-cloudera/compile/ae6e95d629f4cf392cd8ae9f44d1c7d3/categories.java uses or overrides a deprecated API.
Note: Recompile with -Xlint:deprecation for details.
15/10/26 14:53:48 INFO orm.CompilationManager: Writing jar file: /tmp/sqoop-cloudera/compile/ae6e95d629f4cf392cd8ae9f44d1c7d3/categories.jar
15/10/26 14:53:48 WARN manager.MySQLManager: It looks like you are importing from mysql.
15/10/26 14:53:48 WARN manager.MySQLManager: This transfer can be faster! Use the --direct
15/10/26 14:53:48 WARN manager.MySQLManager: option to exercise a MySQL-specific fast path.
15/10/26 14:53:48 INFO manager.MySQLManager: Setting zero DATETIME behavior to convertToNull (mysql)
15/10/26 14:53:48 INFO mapreduce.ImportJobBase: Beginning import of categories
15/10/26 14:53:48 INFO Configuration.deprecation: mapred.job.tracker is deprecated. Instead, use mapreduce.jobtracker.address
15/10/26 14:53:49 INFO Configuration.deprecation: mapred.jar is deprecated. Instead, use mapreduce.job.jar
15/10/26 14:53:52 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM `categories` AS t LIMIT 1
15/10/26 14:53:52 INFO mapreduce.DataDrivenImportJob: Writing Avro schema file: /tmp/sqoop-cloudera/compile/ae6e95d629f4cf392cd8ae9f44d1c7d3/sqoop_import_categories.avsc
15/10/26 14:53:52 INFO Configuration.deprecation: mapred.map.tasks is deprecated. Instead, use mapreduce.job.maps
15/10/26 14:53:52 INFO client.RMProxy: Connecting to ResourceManager at /0.0.0.0:8032
15/10/26 14:53:53 WARN security.UserGroupInformation: PriviledgedActionException as:cloudera (auth:SIMPLE) cause:org.apache.hadoop.mapred.FileAlreadyExistsException: Output directory hdfs://quickstart.cloudera:8020/user/hive/warehouse/categories already exists
15/10/26 14:53:53 ERROR tool.ImportAllTablesTool: Encountered IOException running import job: org.apache.hadoop.mapred.FileAlreadyExistsException: Output directory hdfs://quickstart.cloudera:8020/user/hive/warehouse/categories already exists
[cloudera@quickstart ~]$
[cloudera@quickstart ~]$

 

By the way I just started learning Hadoop, so I don't know much of anything, but this is what it showed on the terminal when I ran the 1st time and also when I tried to run again. Hope you have some answer.

Thanks

avatar
Guru
So Sqoop is trying to do things, that might seem surprising if you're new
to Hadoop: first, it's going to copy files containing the raw data to
/user/hive/warehouse/, and then second, it's going to execute a
CREATE TABLE (similar to what you may have used in SQL databases before)
with Hive to recreate the metadata that goes with those files.

In the output you showed me, it says the /user/hive/warehouse/
directory for the categories table already exists, and it's not expecting
it to. It seems that a previous run has failed for a different reason, and
we should clean it up before trying again.

To get rid of the raw data files, run: 'sudo -u hdfs hadoop fs -rm -r
/user/hive/warehouse/\*' (I'm assuming you don't have any other data in the
cluster you care about).

To get rid of the metadata, start the impala shell with 'impala-shell' (you
don't need any other arguments because you're on the Quickstart VM and the
defaults all happen to be correct). Run 'invalidate metadata;', and then
'show tables;'. For any tables you see, run 'drop table ;'.
Then rerun the Sqoop job and it *should* succeed, but if it doesn't the
output should give us the root cause of the real problem...

avatar
New Contributor

Hello,

 

So I have cleared everything as you said and ran again and it worked fine, may be it must have worked even for the 1st time as the 1st time I have forgotten to create the tables in Impala before quering to show the tables. But I learned something from this.

 

Thanks for helping