Optional: Remove Duplicate Ranger Entries

Prerequisites

About this task

HDP 2.6.3 introduces unique constraints on a few tables in Ranger DB. Depending upon your environment, Ranger DB may contain duplicate data in these tables prior to the upgrade. To make the upgrade faster, you can manually delete this duplicate data in Ranger DB before performing the upgrade.

These steps are optional, but recommended, and only needed for Ranger users. These should be performed after registering and installing the target HDP version but before actually performing the upgrade.

Steps

Verify that you can see both ranger versions (in different folders under /usr/hdp) before the upgrade:
Note
Build 198 in 2.6.4.0-198 is an example; note the actual build version from the repository. Use the patch files found in the new version folder for this step.
```
ls -ltr /usr/hdp
ls -ltr /usr/hdp/current/ranger-*
ls -ltr /usr/hdp/2.6.4.0-<198>/ranger-admin/db
ls -ltr /usr/hdp/2.6.4.0-<198>/ranger-admin/db/mysql/patches/028-delete-xgroup-duplicate-references.sql
```
Check for duplicate entries in x_group and x_group_user table:
1. If there are duplicate groups entries in the x_group table, this sql command will return the list of groups that are duplicates: SELECT group_name,count(1) duplicateCount FROM x_group GROUP BY group_name HAVING duplicateCount>1;.
2. If there are duplicate group-user mapping entries in the x_group_users table, this sql command will return the list of group-user mapping that are duplicates: SELECT group_name,user_id,count(1) duplicateCount FROM x_group_users GROUP BY group_name,user_id HAVING duplicateCount>1;.

Delete duplicate groups and its references entries:

Change your working directory to the current ranger-admin directory: cd /usr/hdp/current/ranger-admin.

Locate the SQL patch files in the new ranger location, according to your DB flavor:

	Note
	In these examples, the location of patch `028-delete-xgroup-duplicate-references.sql` at the new ranger location of my cluster: `/usr/hdp/2.6.4.0-198/ranger-admin/db/mysql/patches/028-delete-xgroup-duplicate-references.sql`

MySQL DB:
java -cp /usr/share/java/mysql-connector-java.jar:/usr/hdp/current/ranger-admin/jisql/lib/* org.apache.util.sql.Jisql -driver mysqlconj -cstring jdbc:mysql://localhost/ranger -u 'rangeradmin' -p 'password' -noheader -trim -c \; -input /path/to/db/mysql/patches/028-delete-xgroup-duplicate-references.sql
Oracle DB:
java -Djava.security.egd=file:///dev/urandom -cp /usr/share/java/ojdbc6.jar:/usr/hdp/current/ranger-admin/jisql/lib/* org.apache.util.sql.Jisql -driver oraclethin -cstring jdbc:oracle:thin:@localhost -u 'rangeradmin' -p 'password' -noheader -trim -input /path/to/db/oracle/patches/028-delete-xgroup-duplicate-references.sql -c /
Postgres DB:
java -cp /usr/share/java/postgresql.jar:/usr/hdp/current/ranger-admin/jisql/lib/* org.apache.util.sql.Jisql -driver postgresql -cstring jdbc:postgresql://localhost/ranger -u rangeradmin -p 'password' -noheader -trim -c \; -input /path/to/db/posgres/patches/028-delete-xgroup-duplicate-references.sql
SQL Anywhere DB:
java -cp /opt/sqlanywhere17/java/sajdbc4.jar:/usr/hdp/current/ranger-admin/jisql/lib/* org.apache.util.sql.Jisql -user rangeradmin -password 'password' -driver sapsajdbc4 -cstring jdbc:sqlanywhere:database=ranger;host=localhost -noheader -trim -input /path/to/db/sqlanywhere/patches/028-delete-xgroup-duplicate-references.sql
MSSQL/SQL Server DB:
java -cp /usr/share/java/sqljdbc4.jar:/usr/hdp/current/ranger-admin/jisql/lib/* org.apache.util.sql.Jisql -user rangeradmin -p ‘password’ -driver mssql -cstring jdbc:sqlserver://localhost\;databaseName=ranger -noheader -trim -input /path/to/db/sqlserver/patches/028-delete-xgroup-duplicate-references.sql

Verify that the duplicate entries from the x_group and x_group_user table have been deleted:

mysql> use ranger;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> SELECT group_name,user_id,count(1) duplicateCount FROM x_group_users GROUP BY group_name,user_id HAVING duplicateCount>1;
Empty set (0.00 sec)

mysql> SELECT group_name,count(1) duplicateCount FROM x_group GROUP BY group_name HAVING duplicateCount>1;
Empty set (0.00 sec)

Next steps

Perform the Upgrade

	Note
	Build 198 in `2.6.4.0-198` is an example; note the actual build version from the repository. Use the patch files found in the new version folder for this step.

​Optional: Remove Duplicate Ranger Entries

Optional: Remove Duplicate Ranger Entries