is there a way to derive information (schema) off a hive table?

doing a describe only shows the clolumn w/ types.

But i want to know if a table is like this:

 ROW FORMAT   DELIMITED

    FIELDS TERMINATED BY '\t'

    COLLECTION ITEMS TERMINATED BY '\002'

    MAP KEYS TERMINATED BY '\003'

  STORED AS INPUTFORMAT 'com.hadoop.mapred.DeprecatedLzoTextInputFormat'
OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat';

Or like this:

ROW FORMAT   DELIMITED

    FIELDS TERMINATED BY '\t'

  STORED AS INPUTFORMAT 'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat';
where can i find that info?
Thanks in advance.

Patch is forthcoming..

https://issues.apache.org/jira/browse/HIVE-967

There is a stand alone util referenced in the JIRA that you can use for now.

On Thu, Aug 2, 2012 at 3:04 PM, Anson Abraham <[EMAIL PROTECTED]> wrote:
> is there a way to derive information (schema) off a hive table?
>
> doing a describe only shows the clolumn w/ types.
>
> But i want to know if a table is like this:
>
> ROW FORMAT   DELIMITED
>
>     FIELDS TERMINATED BY '\t'
>
>     COLLECTION ITEMS TERMINATED BY '\002'
>
>     MAP KEYS TERMINATED BY '\003'
>
>   STORED AS INPUTFORMAT 'com.hadoop.mapred.DeprecatedLzoTextInputFormat'
> OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat';
>
>
>
> Or like this:
>
> ROW FORMAT   DELIMITED
>
>     FIELDS TERMINATED BY '\t'
>
>   STORED AS INPUTFORMAT 'org.apache.hadoop.mapred.TextInputFormat'
> OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat';
>
>
> where can i find that info?
>
>
> Thanks in advance.

Try
describe formatted <table>

igor
decide.com

On Thu, Aug 2, 2012 at 12:04 PM, Anson Abraham <[EMAIL PROTECTED]>wrote:

> is there a way to derive information (schema) off a hive table?
>
> doing a describe only shows the clolumn w/ types.
>
> But i want to know if a table is like this:
>
>  ROW FORMAT   DELIMITED
>
>     FIELDS TERMINATED BY '\t'
>
>     COLLECTION ITEMS TERMINATED BY '\002'
>
>     MAP KEYS TERMINATED BY '\003'
>
>   STORED AS INPUTFORMAT 'com.hadoop.mapred.DeprecatedLzoTextInputFormat'
> OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat';
>
>
>
> Or like this:
>
> ROW FORMAT   DELIMITED
>
>     FIELDS TERMINATED BY '\t'
>
>   STORED AS INPUTFORMAT 'org.apache.hadoop.mapred.TextInputFormat'
> OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat';
>
>
> where can i find that info?
>
>
> Thanks in advance.
>
>

ah, works.  thanks.

On Thu, Aug 2, 2012 at 3:13 PM, Igor Tatarinov <[EMAIL PROTECTED]> wrote:

> Try
> describe formatted <table>
>
> igor
> decide.com
>
> On Thu, Aug 2, 2012 at 12:04 PM, Anson Abraham <[EMAIL PROTECTED]>wrote:
>
>> is there a way to derive information (schema) off a hive table?
>>
>> doing a describe only shows the clolumn w/ types.
>>
>> But i want to know if a table is like this:
>>
>>  ROW FORMAT   DELIMITED
>>
>>     FIELDS TERMINATED BY '\t'
>>
>>     COLLECTION ITEMS TERMINATED BY '\002'
>>
>>     MAP KEYS TERMINATED BY '\003'
>>
>>   STORED AS INPUTFORMAT 'com.hadoop.mapred.DeprecatedLzoTextInputFormat'
>> OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat';
>>
>>
>>
>> Or like this:
>>
>> ROW FORMAT   DELIMITED
>>
>>     FIELDS TERMINATED BY '\t'
>>
>>   STORED AS INPUTFORMAT 'org.apache.hadoop.mapred.TextInputFormat'
>> OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat';
>>
>>
>> where can i find that info?
>>
>>
>> Thanks in advance.
>>
>>
>

What format you used? I tried like this and it doesn't works for me-

*describe formatted table1*

I am running Hive 0.6. Anything else I need to do before running this query?
On Thu, Aug 2, 2012 at 12:13 PM, Igor Tatarinov <[EMAIL PROTECTED]> wrote:

> Try
> describe formatted <table>
>
> igor
> decide.com
>
> On Thu, Aug 2, 2012 at 12:04 PM, Anson Abraham <[EMAIL PROTECTED]>wrote:
>
>> is there a way to derive information (schema) off a hive table?
>>
>> doing a describe only shows the clolumn w/ types.
>>
>> But i want to know if a table is like this:
>>
>>  ROW FORMAT   DELIMITED
>>
>>     FIELDS TERMINATED BY '\t'
>>
>>     COLLECTION ITEMS TERMINATED BY '\002'
>>
>>     MAP KEYS TERMINATED BY '\003'
>>
>>   STORED AS INPUTFORMAT 'com.hadoop.mapred.DeprecatedLzoTextInputFormat'
>> OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat';
>>
>>
>>
>> Or like this:
>>
>> ROW FORMAT   DELIMITED
>>
>>     FIELDS TERMINATED BY '\t'
>>
>>   STORED AS INPUTFORMAT 'org.apache.hadoop.mapred.TextInputFormat'
>> OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat';
>>
>>
>> where can i find that info?
>>
>>
>> Thanks in advance.
>>
>>
>

And I got this error too-

FAILED: Parse Error: line 1:19 mismatched input 'table1' expecting EOF
On Thu, Aug 2, 2012 at 12:44 PM, Techy Teck <[EMAIL PROTECTED]> wrote:

> What format you used? I tried like this and it doesn't works for me-
>
> *describe formatted table1*
>
> I am running Hive 0.6. Anything else I need to do before running this
> query?
>
>
>
>
> On Thu, Aug 2, 2012 at 12:13 PM, Igor Tatarinov <[EMAIL PROTECTED]> wrote:
>
>> Try
>> describe formatted <table>
>>
>> igor
>> decide.com
>>
>> On Thu, Aug 2, 2012 at 12:04 PM, Anson Abraham <[EMAIL PROTECTED]>wrote:
>>
>>> is there a way to derive information (schema) off a hive table?
>>>
>>> doing a describe only shows the clolumn w/ types.
>>>
>>> But i want to know if a table is like this:
>>>
>>>  ROW FORMAT   DELIMITED
>>>
>>>     FIELDS TERMINATED BY '\t'
>>>
>>>     COLLECTION ITEMS TERMINATED BY '\002'
>>>
>>>     MAP KEYS TERMINATED BY '\003'
>>>
>>>   STORED AS INPUTFORMAT 'com.hadoop.mapred.DeprecatedLzoTextInputFormat'
>>> OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat';
>>>
>>>
>>>
>>> Or like this:
>>>
>>> ROW FORMAT   DELIMITED
>>>
>>>     FIELDS TERMINATED BY '\t'
>>>
>>>   STORED AS INPUTFORMAT 'org.apache.hadoop.mapred.TextInputFormat'
>>> OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat';
>>>
>>>
>>> where can i find that info?
>>>
>>>
>>> Thanks in advance.
>>>
>>>
>>
>

pretty much what you did.  I didn't realize that it was in the docs already:

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL#LanguageManualDDL-DescribeTable%2FColumn

Not sure, but I think this function is supported in 0.6  but I could be
wrong.

On Thu, Aug 2, 2012 at 3:59 PM, Techy Teck <[EMAIL PROTECTED]> wrote:

> And I got this error too-
>
> FAILED: Parse Error: line 1:19 mismatched input 'table1' expecting EOF
>
>
>
>
> On Thu, Aug 2, 2012 at 12:44 PM, Techy Teck <[EMAIL PROTECTED]>wrote:
>
>> What format you used? I tried like this and it doesn't works for me-
>>
>> *describe formatted table1*
>>
>> I am running Hive 0.6. Anything else I need to do before running this
>> query?
>>
>>
>>
>>
>> On Thu, Aug 2, 2012 at 12:13 PM, Igor Tatarinov <[EMAIL PROTECTED]> wrote:
>>
>>> Try
>>> describe formatted <table>
>>>
>>> igor
>>> decide.com
>>>
>>> On Thu, Aug 2, 2012 at 12:04 PM, Anson Abraham <[EMAIL PROTECTED]>wrote:
>>>
>>>> is there a way to derive information (schema) off a hive table?
>>>>
>>>> doing a describe only shows the clolumn w/ types.
>>>>
>>>> But i want to know if a table is like this:
>>>>
>>>>  ROW FORMAT   DELIMITED
>>>>
>>>>     FIELDS TERMINATED BY '\t'
>>>>
>>>>     COLLECTION ITEMS TERMINATED BY '\002'
>>>>
>>>>     MAP KEYS TERMINATED BY '\003'
>>>>
>>>>   STORED AS INPUTFORMAT
>>>> 'com.hadoop.mapred.DeprecatedLzoTextInputFormat' OUTPUTFORMAT
>>>> 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat';
>>>>
>>>>
>>>>
>>>> Or like this:
>>>>
>>>> ROW FORMAT   DELIMITED
>>>>
>>>>     FIELDS TERMINATED BY '\t'
>>>>
>>>>   STORED AS INPUTFORMAT 'org.apache.hadoop.mapred.TextInputFormat'
>>>> OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat';
>>>>
>>>>
>>>> where can i find that info?
>>>>
>>>>
>>>> Thanks in advance.
>>>>
>>>>
>>>
>>
>

Nice one worked for me.

On Thu, Aug 2, 2012 at 4:03 PM, Anson Abraham <[EMAIL PROTECTED]>wrote:

> pretty much what you did.  I didn't realize that it was in the docs
> already:
>
>
> https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL#LanguageManualDDL-DescribeTable%2FColumn
>
> Not sure, but I think this function is supported in 0.6  but I could be
> wrong.
>
>
> On Thu, Aug 2, 2012 at 3:59 PM, Techy Teck <[EMAIL PROTECTED]>wrote:
>
>> And I got this error too-
>>
>> FAILED: Parse Error: line 1:19 mismatched input 'table1' expecting EOF
>>
>>
>>
>>
>> On Thu, Aug 2, 2012 at 12:44 PM, Techy Teck <[EMAIL PROTECTED]>wrote:
>>
>>> What format you used? I tried like this and it doesn't works for me-
>>>
>>> *describe formatted table1*
>>>
>>> I am running Hive 0.6. Anything else I need to do before running this
>>> query?
>>>
>>>
>>>
>>>
>>> On Thu, Aug 2, 2012 at 12:13 PM, Igor Tatarinov <[EMAIL PROTECTED]> wrote:
>>>
>>>> Try
>>>> describe formatted <table>
>>>>
>>>> igor
>>>> decide.com
>>>>
>>>> On Thu, Aug 2, 2012 at 12:04 PM, Anson Abraham <[EMAIL PROTECTED]
>>>> > wrote:
>>>>
>>>>> is there a way to derive information (schema) off a hive table?
>>>>>
>>>>> doing a describe only shows the clolumn w/ types.
>>>>>
>>>>> But i want to know if a table is like this:
>>>>>
>>>>>  ROW FORMAT   DELIMITED
>>>>>
>>>>>     FIELDS TERMINATED BY '\t'
>>>>>
>>>>>     COLLECTION ITEMS TERMINATED BY '\002'
>>>>>
>>>>>     MAP KEYS TERMINATED BY '\003'
>>>>>
>>>>>   STORED AS INPUTFORMAT
>>>>> 'com.hadoop.mapred.DeprecatedLzoTextInputFormat' OUTPUTFORMAT
>>>>> 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat';
>>>>>
>>>>>
>>>>>
>>>>> Or like this:
>>>>>
>>>>> ROW FORMAT   DELIMITED
>>>>>
>>>>>     FIELDS TERMINATED BY '\t'
>>>>>
>>>>>   STORED AS INPUTFORMAT 'org.apache.hadoop.mapred.TextInputFormat'
>>>>> OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat';
>>>>>
>>>>>
>>>>> where can i find that info?
>>>>>
>>>>>
>>>>> Thanks in advance.
>>>>>
>>>>>
>>>>
>>>
>>
>
--
Thanks,
sandeep

Can you tell me what version you guys are using?

Can anyone tell me to use *describe formatted table1 *which version of hive
we need?
On Thu, Aug 2, 2012 at 1:12 PM, Sandeep Reddy P <[EMAIL PROTECTED]
> wrote:

> Nice one worked for me.
>
>
> On Thu, Aug 2, 2012 at 4:03 PM, Anson Abraham <[EMAIL PROTECTED]>wrote:
>
>> pretty much what you did.  I didn't realize that it was in the docs
>> already:
>>
>>
>> https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL#LanguageManualDDL-DescribeTable%2FColumn
>>
>> Not sure, but I think this function is supported in 0.6  but I could be
>> wrong.
>>
>>
>> On Thu, Aug 2, 2012 at 3:59 PM, Techy Teck <[EMAIL PROTECTED]>wrote:
>>
>>> And I got this error too-
>>>
>>> FAILED: Parse Error: line 1:19 mismatched input 'table1' expecting EOF
>>>
>>>
>>>
>>>
>>> On Thu, Aug 2, 2012 at 12:44 PM, Techy Teck <[EMAIL PROTECTED]>wrote:
>>>
>>>> What format you used? I tried like this and it doesn't works for me-
>>>>
>>>> *describe formatted table1*
>>>>
>>>> I am running Hive 0.6. Anything else I need to do before running this
>>>> query?
>>>>
>>>>
>>>>
>>>>
>>>> On Thu, Aug 2, 2012 at 12:13 PM, Igor Tatarinov <[EMAIL PROTECTED]>wrote:
>>>>
>>>>> Try
>>>>> describe formatted <table>
>>>>>
>>>>> igor
>>>>> decide.com
>>>>>
>>>>> On Thu, Aug 2, 2012 at 12:04 PM, Anson Abraham <
>>>>> [EMAIL PROTECTED]> wrote:
>>>>>
>>>>>> is there a way to derive information (schema) off a hive table?
>>>>>>
>>>>>> doing a describe only shows the clolumn w/ types.
>>>>>>
>>>>>> But i want to know if a table is like this:
>>>>>>
>>>>>>  ROW FORMAT   DELIMITED
>>>>>>
>>>>>>     FIELDS TERMINATED BY '\t'
>>>>>>
>>>>>>     COLLECTION ITEMS TERMINATED BY '\002'
>>>>>>
>>>>>>     MAP KEYS TERMINATED BY '\003'
>>>>>>
>>>>>>   STORED AS INPUTFORMAT
>>>>>> 'com.hadoop.mapred.DeprecatedLzoTextInputFormat' OUTPUTFORMAT
>>>>>> 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat';
>>>>>>
>>>>>>
>>>>>>
>>>>>> Or like this:
>>>>>>
>>>>>> ROW FORMAT   DELIMITED
>>>>>>
>>>>>>     FIELDS TERMINATED BY '\t'
>>>>>>
>>>>>>   STORED AS INPUTFORMAT 'org.apache.hadoop.mapred.TextInputFormat'
>>>>>> OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat';
>>>>>>
>>>>>>
>>>>>> where can i find that info?
>>>>>>
>>>>>>
>>>>>> Thanks in advance.
>>>>>>
>>>>>>
>>>>>
>>>>
>>>
>>
>
>
> --
> Thanks,
> sandeep
>
>

Hi Techy

To use 'describe formatted' you need at least hive 0.7. But 'describe extended' should be available in hive 0.6 itself.

It is always better using the latest version. I recommend hive 0.9 at the moment.
Regards
Bejoy KS

Sent from handheld, please excuse typos.

-----Original Message-----
From: Techy Teck <[EMAIL PROTECTED]>
Date: Thu, 2 Aug 2012 14:54:06
To: <[EMAIL PROTECTED]>
Reply-To: [EMAIL PROTECTED]
Subject: Re: schema of hive database

Can you tell me what version you guys are using?

Can anyone tell me to use *describe formatted table1 *which version of hive
we need?
On Thu, Aug 2, 2012 at 1:12 PM, Sandeep Reddy P <[EMAIL PROTECTED]
> wrote:

> Nice one worked for me.
>
>
> On Thu, Aug 2, 2012 at 4:03 PM, Anson Abraham <[EMAIL PROTECTED]>wrote:
>
>> pretty much what you did.  I didn't realize that it was in the docs
>> already:
>>
>>
>> https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL#LanguageManualDDL-DescribeTable%2FColumn
>>
>> Not sure, but I think this function is supported in 0.6  but I could be
>> wrong.
>>
>>
>> On Thu, Aug 2, 2012 at 3:59 PM, Techy Teck <[EMAIL PROTECTED]>wrote:
>>
>>> And I got this error too-
>>>
>>> FAILED: Parse Error: line 1:19 mismatched input 'table1' expecting EOF
>>>
>>>
>>>
>>>
>>> On Thu, Aug 2, 2012 at 12:44 PM, Techy Teck <[EMAIL PROTECTED]>wrote:
>>>
>>>> What format you used? I tried like this and it doesn't works for me-
>>>>
>>>> *describe formatted table1*
>>>>
>>>> I am running Hive 0.6. Anything else I need to do before running this
>>>> query?
>>>>
>>>>
>>>>
>>>>
>>>> On Thu, Aug 2, 2012 at 12:13 PM, Igor Tatarinov <[EMAIL PROTECTED]>wrote:
>>>>
>>>>> Try
>>>>> describe formatted <table>
>>>>>
>>>>> igor
>>>>> decide.com
>>>>>
>>>>> On Thu, Aug 2, 2012 at 12:04 PM, Anson Abraham <
>>>>> [EMAIL PROTECTED]> wrote:
>>>>>
>>>>>> is there a way to derive information (schema) off a hive table?
>>>>>>
>>>>>> doing a describe only shows the clolumn w/ types.
>>>>>>
>>>>>> But i want to know if a table is like this:
>>>>>>
>>>>>>  ROW FORMAT   DELIMITED
>>>>>>
>>>>>>     FIELDS TERMINATED BY '\t'
>>>>>>
>>>>>>     COLLECTION ITEMS TERMINATED BY '\002'
>>>>>>
>>>>>>     MAP KEYS TERMINATED BY '\003'
>>>>>>
>>>>>>   STORED AS INPUTFORMAT
>>>>>> 'com.hadoop.mapred.DeprecatedLzoTextInputFormat' OUTPUTFORMAT
>>>>>> 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat';
>>>>>>
>>>>>>
>>>>>>
>>>>>> Or like this:
>>>>>>
>>>>>> ROW FORMAT   DELIMITED
>>>>>>
>>>>>>     FIELDS TERMINATED BY '\t'
>>>>>>
>>>>>>   STORED AS INPUTFORMAT 'org.apache.hadoop.mapred.TextInputFormat'
>>>>>> OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat';
>>>>>>
>>>>>>
>>>>>> where can i find that info?
>>>>>>
>>>>>>
>>>>>> Thanks in advance.
>>>>>>
>>>>>>
>>>>>
>>>>
>>>
>>
>
>
> --
> Thanks,
> sandeep
>
>