Analyzing Patent Data Using Spark and Airflow
My dream is to become a full-stack data scientist, who can handle end-to-end data science projects from data preparation to model building to deployment. Before building a machine-learning model, it is necessary to obtain data and transform it into a format that the model can consume. For this reason, I completed a Udacity Nanodegree Program in Data Engineering to gain a foundational understanding of data engineering.
In this article, I will share with you my final project from the nano-degree program. For the final project, Udacity gave me the option of choosing my own project or doing the one they had prepared. To challenge myself, I decided to curate my project by thinking about what problems to solve, how to approach the problem, and what the outcome should be.
I chose patent analysis as my final project since public data is readily available and patents are familiar to most people.
According to the World Intellectual Property Office (WIPO), a patent is an exclusive right granted for an invention, which is a product or process that provides, in general, a new way of doing something, or offers a new technical solution to a problem.
Therefore, patenting activities are a valuable source of data that can be used to evaluate technological progress. Analyzing patents may help companies to stay on top of the critical developments in their industries, to identify the key players (competitors), and to identify potential research topics.
Throughout this article, I will discuss the steps I took to complete my project, starting from scoping the project and gathering the data to building the dashboard.
In addition to that, I would also cover the challenges I encountered, and what improvements could be made.
The project is implemented using the following technologies: PySpark, Airflow, Amazon Redshift, and Tableau. You can find the project’s source code on GitHub
Scoping the project and gathering the data
Finding the Right Data
Finding the right data is one of the crucial steps in this project, as it would determine what kind of transformation and analysis could be performed. Udacity suggested we look at various public data sources like Kaggle Datasets, Github: Awesome Public Datasets, and UCI Machine Learning Repository to find a dataset. My goal is to find datasets I can understand and that are interesting to me. In my search, I came across patent data provided by USPTO PatentsView. The data is licensed under a Creative Commons Attribution 4.0 License which means it can be shared and adapted with attribution.
Reviewing the Data
I reviewed the dataset to determine what information it contains and what might be interesting. This data contains patents granted by the United States Patent and Trademark Office (USPTO) until 2021, a total of 7,991,058 patents. This data includes information about the patent, its classification, its assignees and inventors, and others.
I also did some reading about patents to gain a deeper understanding, and identify areas for further investigation. The article from World Intellectual Property Report 2022 about The Direction of Innovation was helpful in determining what kind of insights to look for.
Defining Project Goal
The next step is to decide what I want to build as the end product. Having reviewed the datasets, I identified the information I could extract from the data. These are some of them:
- An assessment of the number of patents can be made to determine which industries have experienced growth over time.
- Patent ownership by the top 10 or 20 companies in a particular field indicates the major players in that field.
- Patent keyword extraction could serve as a valuable method for obtaining direction for research in a specific technological field.
To facilitate user exploration of this data, I decided to create a dashboard to display this information.
Data exploration
Before constructing any Extract Transform Load (ETL) scripts, we need to understand the data and identify any data quality issues, such as missing values, invalid values, duplicate values, etc.
Although the patent dataset is only about 5.9 GB in size, I used Spark to perform data exploration and other data transformations to take advantage of the Spark’s distributed data processing.
Spark is a computing platform that is able to rapidly process large data sets as well as distribute data processing tasks to multiple nodes. I used PySpark, a Python library for interacting with Apache Spark. Besides Python, Scala, Java, and R can also be used to interact with Spark.
Spark clusters can be run in various ways, including running it locally. However, we would be limited by the computational power of our machine. The other option is to use managed Spark cluster in the cloud like Amazon EMR. Amazon EMR allows us to specify cluster specifications, such as the number of nodes and compute and storage requirement for each node. It is also possible to start and stop the cluster as and when we need it. In this project, I used an Amazon EMR cluster to run Spark scripts.
To make the datasets accessible to Amazon EMR, I uploaded them to Amazon S3. Afterwards, I could load the data using JupterLab (which is provided out of the box with Amazon EMR ) and analyzed each field to check for any issues. We could use Spark SQL to query and analyse the data. The analysis looks something like this
I documented the issues found as well as the steps I planned to resolve them. After running the above steps, these are the issues that were discovered in the dataset:
- Patent’s owner locations contain multiple fields, including city, county, county_fips, state, state_fips, country, latitude, and longitude. The dataset may, however, contain null values in any of these fields. Due to this, I decided to aggregate the location to the level of the country since it is the highest level of location. When the data do not contain information on the country field, I mapped the latitude and longitude coordinates to the country by using the PyGeodesy Phyton library and GeoNames country polygon data.
- A number of patents do not contain information regarding the assignee. In such cases, the patent inventor is regarded as the patent assignee.
Designing the Data Model
After gaining a deeper understanding of the data from the data exploration, I designed a suitable data model for our analytics database.
The data model follows a star schema with patents and PatentKeywords as fact tables and Dates, Details, WIPOClassifications, Locations and Owners as dimensions.
I chose Amazon Redshift for storing the results because Redshift is built for data warehouse purposes and supports efficient analytical queries. It has columnar table design and ability to do massively parallel processing with data distributed across various nodes.
Redshift allow us to specify the table distribution styles and distribution keys to optimize our query. Our aim while specifying the distribution styles and distribution keys are to distribute workload among the cluster nodes uniformly and minimize data movement during query execution.
For small tables, such as Dates, Owners and Locations, I set the distribution style as ALL, which means these data are copied on all nodes. Meanwhile, for larger tables, such as WIPOClassifications, Patents and Patent Keywords, I set the distribution style as KEY. This would allow the data to be distributed across the nodes according to the specified DISTKEY.
Building the ETL Scripts
In this step, I built the script to perform data cleansing, keyword extraction, and ETL to Redshift. First, I coded my script using JupterLab to experiment with the data. Afterward, I rewrote the code into Python scripts.
Data cleansing
This step involved reading the raw data from Amazon S3, performing the cleansing steps as documented during data exploration, and writing the result back to Amazon S3. Here are some examples of the cleansing steps I took:
- Filtering the patents data which were granted from the year 2000 onwards
- Mapping the location information to country level
- Derive patent owner based on the patent assignee and patent inventor information
Keyword extraction
To build the patent keyword cloud, I extracted the patent keywords from the title and abstract of the patents. I experimented with various algorithms like RAKE, Keybert, YAKE. Compared to the rest, Keybert’s output is the most relevant. However, I chose YAKE keyword extraction for this project because it is faster (about 4 times faster on this dataset) and there is a Spark library implementing this algorithm: Spark NLP. The result of keyword extraction were stored in Amazon S3 folders.
ETL to redshift
In this step, the cleansed data is read from the Amazon S3 folder, transformed into the dimensional model, and then written to Amazon Redshift.
Data transformations could be as simple as selecting some columns from the cleansed data, or as complex as joining several tables.
One thing to note when writing to Redshift, PySpark creates temporary files in Amazon S3 to prepare the data to be written to Amazon Redshift. Thus, in the library to write data to Redshift, we need to provide the path to the Amazon S3 folder to store these temporary files and create an IAM role to enable Redshift to read Amazon S3. This is the function I created to load data to Redshift.
def load_data_to_redshift(
spark: SparkSession,
data: DataFrame,
db_jdbc_url,
db_username,
db_password,
s3_temp_dir,
iam_role,
table,
):
spark_logger = logging.Log4j(spark)
spark_logger.warn(f"Writing data to redshift table {table}")
url = f"{db_jdbc_url}?user={db_username}&password={db_password}"
data.write.format("io.github.spark_redshift_community.spark.redshift").option(
"url", url
).option("tempdir", s3_temp_dir).option("dbtable", table).mode("overwrite").option(
"aws_iam_role", iam_role
).save()If you want to find out more on how to write to Redshift from Amazon EMR, Amazon provides good documentation in this article.
Rewriting Jupyter Notebook Code to Python Script
Running python code using JupyterLab is great for exploration, but when we want to productionize our code, we need to convert it into Python script which could be submitted to the Spark cluster.
My goal is to structure my project so that I can write clean code, perform unit tests, and efficiently handle dependencies between modules and packages. PySpark Example Project has good recommendations for structuring PySpark projects. I structured my project according to their advice. My folder structure looks like this
- The configs folder contains all the configurations for PySpark jobs, such as the Redshift database connection and the Amazon S3 path.
- The dependencies folder contains the functions that are shared across our jobs. Doing this allow me to reduce the code repetition.
- The jobs folder contains PySpark scripts that are going to be executed. The scripts are organized by script functions such as data cleansing, keyword extraction, and ETL.
- The tests folder contains the unit test for the PySpark scripts. The structure mimics that of the jobs folder.
The Pyspark Example Project provides a script called build_dependencies.sh which builds all the python dependencies specified in the Pipfile and all the files in the dependencies folder into a file called packages.zip. This significantly reduced the burden of managing the Python dependencies in the Spark nodes.
Get karlina tjoa’s stories in your inbox
Join Medium for free to get updates from this writer.
When submitting the PySpark script to the Spark cluster, we need to submit the script itself, the packages.zip, and the configuration file. I will describe how I submitted the jobs to a spark cluster using Airflow in the next section.
Orchestrating the Data Pipeline
Once I had all the PySpark scripts available, the next steps were to set their order and dependencies. I chose Airflow as the orchestrator engine because I could programmatically author, schedule, and monitor the workflow. My overall architecture for this project is shown below.
I used Airflow to orchestrate the tasks, including raw data downloading, data cleaning, keyword extraction, and ETL to Redshift.
Aside from the main tasks above, it is also a good idea to incorporate some data quality checks. The data quality check may seem like a hassle, but we need to capture problems with the data pipeline as soon as possible to prevent downstream jobs from being wasted. Further, data quality is crucial for our end users, since they may lose confidence with the data if there is data quality issue.
This is the Airflow DAG I created, incorporating the data quality checks along the way:
After the tasks that write results to Amazon S3, I checked whether the resultant file is available there. After writing data to Redshift tables, I used Airflow’s SQLColumnCheckOperator to validate the field value, for example: checking the null value, checking the unique value, and the min and max values. Other options for checking the table quality include GreatExpectation and PyDeequ
You might also be interested in how to submit a job to the Amazon EMR cluster through Airflow. There are several operators provided by Airflow for interacting with Amazon EMR:
- EmrCreateJobFlowOperator: an operator to create an EMR cluster
- EmrAddStepsOperator: an operator to add steps to an EMR cluster
- EmrStepSensor: a sensor to monitor the EMR steps execution
- EmrTerminateJobFlowOperator: an operator to terminate an EMR cluster
There are several tasks I need to submit to EMR clusters in this project, such as cleaning data, extracting keywords, and performing ETL. I created a reusable function build_emr_task in the airflow plugin for submitting jobs to the EMR cluster. It performs the following actions:
- Upload the PySpark script, configuration, and dependencies from the local path to Amazon S3
- Create a new EMR cluster with the specified configuration
- Add the PySpark scripts to the EMR cluster’s steps
- Keep track of the execution of EMR cluster steps
- Terminate the EMR cluster once all the steps are completed
You can check the code on GitHub
Building the dashboard
Now that I’ve got all the data in Redshift and the data quality check is passed, it’s time to visualize and analyze the data.
In the section on “Designing the Data Model”, you can see that I’ve designed the data model in fact and dimension tables. However, Amazon Quicksight needs the fact and dimension tables to be joined together before they can be visualized in the dashboard.
Normally to handle this, people build metrics layers on top of the dimensional models. Then the downstream applications consume the prepared data from the metrics layer. There are a number of metrics layers available, such as Transform.co and LookML. Nevertheless for simplicity, I created a materialized view in Redshift to store the joined data.
According to AWS documentation, materialized view contains a precomputed result set, based on an SQL query. It is useful for speeding up predictable and repeated queries like those for QuickSight. Additionally, Redshift offers automatic refresh options where the refresh is done when cluster resources are available to limit disruptions to other workloads.
It is pretty straightforward to configure QuickSight to access the Redshift database. First, we need to enable QuickSight’s access to Amazon Redshift. This article explains the steps to achieve this. After that, we just need to select the automatic Redshift connector and the view we would like to analyze. Building a dashboard using QuickSight is pretty intuitive using drag and drop.
Since sharing QuickSight dashboards with the public requires purchasing an enterprise license, I created a similar dashboard using Tableau Public. As Tableau Public can only support fifteen million rows, the dashboard only shows patent data granted in 2021. You can check the Tableau dashboard here
Conclusion
Based on the data, I can draw the following conclusions:
Growing Technology Fields
The number of patent applications has steadily increased over the past 20 years. In comparison to all other technological fields, Computer Technology is at the forefront of technological innovation. Other booming fields include the areas of Digital Communication, Electrical Engineering, Electric Machinery and Apparatus, Energy, Medical technology, and Semiconductors.
Top Patent Inventors
An analysis of patent assignees shows that technology companies dominate United States patent activities with International Business Machines Corporation (IBM) leading the pack with close to 130,000 granted patents.
Company Technology Invention Area
This data analysis project can also be used to examine the type of inventions that a company develops. In the following example, the keywords vehicle, battery, sensors and position are extracted from patents owned by Ford Global Technologies, LLC. These keywords suggest that Ford is also exploring the field of electric vehicles.
Next Steps
This project offers many opportunities for improvement. These are the areas I would like to improve if I had more time. Try it out if you’re looking for a challenge.
Improve the keyword extraction
The keyword extraction process is not optimal yet, there are words that are meaningless as the patent keywords, for example: via, support, component, and coupled. We could fine-tune the YAKE Keyword Extraction parameters, or explore other keyword extraction algorithms to get more relevant keywords.
Add entity resolution
In some cases, the name of the patent owner is spelled differently. An example is IBM, which was spelled as follows:
- International Business Machines Corporation
- International Business Machiens Corporation
- International Business Machined Corporation
- International Business machines Corporation Armonk, New York
This may lead to inaccurate patent numbers for these companies. To resolve this issue, entity resolution may be considered.
Automate the data pipeline
As of now, the airflow pipeline is not scheduled according to any interval, because the datasets are not updated frequently (from my observation, the datasets to be updated half-yearly). To fully automate this, we must configure the airflow pipeline to run every half year and to process only the deltas of the datasets.
Enhance the data visualisation
Consider other visualizations that would be interesting to end users.
References
- WIPO. What is a patent? https://www.wipo.int/patents/en/
- WIPO. World Intellectual Property Report 2022 — The Direction of Innovation https://www.wipo.int/edocs/pubdocs/en/wipo-pub-944-2022-en-world-intellectual-property-report-2022.pdf
- USPTO PatentsView https://patentsview.org/
- GeoNames https://www.geonames.org/
- Alex Loannides. PySpark Example Project https://github.com/AlexIoannides/pyspark-example-project
- Spark NLP https://nlp.johnsnowlabs.com/
- AWS. Use Spark on Amazon Redshift with a connector https://docs.aws.amazon.com/emr/latest/ReleaseGuide/emr-spark-redshift.html
- Apache Airflow. Running Airflow in Docker https://airflow.apache.org/docs/apache-airflow/stable/howto/docker-compose/index.html
- Start Data Engineering. How to submit Spark jobs to EMR cluster from Airflow https://www.startdataengineering.com/post/how-to-submit-spark-jobs-to-emr-cluster-from-airflow/
- AWS. Amazon Redshift Engineering’s Advanced Table Design Playbook: Distribution Styles and Distribution Keys https://aws.amazon.com/blogs/big-data/amazon-redshift-engineerings-advanced-table-design-playbook-distribution-styles-and-distribution-keys/
