本地Windows系统下配置Hive以Azure Data Lake为数据仓库的方法

Alright, let's get your Hive setup (running on Windows with MySQL metastore) connected to Azure Data Lake Storage (ADLS) Gen2 as the data warehouse. I'll break this down into clear, actionable steps:

First, make sure you have these ready:

• An active ADLS Gen2 storage account with a container created (this is where your Hive table data will live).
• Authentication credentials for ADLS: either a service principal (recommended for production) or a SAS token (for quick testing).
• Matching versions of Hadoop's Azure storage JAR files (Hive relies on Hadoop to interact with ADLS). For example, if you're using Hive 3.x, grab Hadoop 3.x-compatible JARs.

Hive needs specific JARs to communicate with ADLS. Here's what to do:

1. Download these JARs (match your Hadoop version):
   • hadoop-azure-<version>.jar
   • azure-storage-<version>.jar
   • azure-core-<version>.jar
   • hadoop-common-<version>.jar
2. Copy all these JARs into your Hive lib folder, typically located at %HIVE_HOME%\lib (replace %HIVE_HOME% with your actual Hive installation path).

Head to %HIVE_HOME%\conf and open hive-site.xml in a text editor. Add the following configurations to point Hive to ADLS and set up authentication.

Set Default Warehouse Location #

Replace the existing hive.metastore.warehouse.dir property (or add it if missing) with your ADLS path:

<property>
  <name>hive.metastore.warehouse.dir</name>
  <value>abfs://<your-container-name>@<your-storage-account>.dfs.core.windows.net/hive/warehouse</value>
  <description>Default storage location for Hive managed tables</description>
</property>

Configure Authentication (Service Principal) #

This is the recommended method for secure access. Replace the placeholder values with your service principal details:

<!-- ADLS Gen2 Service Principal Auth -->
<property>
  <name>fs.azure.account.auth.type.<your-storage-account>.dfs.core.windows.net</name>
  <value>OAuth</value>
</property>
<property>
  <name>fs.azure.account.oauth.provider.type.<your-storage-account>.dfs.core.windows.net</name>
  <value>org.apache.hadoop.fs.azurebfs.oauth2.ClientCredsTokenProvider</value>
</property>
<property>
  <name>fs.azure.account.oauth2.client.id.<your-storage-account>.dfs.core.windows.net</name>
  <value>your-service-principal-client-id</value>
</property>
<property>
  <name>fs.azure.account.oauth2.client.secret.<your-storage-account>.dfs.core.windows.net</name>
  <value>your-service-principal-client-secret</value>
</property>
<property>
  <name>fs.azure.account.oauth2.client.endpoint.<your-storage-account>.dfs.core.windows.net</name>
  <value>https://login.microsoftonline.com/<your-tenant-id>/oauth2/token</value>
</property>

Alternative: SAS Token Authentication (For Testing) #

If you prefer a quick test without setting up a service principal, use these configs instead:

<!-- ADLS Gen2 SAS Token Auth -->
<property>
  <name>fs.azure.account.auth.type.<your-storage-account>.dfs.core.windows.net</name>
  <value>SAS</value>
</property>
<property>
  <name>fs.azure.sas.token.provider.type.<your-storage-account>.dfs.core.windows.net</name>
  <value>org.apache.hadoop.fs.azurebfs.sas.FixedSASTokenProvider</value>
</property>
<property>
  <name>fs.azure.sas.fixed.token.<your-storage-account>.dfs.core.windows.net</name>
  <value>your-full-sas-token</value>
</property>

Before testing Hive, confirm that Hadoop can connect to your ADLS account. Open a command prompt and run:

hadoop fs -ls abfs://<your-container-name>@<your-storage-account>.dfs.core.windows.net/

If this command returns a list of directories/files in your ADLS container, the connection is working. If not, double-check your authentication credentials and JAR versions.

Now let's confirm Hive is using ADLS for its warehouse:

1. Launch the Hive CLI by running hive in your command prompt.
2. Create a test managed table:

   CREATE TABLE adls_test (id INT, username STRING) 
   ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
   

3. Insert some test data:

   INSERT INTO adls_test VALUES (1, 'john_doe'), (2, 'jane_smith');
   

4. Query the table to confirm data is accessible:

   SELECT * FROM adls_test;
   

5. Head to your ADLS container in the Azure portal—you should see a hive/warehouse/adls_test directory with the data files inside.

• JAR Version Mismatch: If you get ClassNotFoundException errors, ensure your Azure JARs match the Hadoop version that Hive is using. Check %HADOOP_HOME%\lib to confirm Hadoop's version.
• Permission Errors: For service principals, make sure the principal has the Storage Blob Data Contributor role assigned to your ADLS storage account or container.
• Authentication Failures: Double-check all service principal/SAS token values—typos in client IDs, secrets, or tenant IDs are a common culprit.
• Windows Environment Variables: Ensure HADOOP_HOME is correctly set in your system environment variables, as Hive relies on Hadoop's configuration files.

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