Augmentation Tables¶
In this tutorial, you will learn how to interact with Augmentation Tables via PyCelonis. More specifically, you will learn:
- How to create an augmentation table and insert data
- How to retrieve, update and remove data from augmentation tables
Prerequisites¶
To follow this tutorial, you should have already created a data pool, data model and a data model table inside the EMS and should have uploaded data into it. If you haven't done this yet, please complete at a minimum the Data Integration - Introduction and the Data Push tutorials first. However, it is recommended to also complete the other Data Integration tutorials, as this tutorial will build upon the tables created in the previous tutorials.
⚠️ Warning: Steps seen in this tutorial can only be performed on the augmentation tables created by pycelonis.
Tutorial¶
Augmentation tables keep augmented attributes that are assets that can be updated in real-time and without the data model reloading. By assigning an augmented attribute to a specific table in the data model, you can add additional information to the source system. This additional information can then be referenced in your Views, describing your business objects in further detail and generating additional process insights.
1. Import PyCelonis and Connect to the Celonis API¶
Augmentation Tables require OAuth authentication. As this is the new standard authentication process, please refer to the Celonis Basics section for detailed instructions on configuring OAuth.
Note: If you are working within the Celonis Workbench, authentication is handled automatically. You do not need to configure OAuth manually; simply call get_celonis().
import pandas as pd
from pycelonis import get_celonis
import pycelonis.pql as pql
celonis = get_celonis(permissions=False)
Couldn't connect to Celonis EMS https://dummy.dummy.celonis.cloud. Please check if you set the correct 'key_type' and the token is valid. To learn more about setting up your Application Key correctly, check out https://dummy.dummy.celonis.cloud/help/display/CIBC/Application+Keys.
2. Get Data Pool and Data Model¶
First step is to get a data model to create and interact with an augmentation table.
data_pool = celonis.data_integration.get_data_pools().find("PyCelonis Tutorial Data Pool")
data_model = data_pool.get_data_models().find("PyCelonis Tutorial Data Model")
3. Create Data Model Table¶
If it is not created already, create a data model table to bind the augmentation table to. Following objects need to be found or created. If the data model table is newly created, data model reload is necessary.
data_pool_table = data_pool.create_table(pd.DataFrame({"DATA_POOL_TABLE_ID": [0, 1, 2]}), "PYCELONIS_TUTORIAL_DATA_MODEL_TABLE")
data_model_table = data_model.add_table(data_pool_table.name)
data_model.reload()
STRING columns are by default stored as VARCHAR(80) and therefore cut after 80 characters. You can specify a custom field length for each column using the `column_config` parameter.
3. Create Augmentation Table¶
Using the data model from the previous step, create an augmentation table.
augmentation_table = data_model.create_augmentation_table(
df=pd.DataFrame(
{
"AUG_ID": [0, 1, 2],
"VALUE": ["A", "B", "C"],
}
),
table_name="PYCELONIS_TUTORIAL_AUGMENTATION_TABLE",
key="AUG_ID",
data_model_table_name="PYCELONIS_TUTORIAL_DATA_MODEL_TABLE",
foreign_key_columns=[("AUG_ID", "DATA_POOL_TABLE_ID")],
)
augmentation_table
AugmentationTable(name='PYCELONIS_TUTORIAL_AUGMENTATION_TABLE', data_model_id='9dbf34e3-8aa6-40fa-87bb-2a46f9503ba9')
You can define columns with other data types as well such as int, float, datetime etc. The data types will be inferred during creation.
3. Retrieve Augmentation Table Content¶
In order to get the content of the augmentation table we can use from_pql function and pycelonis.pql.
query = (
pql.PQL()
+ pql.PQLColumn(name="AUG_ID", query='"PYCELONIS_TUTORIAL_AUGMENTATION_TABLE"."AUG_ID"')
+ pql.PQLColumn(name="VALUE", query='"PYCELONIS_TUTORIAL_AUGMENTATION_TABLE"."VALUE"')
)
table = pql.DataFrame.from_pql(query, data_model=data_model)
table.head()
| AUG_ID | VALUE | |
|---|---|---|
| Index | ||
| 0 | 0 | A |
| 1 | 1 | B |
| 2 | 2 | C |
4. Insert or Update Values in the Augmentation Table¶
Use the upsert function to update or insert values into the augmentation table. If the primary key of the tuple matches a row in the table it updates, if not it inserts.
augmentation_table.upsert(
df=pd.DataFrame(
{
"AUG_ID": [1, 2, 3],
"VALUE": ["D", "E", "F"],
}
)
)
table = pql.DataFrame.from_pql(query, data_model=data_model)
table.head()
| AUG_ID | VALUE | |
|---|---|---|
| Index | ||
| 0 | 0 | A |
| 1 | 1 | D |
| 2 | 2 | E |
| 3 | 3 | F |
Here, since the IDs 1 and 2 existed in the table, they are updated with the new values and ID 3 with value F is added.
Note that, you can only edit or insert new rows but can not add new columns using upsert.
4. Remove Rows from Table¶
Using the key column you can delete rows from the augmentation table.
augmentation_table.remove(df=pd.DataFrame({"AUG_ID":1, "VALUE": ["D"]}), key="AUG_ID")
table = pql.DataFrame.from_pql(query, data_model=data_model)
table.head()
| AUG_ID | VALUE | |
|---|---|---|
| Index | ||
| 0 | 0 | A |
| 1 | 2 | E |
| 2 | 3 | F |
table = pql.DataFrame.from_pql(query, data_model=data_model)
table.head()
| AUG_ID | VALUE | |
|---|---|---|
| Index | ||
| 0 | 0 | A |
| 1 | 2 | E |
| 2 | 3 | F |
5. Delete Augmentation Table¶
You can delete the augmentation table from the data model by calling:
augmentation_table.delete()
Conclusion¶
Congratulations! You have learned how to create and retrieve Augmentation Tables via PyCelonis and how to insert or update rows in the table. You have now reached the end of the Data Integration tutorial and should be equipped with enough knowledge to get data from and upload data into the EMS with ease. In the next tutorial Studio Introduction, you will learn how to perform basic interactions with Celonis Studio.