Apache Ambari Upgrade
Also available as:
PDF

Optional: Remove Duplicate Ranger Entries

Prerequisites

Register and Install Target Version.

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

  1. Verify that you can see both ranger versions (in different folders under /usr/hdp) before the upgrade:

    [Note]Note

    Build 198 in 2.6.3.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.3.0-<198>/ranger-admin/db
    ls -ltr /usr/hdp/2.6.3.0-<198>/ranger-admin/db/mysql/patches/028-delete-xgroup-duplicate-references.sql
  2. 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;.

  3. Delete duplicate groups and its references entries:

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

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

      [Note]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.3.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

  4. 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.