question Ambari Hive View API post? in Archives of Support Questions (Read Only)

Ambari Hive View API post?

daniel_thomas — Wed, 18 Oct 2017 19:24:22 GMT

I've seen little documentation on Hive View Rest API here

https://cwiki.apache.org/confluence/display/AMBARI/View+API

It looks like this is mostly to pull information out of ambari Views. I'm interested in posting to Ambari Hive View so I can import a bunch of saved queries. Is this something supported by the API and if so, where can I find this documented?

Re: Ambari Hive View API post?

jsensharma — Wed, 18 Oct 2017 19:45:55 GMT

@Daniel Thomas

Something like following you may try to see if this fulfills your requirement:

Get the list of "Saved Queries"

# curl -u admin:admin -i -H 'X-Requested-By: ambari' -X GET http://localhost:8080/api/v1/views/HIVE/versions/1.5.0/instances/AUTO_HIVE_INSTANCE/savedQueries

Posting the Saved Queries Involves two steps

Step-1). Place your hive queries into the HDFS using API call as following:

# curl -u admin:admin -i -H 'X-Requested-By: ambari' -H 'Content-Type: application/json' -X PUT -d '{"file":{"fileContent":"SELECT * FROM store LIMIT 300;","hasNext":false,"page":0,"pageCount":1}}' http://localhost:8080/api/v1/views/HIVE/versions/1.5.0/instances/AUTO_HIVE_INSTANCE/resources/file/%2Fuser%2Fadmin%2Fhive%2Fjobs%2Fhive-job-103-2017-10-18_12-09%2Fquery.hql

Step-2). Now use the same HDFS path from the step1 to save the query as following for Posting "Saved Queries"

# curl -u admin:admin -i -H 'X-Requested-By: ambari'  -H 'Content-Type: application/json' -X POST -d '{"savedQuery":{"dataBase":"foodmart","title":"store sample query1","queryFile":"/user/admin/hive/jobs/hive-job-103-2017-10-18_12-09/query.hql","owner":"admin","shortQuery":null}}'  http://localhost:8080/api/v1/views/HIVE/versions/1.5.0/instances/AUTO_HIVE_INSTANCE/savedQueries

Re: Ambari Hive View API post?

jsensharma — Wed, 18 Oct 2017 19:50:29 GMT

@Daniel Thomas

Additionally if you are planning to Migrate from Hive View 1.0 / 1.5 to Hive View 2.0 then you might want to take a look at the following API call:
https://docs.hortonworks.com/HDPDocuments/Ambari-2.5.2.0/bk_ambari-views/content/hive_upgrade_your_view.html

Re: Ambari Hive View API post?

daniel_thomas — Wed, 18 Oct 2017 19:56:58 GMT

@Jay SenSharma, this is great stuff. Thank you for your super-fast response.

Presumably this works the same for Hive 2, just change the path from /api/v1/views/HIVE/versions/1.5.0/instances/AUTO_HIVE_INSTANCE/ to /api/v1/views/HIVE/versions/2.0.0/instances/AUTO_HIVE20_INSTANCE/

-Dan

Re: Ambari Hive View API post?

daniel_thomas — Wed, 18 Oct 2017 20:00:01 GMT

Oh, and what I'm actually doing is migrating saved queries from Cloudera/Hue to Amabri Hive View. I found I can easily export queries to a list of JSON in Hue, but I don't see any native way of importing those to Ambari, so I'm going to try to load them via the Rest API using Python.

Re: Ambari Hive View API post?

jsensharma — Wed, 18 Oct 2017 20:03:15 GMT

@Daniel Thomas

Yes Based on your Hive View version you will need to make the following changes:

1.5.0 => Need to be replaced with your view version (like 2.0.0)

AUTO_HIVE_INSTANCE => This is the View Instance Name, Which might be different in your case. For example if you are using default Hive View 2.0 instance then the default view name should be "AUTO_HIVE20_INSTANCE" (if you have multiple View 2.0 instances then you will need to replace this with your own Hive View 2.0 instance name)

Re: Ambari Hive View API post?

jsensharma — Wed, 18 Oct 2017 20:05:30 GMT

@Daniel Thomas

If you are planning to migrate from Hue to Views then you should refer to this article as well: https://community.hortonworks.com/articles/54146/migrating-hue-to-ambari-views-24.html

More details on "Hue to View Migration" can be found here: https://docs.hortonworks.com/HDPDocuments/Ambari-2.5.2.0/bk_ambari-views/content/configuring_your_cluster_for_htv_migration.html

Re: Ambari Hive View API post?

daniel_thomas — Wed, 18 Oct 2017 20:13:19 GMT

Ah, yes, I also looked at this as well and found I'd actually have to migrate the Hue database first as it currently uses the embedded Sqlite3 database, which doesn't support remote access. This was encouraging though and is likely very helpful for those who configured thier Hue for an external database in the first place.

Re: Ambari Hive View API post?

daniel_thomas — Thu, 19 Oct 2017 19:20:09 GMT

@Jay SenSharma I'm having trouble with this still. I tried what I actually wanted to post and, as below, tried with your sample. In both cases, I got a 500 server error. What am I doing wrong here?

curl -u admin:admin -i -H 'X-Requested-By: ambari' -H 'Content-Type: application/json' -X PUT -d '{"file":{"fileContent":"SELECT * FROM store LIMIT 300;","hasNext":false,"page":0,"pageCount":1}}' http://hdp-node00:8080/api/v1/views/HIVE/versions/2.0.0/instances/AUTO_HIVE20_INSTANCE/resources/file/%2Fuser%2Fadmin%2Fhive%2Fjobs%2Fhive-job-103-2017-10-18_12-09%2Fquery.hql

HTTP/1.1 500 Server Error X-Frame-Options: SAMEORIGIN X-XSS-Protection: 1; mode=block X-Content-Type-Options: nosniff Pragma: no-cache Set-Cookie: AMBARISESSIONID=1woe7sttjidcw1rpi053bemdq5;Path=/;HttpOnly User: admin Content-Type: text/plain;charset=ISO-8859-1 Content-Length: 48 { "status": 500, "message": "Server Error" }

Re: Ambari Hive View API post?

jsensharma — Sat, 21 Oct 2017 18:04:38 GMT

@Daniel Thomas

For Hive View 2.0 you can try something like following:

# curl -u admin:admin -i -H 'X-Requested-By: ambari' -H 'Content-Type: application/json' -X POST -d '{"savedQuery":{"dataBase":"default","title":"Worksheet1","queryFile":"","owner":null,"shortQuery":"select * from customer LIMIT 3;"}}' "http://localhost:8080/api/v1/views/HIVE/versions/2.0.0/instances/AUTO_HIVE20_INSTANCE/savedQueries"

The saved script will be automatically stored inside the HDFS location something like "/user/admin/hive/scripts" (assuming that admin user is running the hive query)

Similarly if the user "maria_dev" runs the API call then the Saved Query SQL dat will be stored in the HDFS directory "/user/maria_dev/hive/scripts" directory:

# curl -u maria_dev:maria_dev -i -H 'X-Requested-By: ambari' -H 'Content-Type: application/json' -X POST -d '{"savedQuery":{"dataBase":"default","title":"Worksheet1","queryFile":"","owner":null,"shortQuery":"select * from customer LIMIT 5;"}}' "http://localhost:8080/api/v1/views/HIVE/versions/2.0.0/instances/AUTO_HIVE20_INSTANCE/savedQueries"

Re: Ambari Hive View API post?

daniel_thomas — Mon, 23 Oct 2017 23:25:16 GMT

This totally worked! Thanks @Jay SenSharma.

Note: I use the previous saved query name from my json source file as the "title" in the new saved query. This allowed me to uniquely identify each query in the UI. If I ever revisit this code, I may also need to query Rest to see if a specific query already exists and replace it or increment the title name or something.

-Dan

Re: Ambari Hive View API post?

jsensharma — Mon, 23 Oct 2017 23:53:52 GMT

@Daniel Thomas

Good to know that it worked for you. As the original issue is resolved, hence it will be also great if you can mark this HCC thread as Answered by clicking on the "Accept" Button on the correct answer. That way other HCC users can quickly find the solution when they encounter the same issue.