Preparing to connect Db2 Big SQL to a remote Hadoop cluster

You must do several tasks before a Db2 Big SQL instance can connect to a remote Hadoop cluster.

Configure the remote file system

Do the following steps to configure the remote file system for Db2 Big SQL:

Make the HDFS NameNode and Hive metastore services accessible from the Db2 Big SQL service.
Specifically, the services must be bound to an IP address that is accessible outside the Hadoop cluster.
1. In Cloudera Manager, navigate to the HDFS service.
2. Click the Configuration tab.
3. Locate HDFS Service Advanced Configuration Snippet (Safety Valve) for hdfs-site.xml and click Add.
4. Create the property dfs.namenode.rpc-bind-host and set its value to 0.0.0.0.
In HDFS, create the /user/db2inst1 directory. An HDFS administrator must run the following commands to create the directory, make the db2inst1 user the owner, and allow the db2inst1 user to write to it:
```
hdfs dfs -mkdir /user/db2inst1
```
```
hdfs dfs -chmod 755 /user/db2inst1
```
```
hdfs dfs -chown -R db2inst1 /user/db2inst1
```

To enable automatic syncing of the Db2 Big SQL catalog and the Hive metastore, an additional subdirectory is needed. Run the following commands:

hdfs dfs -mkdir /user/db2inst1/sync

hdfs dfs -chmod 777 /user/db2inst1/sync

hdfs dfs -chown -R db2inst1:hdfs /user/db2inst1

hdfs dfs -chown -R db2inst1:hdfs /user/db2inst1/sync

Ensure that the DNS for the Red Hat® OpenShift® cluster is configured so that it can resolve hosts in the Hadoop cluster. This configuration means that a DNS entry exists for each host in the Hadoop cluster that maps the hostname to an IP address. There is also an additional requirement for reverse DNS entries when Kerberos security is enabled. For more information, see the details about updating the key distribution center (KDC) in Connecting to a Kerberos enabled cluster.
Give Db2 Big SQL access to the Hive warehouse directory.
For Db2 Big SQL to be able to read and write data on the HDFS, the db2inst1 user must be granted access privileges on the Hive warehouse directory. The specific privileges required depend on whether Db2 Big SQL impersonation is enabled or not.
If Db2 Big SQL impersonation is not enabled, the db2inst1 user requires some permissions on the warehouse directories. These permissions can be granted via a policy in the Ranger HDFS plugin for CDP or via a HDFS ACL. For example, if tables in all schemas are to be created, populated with data, altered, or dropped, run the following commands to grant the required permissions using HDFS ACLs:
```
hdfs dfs -setfacl -R -m user:db2inst1:rwx /warehouse/tablespace/managed/hive
```
```
hdfs dfs -setfacl -R -m default:user:db2inst1:rwx /warehouse/tablespace/managed/hive
```
```
hdfs dfs -setfacl -R -m user:db2inst1:rwx /warehouse/tablespace/external/hiv
```
```
hdfs dfs -setfacl -R -m default:user:db2inst1:rwx /warehouse/tablespace/external/hive
```
Note: If there are many tables in the Hive warehouse, this set of commands might take some time to run.
However, if Db2 Big SQL is to have access to only a subset of the schemas, permissions can be granted at the schema level. For example, the following commands grant all permissions on only the SALES schema to the db2inst1 user:
```
hdfs dfs -setfacl -R -m user:db2inst1:rwx /warehouse/tablespace/external/hive/sales.db
```
```
hdfs dfs -setfacl -R -m default:user:db2inst1:rwx /warehouse/tablespace/external/hive/sales.db
```
If Db2 Big SQL is to have access to tables outside of the warehouse (that is, when the LOCATION clause is specified), then the required permissions can be granted via a policy defined in the Ranger HDFS plugin, or via file access control lists (FACLs) that are similar to those shown in this section should be set for the specified directories.

Connecting to a Kerberos enabled cluster

If Kerberos security is enabled on the Hadoop cluster, you must give Db2 Big SQL access to the cluster and update the key distribution center (KDC) for Db2 Big SQL.

To connect Db2 Big SQL to a Kerberos enabled Hadoop cluster, two options are available:

Provide Kerberos administrative credentials to allow Db2 Big SQL to automatically set up its access.
Pre-generate a keytab for the db2inst1.
If you are connecting Db2 Big SQL to a Hadoop cluster that is using Active Directory as its Kerberos server, you must use this option.

Tip: Even if the Hadoop cluster is not using Active Directory, you still might want to pre-generate a keytab for the db2inst1 user to avoid providing Kerberos administrative credentials.

To pre-generate a keytab for the db2inst1 user, do the following steps:

Generate a keytab for the db2inst1 user, and copy it on a workstation that can access the Red Hat OpenShift cluster:

ktpass -princ db2inst1@REALM -out db2inst1.keytab -pass Password -mapuser db2inst1 -crypto all -ptype KRB5_NT_PRINCIPAL

When you provision a Db2 Big SQL instance, upload the keytab file when you set up a connection to the Hadoop cluster.

Db2 Big SQL uses the version of Kerberos that is shipped with Red Hat Enterprise Linux® 8.4, which supports only strong encryption ciphers. When you connect Db2 Big SQL to a Hadoop cluster with MIT Kerberos security enabled, you can use the following encryption types:

aes256-cts
aes128-cts
aes256-cts-hmac-sha1-96

For more information about how to set the ciphers to be used by Kerberos, see the Cloudera documentation page Managing Kerberos credentials using Cloudera Manager.

To update your KDC for Db2 Big SQL, do the following steps:

Edit the /var/kerberos/krb5kdc/kadm5.acl file. Change the asterisk (*) to e*. For example:
```
cat /var/kerberos/krb5kdc/kadm5.acl
*/admin@IBM.COM e*
```

Restart the KDC by running the following commands:

sudo service krb5kdc restart

sudo service kadmin restart

Note:

Db2 Big SQL obtains a new Kerberos ticket approximately every 12 hours. Therefore, the ticket lifetime defined in the Kerberos configuration (KDC, krb5) must be set to at least 16 hours.

Connecting to a TLS (SSL)-enabled Hadoop cluster

To connect Db2 Big SQL to a Hadoop cluster that uses SSL and TLS protocols, you must create a secret that contains your company's CA certificate, as documented in Using a CA certificate to connect to internal servers from the platform. You can retrieve the CA certificate from the Hadoop cluster by downloading the file /var/lib/cloudera-scm-agent/agent-cert/cm-auto-host_cert_chain.pem from any node that is running the Cloudera Manager Agent.

Do the following steps:

Download the certificate from the Hadoop cluster:

mkdir $HOME/Cert

scp root@<hadoopCluster>:/var/lib/cloudera-scm-agent/agent-cert/cm-auto-host_cert_chain.pem $HOME/Cert

Create a generic secret named connection-ca-certs.

oc create secret generic connection-ca-certs --from-file=$HOME/Cert/cm-auto-host_cert_chain.pem

Requirements for Ranger integration with Db2 Big SQL

If Ranger is enabled in IBM Cloud Object Storage when there is a connection to a remote Cloudera Data Platform (CDP) Hadoop cluster, some configuration on the remote Hadoop cluster is required.

To configure the remote Hadoop cluster, update the download.auth properties for the Hive plugin by adding db2inst1, as shown in the following screen capture. Ensure that there are no blank spaces after the commas.

Screen capture showing the download.auth properties for the Hive plugin