Apache Drill Send Query Through Rest API

[Muhammad Fahreza <fa...@nodeflux.io>]

Hi All,


first of all, thanks for make Apache Drill available to us, it is a really
great software in my honest opinion.

Since I was new to develop Drill, *I was wondering how to execute two
Queries at once via Rest API.*


1. First Business Case

For example, I have Postgresql *Storage *named *psql. *Inside the DB, there
is *postgres *schema, I execute this query command on Apache Drill Web
Interface and Drill-Embedded Shell

USE psql.postgres


Then I fetch out rows from *student_list *table.

SELECT * FROM student_list


This is already done and working perfectly.



2. Second Business Case

I need to repeat the query via REST API. Then I send this query via HTTP
Requests.

SELECT * FROM psql.postgres.student_list


This was also working perfectly



3. -Still working on it- Business Case

Sometimes the query is more complex, and I need to call it via HTTP request
too.

Is it possible to query this

USE psql.postgres; SELECT * FROM student_list;


with two 'sentences' in one Query Syntax,

instead of

 SELECT * FROM psql.postgres.student_list


?


Because, when I tried to, send with two separate syntax through two HTTP
request call,
*it failed.*


*[image: Inline image 1]*
What do you suggest for us?




best regards,

-- 
Muhammad Fahreza

Re: Apache Drill Send Query Through Rest API

[John Omernik <jo...@omernik.com>]

For a simple "use" query, what I have done is enable authentication in
Drill via https://drill.apache.org/docs/configuring-user-authentication/.
With that, you can now create sessions in the REST API. Since you mention
HTTP Requests, basically, you create a requests.session() object,
authenticate, and now a session is started. So Use will chain to the next
command etc.  Here is a hacky example script in Python.

#!/usr/bin/python
import requests
import json

#drillhost = "https://ipofdrillbit:portofdrillbit"

drillhost = "https:/10.100.20.40:8047" # example


def main():
s = requests.session()
s = authDrill(s)
use_results = runQuery("use dfs.root", s)
query_results = runQuery("select * from mytable", s)



def authDrill(s):

    url = drillhost + "/j_security_check"
    user = "" # Put username here
    passwd = "" # Put pass here

    login = {'j_username': user, 'j_password': passwd}

    r = s.post(url, data=login, verify=False)

    if r.status_code == 200:
        if r.text.find("Invalid username/password credentials") >= 0:
            print "Authentication Failed - Please check Secrets - Exiting"
            sys.exit(1)
        elif r.text.find("Number of Drill Bits") >= 0: # This is hacky, but
seems to work
            print "Authentication successful"
        else:
            print "Unknown Response Code 200 - Exiting"
            print r.text
            sys.exit(1)
    else:
        print "Non HTTP-200 returned - Unknown Error - Exiting"
        print "HTTP Code: %s" % r.status_code
        print r.text
        sys.exit(1)

    return s

#####################################################################
# Run a Query
def runQuery(drill_query, s):

    url = drillhost + '/query.json'

    payload = {"queryType":"SQL", "query":drill}
    headers = {'Content-type':'application/json'}

    r = s.post(url, data=json.dumps(payload), headers=headers, verify=False)

    return r.json()


if __name__ == '__main__':
    main()

On Fri, Mar 11, 2016 at 3:44 AM, Muhammad Fahreza <fa...@nodeflux.io>
wrote:

> Hi All,
>
>
> first of all, thanks for make Apache Drill available to us, it is a really
> great software in my honest opinion.
>
> Since I was new to develop Drill, *I was wondering how to execute two
> Queries at once via Rest API.*
>
>
> 1. First Business Case
>
> For example, I have Postgresql *Storage *named *psql. *Inside the DB,
> there is *postgres *schema, I execute this query command on Apache Drill
> Web Interface and Drill-Embedded Shell
>
> USE psql.postgres
>
>
> Then I fetch out rows from *student_list *table.
>
> SELECT * FROM student_list
>
>
> This is already done and working perfectly.
>
>
>
> 2. Second Business Case
>
> I need to repeat the query via REST API. Then I send this query via HTTP
> Requests.
>
> SELECT * FROM psql.postgres.student_list
>
>
> This was also working perfectly
>
>
>
> 3. -Still working on it- Business Case
>
> Sometimes the query is more complex, and I need to call it via HTTP
> request too.
>
> Is it possible to query this
>
> USE psql.postgres; SELECT * FROM student_list;
>
>
> with two 'sentences' in one Query Syntax,
>
> instead of
>
>  SELECT * FROM psql.postgres.student_list
>
>
> ?
>
>
> Because, when I tried to, send with two separate syntax through two HTTP
> request call,
> *it failed.*
>
>
> *[image: Inline image 1]*
> What do you suggest for us?
>
>
>
>
> best regards,
>
> --
> Muhammad Fahreza
>
>