new 1010data dynamic api reference manual · 2020. 3. 10. · 1010data dynamic api reference manual...

(212) 405.1010 | [email protected] | Follow: @1010data | www.1010data.com

1010data DynamicAPI Reference Manual

1010data Dynamic API Reference Manual | Contents | 2

© 2020 1010data, Inc. All rights reserved.

Contents

1010data Dynamic API................................................................................ 3Prerequisites........................................................................................................................................ 3Dynamic API versus XML API............................................................................................................ 3Introduction to the Asynchronous Query Interface (AQI)....................................................................4

Session management..................................................................................5Logging In............................................................................................................................................5Logging out..........................................................................................................................................7

Built-in API endpoints.................................................................................9dbm.....................................................................................................................................................10defs.................................................................................................................................................. 11inspect............................................................................................................................................ 12ls.......................................................................................................................................................13new.....................................................................................................................................................14run.....................................................................................................................................................18sql.....................................................................................................................................................21

User-defined endpoints.............................................................................26QuickApp guidelines..........................................................................................................................26Invoking a QuickApp from the Dynamic API.................................................................................... 26Interacting with your endpoint in the GUI......................................................................................... 27QuickApp variables for Dynamic API................................................................................................28

Appendix: URL conventions.................................................................... 29

1010data Dynamic API Reference Manual | 1010data Dynamic API | 3

1010data Dynamic API

The 1010data Dynamic Application Programming Interface (API) allows you to write a client applicationthat can access and query data on the Insights Platform database servers. This allows for buildingcustomized applications and interfaces, while taking advantage of the Insights Platform's databasemanagement services and fast analytics engine. The Dynamic API is RESTful and is compatible with anyclient application written in a language that supports HTTP transactions (such as Python, Java, C++, andPERL).

The Dynamic API is powered by <dynamic> queries, also known as QuickApps, written in MacroLanguage. In fact, each endpoint in the Dynamic API is a QuickApp available in the Object Manager ofthe Insights Platform and reachable by URL. The Dynamic API posts data to, and retrieves data from,1010data QuickApps.

There are several built-in QuickApps supplied by 1010data. For an overview of the built-in endpointsavailable in the Dynamic API, use the following Insights Platform URL:

https://[host]/[1010-version]/api

For a simple way to access the Dynamic API in the GUI, log into the 1010data Insights Platform.Copy the URL into a new tab, and change /gw to /api. For example, if the URL in your browser ishttps://www2.1010data.com/beta-latest/gw, the Dynamic API user interface is available through https://www2.1010data.com/beta-latest/api. See the Built-in API endpoints on page 9 section of this guide formore information about each endpoint.

You can also build your own endpoints (QuickApps) in the Macro Language Workshop. You can thenaccess these endpoints with the Dynamic API. See User-defined endpoints on page 26 for moreinformation.

The Dynamic API is stateful, which means that the system will remember each query for the life of yoursession. The new endpoint creates a new environment for your QuickApp and assigns it a unique number,or tag. You can return to any QuickApp during the life of your session by referring to the tag.

PrerequisitesTo effectively work with the Dynamic API, you should be familiar with RESTful APIs and with 1010dataInsights Platform concepts.

We recommend that users of the Dynamic API are already familiar with other service APIs such as RESTor SOAP. While similar to REST APIs in several ways, the Insights Platform's stateful, session-basedaccess model is different from traditional REST APIs. Therefore, we also recommend knowledge ofInsights Platform sessions, directories, tables, and queries.

To use the Dynamic API, a valid Insights Platform account with API access is required.

Note: The examples in this guide are written in Python 3 and use the Requests HTTP libraryfor Python. Refer to http://python-requests.org for more information. However, you may use anylanguage that supports HTTP transactions, as well as any HTTP library.

Dynamic API versus XML APIThe Dynamic API has several benefits over the 1010data XML API.

Traditional QuickApps are served through the RENDER verb in the XML API (or you can launch a QuickAppdirectly using the 1010data web interface). The same QuickApp can be rendered to multiple targets—generally the web target—but can also be rendered to alternate targets used for generating PDFs, Excelfiles, and static HTML reports.

https://www2.1010data.com/beta-latest

https://www2.1010data.com/beta-latest/api

https://www2.1010data.com/beta-latest/api

http://python-requests.org

1010data Dynamic API Reference Manual | 1010data Dynamic API | 4

The Dynamic API adds a new RENDER target called api. It is intended to be used independently of theXML API. Instead of routing traditional API requests through the gateway (/[1010_version]/gw),Dynamic API requests are routed through an independent URL (/[1010_version]/api). This iscurrently served by a CGI script.

Serving an API throught this independent channel has several benefits:

First, it allows you to greatly simplify the communications channel when compared to the gateway.Whereas the XML API relies on a custom XML schema for each endpoint, the Dynamic API assumes thatresponses will be structured data composed of strings, numbers, lists, and dictionaries, which can then beencoded in a variety of computer-readable formats or rendered as HTML.

Second, the Dynamic API allows you to field multiple simultaneous requests to the same accum throughthe Asynchronous Query Interface (AQI). See Introduction to the Asynchronous Query Interface for moreinformation about the AQI.

Introduction to the Asynchronous Query Interface (AQI)The Asynchronous Query Interface (AQI) is a new feature in the Dynamic API that allows the user's mainaccum to dispatch entire queries to worker accums to be processed asynchronously.

Traditionally, an individual 1010data query could be broken up and run in parallel across multiple sub-accums. However, with the new AQI, you now have the ability to run multiple independent queriessimultaneously on worker accums (which may also employ their own traditional sub-accums).

With QuickApps, a single RENDER or INTERACT request can result in multiple queries. Without AQI,these requests are all run in sequence on the accum, and the results are then bundled in a single HTTPresponse. The HTTP response contains the HTML and JavaScript needed to update all the affectedwidgets at once.

When you use Dynamic API to query a QuickApp, you can only request a single widget at a time. Bydefault, when you query a QuickApp with the Dynamic API, the response is a table of widget statuses,which the Dynamic API client can use to decide which widgets it should fetch.

A client can issue HTTP requests for multiple widgets simultaneously. Each request gets its own CGIprocess on the backend. Either the accum responds immediately with the requested data, or it enables theAQI to dispatch the query to the worker accums. The client does not need any information about whetherAQI is enabled.

1010data Dynamic API Reference Manual | Session management | 5

Session management

An authentication cookie in the Dynamic API contains user credentials and one or more session IDs.

Authentication CookieThe Dynamic API uses the same style authentication cookie as the Insights Platform user interface. Whenan API session with the Insights Platform is established, the system passes back an authentication cookie.The cookie header is then sent with every request. The authentication cookie header contains the user ID(uid), session ID (sid), and encrypted password (epw), and looks similar to the following:

Cookie: session=csmith|1475447829|_______a664ecc48415c4962bcf26b35f...

Note: The actual encrypted password is longer than the example above.

The user ID is your user ID for logging in to the Insights Platform.

The session ID is a unique identifier for your session. It is created when the session is created and ceasesto exist once the session has ended. Combined, your session ID and encrypted password are a secureand reliable set of credentials for sending successive transactions in the same API session.

You can have multiple session IDs in a single authentication cookie, if, for example, you are calling the APIfrom a QuickApp and you are logged into multiple sessions. You can specify sid=nnnnnn to refer to aspecific session in the cookie. By default, the API runs the first session in the cookie.

The authentication cookie is only valid for the life of the Insights Platform session.

Logging InThe API call !login logs in to a new Insights Platform session.

URL

https://[host]/[1010-version]/api/!login:json

Method

POST

Parameters

uid

The User ID (required)

You can also use a GUS user name (an email address) to log in. The !login response will containan internal uid.

pw

The password (required)

kill

An optional parameter that determines whether you want to kill or possess the existing session.

Possible values are as follows:

yes

Terminate the existing session and start a new one.

possess


Log in and possess the existing session.

The default value is possess.

Response

The following are possible login response codes:

200

Login successful

A successful login returns the session cookie you need for future requests. The following is the JSONresponse from the !login endpoint:

{'uid': 'userid', 'sid': sessionid, 'epw': 'encryptedpw'}

In addition, a Set-Cookie http header comes from the server. Set-Cookie has the followingformat:

Set-Cookie: session=userid|sessionid|encryptedpw

401

Access denied

The user id and/or password are incorrect.

403

Forbidden error

The user id and password combination has expired. The system displays a message suggesting youtry logging in again.

405

Method not allowed

You used a GET method instead of a POST method.

curl Example

The following example uses a curl command to test your login credentials. The authentication cookie isthen saved in the file cookie.txt to be used for subsequent commands.

$ curl -s -c cookie.txt -d uid=csmith -d pw=XXX 'https://www2.1010data.com/beta-latest/api/!login'

You can also use a JSON post to log in, as follows:

$ curl -s -c cookie.txt -H "Content-type: application/json" -X POST -d '{"uid":"csmith", "pw":"XXX"}' 'https://www2.1010data.com/beta-latest/api/!login'

You will receive the following JSON response (note that the encrypted password below is truncated):

{"uid": "csmith", "sid": "956151005", "epw": "_______ca74fb26da1d29647b5cdc2acfb..."}

Python Example

The following Python 3 code logs into a Insights Platform session and returns a session cookie for futurerequests.

# Include system librariesimport sys # standard python libsimport json # for parsing api responses


import getpass # for secure password entryimport requests # Requests HTTP library for Python # http://python-requests.org/en/master/

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

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

# Send the POST request to the Dynamic APIr = 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 callsbody = 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 requestsmycookie = {"session": "{uid}|{sid}|{epw}".format(**session)}

# Display the session IDprint("sessionID secured.\nsession cookie=:" + str(mycookie))

After you enter your user ID and password, this code returns the following JSON response (note that theencrypted password is truncated):

sessionID secured. session cookie=: {'session': 'csmith|956151005|_______491c5969cb2f8c6a8b43e2d300e3e6de4'}

Logging outThe API call !logout logs out of an Insights Platform session.

Description

Although the Insights Platform automatically times out after an idle period, it is good practice to log outwhen you are finished with a session.

URL

https://[host]/[1010-version]/api/!logout:json

Method

POST

Parameters

cookies

The session cookie that was the log in response.

Response

If successful, logging out returns a status code of 200.


curl Example

The following curl command logs you out of the Insights Platform session. It uses the authentication cookiecookie.txt you created when you logged in:

$ curl -s -b cookie.txt -d "cookies=$(cat cookie.txt)" 'https://www2.1010data.com/beta-latest/api/!logout:json'

If the logout is successful, the curl command returns the following JSON response:

{"rc": "0", "msg": "Logged out"}

Python Example

The following Python 3 code passes the session cookie to the /api/!logout endpoint and returnsa message that the logout was successful. This code assumes that you have an active session and apreviously established session cookie called mycookie:

u3 = "https://www2.1010data.com/beta-latest/api/!logout"r3 = requests.post(u3, cookies=mycookie)

if r3.status_code == 200: print ("Logout successful")else: raise Exception("Unable to log out")

1010data Dynamic API Reference Manual | Built-in API endpoints | 9

Built-in API endpoints

The following endpoints are available in the Dynamic API.

Description

Endpoints in the Dynamic API are simply <dynamic/> queries (QuickApps) written in Macro LanguageWorkshop and reachable by URL. You can call methods in the Dynamic API using the following generalformat. For more details and specific code examples, refer to the documentation for each endpoint.

The following are the endpoints included in the Dynamic API:

In each endpoint screen, you can view the results of your query as HTML, text, or JSON by clicking thelinks in the top right corner. HTML is the default format for the user interface. However, you will most likelyuse JSON in an external application so that you can parse the data structure.

URL

https://[host]/[1010-version]/api

Note: Trailing or redundant / characters anywhere in the URL will be ignored, and possiblyrewritten, by the API. For example, /api is equivalent to /api/.

Method

GET

Response

The response to API endpoints can be displayed as HTML, text, or JSON. In your Dynamic API applicationyou would append :html, :text, or :json to the URL. See each API endpoint topic for specificexamples.


dbmdbm executes an action to add, modify, or remove tables, directories, users, groups, companies, and rolesin the Insights Platform, and is the equivalent of <do action_="dbm"/> in the Insights Platform's MacroLanguage.

Description

The dbm endpoint allows you to edit anything you have permissions to edit, including tables, directories,users, groups, companies, and roles.

URL

https://[host]/[1010-version]/api/dbm

Method

POST

Parameters

The dbm endpoint takes a single parameter, cmd, which should contain a list of two items: the name ofthe dbm transaction to invoke and a dictionary of parameters. These two items correspond to the trans_and data_ attributes in <do action_="dbm"/> in macro code. For more information, refer to <doaction_="dbm"/> in the 1010data Reference Manual.

For example, <do action_="dbm" trans_="x" data_="{y: 'z'}"/> in macro code correspondsto the following JSON for input into the dbm endpoint: {"cmd":["x",{"y": "z"}]}

Response

The result is a dictionary containing:

rc

A result code

0 if the dbm action was successful

1 if there was an error

msg

When rc=1, msg displays the error message.

See

value

When rc=0, value displays any JSON object, depending on the transaction performed in cmd.

curl Example

The following example in curl logs into the Insights Platform and returns the names of all the tables in thepub directory.

First, log in with the /!login endpoint and create the authentication cookie cookie.txt:

$ curl -s -c cookie.txt -d uid=csmith -d pw=XXX 'https://www2.1010data.com/beta-latest/api/!login'

The following JSON response represents the authentication cookie:

http://nyc-lamp/DocumentationCenter/1010dataReferenceManual/index_frames.html?q=MLOperations/appdev/doActions/doActionDbm.html

http://nyc-lamp/DocumentationCenter/1010dataReferenceManual/index_frames.html?q=MLOperations/appdev/doActions/doActionDbm.html


{"uid": "csmith", "sid": "1343979483", "epw": "_______ca74fb26da1d29647b5cdc2acfb..."}

Then, input the JSON {"cmd": ["get_names_in_dir", {"path": "pub"}]} intothe /dbm endpoint. This is the equivalent of the QuickApp code <do action_="dbm"trans_="get_names_in_dir" data_="{pkg('path';'pub')}" value_"@tab"/>

$ curl -s -b cookie.txt -H 'Content-type: application/json' -d '{"cmd": ["get_names_in_dir", {"path": "pub"}]}' 'https://www2.1010data.com/beta-latest/api/dbm:json'

The result is JSON containing "rc":0 (because the dbm action was successful), plus "value", which isthis case is a list of table names in the pub directory:

{"rc":0,"value": [ ["dir","dir","dir","dir","dir","dir","dir","dir","dir","dir","dir", "dir","dir"], ["pub.fin","pub.geo","pub.demo","pub.reg","pub.audit","pub.consumer_data", "pub.lib","pub.enigma","pub.public_data","pub.signal","pub.doc", "pub.gui","pub.training_exercise_data" ] ]}

defsdefs lists definitions inside a library.

Description

Use the defs endpoint and a path name to return a list of definition types and names inside a library.

URL

https://[host]/[1010-version]/api/defs/[library_path]

Response

The list of types and names of definitions inside the given library.

User Interface Example

In https://[host]/[1010-version]/api/defs/[library_path], click the example api/defs/default.test.workbook. The following table appears:

default.test.workbook is a library that contains four blocks, named ws0, ws1, ws2, and ws3.


You can also display the definitions as JSON or plain text.

curl ExampleThe following example in curl returns the definitions inside the library default.test.workbook:

$ curl -s -b cookie.txt 'https://www2.1010data.com/beta-latest/api/defs/default.test.workbook':text

The response contains the blocks within the library:

kind name ------ ----- block ws0 block ws1 block ws2 block ws3

inspectinspect inspects the state of currently running QuickApps.

Description

Use inspect and a widget name to inspect various aspects of running QuickApps.

The following widgets are available:

@qatable

A table of all running QuickApps, in order of tag number. It is the equivalent of <doinspect_="qatable"/>

@widgetstates

A table consisting of a subset of columns from the <dynamic tag_="[tag_number]"/>

@dynamic

The full widget state table for a given tag. It is the equivalent of <dynamictag_="[tag_number]"/>

@inspect

A table containing expansion states for each widget. It is the equivalent of <doinspect_="dynamic" tag_=[tag_number]"/>

@scope

URL

https://[host]/[1010-version]/api/inspect/[widget_name]?tag=[tag_number]

If you do not specify a tag number, inspect displays the contents of tag 1 as the default value.

curl Example

The following curl command displays a table of all running QuickApps, in tag order:


$ curl -s -b cookie.txt 'https://www2.1010data.com/beta-latest/api/inspect/@qatable:text'

The resulting JSON displays the tag numbers and details about every currently running QuickApp.

{"tag": [1,2,3,4,5,6],"instid": ["0","HMnest_1_gui20_wsmeter","HMnest_1_dashboard","HMnest_3_hmnonce_1_2__1", "HMnest_3_hmnonce_1_3__1",""],"base": ["default.lonely","default.lonely","default.lonely","default.lonely", "default.lonely","default.lonely"],"target": ["web","web","web","web","web",""],"qaid": ["MEAL1","MEAL2","MEAL3","MEAL11","MEAL5",""],"topqaid": ["","MEAL1","MEAL1","MEAL1","MEAL1",""],"entry": ["standalone","","","","",""],"path": ["default.gui.workspace","","","","",""],"title": ["GUI Default Workspace","","","","",""]}

lsA list of items in a directory.

URL

https://[host]/[1010-version]/api/ls/[directory_path]

Response

The directory listing, which can be formatted as HTML, text, or JSON.

curl Example

The following example in curl returns the names of all the tables in the pub directory in text format.

$ curl -s -b cookie.txt 'https://www2.1010data.com/beta-latest/api/ls/pub:text'

The result is a table containing all the information about each table in the pub directory, including table id,type, path, title, and description. (The columns below are truncated for readability.)

id type path href title --- ----- ------------- ------------------------- ------------------ 3 dir pub.fin /beta-15.09/api/ls/pub... Finance 243 dir pub.geo /beta-15.09/api/ls/pub... Geographic Data 373 dir pub.demo /beta-15.09/api/ls/pub... Demo 381 dir pub.lib /beta-15.09/api/ls/pub... Libraries 427 dir pub.public... /beta-15.09/api/ls/pub... Public Data 516 dir pub.doc /beta-15.09/api/ls/pub... Documentation 572 dir pub.gui /beta-15.09/api/ls/pub... GUI 783 dir pub.train... /beta-15.09/api/ls/pub... Training Exercise...


newNew spawns a new instance of a QuickApp.

Description

When you use new to spawn an instance of a QuickApp, the API assigns a unique number to theQuickApp instance, called a tag. The tag number appears in the URL and in the user interface. The newQuickApp instance has the format https://[host]/[1010-version]/api/[tag_number].

URL

https://[host]/[1010-version]/api/new/[quickapp_name]

This URL spawns a new instance of the QuickApp [quickapp_name]. For example,https://[host]/[1010-version]/api/new/sql spawns a new instance of the sql QuickApp.

Alternatively, use the following URL to submit a <dynamic> query on the fly in the user interface. This isuseful for experimenting with a query:

https://[host]/[1010-version]/api/new

Enter the <dynamic> query in the text box and click Submit.

When you successfully spawn a new instance of a QuickApp, the following appears:


Click the link to interact with the new QuickApp instance.

Methods

POST

Parameters

@xml

A string containing a <dynamic> query.

Response

new returns a numerical tag that enables you to interact with the QuickApp. To run the QuickApp, use thetag number returned by new in the URL:

https://[host]/[1010-version]/api/[tag_number]

User Interface Example: Spawn an instance of an endpoint

In the following example, the api/new/sql spawns a new instance of the sql endpoint:

Click the link api/7 to interact with the new sql QuickApp instance. Interact with the endpoint as younormally would.


You can return to this instance of the QuickApp at api/7 and interact with it any time during your session.

User Interface Example: Entering a <dynamic> query

In the following example, a new QuickApp is loaded on the fly using the @xml parameter in the userinterface. @xml contains a <dynamic> query entered directly into the text box in the new endpoint.

Click Submit to spawn a new QuickApp containing the results of the query. The following screen appears:


Then click the link containing the tag for the QuickApp, in this case, api/10. The query results appear:

You can also interact with the QuickApp within the URL. In this example QuickApp, you can change thevalues of x and y by adding a query to the end of the URL, such as api/10?x=5&y=7. Now x=5 and y=7.The query results are as follows:

You can return to this QuickApp instance at api/10 and interact with it at any point during your session.

curl Example: Spawn an instance of a <dynamic> query

You can test a <dynamic> query with curl.

The following is a simple <dynamic> query saved as a file named app.xml:

<dynamic x="0" y="0" sum="0" msg=""> <do on_="init,change(x),change(y)"> <set sum="{@x + @y}"/> <set msg="{@x}+{@y}={@sum}"/> </do> <widget class_="value" name="index" value_="@msg"/> <widget class_="value" name="sum" value_="@sum"/></dynamic>

First, issue a curl command containing the <dynamic> query to the new endpoint:

$ curl -s -b cookie.txt -d "xml=$(cat app.xml)" 'https://www2.1010data.com/beta-latest/api/new:json'

The command returns JSON containing the tag number of the new instance that was created:

{"tag":18}


Then, issue a curl command to the tag that was created:

$ curl -s -b cookie.txt 'https://www2.1010data.com/beta-latest/api/18/:json'

This command returns the result of the <dynamic> query as follows:

"0 0=0,0"

You can also use curl to test different variables in your query, as follows:

$ curl -s -b cookie.txt 'https://www2.1010data.com/beta-latest/api/18/:json?x=5&y=3'

The result of the <dynamic> query is now:

"5 3=5,3"

runRun (and optionally save data for) an MDB query.

URL

https://[host]/[1010-version]/api/run

Use this URL to test your MDB query in the editor. Click Submit to view the results of your MDB querybelow the text editor as HTML.

Click text or json in the upper right corner to view your query results as a simple text table or as JSON.

Note that the /api/run endpoint supports running only a single query at a time. To run multiple queries,you should instead instantiate the api/run endpoint by posting to api/new/run. The api/new/runendpoint creates an instance of an MDB query and returns a number called a tag, which represents a newendpoint with its own state. You reference the instance with the endpoint api/[tag_number].

Methods

POST


Parameters

@xml

A string containing a <dynamic> query to be run.

Response

The result of your MDB query, which can be formatted as HTML, text, or JSON.

Fetching Result Data and Metadata

The run endpoint contains an index widget, so you can retrieve metadata and column information fromyour MDB query. The index widget is @index, which can be abbreviated as !.

You can use the following methods to refine your data:

!cols (@index/cols)

Returns a dictionary of metadata about the columns, including column name, label, description,type, fixed/not fixed, width, and format. The values are the same as the corresponding values in<columns/> in macro code. For more information, see <columns/> in the 1010data ReferenceManual.

!rows (@index/rows)

Returns the total number of rows in the query

!data?range_=x&to_=y (@index/data?range_=x&to_=y)

Query all rows or a range of rows. ?range is optional. x and y represent row numbers. The firstrow is row 0. Note that this breaks from the Macro Language convention, where i_() (row number)begins at 1.

!head (@index/head)

Returns the result of !cols, !rows, and !data?range_=0&to_=500 (the to_ number is subject tochange).

!data:json.rows

By specifying json.rows as the output, the client application can obtain JSON data in row-majorformat instead of the default column-major format.

!data:json.keyed

By specifying json.keyed as the output, the client application can obtain data as a list ofdictionaries. Each row becomes a dictionary mapping the names of columns in the table to the valuesfor that row.

!stop?force_=1

Attempts to stop the running query. The ?force_=1 argument is optional.


The following MDB query instance uses default.test.solar and returns a table of the planets thatorbit the sun, displaying the columns name, vol, mass, and dist, sorted by distance from the sun.

https://www2.1010data.com/documentationcenter/beta/1010dataReferenceManual/index_frames.html?q=MLOperations/classicops/columns.html


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"],"type": ["planet","planet","planet","planet","planet", "planet","planet","planet"],"vol": [60,928,1083,163,1431280,827130,68340,62540],"mass": [330,4868,59736,6417,1898600,568460,86832,102430],"dist": [57900000,108200000,149600000,227900000,778300000,1427000000, 2871000000,4497100000]}

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

curl Example: Return selected rows of a query

In the following example, curl sends a command to run an MDB query and then return rows 2 through 4 ofthe query:

$ curl -s -b cookie.txt -d xml='<base table="default.test.solar"/>' 'https://www2.1010data.com/beta-latest/api/run/!data:text?range_=2&to_=4'

This command returns the following rows of the table:

name type orbits rkm vol mass dist------- ------- ------- ------ ------- ------- -----------Saturn planet Sun 58232 827130 568460 1427000000Uranus planet Sun 25362 68340 86832 2871000000


curl Example: Return JSON rows instead of columns

In the following example, curl sends a command to run an MDB query and then return rows 2 through 4 ofthe query in row-major JSON format instead of the default column-major format:

$ curl -s -b cookie.txt -d xml='<base table="default.test.solar"/>' 'https://www2.1010data.com/beta-latest/api/run/!data:json.rows?range_=2&to_=4'

This commands returns the following JSON output:

{"cols": ["name","type","orbits","rkm","vol","mass","dist"],"rows": [["Saturn","planet","Sun",58232,827130,568460,1427000000], ["Uranus","planet","Sun",25362,68340,86832,2871000000]]}

curl Example: Return column metadata

In the following example curl sends a command to return the column metadata for the tabledefault.test.solar:

curl -s -b cookie.txt -d xml='<base table="default.test.solar"/>''https://www2.1010data.com/beta-latest/api/run/!cols:text'

This command returns the following column metadata:

name label desc type fixed width format ------- ------- ----- ----- ------ ------ ---------------------- name name a 0 8 type:char;width:8 type type a 0 12 type:char;width:12 orbits orbits a 0 7 type:char;width:7 rkm rkm i 0 5 type:nocommas;width:5 vol vol i 0 7 type:nocommas;width:7 mass mass i 0 7 type:nocommas;width:7 dist dist f 0 7 type:nocommas;width:7

sqlRun a SQL query on data in a Insights Platform table.

URL

https://[host]/[1010-version]/api/sql

Use this URL to test your SQL query in the editor. Click Submit to view the results of your SQL querybelow the text editor as HTML.


Click text or json in the upper right corner to view your query results as a simple text table or as JSON.

Note that the /api/sql endpoint supports running only a single query at a time. To run multiple queries,you should instead instantiate the api/sql endpoint by posting to api/new/sql. The api/new/sqlendpoint creates an instance of a SQL query and returns a number called a tag, which represents a newendpoint with its own state. You reference the instance with the endpoint api/[tag_number].

Methods

POST

Parameters

@sql

A string containing a SQL query to execute.

Response

The result of your SQL query, which can be formatted as HTML, text, or JSON.

Fetching Result Data and Metadata

The SQL endpoint contains an index widget, so you can retrieve metadata and column information fromyour SQL query. The index widget is @index, which can be abbreviated as !.

You can use the following methods to refine your data:

!cols (@index/cols)

Returns a dictionary of metadata about the columns

!rows (@index/rows)

Returns the total number of rows in the query

!data?range=x&to=y (@index/data?range=x&to=y)

Query all rows or a range of rows. ?range is optional. x and y represent row numbers. The firstrow is row 0. Note that this breaks from the Macro Language convention, where i_() (row number)begins at 1.



The following SQL query instance uses default.test.solar and returns a table of the planets thatorbit 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. Thenthe tag returned is used to run the SQL query. Finally, the method !cols returns the metadata of theresulting table.

# Include system librariesimport sys # standard python libsimport json # for parsing api responsesimport getpass # for secure password entryimport requests # http://python-requests.org/en/master/

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

# Prompt for username and passwordusr = 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 credentialsu0 = "https://www2.1010data.com/beta-latest/api/!login"

# Send the POST request to the APIr = 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 callsbody = 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 requestsmycookie = {"session": "{uid}|{sid}|{epw}".format(**session)}

# The URL to the endpoint of the QuickApp to manage SQL executionu1 = "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 callr = 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]}

1010data Dynamic API Reference Manual | User-defined endpoints | 26

User-defined endpoints

Since Dynamic API endpoints are QuickApps, you can create your own Dynamic API endpoints and callthem in the Dynamic API.

Dynamic API interactions are direct interactions with <dynamic> queries, or QuickApps. Like allQuickApps, Dynamic API endpoints are composed of one or more widgets. A single Dynamic API requestcan affect multiple widgets simultaneously. A Dynamic API request can return either data from a singlewidget or a table describing all the widgets.

Currently, there are three types of Dynamic API widgets:

data

data widgets return the results of a query.

value

value widgets return arbitrary structured data.

scope

scope widgets return a dictionary of variables in the environment.

When you call a QuickApp over the Dynamic API, scope and value widgets keep their classes, while allother widgets are mapped to the data class or are currently unsupported.

QuickApp guidelinesThe following guidelines will help you maximize the functionality of your QuickApp in the Dynamic API.

You may need to make minor changes to your QuickApp to expose its full functionality in the Dynamic API.With traditional 1010data QuickApps, the response contains everything the browser needs to update allnecessary widgets at once and keep the display in sync. With the Dynamic API, that burden moves to theclient. Therefore, a request for one widget can trigger changes in other widgets that are not reflected in theresponse.

In order to handle this difference, we recommend the following guidelines for creating QuickApps to becalled by the Dynamic API.

• Design simple endpoints that require only one widget. Most of the built-in endpoints work this way.• Pass in configuration variables once, when you initialize the QuickApp, and do not change them.• Pass all required variables for each widget you request so that results do not depend on server-side

state.• Post all variable changes to the top-level QuickApp (either by name or tag number) and then let

the widget status table tell you which widgets were updated. This approach is the most flexible forcomplicated QuickApps.

Invoking a QuickApp from the Dynamic APIYou can treat any QuickApp as a Dynamic API endpoint by invoking /api/[path_to_quickapp].

Using the new endpoint

If you want multiple copies of the same QuickApp to run simultaneously, you can launch your QuickApp byposting to /api/new/[path_to_quickapp]. This serves a value widget that will contain the numerictag of the spawned QuickApp. You can then fetch the actual QuickApp via /api/[tag_number]. See thenew endpoint for more information.

The index widget

If you name a widget within your QuickApp index, it will be served instead of the widget status tablewhen requesting the top level QuickApp. This is most useful when you have only a single widget in yourQuickApp. For example, when you log into the API from the GUI, using https://[host]/[1010-version]/api, you are directed to the /index endpoint by default. The source code for this endpointis available through the Object Manager as default.api.index. The code consists of a single widgetcalled "index":

<base table="default.lonely"/><dynamic> <widget name="index" navcol_="name"> <layer name="apidoc"> <html><![CDATA[ <h1><tt>index</tt> of 1010data API Endpoints</h1> <p>The following endpoints are available:</p> ]]> </html> </layer> <directory folder="default.api"/> <willbe name="name" value="strpick(path;'.';-1)"/> <colord cols="name,title"/>  <sel value="name<>'index'"/> </widget></dynamic>

Note: Currently, there is no easy way to fetch the status table if you name a widget index.

URL conventions

The Dynamic API uses certain conventions when parsing URLs in client applications. See Appendix: URLconventions on page 29 for a complete reference.

Interacting with your endpoint in the GUIThe Dynamic API GUI interface was designed so that you can interact with any Dynamic API endpoint inyour browser.

By default, the HTML interface shows a grid for data widgets and the raw data in k syntax for the valuewidgets.

You can provide interactive documentation for your endpoint in the Insights Platform GUI by providingHTML in a widget <layer> named apidoc. For examples, see any of the built-in endpoints underdefault.api in the Object Manager. The example below is a section of default.api.dbm, theQuickApp for the dbm endpoint, showing the apidoc layer containing the online documentation for theendpoint.

<widget name="index" class_="value" label_="result" value_="@result"> <layer name="apidoc"> <html><![CDATA[ <h1><tt>dbm</tt> API Endpoint</h1> <p>This endpoint wraps <tt><do action_="dbm"/></tt>.</p> <p>Input is a list of two items called <tt>@cmd</tt>. It contains:</p> <ol> <li>a string indicating the transaction (<tt>trans_</tt>) to run</li> <li>a dictionary containing the parameters (<tt>data_</tt>) for that transaction</li> </ol>


<p>The result is a dictionary containing:</p> <dl> <dt><tt>rc</tt></dt> <dd>A result code (0 for success, 1 for error).</dd> <dt><tt>msg</tt> (when <tt>rc=1</tt>)</dt> <dd>The error message.</dd> <dt><tt>value</tt> (when <tt>rc=0</tt>)</dt> <dd>This can be any json object, depending on the transaction named in <tt>@cmd.1</tt>.</dd> </dl> ]]> </html> </layer> </widget>

You will see the documentation when you access your endpoint in the GUI through /api/[path_to_quickapp].

QuickApp variables for Dynamic APIThe following table contains the variables you can use in your QuickApp that provide information about theHTTP request.

Variable Name Description

@path Provides any path given on the URL after the path to the QuickAppor widget

@http_method Provides the HTTP method used to request the resource. (Thiscould be any string, but generally it is GET or POST)

@content_type In the case of an HTTP post where the content_type isnot application/json or application/x-www-form-urlencoded, this contains the full content of the post.

@content In the case of an HTTP post where the content_type isnot application/json or application/x-www-form-urlencoded, this contains the full content of the post.

1010data Dynamic API Reference Manual | Appendix: URL conventions | 29

Appendix: URL conventions

This section describes how the Dynamic API parses the URL you provide for calling the Dynamic API built-in endpoints or user-defined QuickApps.

Table 1: Interacting with 1010 paths

Syntax Description Examples

/api Invoke /api/index, runningdefault.api.index

/api

/api/[e] Invoke standard endpointdefault.api.[e]

/api/sql

/api/ls

/api/[e.p] If [e.p] is a directory, invoke/api/ls/[e.p]. If you are in a top leveldirectory, use api/ls/dir to resolve theambiguity between this and a standardendpoint (default.api.[e]).

If [e.p] is a table or query, return theresulting data.

If [e.p] is a library, invoke /api/defs/[e.p]. If [e.p] has a query at thebottom, including dynamic queries, that willbe run instead.

If [e.p] is a QuickApp, run the QuickApp.If the QuickApp has a widget named index,return the data from that widget. Otherwise,return a table of widgets. (You shouldprobably first use /api/new/[e.p] toobtain a new tag.)

/api/pub.demo.weather

/api/pub.demo.weather.stations

Table 2: Interacting with QuickApps that don't contain a widget named "index"


/api/[e] Fetch dynamic status table for endpoint [e] /api/path.to.quickapp

/api/[e]/[y] Fetch widget [y] for endpoint [e]. Since thereis no index widget, Dynamic API will treat[y] as a widget name.

/api/path.to.quickapp/widget_name

Table 3: Interacting with QuickApps that do contain a widget named "index"


/api/[e] Render or return the value of the widgetnamed index

/api/ls (the index widget iscalled by default)

/api/[e]/[y] Set variable named path equal to [y]and render or return the value of thewidget named index. [y] must not beginwith a period (.) The Dynamic API willautomatically resolve to any widget named

/api/ls/directory.subdirectory



index, so no reference to a widget name inthe path is necessary.

api/[e]/.[w] Render or return the value of the widgetnamed [w]. The period (.) tells the DynamicAPI to treat [w] as a widget instead ofsomething to set in the path variable. SinceDynamic API automatically resolves to anywidget named index, you must overridethis behavior using the .[w] syntax.

/api/path.to.quickapp/.widget_name

api/[e]/@ Explicitly requests the widget status tablewhen [e] contains an index widget.

/api/path.to.quickapp/@

Table 4: Calling widget methods


/api/[e]!methods List methods available for a widget.Each widget has its own list of supportedmethods, and users can implement theirown methods by writing custom widgets.[e] must resolve to a widget, either bybeing of the form path.to.quickapp/widget_name or by specifying a QuickAppwith a widget named index.

/api/path.to.quickapp/my_grid_widget!methods

/api/[e]/.[w]![m] Invoke method [m] on widget [w] forQuickApp [e]. These methods are definedper the widget class you are using.

!data, !cols, !rows, !head

/api/![m] Invoke root-level method [m] /api/!login

/api/!logout

Table 5: Setting variables in QuickApps


/api/[e]?[y]=[z]&[a]=[b]

Sets dynamic variable @y to value z and @ato value. You can also set variables in thebody of the POST request.

/api/path.to.quickapp?date=20200101&state=NY

/api/[e]?_[y]=[z](There is aleading _ in variablename)

Sets special system "variables" availableto the QuickApp. For example, _fmt is asystem variable that determines the formatof the output from the Dynamic API.

_fmt=json

/api/[e]?[x]_=[y](There is a trailing _ invariable name)

Sets parameters for widget methods. Forexample, for the data method, calling /api/[e]!data?range_=1&to_=10 callsdata(range=2, to=10) for widget [e].

range_, to_


Table 6: Changing output format


/api/[e]:[f]

You can also use thealternate format asfollows: /api/[e]?_fmt=[f]

Render output in format [f]. The defaultformat is html. [f] is an alias for settingthe system variable _fmt=[f].

The format goes before the URL query, forexample: /api/[e]:[f]?[a]=[b]

:html, :text, :json,:json.keyed

Alternatively, _fmt=json,_fmt=text

new 1010data dynamic api reference manual · 2020. 3. 10. · 1010data dynamic api reference manual...

Documents