Export raw data from a Pigment Block

  • 15 January 2024
  • 0 replies
  • 158 views

Userlevel 3
Badge +2

In addition to exporting View data from Pigment, you can also automatically export raw data from your Lists, Metrics, and Tables blocks with our REST API.

This REST API has many benefits. You can:

  • Export Block data, but without the custom operations and calculations that were applied to it in Pigment. 
  • Define a list of Scenarios and filters, giving you the flexibility to specify which data you want to export. 
  • Export more data than exporting with only a View ID, making it a more scalable and more efficient process. 

Before you begin

 

Prerequisite information

  • Access Rights. Only Pigment members with the account type Workspace Admin or Security Admin can access the API management page. Export API keys have no dedicated access rights associated with them. Instead, export API keys impersonate the access rights of their owner. For example, if you export data from Pigment by calling the export API, and you use a key that John created, then the API returns the data as John sees it. For more information on API keys in Pigment, see Manage API keys
  • HTTP/2. We recommend that you use HTTP/2 so that you’re notified in the event of a streaming error. HTTP/1.0 and HTTP/1.1 don’t have the mechanism to report streaming errors, so if an error occurs during your API call, your response is truncated without error notification. 
  • API Requests. The Pigment APIs described here use the POST method, and their request options are all optional. More information is provided below. 
  • Bearer header. You must include your export API key in the bearer header of your API request. This assures secure authentication and authorization during the export process.

Prerequisite tasks

We recommend that you complete the following tasks in Pigment before you start your export: 

  • Obtain your export API key.
    For more information, see Manage API keys
  • Obtain the ID for the List, Metric, or Table that you want to export.
    To do this, you use the payload builder in Pigment. We explain how to generate a payload below. 

Generate a Payload to obtain Pigment Block IDs

 When you create your API request, your API endpoint needs the ID for the List, Metric, or Table that you want to export. To obtain this information, and other field information required for filtering and so on, you need to use the JSON payload builder in Pigment. This captures the precise ID data for your required Block, which you can then paste into your API request.  

When you have successfully generated your API payload, we recommend that you use an external system to validate its generated data. 

  1. In Workspace Settings, open API keys
  2. Click Generate API Payload
  3. In Data Source, select your required Application and Block. 
  4. Depending on the Block you select, select the specific data you want available in your payload. 
    - List: Select the required properties. 
    - Metric: Select the required Scenarios. 
    - Table: Select the required Metrics and Scenarios.
  5. In Pages, select the Pigment Dimension filters that you want to export. 
  6. Use the Header Name toggle to display technical or friendly names in your CSV file headers.
  7. Click Copy payload to clipboard
    The example below shows generating a sample payload for a Pigment Metric.  

Exporting Block data

List, Metric, and Table blocks each require a different API request to export their raw data from Pigment. 

Depending on the information you want to export, you define specific request options in your API request to: 

  • Configure the CSV output
    For example, if you want the CSV file headers to display technical or friendly names.  
  • Filter the data in the CSV output
    For example, if you want only specific data included in the CSV file. 

If you don’t define specific request options in the API request, then all request options retain their default behavior. Information on the List, Metric, and Table API requests, and their respective request options, is provided in detail below. 

The cURL samples at the end of this topic indicate where the List, Metric, and Table IDs, and the export API keys, are used in each API request. 

Export List data

To perform an export of raw List data in Pigment, you need the following information: 

Field  Description
PropertyTechnicalNames

This is the list containing the technical names of the Pigment List properties that you want to export. These must be provided in a JSON string array, and listed in the order in which you want the names exported.

If you don’t specify any List property, then all Pigment List properties are exported. 

ListFilter

This is the list containing the Pigment List items that you want to export. These must be provided in a JSON string array, and each string needs to be the GUID of the List item in the export.

If you don’t specify any List items for export, then all Pigment List items are exported. 

PropertyFilters

This is the list containing the Pigment List property filters that you want to export. Each property filter acts as a Page Selector on a property. Only list items with matching property filters are exported. 

This list must be provided in a JSON array of JSON objects. Each property filter is a JSON object with the following information: 

  • PropertyTechnicalName. This field is the technical name of the property in the form of a JSON string. The filter is applied to this field. 
  •  ModalityIds. This is the list of property items in the form of a JSON string array. Each string is the GUID of the property item. 

If you don’t define the Pigment List property filters that you want to export, then all filters are exported. 

FriendlyHeaders This is a JSON boolean value to specify if the exported CSV displays technical or friendly names in headers. By default, technical names are displayed in the export.

 

Export Metric data

To perform an export of raw Metric data in Pigment, you need the following information: 

Field  Description
scenarios

This is the list of Scenario IDs, which contain the Pigment Metric data that you want to export.

If you don’t define a Scenario filter, then the Metric’s default Scenario value is exported. Your export CSV file requires at least one Scenario in order to have a Scenario column. 

filters

This is the list containing the Pigment Dimension filters that you want to export. Only Metric items with matching Dimension filters are exported. 

This list must be provided in a JSON array of JSON objects. Each Dimension filter is a JSON object with the following information: 

  • dimensionId. This field is the Dimension ID in the form of a JSON string. The filter is applied to this field. 
  • modalityIds. This is the list of Dimension items in the form of a JSON string array. Each string is the GUID of the Dimension item. 

If you don’t define the Pigment Dimension filters that you want to export, then all filters are exported. 

FriendlyHeaders This is a JSON boolean value to specify if the exported CSV displays technical or friendly names in headers. By default, technical names are displayed in the export.

 

Export Table data

To perform an export of raw Table data in Pigment, you need the following information: 

  • Endpoint: https://pigment.app/api/export/table/{tableId}
  • Request options: The request options for exporting Table data are identical to the options needed for exporting Metric data. The additional parameters and filters used to export your Table data are described in detail below. 
Field  Description
Metrics

This is the list of Metric IDs that belong to the Pigment Table data that you want to export. These must be provided in a JSON string array, and each string needs to be the GUID of the Metric in the export. These must be listed in the order in which you want the names exported.

If you don’t specify any Metric IDs, then all the Metric IDs in the Table are exported. 

 

Export CSV Output

When you export your Pigment data using these API calls, your data is streamed to a CSV file. The data in the CSV is separated by a semicolon delimiter, and is in a flat structure, not formatted.

Some points to consider when reading your CSV file output: 

  • For a List export, the first column in the CSV contains the GUID of the List items, followed by a column for each exported List property.
  • For Metric and Table exports, the first columns in the CSV contain Dimensions, followed by a column for each Metric.
    For example, the CSV output for a Table export is provided below. This Table is composed of two Metrics: Unit Price and Sales quantity. These Metrics in this Table are based on the Month and Product Dimensions, which are the first columns in the output.  

    Month;Product;Unit Price;Sales quantity Nov 2023;Shoe;100;4 Nov 2023;Socks;10;40 Dec 2023;Shoe;100;12 Dec 2023;Socks;10;120

REST Samples 

The following are cURL samples for exporting raw data from your Lists, Metrics, and Tables blocks. 

cURL sample for List export 

# Template variables
PIGMENT_API_KEY="CHANGE ME"
LIST_ID="CHANGE ME"
# Get the CSV
curl -X POST "https://pigment.app/api/export/list/${LIST_ID}" \
-H "Authorization: Bearer ${PIGMENT_API_KEY}"
-d '{
"propertyTechnicalNames": [
"string"
],
"listFilter": [
"3fa85f64-5717-4562-b3fc-2c963f66afa6"
],
"propertyFilters": [
{
"propertyTechnicalName": "string",
"modalityIds": [
"3fa85f64-5717-4562-b3fc-2c963f66afa6"
]
}
],
"friendlyHeaders": false
}'

cURL sample for Metric export

# Template variables
PIGMENT_API_KEY="CHANGE ME"
METRIC_ID="CHANGE ME"
# Get the CSV
curl -X POST "https://pigment.app/api/export/metric/${METRIC_ID}" \
-H "Authorization: Bearer ${PIGMENT_API_KEY}"
-d '{
"scenarios": [
"3fa85f64-5717-4562-b3fc-2c963f66afa6"
],
"filters": [
{
"dimensionId": "3fa85f64-5717-4562-b3fc-2c963f66afa6",
"modalityIds": [
"3fa85f64-5717-4562-b3fc-2c963f66afa6"
]
}
],
"friendlyHeaders": false
}'

cURL sample for Table export

# Template variables
PIGMENT_API_KEY="CHANGE ME"
TABLE_ID="CHANGE ME"
# Get the CSV
curl -X POST "https://pigment.app/api/export/table/${TABLE_ID}" \
-H "Authorization: Bearer ${PIGMENT_API_KEY}"
-d '{
"metrics": [
"3fa85f64-5717-4562-b3fc-2c963f66afa6"
],
"scenarios": [
"3fa85f64-5717-4562-b3fc-2c963f66afa6"
],
"filters": [
{
"dimensionId": "3fa85f64-5717-4562-b3fc-2c963f66afa6",
"modalityIds": [
"3fa85f64-5717-4562-b3fc-2c963f66afa6"
]
}
],
"friendlyHeaders": false
}'

 


0 replies

Be the first to reply!

Reply