Copy from Json.Serde formatted table into a text file formatted hive table

0
votes

I am completely new with hive.

I have created a hive table on top of my json files using Json.SerDe and also loaded the data.

Below is the structure

CREATE EXTERNAL TABLE JsonTable_raw (
  data array<struct<
      start_date:string,
      end_date:string,
      measures:struct<
      Visitors:int,
      Singlepagevisits:int
      >
    >
  >
) ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'

I have created one more table which is not formatted using JsonSerDe. 

CREATE EXTERNAL TABLE JsonTable (
  start_date string,
  end_date string,
  Visitors int,
  Singlepagevisits int
)

I tried to use the below query

INSERT INTO TABLE JsonTable select
  data.start_date,data.end_date,data.measures.Visitors,
data.measures.Singlepagevisits FROM JsonTable_raw;

but it threw

NoMatchingMethodException No matching method for class org.apache.hadoop.hive.ql.udf.UDFToInteger with (array). Possible choices: FUNC(bigint) FUNC(boolean) FUNC(decimal(38,18)) FUNC(double) FUNC(float) FUNC(smallint) FUNC(string) FUNC(timestamp) FUNC(tinyint) FUNC(void)

Now how do I copy the data from JsonTable_raw to JsonTable?

1
hadoophive

1 Answers

0
votes

After an hour of searching and trying I was able to do it myself. Below is the script 

INSERT INTO TABLE JsonTable 
select cols.start_date,cols.end_date,cols.measures.Visitors,
cols.measures.Singlepagevisits
FROM JsonTable_raw jt lateral view explode(jt.data) collection as cols;