Challenge 19 - Pokemon Local Database

Challenge 19: Pokémon Local Database

Level: Medium

Description: Recently you have been feeling a bit nostalgic about Pokémon and our related challenge from Season 3, and decided to use the PokeAPI to create your own local database of fantastical creatures. Through the API, you retrieved the dataset of each Pokémon and their types, then created "pokemonAPI_Local.sqlite": a local database. Now you want to create a workflow that fetches data from this database and outputs a list of Pokémon types in descending order of frequency. For a little extra challenge, how about automatically retrieving the latest data from the PokeAPI and updating each data table accordingly? Hint: A lot of the processing should happen within the database for efficiency. Please use the KNIME Database extension in your solution.

(Optional)

For an additional challenge, it would be more beneficial to create a workflow that automatically retrieves the latest data from the PokeAPI and updates or creates each data table accordingly.

Example: Retrieving the Latest Type Data and Updating the Database.

Solution Summary: To address this challenge, we retrieve information on all Pokémon from the existing pokemonAPI_Local.sqlite database. In this process, the DB Joiner node is used to integrate data from two tables, the DB GroupBy node performs aggregation operations, and the DB Sorter node sets the sorting rules. Finally, the DB Reader node outputs the data.

Solution Details:
By utilizing KNIME Database extensions such as the DB Table Selector and SQLite Connector, we access the database and extract the necessary data. Next, we utilize the DB Joiner node to integrate data from two tables within the database, ensuring that all relevant information is consolidated. This is followed by the DB GroupBy node, which performs aggregation operations to group the data according to specific attributes, such as Pokémon types or categories. Once the data is aggregated, the DB Sorter node is employed to set the sorting rules, arranging the data in a specific order based on predefined criteria. This organized dataset is then passed to the DB Reader node, which outputs the data for further analysis.

Solution Summary: To address this additional challenge, we fetch information on all Pokémon using GET requests and filter out the data to retain only Pokémon types. We then compare this data with the existing 'type' table, and update the table by performing the INSERT process for only the new data.

Solution Details:

We begin our solution by issuing GET requests to the Pokémon API (using the GET Request node) to fetch information on all Pokémon types. The responses from these requests are then extracted using the JSON Path node, which allows us to parse the JSON data and retrieve specific attributes, such as Pokémon types and their corresponding API links.

After extracting the necessary information, we proceed to the data transformation stage using the Ungroup node and the String Manipulation node. These nodes help in restructuring and cleaning the data to ensure it is in the desired format. As a result, we obtain a table containing all Pokémon types along with their respective API links.

Next, we compare this newly obtained data with the existing data in the 'type' table. To achieve this, we utilize the Reference Row Splitter node. This node helps in splitting the data into two sets: the existing data and the new data that needs to be inserted.

Once the data is split, we use the Column Renamer node to ensure that the column names are consistent with the existing 'type' table. This step is crucial for maintaining data integrity and avoiding any discrepancies during the insertion process.

Finally, we employ the DB Insert node to update the 'type' table by performing the INSERT process for only the new data. This ensures that the table is kept up-to-date with the latest Pokémon types without duplicating any existing entries.

By meticulously following these steps, we efficiently retrieve, filter, compare, and update Pokémon data, ensuring that the 'type' table is accurately maintained with only the new entries.