docs/View management.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "mdt22",
   "metadata": {},
   "source": [
    "# View management\n",
    "\n",
    "You can run the cells below directly in LUSID's JupyterHub.\n",
    "\n",
    "The `%%luminesce` is a magic command which passes the cell query string to Lumipy,\n",
    "which then returns a DataFrame.\n",
    "    "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "mds220",
   "metadata": {},
   "source": [
    "#### Step 1: Create view with no params"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ccs220",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%luminesce\n",
    "\n",
    "-- ==============================================================\n",
    "-- Description:\n",
    "-- 1. This query shows you how to make a view\n",
    "-- 2. The fiew is called \"TestHoldings\" and will appear under \"Test/Example\"\n",
    "-- 3. Luminesce views are effectively custom providers\n",
    "-- 4. You can think of them as being somewhat like stored procs\n",
    "-- ==============================================================\n",
    "\n",
    "-- First delete the view if it already exists\n",
    "\n",
    "@delete_model_portfolios_view =\n",
    "\n",
    "use Sys.Admin.SetupView\n",
    "--provider=Test.Example.TestHoldings\n",
    "--deleteProvider\n",
    "\n",
    "----\n",
    "\n",
    "select 1 as deleting\n",
    "\n",
    "enduse;\n",
    "\n",
    "-- Wait 5 seconds after delete before attempting to re-create\n",
    "-- This give the view time to reset on the Luminesce grid\n",
    "\n",
    "@delete_model_portfolios_view_response =\n",
    "\n",
    "select *\n",
    "from @delete_model_portfolios_view wait 5;\n",
    "\n",
    "-- The Sys.Admin.SetupView provider\n",
    "\n",
    "@model_portfolios_view = use Sys.Admin.SetupView\n",
    "--provider=Test.Example.TestHoldings\n",
    "\n",
    "----\n",
    "\n",
    "@model_portfolios = use Drive.csv\n",
    "--file=/luminesce-examples/model_portfolios.csv\n",
    "enduse;\n",
    "\n",
    "select\n",
    "    #distinct\n",
    "  #select_agg\n",
    "{\n",
    "    {ModelPortfolioName^ : model_port_name},\n",
    "    {Sector: sect},\n",
    "    {Region: loc},\n",
    "    {Weighting: weighting}\n",
    "}\n",
    "\n",
    "from @model_portfolios\n",
    "where #restrict_agg\n",
    "\n",
    "enduse;\n",
    "\n",
    "select * from @model_portfolios_view;\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "mds221",
   "metadata": {},
   "source": [
    "#### Step 2: Fetch sql used to create view"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ccs221",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%luminesce\n",
    "\n",
    "-- ============================================================\n",
    "-- Description:\n",
    "-- 1. This query shows the query which was run to create a view\n",
    "-- ============================================================\n",
    "\n",
    "select\n",
    "   Sql, At\n",
    "from\n",
    "   sys.logs.hcquery\n",
    "where\n",
    "   Sql like '%SetupView%'\n",
    "   and Sql like '%Test.Example.TestHoldings%'\n",
    "   and Sql not like '%HcQuery%'\n",
    "   and ShowAll = true\n",
    "order by At desc\n",
    "limit 1\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "mds222",
   "metadata": {},
   "source": [
    "#### Step 3: Current content of view"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ccs222",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%luminesce\n",
    "\n",
    "-- =========================================================\n",
    "--  Description:\n",
    "--  1. This query shows you the contents of a view\n",
    "--  2. The results alone cannot be used to recreate the view\n",
    "--  3. You will need to query sys.logs.hcquery to get the SQL\n",
    "--  used to create  a view\n",
    "-- =========================================================\n",
    "\n",
    "select\n",
    "  Content\n",
    "from\n",
    "  sys.file\n",
    "where\n",
    "  directory = 'databaseproviders/Test/Example'\n",
    "and\n",
    "  name = 'TestHoldings'\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "mds223",
   "metadata": {},
   "source": [
    "#### Step 4: Show view history"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ccs223",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%luminesce\n",
    "\n",
    "-- ============================================================\n",
    "-- Description:\n",
    "-- This query shows the history of changes to a view\n",
    "-- (essentially SCCM for views)\n",
    "-- ============================================================\n",
    "\n",
    "select\n",
    "  *\n",
    "from\n",
    "  sys.file.history\n",
    "where\n",
    "  directory = 'databaseproviders/Test/Example'\n",
    "and\n",
    "  name = 'TestHoldings'\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "mds224",
   "metadata": {},
   "source": [
    "#### Step 5: Show view dependencies"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ccs224",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%luminesce\n",
    "\n",
    "-- ============================================================\n",
    "-- Description:\n",
    "-- This query shows what deoendencies a view has, including other views and data providers.\n",
    "-- \"includeindirect\" means the dependencies are fully expanded all the way to the data providers \n",
    "-- ============================================================\n",
    "\n",
    "select\n",
    "  *\n",
    "from\n",
    "  sys.dependency\n",
    "where\n",
    "  name = 'Test.Example.Testholdings'\n",
    "and\n",
    "  includeindirect = true\n",
    "and\n",
    "  showall = true\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "mds225",
   "metadata": {},
   "source": [
    "#### Step 6: Show dependencies on view"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ccs225",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%luminesce\n",
    "\n",
    "-- ============================================================\n",
    "-- Description:\n",
    "-- This query shows which views depending on a particular view\n",
    "-- ============================================================\n",
    "\n",
    "select\n",
    "  *\n",
    "from\n",
    "  sys.dependency\n",
    "where\n",
    "  dependency = 'Drive.Csv'\n",
    "and\n",
    "  includeindirect = true\n",
    "and\n",
    "  showall = true\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "mds226",
   "metadata": {},
   "source": [
    "#### Step 7: Delete a view"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ccs226",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%luminesce\n",
    "\n",
    "-- ============================================================\n",
    "-- Description:\n",
    "-- This query shows you how to delete a custom view\n",
    "-- Wait 5 seconds after delete before attempting to re-create\n",
    "-- This give the view time to reset on the Luminesce grid\n",
    "-- ============================================================\n",
    "\n",
    "@delete_model_portfolios_view = use Sys.Admin.SetupView\n",
    "--provider=Test.Example.TestHoldings\n",
    "--deleteProvider\n",
    "----\n",
    "\n",
    "select 1 as deleting_view\n",
    "\n",
    "enduse;\n",
    "\n",
    "select * from @delete_model_portfolios_view;\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "mds227",
   "metadata": {},
   "source": [
    "#### Step 8: Run table of query strings"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ccs227",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%luminesce\n",
    "\n",
    "/*\n",
    "\n",
    "-- Description:\n",
    "-- Run a table of query strings\n",
    "\n",
    "===========================================\n",
    "    1. Create a view to run query strings \n",
    "===========================================\n",
    "*/\n",
    "\n",
    "\n",
    "@sqlRunner = use Sys.Admin.SetupView \n",
    "--provider=AdHocQuery.Runner\n",
    "--parameters   \n",
    "sqlString,Text,select * from sys.field limit 2;,true\n",
    "----\n",
    "\n",
    "@@sep = select '--' || '--';\n",
    "@@sql = select #PARAMETERVALUE(sqlString);\n",
    "\n",
    "@x = use Sys.Admin.SetupView with @@sql, @@sep\n",
    "{@@sep}\n",
    "{@@sql}\n",
    "\n",
    "enduse;\n",
    "\n",
    "select 'Done' as Result;\n",
    "\n",
    "enduse;\n",
    "\n",
    "@createQueryView = select * from @sqlRunner; \n",
    "\n",
    "/*\n",
    "=================================\n",
    "    2. Define a set of queries \n",
    "=================================\n",
    "\n",
    "    In the example below, we have defined the query strings directly in this script.\n",
    "    In practice, you might want to load these from another location such as the LUSID\n",
    "    config store.\n",
    "\n",
    "*/\n",
    "\n",
    "@sqlStrings = select \n",
    "'select * from lusid.instrument limit 5' as 'sql_strings'\n",
    "union all\n",
    "values\n",
    "('select * from sys.field limit 10'),\n",
    "('select LusidInstrumentId, CouponRate from Lusid.Instrument.Bond limit 10');\n",
    "\n",
    "/*\n",
    "===================================\n",
    "    3. Run the queries\n",
    "===================================\n",
    "*/\n",
    "\n",
    "select s.sql_strings, results.*\n",
    "from @sqlStrings s\n",
    "outer apply (\n",
    "        select a.^\n",
    "        from Adhocquery.Runner a\n",
    "        where a.sqlString = s.sql_strings\n",
    ") results;\n",
    "\n"
   ]
  }
 ],
 "metadata": {},
 "nbformat": 4,
 "nbformat_minor": 5
}