Purpose of the Article: To analyze the value of the currency in every country and query the same. we also established connections between API and PyCharm and also between PyCharm and SNOWFLAKE
Intended Audience: Those benefiting out of the article.
Tools and Technology: Pycharm, Snowflake
Keywords: Pycharm, Python, Snowflake, Database, Table, Currency, API, Transformation, Integrated Development Environment, API REQUEST, Pandas, Libraries
Main Objective of the Blog:
The main objective of the blog is to read data from API (which gets updated daily) using the python language and upload that filtered data to the snowflake database.
What is an API?
Application Program Interface (API) contains all the information one wants to access when looking for a certain door. Here we are using an open public API that anyone can access.
When you request data to an API it responds with the required information.
The Tool we use:
To filter, transform, and extract data from API, we use the PyCharm tool. It is an Integrated Development Environment (IDE) used in computer programming, mainly for Python.
We use snowflake as a target here. Snowflake is a data warehousing system with an automatic scaling process to reduce cost and increase performance.
Calling API Data Using PYTHON:
To call API data using Python, first, we need to access that data using python tools. In Python, the most used library for raising requests and calling API data is the request library. We need to install the request library in the Python console using the terminal to access the API data.
Calling API Data Using GET Request
API data can be classified in many ways; the most used one is a GET request. When we make a request, the reply from the API comes with a response code that tells us whether our request was successful or not. Response codes trigger us if something goes wrong. To make a ‘GET’ request, we’ll use the requests.get() function.
SOURCE – Currency Converter API
LANGUAGE USED – Python
TOOL USED – PyCharm
TARGET – SNOWFLAKE
What does API contain?
Our API Contains currency values of every country, which are updated daily according to the market value.
We are organizing the above data systematically by giving base currency and from currency
by applying some transformation, we can extract data from historical dates.
Applying Transformation to the above Data
We use Python code to apply transformations to the above data, which gives us the exact filtered data we need. Using this code, we get the currency values of historic and current dates.
- To apply transformations to the above data, first, we need to call API using the python code.
- We need to set up a connection with the database where we are willing to dump the filtered data. Here we are using snowflake as our database.
- In python, we use three codes to filter the exact data from API.
- Which deals with the current day; using that front-end code, we can feed the current date and data to the snowflake.
- At first, by using Python, we arrange the data in data frames.
- Data Frames -the data are aligned in a user-readable format.
- After that, by using a module known as write_pandas, we transfer those data frames to the snowflake database.
- Here without using external stages, we are directly integrating PyCharm with Snowflake and transferring data into it.
- Back-end code works the same as front-end code, but the only difference is it is used to load historical date data to Snowflake.
- Herein, we enter base currency and currency codes as well as a range of dates.
- The range of dates can be any, and we will get the data from those dates, whether historical or current.
Data in Snowflake
After the successful integration of PyCharm with Snowflake, we can see the above image when the data is inserted successfully.
After running the code in PyCharm, we can see the extracted, transformed, and final data in snowflake. Where we perform queries depends upon the data we need.
This is how you can extract data from a public API. Apply some transformations using coding languages you know and feed the transformed data to the required database.
Here is how we have used Python as a coding language and Snowflake as the database.
Note – While working with Snowflake, check the country code because you may face some delays when dealing with ad hoc data.