Query to show all column, table and schema names together in IMPALA

I want to get metadata of impala db in one query. Probably It will be like

SELECT columnname,tablename,schemaname from SYSTEM.INFO

Is there a way to do that? and I dont want to fetch only current tables columns for example;

SHOW COLUMN STATS db.table_name

This query is not answer of my question. I want to select all metadata in one query.

Answer

From impala-shell you have commands like:

describe table_name

describe formatted table_name

describe database_name

EXPLAIN { select_query | ctas_stmt | insert_stmt }

and the SHOW Statement that is a flexible way to get information about different types of Impala objects. You can follow this link to the Impala documentation SHOW statement.

On the other hand, information about the schema objects is held in the metastore database. This database is shared between Impala and Hive.

In particular, Impala keeps its table definitions in a traditional MySQL or PostgreSQL database known as the metastore, the same database where Hive keeps this type of data. Thus, Impala can access tables defined or loaded by Hive, as long as all columns use Impala-supported data types, file formats, and compression codecs.

If you want to query this information in one shot you would have to query to MySQL, PostgreSQL, Oracle, or etc, it’s depending on your particular case.

For example, in my case Impala keeps metadata in MySQL.

use metastore;
-- Database changed
SHOW tables;
+---------------------------+
| Tables_in_metastore       |
+---------------------------+
| BUCKETING_COLS            |
| CDS                       |
| COLUMNS_V2                |
| COMPACTION_QUEUE          |
| COMPLETED_TXN_COMPONENTS  |
| DATABASE_PARAMS           |
| DBS                       |
.......
........
| TAB_COL_STATS             |
| TBLS                      |
| TBL_COL_PRIVS             |
| TBL_PRIVS                 |
| TXNS                      |
| TXN_COMPONENTS            |
| TYPES                     |
| TYPE_FIELDS               |
| VERSION                   |
+---------------------------+
54 rows in set (0.00 sec)

SELECT * FROM VERSION;

+--------+----------------+----------------------------+-------------------+
| VER_ID | SCHEMA_VERSION | VERSION_COMMENT            | SCHEMA_VERSION_V2 |
+--------+----------------+----------------------------+-------------------+
|      1 | 1.1.0          | Hive release version 1.1.0 | 1.1.0-cdh5.12.0   |
+--------+----------------+----------------------------+-------------------+
1 row in set (0.00 sec)

Hope this helps.