You can request LocalFs.*
providers from FINBOURNE and host them locally to interact with files on a network file share (via a NAS or similar device).
Providing you have a suitable license and access control permissions, you can:
- Read from CSV, Excel, SQLite, XML, Parquet and plain text files stored locally, perhaps in preparation for loading investment data into LUSID.
- Write to files of the same types, perhaps having retrieved investment data from LUSID.
- Search a folder or folders for files.
Hosting the LocalFs.* providers
- Make sure your computer has .NET 6+ installed.
- Contact us to request an archive and client certificate for each LUSID domain you wish to enable.
- Upon receipt, unzip the archive to a suitable folder. Note it might be preferable to locate each domain's archive on a separate computer.
- Copy the client certificate files (
client_cert.pem
andclient_key.pem
) over the default ones in the directory containing the DLLs, for example<unpacked-archive>\.dotnet\tools\.store\finbourne.luminesce.localfs\<version>\finbourne.luminesce.localfs\<version>\tools\net6.0\any\
. - Open the
<unpacked-archive>\.dotnet\tools\.store\finbourne.luminesce.localfs\<version>\finbourne.luminesce.localfs\<version>\tools\net6.0\any\host-settings.json
file and search for the following line:"ClientDomain": "fbn-ci,*",
Add a^
(caret) character in front offbn-ci
, for example:"ClientDomain": "^fbn-ci,*",
- Navigate to the
<unpacked-archive>\.dotnet\tools
folder and run the following command in a terminal window to start the service:.\luminesce-local-file-access.exe
- When prompted in a browser window, authenticate to LUSID as a user with administrative privileges.
- Open your LUSID web app, navigate to the Data Virtualisation > Query Editor window, and click the Refresh button (highlighted in red). You should be able to search for
LocalFs
providers:
To stop the service, enter Ctrl-C
in the terminal window. The providers should disappear from your web app catalog.
Renaming the LocalFs.* providers
You can optionally rename or remove the Mine
suffix for all providers (you cannot change the LocalFs.*
prefix). To do this, open the <unpacked-archive>\.dotnet\tools\.store\finbourne.luminesce.localfs\<version>\finbourne.luminesce.localfs\<version>\tools\net6.0\any\api-settings.json
file and search for the following line:
"ProviderNameFormat": "{0}.Mine",
Change the text after {0}
to something more intuitive or memorable, or remove it altogether, for example:
"ProviderNameFormat": "{0}",
The new names are reflected in the Luminesce catalog the next time you sign in. Note you must restart the service if you change this file while it is running.
Understanding access control permissions for the LocalFs.* providers
You, and any other LUSID user wishing to run queries against a LocalFs.*
provider, must have suitable access control permissions. The general principle of creating policies for Luminesce providers, assigning them to roles and roles to users, is explained in this article. Note you should automatically have sufficient privileges if you are the LUSID domain owner with the built-in lusid-administrator
role.
Getting help for the LocalFs.* providers
You can access the latest online help for LocalFs.*
providers by running a query like this:
@x = use LocalFs.Csv.Mine
--help
enduse;
Using the LocalFs.* providers
The LocalFs.*
providers are very similar to the Drive.*
providers for interacting with files in Drive, except you must provide either an absolute location, or a location relative to that of the luminesce-local-file-access.exe
file.
For reading data using the LocalFs.Csv.Mine
, LocalFs.Excel.Mine
, LocalFs.Sqlite.Mine
, LocalFs.Xml.Mine
, LocalFs.RawText.Mine
and LocalFs.Parquet.Mine
providers, specify a full path using the --file
option, for example:
@x = use LocalFs.Csv.Mine --file=C:\Users\JohnDoe\instruments.csv # Either an absolute file path... --file=..\..\instruments.csv # ...or one relative to luminesce-local-file-access.exe enduse; select * from @x
For writing data using the LocalFs.SaveAs.Mine
provider, specify a folder path using the --path
option and the names of one file per input table using the --fileNames
option, for example:
@data1 = select distinct TableName from Sys.Field order by 1; @data2 = select distinct TableName, FieldName from Sys.Field order by 1,2; @x = use LocalFs.SaveAs.Mine with @data1, @data2 --path=C:\Users\JohnDoe --fileNames file1 file2 enduse; select * from @x
For finding files using the LocalFs.File.Mine
provider, specify the RootPath
parameter to nominate a base folder in the where
clause of your query, for example:
select * from LocalFs.File.Mine where RootPath = 'C:\Users\JohnDoe' and Name like '%.csv' and IncludeSubdirectories = True
For examples of other operations, see the documentation for the similar Drive.*
providers below:
To interact with... | Read from using the ... provider | Write to using the ... provider (links are to docs for similar Drive.* providers) |
CSV and similarly-structured files | LocalFs.Csv.Mine | LocalFs.SaveAs.Mine |
Excel files | LocalFs.Excel.Mine | |
SQlite files | LocalFs.Sqlite.Mine | |
XML files | LocalFs.Xml.Mine | |
Text files | LocalFs.RawText.Mine | |
Apache Parquet files | LocalFs.Parquet.Mine | |
Find files | LocalFs.File.Mine | N/A |