IMPORTANT: No additional bug fixes or documentation updates will be released for this version. For the latest information, see the current release documentation.

« API quick reference Transform examples »

› › ›

Tutorial: Transforming the eCommerce sample data

edit

Tutorial: Transforming the eCommerce sample data

edit

This functionality is in beta and is subject to change. The design and code is less mature than official GA features and is being provided as-is with no warranties. Beta features are not subject to the support SLA of official GA features.

Transforms enable you to retrieve information from an Elasticsearch index, transform it, and store it in another index. Let’s use the Kibana sample data to demonstrate how you can pivot and summarize your data with transforms.

If the Elasticsearch security features are enabled, obtain a user ID with sufficient privileges to complete these steps.

You need manage_data_frame_transforms cluster privileges to preview and create transforms. Members of the built-in data_frame_transforms_admin role have these privileges.

You also need read and view_index_metadata index privileges on the source index and read, create_index, and index privileges on the destination index.

For more information, see Security privileges and Built-in roles.
Choose your source index.

In this example, we’ll use the eCommerce orders sample data. If you’re not already familiar with the kibana_sample_data_ecommerce index, use the Revenue dashboard in Kibana to explore the data. Consider what insights you might want to derive from this eCommerce data.
Play with various options for grouping and aggregating the data.

Pivoting your data involves using at least one field to group it and applying at least one aggregation. You can preview what the transformed data will look like, so go ahead and play with it!

For example, you might want to group the data by product ID and calculate the total number of sales for each product and its average price. Alternatively, you might want to look at the behavior of individual customers and calculate how much each customer spent in total and how many different categories of products they purchased. Or you might want to take the currencies or geographies into consideration. What are the most interesting ways you can transform and interpret this data?

Go to Machine Learning > Data Frames in Kibana and use the wizard to create a transform:

In this case, we grouped the data by customer ID and calculated the sum of products each customer purchased.

Let’s add some more aggregations to learn more about our customers' orders. For example, let’s calculate the total sum of their purchases, the maximum number of products that they purchased in a single order, and their total number of orders. We’ll accomplish this by using the sum aggregation on the taxless_total_price field, the max aggregation on the total_quantity field, and the cardinality aggregation on the order_id field:

If you’re interested in a subset of the data, you can optionally include a query element. In this example, we’ve filtered the data so that we’re only looking at orders with a currency of EUR. Alternatively, we could group the data by that field too. If you want to use more complex queries, you can create your data frame from a saved search.

If you prefer, you can use the preview transforms API:
```
POST _data_frame/transforms/_preview
{
  "source": {
    "index": "kibana_sample_data_ecommerce",
    "query": {
      "bool": {
        "filter": {
          "term": {"currency": "EUR"}
        }
      }
    }
  },
  "pivot": {
    "group_by": {
      "customer_id": {
        "terms": {
          "field": "customer_id"
        }
      }
    },
    "aggregations": {
      "total_quantity.sum": {
        "sum": {
          "field": "total_quantity"
        }
      },
      "taxless_total_price.sum": {
        "sum": {
          "field": "taxless_total_price"
        }
      },
      "total_quantity.max": {
        "max": {
          "field": "total_quantity"
        }
      },
      "order_id.cardinality": {
        "cardinality": {
          "field": "order_id"
        }
      }
    }
  }
}
```

When you are satisfied with what you see in the preview, create the transform.

Supply a job ID and the name of the target (or destination) index. If the target index does not exist, it will be created automatically. In Kibana, you can choose to create and start the job, create it, or copy it to the clipboard.

If you prefer, you can use the create transforms API. For example:

PUT _data_frame/transforms/ecommerce-customer-transform
{
  "source": {
    "index": [
      "kibana_sample_data_ecommerce"
    ],
    "query": {
      "bool": {
        "filter": {
          "term": {
            "currency": "EUR"
          }
        }
      }
    }
  },
  "dest": {
    "index": "ecommerce-customers"
  },
  "pivot": {
    "group_by": {
      "customer_id": {
        "terms": {
          "field": "customer_id"
        }
      }
    },
    "aggregations": {
      "total_quantity.sum": {
        "sum": {
          "field": "total_quantity"
        }
      },
      "taxless_total_price.sum": {
        "sum": {
          "field": "taxless_total_price"
        }
      },
      "total_quantity.max": {
        "max": {
          "field": "total_quantity"
        }
      },
      "order_id.cardinality": {
        "cardinality": {
          "field": "order_id"
        }
      }
    }
  }
}

Start the transform.

Even though resource utilization is automatically adjusted based on the cluster load, a transform increases search and indexing load on your cluster while it runs. When it reaches the end of the data in your index, it stops automatically. If you’re experiencing an excessive load, however, you can stop it sooner.

You can start, stop, and manage data frame jobs in Kibana:

Alternatively, you can use the start transforms and stop transforms APIs. For example:
```
POST _data_frame/transforms/ecommerce-customer-transform/_start
```
Explore the data in your new index.

For example, use the Discover application in Kibana:

If you do not want to keep the transform, you can delete it in Kibana or use the delete transform API. When you delete a transform, its destination index remains.

« API quick reference Transform examples »