Querying hive without providing schema during create table for JSON data

[Jeetendra G]

Hi All,

I have JSON data Can I create table without schema? and query that data,
since all my data os JSON data so I was thinking there should be some way.

Currently I am doing something like below, but I want to remove Schema from
this, because I have i ma having thousand of event type and all of them i
cant really create the schema, Highlighted part I want to remove and let
the hive decide the schema.


create external table if not exists impressions_data
(
*city string,*
*service string,*
*id int,*
*type string,*
*listings int,*
*visible_markers int,*
*visible_as_markers int,*
*city_id int,*
*location_type string,*
*polygon_id string,*
*polygon_name string,*
*BuyListViewCount string,*
*AutoSuggest string,*
*LocalityExpert string,*
*BuyerExperiment string,*
*SellerExperiment string,*
*PaidAdProducts string,*
*index_view_type string,*
*page_type string,*
*clid string,*
*device string,*
*results_by_profile string,*
*listing_index int,*
*huid string,*
*uid string,*
*sid int,*
*count int,*
*category string,*
*action string,*
*url string,*
*`timeStamp` bigint)*
partitioned by (`date` string)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
LOCATION 'hdfs://
hadoop01.housing.com:8020/housing/events/validated/category=impressions/';

MSCK REPAIR TABLE impressions_data;
SHOW PARTITIONS impressions_data;
select * from impressions_data limit 1;

Re: Querying hive without providing schema during create table for JSON data

[Jeetendra G]

anybody here? can somebody guide me on this?

On Thu, Sep 10, 2015 at 2:36 PM, Jeetendra G 
wrote:

> Hi All,
>
> I have JSON data Can I create table without schema? and query that data,
> since all my data os JSON data so I was thinking there should be some way.
>
> Currently I am doing something like below, but I want to remove Schema
> from this, because I have i ma having thousand of event type and all of
> them i cant really create the schema, Highlighted part I want to remove and
> let the hive decide the schema.
>
>
> create external table if not exists impressions_data
> (
> *city string,*
> *service string,*
> *id int,*
> *type string,*
> *listings int,*
> *visible_markers int,*
> *visible_as_markers int,*
> *city_id int,*
> *location_type string,*
> *polygon_id string,*
> *polygon_name string,*
> *BuyListViewCount string,*
> *AutoSuggest string,*
> *LocalityExpert string,*
> *BuyerExperiment string,*
> *SellerExperiment string,*
> *PaidAdProducts string,*
> *index_view_type string,*
> *page_type string,*
> *clid string,*
> *device string,*
> *results_by_profile string,*
> *listing_index int,*
> *huid string,*
> *uid string,*
> *sid int,*
> *count int,*
> *category string,*
> *action string,*
> *url string,*
> *`timeStamp` bigint)*
> partitioned by (`date` string)
> ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
> LOCATION 'hdfs://
> hadoop01.housing.com:8020/housing/events/validated/category=impressions/';
>
> MSCK REPAIR TABLE impressions_data;
> SHOW PARTITIONS impressions_data;
> select * from impressions_data limit 1;
>

Re: Querying hive without providing schema during create table for JSON data

[Muthu Pandi]

You can use Apache Drill to query Json data without creating the schema but
you cant in Hive.

http://drill.apache.org/docs/querying-json-files/


*RegardsMuthupandi.K*

*muthupandi.strikingly.com *

 Think before you print.



On Thu, Sep 10, 2015 at 9:16 PM, Jeetendra G 
wrote:

> anybody here? can somebody guide me on this?
>
> On Thu, Sep 10, 2015 at 2:36 PM, Jeetendra G 
> wrote:
>
>> Hi All,
>>
>> I have JSON data Can I create table without schema? and query that data,
>> since all my data os JSON data so I was thinking there should be some way.
>>
>> Currently I am doing something like below, but I want to remove Schema
>> from this, because I have i ma having thousand of event type and all of
>> them i cant really create the schema, Highlighted part I want to remove and
>> let the hive decide the schema.
>>
>>
>> create external table if not exists impressions_data
>> (
>> *city string,*
>> *service string,*
>> *id int,*
>> *type string,*
>> *listings int,*
>> *visible_markers int,*
>> *visible_as_markers int,*
>> *city_id int,*
>> *location_type string,*
>> *polygon_id string,*
>> *polygon_name string,*
>> *BuyListViewCount string,*
>> *AutoSuggest string,*
>> *LocalityExpert string,*
>> *BuyerExperiment string,*
>> *SellerExperiment string,*
>> *PaidAdProducts string,*
>> *index_view_type string,*
>> *page_type string,*
>> *clid string,*
>> *device string,*
>> *results_by_profile string,*
>> *listing_index int,*
>> *huid string,*
>> *uid string,*
>> *sid int,*
>> *count int,*
>> *category string,*
>> *action string,*
>> *url string,*
>> *`timeStamp` bigint)*
>> partitioned by (`date` string)
>> ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
>> LOCATION 'hdfs://
>> hadoop01.housing.com:8020/housing/events/validated/category=impressions/
>> ';
>>
>> MSCK REPAIR TABLE impressions_data;
>> SHOW PARTITIONS impressions_data;
>> select * from impressions_data limit 1;
>>
>
>