No data in table created using .avsc

hive
sqoop
#1

Hi,

Table is created sucessfully using avro .avsc file. But when queried the table it shows no records.

Code:
CREATE EXTERNAL TABLE departments_format
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.avro.AvroSerDe’
STORED AS INPUTFORMAT 'org.apache.hadoop.hive.ql.io.avro.AvroContainerInputFormat’
OUTPUTFORMAT ‘org.apache.hadoop.hive.ql.io.avro.AvroContainerOutputFormat’
LOCATION ‘hdfs:///user/mangleeswaran/departments_format’
TBLPROPERTIES (‘avro.schema.url’=’/user/mangleeswaran/departments_man.avsc’);

Thanks

0 Likes

#2

Try with Stored as Avro. below is the example

CREATE EXTERNAL TABLE departments_format
STORED AS AVRO
LOCATION 'hdfs:///user/mangleeswaran/departments_format’
TBLPROPERTIES (‘avro.schema.url’=‘hdfs://nn01.itversity.com/user/mangleeswaran/departments_man.avsc’);

0 Likes

#3

@gnanaprakasam : I am able to create the table using your code but still no record coming.
But I am seeing data in avro format in /user/mangleeswaran/departments_format.

0 Likes

#4

@mangleeswaran
I have changed the permission to view this files
/apps/hive/warehouse/gnanaprakasam.db/sqoop_import/departments
hdfs://nn01.itversity.com/user/gnanaprakasam/avsc_files/departments.avsc

Below code working fine to create the table, could you please check you have anything different with data.

CREATE EXTERNAL TABLE departments_new
STORED AS AVRO
LOCATION 'hdfs:///apps/hive/warehouse/gnanaprakasam.db/sqoop_import/departments’
TBLPROPERTIES (‘avro.schema.url’=‘hdfs://nn01.itversity.com/user/gnanaprakasam/avsc_files/departments.avsc’);

hive> select * from departments_new;
OK
2 Fitness
3 Footwear
4 Apparel
5 Golf
6 Outdoors
7 Fan Shop
8 Testing Merge
9 paper
10 Indoor
10000 Inserting for merge
Time taken: 0.221 seconds, Fetched: 10 row(s)

1 Like

#5

@gnanaprakasam

hive> CREATE EXTERNAL TABLE departments_e
> ROW FORMAT SERDE ‘org.apache.hadoop.hive.serde2.avro.AvroSerDe’
> STORED AS INPUTFORMAT ‘org.apache.hadoop.hive.ql.io.avro.AvroContainerInputFormat’
> OUTPUTFORMAT ‘org.apache.hadoop.hive.ql.io.avro.AvroContainerOutputFormat’
> LOCATION ‘hdfs:///user/cloudera/departments_e’
> TBLPROPERTIES (‘avro.schema.url’=‘hdfs://quickstart.cloudera/user/cloudera/deparOKents.avsc’);
Time taken: 0.203 seconds
hive> select * from departments_e;
OK
Time taken: 0.476 seconds
hive>

No data found on /user/cloudera/departments_e although I have 755 on /user/cloudera/departments.avsc

[cloudera@quickstart ~]$ hadoop fs -ls /user/cloudera/departments.avsc
-rwxr-xr-x 1 cloudera cloudera 440 2016-12-31 09:26 /user/cloudera/departments.avsc
[cloudera@quickstart ~]$ hadoop fs -ls /user/cloudera/departments_e
[cloudera@quickstart ~]$

0 Likes

#6

@surajminz1 - You have to check your sqoop import is successfully to have data in /user/cloudera/departments_e before executing create table with schema.

0 Likes