Reference Tables (Public Preview)¶

Warning

Reference Tables are in Public Preview. Use the in-product Contact Support button to contact Observe for more information. On the left side navigation menu, click Docs & Support, Contact Support, and Send Us a Message to contact an Observe Data Engineer.

Reference tables offer an easy way to integrate long-lived business context with machine data. This section includes instructions for creating and managing reference tables from CSV files via Observe API.

Note

The input CSV file must be well-formed which means:

Every row must have the same number of columns
The type of each row must be consistent with the schema, if specified
Header is optional
The column names in the header must be a valid dataset column name
File size can be up to 10 MiB.

Create an auth token¶

To create a reference table, you first need to generate an authentication token using these steps. If you have integrated Observe with a supported SSO provider, you can generate an authentication token by following these steps.

Create a reference table¶

Once you have the authentication token, you can use the following API to create a reference table.

# Create a reference table with name, primary key, and its schema 

curl <host>/v1/meta/reftable \
 -X POST \
 -H 'Authorization: Bearer <customer id> <token>' \
 -F 'input={"workspaceId": "<workspace id>", "name": "<name>", "primaryKey": ["col1", "col2", "col3"], "labelField": "col2"}' \
 -F 'upload=@<path to csv>' \
 -F 'schema=@<path to schema json>'

name: Optional. Name of the reference table. If not provided, the name is the same as the file name.
primarykey: Optional. One or more columns of the CSV to use as primary key of the managed dataset. No primary key will be set if this argument is not passed in.
labelField: Optional. One column of the CSV to be the label field of the reference table.
schema: Optional. Path to a JSON file specifying the schema of the managed dataset. The following types are supported: array, bool, duration, float64, int64, numeric, object, string, variant. The default is a stringly-typed schema (i.e., every column is of type string), with column names extracted from the header of the CSV file. If the CSV file has no header, then the column names are generated in the form col0, col1, …, coln-1.

Here is an example JSON file specifying a schema.

[
    {"name": "id", "type": "string"},
    {"name": "name", "type": "string"},
    {"name": "description", "type": "string"},
    {"name": "picture", "type": "string"},
    {"name": "categories", "type":"array"},
    {"name": "priceUsd_currencyCode", "type": "string"},
    {"name": "priceUsd", "type": "float64"}
]

The following example creates a reference table called Products with a primary key, id and a schema in JSON representing the schema of the csv file. Here are a sample CSV file and its schema in JSON converted from the Astronomy Shop’s product catalog.

curl 'https://123456789.observeinc.com/v1/meta/reftable' \
-X POST \
-H 'Authorization: Bearer 123456789 sB_pLWTM0ED_redacted' \
-F 'input={"workspaceId": "41139097", "name": "Products", "primaryKey": ["id"]}' \
-F '[email protected]' \
-F 'schema=@products_schema.json'

When creating a reference table is successful, you will see a response similar to the following one.

{"Id":"41676213","Name":"Products","Description":"","IconUrl":"","WorkspaceId":"41139097","ManagedById":null,"CustomerId":"123456789","FolderId":"41143965","CreatedBy":"2896","CreatedDate":"2024-07-29T20:55:55.678497479Z","UpdatedBy":"2896","UpdatedDate":"2024-07-29T20:55:55.678497479Z","datasetID":"41676214"}%

Go to the dataset page and search for Products or your reference table name. You will find the reference table under the Reference Tables/ namespace.

Use a reference table for `lookup`, `join`, or `set_link`¶

Create a worksheet from a dataset (e.g. Container Logs dataset).
Switch to the OPAL editor.
Use Manage inputs to add a reference table (e.g. Products dataset)
Use a lookup, join, or set_link based on the following examples.

lookup on (productId = @"Reference Tables/Products".id), productName:@"Reference Tables/Products".name, price:@"Reference Tables/Products".priceUsd

join on (productId = @"Reference Tables/Products".id), productName:@"Reference Tables/Products".name, price:@"Reference Tables/Products".priceUsd

set_link "productName", productId:@"Reference Tables/Products with labels".id

Search for a reference table¶

You can search for reference tables by id or by substring match on the name.

# Search for a reference table by id
curl <host>/v1/meta/reftable/<id> -H 'Authorization: Bearer <customer id> <token>'

# Search for a reference table by name
curl <host>/v1/meta/reftable?name=<name> -H 'Authorization: Bearer <customer id> <token>'

Here is an example to search for a reference table by name.

curl 'https://123456789.observe-eng.com/v1/meta/reftable?name=Products' \
  -H 'Authorization: Bearer 123456789 sB_pLWTM0ED_redacted'

When searching for a reference table is successful, you will see a response similar to the following one.

{"Results":[{"Id":"41676213","Name":"Products","Description":"","IconUrl":"","WorkspaceId":"41139097","ManagedById":null,"CustomerId":"123456789","FolderId":"41143965","CreatedBy":"2896","CreatedDate":"2024-07-29T20:55:55.678497Z","UpdatedBy":"2896","UpdatedDate":"2024-07-29T20:55:55.678497Z","datasetID":"41676214"},{"Id":"41678579","Name":"Products_02","Description":"","IconUrl":"","WorkspaceId":"41139097","ManagedById":null,"CustomerId":"123456789","FolderId":"41143965","CreatedBy":"2896","CreatedDate":"2024-07-30T15:45:59.337678Z","UpdatedBy":"2896","UpdatedDate":"2024-07-30T15:45:59.337678Z","datasetID":"41678580"}]}%

Update a reference table¶

You can update a reference table by id. We only support updating the name of the reference table, replacing its data contents and set or unset primary keys. Use Search for a reference table to find the id of the reference table you want to update.

# Update both the name and data of the reference table
curl <host>/v1/meta/reftable/<id> \
  -X PUT \
  -H 'Authorization: Bearer <customer id> <token>' \
  -F 'input={"name": "<new name>"}' \
  -F 'upload=@<path to csv>'

# Update the primary key to [a, b]
curl <host>/v1/meta/reftable/<id> \
  -X PUT \
  -H 'Authorization: Bearer <customer id> <token>' \
  -F 'input={"primaryKey": ["a", "b"]}'
  
# Unset primary key
curl <host>/v1/meta/reftable/<id> \
  -X PUT \
  -H 'Authorization: Bearer <customer id> <token>' \
  -F 'input={"primaryKey": []}'

The following example updates the existing Products reference table with the updated product catalog, reducing price for OLJCESPC7Z and adding a new product, Solar Filter v2.

curl 'https://123456789.observe-eng.com/v1/meta/reftable/41676213' \
  -X PUT \
  -H 'Authorization: Bearer 123456789 sB_pLWTM0ED_redacted' \
  -F 'input={"name": "Product v2"}' \
  -F 'upload=@products_v2.csv'

When updating a reference table is successful, you will see a response similar to the following one.

{"Id":"41676213","Name":"Product v2","Description":"","IconUrl":"","WorkspaceId":"41139097","ManagedById":null,"CustomerId":"123456789","FolderId":"41143965","CreatedBy":"2896","CreatedDate":"2024-07-29T20:55:55.678497Z","UpdatedBy":"2896","UpdatedDate":"2024-07-30T23:11:26.26901969Z","datasetID":"41676214"}%

Delete a reference table¶

You can delete a reference table by id. Use Search for a reference table to find the id of the reference table you want to delete.

# Delete a reference table by id
curl <host>/v1/meta/reftable/<id> \
  -X DELETE \
  -H 'Authorization: Bearer <customer id> <token>'

The following example deletes the existing reference table by its id, 41676213.

curl 'https://123456789.observe-eng.com/v1/meta/reftable/41676213' \
  -X DELETE \
  -H 'Authorization: Bearer 123456789 sB_pLWTM0ED_redacted' \

When deleting a reference table is successful, you will see a response similar to the following one.

{"success":true,"errorMessage":"Deleted ReferenceTable","detailedInfo":{"id":"41676213"}}%