HIVE error while accessing Ambari views

[#1]

Below is the error while accessing ambari hive view "Table 'ambari.DS_JOBIMPL_xxx' doesn't exist" Do we need to create table prior accessing ambari hive view? I do not see any step for the same in link below - https://docs.hortonworks.com/HDPDocuments/Ambari-2.1.0.0/bk_ambari_views_guide/content/_configuring_...

[#2 · Accepted solution]

@chennuri gouri shankar

This seems to be a BUG - (Hortonworks Bug Id# BUG-40804). This issue is related to table DS_JOBIMPL_<xxxx> not being created. This is because MySql is looking for the range between 0-65535 and the create table syntax for the above table has VARCHAR(4000) 18 times which accounts to 78000 which is more than what MySql is looking for.

You can try workaround below -

WORKAROUND:  Please follow the steps below:
1. Copy the table name from the exception received. 
2. Using Mysql client, login to MySql server and go to the database using 'use <databasename>' and copy command below (replacing table name with required in 1st step)
CREATE TABLE DS_JOBIMPL_<REPLACE THIS WITH ACTUAL TABLE NAME> (
    ds_id character varying(255) NOT NULL,
    ds_applicationid character varying(2800),
    ds_conffile character varying(2800),
    ds_dagid character varying(2800),
    ds_dagname character varying(2800),
    ds_database character varying(2800),
    ds_datesubmitted bigint,
    ds_duration bigint,
    ds_forcedcontent character varying(2800),
    ds_globalsettings character varying(2800),
    ds_logfile character varying(2800),
    ds_owner character varying(2800),
    ds_queryfile character varying(2800),
    ds_queryid character varying(2800),
    ds_referrer character varying(2800),
    ds_sessiontag character varying(2800),
    ds_sqlstate character varying(2800),
    ds_status character varying(2800),
    ds_statusdir character varying(2800),
    ds_statusmessage character varying(2800),
    ds_title character varying(2800)
);


This should help to address the issue.

[#3]

@chennuri gouri shankar

This is a known issue with the Ambari version if the database used is MySQL. Manually create the required table by using the following create table statement:

CREATE TABLE DS_JOBIMPL_<REPLACE THIS WITH THE NUMBER IN THE ACTUAL TABLE NAME> ( ds_id character varying(255) NOT NULL, ds_applicationid character varying(2800), ds_conffile character varying(2800), ds_dagid character varying(2800), ds_dagname character varying(2800), ds_database character varying(2800), ds_datesubmitted bigint, ds_duration bigint, ds_forcedcontent character varying(2800), ds_globalsettings character varying(2800), ds_logfile character varying(2800), ds_owner character varying(2800), ds_queryfile character varying(2800), ds_queryid character varying(2800), ds_referrer character varying(2800), ds_sessiontag character varying(2800), ds_sqlstate character varying(2800), ds_status character varying(2800), ds_statusdir character varying(2800), ds_statusmessage character varying(2800), ds_title character varying(2800) );