The problem has been resolved by deleting Hive service and adding it back. The health test of HMS failed initially - the metastore canary failed to drop the table it created. Then it became good after a few minutes. The "show tables" command works in the Hive shell now.

The only mistake or cause I can think of is that we started panic after seeing the red/bad health test result right after the install. Then, we troubleshot and edited hive-site.xml by following Configuring the Hive Metastore instruction in the CDH installation guide, which is not necessary and probably caused the issue.