データ分析
2023/03/24
上野 桃香

PythonでGoogleドライブの大きなファイルを簡単にBigQueryにアップロードする方法

logo_Python

PythonでGoogleドライブの大きなファイルを簡単にBigQueryにアップロードする方法をまとめました。

はじめに

今回はGoogleドライブからPythonを使ってBigQueryにデータをアップデートする方法をご紹介します。

PythonでGoogleドライブからデータをダウンロードしたい方、PythonのDataFrameをBigQueryにアップロードしたい方は是非参考にしてください。

<全体の流れ>

①GoogleドライブとPythonを接続

②Googleドライブからデータをダウンロード

③BigqueryとPythonを接続

④データをBigqueryにアップロード

なぜわざわざPythonを使うのか

ここでなぜわざわざPythonを使うのか疑問に思った方もいると思います。それはGoogleドライブでファイルを開けなかったからです。そして他のアプリでも表示不可能でした。

もし普通にGoogleドライブでファイルを開けるならばわざわざPythonを経由する必要はあまりないと思います。(なにかPythonで加工なりしたいなら別ですが)

Googleドライブから直接開けた場合はBigQueryの「データを追加」→「その他のソース」→「Googleドライブ」を選択し、URLを挿入しデータを直接アップロードできます。



また、他のアプリで開けた場合にはローカルファイルに保存してアップロードなどBigQuery上でやり方はあるでしょう。

基本的にこちらの方法の方が個人的には時間効率がいいと思います。

しかし、すべてのアプリでファイルを開けない場合にはURLを取得できないためこのような方法でアップロードできません。



なのでPythonを使ってBigQueryにデータをアップロードすることをお勧めします。

PythonからGoogleドライブデータをアップロード_1_データを開いてみる

今回の記事では例としてサイズの大きいsample.csvというcsvファイルで説明したいと思います。sample.csvはGoogleドライブのMyDriveにあります。

では、さっそくサイズの大きいsample.csvファイルを開いてみようとおもいます。

Googleドライブからファイルを開こうとすると写真のようなエラーがでます。サイズが大きすぎるためほかのアプリを選択してくださいと書いていますね。



その指示通りほかのアプリで開こうとしたもののGoogleスプレッドシートは永遠に写真のようにグルグル回る、Excelもエラーが起こるなどして私が知りうる他のアプリは全滅でした。



ちなみにsample.csvのファイル容量は1.1GBでした。これは大きいですね。

PythonからGoogleドライブデータをアップロード_2_PythonからGoogleドライブ内データを取得

sample.csvファイルが通常のようにBigQueryにアップロードできないことを確認しました。それではPythonでGoogleドライブデータを取得しましょう。今回はGoogleColaboratoryを使います。

以下のコードを実行することでGoogleドライブと接続され、データをダウンロードできます。このときにカラムをBigQuery用に英語表記などにして直しておくといいでしょう。


#Googleドライブからデータを読み込む
from google.colab import drive
import pandas as pd
import pandas_gbq

#googleドライブへのアカウント認証
drive.mount('/content/drive')

#googleドライブからデータを読み込む
df = pd.read_csv('/content/drive/MyDrive/sample.csv',encoding='cp932')

#カラム名をBigquery用に直す
df.columns = ["X1", "X2", "X3", "X4", "X5", "X6", "X7", "X8", "X9", "X10", "X11", ・・・]


これを実行したときにGoogleドライブのアクセス許可を求められると思いますが、そのままGoogleドライブに接続を押しましょう。



encodingにはutf-8など適切な文字コードを入れてください。

PythonからGoogleドライブデータをアップロード_3_PythonとBigQueryを接続_Googleアカウント認証

次にBigQueryとPythonを接続します。方法は主に二つあります。

<BigqueryとPythonの接続方法>

GoogleAccountで認証する方法

サービスアカウントキーを使って認証する方法

一つ目のGoogleAccountで認証するには以下のコードを実行します。


#グーグルアカウント認証でgooglecolabとBQを繋ぐ(クエリ実行&データをBQへ)
from google.colab import auth
from google.cloud import bigquery

#認証
auth.authenticate_user()


このコードは、Google ColabとBigQueryを接続し、Googleアカウントで認証するためのコードです。

まず、Google Colabからauthをインポートしています。これは、Googleアカウントで認証するためのライブラリです。

次に、Google Cloudのbigqueryライブラリからbigqueryをインポートしています。そして、auth.authenticate_user()を呼び出して、Googleアカウントで認証を行っています。これにより、Googleアカウントで認証された状態でBigQueryにアクセスすることができるようになります。

この後に、BigQueryを使ってクエリを実行したり、データをBigQueryにアップロードするためのコードを書くことができます。

実行すればColaboratoryがGoogleアカウントへアクセスし、Google Cloudへデータの参照や編集を行うことの許可を求められます。

許可にして次に進みましょう。

PythonからGoogleドライブデータをアップロード_3_PythonとBigQueryを接続_サービスアカウント認証

二つ目のサービスアカウントキーで認証するには以下のコードを実行します。

前準備としてサービスアカウントキーファイルをGooglecolaboratoryのファイルにアップロードしておきましょう。

サービスアカウントキーはGCPの「IAMと管理」→「サービスアカウント」→「サービスアカウントを作成」を行いサービスアカウントキーのダウンロードをすればサービスアカウントキーのファイルを取得できます。


#必要なライブラリをインポート
import pandas_gbq as pdg
from google.oauth2 import service_account

#データを格納するプロジェクトIDとテーブルIDを入力
project_id = "projectID"
table_id = "dataset.table"
data_id = project_id + "." + table_id

#サービスアカウントキーのファイルパスを入力
service_account_key_path='Key_path'

#-------pythonとBigQueryをつなぐための認証----------
#credentials = ... # From google-auth or pydata-google-auth library.
credentials = service_account.Credentials.from_service_account_file(
#サービスアカウントキー(jsonファイルパスを指定)
service_account_key_path,
)

# Update the in-memory credentials cache (added in pandas-gbq 0.7.0).
pdg.context.credentials = credentials
pdg.context.project = project_id


このコードはGoogle Cloud PlatformのBigQueryに接続し、サービスアカウントで認証するためのコードです。

まず、必要なライブラリをインポートしています。pandas-gbqは、PythonとBigQueryの間でデータをやりとりするためのライブラリで、Google Cloud Platformで動作するように設計されています。また、google-authライブラリからservice_accountをインポートしています。

次に、データを格納するプロジェクトIDとテーブルIDを入力し、data_idに格納しています。

そして、サービスアカウントキーのファイルパスを入力しています。このキーは、BigQueryにアクセスするための認証情報を持っています。

最後に、credentialsにサービスアカウントキーから認証情報を取得し、pdg.context.credentialsとpdg.context.projectにそれぞれ格納しています。これにより、pandas-gbqを使ってBigQueryにアクセスする準備が整いました。

PythonからGoogleドライブデータをアップロード_3_PythonからBigQueryにデータをアップロード

最後にGoogleドライブからダウンロードしたデータをBigqueryへアップロードします。

①Googleアカウントで認証する場合


#Googleドライブからデータを読み込む
from google.colab import drive
import pandas as pd
import pandas_gbq as pdg

#googleドライブへのアカウント認証
drive.mount('/content/drive')

#googleドライブからデータを読み込む
df = pd.read_csv('/content/drive/MyDrive/sample.csv',encoding='cp932')

#カラム名をBigquery用に直す
df.columns = ["X1", "X2", "X3", "X4", "X5", "X6", "X7", "X8", "X9", "X10", "X11", "X12", "X14", "X15", "X16", "X17", "X18", "X19", "X20", "X21", "X22", "X23", "DSales_Cumulative_Attribute_1_person", "DSales_Cumulative_Attribute_2", "DSales_cumulative_attribute_2_people", "DSales_Cumulative_Attribute_3", "DSales_Cumulative_Attributes_3_people", "DSales_Cumulative_Attribute_4", "DSales_Cumulative_Attribute_4_people", "DSales_Cumulative_Purchase_CD", "DSales_Cumulative_Purchase_destination_name", "DSales_Cumulative_Payment_CD", "DSales_Cumulative_Payment_destination_name", "DSales_Cumulative_Special_CD", "DSales_Cumulative_Time_zone_classification", "DSales_Cumulative_Event_classification", "DSales_Cumulative_Event_classification_name", "DSales_Cumulative_Visit", "DSales_Cumulative_Number_of_customers", "DSales_Cumulative_Competition_opponent", "DSales_Cumulative_Competition_opponent_name", "DSales_Cumulative_Weather", "DSales_Cumulative_Weather_name", "DSales_Cumulative_Temperature", "DSales_Cumulative_Number_of_sales", "DSales_Cumulative_Sales_including_tax", "DSales_Cumulative_During_sales", "DSales_Cumulative_Sales_amount", "DSales_Cumulative_Actual_sales_amount", "DSales_Cumulative_Sales", "DSales_Cumulative_Sales_including_tax_lower_price", "DSales_Cumulative_Sales_evaluation_lower_price", "DSales_Cumulative_fc_division"]

#グーグルアカウント認証でgooglecolabとBQを繋ぐ
from google.colab import auth
from google.cloud import bigquery

#認証
auth.authenticate_user()

project_id = "projectID"
table_id = "dataset.table"

#結合したデータをBigQueryにアップロード
df.to_gbq(destination_table=table_id,project_id=project_id,if_exists='replace')


pandas_gbqのto_gpq関数を使用してデータをBigQueryにアップロードします。

・destination_table:パラメータ:アップロードするテーブルのIDを指定。

・project_id:パラメーター:BigQueryで使用するプロジェクトのIDを指定。

・if_existsパラメータ:テーブルがすでに存在する場合にどのように処理するかを指定。                                                                           replaceを指定すると、既存のテーブルを置き換えることができる。

②サービスアカウントで認証する場合

#Googleドライブからデータを読み込む
from google.colab import drive
import pandas as pd
import pandas_gbq as pdg
from google.oauth2 import service_account
from google.cloud import bigquery

#googleドライブへのアカウント認証
drive.mount('/content/drive')

#googleドライブからデータを読み込む
df = pd.read_csv('/content/drive/MyDrive/sample.csv',encoding='cp932')

#カラム名をBigquery用に直す
df.columns = ["X1", "X2", "X3", "X4", "X5", "X6", "X7", "X8", "X9", "X10", "X11", "X12", "X14", "X15", "X16", "X17", "X18", "X19", "X20", "X21", "X22", "X23",・・・]

#GoogleドライブからダウンロードしたデータをBigQueryにアップロード
def GoogleDrivedata_to_bigquery(df):
#データを格納するプロジェクトIDとテーブルIDを入力
project_id = "projectID"
table_id = "dataset.table"
data_id = project_id + "." + table_id

#サービスアカウントキーのファイルパスを入力
service_account_key_path='Key_path'

#-------pythonとBigQueryをつなぐための認証----------
#credentials = ... # From google-auth or pydata-google-auth library.
credentials = service_account.Credentials.from_service_account_file(
#サービスアカウントキー(jsonファイルパスを指定)
service_account_key_path,
)

# Update the in-memory credentials cache (added in pandas-gbq 0.7.0).
pdg.context.credentials = credentials
pdg.context.project = project_id

#結合したデータをBigQueryにアップロード
df.to_gbq(destination_table=table_id,project_id=project_id,if_exists='replace')

#関数を実行
GoogleDrivedata_to_bigquery(df)

#カラム名を指定したい場合には以下のコードも実行してください。

#BigQueryのスキーマを指定する
column_types = [{'name': 'col1', 'type': 'STRING'},...]


これでBigqueryにデータをアップロードできます。

まとめ

まとめると注意点として、このコードを実行する前に、以下のことを行う必要があります。

・Google DriveにCSVファイルを保存しておく。

・BigQueryのプロジェクトIDとテーブルIDを取得しておく。

・BigQueryの操作に必要なサービスアカウントキーを取得しておく。

手順が多いなと感じた方もいるかもしれませんが、Googleドライブからデータをダウンロードし、Bigqueryにアップロードできればサイズが大きいファイルでもデータ活用ができます。 私自身かなりデータのアップロード方法に手間取ったので、なにかヒントになれば幸いです。

New call-to-action