Vertica + Anchor Modeling = start the growth of your mycelium
Some time ago I wrote an article on Habré . In it, he promised to continue in a couple of weeks. But, as you know, they are waiting for the promised three years - and since then, indeed three years have passed. If you haven’t remembered since that article, I’ll remind you that I work at Avito and build storage based on Vertica.
From what has changed - now I can not just write an article, but do it in the company’s blog. And hopefully more than once. Samopiar is over, now to the point. My repository is based on the Anchor Modeling methodology
. In 2013, this choice of methodology was, in many ways, a leap of faith. Now, after almost 4 years, we can say that the jump was a success. The first article presented quite a few arguments for Anchor Modeling. Now these arguments have remained true, but faded into the background.
To date, the main argument for using Anchor Modeling + Vertica is one: almost limitless growth is possible. Growth in volumes, flow rates and diversity - all that is commonly called 3V (Volume, Velocity, Variety), and that characterizes big data.
Imagine that your storage is a small mushroom picker. It begins with one argument, and then begins to grow, covering meter by meter, braids trees until a multi-ton monster is obtained ... whose growth does not stop ... does not stop.
Imagine yourself as a vault architect. It’s good when you can at the start evaluate the approximate volumes of data, integrable systems, data analysis algorithms. Then you can choose a data model and processing platform for your situation. And not the fact that in this case, the choice of Vertica + Anchor Modeling will be optimal.
Anchor is a noun, an object of the real world. Product, user, payment. Accordingly, each noun has its own table. Anchor table should store ONLY a surrogate key (in Vertica, the best key is int ) and several technical fields. Conceptually, Anchor is needed only for one task - to ship each unique product / user / payment only once. Avoid reloading, and remember at what point and from which system the original recording came from. All.
To understand how the task of identifying the product / user / payment is solved, we turn to the second entity.
Attribute is a table for storing a property, an attribute of an object. Names of goods, username and date of birth of the user, payment amount. One property of an object is one Attribute table. Ten properties of the object (name, surname, date of birth, gender, registration address, ...) - ten Attribute tables. Everything is simple. It’s hard for the psyche, because the number of tables at the beginning is very scary, but simple.
Each Attribute table contains a surrogate key of the object, which is a link to the corresponding Anchor, a field for the attribute value, and, optionally, a date for historicity and technical fields. Accordingly, the Attribute table for the name of the buyer (Customer) should be called S_Customer_Name and contain the fields Customer_id (surrogate key), Name (attribute value) and Actual_date (date for SC2 historicity). As you can see, the name of the table and the names of all its fields are uniquely determined by its contents (customer name).
What nuance does Vertica add? ... It's simple, all Attribute tables for one Anchor should be segmented identically: segmented by hash of a surrogate key, sorted by surrogate key and by historical date. A simple rule, following which you will be guaranteed that all joins between the Attribute tables of the same Anchor will be MERGE JOIN - the most efficient join in Vertica. Similarly, this segmentation guarantees the optimality of the window functions necessary to serve ETL operations with SC2 historicity on a single date.
In the previous section, a description of the approach to identifying objects was announced: a string of data about the user comes in - how to understand if this user is already in Anchor, or is he new? Naturally, the answer to this question is sought in the attributes. The main advantage of Anchor Modeling is the ability to first use some attributes (name), and then start using others (name + TIN). Moreover, taking into account historicity.
Tie is a table for storing relationships between objects. For example, a table to store the fact that the buyer has citizenship in a particular country. Accordingly, the table should contain the surrogate key of the left object (customer_id), the right object (country_id) and, if necessary, the historicity date and technical fields.
From the point of view of Vertica, the following nuance is added - the Tie table must be created with two projections - segmented by the left surrogate and segmented by the right surrogate. So that at least one of the JOINs in this table is MERGE JOIN.
An important nuance from the point of view of simulations - Anchor Modeling is very different from Data Vault in that in Data Vault you can hang data (satellites) for communication (link), and in Anchor Modeling you can hang data (Attribute) only on Anchor, you can not hang it on Tie ( important - NOT). This seemingly excessive restriction makes it possible to more accurately model the real physical world. For example, the traditional relationship with properties in Data Vault is the fact of selling a product to a customer whose property is the sale amount. Anchor Modeling makes you think a little and understand that the fact of selling a customer’s product is not an element of the real world, but an abstraction. An element of the real world is a check (piece of paper) with a number, date, etc. Accordingly, in Anchor modeling, the described example is described by three Anchor - Buyer, Check, Product, and two Tie: Buyer-Check and Check-Product.
(An attentive reader will notice that even the example picture at the beginning of the section is not entirely correct. The fact of citizenship is fixed by a certain document (passport), and it is more correct to present the specified data through Anchor with a passport).
When you read Anchor Modeling for the first time, it gets scary.
It's scary to drown in the tables. Fear is fair, it is important not to let it stop itself. The above illustration shows the growth rate of the number of tables of each type in Avito over 4 years (the right graph is the total number of Anchor + Attribute + Tie).
Let me remind you the first graph in the article - the Avito storage at the end of 2016 included data from more than 29 source systems. As you can see, there are many tables. But not terrifyingly much. We can say that a large jump in the number of tables occurs at the beginning, and then, due to the growing reuse of old tables, the growth rate decreases. The sharp jump in the number of tables at the end of 2016 is due to the connection of an unusually large number of new systems and demonstrates that, despite the size of the system, it is still able to expand.
The second reason to be afraid of a large number of tables is the complexity of analysis by external analysts.
I’ll talk about a way to deal with this fear in the next article. I hope this time she won’t have to wait another three years :) In the meantime, you can study the recordings of my speeches on the topic at seminars , conferences and webinars .
PS. Lars has completed his online course for those who want to understand the nuances of Anchor Modeling.
anchor.teachable.com/courses/enrolled/124660 . Recommend. There is even one video about Avito case :).
From what has changed - now I can not just write an article, but do it in the company’s blog. And hopefully more than once. Samopiar is over, now to the point. My repository is based on the Anchor Modeling methodology
. In 2013, this choice of methodology was, in many ways, a leap of faith. Now, after almost 4 years, we can say that the jump was a success. The first article presented quite a few arguments for Anchor Modeling. Now these arguments have remained true, but faded into the background.
To date, the main argument for using Anchor Modeling + Vertica is one: almost limitless growth is possible. Growth in volumes, flow rates and diversity - all that is commonly called 3V (Volume, Velocity, Variety), and that characterizes big data.
Imagine that your storage is a small mushroom picker. It begins with one argument, and then begins to grow, covering meter by meter, braids trees until a multi-ton monster is obtained ... whose growth does not stop ... does not stop.
Imagine yourself as a vault architect. It’s good when you can at the start evaluate the approximate volumes of data, integrable systems, data analysis algorithms. Then you can choose a data model and processing platform for your situation. And not the fact that in this case, the choice of Vertica + Anchor Modeling will be optimal.
Essence One - Anchor
Anchor is a noun, an object of the real world. Product, user, payment. Accordingly, each noun has its own table. Anchor table should store ONLY a surrogate key (in Vertica, the best key is int ) and several technical fields. Conceptually, Anchor is needed only for one task - to ship each unique product / user / payment only once. Avoid reloading, and remember at what point and from which system the original recording came from. All.
To understand how the task of identifying the product / user / payment is solved, we turn to the second entity.
Essence Two - Attribute
Attribute is a table for storing a property, an attribute of an object. Names of goods, username and date of birth of the user, payment amount. One property of an object is one Attribute table. Ten properties of the object (name, surname, date of birth, gender, registration address, ...) - ten Attribute tables. Everything is simple. It’s hard for the psyche, because the number of tables at the beginning is very scary, but simple.
Each Attribute table contains a surrogate key of the object, which is a link to the corresponding Anchor, a field for the attribute value, and, optionally, a date for historicity and technical fields. Accordingly, the Attribute table for the name of the buyer (Customer) should be called S_Customer_Name and contain the fields Customer_id (surrogate key), Name (attribute value) and Actual_date (date for SC2 historicity). As you can see, the name of the table and the names of all its fields are uniquely determined by its contents (customer name).
What nuance does Vertica add? ... It's simple, all Attribute tables for one Anchor should be segmented identically: segmented by hash of a surrogate key, sorted by surrogate key and by historical date. A simple rule, following which you will be guaranteed that all joins between the Attribute tables of the same Anchor will be MERGE JOIN - the most efficient join in Vertica. Similarly, this segmentation guarantees the optimality of the window functions necessary to serve ETL operations with SC2 historicity on a single date.
In the previous section, a description of the approach to identifying objects was announced: a string of data about the user comes in - how to understand if this user is already in Anchor, or is he new? Naturally, the answer to this question is sought in the attributes. The main advantage of Anchor Modeling is the ability to first use some attributes (name), and then start using others (name + TIN). Moreover, taking into account historicity.
Essence Three - Tie
Tie is a table for storing relationships between objects. For example, a table to store the fact that the buyer has citizenship in a particular country. Accordingly, the table should contain the surrogate key of the left object (customer_id), the right object (country_id) and, if necessary, the historicity date and technical fields.
From the point of view of Vertica, the following nuance is added - the Tie table must be created with two projections - segmented by the left surrogate and segmented by the right surrogate. So that at least one of the JOINs in this table is MERGE JOIN.
An important nuance from the point of view of simulations - Anchor Modeling is very different from Data Vault in that in Data Vault you can hang data (satellites) for communication (link), and in Anchor Modeling you can hang data (Attribute) only on Anchor, you can not hang it on Tie ( important - NOT). This seemingly excessive restriction makes it possible to more accurately model the real physical world. For example, the traditional relationship with properties in Data Vault is the fact of selling a product to a customer whose property is the sale amount. Anchor Modeling makes you think a little and understand that the fact of selling a customer’s product is not an element of the real world, but an abstraction. An element of the real world is a check (piece of paper) with a number, date, etc. Accordingly, in Anchor modeling, the described example is described by three Anchor - Buyer, Check, Product, and two Tie: Buyer-Check and Check-Product.
(An attentive reader will notice that even the example picture at the beginning of the section is not entirely correct. The fact of citizenship is fixed by a certain document (passport), and it is more correct to present the specified data through Anchor with a passport).
Total - 4 years with Anchor Modeling
When you read Anchor Modeling for the first time, it gets scary.
It's scary to drown in the tables. Fear is fair, it is important not to let it stop itself. The above illustration shows the growth rate of the number of tables of each type in Avito over 4 years (the right graph is the total number of Anchor + Attribute + Tie).
Let me remind you the first graph in the article - the Avito storage at the end of 2016 included data from more than 29 source systems. As you can see, there are many tables. But not terrifyingly much. We can say that a large jump in the number of tables occurs at the beginning, and then, due to the growing reuse of old tables, the growth rate decreases. The sharp jump in the number of tables at the end of 2016 is due to the connection of an unusually large number of new systems and demonstrates that, despite the size of the system, it is still able to expand.
The second reason to be afraid of a large number of tables is the complexity of analysis by external analysts.
I’ll talk about a way to deal with this fear in the next article. I hope this time she won’t have to wait another three years :) In the meantime, you can study the recordings of my speeches on the topic at seminars , conferences and webinars .
PS. Lars has completed his online course for those who want to understand the nuances of Anchor Modeling.
anchor.teachable.com/courses/enrolled/124660 . Recommend. There is even one video about Avito case :).
