Table not getting created using sqoop import-all

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’

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.

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 …

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

–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.

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

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

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

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