Lookup Table API 2

CSV file requirements

The CSV file must follow these requirements:

Maximum file size is 100 MB. The file can't have more than 1,000,000 rows.
The first row must contain column names or headers.
The first column must correspond to the mapping property value and must contain unique values. Lookup tables search for exact matches and are case-sensitive.
Separate columns with commas.
Separate rows with line breaks.
If a field value contains commas or quotes, wrap the value in double quotation marks. The first double quote signifies the beginning of the column data, and the last double quote marks the end. If the value contains a string with double quotes, Amplitude replaces those quotes with two double quotes "".

Create a Lookup Table

Create a lookup table object by uploading a CSV that maps an existing property to the new properties to create. Send the request with the multipart/form-data content type.

Parameters

Name	Description
`name`	Required. Type: `string`. Name of the table.
`file`	Required. Type: `file`. A CSV representation of the mappings.
`key`	Required. Type: `string`. Column in CSV to use as key of lookup table.
`property`	Required. Type: `JSON`. Property in Amplitude to map to the key column in CSV.
`property.value`	Required. Type: `string`. Name of property in Amplitude.
`property.type`	Required. Type: `string`. Type of property in Amplitude.
`property.groupType`	Optional. Type: `string`. Required only if property is a group property.

Example request

curl

curl -L -X POST 'https://data-api.amplitude.com/api/3/lookup_table' \
        -u API_KEY:SECRET_KEY \
        -F 'file=@"/path/to/file.csv";type=text/csv' \
        -F 'name=":name"' \
        -F 'key=":key"' \
        -F 'property="{\"value\": \":propertyName\", \"type\": \":propertyType\", \"groupType\":  \":propertyGroupType\"}";type=application/json'

Response

json

{
"appId": "<projectId>",
"name": "example-lookup",
"columnHeaders": [
    "Language"
],
"createdAt": 1715912516,
"createdBy": "api",
"lastModifiedAt": 1715912516,
"lastModifiedBy": "api",
"isDeleted": false,
"isConfigured": true,
"keyColumnHeader": "SKU",
"keyProperty": {
    "type": "event",
    "value": "example",
    "groupType": "User"
},
"fileName": "lookup-table-example.csv",
"rowCount": 3,
"sizeBytes": 0,
}

Retrieve a Lookup Table

Retrieve a lookup table by its name.

Parameters

Name	Description
`name`	Required. Type: `string`. Name of the table.

Example request

curl

curl -L -X GET 'https://data-api.amplitude.com/api/3/lookup_table/:name' \
        -u API_KEY:SECRET_KEY

Response

json

{
    "appId": "<projectId>",
    "name": "example-lookup",
    "columnHeaders": [
        "Language"
    ],
    "createdAt": 1715912516,
    "createdBy": "api",
    "lastModifiedAt": 1715912516,
    "lastModifiedBy": "api",
    "isDeleted": false,
    "isConfigured": true,
    "keyColumnHeader": "SKU",
    "keyProperty": {
        "type": "event",
        "value": "example",
        "groupType": "User"
    },
    "fileName": "lookup-table-example.csv",
    "rowCount": 3,
    "sizeBytes": 5,
}

Download a CSV

Download the lookup table object as a CSV. Any incremental changes are applied in the downloaded file.

Parameters

Name	Description
`name`	Required. Type: `string`. Name of the table.

Example request

curl

curl -L -X GET 'https://data-api.amplitude.com/api/3/lookup_table/:name/csv' \
        -u API_KEY:SECRET_KEY

Override a Lookup Table

Override a lookup table object by uploading a CSV that replaces the CSV already uploaded to Amplitude. Send the request with the multipart/form-data content type.

Parameters

Name	Description
`name`	Required. Type: `string`. Name of the table.
`file`	Optional. Type: `file`. A CSV representation of the mappings.
`property`	Optional. Type: `JSON`. Property in Amplitude to map to the key column in CSV.
`property.value`	Optional. Type: `string`. Name of property in Amplitude.
`property.type`	Optional. Type: `string`. Type of property in Amplitude.
`property.groupType`	Optional. Type: `string`. Required only if property is a group property.

Example request

curl

curl -L -X PUT 'https://data-api.amplitude.com/api/3/lookup_table/:name' \
        -u API_KEY:SECRET_KEY \
        -F 'file=@"/path/to/file.csv";type=text/csv' \
        -F 'property="{\"value\": \":propertyName\", \"type\": \":propertyType\", \"groupType\":  \":propertyGroupType\"}";type=application/json'

Response

json

{
    "appId": "<projectId>",
    "name": "example-lookup",
    "columnHeaders": [
        "Language"
    ],
    "createdAt": 1715912516,
    "createdBy": "api",
    "lastModifiedAt": 1715912516,
    "lastModifiedBy": "api",
    "isDeleted": false,
    "isConfigured": true,
    "keyColumnHeader": "SKU",
    "keyProperty": {
        "type": "event",
        "value": "example",
        "groupType": "User"
    },
    "fileName": "lookup-table-example.csv",
    "rowCount": 3,
    "sizeBytes": 0,
}

Update a Lookup Table

Update a lookup table's columns and data. If you provide a CSV file, Amplitude merges the file with the existing CSV. This supports incremental updates of the CSV instead of a complete replacement.

Parameters

Name	Description
`name`	Type: `string`. Name of the table.
`file`	Type: `file`. A CSV representation of the mappings.
`property`	Type: `JSON`. Property in Amplitude to map to the key column in CSV.
`property.value`	Type: `string`. Name of property in Amplitude.
`property.type`	Type: `string`. Type of property in Amplitude.
`property.groupType`	Type: `string`. Required only if property is a group property.

Example request

curl

curl -L -X PATCH 'https://data-api.amplitude.com/api/3/lookup_table/:name' \
        -u API_KEY:SECRET_KEY
        -F 'file=@"/path/to/file.csv";type=text/csv' \
        -F 'property="{\"value\": \":propertyName\", \"type\": \":propertyType\", \"groupType\":  \":propertyGroupType\"}";type=application/json'

Response

json

{
    "appId": "<projectId>",
    "name": "example-lookup",
    "columnHeaders": [
        "Language"
    ],
    "createdAt": 1715912516,
    "createdBy": "api",
    "lastModifiedAt": 1715912516,
    "lastModifiedBy": "api",
    "isDeleted": false,
    "isConfigured": true,
    "keyColumnHeader": "SKU",
    "keyProperty": {
        "type": "event",
        "value": "example",
        "groupType": "User"
    },
    "fileName": "lookup-table-example.csv",
    "rowCount": 3,
    "sizeBytes": 0,
}

Delete a Lookup Table

Delete a Lookup Table.

Parameters

Name	Description
`name`	Required. Type: `string`. Name of the table.

Example request

curl

curl -L -X DELETE 'https://data-api.amplitude.com/api/3/lookup_table/:name' \
        -u API_KEY:SECRET_KEY

Response

json

{
    "message": "Lookup table <:name> deleted successfully",
    "success": true
}

List all Lookup Tables

List all the Lookup Tables for the project.

Example request

curl

curl -L -X GET 'https://data-api.amplitude.com/api/3/lookup_table' \
        -u API_KEY:SECRET_KEY

Response

json

[
    {
        "appId": "<projectId>",
        "name": "example-lookup",
        "columnHeaders": [
            "Language"
        ],
        "createdAt": 1715912516,
        "createdBy": "api",
        "lastModifiedAt": 1715912516,
        "lastModifiedBy": "api",
        "isDeleted": false,
        "isConfigured": true,
        "keyColumnHeader": "SKU",
        "keyProperty": {
            "type": "event",
            "value": "example",
            "groupType": "User"
        },
        "fileName": "lookup-table-example.csv",
        "rowCount": 3,
        "sizeBytes": 5,
    },
    {
        "appId": "<projectId>",
        "name": "example-lookup-2",
        "columnHeaders": [
            "Language"
        ],
        "createdAt": 1715912516,
        "createdBy": "api",
        "lastModifiedAt": 1715912516,
        "lastModifiedBy": "api",
        "isDeleted": false,
        "isConfigured": true,
        "keyColumnHeader": "SKU",
        "keyProperty": {
            "type": "event",
            "value": "example",
            "groupType": "User"
        },
        "fileName": "lookup-table-example.csv",
        "rowCount": 50,
        "sizeBytes": 10,
    }
]

Error codes

All lookup table APIs share these common error codes.

Structure

Name	Description
`statusCode`	Http status code of error. 400, 409, 413
`message`	Human readable message describing the error
`errorCode`	Static error code string
`extraParams`	Each error might have extra parameters in the response to help better point to the exact reason for the error

Types

Code	Description
`LOOKUP_TABLE_INVALID_FILE_COUNT`	Attempted to upload more than 1 file for a single lookup table.
`LOOKUP_TABLE_INVALID_FILE_SIZE`	Created/Edited a lookup table using a file bigger than 100mb.
`LOOKUP_TABLE_INVALID_FILE_TYPE`	Created/Edited a lookup table using a file that wasn't a CSV.
`LOOKUP_TABLE_INVALID_KEY_COLUMN`	Created a lookup table with a "key" input not present in the headers of the table.
`LOOKUP_TABLE_INVALID_VALUE_COLUMN`	Cell in uploaded file exceeds 1,024 characters.
`LOOKUP_TABLE_INVALID_KEY_PROPERTY`	Provided key property doesn't exist in Amplitude.
`LOOKUP_TABLE_INVALID_NUMBER_OF_ROWS`	Created/Edited a lookup table using a file with more than 1mil rows.
`LOOKUP_TABLE_KEY_COLUMN_DUPLICATE_VALUES`	Specified key column has duplicate values.
`LOOKUP_TABLE_INVALID_TABLE_NAME`	Provided name is invalid.
`LOOKUP_TABLE_MALFORMED_CSV`	Provided CSV not processed correctly. See error message for more details.
`LOOKUP_TABLE_INVALID_INPUT`	Input for field doesn't match expectation. See error message for more details.
`LOOKUP_TABLE_ALREADY_EXISTS`	Created a table that already exists in the provided project.
`LOOKUP_TABLE_DOES_NOT_EXIST`	Attempted to load or edit table that doesn't exist.
`LOOKUP_TABLE_INVALID_COLUMN_HEADERS`	Column headers in file not processed correctly.

Was this helpful?