Apache Drill无法列出Hive数据库中的表问题咨询

Hey there! Let's work through your Drill-Hive issue and break down the connection mechanics too.

First, let's tackle the "can see databases but not tables" problem—permissions are definitely a likely culprit here, but we need to check a few layers:

• Hive Metastore Permissions: Drill uses the user account it's running under to query Hive's metastore. Make sure this user has the right privileges on your target database. In Hive, run:

  SHOW GRANTS FOR 'drill_service_user'@'%';
  

  (Replace drill_service_user with the actual user starting your Drill cluster.) If you don't see SELECT and SHOW_TABLES permissions for your database, grant them with an admin account:

  GRANT SELECT, SHOW_DATABASES ON DATABASE your_target_db TO 'drill_service_user'@'%';
  

• HDFS/Storage Layer Permissions: Hive tables are backed by files in HDFS (or other storage). Even if the metastore lets Drill see the table metadata, Drill needs read access to the actual data directories. Check the permissions on your database's warehouse path:

  hdfs dfs -ls /user/hive/warehouse/your_target_db.db
  

  If the Drill user can't read these directories, adjust the permissions (stick to least-privilege principles instead of broad 755):

  hdfs dfs -chmod -R 750 /user/hive/warehouse/your_target_db.db
  hdfs dfs -chown -R hive:drill_group /user/hive/warehouse/your_target_db.db
  

• Metastore Security Add-ons: If you're using Kerberos or Ranger for Hive security:

  • For Kerberos: Ensure Drill's hive-site.xml (in Drill's config directory) has the correct Kerberos credentials and hive.metastore.kerberos.principal set.
  • For Ranger: Verify that the Drill user has been granted access to the target tables/databases in the Ranger policy editor.

• Drill Metadata Cache: Sometimes Drill caches outdated metastore info. Try refreshing the metadata for your database:

  REFRESH DATABASE your_target_db;
  

Let's break down the flow so you understand where things might go wrong:

1. Metadata Lookup: When you run SHOW DATABASES or USE your_db, Drill talks directly to the Hive Metastore (using the hive.metastore.uris configured in your Drill Hive storage plugin). It fetches database and table metadata from the metastore's backend database (usually MySQL/PostgreSQL). This is why you can see databases but not tables—if metastore permissions are partial, it might return databases but filter table entries.

2. Data Access: When you run SHOW TABLES or query a table, Drill does two critical things:

   • It fetches the full table metadata (storage format, file paths, schema) from the metastore.
   • It directly reads the table's data files from the underlying storage (HDFS/S3, etc.) using its own storage engine. If the Drill user lacks read permissions on these files, Drill won't display the tables (or will throw errors when attempting to query them).

The key takeaway: Drill doesn't rely on Hive's query engine—it uses Hive's metastore for metadata, but accesses the actual data independently. So permissions need to be set correctly both in the metastore and on the storage layer.

内容的提问来源于stack exchange，提问作者user9429934