Drive.Sqlite

Type	Read/write	Author	Availability
Direct provider	Read	Finbourne	Provided with LUSID

The Drive.Sqlite provider enables you to write a Luminesce query that extracts data from one or more SQLite files stored in Drive.

The query returns a table of data assembled from the contents of the file or files in the order they are read.

See also: Drive.Excel, Drive.Csv, Drive.Xml, Drive.RawText

Basic usage

@x = use Drive.Sqlite
<options>
enduse;
select * from @x

Options

Drive.Sqlite has options that enable you to filter or refine a query.

Note: The --file option is mandatory.

An option takes the form --<option>=<value>, for example --file=my_file.sqlite. Note no spaces are allowed either side of the = operator. If an option:

Takes a boolean value, then specifying that option (for example --addFileName) sets it to True; omitting the option specifies False.
Takes multiple string values, then specify a comma-separated list, for example --select=My,Column,Names.

Current options at article update time are listed in the table below. For the very latest information, run the following query using a suitable tool and examine the online help:

@x = use Drive.Sqlite
enduse;
select * from @x

Current options	Explanation
`file` (`-f`)	Mandatory. The file to read. Should be a sqlite file. It may also be a folder, in which case --folderFilter is also required to specify which files in the folder to process. [String]
`folderFilter`	Denotes this is searching an entire folder structure and provides a Regular Expression of path/file names within it that should be processed. All matches should be of the same format. [String]
`zipFilter` (`-z`)	Denotes this is a Zip file and provides a Regular Expression of path/file names within it that should be processed. All matches should be of the same format. [String]
`allowMissing`	Should a file/folder simply not exist, don't throw an error but return an empty table with column names and types created as best possible given other options. [Boolean]
`addFileName`	Adds a column (the first column) to the result set which contains the file the row came from. [Boolean]
`table`	Table name to read. If missing then an error will be raised if there is any number of tables other than one. [String]

Examples

In the following examples, the select * from @x syntax at the end prints the table of data assembled by the query.

Note: For more examples, try the Luminesce Github repo.

Example 1: Extract data from a particular SQLite file

@x = use Drive.Sqlite
--file=/Finbourne-Examples/my_file.sqlite
enduse;
select * from @x

Example 2: Extract a specific table from a SQLite file

In this example, just the table instruments1 is extracted from a SQLite file containing more than one table of data.

@x = use Drive.Sqlite
--file=/Finbourne-Examples/combined.sqlite
--table=instruments1
enduse;
select * from @x

Example 3: Extract data from a particular SQLite file stored in a ZIP archive

In this example, my-sqlite-files.zip is stored in the root Drive folder, containing one or more SQLite files. Data is extracted from the archived SQLite file specified by the --zipFilter option.

@x = use Drive.Sqlite
--file=my-sqlite-files.zip
--zipFilter=my_file.sqlite
enduse;
select * from @x