Table not getting created using sqoop import-all

Big Data Apache Sqoop
, ,
#1

Hello,

I am trying to create a different schema for AVRO based tables. Below is the command m using. I can see the proper directory structure with all the data files with .avro extension but when i log into HIVE & execute show tables; under retail_avro.db schema there are no tables

> sqoop import-all-tables \
> -m 2 \
> --connect "jdbc:mysql://quickstart.cloudera:3306/retail_db" \
> --username=retail_dba \
> --password=cloudera \
> --warehouse-dir=/user/hive/warehouse/retail_avro.db \
> --create-hive-table \
> --as-avrodatafile

After the above Command below is the Output if i access using hdfs commands:

[cloudera@quickstart ~]$ hdfs dfs -ls /user/hive/warehouse/retail_avro.db
Found 6 items
drwxr-xr-x   - cloudera hive          0 2016-12-30 04:25 /user/hive/warehouse/retail_avro.db/categories
drwxr-xr-x   - cloudera hive          0 2016-12-30 04:25 /user/hive/warehouse/retail_avro.db/customers
drwxr-xr-x   - cloudera hive          0 2016-12-30 04:25 /user/hive/warehouse/retail_avro.db/departments
drwxr-xr-x   - cloudera hive          0 2016-12-30 04:25 /user/hive/warehouse/retail_avro.db/order_items
drwxr-xr-x   - cloudera hive          0 2016-12-30 04:26 /user/hive/warehouse/retail_avro.db/orders
drwxr-xr-x   - cloudera hive          0 2016-12-30 04:26 /user/hive/warehouse/retail_avro.db/products
[cloudera@quickstart ~]$
[cloudera@quickstart ~]$
[cloudera@quickstart ~]$ hdfs dfs -ls /user/hive/warehouse/retail_avro.db/categories
Found 3 items
-rw-r--r--   1 cloudera hive          0 2016-12-30 04:25 /user/hive/warehouse/retail_avro.db/categories/_SUCCESS
-rw-r--r--   1 cloudera hive       1007 2016-12-30 04:25 /user/hive/warehouse/retail_avro.db/categories/part-m-00000.avro
-rw-r--r--   1 cloudera hive        959 2016-12-30 04:25 /user/hive/warehouse/retail_avro.db/categories/part-m-00001.avro

And When i login into HIVE :

hive> use retail_avro;
OK
Time taken: 1.942 seconds
hive> show tables;
OK
Time taken: 0.518 seconds
hive> select * from departments;
FAILED: SemanticException [Error 10001]: Line 1:14 Table not found ‘departments’

#2

You need to use --hive-import to create tables in Hive. But I think --hive-import with avrodatafile is not supported. So you need to create tables in Hive on top of dir you imported data from sqoop.

#3

Ya that is the option we have. But what i was trying to do was to import all tables from retail_db(mysql) into my empty retail_avro.db schema in AVRO format and without creating tables first. So that is the reason why i gave --create-hive-table option which will also create all tables in empty schema without Manually creating it.

But i don’t whats going wrong over here. or whether it is actually NOT possible to work in the way m thinking …

#4

As @N_Chakote mentioned you need to use --hive-import. There is no need to create tables before hand to use --hive-import.

#5

–hive-import is not compatible with avro files :slight_smile:

sqoop import-all-tables \
-m 2 \
--connect "jdbc:mysql://quickstart.cloudera:3306/retail_db" \
--username=retail_dba \
--password=cloudera \
--hive-import \
--warehouse-dir=/user/hive/warehouse/retail_avro.db \
--as-avrodatafile

The above gave me ERROR
You can override 16/12/30 04:10:47 INFO tool.BaseSqoopTool: delimiters with --fields-terminated-by, etc.
Hive import is not compatible with importing into AVRO format.

#6

Ok, good to know. In that case you have to first do the import and create the tables later.

#7

Ya I tried creating a table later & it is showing me the results when queried.

#8

Created orders table :-

CREATE TABLE orders (
order_id int,
order_date string,
order_customer_id int,
order_status string
) 
STORED AS AVRO;

Don’t know Why getting this ERROR :confused:
hive>
> select * from orders limit 10;
OK
Failed with exception java.io.IOException:org.apache.avro.AvroTypeException: Found long, expecting union
Time taken: 0.085 seconds

#9

You already have data in hdfs and now ur are creating a table so it will be external table not managed table.