BigQueryでファイルパスからファイル名やフォルダ名を取得する方法

BigQuery(BQ)で長いファイルパスからファイル名やフォルダ名だけ取得したい。

データ分析によく使われるGoogleのBigQuery(通称BQ)。

もちろんテキストデータは扱えるので、中には gs://bucketname/directoryname/filename.jpg のようなファイルのパスを扱うこともある。

ファイルパスを扱う場合、フルパスでそのまま使いたい場合もあれば、filename.jpg , filename , gs://bucketname/directoryname/ のようにファイル名やフォルダ（ディレクトリ）名を使いたい場合もある。

ではどうやってフルパスからファイル名やフォルダ名だけ取得するのか。

そこで今回はBigQueryでファイルパスからファイル名やフォルダ名を取得する方法について紹介する。

この記事を書いている人

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

記事を読むメリット

BigQueryでファイルパスからファイル名やフォルダ名を取得する方法がわかる

データの準備

まずはデータを準備する。

CREATE OR REPLACE TABLE
pj_name.test.tmp_path
AS
(
  (select "gs://aaa/bbb/ccc/file_a.jpg" as path)
  union all
  (select "gs://aaa/bbb/file_b.jpg" as path)
  union all
  (select "gs://ccc/file_c.gif" as path)
)

作成されたテーブルを確認する。

SELECT * FROM pj_name.test.tmp_path

結果

Row	path
1	gs://aaa/bbb/file_b.jpg
2	gs://ccc/file_c.gif
3	gs://aaa/bbb/ccc/file_a.jpg

BigQueryでグループ毎の最初や最後の値を取得する方法

ではこの作成したデータからどうやってファイル名のみを抽出するのか。

方法としては split() と [offset(n)] で工夫する方法がある。

step
1パスをsplitで分解する

まずはパスをsplitで分解する。

split(テキストカラム, "区切り文字") で指定した区切り文字を使ってテキストを分解できる。

SPLIT(value[, delimiter])
delimiter 引数を使用して value を分割します。
STRING の場合、デフォルトの区切り文字はカンマ（,）です。

参考：標準 SQL の式、関数、演算子 | BigQuery | Google Cloud

今回は / を区切り文字とする。

select split(path,"/") as path_array
from pj_name.test.tmp_path

結果

Row	path_array
1	gs:

	aaa
	bbb
	file_b.jpg
2	gs:

	ccc
	file_c.gif
3	gs:

	aaa
	bbb
	ccc
	file_a.jpg

すると結果のような1行の中に複数のarrayが含まれるデータになる。

step
2分解したarrayから目的の部分を取得する

次に分解したarrayから目的の部分を取得する。

目的の要素を取得するのに[offset(n)]を用いる。

OFFSET と ORDINAL
array_expression[OFFSET(zero_based_offset)]
array_expression[ORDINAL(one_based_offset)]
説明

位置によって ARRAY 要素にアクセスし、その要素を返します。OFFSET は番号付けがゼロから始まることを意味し、ORDINAL は番号付けが 1 から始まることを意味します。

参考：標準 SQL の式、関数、演算子 | BigQuery | Google Cloud

ファイル名を取得したい場合はarrayの最後の要素を取得することになるので n には array_length(q.path_array)-1 を指定する。

select q.path_array[offset(array_length(q.path_array)-1)] as file_name
from
(
  select split(path,"/") as path_array
  from pj_name.test.tmp_path
)as q

結果