E-MapReduce:Use Hive to create an OSS table in an EMR cluster - E-MapReduce

Prerequisites

Before you begin, ensure that you have:

Confirmed that the role assigned to your EMR cluster has Resource Access Management (RAM) permissions to access OSS. For details, see Assign roles to an Alibaba Cloud account
(Optional) Enabled DLF-Auth if you need to map Linux, Lightweight Directory Access Protocol (LDAP), or Kerberos accounts to RAM users. By default, these accounts are not mapped to RAM users. For details, see DLF-Auth

Create an OSS database and table

The following example creates a Hive database and two tables stored in OSS, inserts data, queries results, and cleans up.

-- Create a database at an OSS path
CREATE DATABASE test_db LOCATION "oss://test_bucket/test_db";
USE test_db;

-- Create a table that inherits the database location
CREATE TABLE test_table (id INT, name STRING, age INT);
INSERT INTO test_table VALUES (1, "ab", 12);
SELECT * FROM test_table;

-- Create a table at a specific OSS path
CREATE TABLE test_table_1 (id INT, name STRING, age INT) LOCATION "oss://test_bucket/test_db/test_table_1";
INSERT INTO test_table_1 VALUES (1, "cd", 14);
SELECT * FROM test_table_1;

DROP TABLE test_table_1;
DROP DATABASE test_db;

Replace test_bucket with your OSS bucket name.

The key statements in this example:

Statement	Description
`CREATE DATABASE ... LOCATION`	Creates a Hive database mapped to an OSS path. All tables in this database store data under that path by default.
`CREATE TABLE` (without `LOCATION`)	Creates a managed table that inherits the database location. Data is stored in a subdirectory named after the table.
`CREATE TABLE ... LOCATION`	Creates a managed table at a specific OSS path, overriding the database-level location.
`INSERT INTO`	Inserts rows into the table. Data is written to the corresponding OSS path.
`SELECT *`	Queries all rows from the table.
`DROP TABLE`	Drops the table definition.

Troubleshooting

Error when an AccessKey pair is in the CREATE TABLE statement

EMR clusters do not support specifying an AccessKey pair directly in the OSS path of a CREATE TABLE statement. If you include credentials in the URI, Hive returns the following error:

hive> CREATE TABLE test_table (id INT, name STRING, age INT)
      LOCATION "oss://[accessKeyId]:[accessKeySecret]@test_bucket.oss-cn-hangzhou-internal.aliyuncs.com/test_db/test_table";

FAILED: SemanticException java.lang.IllegalArgumentException: The Filesystem URI contains login details. This authentication mechanism is no longer supported

Remove the AccessKey pair from the OSS URI and use the approach described in Create an OSS database and table.

Use an AccessKey pair to access OSS

By default, EMR clusters use the cluster role to access OSS (password-free access). The fs.oss.credentials.provider parameter in the core-site.xml file of Hadoop-Common controls this behavior.

To switch to AccessKey-based access, follow these steps.

In the core-site.xml file of Hadoop-Common, change the setting of the fs.oss.credentials.provider parameter to the following parameter settings:

<property>
  <name>fs.oss.accessKeyId</name>
  <value>yourAccessKeyID</value>
</property>
<property>
  <name>fs.oss.accessKeySecret</name>
  <value>yourAccessKeySecret</value>
</property>

In the EMR console, verify that the configuration is active by running the following commands:

# After you remove the setting of the fs.oss.credentials.provider parameter, you cannot access OSS by running the ls command.
hadoop fs -ls oss://test_bucket/test_db
# ls: ERROR: without login secrets configured.

# After the AccessKey pair is configured, OSS directories are listed
hadoop fs -ls oss://test_bucket/test_db
# drwxrwxrwx   - root root          0 2022-11-30 12:51 oss://test_bucket/test_db/student

Restart HiveServer2 and the components related to HiveMetaStore.