Using Luminesce to read/write SFTP server files

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:

1. Sign in to the LUSID web app as a user with administrative privileges.
2. Navigate to System Settings > Configuration Store, select the Shared tab, and click the Add configuration set button (top right)
3. In the New set dialog, specify a Scope of Luminesce-Provider and a Code of the form Sftp-<your-memorable-string>
4. 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 lusid-administrator role.

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

The 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.

For the Sftp.File and 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';

and:

@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.Csv, Sftp.Excel, Sftp.Sqlite, Sftp.Xml, Sftp.RawText and 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: