Access & Installation

SQL access is exposed through Dashboard, REST, Python, ODBC/JDBC and client tools.

Dashboard Access

SQL Access to real time and historic market data stored in OneTick Cloud. and is available in the SQL Querying Examples Dashboard.

This includes both query execution, plus over 80 examples covering data retrieval, and SQL syntax.

REST Access

SQL Queries can be issued via REST to a OneTick server. The REST query includes the parameters:

• query_type equal to sql

• time_zone equal to the desired time zone for the returned data. (e.g. America\\New_York)

• statement equal to the desired SQL Statement

e.g. https://rest.cloud.onetick.com/omdwebapi/rest/?params={"query_type":"sql","response":"csv","compression":"none","limit":"10000","all_times_are_readable":"false","show_times_as_nanos":"true","timezone":"Europe/London","statement":"SELECT * FROM LSE_SAMPLE_BARS.TRD_1M WHERE SYMBOL_NAME='VOD' and TIMESTAMP >= '2024-01-03 00:00:00 UTC' and TIMESTAMP < '2024-01-04 00:00:00 UTC' LIMIT 10"}

REST Queries can be issued via Python by specifying the SQL statement, building the params dictionary, and executing the REST request. OneTick Cloud requires the specification of a client_id and client_secret which can be retrieved from your profile, after registering.

Example REST Request for executing SQL Statement

import requests, json, gzip

rest_url = "https://rest.cloud.onetick.com/omdwebapi/rest/"
access_token_url = "https://cloud-auth.parent.onetick.com/realms/OMD/protocol/openid-connect/token"
# Replace [client_id] and [client_secret] with values retrieved from your profile
client_id = "[client_id]"
client_secret = "[client_secret]"

def get_access_token(url, client_id, client_secret):
    response = requests.post(url,data={"grant_type": "client_credentials"}, auth=(client_id, client_secret))
    return response.json()["access_token"]

sql_statement = "SELECT * FROM LSE_SAMPLE_BARS.TRD_1M "
sql_statement += "WHERE SYMBOL_NAME='VOD' "
sql_statement += "and TIMESTAMP >= '2024-01-03 00:00:00 UTC' "
sql_statement += "and TIMESTAMP < '2024-01-04 00:00:00 UTC' "
sql_statement += "LIMIT 10 "

params = {
    "query_type": "sql",
    "timezone" : "Europe/London",
    "response": "csv",
    "show_times_as_nanos" : "true",
    "all_times_are_readable" : "false",
    "compression":"gzip",
    "limit":"1000",
    "statement":sql_statement
    }

access_token = get_access_token(access_token_url, client_id, client_secret)
data_url = rest_url + "?params=" + json.dumps(params)
headers = {
    "Authorization": f"Bearer {access_token}",
    "Content-Type": "application/json"
}
response = requests.get(data_url, headers=headers)
uncompressed = gzip.decompress(response.content)
print(uncompressed.decode('utf-8'))

Python Access

SQL Queries can be issued via Python to a OneTick server using our Python API (onetick.query_webapi), returning data directly into a dataframe. To install the python wheel:

pip install of OneTick Python Wheels

pip install -U --index-url https://pypi:NXBNcG86@pip.distribution.sol.onetick.com/simple/ onetick-py onetick-query-webapi

SQL statements are stored in a query object using otq.SqlQuery, and the query is executed using otq.run. OneTick Cloud requires the specification of a client_id and client_secret which can be retrieved from your profile, after registering.

Example onetick.query_webapi script for executing SQL Statement

import datetime, requests, json
import pandas as pd
import onetick.query_webapi as otq  # See the About Tab for how to download and install from our pip server

http_address = "https://rest.cloud.onetick.com:443"
access_token_url = "https://cloud-auth.parent.onetick.com/realms/OMD/protocol/openid-connect/token"
# Replace [client id] and [client_secret] with values retrieved from your profile
client_id = "[client id]"
client_secret = "[client secret]"


sql_statement = "SELECT * FROM LSE_SAMPLE_BARS.TRD_1M "
sql_statement += "WHERE SYMBOL_NAME='VOD' "
sql_statement += "and TIMESTAMP >= '2024-01-03 00:00:00 UTC' "
sql_statement += "and TIMESTAMP < '2024-01-04 00:00:00 UTC' "
sql_statement += "LIMIT 10 "

query = otq.SqlQuery(sql_statement=sql_statement)
query.set_merge_all_symbols_flag(True)

access_token = otq.get_access_token(access_token_url, client_id, client_secret)
result = otq.run(query,
    http_address=http_address,access_token=access_token,
    output_mode="pandas",
    timezone="UTC")

for sym in result:
    print(sym)
    print(result.output(sym).data)

ODBC, JDBC & Client Tools

Client Tools together with ODBC & JDBC Drivers are available through our client download via SFTP. This download is available for registered customers.