Querying the information_schema database

Hive supports the ANSI-standard information_schema database, which you can query for information about tables, views, columns, and your Hive privileges. The information_schema data reveals the state of the system, similar to sys database data, but in a user-friendly, read-only way. You can use joins, aggregates, filters, and projections in information_schema queries.

One of the following steps involves changing the time interval for synchronization between HiveServer and the policy. HiveServer responds to any policy changes within this time interval. You can query the information_schema database for only your own privilege information.

Open Ranger Access Manager, and check that the preloaded default database tables columns and information_schema database policies are enabled for group public.

The information schema database is synchronized every half hour by default.

From the Beeline shell, start Hive, and check for the information_schema database:

SHOW DATABASES;
...
+---------------------+
|    database_name    |
+---------------------+
| default             |
| information_schema  |
| sys                 |
+---------------------+

Use the information_schema database to list tables in the database.

USE information_schema;
...
SHOW TABLES;
...
+--------------------+
|      tab_name      |
+--------------------+
| column_privileges  |
| columns            |
| schemata           |
| table_privileges   |
| tables             |
| views              |
+--------------------+

Query the information_schema database to see, for example, information about tables into which you can insert values.

SELECT * FROM information_schema.tables WHERE is_insertable_into='YES' limit 2;
...
+--------------------+-------------------+-----------------
|tables.table_catalog|tables.table_schema|tables.table_name
+--------------------+-------------------+-----------------
|default             |default            |students2
|default             |default            |t3