Pythonローカル環境JupyterとGCPのBigqueryデータ連携と実行

ローカルPCからBigqueryのアップロードとダウンロード
※当サイトはアフェリエイト広告を利用しています

ローカルPCとGoogle Cloud Platform(GCP)の両環境データをシームレス連携して自動化を促進したいと困っていませんか?

環境が影響し、手作業が間に入ると自動化のメリットが軽減されるので可能ならば一気通貫で自動化したいと私は思っています。ボタン押すだけやbat処理も楽です。

私自身はローカルPCにPython環境を構築しておりjupyterlabからpythonコマンドで操作しているので、GCPをjupyterlabから操作する方法についてご紹介していきたと思います。

現在はJupyterlabとGCPのBigqueryデータの連携や更新を一気通貫で行うことができたことで快適に自動化できておりますのでこの記事が皆さまのお役に立てれば幸いです。

こんな人のお役に立てます
  • ローカルPCからjupyterを用いてダウンロードする方法
  • ローカルPCからjupyterを用いてアップロードする方法

今回の検証環境はWindows、JupyterLab、サービスアカウント(GCP)を利用して行っています。

目次

連携するための初期設定

  1. サービスアカウント(json)を取得
  2. 上記のサービスアカウントをローカルPCの任意の場所に保存
  3. 環境変数に上記を登録
  4. PC再起動
  5. 認証プログラムを実行 ※下記にコードを掲載
def implicit():
    from google.cloud import storage

    # If you don't specify credentials when constructing the client, the
    # client library will look for credentials in the environment.
    storage_client = storage.Client()

    # Make an authenticated API request
    buckets = list(storage_client.list_buckets())
    print(buckets)

def implicit()

これで完了です。グーグル公式ページにも記載がありましたので参考にリンクをはっておきます。

BigqueryからローカルJupyterにダウンロードする3つの方法

pandas.read_gbqを利用

PandasにはGCPと連携できるコマンドが用意されていますのでpandas_gbqをpipでインストールします。

pip install pandas-gbq 

pandas_gbqを利用したbigqueryからデータをダウンロードするサンプルコードは下記です。

import pandas

#SQL文
    sql = """
        SELECT name
        FROM `bigquery-public-data.usa_names.usa_1910_current`
        WHERE state = 'TX'
        LIMIT 100
    """
#Bigqueryからダウンロードする関数
def bigquery_dl(sql):

    df = pandas.read_gbq(sql, dialect='standard')

    project_id = 'GCPのプロジェクトIDを入れる'

    df = pandas.read_gbq(sql, project_id=project_id, dialect='standard')

    return df

上記コマンド実行後にグーグル認証がはいるため、初回のみパスワードを聞かれます。jupyterlabのセル画面下にグーグル認証するURLと四角い枠がでてきますのでURLクリック後にパスコードをコピペし、jupyterlabの四角い枠に貼り付けて実行します。そうすることでBigqueryからjupyterlab環境にデータをダウンロードすることができるようになります。

pandasを利用している人には手軽にGCPとの連携ができますが、Pandasの仕組みを通すために処理時間が遅いというデメリットがあります。

APIを利用

BigqueryとローカルPCを連携するAPIが用意されていますので実施方法をご紹介します。下記コマンドでbigqueryをインストールします。

pip install google-cloud-bigquery

インストール完了後、下記コードを実行するとデータがダウンロードできます。先程のpandas_gbqと比較すると処理スピードがとても速いことがわかります。

from google.cloud import bigquery


#SQL文
sql = """
    SELECT name
    FROM `bigquery-public-data.usa_names.usa_1910_current`
    WHERE state = 'TX'
    LIMIT 100
"""
#Bigqueryからダウンロードする関数
def bigquery_dl_2(sql):

    client = bigquery.Client()

    df = client.query(sql).to_dataframe()

    project_id = 'GCPのプロジェクトIDを入れる'
    df = client.query(sql, project=project_id).to_dataframe()

    return df

jupyterのマジックコマンドを利用

JupyterにはBigqueryと連携するためのマジックコマンドが用意されています。

%load_ext google.cloud.bigquery

上記コマンド実行後に違うセルで「%%bigquery 好きな変数名」の後にSQL文を記載して実行するとクエリ結果が変数に入ります。

%%bigquery df
SELECT
  country_code,
  country_name,
  COUNT(DISTINCT region_code) AS num_regions
FROM
  `bigquery-public-data.google_trends.international_top_terms`
WHERE
  refresh_date = DATE_SUB(CURRENT_DATE, INTERVAL 1 DAY)
GROUP BY
  country_code,
  country_name
ORDER BY
  num_regions DESC;

こちらもAPI同様スピードは速いので私はこちらをメインで利用しています。

ローカルJupyterlabからBigqueryにアップロードする2つの方法

Bigquery上にアップロード先の既存テーブルがあると仮定して例を記載します。

pandas.to_gbqを利用

既存データがあった場合に上書きするサンプルコードです。※if_exists=”replace”が上書きの箇所

import pandas

df = pandas.DataFrame(
    {
        "my_string": ["a", "b", "c"],
        "my_int64": [1, 2, 3],
        "my_float64": [4.0, 5.0, 6.0],
        "my_bool1": [True, False, True],
        "my_bool2": [False, True, False],
        "my_dates": pandas.date_range("now", periods=3),
    }
)

df.to_gbq([project_id].[テーブル名],if_exists="replace")

APIを利用

DataframeをBigqueryのテーブルにアップロードします。

from google.cloud import bigquery

bq_client = bigquery.Client()

bq_client.insert_rows_from_dataframe(bq_client.get_table([project_id].[テーブル名]),df)

今までのサンプルコードをコピペして実行すれば結果が思っている結果がかえってきたと思いますので活用してくださいね。

よかったらシェアしてね!
  • URLをコピーしました!
  • URLをコピーしました!

コメント

コメントする

目次