ラズパイの測定データをmetabaseで可視化しようとしたが諦めた話。

# はじめに
ラズパイの測定情報をbig queryに入れ、metabaseでグラフに可視化している書き込みがあったので、試してみました。しかし、データの取り込みをするための操作で、クレジットカードの登録が必要ということが分かったので、途中であきらめることにしました。試してみる際に、やったことをまとめてみます。

# イメージ
今回行うのは、ラズパイにBME280をつけて、湿度、温度、気圧のデータを取得し、Big Queryのデータテーブルに書き込みを行います。そのデータをグラフで確認するためにmetabaseで見れるようにします。そのためには、Google Could Platform内で、Big Query APIの有効化、サービスアカウントを作成し、Big Queryのアクセス権限、データテーブルの作成が必要になります。また、ラズパイやMetabaseがBig Queryへアクセスするためには、サービスアカウントで作成したxxx.jsonファイルを使います。ただ、今回は、Big Queryへのデータの取り込みをするために必要なクレジットカードの登録の手続きをしていません。
![スクリーンショット 2022-03-08 23.17.31.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/176158/48b0644f-a58e-e942-5733-4f6735575173.png)

# 環境
Python version:3.9.7
OS: windows: 10.0
Linux raspberrypi: 5.10.63+

# 1.GCPの設定
## 1.1 サービスアカウントの発行
Big QueryからMetabase接続を行うため、サービスアカウントを作成します。
1. IAMと管理 -> サービスアカウント -> サービスアカウントを作成をクリックします。
2. 適当にサービスアカウント名を入力し、作成して続行を押す。今回、big-query-userとしています。
![1.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/176158/e3e8243c-e2e1-96d5-e49c-adc1c0035241.png)
3. 下記2つのプロジェクトへのアクセス権限を与え、完了をクリックします。
   - Big Query データオーナー
   - Big Query ジョブユーザー
![2.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/176158/55f78e97-3c1e-ac61-8a16-20541942e9c6.png)
4. 作成したサービスアカウントの右側に操作という欄があり、そこの3つの点の部分をクリックして、「鍵を作成」をクリックします。ここで作成したjsonファイルは、後ほど使用します。これを使用することで、Big Queryへアクセスできるようになります。

## 1.2 Big Queryのデータセット作成
1. GCPのサイトにアクセスします。APIとサービス -> ライブラリで-> BigQuery APIを有効化します。
2. BigQuery -> SQL ワークスペースをクリックします。プロジェクト名の横にある点々の部分にカーソルを置くと、データセットを作成が出てきます。データセットID、データロケーションを設定し、データセットを作成をクリックします。

### 1.2.1 tabel作成
プロジェクトIDの横の点三つの部分をクリックすると、データセットの作成ができます。適当な名前を入力してください。私は、mydatasetとしました。作成後、データセットを選択し、右にあるクエリを新規作成をクリックし、下記のように入力してください。mydatasetの部分は、自分が作成したデータセット名に変更して下さい。これで、テーブルの作成ができます。
``` table作成
CREATE TABLE
mydataset.bme280
(
datetime timestamp
, Temperature FLOAT64
, Humidity FLOAT64
, Pressure FLOAT64
)
```

# 2. Metabaseのインストール
## 2.1 Javaの確認
1.下記コマンドをコマンドプロンプトに打ち込み、Javaが入っているか確認する。あれば、`java version “xx.x.xx”`と表示される。
```
java -version
```
## 2.2 Java、Metabaseのインストール
2. 表示されなかった場合、ダウンロードします。[こちら](https://www.metabase.com/docs/latest/operations-guide/running-the-metabase-jar-file.html)のサイトに飛び、「Install Java JRE」の「Eclipse Temurin」からダウンロードを行います。
3. 再度、[こちら](https://www.metabase.com/docs/latest/operations-guide/running-the-metabase-jar-file.html)のサイトに飛び、「Download Metabase」の「Metabase download page 」をクリックします。
4. ダウンロードしたファイルを特定の場所に置きます。コマンドプロンプトは、置いたディレクトリまで移動し、下記コマンド入力します。
```
// metabase.jarの置いたディレクトリへ移動
cd C:\xxxx\xx\metabase

// コマンドの実行
java -jar metabase.jar

```
## 2.3 ブラウザの起動(Metabase)
5. 走行のlogをみると、下記の黄色で塗りつぶしたメッセージが出てくるので、これを、ブラウザのURLに入力し、アクセスします。
```
// このリンクをブラウザで開く
http://localhost:3000/setup/
```
![キャプx1.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/176158/4199d923-7c35-9fe1-3634-2107a6354243.png)
6. データ追加するの部分まで適当に入力し、Big Queryを選びます。表示名は、何でもよく、Project IDは、Google Cloud Platformで作成したjsonファイルを選択します。そのあとは、適当に入力してください。

# 3.ラズパイでの操作
## 3.1google-cloud-bigqueryのインストール
1. 下記コマンドでライブラリをインストールしてください。
```
pip install --upgrade google-cloud-bigquery
```

## 3.2 pythonファイルの作成
ラズパイにbme280.pyとjsonファイルを置き、pythonを走行させます。ただ、クレジットカードの登録をしていないので、後半に示すようなエラーが出ます。
```bme280.py
from smbus2 import SMBus
import time
from google.cloud import bigquery
import os
import datetime
from pathlib import Path

credentials_json = './upload-test.json'
os.environ['GOOGLE_APPLICATION_CREDENTIALS'] = credentials_json

bus_number = 1
i2c_address = 0x76

bus = SMBus(bus_number)

digT = []
digP = []
digH = []

t_fine = 0.0


def writeReg(reg_address, data):
    bus.write_byte_data(i2c_address,reg_address,data)

def get_calib_param():
    calib = []

    for i in range (0x88,0x88+24):
        calib.append(bus.read_byte_data(i2c_address,i))
        calib.append(bus.read_byte_data(i2c_address,0xA1))
    for i in range (0xE1,0xE1+7):
        calib.append(bus.read_byte_data(i2c_address,i))

    digT.append((calib[1] << 8) | calib[0])
    digT.append((calib[3] << 8) | calib[2])
    digT.append((calib[5] << 8) | calib[4])
    digP.append((calib[7] << 8) | calib[6])
    digP.append((calib[9] << 8) | calib[8])
    digP.append((calib[11]<< 8) | calib[10])
    digP.append((calib[13]<< 8) | calib[12])
    digP.append((calib[15]<< 8) | calib[14])
    digP.append((calib[17]<< 8) | calib[16])
    digP.append((calib[19]<< 8) | calib[18])
    digP.append((calib[21]<< 8) | calib[20])
    digP.append((calib[23]<< 8) | calib[22])
    digH.append( calib[24] )
    digH.append((calib[26]<< 8) | calib[25])
    digH.append( calib[27] )
    digH.append((calib[28]<< 4) | (0x0F & calib[29]))
    digH.append((calib[30]<< 4) | ((calib[29] >> 4) & 0x0F))
    digH.append( calib[31] )

    for i in range(1,2):
        if digT[i] & 0x8000:
            digT[i] = (-digT[i] ^ 0xFFFF) + 1

    for i in range(1,8):
        if digP[i] & 0x8000:
            digP[i] = (-digP[i] ^ 0xFFFF) + 1

    for i in range(0,6):
        if digH[i] & 0x8000:
            digH[i] = (-digH[i] ^ 0xFFFF) + 1

def readData():
    data = []
    for i in range (0xF7, 0xF7+8):
        data.append(bus.read_byte_data(i2c_address,i))
    pres_raw = (data[0] << 12) | (data[1] << 4) | (data[2] >> 4)
    temp_raw = (data[3] << 12) | (data[4] << 4) | (data[5] >> 4)
    hum_raw = (data[6] << 8) | data[7]

    sensor_data = []
    sensor_data.append(compensate_T(temp_raw))
    sensor_data.append(compensate_P(pres_raw))
    sensor_data.append(compensate_H(hum_raw))

    return sensor_data

def compensate_P(adc_P):
    global t_fine
    pressure = 0.0

    v1 = (t_fine / 2.0) - 64000.0
    v2 = (((v1 / 4.0) * (v1 / 4.0)) / 2048) * digP[5]
    v2 = v2 + ((v1 * digP[4]) * 2.0)
    v2 = (v2 / 4.0) + (digP[3] * 65536.0)
    v1 = (((digP[2] * (((v1 / 4.0) * (v1 / 4.0)) / 8192)) / 8) + ((digP[1] * v1) / 2.0)) / 262144
    v1 = ((32768 + v1) * digP[0]) / 32768

    if v1 == 0:
        return 0
    pressure = ((1048576 - adc_P) - (v2 / 4096)) * 3125
    if pressure < 0x80000000:
        pressure = (pressure * 2.0) / v1
    else:
        pressure = (pressure / v1) * 2
    v1 = (digP[8] * (((pressure / 8.0) * (pressure / 8.0)) / 8192.0)) / 4096
    v2 = ((pressure / 4.0) * digP[7]) / 8192.0
    pressure = pressure + ((v1 + v2 + digP[6]) / 16.0)
    pressure = pressure/100
    return pressure

def compensate_T(adc_T):
    global t_fine
    v1 = (adc_T / 16384.0 - digT[0] / 1024.0) * digT[1]
    v2 = (adc_T / 131072.0 - digT[0] / 8192.0) * (adc_T / 131072.0 - digT[0] / 8192.0) * digT[2]
    t_fine = v1 + v2
    temperature = t_fine / 5120.0
    return temperature

def compensate_H(adc_H):
    global t_fine
    var_h = t_fine - 76800.0
    if var_h != 0:
        var_h = (adc_H - (digH[3] * 64.0 + digH[4]/16384.0 * var_h)) * (digH[1] / 65536.0 * (1.0 + digH[5] / 67108864.0 * var_h * (1.0 + digH[2] / 67108864.0 * var_h)))
    else:
        return 0
    var_h = var_h * (1.0 - digH[0] * var_h / 524288.0)
    if var_h > 100.0:
        var_h = 100.0
    elif var_h < 0.0:
        var_h = 0.0
    hum = var_h
    return hum

def setup():
    osrs_t = 1 #Temperature oversampling x 1
    osrs_p = 1 #Pressure oversampling x 1
    osrs_h = 1 #Humidity oversampling x 1
    mode = 3 #Normal mode
    t_sb = 5 #Tstandby 1000ms
    filter = 0 #Filter off
    spi3w_en = 0 #3-wire SPI Disable

    ctrl_meas_reg = (osrs_t << 5) | (osrs_p << 2) | mode
    config_reg = (t_sb << 5) | (filter << 2) | spi3w_en
    ctrl_hum_reg = osrs_h

    writeReg(0xF2,ctrl_hum_reg)
    writeReg(0xF4,ctrl_meas_reg)
    writeReg(0xF5,config_reg)

def insert_table(data):
    ## Set Project ID
    project = "upload-test-342900"
    ## Set Dataset
    dataset = "mydataset"
    ## Set Table
    table = "bme280"

    bigquery_client = bigquery.Client()
    query = "INSERT INTO `{0}.{1}.{2}` values('{3}',{4},{5},{6})".format(project, dataset, table,datetime.datetime.now(),data[0],data[1],data[2])
    rows = bigquery_client.query(query).result()

setup()
get_calib_param()


if __name__ == '__main__':
    try:
        data = readData()
        insert_table(data)
    except KeyboardInterrupt:
        pass

```

今回作成したプログラムを走行させると、下記のようなエラーが出ました。このエラーは、クレジットカードの登録ができていないときに出るそうです。[リンク](https://www.monotalk.xyz/blog/python-%E3%81%A6-google-cloud-storage-%E3%81%A8bigquery-%E3%82%92%E4%BD%BF%E3%81%86%E9%81%8E%E7%A8%8B%E3%81%A6%E3%81%AE%E3%82%A8%E3%83%A9%E3%83%BC/)を参照してください。
```
google.api_core.exceptions.Forbidden: 403 Billing has not been enabled for this project. Enable billing at https://console.cloud.google.com/billing. DML queries are not allowed in the free tier. Set up a billing account to remove this restriction.
```

# 語句
仕事でデータベースを扱うことがないので、知らない言葉が多いため、用語について調べてみました。
[Metabase](https://openstandia.jp/oss_info/metabase/)
>　Metabase（メタベース）は、Metabaseプロジェクトによって開発されているオープンソースのデータ可視化ツール（BIツール）です。当初Metabaseは、Uberの共同創業者Garrett Campが2013年に設立したスタートアップスタジオ、Expaが開発した内製ツールでした。
> Metabaseは外部のデータソース（BigQurery, Google Analytics, MongoDB, MySQL, etc...）を参照してデータを分析します。Metabase自身には分析対象となるデータを保存する必要はなく、この外部データソースを元にして分析に必要な情報やキーワードをMetabase上で問い合わせることで、チャートやグラフなどによりデータが可視化されます。

[BigQuery](https://cloud-ace.jp/column/detail174/)
> BigQuery は Google Cloud にて提供されているプロダクトで、ビッグデータを超高速で解析することができるサービスです。

# おわりに
今回、Big Query,Metabaseを操作してみて、データベース周りを理解してみたくなりました。時間があるときに調べてみるつもりです。

# link
今回、参照させていただいたリンク集です。
[Google スプレッドシートで管理しているデータを BigQuery から参照して SQL で集計してみた。](https://dev.classmethod.jp/articles/google-spreadsheet-sql-from-bigquery/#toc-3)
[MetabaseからGoogle BigQueryのデータを可視化する。](https://growplants.jp/frombigquerytometabase/)
[ラズパイで温度・湿度・気圧センサー（HiLetgo BME280）からpython3で値を取得し、 Google BigQueryに記録する。（後編）](https://growplants.jp/bme280-2/)