BigQueryのINFORMATION_SCHEMAを使い、対象テーブルが日付サフィックステーブルかに判定してみる

備忘録です。データ連携の転送ファイルを自動生成するために、対象テーブルのカラムの取得に加えて、テーブルが日付サフィックステーブルか判定する必要がありました。直接テーブルに対してクエリを発行して調べるとお金が高くかかってしまうので、INFORMATION_SCHEMAを使ってリーズナブルに判定してみました。

require "google/cloud/bigquery"

project_id = ''
dataset_name = ''
table_name = ''
bigquery_client = Google::Cloud::Bigquery.new(project:project_id)
dataset = bigquery_client.dataset(dataset_name)
sql = <<-EOS
WITH
  dailybatch_table_columns AS (
  SELECT
    table_name,
    column_name
  FROM
    `#{project_id}.#{dataset_name}.INFORMATION_SCHEMA.COLUMNS`
  WHERE
    table_name = '#{table_name}'),
  dailybatch_table_suffix AS (
  SELECT
    table_name AS table_name_timesuffix,
    column_name
  FROM
    `#{project_id}.#{dataset_name}.INFORMATION_SCHEMA.COLUMNS`
  WHERE
    table_name = CONCAT('#{table_name}_',FORMAT_TIMESTAMP('%Y%m%d', TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 2 day),'Asia/Tokyo') ) )
SELECT
  a.table_name,
  a.column_name,
  b.table_name_timesuffix,
  CASE
    WHEN b.table_name_timesuffix IS NULL THEN 0
  ELSE
  1
END
  AS timesuffix
FROM
  dailybatch_table_columns AS a
LEFT OUTER JOIN
  dailybatch_table_suffix AS b
ON
  a.table_name = RTRIM(b.table_name_timesuffix, CONCAT('_', FORMAT_TIMESTAMP('%Y%m%d', TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 2 day),'Asia/Tokyo') ))
  AND a.column_name = b.column_name
EOS
job = bigquery_client.query_job(sql, large_results: true)
job.wait_until_done!
unless job.failed?
    job.query_results.each do |row|
        puts row
    end
end

実行すると次のような形でデータを取れます。日付サフィックスの場合timesuffix=1、そうでない場合0となります。

{:table_name=>"table", :column_name=>"col1", :table_name_timesuffix=>"table_20201211", :timesuffix=>1}
{:table_name=>"table", :column_name=>"col2", :table_name_timesuffix=>"table_20201211", :timesuffix=>1}
{:table_name=>"table", :column_name=>"col3", :table_name_timesuffix=>"table_20201211", :timesuffix=>1}
{:table_name=>"table", :column_name=>"col4", :table_name_timesuffix=>"table_20201211", :timesuffix=>1}

Dockerfile
SqlServerからBigQueryへの転送設定なのでtiny_tdsも入れてます。

FROM ruby:2.6.6-slim-stretch

ENV FREETDS_VERSION 1.1.40

WORKDIR /usr/src/app/
RUN apt-get update && apt-get install -y \
    wget \
    build-essential \
    libc6-dev \
    curl

# freetdsのダウンロード
RUN wget http://www.freetds.org/files/stable/freetds-${FREETDS_VERSION}.tar.gz && \
  tar -xzf freetds-${FREETDS_VERSION}.tar.gz && \
  rm -f freetds-${FREETDS_VERSION}.tar.gz

# freetdsのmake
WORKDIR freetds-${FREETDS_VERSION}
RUN ./configure --prefix=/usr/local --with-tdsver=7.4 && \
  make && \
  make install

#RUN make install
WORKDIR /usr/src/app/
RUN gem install tiny_tds -v 2.1.2
RUN gem install \
   fluentd \
   fluent-plugin-gcloud-pubsub-custom \
   jemalloc \
   google-cloud-bigquery

COPY . /usr/src/app/

RUN echo "deb [signed-by=/usr/share/keyrings/cloud.google.gpg] http://packages.cloud.google.com/apt cloud-sdk main" | tee -a /etc/apt/sources.list.d/google-cloud-sdk.list && curl https://packages.cloud.google.com/apt/doc/apt-key.gpg | apt-key --keyring /usr/share/keyrings/cloud.google.gpg  add - && apt-get update -y && apt-get install google-cloud-sdk -y

dev.classmethod.jp