Count Number of Columns In Hive

Question

I am looking for a way to count the number of columns in a table in Hive.

I know the following code works in Microsoft SQL Server. Is there a Hive equivalent?

SELECT COUNT(*),
  FROM INFORMATION_SCHEMA.COLUMNS
 WHERE TABLE_CATALOG = 'database_name'
   AND TABLE_SCHEMA = 'schema_name'
   AND TABLE_NAME = 'table_name'

Solution 1

Try this

SHOW COLUMNS (FROM|IN) table_name [(FROM|IN) db_name]

Solution 2

Try this, it will show you the columns of your table:

DESCRIBE schemaName.tableName;

Solution 3

I do not know of a way to count the columns directly, however, I solved the problem for my needs indirectly via:

echo 'table1name:, '`hive -e 'describe schemaname.table1name;' | grep -v 'col_name' | wc -l > num_columns.csv
echo 'table2name:, '`hive -e 'describe schemaname.table2name;' | grep -v 'col_name' | wc -l >> num_columns.csv
...

(I needed the grep -v bit because I have headers on by default; without it you get one too many lines counted in the wc -l step.)

Comments

[Gyan Veda]

Thank you for your response! Are you saying that columns are counted with COUNT(1) in Hive? What would be the rest of the code? The above code doesn't work.

[user3095691]

I'm saying that you need use COUNT(1) if your HIVE version don't include HIVE-287. Then you have to download this the patch from issues.apache.org/jira/browse/HIVE-287. if you don't want to download the patch or you have HIVE-287 and the code doesn't work, use something like this: SELECT col1, col2, count(1) FROM table GROUP BY col1, col2

[Gyan Veda]

Thanks for clarifying, but your suggestions output the number of rows in my table. I'm trying to find the number of columns.

[J Maurer]

At the end of the SHOW COLUMNS command, it shows the number of rows returned, which indicates the number of columns, so this answer is correct.

[Srekk]

Even 'Describe db_name.table_name;' will give the count in similar way.

[sAguinaga]

but not the count