You can use the
Sftp.* providers supplied by FINBOURNE to interact with files stored on a SFTP server.
Providing you have a suitable license and access control permissions, you can:
- Read from CSV, Excel, SQLite, XML, Parquet and plain text files stored on the SFTP server, 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.
- Perform operations such as moving, renaming and deleting files.
Obtaining a connection string for your SFTP server
You must store location and credential details for your SFTP server in the LUSID Configuration Store. You then supply this information to a
Sftp.* provider in the form of a connection string.
The following instructions are for the LUSID web app but you can use the Configuration Store REST API if you like:
- Sign in to the LUSID web app as a user with administrative privileges.
- Navigate to System Settings > Configuration Store, select the Shared tab, and click the Add configuration set button (top right)
- In the New set dialog, specify a Scope of
Luminesce-Providerand a Code of the form
- Click the Add item button four times to add keys with the names in the red box below, and supply appropriate values for your SFTP server:
Your connection string is the Code minus the
Sftp- prefix, so in this example
my-secure-sftp-server (underlined in green above).
Understanding access control permissions for the Sftp.* providers
You, and any other LUSID user wishing to run queries against a
Sftp.* 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
Getting help for the Sftp.* providers
You can access the latest online help for
Sftp.* providers by running a query like this:
@x = use Sftp.Csv --help enduse;
Using the Sftp.* providers
Sftp.* providers are very similar to the
Drive.* providers for interacting with files in Drive, except you must provide a connection string encapsulating location and credential details for your SFTP server.
Sftp.File.Operation providers, apply the connection string to the
Connection parameter in the
where clause of your query, for example:
select * from Sftp.File where Name like '%.csv' and RecurseDepth = 6 and Connection = 'my-secure-sftp-server';
@fileOperationsToPerform = select '/redundant.txt' as FullPath, 'Delete' as Operation; select * from Sftp.File.Operation where OperationsToPerform = @fileOperationsToPerform and Connection='my-secure-sftp-server';
For reading data using the
Sftp.Parquet providers, apply the connection string as the first argument to the
--file option, separated from the file path by a colon, for example:
@x = use Sftp.Csv --file=my-secure-sftp-server:/trade-files/equities/end-of-day.csv enduse; select * from @x
For writing data using the
Sftp.SaveAs provider, apply the connection string as the first argument to the
--path option, separated from the folder path by a colon, 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 Sftp.SaveAs with @data1, @data2 --path=my-secure-sftp-server:/trade-files/equities --fileNames start-of-day end-of-day enduse; select * from @x
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
|CSV and similarly-structured files||Sftp.Csv||Sftp.SaveAs|
|Apache Parquet files||Sftp.Parquet|
|Move, rename and delete files||N/A|