BigQueryで多数のカラムを使ったSELECT文を簡単に作る方法

SELECT文書くのにカラムを1個ずつ指定するの面倒くさい

データの分析や処理に便利なBigQuery(BQ)。

データをテーブルに保存しておけば、SQLを使ってテーブル同士を結合したり集計したり色々な角度でデータを操作することができる。

中でもよく使うのがSELECT文。

以下のようにテーブルの中のカラムを指定してデータを呼び出すことができる。

SELECT column1, column2, column3 from table1

しかしこの文法、指定するカラム数が増えるとSQL文が長く書くのが大変になる。

何とか簡単に書く方法は無いのか。

そこで今回はBigQueryで多数のカラムを使ったSELECT文を簡単に作る方法について紹介する。

この記事を書いている人

システムエンジニア、AIエンジニアと、IT業界で10年以上働いている中堅。PythonとSQLが得意。

データ準備

ではまずカラムの多いデータを準備する。

以下のCSVを many_column_sample というテーブルとして取り込む。

text_col1,text_col2,text_col3,num_col1,num_col2,num_col3,num_col4,num_col5,num_col6,num_col7,num_col8,num_col9,num_col10
a1,b1,c1,1,2,3,4,5,6,7,8,9,10
a2,b2,c2,2,3,4,5,6,7,8,9,10,11
a3,b3,c3,3,4,5,6,7,8,9,10,11,12
a4,b4,c4,4,5,6,7,8,9,10,11,12,13
a5,b5,c5,5,6,7,8,9,10,11,12,13,14
a6,b6,c6,6,7,8,9,10,11,12,13,14,15

すると以下のようなテーブルとなる。

SELECT文の書き方

データが準備できたら、次はSELECT文を書いてみる。

全部のカラムを指定するのはワイルドカード * で指定すればよいので簡単にできる。

SELECT * FROM test.many_column_sample

では数値が保存されている num_col1 から num_col10 までの10カラムを指定するにはどうしたらよいだろうか。

SELECT
num_col1,
num_col2,
num_col3,
num_col4,
num_col5,
num_col6,
num_col7,
num_col8,
num_col9,
num_col10
FROM test.many_column_sample

カラム指定になったら一気に文字数が増えた。

指定しているのが10カラムなのでまだ入力できるが、これが100、1000と増えるともう手入力でSELECT文を書くのは難しくなる。

カラム名を100個も入力したくないお・・・

BigQueryで多数のカラムを使ったselect文を簡単に作る方法

ではカラム数が多くても簡単にSELECT文を書くにはどうしたらよいのか。

答えは EXCEPT で不要なカラムの方を指定すること。

EXCEPT をSELECT文に組み込むには以下のようにして使う。

SELECT
* EXCEPT (text_col1,text_col2,text_col3)
FROM test.many_column_sample

するとこのように EXCEPT で指定したカラムを除いた全てのカラムのデータが得られる。

まとめ

今回はBigQueryで多数のカラムを使ったSELECT文を簡単に作る方法について解説した。

多数のカラムを指定したい場合は、使いたいカラムを指定するよりも EXCEPT で使わないカラムを指定したほうがSQL文が短くて済む。

今まで１カラム除外する為に他のカラムを全部列挙してたので、この書き方は短く書けて良いわー

特定のカラムだけ別の計算した値で書き換えたい場合なんかは、UPDATEを使わずにEXCEPTで指定カラムを外して代わりに計算値カラムで置き換えてしまうという使い方もできますね

他にもBQ関連のTipsを書いているので、もし気になったら見てみて欲しい。