Hello PubSub:🖐 Dear BigQuery👨‍✈️, Sir Looker🎂

In this article, I will briefly talk about Google PubSub and BigQuery. We’ll end with Looker Studio in the next section.

General İmage

About Application

In this project, we will get the historical prices of Apple company in the stock market from 2018 to the present via api, transfer BigQuery with Google Pubsub and visualize it with Looker.

Hello PubSub:🖐

Google PubSub is a messaging service provided by Google Cloud. It allows you to send and receive messages between independent applications or microservices. PubSub provides a scalable and reliable way to exchange data between different components of your application or different applications entirely.Well, let’s examine how this messaging structure is created and then use it with the project.Publish consists of Topic and Subcriber. Publish data source , topic is a carrier channel , Subscribe as a recipient .

PubSub basically has two features, one is Topic and one is Schemas. Schemas, the message structure to be transported is determined, carries the messages in Auro format, in Topics it carries the messages depending on this schema.

In order to create a schema in Google Cloud, log in to Google cloud Console, respectively, Pubsub opens, service api is enabled, and PuSub schema on the left is selected.Values from schema api, coln names and types must be specified in euro format.

Part of the schema used in our project:

PubSub Schema

It’s time to create a Topic that will take the messages in Python and carry the bigquery. When you create a new topic from the Pubsub/Topic menu, the following topic menu opens, the critical point here is, since we will use the schema we created above, we need to choose the name of the schema we created from the next menu with Use A schema selected, we select the format Json and create it at the bottom.

Now we have created a topic, we will take the data over the api with the publishing Python and send it to the topic in the cloud.

You need to add your json key file of your account under Google AIM to your project.

from google.cloud import pubsub_v1
import requests
import json
import pandas as pd
import os

#Get data İngest
url='https://financialmodelingprep.com/api/v3/historical-price-full/AAPL?apikey=d8ff7cb62ac0e084cb1cc9c5aa27357a'

response = requests.get(url).json()
df = pd.json_normalize(response, record_path=['historical'])

#Googel AIM creadtinal Json file
os.environ['GOOGLE_APPLICATION_CREDENTIALS'] = 'nth-autumn-381608-2b71f7276aa6.json'
def publish_message(project_id, topic_name, message):
    # Create PubSub Publish
    publisher = pubsub_v1.PublisherClient()
    
    # Mesaj verisini JSON formatına dönüştür
    message_data = json.dumps(message).encode('utf-8')
    
    # Send Pub/Sub'--
    topic_path = publisher.topic_path(project_id, topic_name)
    future = publisher.publish(topic_path, data=message_data)
    future.result()
    
    print('Message successfully sent to Pub/Sub')


# Pub/sub ProjectId
project_id = 'nth-autumn-381608'

# PubSub Topic Name
topic_name = 'finance-v1'

count=0

for _, row in df.iterrows():
    to_json = row.to_dict()
    message=to_json
    count +=1
    print(count)
    publish_message(project_id, topic_name, message)

In order to check that these messages are received by PubSub, it is enough to enter the topic we created under Subscriptions after selecting the subscription and pull it from the message section. The following data will come.

Now we can transfer the incoming data to BigQuery. To transfer, we need to select BiqQuery from the export option below under Topic -> Subscriptions and continue.

Export BigQuery

After selecting Continue, an ID name is given from the menu below, the project is selected, the BigQuery dataset name to be sent and the table name are entered. These fields must be prepared beforehand. I’ll detail it in the BigQuery section.

BigQuery Topic

Dear BigQuery : 👨‍✈️

I have transferred BigQuery in my detailed Data Ware house article, so I will talk about its use in the project without going into details here.

First we need to create a dataset, we create a dataset named Finance.

Create Dataset

You can set the region settings of the created table here, I leave it as default, but when you choose Muti Region, you can create datasets in different regions, this service provided by the Cloud infrastructure, for example, if this dataset will only be used by a certain user of your company in Europe, you can choose the closest Google Datacenter in the European region and it will return to you in the future as performance.

After the Dataset is created, we create our table with the create table from the 3 points next to the Dataset below.

Create Table

Important points while creating the table If you have a sample JsonL file for the data you have received, if you show this json file from the Upload option, the table will automatically create the schma itself. This is the method I use, but if you don’t have an example or if there are files formatted like Avro it supports, you can show it or you can create it manually under the Schema tab.

Create Table Schema

You can see the schema detail by selecting the Table you created from the left side.

Table Scheama

You can see the data from the Preview Tab.

Preview Data

Now that our data is in BigQuery, you can query or create other datasets. Since our purpose here is to visualize our data with Looker, we provide Looker transfer from the Export menu.

Export Looker Studio

Sir Looker🎂

Looker is a business intelligence and data analytics platform that allows organizations to explore, analyze, and share real-time business analytics. With Looker, you can connect to your data sources, create dashboards and reports, and collaborate with other users in your organization.

You can choose the type of chart you will create the image from the Add Chart. Since it is the date and price, I have shown which percentage change is the most with pie chart percentiles to show the time series and change percentages.

Select Chart

After the desired graphic selection is made, you can select the values and constraints or filters to be displayed, select it under the Setup menu,
You can change the shape of the graphic from the Style menu, for example, to a bar or a line.

Select Value and Style

You can save the image you have made, send an e-mail in the desired time period, edit the abilities of the person you send the e-mail to, and share the weekly updated version.

I hope you have a pleasant reading in the project, where we obtained and visualized the end-to-end data. Stay healthy and see you in the next article