docs/Drive.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "mdt1",
   "metadata": {},
   "source": [
    "# Drive\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": "mds10",
   "metadata": {},
   "source": [
    "#### Create and move file in drive"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ccs10",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%luminesce\n",
    "\n",
    "-- ============================================================\n",
    "-- Description:\n",
    "-- 1. This query shows you how to create and move files.\n",
    "-- 2. Files are created using the Drive.SaveAs function.\n",
    "-- 3. Files are moved using the Drive.File.Operation provider\n",
    "-- More details on using Luminesce with Drive here:\n",
    "-- https://support.lusid.com/knowledgebase/article/KA-01688/en-us\n",
    "-- ============================================================\n",
    "\n",
    "-- Define a file and folder names\n",
    "\n",
    "@@file_name = select 'daily_instruments_20220215';\n",
    "@@create_file_location = select '/luminesce-examples/new/';\n",
    "@@new_file_location = select '/luminesce-examples/archive/';\n",
    "\n",
    "-- Collect sample instruments from the Lusid.Instrument provider\n",
    "\n",
    "@instruments = select * from Lusid.Instrument limit 10;\n",
    "\n",
    "-- Save the instruments to Drive as a CSV\n",
    "\n",
    "@save_to_drive = use Drive.SaveAs with @instruments, @@file_name, @@create_file_location\n",
    "--path=/{@@create_file_location}\n",
    "--fileNames={@@file_name}\n",
    "enduse;\n",
    "\n",
    "-- Move the instruments CSV file to a new location\n",
    "-- We need to add the wait statement, so Luminesce knows to finish the file writer,\n",
    "-- before attempting to move the file\n",
    "\n",
    "@move_file_request =\n",
    "select (@@create_file_location || @@file_name || '.csv' ) as [FullPath],\n",
    "(@@new_file_location || @@file_name || '.csv') as [NewFullPath],\n",
    "'MoveRenameMayOverwrite' as [Operation] wait 2;\n",
    "\n",
    "select * from Drive.File.Operation wait 5\n",
    "where UpdatesToPerform = @move_file_request;\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "mds11",
   "metadata": {},
   "source": [
    "#### Create log file on error"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ccs11",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%luminesce\n",
    "\n",
    "-- ============================================================\n",
    "-- Description:\n",
    "-- 1. In this query, we create a table of two transactions.\n",
    "-- 2. These transactions are saved to LUSID Drive.\n",
    "-- ============================================================\n",
    "\n",
    "-- Define some variables for file and folder name\n",
    "\n",
    "@@file_name = select 'daily_transactions_20220215.csv';\n",
    "@@folder_name = select '/luminesce-examples/new';\n",
    "\n",
    "-- populate a variable with a table of transactions\n",
    "\n",
    "@transactions = \n",
    "select \"GB\" as ClientInternal,\n",
    "\"Test\" as PortfolioCode,\n",
    "\"IborTest\" as PortfolioScope, \n",
    "\"GBP\" as TradeCurrency,\n",
    "\"GBP\" as SettlementCurrency,\n",
    "#2022-01-01# as TradeDate,\n",
    "#2022-01-03#  as SettlementDate,\n",
    "100 as TradePrice,\n",
    "\"trd_001\" as TxnId, \n",
    "\"StockIn\" as Type,\n",
    "10 as Units\n",
    "union\n",
    "select\n",
    "\"GB\" as ClientInternal,\n",
    "\"Test\" as PortfolioCode,\n",
    "\"IborTest\" as PortfolioScope, \n",
    "\"GBP\" as TradeCurrency,\n",
    "\"GBP\" as SettlementCurrency,\n",
    "#2022-01-01# as TradeDate,\n",
    "#2022-01-03#  as SettlementDate,\n",
    "100 as TradePrice,\n",
    "\"trd_002\" as TxnId, \n",
    "\"StockIn\" as Type,\n",
    "10 as Units;\n",
    "\n",
    "-- Write the transaction table to LUSID Drive\n",
    "-- You can pass in the @@file_name and @@folder_name as variables.\n",
    "-- You can also pass the file_name as folder name as strings\n",
    "\n",
    "@save_to_drive = use Drive.SaveAs with @transactions, @@file_name, @@folder_name\n",
    "--path=/{@@folder_name}\n",
    "--fileNames={@@file_name}\n",
    "enduse;\n",
    "\n",
    "select * from @save_to_drive;\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "mds12",
   "metadata": {},
   "source": [
    "#### Read a file from drive"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ccs12",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%luminesce\n",
    "\n",
    "-- ===============================================================\n",
    "-- Description:\n",
    "-- 1. This query shows you how to load a CSV file from LUSID Drive\n",
    "-- ===============================================================\n",
    "\n",
    "@data = use Drive.csv\n",
    "--file=/luminesce-examples/equity_transactions.csv\n",
    "enduse;\n",
    "\n",
    "select * from @data;\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "mds13",
   "metadata": {},
   "source": [
    "#### Read an excel file from drive"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ccs13",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%luminesce\n",
    "\n",
    "-- ================================================================\n",
    "-- Description:\n",
    "-- This query shows you how to load an Excel file from LUSID Drive\n",
    "-- We use the worksheet parameter to load a specific worksheet\n",
    "-- See further details on the Drive.Excel provider here:\n",
    "-- https://support.lusid.com/knowledgebase/article/KA-01682/en-us\n",
    "-- ================================================================\n",
    "\n",
    "@@worksheet = select 'holdings';\n",
    "\n",
    "@holdings = use Drive.Excel with @@worksheet\n",
    "--file=/luminesce-examples/equity_holding.xlsx\n",
    "--worksheet={@@worksheet}\n",
    "enduse;\n",
    "\n",
    "select * from @holdings;\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "mds14",
   "metadata": {},
   "source": [
    "#### Save data into drive"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ccs14",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%luminesce\n",
    "\n",
    "-- ============================================================\n",
    "-- Description:\n",
    "-- 1. In this query, we create a table of two transactions.\n",
    "-- 2. These transactions are saved to LUSID Drive.\n",
    "-- ============================================================\n",
    "\n",
    "-- Define some variables for file and folder name\n",
    "\n",
    "@@file_name = select 'daily_transactions_20220215.csv';\n",
    "@@folder_name = select '/luminesce-examples/new';\n",
    "\n",
    "-- populate a variable with a table of transactions\n",
    "\n",
    "@transactions =\n",
    "select \"GB\" as ClientInternal,\n",
    "\"Test\" as PortfolioCode,\n",
    "\"IborTest\" as PortfolioScope, \n",
    "\"GBP\" as TradeCurrency,\n",
    "\"GBP\" as SettlementCurrency,\n",
    "#2022-01-01# as TradeDate,\n",
    "#2022-01-03# as SettlementDate,\n",
    "100 as TradePrice,\n",
    "\"trd_001\" as TxnId, \n",
    "\"StockIn\" as Type,\n",
    "10 as Units\n",
    "union\n",
    "select\n",
    "\"GB\" as ClientInternal,\n",
    "\"Test\" as PortfolioCode,\n",
    "\"IborTest\" as PortfolioScope, \n",
    "\"GBP\" as TradeCurrency,\n",
    "\"GBP\" as SettlementCurrency,\n",
    "#2022-01-01# as TradeDate,\n",
    "#2022-01-03#  as SettlementDate,\n",
    "100 as TradePrice,\n",
    "\"trd_002\" as TxnId, \n",
    "\"StockIn\" as Type,\n",
    "10 as Units;\n",
    "\n",
    "-- Write the transaction table to LUSID Drive\n",
    "-- You can pass in the @@file_name and @@folder_name as variables.\n",
    "-- You can also pass the file_name as folder name as strings\n",
    "\n",
    "@save_to_drive = use Drive.SaveAs with @transactions, @@file_name, @@folder_name\n",
    "--path=/{@@folder_name}\n",
    "--fileNames={@@file_name}\n",
    "enduse;\n",
    "\n",
    "select * from @save_to_drive;\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "mds15",
   "metadata": {},
   "source": [
    "#### Unformatted string to table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ccs15",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%luminesce\n",
    "\n",
    "-- Description:\n",
    "-- In this example we load raw data from a \n",
    "-- txt file into a Luminesce table. The \n",
    "-- data is in a comma separated format\n",
    "\n",
    "/*\n",
    "====================================================\n",
    "    1. Load comma separated string from txt file\n",
    "====================================================\n",
    "*/\n",
    "\n",
    "@@txtDataOriginal = use Drive.RawText\n",
    "--file=/luminesce-examples/prices-in-txt-file.txt\n",
    "enduse;\n",
    "\n",
    "@@txtData_RemoveRedundantTxt = select regexp_replace(@@txtDataOriginal, '\"|ADD| ', '');\n",
    "\n",
    "@@txtData_CommaSeparatedString = select regexp_match(@@txtData_RemoveRedundantTxt, '(?<=ENDRECORD)[.|\\n|\\W|\\w]*');\n",
    "\n",
    "/*\n",
    "======================================\n",
    "    2. Parse out the columns names \n",
    "======================================\n",
    "*/\n",
    "\n",
    "@@columnsWithNoSepCommas = select regexp_match(@@txtDataOriginal , '(?<=RECORD)([.|\\n|\\W|\\w]*)(?=ENDRECORD)');\n",
    "\n",
    "@@columnsWithTrailingComma = select regexp_replace(@@columnsWithNoSepCommas, '\\r\\n', ',');\n",
    "\n",
    "@columnsWithSepCommas = select substr(@@columnsWithTrailingComma, 2, length(@@columnsWithTrailingComma) -2) as ColumnNames;\n",
    "\n",
    "/*\n",
    "==========================================\n",
    "    3. Split string into separate rows\n",
    "==========================================\n",
    "*/\n",
    "\n",
    "-- Note: This is how you split by endlines in Luminesce\n",
    "\n",
    "@txtSplitByRow = select Value from Tools.Split where DelimiterString = '\n",
    "'\n",
    "and Original = @@txtData_CommaSeparatedString;\n",
    "\n",
    "@tableRowsAsOneCol = select distinct Value as OriginalColumnValue from @txtSplitByRow\n",
    "where Value != '';\n",
    "\n",
    "/*\n",
    "====================================================================    \n",
    "    4. Use the Tools.Split provider to split the data into cells    \n",
    "====================================================================\n",
    "*/\n",
    "\n",
    "-- Split the table body first\n",
    "\n",
    "@tableRowsAsOneColWithIndex = select r.OriginalColumnValue, t.*\n",
    "from @tableRowsAsOneCol r\n",
    "inner join Tools.Split t \n",
    "on t.Original = r.OriginalColumnValue;\n",
    "\n",
    "-- Then split column names\n",
    "\n",
    "@columnsNamesToTable = select r.ColumnNames, t.*\n",
    "from @columnsWithSepCommas r\n",
    "inner join Tools.Split t \n",
    "on t.Original = r.ColumnNames;\n",
    "\n",
    "-- Join the table body and column names together\n",
    "\n",
    "@formattedTable = select \n",
    "tr.Value as [RowValue], tr.[OriginalIndex], ct.Value as [ColumnName]\n",
    "from @columnsNamesToTable ct\n",
    "join @tableRowsAsOneColWithIndex tr on (ct.[Index] = tr.[Index])\n",
    ";\n",
    "\n",
    "/*\n",
    "=======================================\n",
    "    5. Pivot and format the results\n",
    "=======================================\n",
    "*/\n",
    "\n",
    "@pivoted = \n",
    "use Tools.Pivot with @formattedTable\n",
    "--key=ColumnName\n",
    "--aggregateColumns=RowValue\n",
    "enduse;\n",
    "\n",
    "select\n",
    "*\n",
    "from @pivoted;\n",
    "\n"
   ]
  }
 ],
 "metadata": {},
 "nbformat": 4,
 "nbformat_minor": 5
}