sql

User Interface Example

The following SQL query instance uses default.test.solar and returns a table of the planets that orbit the sun, sorted by distance from the sun.

Click the json link in the upper right corner to return your query results as JSON, as follows:

{"name":
      ["Mercury","Venus","Earth","Mars","Jupiter",
       "Saturn","Uranus","Neptune","Pluto","Eris"],
"type":
      ["planet","planet","planet","planet","planet",
       "planet","planet","planet","dwarf planet","dwarf planet"],
"orbits":
      ["Sun","Sun","Sun","Sun","Sun","Sun","Sun","Sun",
       "Sun","Sun"],
"rkm":
      [2439,6051,6371,3389,69911,58232,25362,24622,1186,1163],
"vol":
      [60,928,1083,163,1431280,827130,68340,62540,7,6],
"mass":
      [330,4868,59736,6417,1898600,568460,86832,102430,13,17],
"dist":
      [57900000,108200000,149600000,227900000,778300000,1427000000,
       2871000000,4497100000,5913000000,14000000000]}

You can also click the text link to see your query results as a simple text table.

curl Example

In the following example, a curl command is used to perform a SQL query and present the results as text:

$ curl -s -b cookie.txt -d "sql=select * from default.test.solar 
    WHERE orbits = 'Jupiter' order by dist;"
   'https://www2.1010data.com/beta-latest/api/sql/new:text'

The results are as follows:

 name      type  orbits   rkm   vol  mass  dist    
 --------- ----- -------- ----- ---- ----- --------
 Io        moon  Jupiter   1821   25    89   422000
 Europa    moon  Jupiter   1560   16    48   670900
 Ganymede  moon  Jupiter   2634   76   148  1070000
 Callisto  moon  Jupiter   2410   58   108  1880000

Python Example

In the following example, a new SQL query instance is created by using the endpoint api/new/sql. Then the tag returned is used to run the SQL query. Finally, the method !cols returns the metadata of the resulting table.

# Include system libraries
import sys             # standard python libs
import json            # for parsing api responses
import getpass         # for secure password entry
import requests        # http://python-requests.org/en/master/


########################################################
# Step 1: use the API to start a new session:
########################################################

# Prompt for username and password
usr = input("Username: ")
pwd = getpass.getpass()

###############################################################
# Step 2: now use the sessionID to post a request to the API
###############################################################

# The URL where we will post username and password credentials
u0 = "https://www2.1010data.com/beta-latest/api/!login"

# Send the POST request to the API
r = requests.post(u0,
                  data=dict(uid=usr, pw=pwd),
                  verify=True)

# Decode the result so the sid and epw values may be stored and used in subsequent calls
body = r.content.decode('utf-8')
d = json.loads(body)
session = dict(uid=usr, sid=int(d['sid']), epw=d['epw'])

# Use the new session credentials to create a cookie, used in subsequent requests
mycookie = {"session": "{uid}|{sid}|{epw}".format(**session)}

# The URL to the endpoint of the QuickApp to manage SQL execution
u1 = "https://www2.1010data.com/beta-latest/api/new/sql:json"

# Post to this endpoint with the session cookie, and empty JSON.  This will
# return a handle to this QuickApp, so it may be used to execute SQL via another API call
r = requests.post(u1, cookies=mycookie, json='{}', verify=True)

# Decode the result, and extract the 'tag' which is the ID of the QuickApp running
# in your Insights Platform session.
res=json.loads(r.content.decode('utf-8'))
tag = res.get('tag')

print("tag=" + str(tag))

###########################################################################
# Step 3: issue the API command to fetch data according to the SQL query
###########################################################################

# This URL points towards the QuickApp running in your Insights Platform session as the endpoint.
u2 = "https://www2.1010data.com/beta-latest/api/"+str(tag)+":json"

# The payload to send with the request is a SQL statement, querying a test table.
sqlquery = "SELECT * FROM default.test.solar WHERE orbits='Jupiter' ORDER BY dist;"

# Issue the API request, using the session cookie and the tag (part of the url 'u2' above)
#  Note how the SQL query is sent, formatted as part of a json block in the function call.
r2 = requests.post(u2, cookies=mycookie, json={'sql':sqlquery}, verify=True)

print("Result of the SQL Statement, executed via the API:\n" + r2.content.decode('utf-8') + "\n")

u3 = "https://www2.1010data.com/beta-latest/api/"+str(tag)+"/!cols:json"
r3 = requests.post(u3, cookies=mycookie, json={'sql':sqlquery}, verify=True)

print("The following is the column metadata: \n" + r3.content.decode('utf-8') + "\n")

This code produces the following:

tag=[tag_number]

Result of the SQL Statement, executed via the API: 
{"name":["Io","Europa","Ganymede","Callisto"],
"type":["moon","moon","moon","moon"],
"orbits":["Jupiter","Jupiter","Jupiter","Jupiter"],
"rkm":[1821,1560,2634,2410],
"vol":[25,16,76,58],
"mass":[89,48,148,108],
"dist":[422000,670900,1070000,1880000]}

The following is the column metadata:
{"name":["name","type","orbits","rkm","vol","mass","dist"],
"label":["name","type","orbits","rkm","vol","mass","dist"],
"width":[8,12,7,5,7,7,7]}