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 <https://www.onetick.com/cloud-services>`_.
and is available in the `SQL Querying Examples Dashboard <https://authdash.cloud.onetick.com/web_dashboard/?dash=sql>`_.

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 <https://www.onetick.com/cloud-services>`_ requires the specification of a ``client_id`` and ``client_secret`` which can be retrieved from your `profile
<https://authdash.cloud.onetick.com/web_dashboard/?dash=sub_profile>`_, after registering.

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


.. code-block:: python
   :caption: 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:

.. code-block:: python
   :caption: 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 <https://www.onetick.com/cloud-services>`_ requires the specification of a ``client_id`` and ``client_secret`` which can be retrieved from your `profile
<https://authdash.cloud.onetick.com/web_dashboard/?dash=sub_profile>`_, after registering.

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

.. code-block:: python
   :caption: 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 <https://www.onetick.com/cloud-services>`_ requires the specification of a ``client_id`` and ``client_secret`` which can be retrieved from your `profile
<https://authdash.cloud.onetick.com/web_dashboard/?dash=sub_profile>`_, after registering.

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


.. code-block:: python
   :caption: 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)