Migrate Hive saved queries from Hue 3.9.0 of CDH 5.7.2 into Hive Views of Ambari 2.6.2.2

[#1]

Short Description

Migrate Hive saved queries from Hue 3.9.0 of CDH 5.7.2 into Hive Views of Ambari 2.6.2.2 on HDP 2.6.5

Environment: CentOS 7, CDH 5.7.2, Hue 3.9.0, Ambari 2.6.2, HDP 2.6.2, Hue 2.6.2, mySQL5.6

Problem: There are over 40,000 saved / history queries in CDH-Hue. During CDH to HDP migration, Hive saved queries from Hue needs to be migrated to Ambari Hive View saved queries.

Assumptions: You have Hue of CDH configured with MySQL and CDH to HDP migration is successful.

Solution

Step 1: Install Hue 2.6.2 on HDP cluster pointing to a new database called hue_new262

follow the link HDP-2.6.5 - installing_hue

Step 2: Insert records from hue_cdh (database of Hue CDH) to hue_new262 (database of Hue HDP)

Impacted tables auth_user, auth_user_groups, beeswax_savedquery, beeswax_queryhistory

insert into hue_new262.auth_user (id, username, first_name, last_name, email, password, is_staff, is_active, is_superuser, last_login, date_joined) select id, username, first_name, last_name, email, password, is_staff, is_active, is_superuser, last_login, date_joined from hue_cdh.auth_user;
insert into hue_new262.auth_user_groups (id, user_id, group_id) select id, user_id, group_id from hue_cdh.auth_user_groups;
insert into hue_new262.beeswax_savedquery (name, type, is_auto, mtime, owner_id, data, id, `desc`, is_trashed) select name, type, is_auto, mtime, owner_id, data, id, 'desc', is_trashed from hue_cdh.beeswax_savedquery;
insert into hue_new262.beeswax_queryhistory (submission_date, last_state, server_id, log_context, design_id, owner_id, query, has_results, id, notify, server_name, server_host, server_port, server_type, server_guid, operation_type, modified_row_count, statement_number, query_type, result_rows, result_size, query_stats, session_id, cache_tag) select submission_date, last_state, server_id, log_context, design_id, owner_id, query, has_results, id, notify, server_name, server_host, server_port, server_type, server_guid, operation_type, modified_row_count, statement_number, query_type, NULL, NULL, NULL, NULL, NULL from hue_cdh.beeswax_queryhistory;

Step 3: Migrating Hue Artifacts to Ambari Views

Follow instructions migrating_hue_to_ambari_views to configure HueToAmbariMigration

Issues faced

1. If we want to create new Hive View (ex: HueView) then make sure you open the HueView at least once before starting the migration. Otherwise DS_SAVEDQUERY_* table not found error would occur.

2. If we try to open Hue UI, the login will fail as the stored password in table auth_user in Hue 3.9.0 is different from Hue 2.6.2. So do not try to logon to Hue or do not insert the auth_user and auth_user_groups. Create the users as new users in Hue 2.6.2 UI but make sure the order (column id is auto increment) of user creation is same as CDH Hue user creation order.

[#2]

If we don't want to install Hue on HDP then we can insert the queries manually to Ambari Hive Views by following the below.

INSERT INTO ambari_db.DS_SAVEDQUERY_9 VALUES ("4", "default", "admin", "/user/admin/hive/scripts/hive-query-4.hql", "select * from sample_07 limit 15;", "sample_07_manual2" );

echo "select * from sample_07 limit 15;" >> hive-query-4.hql

su - hdfs
hdfs dfs -put hive-query-4.hql /user/admin/hive/scripts/hive-query-4.hql
hdfs dfs -chown admin:hadoop /user/admin/hive/scripts/hive-query-4.hql