{"id":601911,"date":"2019-02-21T11:10:19","date_gmt":"2019-02-21T10:10:19","guid":{"rendered":"https:\/\/www.devoteam.com\/expert-view\/implementing-a-left-join-in-google-dataflow-apache-beam\/"},"modified":"2019-02-21T11:10:19","modified_gmt":"2019-02-21T10:10:19","slug":"implementing-a-left-join-in-google-dataflow-apache-beam","status":"publish","type":"expert-view","link":"https:\/\/devoteam.info\/en-nl\/expert-view\/implementing-a-left-join-in-google-dataflow-apache-beam\/","title":{"rendered":"Implementing a left join in Google Dataflow (Apache Beam)"},"content":{"rendered":"
\n
NOTE: The python example code for this technical \n<\/strong>blog can be found in this GitHub repo:<\/strong><\/pre>\n
GitHub repo<\/a><\/div>\n<\/div>\n
Consider the following situation; you have some data loaded into Apache Beam which you need to process. You have some value in the source data which you want to use to find a unique row in a BigQuery table. Consider the following source data.<\/span><\/p>\n\n\n\n\n\n\n\n
Row UUID<\/b><\/td>\nCountry<\/b><\/td>\nYear<\/b><\/td>\nCheese consumption per capita per year (kg)<\/b><\/td>\n<\/tr>\n
1<\/span><\/td>\nThe Netherlands<\/span><\/td>\n2011<\/span><\/td>\n19.4<\/span><\/td>\n<\/tr>\n
2<\/span><\/td>\nThe Netherlands<\/span><\/td>\n2012<\/span><\/td>\n20.1<\/span><\/td>\n<\/tr>\n
3<\/span><\/td>\nFrance<\/span><\/td>\n2011<\/span><\/td>\n26.3<\/span><\/td>\n<\/tr>\n
4<\/span><\/td>\nChina<\/span><\/td>\n2011<\/span><\/td>\n0.1<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Table 1. Source data<\/i><\/p>\n
The Challenge<\/span><\/h2>\n
We want to look up in which continent the countries described in the table above reside for each row. As an example we have the following table from some data source (e.g. BigQuery, json etc.):<\/span><\/p>\n\n\n\n\n\n\n\n
Country<\/b><\/td>\nContinent<\/b><\/td>\n<\/tr>\n
The Netherlands<\/span><\/td>\nEurope<\/span><\/td>\n<\/tr>\n
China<\/span><\/td>\nAsia<\/span><\/td>\n<\/tr>\n
France<\/span><\/td>\nEurope<\/span><\/td>\n<\/tr>\n
Brazil<\/span><\/td>\nSouth America<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Table 2. Join Data<\/i><\/p>\n
We need to left join on country to find the correct continent. In SQL, a left join is a command to join two tables with a common column. The SQL command would be:<\/span><\/p>\n
\n
SELECT Row UUID, Country, Year, Cheese consumption per capita per year (kg)\nFROM table1\nLEFT JOIN table2 ON table1.Country = table2.Country;<\/pre>\n<\/div>\n
We want to join the first table with the second table on the Country column. This would result in following joined table:<\/span><\/p>\n\n\n\n\n\n\n\n
Row UUID<\/b><\/td>\nCountry<\/b><\/td>\nYear<\/b><\/td>\nCheese consumption per capita per year (kg)<\/b><\/td>\nContinent<\/b><\/td>\n<\/tr>\n
1<\/span><\/td>\nThe Netherlands<\/span><\/td>\n2011<\/span><\/td>\n19.4<\/span><\/td>\nEurope<\/span><\/td>\n<\/tr>\n
2<\/span><\/td>\nThe Netherlands<\/span><\/td>\n2012<\/span><\/td>\n20.1<\/span><\/td>\nEurope<\/span><\/td>\n<\/tr>\n
3<\/span><\/td>\nFrance<\/span><\/td>\n2011<\/span><\/td>\n26.3<\/span><\/td>\nEurope<\/span><\/td>\n<\/tr>\n
4<\/span><\/td>\nChina<\/span><\/td>\n2011<\/span><\/td>\n0.1<\/span><\/td>\nAsia<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
The left join is a build-in function in SQL and is very useful to match records from 1 table with another in relational databases. In Apache Beam however there is no left join implemented natively. There is however a CoGroupByKey PTransform that can merge two data sources together by a common key.<\/p>\n
How to implement a left join using the python version of Apache Beam<\/span><\/h2>\n
The overall workflow of the left join is presented in the dataflow diagram presented in Figure 1.\u00a0<\/span><\/p>\n
\"hcl-technical-blog-google-cloud-platform-devoteam\"<\/a><\/p>\n
Figure 1. Overall Dataflow Diagram<\/i>
\n<\/i><\/b>(Click to enlarge)<\/i><\/p>\n
In a production environment you would typically have data in some file format (e.g. csv, json, txt) which you have read using for example the build-in ReadFromText module of Apache Beam IO and do some processing using DoFn\u2019s to format it as a pcollection of dictionaries. <\/span><\/p>\n
CoGroupByKey explained<\/span><\/h2>\n
In order to use the CoGroupByKey PTransform, we need to format the read data as common (key, value) tuples. The read data from 2 or more sources are grouped together by the common key. Figure 2 shows what the CoGroupByKey looks like in python syntax, where we have 2 element tuples to be cogrouped. Each 2-element tuple must contain a key to be grouped. The first element of the tuple is the key and the second element of the tuple is the object you want to group. It can be any serializable object<\/span>. The grouped tuple contains (for every unique key) one 2-element tuple where the second element is a dictionary where the keys are the names of the data sources and the values are a list of all the second elements of the set of all data source tuples you want to cogroup.<\/span><\/p>\n
\"hcl-technical-blog-google-cloud-platform-2-devoteam\"Figure 2. CogroupByKey Diagram<\/i><\/p>\n
Apache Beam’s built-in <\/span>CoGroupByKey<\/span><\/a> core Beam transform forms the basis of a left join. The objects we want to cogroup are in case of a LeftJoin dictionaries. Example source data tuples:<\/span><\/p>\n
\n
('The Netherlands', {'Country': 'The Netherlands', 'Year': '2011','Cheese consumption \nper capita per year (kg)': '19.4'})<\/pre>\n
('The Netherlands', {'Country': 'The Netherlands', 'Year': '2012','Cheese consumption \nper capita per year (kg)': '20.1'})<\/pre>\n<\/div>\n
Example join data tuple:<\/span><\/p>\n
\n
('The Netherlands', {'Country': 'The Netherlands', 'Continent': 'Europe'})<\/pre>\n<\/div>\n
Example grouped tuple:<\/span><\/p>\n
\n
('The Netherlands', {'source_data': [{'Country': 'The Netherlands', 'Cheese consumption \nper capita per year (kg)': '20.1', 'Year': '2012'}, {'Country': 'The Netherlands', \n'Cheese consumption per capita per year (kg)': '19.4', 'Year': '2011'}], 'join_data': \n[{'Country': 'The Netherlands', 'Continent': 'Europe'}]})<\/pre>\n<\/div>\n
We still need to unnest the CoGroupBykey output to get the original source data. So our grouped tuple has to become like the following:<\/span><\/p>\n
\n
{'Country': 'The Netherlands', 'Cheese consumption per capita per year (kg)': \n'19.4', 'Continent': 'Europe', 'Year': '2011'}<\/pre>\n
{'Country': 'The Netherlands', 'Cheese consumption per capita per year (kg)': \n'20.1', 'Continent': 'Europe', 'Year': '2012'}<\/pre>\n<\/div>\n
 We will explain how to implement the entire left join process in Apache Beam.<\/span><\/p>\n
How to implement Left Join using CoGroupByKey<\/span>
\n<\/span><\/h2>\n
\"hcl-technical-blog-google-cloud-platform-3-devoteam\"<\/p>\n
Figure 3. Overall Join Dataflow Diagram<\/i><\/p>\n
The Dataflow diagram of the join process is presented in Figure 3. The join process has 4 processing steps consisting of steps 1.1, 1.2, 2.0 and 3.0. Steps 1.1 and 1.2 run in parallel and steps 2.0 and 3.0 run after each other. We are now going to walkthrough the transformation process of the dataflow diagram in Figure 3. At step 0.1 and 0.2 we have (for illustration purposes) the source_data and join_data pcollection. For the purpose of our example implementation we generate these read elements ourselves. The following code creates the example dictionaries in Apache Beam, puts them into a pipelines_dictionary containing the source data and join data pipeline names and their respective pcollections and performs a Left Join.<\/span><\/p>\n
\"google-cloud-platform-technical-blog-left-join-tables-devoteam-1\"<\/p>\n
The code above can be found as part of the example code on the\u00a0GitHub repo<\/a><\/em><\/p>\n
The LeftJoin is implemented as a composite PTransform. The composite PTransform takes as input a pipelines_dictionary containing the source_pipeline_name, source_data pcollection, join_pipeline_name and join_data pcollection.<\/span><\/p>\n
\"google-cloud-platform-technical-blog-left-join-tables-devoteam-2\"<\/p>\n
The code above can be found as part of the example code on the GitHub repo<\/a><\/em><\/p>\n
It also needs source_pipeline_name, source_data, \u00a0join_pipeline_name, join_data and common_key as constructor variables. An example walkthrough of the transformations that happen with the data is as follows.<\/span><\/p>\n
For example:<\/span><\/p>\n
\n
{'Country': 'The Netherlands', 'Year': '2011',\n 'Cheese consumption per capita per year (kg)': '19.4'}<\/pre>\n
{'Country': 'The Netherlands', 'Year': '2012', \n'Cheese consumption per capita per year (kg)': '20.1'}<\/pre>\n
{'Country': 'France', 'Year': '2011', \n'Cheese consumption per capita per year (kg)': '26.3'}<\/pre>\n
{'Country': 'China', 'Year': '2012',\n'Cheese consumption per capita per year (kg)': '0.1'}<\/pre>\n<\/div>\n
and<\/p>\n
\n
{'Country': 'The Netherlands', 'Continent': 'Europe'}\n{'Country': 'China', 'Continent': 'Asia'}\n{'Country': 'USA', 'Continent': 'North America'}\n{'Country': 'Brazil', 'Continent': 'South America'}<\/pre>\n<\/div>\n
becomes<\/span><\/p>\n
\n
('Brazil', {'source_data': [], 'join_data': [{'Country': 'Brazil', \n'Continent': 'South America'}]})<\/pre>\n
('USA', {'source_data': [], 'join_data': [{'Country': 'USA', \n'Continent': 'North America'}]})<\/pre>\n
('The Netherlands', {'source_data': [{'Country': 'The Netherlands', \n'Cheese consumption per capita per year (kg)': '20.1', 'Year': '2012'}, \n{'Country': 'The Netherlands', 'Cheese consumption per capita \nper year (kg)': '19.4', 'Year': '2011'}], 'join_data': [{'Country': \n'The Netherlands', 'Continent': 'Europe'}]})<\/pre>\n
('China', {'source_data': [{'Country': 'China', \n'Cheese consumption per capita per year (kg)': '0.1', \n'Year': '2011'}], 'join_data': [{'Country': 'China', \n'Continent': 'Asia'}]})<\/pre>\n
('France', {'source_data': [{'Country': 'France', \n'Cheese consumption per capita per year (kg)': '26.3', \n'Year': '2011'}], 'join_data': []})<\/pre>\n<\/div>\n
From here onwards we unnests the source data dictionaries and update them with the matched joined data dictionary. We then emit the updated source data dictionaries. The output will then be:<\/span><\/p>\n
\n
{'Country': 'The Netherlands', 'Year': '2011', \n'Cheese consumption per capita per year (kg)': '19.4', 'Continent': 'Europe'}<\/pre>\n
{'Country': 'The Netherlands', 'Year': '2012', \n'Cheese consumption per capita per year (kg)': '20.1', 'Continent': 'Europe'}<\/pre>\n
{'Country': 'France', 'Year': '2011', \n'Cheese consumption per capita per year (kg)': '26.3'}<\/pre>\n
{'Country': 'China', 'Year': '2011',\n'Cheese consumption per capita per year (kg)': '0.1', 'Continent': 'Asia'}<\/pre>\n<\/div>\n
The PTransform code uses 2 classes, namely the LeftJoin Ptransform class and the UnnestCoGrouped DoFn class. The LeftJoin is a Composite PTransform that uses dictionary comprehension and Map to format the source and join data as suitable cogroupby tuples. It then uses the built-in CoGroupByKey PTransform to group them together by common_key. The last part is to unnest the grouped dictionaries and emit the updated dictionaries which is done using the UnnestCogrouped DoFn.<\/span><\/p>\n
\"google-cloud-platform-technical-blog-left-join-tables-devoteam-3\"<\/p>\n
The code above can be found as part of the example code on the GitHub repo<\/a><\/em><\/p>\n
The full example code block to run this example is given below:<\/span><\/p>\n
\"google-cloud-platform-technical-blog-left-join-tables-devoteam-4a\"<\/p>\n
\"google-cloud-platform-technical-blog-left-join-tables-devoteam-4b\"<\/p>\n
The code above can be found as part of the example code on the GitHub repo<\/a><\/em><\/p>\n
If you run this example you would have the following Graph in Dataflow:<\/span><\/p>\n
\"hcl-technical-blog-google-cloud-platform-4-devoteam\"<\/p>\n
Which can be magnified by clicking on the down arrow next to the Left Join node:<\/span><\/p>\n
\"hcl-technical-blog-google-cloud-platform-5-devoteam\"<\/p>\n
Exposing the nested logic of the implemented Leftjoin PTransform.<\/span><\/p>\n
If you want to use the implemented LeftJoin, you would need the LeftJoin PTransform class and the UnnestCoGrouped DoFn class. Use the full code as an example guide to use the LeftJoin PTransform. This completes the walkthrough of implementing a LeftJoin in the python version of Apache Beam. <\/span><\/p>\n
Conclusion<\/span><\/h2>\n
In short, this article explained how to implement a leftjoin in the python version of Apache Beam. The user can use the provided example code as a guide to implement leftjoin in their own Apache Beam workflows. If you want to learn more about Apache Beam, <\/span>click here to get all the Apache Beam resources<\/a>.<\/span><\/p>\n
<\/div>\n
Related Google Cloud Technical blog-posts<\/h3>\n