You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@hive.apache.org by Mich Talebzadeh <mi...@gmail.com> on 2017/01/16 15:37:23 UTC
VARCHAR or STRING fields in Hive
Coming from DBMS background I tend to treat the columns in Hive similar to
an RDBMS table. For example if a table created in Hive as Parquet I will
use VARCHAR(30) for column that has been defined as VARCHAR(30) as source.
If a column is defined as TEXT in RDBMS table I use STRING in Hive with a
max size of 2GB I believe.
My view is that it is more efficient storage wise to have Hive table
created as VARCHA as opposed to STRING.
I have not really seen any performance difference if one uses VARCHAR or
STRING. However, I believe there is a reason why one has VARCH in Hive as
opposed to STRRING.
What is the thread view on this?
Thanks
Dr Mich Talebzadeh
LinkedIn * https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
<https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw>*
http://talebzadehmich.wordpress.com
*Disclaimer:* Use it at your own risk. Any and all responsibility for any
loss, damage or destruction of data or any other property which may arise
from relying on this email's technical content is explicitly disclaimed.
The author will in no case be liable for any monetary damages arising from
such loss, damage or destruction.
Re: VARCHAR or STRING fields in Hive
Posted by Gopal Vijayaraghavan <go...@apache.org>.
> Sounds like VARCHAR and CHAR types were created for Hive to have ANSI SQL Compliance. Otherwise they seem to be practically the same as String types.
They are relatively identical in storage, except both are slower on the CPU in actual use (CHAR has additional padding code in the hot-path).
There is no constant form for those two types, so all string operations like say = 'NONE' would get promoted up as
UDFToString(varcharcol) = 'NONE'
Resulting in all ORC/Parquet index pushdowns being turned off due to the cast on the column & if you run an explain and notice something similar, it will cause a significant performance loss.
In general, I see 2-3x performance degradation in case of CHAR/VARCHAR when doing constant filter operations & other issues when joining different sized ops (Varchar(3) x Varchar(4) would go this route).
The default String types are faster purely because they are the destination type for any up-conversion or constant-folding conversions.
Cheers,
Gopal
Re: VARCHAR or STRING fields in Hive
Posted by Mich Talebzadeh <mi...@gmail.com>.
Sounds like VARCHAR and CHAR types were created for Hive to have ANSI SQL
Compliance. Otherwise they seem to be practically the same as String types.
HTH
Dr Mich Talebzadeh
LinkedIn * https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
<https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw>*
http://talebzadehmich.wordpress.com
*Disclaimer:* Use it at your own risk. Any and all responsibility for any
loss, damage or destruction of data or any other property which may arise
from relying on this email's technical content is explicitly disclaimed.
The author will in no case be liable for any monetary damages arising from
such loss, damage or destruction.
On 16 January 2017 at 17:15, Mich Talebzadeh <mi...@gmail.com>
wrote:
> Thanks Elliot for the insight.
>
> Another issue that Spark does not support "CHAR" types. It supports
> VARCHAR. Often one uses Spark as well on these tables.
>
> This should not really matter. I tend to define CHA(N) to be VARCHAR(N) as
> the assumption is that the table ingested into Parquet say is already
> filled with data so it does not really matter whether one defines CHAR or
> VARCHAR here.
>
> For compatibility reason I would rather use VARCHA as opposed to String to
> provide RDBMS developer a more familiar look and fill.
>
> Also if one uses SNAPPY compression on columns will a VARCHAR as opposed
> to String make any difference in terms of storage efficiency?
>
>
> Regards
>
>
>
>
>
> Dr Mich Talebzadeh
>
>
>
> LinkedIn * https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
> <https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw>*
>
>
>
> http://talebzadehmich.wordpress.com
>
>
> *Disclaimer:* Use it at your own risk. Any and all responsibility for any
> loss, damage or destruction of data or any other property which may arise
> from relying on this email's technical content is explicitly disclaimed.
> The author will in no case be liable for any monetary damages arising from
> such loss, damage or destruction.
>
>
>
> On 16 January 2017 at 17:00, Elliot West <te...@gmail.com> wrote:
>
>> Internally it looks as though Hive simply represents CHAR/VARCHAR values
>> using a Java String and so I would not expect a significant change in
>> execution performance. The Hive JIRA suggests that these types were added
>> to 'support for more SQL-compliant behavior, such as SQL string
>> comparison semantics, max length, etc.' rather than for performance reasons.
>>
>> - https://issues.apache.org/jira/browse/HIVE-4844
>> - https://issues.apache.org/jira/browse/HIVE-5191
>>
>> In terms of storage I expect it depends on the underlying file format and
>> the types that these values are encoded to. Parquet does appear to
>> support the specific encoding of both CHAR/VARCHAR, however I'm skeptical
>> that there would be significant storage efficiencies gained by using the
>> CHAR types, over String for comparable values. I'd be keen to hear
>> otherwise.
>>
>> - https://issues.apache.org/jira/browse/HIVE-7735
>>
>> Thanks,
>>
>> Elliot.
>>
>> On 16 January 2017 at 15:37, Mich Talebzadeh <mi...@gmail.com>
>> wrote:
>>
>>>
>>> Coming from DBMS background I tend to treat the columns in Hive similar
>>> to an RDBMS table. For example if a table created in Hive as Parquet I will
>>> use VARCHAR(30) for column that has been defined as VARCHAR(30) as source.
>>> If a column is defined as TEXT in RDBMS table I use STRING in Hive with a
>>> max size of 2GB I believe.
>>>
>>> My view is that it is more efficient storage wise to have Hive table
>>> created as VARCHA as opposed to STRING.
>>>
>>> I have not really seen any performance difference if one uses VARCHAR or
>>> STRING. However, I believe there is a reason why one has VARCH in Hive as
>>> opposed to STRRING.
>>>
>>> What is the thread view on this?
>>>
>>> Thanks
>>>
>>>
>>> Dr Mich Talebzadeh
>>>
>>>
>>>
>>> LinkedIn * https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
>>> <https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw>*
>>>
>>>
>>>
>>> http://talebzadehmich.wordpress.com
>>>
>>>
>>> *Disclaimer:* Use it at your own risk. Any and all responsibility for
>>> any loss, damage or destruction of data or any other property which may
>>> arise from relying on this email's technical content is explicitly
>>> disclaimed. The author will in no case be liable for any monetary damages
>>> arising from such loss, damage or destruction.
>>>
>>>
>>>
>>
>>
>
Re: VARCHAR or STRING fields in Hive
Posted by Mich Talebzadeh <mi...@gmail.com>.
Thanks Elliot for the insight.
Another issue that Spark does not support "CHAR" types. It supports
VARCHAR. Often one uses Spark as well on these tables.
This should not really matter. I tend to define CHA(N) to be VARCHAR(N) as
the assumption is that the table ingested into Parquet say is already
filled with data so it does not really matter whether one defines CHAR or
VARCHAR here.
For compatibility reason I would rather use VARCHA as opposed to String to
provide RDBMS developer a more familiar look and fill.
Also if one uses SNAPPY compression on columns will a VARCHAR as opposed to
String make any difference in terms of storage efficiency?
Regards
Dr Mich Talebzadeh
LinkedIn * https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
<https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw>*
http://talebzadehmich.wordpress.com
*Disclaimer:* Use it at your own risk. Any and all responsibility for any
loss, damage or destruction of data or any other property which may arise
from relying on this email's technical content is explicitly disclaimed.
The author will in no case be liable for any monetary damages arising from
such loss, damage or destruction.
On 16 January 2017 at 17:00, Elliot West <te...@gmail.com> wrote:
> Internally it looks as though Hive simply represents CHAR/VARCHAR values
> using a Java String and so I would not expect a significant change in
> execution performance. The Hive JIRA suggests that these types were added
> to 'support for more SQL-compliant behavior, such as SQL string
> comparison semantics, max length, etc.' rather than for performance reasons.
>
> - https://issues.apache.org/jira/browse/HIVE-4844
> - https://issues.apache.org/jira/browse/HIVE-5191
>
> In terms of storage I expect it depends on the underlying file format and
> the types that these values are encoded to. Parquet does appear to
> support the specific encoding of both CHAR/VARCHAR, however I'm skeptical
> that there would be significant storage efficiencies gained by using the
> CHAR types, over String for comparable values. I'd be keen to hear
> otherwise.
>
> - https://issues.apache.org/jira/browse/HIVE-7735
>
> Thanks,
>
> Elliot.
>
> On 16 January 2017 at 15:37, Mich Talebzadeh <mi...@gmail.com>
> wrote:
>
>>
>> Coming from DBMS background I tend to treat the columns in Hive similar
>> to an RDBMS table. For example if a table created in Hive as Parquet I will
>> use VARCHAR(30) for column that has been defined as VARCHAR(30) as source.
>> If a column is defined as TEXT in RDBMS table I use STRING in Hive with a
>> max size of 2GB I believe.
>>
>> My view is that it is more efficient storage wise to have Hive table
>> created as VARCHA as opposed to STRING.
>>
>> I have not really seen any performance difference if one uses VARCHAR or
>> STRING. However, I believe there is a reason why one has VARCH in Hive as
>> opposed to STRRING.
>>
>> What is the thread view on this?
>>
>> Thanks
>>
>>
>> Dr Mich Talebzadeh
>>
>>
>>
>> LinkedIn * https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
>> <https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw>*
>>
>>
>>
>> http://talebzadehmich.wordpress.com
>>
>>
>> *Disclaimer:* Use it at your own risk. Any and all responsibility for
>> any loss, damage or destruction of data or any other property which may
>> arise from relying on this email's technical content is explicitly
>> disclaimed. The author will in no case be liable for any monetary damages
>> arising from such loss, damage or destruction.
>>
>>
>>
>
>
Re: VARCHAR or STRING fields in Hive
Posted by Elliot West <te...@gmail.com>.
Internally it looks as though Hive simply represents CHAR/VARCHAR values
using a Java String and so I would not expect a significant change in
execution performance. The Hive JIRA suggests that these types were added
to 'support for more SQL-compliant behavior, such as SQL string comparison
semantics, max length, etc.' rather than for performance reasons.
- https://issues.apache.org/jira/browse/HIVE-4844
- https://issues.apache.org/jira/browse/HIVE-5191
In terms of storage I expect it depends on the underlying file format and
the types that these values are encoded to. Parquet does appear to support
the specific encoding of both CHAR/VARCHAR, however I'm skeptical that
there would be significant storage efficiencies gained by using the CHAR
types, over String for comparable values. I'd be keen to hear otherwise.
- https://issues.apache.org/jira/browse/HIVE-7735
Thanks,
Elliot.
On 16 January 2017 at 15:37, Mich Talebzadeh <mi...@gmail.com>
wrote:
>
> Coming from DBMS background I tend to treat the columns in Hive similar to
> an RDBMS table. For example if a table created in Hive as Parquet I will
> use VARCHAR(30) for column that has been defined as VARCHAR(30) as source.
> If a column is defined as TEXT in RDBMS table I use STRING in Hive with a
> max size of 2GB I believe.
>
> My view is that it is more efficient storage wise to have Hive table
> created as VARCHA as opposed to STRING.
>
> I have not really seen any performance difference if one uses VARCHAR or
> STRING. However, I believe there is a reason why one has VARCH in Hive as
> opposed to STRRING.
>
> What is the thread view on this?
>
> Thanks
>
>
> Dr Mich Talebzadeh
>
>
>
> LinkedIn * https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
> <https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw>*
>
>
>
> http://talebzadehmich.wordpress.com
>
>
> *Disclaimer:* Use it at your own risk. Any and all responsibility for any
> loss, damage or destruction of data or any other property which may arise
> from relying on this email's technical content is explicitly disclaimed.
> The author will in no case be liable for any monetary damages arising from
> such loss, damage or destruction.
>
>
>
Re: VARCHAR or STRING fields in Hive
Posted by Devopam Mittra <de...@gmail.com>.
Few things that might have an effect:
1. Compression (better if you are in VARCHAR with finite length, instead of
a STRING)
2. Multicharset support (like NVARCHAR)
3. LOBs from RDBMS world are more suitable to be typecast to STRING for
pure text data (not images e.g.)
regards
Devopam
On Mon, Jan 16, 2017 at 9:07 PM, Mich Talebzadeh <mi...@gmail.com>
wrote:
>
> Coming from DBMS background I tend to treat the columns in Hive similar to
> an RDBMS table. For example if a table created in Hive as Parquet I will
> use VARCHAR(30) for column that has been defined as VARCHAR(30) as source.
> If a column is defined as TEXT in RDBMS table I use STRING in Hive with a
> max size of 2GB I believe.
>
> My view is that it is more efficient storage wise to have Hive table
> created as VARCHA as opposed to STRING.
>
> I have not really seen any performance difference if one uses VARCHAR or
> STRING. However, I believe there is a reason why one has VARCH in Hive as
> opposed to STRRING.
>
> What is the thread view on this?
>
> Thanks
>
>
> Dr Mich Talebzadeh
>
>
>
> LinkedIn * https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
> <https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw>*
>
>
>
> http://talebzadehmich.wordpress.com
>
>
> *Disclaimer:* Use it at your own risk. Any and all responsibility for any
> loss, damage or destruction of data or any other property which may arise
> from relying on this email's technical content is explicitly disclaimed.
> The author will in no case be liable for any monetary damages arising from
> such loss, damage or destruction.
>
>
>
--
Devopam Mittra
Life and Relations are not binary
Re: VARCHAR or STRING fields in Hive
Posted by Mich Talebzadeh <mi...@gmail.com>.
thanks both.
String has a max length of 2GB so in a MapReduce with a 128MB block size we
are talking about 16 blocks. With VARCHAR(30) we are talking about 1 block.
I have not really experimented with this, however, I assume a table of 100k
rows with VARCHAR columns will have a smaller footprint in HDFS compared to
STRING columns?
Cheers
Dr Mich Talebzadeh
LinkedIn * https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
<https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw>*
http://talebzadehmich.wordpress.com
*Disclaimer:* Use it at your own risk. Any and all responsibility for any
loss, damage or destruction of data or any other property which may arise
from relying on this email's technical content is explicitly disclaimed.
The author will in no case be liable for any monetary damages arising from
such loss, damage or destruction.
On 16 January 2017 at 15:49, sreebalineni . <sr...@gmail.com> wrote:
> How is that efficient storage wise because as far as I see it is in hdfs
> and storage is based on your block size.
>
> Am i missing something here?
>
> On Jan 16, 2017 9:07 PM, "Mich Talebzadeh" <mi...@gmail.com>
> wrote:
>
>
> Coming from DBMS background I tend to treat the columns in Hive similar to
> an RDBMS table. For example if a table created in Hive as Parquet I will
> use VARCHAR(30) for column that has been defined as VARCHAR(30) as source.
> If a column is defined as TEXT in RDBMS table I use STRING in Hive with a
> max size of 2GB I believe.
>
> My view is that it is more efficient storage wise to have Hive table
> created as VARCHA as opposed to STRING.
>
> I have not really seen any performance difference if one uses VARCHAR or
> STRING. However, I believe there is a reason why one has VARCH in Hive as
> opposed to STRRING.
>
> What is the thread view on this?
>
> Thanks
>
>
> Dr Mich Talebzadeh
>
>
>
> LinkedIn * https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
> <https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw>*
>
>
>
> http://talebzadehmich.wordpress.com
>
>
> *Disclaimer:* Use it at your own risk. Any and all responsibility for any
> loss, damage or destruction of data or any other property which may arise
> from relying on this email's technical content is explicitly disclaimed.
> The author will in no case be liable for any monetary damages arising from
> such loss, damage or destruction.
>
>
>
>
>
Re: VARCHAR or STRING fields in Hive
Posted by "sreebalineni ." <sr...@gmail.com>.
How is that efficient storage wise because as far as I see it is in hdfs
and storage is based on your block size.
Am i missing something here?
On Jan 16, 2017 9:07 PM, "Mich Talebzadeh" <mi...@gmail.com>
wrote:
Coming from DBMS background I tend to treat the columns in Hive similar to
an RDBMS table. For example if a table created in Hive as Parquet I will
use VARCHAR(30) for column that has been defined as VARCHAR(30) as source.
If a column is defined as TEXT in RDBMS table I use STRING in Hive with a
max size of 2GB I believe.
My view is that it is more efficient storage wise to have Hive table
created as VARCHA as opposed to STRING.
I have not really seen any performance difference if one uses VARCHAR or
STRING. However, I believe there is a reason why one has VARCH in Hive as
opposed to STRRING.
What is the thread view on this?
Thanks
Dr Mich Talebzadeh
LinkedIn * https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
<https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw>*
http://talebzadehmich.wordpress.com
*Disclaimer:* Use it at your own risk. Any and all responsibility for any
loss, damage or destruction of data or any other property which may arise
from relying on this email's technical content is explicitly disclaimed.
The author will in no case be liable for any monetary damages arising from
such loss, damage or destruction.