Configuring Hive access for Ozone using S3A

Learn about how to configure Hive access for Ozone using S3A.

You must import the CA certificate to run Accessing Ozone S3 using S3A FileSystem.
Perform the following steps to add the following configurations to S3 configurations for Ozone access:
important
Make sure you do not add any S3 credentials.
1. Sign in to Cloudera Manager.
2. Navigate to Clusters > Hive > Configurations > Hive Service Advanced Configuration Snippet (Safety Valve) for hive-site.xml.
3. Add the following properties:
```
fs.s3a.endpoint = <Ozone S3 endpoint URL>
fs.s3a.bucket.probe = 0
fs.s3a.change.detection.version.required = false
fs.s3a.path.style.access = true
fs.s3a.change.detection.mode = none
```
4. Navigate to Clusters > Hive_On_Tez > Configurations > Hive Service Advanced Configuration Snippet (Safety Valve) for hive-site.xml.
5. Add the following properties:
```
fs.s3a.endpoint = <Ozone S3 endpoint URL>
fs.s3a.bucket.probe = 0
fs.s3a.change.detection.version.required = false
fs.s3a.path.style.access = true
fs.s3a.change.detection.mode = none
```
6. Restart the Stale services on the Clusters.
7. Navigate to Hive Service > Actions and click Deploy client configuration.
Provide the required permissions in Ranger to the user running the queries.
Example: Provide a user with all the permissions:
- Assign the user with all permissions to the Database, table/udf, and URL resources in a HadoopSQL resource-based policy.
- Assign the user with S3_VOLUME_POLICY in an Ozone policy.
You can change the permissions based on your requirements.

Perform the following steps to sign in to the Hive shell, create a Hive table using S3A, add data to the table, and view the added data. You can perform the same procedure by signing on to Hue using the Hive or Beeline shell.

Create an Ozone bucket by running the following command:
```
ozone sh bucket create /s3v/[***BUCKET-NAME***]
```
Example: The following example shows how you can create a bucket named s3hive:
```
ozone sh bucket create /s3v/s3hive
```

Create a table on Ozone using S3A.

jdbc:hive2://> create external table mytable1(key string, value int) location 's3a://s3hive/mytable1';

Add the data to the table.

jdbc:hive2://> insert into mytable1 values("cldr",1);
jdbc:hive2://> insert into mytable1 values("cldr-cdp",1);

View the data added to the table.

jdbc:hive2://> select * from mytable1;

Example,

+-------------------+---------------------+
| mytable1.key  | mytable1.value  |
+------------------+----------------------+
| cldr             |     1                 |
+------------------+----------------------+
| cldr-cdp         |     1                 |
+------------------+----------------------+