Hive，我如何检索所有数据库的表列 [英] Hive, how do I retrieve all the database's tables columns

问题描述

  select * from information_schema.columns where table_schema =' database_name'
  

如何访问配置单元的Metastore并检索存储在特定数据库中的所有表的所有列？我知道我们可以通过describe [table_name]来完成
的表格，但无论如何，在同一个请求中，所有
都是数据库中所有表的列吗？

解决方案

如果您希望有能力运行返回配置单元元数据的查询，你可以在MySQL中设置Hive Metastore，在Hive中使用的元数据存储在MySQL的特定帐户中。

您必须通过执行 CREATE USER'hive'@'metastorehost'IDENTIFIED BY'mypassword'。

然后你会发现像 COLUMNS_VS 。

用于检索所有表中所有列的示例查询可以是： SELECT COLUMN_NAME，TBL_NAME FROM COLUMNS_V2 c JOIN TBLS a ON c.CD_ID = a.TBL_ID

或者，您可以访问此通过REST调用信息 WebHCat 查看 wiki 了解更多信息。

I want to write the equivalent of this sql request in Hive :

select * from information_schema.columns where table_schema='database_name'

How can I access hive's metastore and retrieve all the columns of all the tables stored in a specific database? I know that we can do it by table via describe [table_name] but is there anyway to have all the columns for all the tables in a database in the same request?

解决方案

If you want to have the ability to run such queries that return hive metadata, you can setup Hive metastore with MySQL, metadata used in Hive is stored in a specific account of MySQL.

You will have to create a user of MySQL for hive by doing CREATE USER 'hive'@'metastorehost' IDENTIFIED BY 'mypassword'.

Then you will find tables like COLUMNS_VS with the info you are looking for.

An example query to retrieve all columns in all tables could be: SELECT COLUMN_NAME, TBL_NAME FROM COLUMNS_V2 c JOIN TBLS a ON c.CD_ID=a.TBL_ID

Alternatively, you can access this information via REST calls to WebHCat see wiki for more info.

这篇关于Hive，我如何检索所有数据库的表列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！