V2/university/T02002-Luminesce-syntax/Luminesce Syntax.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "857f3b0d",
   "metadata": {
    "scrolled": true,
    "slideshow": {
     "slide_type": "skip"
    }
   },
   "outputs": [],
   "source": [
    "from finbourne_sdk_utils.jupyter_tools import toggle_code\n",
    "\n",
    "\"\"\"Luminesce Syntax\n",
    "\n",
    "Attributes\n",
    "----------\n",
    "\n",
    "\"\"\"\n",
    "\n",
    "toggle_code(\"Toggle Docstring\")\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e758aa05",
   "metadata": {
    "slideshow": {
     "slide_type": "skip"
    }
   },
   "source": [
    "*Setting up the environment for the tutorial*\n",
    "\n",
    "This section of the notebook creates a portfolio, some equities, and a file in drive to run luminesce queries against."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "78404ef0",
   "metadata": {
    "scrolled": true,
    "slideshow": {
     "slide_type": "skip"
    }
   },
   "outputs": [],
   "source": [
    "from lusid.extensions import (\n",
    "    SyncApiClientFactory,\n",
    "    ArgsConfigurationLoader,\n",
    "    EnvironmentVariablesConfigurationLoader,\n",
    "    SecretsFileConfigurationLoader\n",
    ")\n",
    "from lusidjam import RefreshingToken\n",
    "import os\n",
    "import lusid as lu\n",
    "import lusid.models as lm\n",
    "import pandas as pd\n",
    "import lusid_drive\n",
    "import lusid_drive.rest\n",
    "import lusid.rest\n",
    "from lusid_drive.rest import ApiException\n",
    "from pprint import pprint\n",
    "import json\n",
    "\n",
    "# Authenticate to SDK\n",
    "# Run the Notebook in Jupyterhub for your LUSID domain and authenticate automatically\n",
    "secrets_path = os.getenv(\"FBN_SECRETS_PATH\")\n",
    "# Run the Notebook locally using a secrets file (see https://support.lusid.com/docs/how-do-i-use-an-api-access-token-with-the-lusid-sdk)\n",
    "if secrets_path is None:\n",
    "    secrets_path = os.path.join(os.path.dirname(os.getcwd()), \"secrets.json\")\n",
    "\n",
    "# Initiate an API Factory which is the client side object for interacting with LUSID APIs\n",
    "config_loaders=[\n",
    "    ArgsConfigurationLoader(access_token = RefreshingToken(), app_name = \"LusidJupyterNotebook\"),\n",
    "    EnvironmentVariablesConfigurationLoader(),\n",
    "    SecretsFileConfigurationLoader(secrets_path)]\n",
    "api_factory = lusid.extensions.SyncApiClientFactory(config_loaders=config_loaders)\n",
    " \n",
    "\n",
    "# Confirm success by printing SDK version\n",
    "api_status = pd.DataFrame(api_factory.build(lu.ApplicationMetadataApi).get_lusid_versions().to_dict())\n",
    "display(api_status)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "38e5b530",
   "metadata": {
    "scrolled": true,
    "slideshow": {
     "slide_type": "skip"
    }
   },
   "outputs": [],
   "source": [
    "# here's some sample equities we've prepared earlier\n",
    "uk_equity_instruments = pd.read_csv('_data/uk_equity_instruments.csv')\n",
    "us_equity_instruments = pd.read_csv('_data/us_equity_instruments.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "d06b99a7",
   "metadata": {
    "scrolled": true,
    "slideshow": {
     "slide_type": "skip"
    }
   },
   "outputs": [],
   "source": [
    "# don't show exception if error is due to upsert\n",
    "def exception_guard(e, code):\n",
    "    return e.status and e.status != '400 Bad Request' and e.body and json.loads(e.body)[\"code\"] == code\n",
    "\n",
    "scope = 'FBNUniversityModule-T2-2' # str | The scope of the portfolio."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "539b9389",
   "metadata": {
    "scrolled": true,
    "slideshow": {
     "slide_type": "skip"
    }
   },
   "outputs": [],
   "source": [
    "# create a request to upsert a bunch of instruments.\n",
    "def generate_upsert_instrument_request(api_instance, name, ccy, figi, isin, other_identifier_name, other_identifier):\n",
    "    request_id = name + isin\n",
    "    request ={\"name\":name,\n",
    "              \"identifiers\":{ \n",
    "                  \"Figi\":{\"value\":figi},\n",
    "                  \"Isin\":{\"value\":isin},\n",
    "                  other_identifier_name:{\"value\":other_identifier}\n",
    "                },\n",
    "              \"definition\":lm.Equity(\n",
    "                              instrument_type = \"Equity\",\n",
    "                              dom_ccy = ccy,\n",
    "                              identifiers = {}\n",
    "                          )\n",
    "    }\n",
    "    return request_id, request"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "050894a5",
   "metadata": {
    "scrolled": true,
    "slideshow": {
     "slide_type": "skip"
    }
   },
   "outputs": [],
   "source": [
    "# Create an instance of the API class\n",
    "api_instance = api_factory.build(lu.InstrumentsApi)\n",
    "\n",
    "# We're going to load some american and UK equities into LUSID\n",
    "\n",
    "other_identifier_name = 'Sedol'\n",
    "ccy = 'GBP'\n",
    "upsert_body_tuple = (generate_upsert_instrument_request(api_instance, name, ccy, figi, isin, other_identifier_name, other_identifier)\n",
    "               for \n",
    "               index, ticker, name, _, isin, other_identifier, figi\n",
    "               in uk_equity_instruments.itertuples())\n",
    "uk_equity_body_dict = {request_id:request_body for request_id,request_body in upsert_body_tuple}\n",
    "other_identifier_name = 'Cusip'\n",
    "ccy = 'USD'\n",
    "upsert_body_tuple = (generate_upsert_instrument_request(api_instance, name, ccy, figi, isin, other_identifier_name, other_identifier)\n",
    "               for \n",
    "               index, ticker, name, _, isin, other_identifier, figi\n",
    "               in us_equity_instruments.itertuples())\n",
    "us_equity_body_dict = {request_id:request_body for request_id,request_body in upsert_body_tuple}\n",
    "\n",
    "request_body_dict = us_equity_body_dict | uk_equity_body_dict\n",
    "\n",
    "try:\n",
    "    # UpsertInstruments: Upsert instruments\n",
    "    api_response = api_instance.upsert_instruments(request_body_dict, scope=scope)\n",
    "#     pprint(api_response)\n",
    "except lusid.rest.ApiException as e:\n",
    "    print(\"Exception when calling InstrumentsApi->upsert_instruments: %s\\n\" % e)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "22c5f58c",
   "metadata": {
    "scrolled": true,
    "slideshow": {
     "slide_type": "skip"
    }
   },
   "outputs": [],
   "source": [
    "\n",
    "from lusid_drive.extensions import (\n",
    "    SyncApiClientFactory,\n",
    "    ArgsConfigurationLoader,\n",
    "    EnvironmentVariablesConfigurationLoader,\n",
    "    SecretsFileConfigurationLoader\n",
    ")\n",
    "from lusid_drive.models import *\n",
    "\n",
    "# Authenticate to SDK\n",
    "# Run the Notebook in Jupyterhub for your LUSID domain and authenticate automatically\n",
    "secrets_path = os.getenv(\"FBN_SECRETS_PATH\")\n",
    "api_url_drive = os.getenv(\"FBN_DRIVE_API_URL\")\n",
    "# Run the Notebook locally using a secrets file (see https://support.lusid.com/docs/how-do-i-use-an-api-access-token-with-the-lusid-sdk)\n",
    "if secrets_path is None:\n",
    "    secrets_path = os.path.join(os.path.dirname(os.getcwd()), \"secrets.json\")\n",
    "\n",
    "# Initiate an API Factory which is the client side object for interacting with LUSID APIs\n",
    "config_loaders_drive=[\n",
    "    ArgsConfigurationLoader(access_token = RefreshingToken(), app_name = \"LusidJupyterNotebook\", api_url=api_url_drive),\n",
    "    EnvironmentVariablesConfigurationLoader(),\n",
    "    SecretsFileConfigurationLoader(secrets_path)]\n",
    "api_factory_drive = lusid_drive.extensions.SyncApiClientFactory(config_loaders=config_loaders_drive)\n",
    " \n",
    "folders_api = api_factory_drive.build(lusid_drive.api.FoldersApi)\n",
    "\n",
    "# let's chuck a file containing a randomly selected subset of 25 instruments with their sectors into drive for some examples:\n",
    "subset_file = open('_data/instrument_subset.csv').read()\n",
    "create_folder = {\"path\":\"/\",\"name\":\"FBN-University\"} # CreateFolder | A CreateFolder object that defines the name and path of the new folder\n",
    "\n",
    "try:\n",
    "    # [EARLY ACCESS] CreateFolder: Create a new folder in LUSID Drive\n",
    "    api_response = folders_api.create_folder(create_folder)\n",
    "    pprint(api_response)\n",
    "except ApiException as e:\n",
    "    if not exception_guard(e, 664):\n",
    "        print(\"Exception when calling FilesApi->create_file: %s\\n\" % e)\n",
    "    \n",
    "files_api = api_factory_drive.build(lusid_drive.api.FilesApi)\n",
    "    \n",
    "x_lusid_drive_filename = 'instrument_subset.csv' # str | File name.\n",
    "x_lusid_drive_path = '/FBN-University/' # str | File path.\n",
    "content_length = len(subset_file.encode('UTF-8'))\n",
    "body = subset_file # str | \n",
    "\n",
    "try:\n",
    "    # [EARLY ACCESS] CreateFile: Uploads a file to Lusid Drive. If using an SDK, consider using the UploadAsStreamAsync function for larger files instead.\n",
    "    api_response = files_api.create_file(x_lusid_drive_filename, x_lusid_drive_path, content_length, body)\n",
    "except lusid_drive.rest.ApiException as e:\n",
    "    if not exception_guard(e, 671):\n",
    "        print(\"Exception when calling FilesApi->create_file: %s\\n\" % e)\n",
    "        "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b5aa353f",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "# Luminesce Syntax"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9a0943e0",
   "metadata": {
    "slideshow": {
     "slide_type": "notes"
    }
   },
   "source": [
    "In this part of the course we will provide some additional technical information about luminesce, and demonstrate how luminesce queries can be used to extract information from your data."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8949ca74",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## What is Structured Query Language (SQL)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c9cfbf90",
   "metadata": {
    "slideshow": {
     "slide_type": "notes"
    }
   },
   "source": [
    "SQL - pronounced \"sequel\" was first developed at IBM in the early 1970s, as a way to query and manipulate structured relational databases. SQL is a set-based, declarative programming language. Many implementations of SQL add extensions with procedural programming functionality, such as control-of-flow contructs."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b252e64d",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Luminesce Queries - SQL with extensions"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2717f2d7",
   "metadata": {
    "slideshow": {
     "slide_type": "notes"
    }
   },
   "source": [
    "\n",
    "SQLite is the most widely deployed database engine, used by several of the top web browsers, operating systems and application software.\n",
    "\n",
    "Luminesce supports most of the query syntax for the SQLite dialect of SQL, but not the data manipulation or data definition syntax. Data can still be manipulated using some extensions to the language that Luminesce adds. \n",
    "\n",
    "Luminesce adds extensions to the SQLite implementation which can:\n",
    "- exclude columns from queries\n",
    "- automatically select important columns in queries\n",
    "- parameterise queries\n",
    "- write data to some Luminesce providers\n",
    "- wait on results of some dependent operation, or for a time period\n",
    "- run queries in parallel on an iterated range of parameters.\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "83425e0e",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Luminesce providers"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "aa441797",
   "metadata": {
    "slideshow": {
     "slide_type": "notes"
    }
   },
   "source": [
    "\n",
    "A provider is a component that enables you to write a Luminesce SQL query for a data source in situ, without first having to extract, transform or load data from that source.\n",
    "\n",
    "We supply providers for numerous data sources, including for the investment data stored in LUSID itself, for files stored in Drive and for some external data providers. You can also create your own provider for a proprietary data source.\n",
    "\n",
    "There are two types of provider: a data provider and a direct provider.\n",
    "\n",
    "A data provider is designed to query a data source whose shape is known. It therefore returns a table of results with a fixed number of fields (columns).\n",
    "\n",
    "A direct provider is designed to query a data source whose shape is not known, and thus cannot return a table of results with a fixed number of fields (columns).\n",
    "\n",
    "We'll use both data and direct providers in this tutorial."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5fb552df",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Use case: Using Luminesce to query and join data from disparate sources, and to distribute the resulting information."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2d07e309",
   "metadata": {
    "slideshow": {
     "slide_type": "notes"
    }
   },
   "source": [
    "\n",
    "I'd like to read a file from Drive and use the data from this file to filter transaction results. I'd like to join these filtered results with another provider and distribute the results. This will be packaged up into a simple to use View which runs on a schedule."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f1f5f0c8",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## An intro to SQLite queries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7854d904",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Only required if running locally:\n",
    "!pip3 install -U lumipy"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "9b0c5379",
   "metadata": {},
   "outputs": [],
   "source": [
    "## Only required if running locally:\n",
    "import os\n",
    "from IPython.core.magic import (register_line_cell_magic)\n",
    "from lumipy.client import Client\n",
    "from lusidjam import RefreshingToken\n",
    "\n",
    "token = RefreshingToken()\n",
    "lumi_url = os.getenv(\"FBN_LUMI_API_URL\")\n",
    "\n",
    "@register_line_cell_magic\n",
    "def luminesce(line, cell=None):\n",
    "    query = cell if cell is not None else line\n",
    "\n",
    "    try:\n",
    "        lm_client = Client(token=token, api_url=lumi_url)\n",
    "    except TypeError:\n",
    "        # Attempt to use V2 SDK syntax if V1 syntax fails.\n",
    "        # This gives V2 SDK support for luminesce magic\n",
    "        lm_client = Client(access_token=token, api_url=lumi_url)\n",
    "\n",
    "    df = lm_client.query_and_fetch(query)\n",
    "            \n",
    "    return df\n",
    "\n",
    "# In an interactive session, we need to delete to avoid name conflicts for automagic to work on line magics.\n",
    "del luminesce"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "837657e4",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "### A query using a simple SELECT statement"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5a86b265",
   "metadata": {
    "slideshow": {
     "slide_type": "notes"
    }
   },
   "source": [
    "\n",
    "Its simple to get data from a provider using a select statement.\n",
    "\n",
    "Use the following statement to get all data from a provider:\n",
    "\n",
    "`SELECT * FROM SOME.PROVIDER`\n",
    "\n",
    "Or retrieve data from a subset of columns by specifying them:\n",
    "\n",
    "`SELECT A_COLUMN, ANOTHER_COLUMN FROM SOME.PROVIDER`\n",
    "\n",
    "Here we'll use a SELECT statement to query for all of our porfolios."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1f69451c",
   "metadata": {
    "scrolled": true,
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [],
   "source": [
    "%%luminesce\n",
    "SELECT * \n",
    "FROM Lusid.Instrument.Equity\n",
    "LIMIT 100"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a33d49b0",
   "metadata": {
    "slideshow": {
     "slide_type": "notes"
    }
   },
   "source": [
    "In this example, we've selected all columns and rows from the Lusid.Instrument.Equity provider. We can specify column names if we are only interested in some parts of the data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "105482e5",
   "metadata": {
    "scrolled": true,
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [],
   "source": [
    "%%luminesce\n",
    "SELECT DisplayName, Figi \n",
    "FROM Lusid.Instrument.Equity\n",
    "LIMIT 100"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "876dc339",
   "metadata": {
    "slideshow": {
     "slide_type": "notes"
    }
   },
   "source": [
    "Here I really just want to see the name of the Equity and the Figi, I've selected only those columns from the provider."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "92a27e01",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "### Limiting the number of return results using LIMIT"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3ca39194",
   "metadata": {
    "slideshow": {
     "slide_type": "notes"
    }
   },
   "source": [
    "\n",
    "These simple SELECT statements are useful for getting an idea of the shape and quality of the data returned by a provider, but they tend to return a lot of data. Lets use LIMIT to reduce the amount of data requested:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "23b4dd53",
   "metadata": {
    "scrolled": true,
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [],
   "source": [
    "%%luminesce\n",
    "SELECT DisplayName, Figi \n",
    "FROM Lusid.Instrument.Equity \n",
    "LIMIT 5"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a6802a3e",
   "metadata": {
    "slideshow": {
     "slide_type": "notes"
    }
   },
   "source": [
    "This example returns the first 5 rows of data from the Lusid.Instrument.Equity provider."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a498a6f1",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "### Filter queries WHERE ..."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "461baca2",
   "metadata": {
    "slideshow": {
     "slide_type": "notes"
    }
   },
   "source": [
    "\n",
    "We can now filter these results down - lets get all of the Equities that are listed in GBP\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c3279a17",
   "metadata": {
    "scrolled": true,
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [],
   "source": [
    "%%luminesce\n",
    "SELECT * \n",
    "FROM Lusid.Instrument.Equity \n",
    "WHERE DomCcy = 'GBP'\n",
    "LIMIT 100"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "46c96349",
   "metadata": {
    "slideshow": {
     "slide_type": "notes"
    }
   },
   "source": [
    "We've filtered our query for equities who have a domestic currency of Pound Sterling."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "48cdf186",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "### JOINing disparate datasources with join"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0edc7951",
   "metadata": {
    "slideshow": {
     "slide_type": "notes"
    }
   },
   "source": [
    "\n",
    "Now, I've got a file in drive with sector information for some of these instruments. I'm going to join data from the file using our Drive provider with the saved instrument data so I can see the sectors for instruments where we know this information.\n",
    "\n",
    "First lets get our instrument data.\n",
    "\n",
    "Then we'll grab the file contents from drive.\n",
    "\n",
    "We'll take a look at cross-joins, which SQLite uses to build left joins and inner joins. Cross-joins return the cartesian product of both tables - each row on the left table is concatenated with every row on the right table, to form a massive table with every combination of both tables' rows:\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8bb14b19",
   "metadata": {
    "scrolled": true,
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [],
   "source": [
    "%%luminesce\n",
    "\n",
    "/*\n",
    "    We'll cover using variables later in the tutorial\n",
    "*/\n",
    "\n",
    "@x = use Drive.Csv\n",
    "--file=/finbourne university/instrument_subset.csv\n",
    "enduse;\n",
    "\n",
    "SELECT DisplayName, DomCcy, File.Sector \n",
    "FROM Lusid.Instrument.Equity as Equities \n",
    "CROSS JOIN @x as File\n",
    "LIMIT 100"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1873d777",
   "metadata": {
    "slideshow": {
     "slide_type": "notes"
    }
   },
   "source": [
    "We've returned a table with every combination of Lusid Equities with every row in our file.\n",
    "\n",
    "In a left join, all rows from the left table are returned, along with rows in the right table that match the supplied key constraint. If there is a row in the left table with no matching row on the right, the columns that come from the right table in the resulting row are set to null."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "27bdbbd0",
   "metadata": {
    "scrolled": true,
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [],
   "source": [
    "%%luminesce\n",
    "\n",
    "/*\n",
    "    We'll cover using variables later in the tutorial\n",
    "*/\n",
    "\n",
    "@x = use Drive.Csv\n",
    "--file=/finbourne university/instrument_subset.csv\n",
    "enduse;\n",
    "\n",
    "SELECT DisplayName, DomCcy, File.Sector, Equities.Figi as Figi \n",
    "FROM @x as File\n",
    "LEFT OUTER JOIN Lusid.Instrument.Equity as Equities\n",
    "on Equities.Figi = File.Figi\n",
    "LIMIT 100"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6a2bb78a",
   "metadata": {
    "slideshow": {
     "slide_type": "notes"
    }
   },
   "source": [
    "We see this here as there are some companies with no matching sector in our file - these are returned as null.\n",
    "\n",
    "We are going to ignore instruments without a matching sector for now, so we'll use an inner join.\n",
    "Inner joins only return rows where the joining key has a match on both sides of the table:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c00eae4d",
   "metadata": {
    "scrolled": false,
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [],
   "source": [
    "%%luminesce\n",
    "\n",
    "/*\n",
    "    We'll cover using variables later in the tutorial\n",
    "*/\n",
    "\n",
    "@x = use Drive.Csv\n",
    "--file=/finbourne university/instrument_subset.csv\n",
    "enduse;\n",
    "\n",
    "SELECT DisplayName, DomCcy, File.Sector, Equities.Figi as Figi \n",
    "FROM @x as File \n",
    "INNER JOIN Lusid.Instrument.Equity as Equities  on File.Figi = Equities.Figi \n",
    "LIMIT 100\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7a721fe8",
   "metadata": {
    "slideshow": {
     "slide_type": "notes"
    }
   },
   "source": [
    "Now we have a joined table where only Equities with a sector on file are returned, along with their sector."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "06b6e648",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "### GROUPing results BY some condition in query"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "91808b58",
   "metadata": {
    "slideshow": {
     "slide_type": "notes"
    }
   },
   "source": [
    "\n",
    "We can also group our results by a column - we'll perform an aggregation and group-by to count the number of instrument in each sector.\n",
    "\n",
    "There are many different aggregation functions in SQLite, such as sum, avg and count.\n",
    "\n",
    "Sum will return the sum of the specified field in each group.\n",
    "Average returns the mean of the specified field in each group.\n",
    "Count returns the number of results that are non-null for the field in each group. We can also specify that count returns the count of distinct values [Count(Distinct <field>)], or all rows [count(*)], in each group."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f478c5e0",
   "metadata": {
    "scrolled": true,
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [],
   "source": [
    "%%luminesce\n",
    "\n",
    "/*\n",
    "    We'll cover using variables later in the tutorial\n",
    "*/\n",
    "\n",
    "@x = use Drive.Csv\n",
    "--file=/finbourne university/instrument_subset.csv\n",
    "enduse;\n",
    "\n",
    "SELECT count(File.Sector), File.Sector\n",
    "FROM @x as File\n",
    "LEFT OUTER JOIN Lusid.Instrument.Equity as Equities\n",
    "on Equities.Figi = File.Figi\n",
    "group by File.Sector\n",
    "LIMIT 100"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0b04d64a",
   "metadata": {
    "slideshow": {
     "slide_type": "notes"
    }
   },
   "source": [
    "We've now grouped our results by sector, and for each sector, we''ve returned how many Equities belong to the sector."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9b370ea3",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## Introducing some Luminesce extensions\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1baa55f5",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "### Using ^ to SELECT the most important columns from a provider"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fbb4493d",
   "metadata": {
    "slideshow": {
     "slide_type": "notes"
    }
   },
   "source": [
    "\n",
    "Let's use the carat symbol to grab then more important columns in the Equity provider. The list of important columns is decided by the writer of the Provider."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3f9e2a43",
   "metadata": {
    "scrolled": true,
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [],
   "source": [
    "%%luminesce\n",
    "SELECT ^ \n",
    "FROM Lusid.Instrument.Equity\n",
    "LIMIT 100"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dabe3e76",
   "metadata": {
    "slideshow": {
     "slide_type": "notes"
    }
   },
   "source": [
    "In the Lusid Equities provider: LusidInstrumentId, Scope, DisplayName, InferredAssetClass, InferredDomCcy and IsActive are the fields marked by the provider as being most important."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "77213a4b",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "### Excluding columns from the results of a query using the except keyword."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "034db0f7",
   "metadata": {
    "slideshow": {
     "slide_type": "notes"
    }
   },
   "source": [
    "\n",
    "Using some providers, we can also exclude columns from the result of a query by using the except keyword:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "68a78ab6",
   "metadata": {
    "scrolled": true,
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [],
   "source": [
    "%%luminesce\n",
    "SELECT ^ (except Type, IsActive) \n",
    "FROM Lusid.Instrument.Equity\n",
    "LIMIT 100"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "84a844b0",
   "metadata": {
    "slideshow": {
     "slide_type": "notes"
    }
   },
   "source": [
    "Here we select the most important columns again, but exclude the Type and IsActive columns"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4c5b596a",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "### Using parameters to filter queries"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e4b22722",
   "metadata": {
    "slideshow": {
     "slide_type": "notes"
    }
   },
   "source": [
    "\n",
    "Many providers will have parameters that can also be used to filter a Luminesce query as part of a standard WHERE clause.\n",
    "\n",
    "We can see these parameters by querying the Sys.Field Provider."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d8cec937",
   "metadata": {
    "scrolled": true,
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [],
   "source": [
    "%%luminesce\n",
    "SELECT FieldName, DataType, ParamDefaultValue, Description \n",
    "FROM Sys.Field \n",
    "WHERE TableName = 'Lusid.Instrument.Equity' \n",
    "AND FieldType = 'Parameter'\n",
    "LIMIT 100"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8063a5e3",
   "metadata": {
    "slideshow": {
     "slide_type": "notes"
    }
   },
   "source": [
    "We can see there are 3 fields we can filter our query on: AsAt, EffectiveAt and UseLusidFilter"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "19dc8809",
   "metadata": {
    "scrolled": true,
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [],
   "source": [
    "%%luminesce\n",
    "\n",
    "SELECT * \n",
    "FROM Lusid.Instrument.Equity\n",
    "WHERE UseLusidFilter = False\n",
    "LIMIT 100"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6e9319b2",
   "metadata": {
    "slideshow": {
     "slide_type": "notes"
    }
   },
   "source": [
    "Here we've filtered an Equity query on the UseLusidFilter. Notice there is no returned Field called UseLusidFilter - its not a column in our data."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5522f8b8",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Using Luminesce variables\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e9f326fc",
   "metadata": {
    "slideshow": {
     "slide_type": "notes"
    }
   },
   "source": [
    "You can use variables to create and populate arbitrary tables of data that can then be used as part of a Luminesce query for either a data provider or a direct provider.\n",
    "\n",
    "Declare a variable that represents a table of data using:\n",
    "`@variable`\n",
    "\n",
    "Or to represent a single value:\n",
    "`@@variable`\n",
    "\n",
    "Let's see variables in action. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "379ca3df",
   "metadata": {
    "scrolled": true,
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [],
   "source": [
    "%%luminesce\n",
    "@instruments_in_GBP = SELECT * \n",
    "                      FROM Lusid.Instrument.Equity \n",
    "                      WHERE DomCcy = 'GBP'\n",
    "                      LIMIT 100;\n",
    "SELECT Isin \n",
    "FROM @instruments_in_GBP;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f03b8022",
   "metadata": {
    "slideshow": {
     "slide_type": "notes"
    }
   },
   "source": [
    "Here_instruments_in GBP is a table variable, set from the results of the query on equities."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "687ecedc",
   "metadata": {
    "scrolled": true,
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [],
   "source": [
    "%%luminesce\n",
    "@sectors = use Drive.Csv\n",
    "--file=/finbourne university/instrument_subset.csv\n",
    "enduse;\n",
    "\n",
    "@@selected_sector = SELECT 'Life Insurance';\n",
    "SELECT * \n",
    "FROM @sectors \n",
    "WHERE Sector = @@selected_sector\n",
    "LIMIT 100"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b3fe1026",
   "metadata": {
    "slideshow": {
     "slide_type": "notes"
    }
   },
   "source": [
    "I've set selected_sector as a single-value variable \"Lise Insurance\", and filtered my file by sector."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c28c35ec",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## Introducing more advanced Luminesce extensions\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "353c607b",
   "metadata": {
    "slideshow": {
     "slide_type": "notes"
    }
   },
   "source": [
    "You can use the CROSS APPLY or OUTER APPLY statement to iterate over a set of parameters. This is useful to execute a Luminesce query in parallel for each of the parameter inputs."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bb913632",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "### Cross-apply"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "51b771b2",
   "metadata": {
    "slideshow": {
     "slide_type": "notes"
    }
   },
   "source": [
    "Cross-apply is similar to INNER JOIN, in that only records that have matching values on both sides are returned. \n",
    "We'll run some equity queries in parallel using Tools.split to create a set of currencies to filter by- the table will contain the currency joined to all of the returned equity tables:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "51d52e3f",
   "metadata": {
    "scrolled": true,
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [],
   "source": [
    "%%luminesce\n",
    "\n",
    "select VALUE as val\n",
    "from Tools.Split \n",
    "where Original = 'GBP USD CHF' \n",
    "AND Delimiters = ' ';\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bb85b2a0",
   "metadata": {
    "slideshow": {
     "slide_type": "notes"
    }
   },
   "source": [
    "First, we use Tools.Split to turn a space separated string of currencies into into a table where each currency is an entry in the table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e418f7d3",
   "metadata": {
    "scrolled": true,
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [],
   "source": [
    "%%luminesce\n",
    "\n",
    "@currencies = select VALUE as val\n",
    "              from Tools.Split \n",
    "              where Original = 'GBP USD CHF' \n",
    "              AND Delimiters = ' ';\n",
    "\n",
    "SELECT c.val as selectedCurrency, results.*\n",
    "FROM @currencies c\n",
    "CROSS APPLY (\n",
    "       SELECT equity.^ \n",
    "       FROM Lusid.Instrument.Equity equity\n",
    "       WHERE equity.DomCcy = c.val\n",
    "       LIMIT 100       \n",
    ") results\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f461d619",
   "metadata": {
    "slideshow": {
     "slide_type": "notes"
    }
   },
   "source": [
    "We then save the result of that query in a variable @currency, which is passed as a criterion in the CROSS APPLY subquery,  which runs in parallel for each row in @currency.\n",
    "\n",
    "This example works similar to an inner join, we've run a query in parallel for some currency values, returning a similar table to an inner join. Remember, inner join returns only rows which fulfil the join criteria."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "508085ed",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "### Outer-apply"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "befd3f0b",
   "metadata": {
    "slideshow": {
     "slide_type": "notes"
    }
   },
   "source": [
    "\n",
    "Outer apply is similar to LEFT OUTER JOIN, in that all records on the left side are returned, even when there are no matching records on the right side."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "14284e9d",
   "metadata": {
    "scrolled": true,
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [],
   "source": [
    "%%luminesce\n",
    "\n",
    "@currencies = select VALUE as val\n",
    "              from Tools.Split \n",
    "              where Original = 'GBP USD CHF' \n",
    "              AND Delimiters = ' ';\n",
    "              \n",
    "SELECT c.val, results.*\n",
    "FROM @currencies c\n",
    "OUTER APPLY (\n",
    "       SELECT equity.^ \n",
    "       FROM Lusid.Instrument.Equity as equity\n",
    "       WHERE equity.DomCcy = c.val\n",
    "       LIMIT 100\n",
    ") results\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "38f0a6cf",
   "metadata": {
    "slideshow": {
     "slide_type": "notes"
    }
   },
   "source": [
    "We've run a query in parallel for some currency values, returning a similar table to a left join with our selected currencies on the left side of the join. Here, the values from Lusid.Instrument.Equity on the CHF row are null."
   ]
  }
 ],
 "metadata": {
  "celltoolbar": "Slideshow",
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.8"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": false,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": false
  },
  "varInspector": {
   "cols": {
    "lenName": 16,
    "lenType": 16,
    "lenVar": 40
   },
   "kernels_config": {
    "python": {
     "delete_cmd_postfix": "",
     "delete_cmd_prefix": "del ",
     "library": "var_list.py",
     "varRefreshCmd": "print(var_dic_list())"
    },
    "r": {
     "delete_cmd_postfix": ") ",
     "delete_cmd_prefix": "rm(",
     "library": "var_list.r",
     "varRefreshCmd": "cat(var_dic_list()) "
    }
   },
   "types_to_exclude": [
    "module",
    "function",
    "builtin_function_or_method",
    "instance",
    "_Feature"
   ],
   "window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}