CODEWITHSUNDEEP
sqlsql-servercounthivedistinct
Gyan Veda
Gyan Veda

Reputation: 6599

Count Number of Columns In Hive

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'

Upvotes: 7

Views: 31005

Answers (5)

Sied Zarrinsaray
Sied Zarrinsaray

Reputation: 136

Try this

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

Upvotes: 12

Gaurav Yadav
Gaurav Yadav

Reputation: 1

Just do a describe it will show you all columns then at the bottom then you can see number of rows it fetched that is number of columns.

Upvotes: 0

oskar
oskar

Reputation: 19

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.)

Upvotes: 1

user3645070
user3645070

Reputation: 51

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

DESCRIBE schemaName.tableName;

Upvotes: 5

user3095691
user3095691

Reputation: 1

you have to check if your HIVE include HIVE-287 because for versions of HIVE which don't include HIVE-287, you'll need to use COUNT(1) in place of COUNT(*).

Upvotes: 0

Related Questions