E-MapReduce:Connect to an external Hive Metastore service

Step 1: Prepare Hive Metastore service

1. On the EMR on ECS page, create a DataLake cluster that includes the Hive service and set Metadata to Built-in MySQL. For more information, see Create a cluster.

2. Use SSH to log on to the cluster's master node. For more information, see Log on to a cluster.

3. Run the following command to enter the Hive CLI:

   hive

4. Run the following commands to create a table named dw_users in Object Storage Service (OSS) and insert data into it:

   CREATE TABLE `dw_users`(
     `name` string)
   LOCATION
     'oss://<yourBucket>/path/to/file';
   INSERT INTO dw_users select 'Bob';

Step 2: Add a network connection

1. Go to the network connection page.

   1. Log on to the EMR console.

   2. In the left-side navigation pane, choose EMR Serverless > Spark.

   3. On the Spark page, click the name of the target workspace.

   4. On the EMR Serverless Spark page, click Normal Network Connection in the left-side navigation pane.

2. On the Normal Network Connection page, click Create Network Connection.

3. In the Create Network Connection dialog box, configure the following parameters and click OK.

   Parameter	Description
   Name	Enter a name for the new connection.
   VPC	Select the same VPC as the EMR cluster.
   vSwitch	Select the vSwitch that is in the same VPC as the EMR cluster.

   A Status of Succeeded indicates that the network connection has been added.

Step 3: Open the Hive Metastore service port

1. Obtain the CIDR block of the vSwitch for the network connection.

   You can log on to the VPC console and go to the vSwitch page to obtain the CIDR block of the vSwitch.

2. Add a security group rule.

   1. Log on to the EMR on ECS console.

   2. On the EMR on ECS page, click the ID of the target cluster.

   3. On the Basic Information page, click the link next to Cluster Security Group.

   4. On the Security Group page, click Add Rule. Specify the Source and Port, and then click OK.

      Parameter	Description
      Port	Enter 9083.
      Source	Enter the CIDR block of the vSwitch that you obtained in the preceding step. Important To prevent security risks from external attacks, do not set the Source to 0.0.0.0/0.

Step 4: Connect to Hive Metastore

1. On the EMR Serverless Spark page, click Catalogs in the left-side navigation pane.

2. On the Catalogs page, click Add Catalog.

3. In the dialog box that appears, click External Hive Metastore, configure the following information, and then click OK. On the External Hive Metastore tab, enter hive_metastore in the Data Catalog Name field, and configure the following parameters.

   Parameter	Description
   Normal Network Connection	Select the network connection that you added in Step 2.
   Metastore service address	The URI of the Hive Metastore service. The format is thrift://<IP address of Hive metastore>:9083. The <IP address of Hive metastore> is the internal IP of the master node in your EMR on ECS cluster. You can find this IP address on the cluster's Nodes page. Note If your Hive Metastore service uses high availability (HA), enter the URIs for all relevant nodes, separated by commas (,). For example: thrift://<IP address of Hive metastore 1>:9083,thrift://<IP address of Hive metastore 2>:9083.
   Kerberos keytab file	The path to the Kerberos keytab file.
   Kerberos principal	The name of the Principal in the keytab file, which is used to authenticate with the Kerberos service. You can use the klist -kt <keytab_file> command to view the name of the Principal in the target keytab file.

Step 5: Use Hive Metastore

1. On the Catalogs page, find the hive_metastore entry and click Set as default in the Actions column to set it as the default data catalog for the workspace.

2. Restart the SQL session.

   If an SQL session is running in your workspace, you must restart it for the hive_metastore settings to take effect.

3. Query data from the hive_metastore table in your SparkSQL job.

   1. Create a SparkSQL development job. For more information, see SparkSQL development.

   2. Run the following command to query the dw_users table in the Hive Metastore service:

      SELECT * FROM dw_users;

      The query returns one record, with the value Bob in the name column.

FAQ