The externaldata operator in APL allows you to retrieve data from external storage sources, such as Azure Blob Storage, AWS S3, or HTTP endpoints, and use it within queries. You can specify the schema of the external data and query it as if it were a native dataset. This operator is useful when you need to analyze data that is stored externally without importing it into Axiom.

The externaldata operator currently supports external data sources with a file size of maximum 5 MB.

The externaldata operator is currently in public preview. For more information, see Features states.

​
For users of other query languages

If you come from other query languages, this section explains how to adjust your existing queries to achieve the same results in APL.

​
Usage

​
Syntax

externaldata (FieldName1:FieldType1, FieldName2:FieldType2, ...) ["URL1", "URL2", ...] [with (format = "FormatType", ignoreFirstRecord=false)]

​
Parameters

ParameterDescription
FieldName1:FieldType1, FieldName2:FieldType2, ...Defines the schema of the external data.
URL1, URL2, ...The external storage URIs where the source data resides.
formatOptional: Specifies the file format. The supported types are csv, scsv, tsv, psv, json, multijson, raw, txt.
ignoreFirstRecordOptional: A Boolean value that specifies whether to ignore the first record in the external data sources. The default is false. Use this property for CSV files with headers.

​
Returns

The operator returns a table with the specified schema, containing data retrieved from the external source.

​
Use case examples

You have an Axiom dataset that contains access logs with a field employeeID. You want to add extra information to your APL query by cross-referencing each employee ID in the Axiom dataset with an employee ID defined in an external lookup table. The lookup table is hosted somewhere else in CSV format.

External lookup table

employeeID, email, name, location
00001, tifa@acme.com, Tifa Lockhart, US
00002, barret@acme.com, Barret Wallace, Europe
00003, cid@acme.com, Cid Highwind, Europe

Query

let employees = externaldata (employeeID: string, email: string, name: string, location: string) ["http://example.com/lookup-table.csv"] with (format="csv", skipFirstRow=true);
accessLogs
| where severity == "high"
| lookup employees on employeeID
| project _time, severity, employeeID, email, name

Output

_timeseverityemployeeIDemailname
Mar 13, 10:08:23high00001tifa@acme.comTifa Lockhart
Mar 13, 10:05:03high00001tifa@acme.comTifa Lockhart
Mar 13, 10:04:51high00003cid@acme.comCid Highwind
Mar 13, 10:02:29high00002barret@acme.comBarret Wallace
Mar 13, 10:01:13high00001tifa@acme.comTifa Lockhart

This example extends the original dataset with the fields email and name. These new fields come from the external lookup table.

  • lookup: Performs joins between a dataset and an external table.
  • union: Merges multiple datasets, including external ones.

Was this page helpful?

Suggest editsRaise issue