Carter Shanklin created HIVE-16937:
--------------------------------------
Summary: INFORMATION_SCHEMA usability: everything is currently a
string
Key: HIVE-16937
URL: https://issues.apache.org/jira/browse/HIVE-16937
Project: Hive
Issue Type: Bug
Affects Versions: 3.0.0
Reporter: Carter Shanklin
HIVE-1010 adds an information schema to Hive, also taking the opportunity to
expose some non-standard but valuable things like statistics in a SYS table.
A challenge I have noted with the SYS table is that all statistic counts are
exposed as string types rather than numerics.
{code}
hive> show create table sys.tab_col_stats;
OK
CREATE TABLE `sys.tab_col_stats`(
`cs_id` string COMMENT 'from deserializer',
`db_name` string COMMENT 'from deserializer',
`table_name` string COMMENT 'from deserializer',
`column_name` string COMMENT 'from deserializer',
`column_type` string COMMENT 'from deserializer',
`tbl_id` string COMMENT 'from deserializer',
`long_low_value` string COMMENT 'from deserializer',
`long_high_value` string COMMENT 'from deserializer',
`double_high_value` string COMMENT 'from deserializer',
`double_low_value` string COMMENT 'from deserializer',
`big_decimal_low_value` string COMMENT 'from deserializer',
`big_decimal_high_value` string COMMENT 'from deserializer',
`num_nulls` string COMMENT 'from deserializer',
`num_distincts` string COMMENT 'from deserializer',
`avg_col_len` string COMMENT 'from deserializer',
`max_col_len` string COMMENT 'from deserializer',
`num_trues` string COMMENT 'from deserializer',
`num_falses` string COMMENT 'from deserializer',
`last_analyzed` string COMMENT 'from deserializer')
ROW FORMAT SERDE
'org.apache.hive.storage.jdbc.JdbcSerDe'
STORED BY
'org.apache.hive.storage.jdbc.JdbcStorageHandler'
{code}
So you might run this query to try and find the column(s) which have the most
distinct values.
{code}
select
db_name, table_name, column_name
from
sys.tab_col_stats
where
num_distincts = ( select max(num_distincts) from sys.tab_col_stats );
{code}
Unfortunately this maximum is based on string sorting so it's not likely what
you really want.
It would be better to use numeric types where appropriate such as all the
numbers in tab_col_stats, and most likely bigints should be used for stats like
# rows, etc.
--
This message was sent by Atlassian JIRA
(v6.4.14#64029)
