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.

They may also be stored in environment variables: OTP_CLIENT_ID and OTP_CLIENT_SECRET

Example REST Request for executing SQL Statement Returning a CSV

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
WHERE SYMBOL_NAME='VOD'
and TIMESTAMP >= '2024-01-03 00:00:00 UTC'
and TIMESTAMP < '2024-01-04 00:00:00 UTC'
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)
headers = {
    "Authorization": f"Bearer {access_token}",
    "Content-Type": "application/json"
}
response = requests.post(rest_url, json=params, 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 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.

They may also be stored in environment variables: OTP_CLIENT_ID and OTP_CLIENT_SECRET

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
WHERE SYMBOL_NAME='VOD'
and TIMESTAMP >= '2024-01-03 00:00:00 UTC'
and TIMESTAMP < '2024-01-04 00:00:00 UTC'
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)

Arrow Flight

SQL Queries can be issued via Python to a OneTick server via Arrow Flight using ADBC, returning data directly into a dataframe. OneTick Cloud requires the specification of a client_id and client_secret which can be retrieved from your profile, after registering.

They may also be stored in environment variables: OTP_CLIENT_ID and OTP_CLIENT_SECRET

Example Arrow Flight ADBC script for executing SQL Statement

import adbc_driver_flightsql.dbapi
import requests, json
import pandas as pd

flight_address = "grpc+tcp://proxy.cloud.onetick.com:32010"
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
WHERE SYMBOL_NAME='VOD'
and TIMESTAMP >= '2024-01-03 00:00:00 UTC'
and TIMESTAMP < '2024-01-04 00:00:00 UTC'
LIMIT 10
"""

# Receive and add Access Token
access_token = get_access_token(access_token_url, client_id, client_secret)
db_kwargs = {adbc_driver_flightsql.DatabaseOptions.AUTHORIZATION_HEADER.value: f"Bearer {access_token}"}

# Execute Query
with adbc_driver_flightsql.dbapi.connect(flight_address, db_kwargs=db_kwargs) as conn:
    with conn.cursor() as cursor:
        cursor.execute(sql_statement)
        arrow_table = cursor.fetch_arrow_table()
        df = arrow_table.to_pandas()
        print(df)