Reference Tables¶

Warning

Reference Tables are in GA. We made API changes to streamline your experience. Public Preview APIs will continue to work, but we encourage you to use the new API moving forward.

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 and Terraform.

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 https://{customerid}.observeinc.com/v1/referencetables \
 -X POST \
 -H 'Authorization: Bearer <customer id> <token>' \
 -F 'metadata={"label": "<name>", "description":"description", "primaryKey": ["col1", "col2", "col3"], "labelField": "col2"}' \
 -F 'upload=@<path to csv>' \
 -F 'schema=@<path to schema json>'

label: Required. Name of the reference table.
description: Optional. The description of the reference table.
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/referencetables' \
  -X POST \
  -H 'Authorization: Bearer 123456789 sB_pLWTM0ED_redacted' \
  -F 'metadata={"label": "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":"41941674","customerId":"123456789","workspaceId":"41139097","label":"Products","datasetId":"41941673","folderId":"0","createdBy":{"id":2896},"createdDate":"2025-02-07T19:12:36.157153Z","updatedBy":{"id":2896},"updatedDate":"2025-02-07T19:12:36.157153Z","checksum":"b26f8dc7102f33a1ea9dd81723ee53b7"}%    

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 https://{customerid}.observeinc.com/v1/referencetables/{id} -H 'Authorization: Bearer <customer id> <token>'

# Search for a reference table by name
curl https://{customerid}.observeinc.com/v1/referencetables?label=<name> -H 'Authorization: Bearer <customer id> <token>'

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

curl 'https://123456789.observeinc.com/v1/referencetables?label=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.

{"totalCount":3,"referenceTables":[{"id":"41819922","customerId":"123456789","workspaceId":"41139097","label":"Products without keys","datasetId":"41819923","folderId":"41143965","createdBy":{"id":2896},"createdDate":"2024-11-21T22:46:25.21438Z","updatedBy":{"id":2896},"updatedDate":"2024-11-21T22:46:25.21438Z","checksum":""},{"id":"41791338","customerId":"123456789","workspaceId":"41139097","label":"Products with labels","datasetId":"41791339","folderId":"41143965","createdBy":{"id":2896},"createdDate":"2024-11-05T20:47:59.755023Z","updatedBy":{"id":2896},"updatedDate":"2024-11-05T20:47:59.755023Z","checksum":""},{"id":"41710969","customerId":"123456789","workspaceId":"41139097","label":"Products","datasetId":"41710970","folderId":"41143965","createdBy":{"id":2896},"createdDate":"2024-08-16T16:40:09.694988Z","updatedBy":{"id":2896},"updatedDate":"2024-08-17T01:01:33.090887Z","checksum":""}]}%   

Update metadata of a reference table¶

You can update metadata of a reference table by id. We only support updating the name or the description of the reference table. Use Search for a reference table to find the id of the reference table you want to update.

# Update the name or the description of the reference table
curl https://{customerid}.observeinc.com/v1/referencetables/{id} \
  -X PATCH \
  -H 'Authorization: Bearer <customer id> <token>' \
  -d '{
    "label": "<new name>",
    "description": "<new description>"
  }'

The following example updates the existing Products reference table with a new name, Products v2.

curl 'https://123456789.observeinc.com/v1/referencetables/41942573' \
  -X PATCH \
  -H 'Authorization: Bearer 123456789 sB_pLWTM0ED_redacted' \
  -d '{
        "label": "Products v2"
      }'

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

{"id":"41942573","customerId":"123456789","workspaceId":"41139097","label":"Products v2","datasetId":"41942572","folderId":"0","createdBy":{"id":2896},"createdDate":"2025-02-07T21:18:07.416498Z","updatedBy":{"id":2896},"updatedDate":"2025-02-07T21:18:07.416498Z","checksum":"b26f8dc7102f33a1ea9dd81723ee53b7"}%        

Replace a reference table¶

You can replace a reference table by id. While replacing the reference table, you can also change the name, the description, primaryKey, or labelField. Use Search for a reference table to find the id of the reference table you want to update.

curl https://{customerid}.observeinc.com/v1/referencetables/{id} \
  -X PUT \
  -H 'Authorization: Bearer <customer id> <token>' \
  -F 'metadata={"label": "<new name>", "description":"<new description>", "primaryKey": ["col1", "col2", "col3"], "labelField": "col2"}' \
  -F '[email protected]'

The following example repalce the existing Products reference table by its id, 41819922 and reset primary keys.

curl https://123456789.observeinc.com/v1/referencetables/41819922 \
  -X PUT \
  -H 'Authorization: Bearer 123456789 sB_pLWTM0ED_redacted' \
  -F 'metadata={"label": "Products", "primaryKey": ["id", "name"]}' \
  -F 'upload=@<path to csv>'

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

{"id":"41819922","customerId":"123456789","workspaceId":"41139097","label":"Products","datasetId":"41942572","folderId":"0","createdBy":{"id":2896},"createdDate":"2025-02-07T23:03:29.573924Z","updatedBy":{"id":2896},"updatedDate":"2025-02-07T23:03:29.573924Z","checksum":"b26f8dc7102f33a1ea9dd81723ee53b7"}

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 https://{customerid}.observeinc.com/v1/referencetables/{id} \
  -X DELETE \
  -H 'Authorization: Bearer <customer id> <token>'

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

curl 'https://123456789.observeinc.com/v1/referencetables/41819922' \
  -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.

{"message":"Reference Table 41819922 successfully deleted."}

Reference Tables¶

Create an auth token¶

Create a reference table¶

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

Search for a reference table¶

Update metadata of a reference table¶

Replace a reference table¶

Delete a reference table¶

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