To create a reference table, you first need to generate an authentication token by following the the steps in Obtaining a bearer token from your Observe instance - local auth. If you have integrated Observe with a supported SSO provider, you can generate an authentication token by following the steps in Obtaining a bearer token from your Observe instance - SSO

Create a reference table using the API

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>'

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

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

lookup example

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

join example

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

set_link example

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

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

curl https://{customerid}.observeinc.com/v1/referencetables?label=<name> -H 'Authorization: Bearer <customer id> <token>'

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](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](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](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."}