hive view can not used for " S020 Data storage error "

[#1]

In HDP-2.3.2.0-2950 version , query in hive view , throw error " S020 Data storage error ". I check ambari server log, it show

exception ( ambari.DS_JOBIMPL_8 not exist) ,how to fix it ?

-----------------------------------------------------------------------------------------------------------------------------------------------------------------

05 Jan 2016 18:25:20,091 ERROR [qtp-client-148092] DataStoreImpl:647 - Caught exception trying to store view entity org.apache.ambari.view.hive.resources.jobs.viewJobs.JobImpl@67e
Local Exception Stack: 
Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.5.2.v20140319-9ad6abd): org.eclipse.persist: ence.exceptions.DatabaseException
Internal Exception: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'ambari.DS_JOBIMPL_8' doesn't exist
Error Code: 1146
Call: SELECT DS_id, DS_applicationId, DS_confFile, DS_dagId, DS_dagName, DS_dataBase, DS_dateSubmitted, DS_duration, DS_forcedContent, DS_globalSettings, DS_logFile, DS_owner, DS_queryFile, DS_queryId, DS_referrer, DS_sessionTag, DS_sqlState, DS_status, DS_statusDir, DS_statusMessage, DS_title FROM DS_JOBIMPL_8 WHERE (DS_id = ?)
bind => [1 parameter bound]
Query: ReadObjectQuery(name="readDS_JobImpl_8" referenceClass=DS_JobImpl_8 sql="SELECT DS_id, DS_applicationId, DS_confFile, DS_dagId, DS_dagName, DS_dataBase, DS_dateSubmitted, DS_duration, DS_forcedContent, DS_globalSettings, DS_logFile, DS_owner, DS_queryFile, DS_queryId, DS_referrer, DS_sessionTag, DS_sqlState, DS_status, DS_statusDir, DS_statusMessage, DS_title FROM DS_JOBIMPL_8 WHERE (DS_id = ?)")
at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:340)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:682)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:558)
at org.eclipse.persistence.internal.sessions.AbstractSession.basicExecuteCall(AbstractSession.java:2002)
at org.eclipse.persistence.sessions.server.ServerSession.executeCall(ServerSession.java:570)
at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:242)
at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:228)

[#2 · Accepted solution]

I encountered the same issue before. The below resolved it

ROOT CAUSE: This is a limitation with the Mysql DB itself with varchar accounting to more than 75000 maximum row size where as the mysql DB can accept only 65535 as its max value.

SOLUTION: Re-create table in the MySql Ambari db by running the below and then retrying the query in Hive view

CREATE TABLE DS_JOBIMPL_2 ( ds_id VARCHAR(255) NOT NULL, ds_applicationid TEXT, ds_conffile TEXT, ds_dagid TEXT, ds_dagname TEXT, ds_database TEXT, ds_datesubmitted BIGINT, ds_duration BIGINT, ds_forcedcontent TEXT, ds_globalsettings TEXT, ds_logfile TEXT, ds_owner TEXT, ds_queryfile TEXT, ds_queryid TEXT, ds_referrer TEXT, ds_sessiontag TEXT, ds_sqlstate TEXT, ds_status TEXT, ds_statusdir TEXT, ds_statusmessage TEXT, ds_title TEXT, PRIMARY KEY (ds_id) )

[#3]

right you are!

[#4]

For people having a similar issue with ambariserver.DS_JOBIMPL_4:

CREATE TABLE ambariserver.DS_JOBIMPL_4 ( 
DS_id VARCHAR(255) NOT NULL, 
S_applicationId TEXT, 
DS_confFile TEXT, 
DS_dagId TEXT, 
DS_dagName TEXT, 
DS_dataBase TEXT, 
DS_dateSubmitted BIGINT, 
DS_duration BIGINT,
DS_forcedContent TEXT,
DS_globalSettings TEXT,
DS_guid TEXT, 
DS_hiveQueryId TEXT, 
DS_logFile TEXT, 
DS_owner TEXT, 
DS_queryFile TEXT, 
DS_queryId TEXT,
DS_referrer TEXT, 
DS_sessionTag TEXT,
DS_sqlState TEXT, 
DS_status TEXT, 
DS_statusDir TEXT, 
DS_statusMessage TEXT, 
DS_title TEXT,
DS_applicationId TEXT, 
PRIMARY KEY (ds_id));
<br>