DB

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

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


結果

Row file_name
1 file_b.jpg
2 file_c.gif
3 file_a.jpg


すると結果のようにファイル名のみを取得することができる。

フォルダ名を取得するには [offset(n)] のnの値を変えれば良い。


拡張子を除きたい場合はさらにピリオド . を区切り文字として split() すれば良い。



まとめ

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

やり方としては split() で区切り文字を使ってテキストを分解し、分解したarrayに対して [offset(n)] で取得したい要素を指定する。



他にもBigQueryの使い方に関する記事もあるので、もし気になるものがあれば見ていって欲しい。

コチラもオススメ

KRSW

駆け出し機械学習エンジニア。機械学習、DB、WEBと浅く広い感じ。 Junior machine learning engineer. Not a specialist but a generalist who knows DB, WEB too.

役に立ったらシェアしてくれると励みになります。

にほんブログ村 IT技術ブログへ

-DB
-

Translate »

Copyright© ITips , 2021 All Rights Reserved.