概要

データ分析を行う上で Python3 で Google BigQuery を操作することが多いです．
操作には下記の 2 つの手法が存在します．

• Python の BigQuery 連携ライブラリを使う
• Google Cloud Datalab を使う

今回は, Python の BigQuery 連携ライブラリを使う手法の内 pandas.io.gbq を使う手法について説明します．今回は Apache Parquet 形式で取ってきたデータを保存します.

Apache Parquet 形式とは

csvなどの行志向のデータフォーマットと違い、列志向のフォーマットで、列単位でデータを取り出す分析用途に向いています。この記事で他の形式との比較をしています． 特に大規模データファイルを圧縮する際に効率が良くなるのでビッグデータの分析データの保存に用いられます．

pandas.io.gbq ことはじめ

自分ははじめにこの記事を参考にして, pandas.io.gbq を使おうとしていましたが, 下記のエラーが発生し, 上手くいきませんでした．

GenericGBQException: Reason: 400 Invalid table name: `project.dataset.table`
[Try using standard SQL (https://cloud.google.com/bigquery/docs/reference/standard-sql/enabling-standard-sql)].

おそらく 2016 年の記事なので, 2019 年の仕様と異なってしまうことがエラーの原因であると考えられます.
さらにエラー文を読んでみると StandardSQL に設定できていないことが考えられるわけです.

操作手法

以下の方法で Python から Google BigQuery を操作します.この方式を利用することにより, オンプレミス環境においてクエリを叩く回数を減らしコストを削減可能で, Parquetファイルとgzipを利用し圧縮効率をあげローカルに落とすデータの容量を削減することが可能です．

• ローカルに .parquet ファイル(データを保存したファイル)がないとき
  1. ローカル .sql ファイルを読み込む
  2. 読み込んだクエリで, BigQuery を叩く
  3. データフレームに格納する
  4. 列志向形式 (Apache Parquet) に圧縮して, .parquet 形式でローカルに保存する
• ローカルに .parquet ファイルがあるとき
  1. ローカルの .parquet ファイルを読み込む
  2. データフレームに格納する

コード説明

自分は下記のようなコードを書いて, 上の条件を満たすようにしました.

test.pynb

import os 
from google.cloud import bigquery
import pandas as pd
import pyarrow as pa
import pyarrow.parquet as pq

#parquetを入手，ある場合は取得しない
filePath = "../input/test.parquet"
sqlPath = "../script/test.sql"
if(not os.path.isfile(filePath)): #1
    with open(sqlPath, 'r') as fd: 
        query = fd.read() #2
    df = pd.read_gbq(query, 'project名', dialect='standard') #3
    table = pa.Table.from_pandas(df) #4
    pq.write_table(table, filePath, compression='gzip') #5
else:
    table = pq.read_table(filePath) #6
    df = table.to_pandas() #7

軽く説明すると,

• #1 では os ライブラリを用いて指定したパスにデータファイルがあるかないかを確認し, なかったら if 文内の処理に移行します
• #2 では #1 で指定されたパスの .sql ファイルから, クエリを変数 query に代入します．
• #3 では #2 で取得したクエリを BigQuery 上で実行します
• #4 では #3 で実行したクエリの結果をデータフレーム型に代入します．
• #5 では #4 で作ったデータフレームを parquet ファイルで gzip 形式に圧縮し保存します．
• #6 では指定されたパスにデータファイルがあったとき, table にロードします
• #7 では #6 でロードした table をデータフレーム型に変更します

実行結果

これを実行すると Jupyter Notebook 上で, 下記のような画面が出力されます.

リンクを踏み, 認証を行うことで, 認証コードが発行されます. そのコードを Jupyter Notebook 上のフォームに入力することで, Python3 の PandasGBQ で BigQuery を操作することができるようになります.

まとめ

以上より, Python を用いてGoogle BigQuery を操作することができます．また上記のようなコードを書くことで, コストを削減し, 圧縮効率をあげ, 効率よくデータを取ってくることが可能です.

概要

データ分析を行う上で Python3 で Google BigQuery を操作することが多いです．
操作には下記の 2 つの手法が存在します．

• Python の BigQuery 連携ライブラリを使う
• Google Cloud Datalab を使う

今回は, Python の BigQuery 連携ライブラリを使う手法の内 pandas.io.gbq を使う手法について説明します．

今回の目標

BigQuery などのオンプレミス環境においてクエリを叩く回数を減らし, コストを削減することおよび, ローカル環境を圧迫しないようなデータの保持を行うことを目標にします. そのため Apache Parquet 形式で取ってきたデータを保存することにしました.

Apache Parquet 形式とは

csvなどの行志向のデータフォーマットと違い、列志向のフォーマットで、列単位でデータを取り出す分析用途に向いています。この記事で他の形式との比較をしています． 特に大規模データファイルを圧縮する際に効率が良くなるのでビッグデータの分析データの保存に用いられます．

pandas.io.gbq ことはじめ

自分ははじめにこの記事を参考にして, pandas.io.gbq を使おうとしていましたが, 下記のエラーが発生し, 上手くいきませんでした．

GenericGBQException: Reason: 400 Invalid table name: `project.dataset.table`
[Try using standard SQL (https://cloud.google.com/bigquery/docs/reference/standard-sql/enabling-standard-sql)].

おそらく 2016 年の記事なので, 2019 年の仕様と異なってしまうことがエラーの原因であると考えられます.
さらにエラー文を読んでみると StandardSQL に設定できていないことが考えられるわけです.

操作手法

以下の方法で Python から Google BigQuery を操作します.この方式を利用することにより, オンプレミス環境においてクエリを叩く回数を減らしコストを削減可能で, Parquetファイルとgzipを利用し圧縮効率をあげローカルに落とすデータの容量を削減することが可能です．

• ローカルに .parquet ファイル(データを保存したファイル)がないとき
  1. ローカル .sql ファイルを読み込む
  2. 読み込んだクエリで, BigQuery を叩く
  3. データフレームに格納する
  4. 列志向形式 (Apache Parquet) に圧縮して, .parquet 形式でローカルに保存する
• ローカルに .parquet ファイルがあるとき
  1. ローカルの .parquet ファイルを読み込む
  2. データフレームに格納する

コード説明

自分は下記のようなコードを書いて, 上の条件を満たすようにしました.

test.pynb

import os 
from google.cloud import bigquery
import pandas as pd
import pyarrow as pa
import pyarrow.parquet as pq

#parquetを入手，ある場合は取得しない
filePath = "../input/test.parquet"
sqlPath = "../script/test.sql"
if(not os.path.isfile(filePath)): #1
    with open(sqlPath, 'r') as fd: 
        query = fd.read() #2
    df = pd.read_gbq(query, 'project名', dialect='standard') #3
    table = pa.Table.from_pandas(df) #4
    pq.write_table(table, filePath, compression='gzip') #5
else:
    table = pq.read_table(filePath) #6
    df = table.to_pandas() #7

軽く説明すると,

• #1 では os ライブラリを用いて指定したパスにデータファイルがあるかないかを確認し, なかったら if 文内の処理に移行します
• #2 では #1 で指定されたパスの .sql ファイルから, クエリを変数 query に代入します．
• #3 では #2 で取得したクエリを BigQuery 上で実行します
• #4 では #3 で実行したクエリの結果をデータフレーム型に代入します．
• #5 では #4 で作ったデータフレームを parquet ファイルで gzip 形式に圧縮し保存します．
• #6 では指定されたパスにデータファイルがあったとき, table にロードします
• #7 では #6 でロードした table をデータフレーム型に変更します

実行結果

これを実行すると Jupyter Notebook 上で, 下記のような画面が出力されます.

リンクを踏み, 認証を行うことで, 認証コードが発行されます. そのコードを Jupyter Notebook 上のフォームに入力することで, Python3 の PandasGBQ で BigQuery を操作することができるようになります.

まとめ

以上より, Python を用いてGoogle BigQuery を操作することができます．また上記のようなコードを書くことで, コストを削減し, 圧縮効率をあげ, 効率よくデータを取ってくることが可能です.

下記をコピペして、実行する(F5)を押す。

　→accessファイルと同じフォルダに、「クエリ名.sql」というテキストファイルができます。

Sub exportQueryAsSqlFile()

    Dim db, query, nFP As Integer

    Set db = Application.CurrentDb
    For Each query In db.QueryDefs

        Debug.Print query.Name


        nFP = FreeFile
        Open CurrentProject.Path & "\" & query.Name & ".sql" For Output As nFP
        Print #nFP, query.sql
        Close nFP

    Next query

End Sub

データベースの仕組み

• 表 ＝ テーブル
• 縦列 ＝ カラム
• 横列 ＝ レコード

select = どのカラム(縦列)のデータを取得するか
from = どのテーブルのカラムなのか
where = どこのレコード（横の列）を取得するか
WHERE category = "〇〇"
;(セミコロン) = SQLの締め

2019年6月27日（木）所要時間：30分

はじめに

PostgreSQLを使用する際に個人的にハマった点をまとめたものです。
随時更新していく予定です。
間違いやアドバイスなどがありましたら、コメントで教えていただけると幸いです。

更新された行のデータを返したい

• PostgreSQLバージョン：9.2.23（サーバー）
• 参考サイト

※参考サイトのバージョンは PostgreSQL 9.6.5ですが、自分の場合PostgreSQL 9.2.23でも動作しました。

解決策：RETURNING句を使う

以下参考サイトより
RETURNINGを使うことで、行を取得するために余分なデータベースへの問い合わせを行うことを避けられ、それ以外の方法で更新された行を確実に特定するのが難しい場合には、これは特に貴重です。

CSVファイルをコピーコマンドで読み込むと、SERIAL型のカラムがインクリメントされない

• PostgreSQLバージョン：9.2.23（サーバー）
• 参考サイト

症状

idカラムはSERIAL型を設定してる。psqlのメタコマンドでidカラムを含めたデータをCSVファイルからコピーする。
その後、idカラムを指定せずにINSERTを実行するとidカラムの値に重複が生じる。

data.csv

1, hoge1
2, hoge2
3, hoge3

テーブル作成.txt

$ testdbcon #データベースへ接続するためのエイリアス
psql (10.7 (Ubuntu 10.7-0ubuntu0.18.04.1), server 9.2.23)
Type "help" for help.

testdb=# -- テーブルを作成
testdb=# CREATE TABLE users (id SERIAL, name VARCHAR(100), PRIMARY KEY (id));
...(省略)
testdb=# \d users;
                                    Table "public.users"
 Column |          Type          | Collation | Nullable |              Default
--------+------------------------+-----------+----------+-----------------------------------
 id     | integer                |           | not null | nextval('users_id_seq'::regclass)
 name   | character varying(100) |           |          |
Indexes:
    "users_pkey" PRIMARY KEY, btree (id)

実行結果(エラー).txt

testdb=# -- CSVファイルを読み込む
testdb=# \copy users FROM data.csv  DELIMITER AS ','
COPY 3
testdb=# -- idに4が設定されることを期待してデータを挿入
testdb=# INSERT INTO users (name) VALUES ('hoge4');
ERROR:  duplicate key value violates unique constraint "users_pkey"
DETAIL:  Key (id)=(1) already exists.

解決策

SERIAL型のidカラム以外を指定してコピーする。その際、CSVファイルからSERIAL型のカラムのデータを削除すること。

data.csv

hoge1
hoge2
hoge3

実行結果(成功).txt

testdb=# -- テーブルを作成
testdb=# CREATE TABLE users (id SERIAL, name VARCHAR(100), PRIMARY KEY (id));
...(省略)
testdb=# -- SERIAL型のカラム以外を指定してコピーする。
testdb=# \copy users (name) FROM data.csv DELIMITER AS ','
COPY 3
testdb=# -- SERIAL型のidカラム以外を指定してデータを挿入
testdb=# INSERT INTO users (name) VALUES ('hoge4');
INSERT 0 1
testdb=# SELECT * FROM users;
 id | name
----+-------
  1 | hoge1
  2 | hoge2
  3 | hoge3
  4 | hoge4
(4 rows)

はじめに

業務系のシステム開発をしていると、トランにマスタをぶつける際に外部結合にすることがあります。
これは、万が一、マスタが取得できなくても検索対象となるべきトランの欠落を防ぐためです。

但し、外部結合は、絞込み条件をどこに書くかによって検索結果は異なります。安全のために外部結合にした結果、バグを埋め込んでしまっては元も子もないので気を付けましょう。

具体例

対象とするテーブル

【受注T】

受注NO	商品CD	金額
001	AAA	100
002	BBB	300
003	CCC	500

【商品M】（商品CD='BBB'が欠落）

商品CD	商品名	商品区分
AAA	商品A	0
CCC	商品B	1

マスタが欠落しても外部結合していれば、受注データは表示される

select
    T.受注NO
  , T.商品CD
  , M.商品名
  , T.金額
from
    受注T T
    left outer join 商品M M
         on T.商品CD = M.商品CD

受注NO	商品CD	商品名	金額
001	AAA	商品A	100
002	BBB	null	300
003	CCC	商品C	500

• 商品名は表示されないレコードが存在するが、受注Tの対象データは全て検索されている。
• select するのは、T.商品CD で、M.商品CD 出ないことに注意。

商品区分='1'の商品の受注のみを表示する（誤り）

誤り

select
    T.受注NO
  , M.商品CD
  , M.商品名
  , T.金額
from
    受注T T
    left outer join 商品M M
         on T.商品CD = M.商品CD
        and M.商品区分 = '1'

受注NO	商品CD	商品名	金額
001	AAA	null	100
002	BBB	null	300
003	CCC	商品C	500

• 絞り込んだ後で、外部結合しているので、受注Tの全レコードが検索される。

商品区分='1'の商品の受注のみを表示する（考察→成果へ）

結果オーライ

select
    T.受注NO
  , M.商品CD
  , M.商品名
  , T.金額
from
    受注T T
    left outer join 商品M M
         on T.商品CD = M.商品CD
where
    M.商品区分 = '1'

受注NO	商品CD	商品名	金額
003	CCC	商品C	500

• 外部結合した後に絞り込む。
• 但し、上記の場合は、商品Mに存在しない受注Tのレコードの商品区分は null になるため、left outer join ではなく、inner join で結合するのが正しい。

上記を踏まえた修正

select
    T.受注NO
  , M.商品CD
  , M.商品名
  , T.金額
from
    受注T T
    inner join 商品M M
         on T.商品CD = M.商品CD
where
    M.商品区分 = '1'

• inner join にした場合は、inner join の条件部分で絞り込んでも、where 条件で絞り込んでも、結果は同じになるが、個人的には、inner join の中で先に絞り込んだ商品Mと受注Tを結合した方がよいと思う。（最近のDBは賢いので、どっちで書いても実行計画は同じになることが多い。）

最終版

select
    T.受注NO
  , M.商品CD
  , M.商品名
  , T.金額
from
    受注T T
    inner join 商品M M
         on T.商品CD = M.商品CD
        and M.商品区分 = '1'

番外編

結果はあってるけど、おかしなSQL

結果があえばよいってわけじゃないぞ！

select
    T.受注NO
  , M.商品CD
  , M.商品名
  , T.金額
from
    受注T T
    left outer join 商品M M
         on T.商品CD = M.商品CD
        and M.商品区分 = '1'
where
    M.商品CD is not null

• 受注Tの全件に対して商品Mをぶつけた後に絞り込むので効率が悪いし、そもそも、SQL の組み立て方からしておかしいてす。

おわりに

そもそもテーブル間の整合性が担保できないDBに問題があるのですが、世の中のシステムは結構いい加減です。いい加減なシステムのSQLは、いい加減なものがちょこちょこあって、改めて基本を押さえましょうという意味で書きました。