Using Apache HiveQL
Also available as:
PDF

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

  • You used Ambari to install HDP 3.0 or later.
  • In Ambari, you added, configured, and started the Ranger service, which makes the information_schema database accessible and sets up an access policy for the Hive user.

One of the steps in this task 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.

  1. In Ambari, open the Ranger Access Manager at <node URI>:6080, and check that access policies exist for the hive user.
  2. Navigate to Hive > Advanced > Custom hive-site.
  3. Add the hive.privilege.synchronizer.interval key and set the value to 1.
    This setting changes the synchronization from the default one-half hour to one minute.
  4. From the Beeline shell, start Hive, and check that Ambari installed the information_schema database:
    SHOW DATABASES;
    ...
    +---------------------+
    |    database_name    |
    +---------------------+
    | default             |
    | information_schema  |
    | sys                 |
    +---------------------+
  5. 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              |
    +--------------------+
  6. 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