# GCP(Cloud Hands On Study)

[About Takayuki](https://taka-baya-work.com/about/)

## Agenda

### Time Schedule
| Time | To Do |
| -------- | -------- |
| 10:00 a.m | Preparing the conference room |
| 10:15 a.m | self-introduction |
| 10:30 a.m | Lecture about cloud |
| 12:00 a.m | Lunch & Break time |
| 1:00 p.m | Start Hunds On |
| Around 4 p.m | End Hands On |

### Artifact1

Create a page displaying Japan's imports and exports and the balance of payments for services.
https://learning-gcp-403802.ue.r.appspot.com/


【Cloud Design of Artifact】

### Artifact2
Please customize artifact 1 and improve it to make it better, such as more visualized graphs.

## About Using tool and programing language

### programing language
・Python
・HTML
・JavaScript (Chart.js)

### tool
・Google Cloud Pratform(GCP)
・gcloud CLI

---

## advance preparation

### advance preparation１：Registration to GCP

Please refer to the article in the link below to register with GCP (add a credit card).

【Japanese】
https://www.n-study.com/gcp/start-gcp/

【English】
https://cloud.google.com/docs/get-started?hl=en

### advance preparation2：install to Python

<span style="color:red">**When installing Python, please install it with version 3.10 (because the environment is unified & gcloud CLI only supports v3.10)**</span>.

#### Windows
●How to install the Python runtime environment on Windows
1. **To install Python on Windows, follow these steps**
Download the installer from the official website:
【Python official website】
https://www.python.org/ftp/python/3.10.11/python-3.10.11-amd64.exe
and download the installer for the 3.10 version of Python from the download page.

2. **execute installer:**
Click on the installer after you have downloaded it..

3. **add python path on your computer:**
On the first screen of the installer, check the option "Add Python X.Y to PATH" (where X.Y is the version number of the Python you downloaded). This will add Python to your system's PATH environment variable, allowing you to run Python directly from the command prompt.

3. **install python:**
Click "Install Now" to begin installation.

4. **confirm instalation status:**
When the installation is complete, you will see a "Setup was successful" or similar message.
The Python installation is now complete.

5. **Confirmation of Python operation:**
Open a command prompt and type `python --version` to verify that the Python version is displayed correctly.

6. **（optional）Installing additional packages:**
If necessary, use pip, the Python package manager, to install additional packages. For example, you can run `pip install numpy` to install NumPy, a numerical computation library.

You are now ready to use Python on Windows.

#### OS X
●There are several ways to install Python on a Mac, but here are the two main ones

There are have some apploach to install python on mac OSX but, I suggest two approaches now

【Installation when using the installer】
1. **Downloaded the installer from the offical website:**
https://www.python.org/ftp/python/3.10.11/python-3.10.11-macos11.pkg
and download the installer for the 3.10 version of Python from the download page.

2. **Double-click the downloaded installer to run it and follow the on-screen instructions to complete the installation.**

【Using Homebrew】

1. **Open a terminal and run the following command to install Python:**

brew install python3.10

Either way, once the installation is complete, you can open a terminal and run the command `python3 --version` to check the Python version. Now your Python installation is complete and you are ready to use Python on your Mac.

We also recommend using pyenv if you want to version Python on OSX. pyenv makes it easy to manage multiple Python versions and use different Python versions for different projects.

### advance preparation3：Install Cloud SDK tool
*gcloud CLI* is a tool (command) to control GCP cloud environment by commands from a PC terminal.

Basically, please refer to the following URL.

#### ●Install for Windows

1. Download CLI Installer
download the [Google Cloud CLI installer](https://dl.google.com/dl/cloudsdk/channels/rapid/GoogleCloudSDKInstaller.exe?hl=ja)
or
Alternatively, open the PowerShell Terminal and execute the following PowerShell command

(New-Object Net.WebClient).DownloadFile("https://dl.google.com/dl/cloudsdk/channels/rapid/GoogleCloudSDKInstaller.exe", "$env:Temp\GoogleCloudSDKInstaller.exe")

& $env:Temp\GoogleCloudSDKInstaller.exe

2. **Launch the installer and follow the on-screen instructions. The installer is signed by Google LLC.**
If you are using a screen reader, check the "Turn on screen reader mode" checkbox. If you configure gcloud with this option, a status tracker will be used instead of a Unicode spinner, and progress will be displayed as a percentage. It also flattens the table. For more information, please see the User Assistance Features guide.

The Google Cloud CLI requires Python. Supported versions are Python 3 (3.8-3.10). By default, the Windows version of the Google Cloud CLI comes bundled with Python 3.

After installing the CLI, run **gcloud CLI** and execute the following command

gcloud auth login

When executed, you will see the authentication of your Google account, so please log in with the account you have registered with GCP.

#### ●How to install on MacOS

1. **Make sure you are using a supported version of Python.**
To check the current Python version, run python3 -V or python -V. Supported versions are Python 3 (3.8-3.10).
For Google Cloud CLI release version 352.0.0 or later, the main installation script will install Python 3.10 of CPython on Intel-based Macs.
Otherwise, please visit Python.org's Python for macOS release to install the supported Python versions.
If you have more than one Python interpreter installed on your machine, set the CLOUDSDK_PYTHON environment variable in your shell to point to the path of the desired interpreter.
See gcloud topic startup for information on how to select and configure a Python interpreter.

2. **Download Installer**

| package | size | SHA256 | CheckSum |
| -------- | -------- | -------- | -------- |
|macOS 64 bit (x86_64) | [google-cloud-cli-452.0.1-darwin-x86_64.tar.gz](https://dl.google.com/dl/cloudsdk/channels/rapid/downloads/google-cloud-cli-452.0.1-darwin-x86_64.tar.gz?hl=ja) | 123.0 MB | 2afce77c2a4c98bf6a133018b71c92bf22ef63940867f1e919c8b4d0959e13c6 |
|macOS 64 bit (ARM64, Apple M1 silicon) | [google-cloud-cli-452.0.1-darwin-arm.tar.gz](https://dl.google.com/dl/cloudsdk/channels/rapid/downloads/google-cloud-cli-452.0.1-darwin-arm.tar.gz?hl=ja) | 120.1 MB | b57abce33c044ddd2608b2d4993d91b2148c823abb1bb4dfadb6034c1ab9343c |
| macOS 32 bit (x86) | [google-cloud-cli-452.0.1-darwin-x86.tar.gz](https://dl.google.com/dl/cloudsdk/channels/rapid/downloads/google-cloud-cli-452.0.1-darwin-x86.tar.gz?hl=ja) | 121.7 MB | b3b35a6bc0eea9e5f7ea1c2f0407c571a63457a31c2c31d55e326fe17eb3169d |

3. **Extract Installer**
Extract the archive to any location on the file system. We recommend using your home directory; on macOS, this can be accomplished by opening the downloaded .tar.gz archive file in any location.
To replace an existing installation, delete the existing google-cloud-sdk directory and extract the archive to the same location.

4. **Execute Installer**
Use the installation script to add the gcloud CLI tools to your PATH. You can also use command completion for shell and usage statistics collection to install Python 3.10.
Use this command to run the script (from the root of the folder you deployed in the previous step).

./google-cloud-sdk/install.sh

5. **To initialize the gcloud CLI, run gcloud init.**

After installing the CLI, run **gcloud CLI** and execute the following command

gcloud auth login

When you run it, you will see the authentication of your Google account, so please log in with the account you have registered with GCP.

**That's all for the advance preparation**, thank you for your time.

---

## Hands-on Assignment

### a. Download the program to be used in this Hands-On project

You can download the complete set of programs used in this project from the github link below.

[github link](https://github.com/taka-baya/learning_gcp_appengine)

or

command

git clone https://github.com/taka-baya/learning_gcp_appengine.git

●folder hierarchy

./app/
 |-main.py
 |-app.yaml
 |-templates
    |-chart.html

● ./app/main.py

import os

from flask import Flask, render_template, request
from google.cloud import bigquery

from google.cloud import bigquery
from google.oauth2 import service_account


#key_path = "..\\ssh\\credencial-key.json"
#credentials = service_account.Credentials.from_service_account_file(
#    key_path, scopes=["https://www.googleapis.com/auth/cloud-platform"],
#)
#bigquery_client = bigquery.Client(credentials=credentials, project=credentials.project_id,)

bigquery_client = bigquery.Client()
app = Flask(__name__)

# get request method
@app.route('/', methods=['GET'])
def diplay():

    # throw query to BigQuery
    query_job = bigquery_client.query(
        """
        SELECT
            *
        FROM 
            `your-databse-name`
        ORDER BY 
            day
        """
    )

    # execute query and get data
    df = query_job.to_dataframe()
    
    #print(type(df))
    # print(df.fillna(0))
    # df = df.fillna(0)
    
    labels = df["day"]
    datas = [df["Goods_and_services"], df["Goods_total"], df["Exports"], df["Imports"], df["Services"]]
    
    return render_template('chart.html', datas=datas, labels=labels)


if __name__ == '__main__':
    # use to 8080 port
    app.run(host='0.0.0.0', port=8080)

./app/app.yaml

runtime: python310
service: default

./app/requirements.txt

db-dtypes==1.1.1
Flask==3.0.0
google-cloud-bigquery==3.13.0
pandas==2.1.2
pyarrow==13.0.0

./app/templates/chart.html

<!DOCTYPE html>
<html lang="ja">
  <head>
    <meta charset="utf-8">
    <title>assignment</title>

    <script src="https://cdn.jsdelivr.net/npm/chart.js@4.4.0"></script>
  </head>
  <body>
    <canvas id="chart"></canvas>

    <script>
    window.chartColors = {
        red: 'rgb(255, 99, 132)',
        orange: 'rgb(255, 159, 64)',
        yellow: 'rgb(255, 205, 86)',
        green: 'rgb(75, 192, 192)',
        blue: 'rgb(54, 162, 235)',
        purple: 'rgb(153, 102, 255)',
        grey: 'rgb(201, 203, 207)'
    };

    var chartDatas = {
        type: 'bar',
        data: {
            labels: [
                {% for label in labels %}
                    "{{label}}",
                {% endfor %}
            ],
            datasets: [{
                label: '
International expenditures for imports, exports and services',
                type: 'bar',
                backgroundColor: window.chartColors.red,
                borderColor: window.chartColors.red,
                yAxisID: 'yaxis1',
                data: [
                    {% for data in datas[0] %}
                        {{data}},
                    {% endfor %}
                ],
                fill: false,
            }, {
                label: 'Total trade expenditure',
                type: 'bar',
                fill: false,
                backgroundColor: window.chartColors.green,
                borderColor: window.chartColors.green,
                yAxisID: 'yaxis1',
                data: [
                    {% for data in datas[1] %}
                        {{data}},
                    {% endfor %}
                ],
            }, {
                label: 'export',
                type: 'line',
                fill: false,
                backgroundColor: window.chartColors.orange,
                borderColor: window.chartColors.orange,
                yAxisID: 'yaxis1',
                data: [
                    {% for data in datas[2] %}
                        {{data}},
                    {% endfor %}
                ],
            }, {
                label: 'import',
                type: 'line',
                fill: false,
                backgroundColor: window.chartColors.grey,
                borderColor: window.chartColors.grey,
                yAxisID: 'yaxis1',
                data: [
                    {% for data in datas[3] %}
                        {{data}},
                    {% endfor %}
                ],
            },  {
                label: '
service business',
                type: 'line',
                fill: false,
                backgroundColor: window.chartColors.purple,
                borderColor: window.chartColors.purple,
                yAxisID: 'yaxis1',
                data: [
                    {% for data in datas[4] %}
                        {{data}},
                    {% endfor %}
                ],
            }]
        },
        options: {
            responsive: true,
            title: {
                display: true,
                text: 'finance of japan'
            },
            tooltips: {
                mode: 'index',
                intersect: false,
            },
            hover: {
                mode: 'nearest',
                intersect: true
            },
            scales: {
                x: {
                    display: true,
                    scaleLabel: {
                        display: true,
                        labelString: 'day'
                    }
                },
                yaxis1 : {
                    display: true,
                    position: 'left',
                    scaleLabel: {
                        display: true,
                        labelString: '￥'
                    }
                },
                yaxis2 : {
                    display: true,
                    position: 'right',
                    scaleLabel: {
                        display: true,
                        labelString: '￥'
                    }
                }
            }
        }
    };
    var mychart = document.getElementById("chart");
    var chart = new Chart(mychart, chartDatas);
    </script>
  </body>
</html>

### b. Add data for BigQuery

#### download the data

The data used for this project can be downloaded from the "6s-1-4 Balance of Payments Summary 【Monthly】" on the page linked below.
https://www.mof.go.jp/policy/international_policy/reference/balance_of_payments/bpnet.htm

Since the formatted version is already prepared, please download it by the following URL or command.

[URL to download formatted data](https://storage.googleapis.com/balance-of-payments/Sample_data.csv)

or

 curl -O https://storage.googleapis.com/balance-of-payments/Sample_data.csv

Make sure "Sample_data.csv" is downloaded to any folder.

#### Add data for BigQuery

**●About GUI**

As shown in the screen below. Select "BigQuery" from the tab on the left side and select "+Add".


After that, under "General Sources," select "Local Files.
Then you will see a screen to create a table as shown in the image below.


> Item Source: Please select the dataset you downloaded here and upload it...

> Destination: Select the target project, the item "Dataset" is a DB. Please enter any DB name and select which region to save it. The item "Table" is a table, so enter any table name.

> Schema: This item is for editing column names. Basically, the column name is automatically recognized, but if you want to edit it yourself, please set here.

**About CLI**

For CLI, use gcloud CLI to create a database in BigQuery from a local downloaded CSV file. Select the tab "bq" in the page of documentation at the following URL and refer to it to build it.

https://cloud.google.com/bigquery/docs/batch-loading-data?hl=ja#bq

Example）
・Create Table

bq load --skip_leading_rows=1 --source_format=CSV mydataset.mytable data.csv my_schema.json

Example Schema.json

[
  {"name": "day", "type": "date", "mode": "required"},
  {"name": "Goods_and_services", "type": "integer", "mode": "required"},
  {"name": "Goods_total", "type": "integer", "mode": "required"},
  {"name": "Exports", "type": "integer", "mode": "required"},
    {"name": "Imports", "type": "integer", "mode": "required"},
  {"name": "Services", "type": "integer", "mode": "required"}
]

After the table creation is completed, return to "BigQuery Studio" and execute the following SQL.

SELECT * FROM `learning-gcp-403802.Finance_data.balance_of_payments` LIMIT 1000

### c. Creating Credential Keys

Select "IAM and Administration" and then "Service Accounts."


Then select "Create Service Account" to create a credential key. You can choose any service account name you like.
However, please set the role as "BigQuery administrator" for access privileges.


After creating a new service account, the name of the newly created service account will appear in the service account list.
Select it and you will see a tab called Keys.

When you see the tab "Key",
> "Add new key" -> "Create new key" -> "Json" - "Create"

and select "Add new key" -> "Create new key" -> "Json" - "Create". Then you will see a file in Json format downloaded. **Keep it in a secure place (e.g. ~. /.ssh/ and so on)**


### d. ローカル端末での実行

**Please work on this work after you have finished building the credential key and DB with BigQuery.**

**step1.** First, open 'main.py' in the downloaded sample program folder.
**step2.** After opening, delete comment out "#" from line 10 and comment out "#" line 16 as shown below.
**step3.** Next, put the path to the credential key you downloaded in the variable key_path on line 10.
**step4.** Next, specify the database name you created with BigQuery in 'your-databse-name' on line 29.

import os

from flask import Flask, render_template, request
from google.cloud import bigquery

from google.cloud import bigquery
from google.oauth2 import service_account


key_path = "..\\ssh\\credencial-key.json"
credentials = service_account.Credentials.from_service_account_file(
    key_path, scopes=["https://www.googleapis.com/auth/cloud-platform"],
)
bigquery_client = bigquery.Client(credentials=credentials, project=credentials.project_id,)

#bigquery_client = bigquery.Client()
app = Flask(__name__)

# get request function
@app.route('/', methods=['GET'])
def diplay():

    #through to Bigquery
    query_job = bigquery_client.query(
        """
        SELECT
            *
        FROM 
            `your-databse-name`
        ORDER BY 
            day
        """
    )

    # Retrieve the results of the query execution into a data frame
    df = query_job.to_dataframe()
    
    #print(type(df))
    # print(df.fillna(0))
    # df = df.fillna(0)
    
    labels = df["day"]
    datas = [df["Goods_and_services"], df["Goods_total"], df["Exports"], df["Imports"], df["Services"]]
    
    return render_template('chart.html', datas=datas, labels=labels)


if __name__ == '__main__':
    # Use Cloud Shell's recommended port 8080 for local execution.
    app.run(host='0.0.0.0', port=8080)

**step5.** Save the updated main.py and execute the following command to the folder where main.py is located because it is a terminal or command.

python main.py

**step6.** Finally, check to see if a graphical chart is displayed at http://localhost:8080.

### e. deploy to AppEngine

For an overview of AppEngine, please refer to the following document.
https://cloud.google.com/appengine/docs/the-appengine-environments?hl=ja

Now, finally, rewrite the program for deployment with AppEngine.
After opening main.py, add the comment out "#" on line 10~ and uncomment out "#" on line 16 as shown below.

import os

from flask import Flask, render_template, request
from google.cloud import bigquery

from google.cloud import bigquery
from google.oauth2 import service_account

#key_path = "..\\ssh\\credencial-key.json"
#credentials = #service_account.Credentials.from_service_account_file(
#    key_path, scopes=["https://www.googleapis.com/auth/cloud-platform"],
#)
#bigquery_client = bigquery.Client(credentials=credentials, project=credentials.project_id,)

bigquery_client = bigquery.Client()
app = Flask(__name__)


# get request function
@app.route('/', methods=['GET'])
def diplay():

    #through to Bigquery
    query_job = bigquery_client.query(
        """
        SELECT
            *
        FROM 
            `your-databse-name`
        ORDER BY 
            day
        """
    )

    # Retrieve the results of the query execution into a data frame
    df = query_job.to_dataframe()
    
    #print(type(df))
    # print(df.fillna(0))
    # df = df.fillna(0)
    
    labels = df["day"]
    datas = [df["Goods_and_services"], df["Goods_total"], df["Exports"], df["Imports"], df["Services"]]
    
    return render_template('chart.html', datas=datas, labels=labels)


if __name__ == '__main__':
    # Use Cloud Shell's recommended port 8080 for local execution.
    app.run(host='0.0.0.0', port=8080)

Save your modifications to `main.py` when you are done.

Deploying to the cloud is easy.
Simply navigate to the folder where app.yaml is located in the terminal and execute the following command.

gcloud app deploy

#### ※About Python Runtime
If you want to more learn app.yaml, Please refer following documents.
https://cloud.google.com/appengine/docs/standard/python3/runtime?hl=ja

### f. customized development

Issue 1 : Please make the graph more readable.
Issue 2 : Can you make a pie chart of the ratio of exports to imports or something...