Top order Array

[sushantkhanayat]

Hi,
I am able to insert the data in the table but only first record is getting picked and for other record it's coming NULL, i am pretty sure its not able to find out the second record in the array.
Please help me out here.
I have a sample file in below format:

[{"expiration_date":"2003-04-04","county_parish":"Bee","acres":20.00000000,"grantee_alias":"ROBERT H ADAMS","bonus":null,"instrument_date":"2000-04-04","grantee_address":"P. O. Box 20602 , Oklahoma City, OK 73156-0602","state_province":"TX","instrument_type":"Memo of lease","effective_date":null,"maximum_depth":0,"county_state":"Bee (TX)","volume_page":"629/734","id":168,"grantee_name":"Robert H. Adams","record_number":"118567","blm_lease":0,"grantor_address":"235 West Jefferson Street , Tipton, IN 46072-1823","term_months":36,"extension_bonus":null,"royalty":null,"extension_term_month":null,"record_date":"2000-05-08","minimum_depth":0,"grantor_name":"Earl E. Nelson, et ux","geometry":{"type":"Polygon","coordinates":[[[-98.02555516,28.65628727],[-98.02763665,28.6562646],[-98.02767242,28.65998903],[-98.02559752,28.65996315],[-98.02555516,28.65628727]]]},"state_lease":0,"extension":0},{"expiration_date":"2003-03-23","county_parish":"Bee","acres":36.28000000,"grantee_alias":"SOUTHERN RESOURCE","bonus":null,"instrument_date":"2000-03-23","grantee_address":"P. O. Box 6483 , Corpus Christi, TX 78466","state_province":"TX","instrument_type":"Lease","effective_date":null,"maximum_depth":null,"county_state":"Bee (TX)","volume_page":"629/946","id":169,"grantee_name":"Southern Resources Company","record_number":"10","blm_lease":0,"grantor_address":"726 Texas Avenue , Port Neches, TX 77651","term_months":36,"extension_bonus":null,"royalty":0.20000000,"extension_term_month":null,"record_date":"2000-05-10","minimum_depth":0,"grantor_name":"Winnie Harris Brown, et al","geometry":{"type":"Polygon","coordinates":[[[-97.83107188,28.43751166],[-97.83275784,28.4404257],[-97.83025018,28.44150562],[-97.82625245,28.43967666],[-97.83107188,28.43751166]]]},"state_lease":0,"extension":0}]

DDL i have come up with is :
CREATE TABLE test(
a ARRAY<
struct
<expiration_date:Date,
county_parish:String,
acres:float,
grantee_alias:string,
bonus:string,
instrument_date:date,
grantee_address:string,
state_province:string,
instrument_type:string,
effective_date:date,
maximum_depth:int,
county_state:string,
volume_page:string,
id:INT,
grantee_name:string,
record_number:int,
blm_lease:int,
grantor_address:STRING,
term_months:INT,
extension_bonus:STRING,
royalty:DOUBLE,
extension_term_month:STRING,
record_date:DATE,
minimum_depth:INT,
grantor_name:STRING,
geometry:STRUCT<
type: STRING,
coordinates: ARRAY< ARRAY< ARRAY< ARRAY< DOUBLE > > > > >,
state_lease:INT,
extension:INT

)ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
stored as textfile;

[YevheniiStepaniuk]

@sushantkhanayat I've faced with the same. Have you any solution to fix that?

[rcongiu]

I see, the issue is that your record is actually an array. In hive a record has to be a struct.