Need help!!!
I am streaming twitter feeds into hdfs using flume
and loading it up in hive
for analysis.
The steps are as follows:
Data in hdfs:
I have described the avro schema
in an avsc
file and put it in hadoop:
{"type":"record",
"name":"Doc",
"doc":"adoc",
"fields":[{"name":"id","type":"string"},
{"name":"user_friends_count","type":["int","null"]},
{"name":"user_location","type":["string","null"]},
{"name":"user_description","type":["string","null"]},
{"name":"user_statuses_count","type":["int","null"]},
{"name":"user_followers_count","type":["int","null"]},
{"name":"user_name","type":["string","null"]},
{"name":"user_screen_name","type":["string","null"]},
{"name":"created_at","type":["string","null"]},
{"name":"text","type":["string","null"]},
{"name":"retweet_count","type":["boolean","null"]},
{"name":"retweeted","type":["boolean","null"]},
{"name":"in_reply_to_user_id","type":["long","null"]},
{"name":"source","type":["string","null"]},
{"name":"in_reply_to_status_id","type":["long","null"]},
{"name":"media_url_https","type":["string","null"]},
{"name":"expanded_url","type":["string","null"]}]}
I have written an .hql file to create a table and loaded data in it:
create table tweetsavro
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'
tblproperties ('avro.schema.url'='hdfs:///avro_schema/AvroSchemaFile.avsc');
load data inpath '/test/twitter_data/FlumeData.*' overwrite into table tweetsavro;
I have successfully run the .hql file but when i run the select *from <tablename>
command in hive it shows the following error:
error
The output of tweetsavro is:
hive> desc tweetsavro;
OK
id string
user_friends_count int
user_location string
user_description string
user_statuses_count int
user_followers_count int
user_name string
user_screen_name string
created_at string
text string
retweet_count boolean
retweeted boolean
in_reply_to_user_id bigint
source string
in_reply_to_status_id bigint
media_url_https string
expanded_url string
Time taken: 0.697 seconds, Fetched: 17 row(s)
I was facing the exact same issue. The issue existed in the timestamp field("created_at" column in your case) which i was trying to insert as string into my new table. My assumption was this data would be in [ "null","string"]
format in my source. I analyzed the source avro schema which got generated from the sqoop import --as-avrodatafile process. The avro schema generated from import had the below signature for the timestamp column.{
"name" : "order_date",
"type" : [ "null", "long" ],
"default" : null,
"columnName" : "order_date",
"sqlType" : "93"
},
SqlType 93 stands for Timestamp datatype. So in my target table Avro Schema file I changed the data type to 'long' and this solved the issue. My guess is possibly the mismatch of datatype in one of your columns.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With