日々地道に成長

思いついたことを書いていきます。

【BigQuery】既存のテーブルをパーティションテーブルに変更する方法

お疲れ様です。コーヒーです。

 

今回はBigQueryの既存テーブルをパーティションテーブルに変更する方法をまとめてみました。

今回はbqコマンドを使用しての方法になりますので是非参考にしてみてください！

 

使用するコマンドについて

こちらが今回使用する方法の公式ドキュメントになります！

こちらも見ることでより深く理解につながると思います。

cloud.google.com

パーティションテーブルに変更する方法

今回は例として日別でのパーティションを設定してみたいと思います。

bq query --destination_table project_id.data_set_partition --time_partitioning_field date --time_partitioning_type DAY --use_legacy_sql=false 'select * from `poject_id.data_set`'

上記コマンドは[data_se]tテーブルの[date]カラムを[DAY]パーティションで設定し、「data_set_partition」テーブルとして新たに作成したものになります！

それぞれのオプションについて説明します。

基本的に上記を指定し、あとは既存のテーブルをクエリで抽出するだけでパーティションテーブルに変更することが出来ます！

 

以上でパーティションテーブルへの変更は終了となります。

パーティションテーブルは料金的にもお得になるのでどんどん活用していきましょう！

最後まで読んでいただきありがとうございました！！

【BigQuery】tsvファイルをBQに取り込む方法

お疲れ様です。コーヒーです。

 

今回はBigQueryにtsvファイルを取り込む方法を2パターン紹介します！

tsvファイルをBigQueryに取り込むにはひと工夫必要となりますので、こちら参考にしてみてください。

 

BQコマンドでのロードする方法

bqコマンドのloadを使用した方法になります。

ロードしたいファイルに関してはGCSにあらかじめ置いておいてください。

bq load --source_format=CSV --encoding=UTF-8 --field_delimiter="\t" dataset.table gs://path
  • --source_format：csv
  • --field_delimiter："\t" 

上記のようにオプションを設定することによりtsvファイルでもロードを行うことが出来ます！

Linuxコマンドでcsvファイルに変換する方法

こちらはそもそもcsvに変換してから取り込んでしまおうという方法です。

cat test.tsv | tr "\\t" "," > test.csv

catコマンド：ファイル内容を出力

trコマンド：文字列変換

上記2つのコマンドをパイプ処理で繋げ、csvファイルをtsvファイルに変換してます。

変換後のファイルは通常のcsvファイルをBQに取り込む方法で行えます。

例としてbqコマンドでの取り込みは以下のようになります。

bq load --source_format=CSV --encoding=UTF-8 dataset.table gs://path

 

以上でtsvファイルの読み込み方法は終了となります！

最後まで読んでいただきありがとうございました！！

【BigQuery】SQLでテーブルの差分確認

お疲れ様です。コーヒーです。

 

今回はBigQueryでのテーブルの差分確認方法についてです。

データを整理しているときやデータのリカバリ時に、二つのテーブルの差分を確認する必要が出てくると思います。

そんな時に今回紹介する方法を利用すればすぐに確認できると思います。

クエリ内容

以下のクエリはお互いのテーブルの差分カラムを抽出するものになります！

お互いの差分カラムをすべて表示するものになります。

(
    SELECT date,count(*) FROM table_a 
    EXCEPT DISTINCT
    SELECT date,count(*) FROM table_b  
)
UNION ALL
( 
    SELECT date,count(*) FROM table_b 
    EXCEPT DISTINCT
    SELECT date,count(*) FROM table_a 
)

 

以下のクエリはtable_aとtable_bを日別で件数比較しているものになります。

応用するとこのような使い方もできます！

with a as(
(
    SELECT date,count(*) FROM table_a group by date 
    EXCEPT DISTINCT
    SELECT date,count(*) FROM table_b group by date 
)
UNION ALL
( 
    SELECT date,count(*) FROM table_b group by date
    EXCEPT DISTINCT
    SELECT date,count(*) FROM table_a group by date 
)
)
select * from a order by 1

以上が差分確認クエリの作成方法がです。

テーブルの比較に使える手法なのでぜひ覚えておいてください！

最後まで読んでいただきありがとうございました！！

【Linux,windows,S3】ファイルの行数カウントについて

お疲れ様です。コーヒーです。

 

今回はデータ準備をする上でよく確認するファイルの行数カウントについてまとめてみました。

特にビッグデータはファイルを開いて確認することが出来ないので、今回紹介する方法は役に立ちます。

それぞれの状況別に確認方法を紹介していきます！

 

Linuxコマンドでの確認方法

Linuxコマンドでの確認にはwcコマンドを使用します。

wcコマンドはテキストファイルの行数や単語数、ファイルサイズ等を確認できるコマンドになっています。

今回は行数を確認するので以下のように入力します。

wc -l test.csv
201023 test.csv

[-l]オプションを使用することで上記のようにファイルの行数のみを表示してくれます。

Windowsでの確認方法

Windows環境ではFINDコマンドを使用します。

FIND　/v /c "" test.csv 
test.csv: 201023

S3にあるファイルの確認方法

上記のLinuxコマンドの確認の応用としてS3にあるファイルをダウンロードせずに、確認する方法となります！

aws s3 cp s3://bucket_name/test.csv - | wc -l

上記のようにs3コマンドをwcコマンドに渡すことで確認することができます。

s3にあるファイルを直接確認できて手間が減るので、s3を使用している場合お勧めの方法です！

 

以上でファイルの行数カウント方法は終了となります。

データ準備においてファイルの取り込み件数は重要な確認事項となりますので、今回の方法を活用してみてください。

最後まで読んでいただきありがとうございました！！

 

【BigQuery,digdag】BQとdigdagを使用した平日判定処理の作成方法

お疲れ様です。コーヒーです。

 

今回はDigdagのでの平日判定処理の作成方法をまとめてみました。

祝日も除外しての平日のみの処理を作成する必要があったため、この処理を作成してみました。

今まで本ブログで紹介した方法を複合して作成したもになっています！

今までの記事を参考にするとより理解が深まるかと思います。

digdagによる平日判定処理の作成方法

今回使用したdigdagのコードは以下になります。

timezone: 'Asia/Tokyo'
_export:
  date: ${moment(session_date).format('YYYY-MM-DD')}

#平日判定処理
+weekday_check:
  +check_query:
    sh_result>: bq query --format=json --use_legacy_sql=false --parameter=date:DATE:${date} < check_weekday.sql
    destination_variable: resultset
    stdout_format: json-list-map

  +weekday_if:
    for_each>:
      rs: ${resultset}
    _do:
      if>: ${rs.flag}
      _do:
        echo>: ${target_date}

処理の流れとしては、以下になります。

  1. 平日判定処理クエリを実行
  2. 平日判定処理クエリ結果をsh_resultプラグインを使用して、変数に代入
  3. for文を使用して変数の値を取り出す
  4. if文にて平日かそうでないかを判別(true or false)
  5. trueの場合処理を実行(今回は実行日を標準出力)

平日判定処理クエリに関しては以下のようになってます。

SELECT
    if(EXTRACT(DAYOFWEEK FROM  DATE (@date)) in (1,7) or
     date(@date) in (select holiday_date from test.holiday),false,true) as flag

test.holidayテーブルには祝日をあらかじめ登録しています。

自分はこちらの内閣府がだしているcsvファイルをもとに作成しました。

www8.cao.go.jp

 

以上で平日判定処理の作成方法は終了となります！

最後まで読んでいただきありがとうございました！！

【Digdag】sh_resultプラグインの使用、活用方法

お疲れ様です。コーヒーです。

 

今回はDigdagのsh_resultプラグインの使用方法をまとめてみました。

こちらのプラグインはshコマンドの結果を変数として使用できる、非常に便利なプラグインになっております。

使い方によってはBQのクエリ結果を変数として使用できたりするため、ぜひ覚えておいてください！

sh_resultプラグインの概要

今回使用するsh_resultプラグインのドキュメントはこちらです！

github.com

for_eachオペレータの実際の使用例は以下のようになります。

_export:
  plugin:
    repositories:
      - https://jitpack.io
    dependencies:
      - com.github.takemikami:digdag-plugin-shresult:0.0.3

+result:
  +result_set:
    sh_result>: ls
    destination_variable: resultset
    stdout_format: text

+display:
  echo>: ${resultset}

上記はlsコマンドの出力結果をechoコマンドで表示しているものになります。

shresultプラグインを使用するには_exportタスクにてpluginの設定をする必要があります。

pluginの設定はコピペで問題ないです。

オプションの設定は以下のようになってます。

  • destination_variable:格納する変数名の設定
  • stdout_format:出力フォーマトのタイプ設定(text,json-list-map等)

今回は基本的な使い方としてフォーマットはtextでの使用をしています。

 

次回はBQのクエリ結果を使用してのshresultの使用方法を解説しようと思います！

最後まで読んでいただきありがとうございました！！

【Digdag】digdagにおけるif文の基本使用方法【基礎】

お疲れ様です。コーヒーです。

 

今回はDigdagのif文の使用方法をまとめてみました。

if文はプログラムにおいて必須の部分ですので、Digdagにおいてもマスターしておくとよいと思います！

Digdagにおけるif文の概要

今回使用するifオペレータの公式ドキュメントはこちらです！

docs.digdag.io


ifオペレータの実際の使用例は以下のようになります。

+task:
  if>: ${param}
  _do:
    echo>: "ok"
  _else_do:
    echo>:  "no"

上記はparam変数がtrueなら[ok]、falseなら[no]と出力するものになってます。

それぞれのタスクの役割は以下のようになってます。

  • if>:trueまたはfalseを判定するタスク
  • _do:ifタスクでtrueを受け取ったときに実行されるタスク
  • _do:ifタスクでfalseを受け取ったときに実行されるタスク

注意点としてifタスクではtrueもしくはfalseの判定しかできません！

他プログラム等の10以上だったら実行のようなものは、直接入力はできなくなっております。

 

if文の基本的な使用方法は以上になります。

if文の応用的使い方については別で解説しようと考えています！

最後まで読んでいただきありがとうございました！！