Querying hive without providing schema during create table for JSON data

[Jeetendra G <je...@housing.com>]

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 <mu...@gmail.com>]

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 <http://muthupandi.strikingly.com>*

 Think before you print.



On Thu, Sep 10, 2015 at 9:16 PM, Jeetendra G <je...@housing.com>
wrote:

> anybody here? can somebody guide me on this?
>
> On Thu, Sep 10, 2015 at 2:36 PM, Jeetendra G <je...@housing.com>
> 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

[Jeetendra G <je...@housing.com>]

anybody here? can somebody guide me on this?

On Thu, Sep 10, 2015 at 2:36 PM, Jeetendra G <je...@housing.com>
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;
>