Ambari Hive View - User's Saved Queries Location

[#1]

In the Ambari Hive view, there is a "Saved Queries" tab, where are these queries saved? Are they in the Ambari DB? A local file on the Ambari node? HDFS?

[#2]

@Jon Maestas

Please check the following directory in the HDFS. Files with name "*.hql". More information about the metadata of those files you can find in the ambari DB. With tables starting with name "ds_jobimpl_*" .

These tables ("ds_jobimpl_*") contains column like "ds_queryfile" which contains the path of those queries.

/user/admin/hive/jobs/

[#3]

Thanks @jss! Which column in ds_jobimpl_* references that the query is a saved one?

The user doesn't have 2100+ saved queries. This looks more like job history.

select count(*) from ds_jobimpl_6 where ds_owner = 'xxxxxxx';
2182

 \d ds_jobimpl_6
 ds_id             | character varying(255)  | not null
 ds_applicationid  | character varying(3000) |
 ds_conffile       | character varying(3000) |
 ds_dagid          | character varying(3000) |
 ds_dagname        | character varying(3000) |
 ds_database       | character varying(3000) |
 ds_datesubmitted  | bigint                  |
 ds_duration       | bigint                  |
 ds_forcedcontent  | character varying(3000) |
 ds_globalsettings | character varying(3000) |
 ds_logfile        | character varying(3000) |
 ds_owner          | character varying(3000) |
 ds_queryfile      | character varying(3000) |
 ds_queryid        | character varying(3000) |
 ds_referrer       | character varying(3000) |
 ds_sessiontag     | character varying(3000) |
 ds_sqlstate       | character varying(3000) |
 ds_status         | character varying(3000) |
 ds_statusdir      | character varying(3000) |
 ds_statusmessage  | character varying(3000) |
 ds_title          | character varying(3000) |

hdfs dfs -find /user/xxxxxxx -name *.hql | wc -l
2546

[#4]

Also, is there a difference in location between, ambari-only users vs ambari/linux users? (Still referring to the hive view)

linux user = the user has an account on the linux box

ambari user = the user has an ambari account

We have a user that only had an ambari account. They seem to have lost their queries after we created a linux account for them. Both ambari and linux account names are the same.

[#5]

The path in HDFS will be different for different users who Created/Saved the scripts.

Like user1 may see something like:

/user/user1/hive/jobs/

This directory is in HDFS hence not directly related to linux user until you have linked linux users with the hdfs.

.

[#6 · Accepted solution]

@Jon Maestas

Saved queries tables name is different something like following you will find in your DB. "ds_savedquery_55"

It should have the following column.

ds_id
ds_database
ds_owner
ds_queryfile 
ds_shortquery     => Short query name
ds_title          =>   worksheet name

[#7]

The user's saved queries weren't in this table. Which explains why they aren't seeing them. I opened one of our nightly pg dumps and pulled the user's query file location from the ds_savedquery_* table and cat'd them from hdfs and sent the output to the user.

cat hdfs_files.out
/user/xxxxxxx/hive/jobs/hive-job-813-2016-07-28_11-46/query.hql
/user/xxxxxxx/hive/jobs/hive-job-1952-2016-10-18_09-31/query.hql
...

for f in `cat hdfs_files.out`;do
> hdfs dfs -cat $f >> saved_queries.hql
> echo >> saved_queries.hql
> echo >> saved_queries.hql
> done

Thanks @jss for your help with this.