過去の状態のテーブルが見たい（7日前まで）

SELECT
  *
FROM
  `dataset.table_name`
-- 絶対時間
FOR SYSTEM_TIME AS OF TIMESTAMP('2023-12-01 14:00:00', 'Asia/Tokyo');
-- 相対時間
-- FOR SYSTEM_TIME AS OF TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 2 HOUR);

Yuichi

2つのテーブル（table1とtable2）の差分を両方向で取得

SELECT 'table1' AS source_table, *
FROM (
    SELECT * FROM {table1}
    EXCEPT DISTINCT
    SELECT * FROM {table2}
)
UNION ALL
SELECT 'table2' AS source_table, *
FROM (
    SELECT * FROM {table2}
    EXCEPT DISTINCT
    SELECT * FROM {table1}
)

このクエリでは、以下の変更を行っています:
各サブクエリの結果に source_table という新しいカラムを追加しています。
table1 からの結果には 'table1' という値を、table2 からの結果には 'table2' という値を設定しています。
この修正により、結果セットの各行がどちらのテーブルからのものかを簡単に識別できるようになります。source_table カラムの値を確認することで、その行が table1 に固有のものか、table2 に固有のものかがわかります。
この方法は、両方のテーブルの差分を効率的に取得しつつ、各行の出所を明確にすることができます。データ分析やデバッグの際に特に有用です


```WITH new_table AS (
新しいロジック
)

SELECT
  'new_table' AS source_table
  ,*
FROM (
  SELECT * FROM new_table
  EXCEPT DISTINCT
  SELECT * FROM {{ ref('既存のモデル') }}
)

UNION ALL

SELECT
  'old_table' AS source_table
  ,*
FROM (
  SELECT * FROM {{ ref('既存のモデル') }}
  EXCEPT DISTINCT
  SELECT * FROM new_table
)

Yuichi

テーブルの完全なコピーを作成する

like 句は、新しいテーブルのスキーマ（列の構造）を既存のテーブルと同じにします

CREATE OR REPLACE TABLE `プロジェクトID.データセットID.test_02`
like `プロジェクトID.データセットID.test_01` AS (
SELECT *
FROM `プロジェクトID.データセットID.test_01`
) ;

Yuichi

先月の低速クエリランキング

WITH jobs AS (
  SELECT
    user_email,
    query,
    total_slot_ms,
    TIMESTAMP(DATETIME(TIMESTAMP(creation_time), "Asia/Tokyo")) AS creation_time_jst
  FROM
    `region-asia-northeast1`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
),
filtered_jobs AS (
  SELECT
    user_email,
    query,
    total_slot_ms,
    creation_time_jst
  FROM
    jobs
  WHERE
    DATE(creation_time_jst) >= DATE_SUB(DATE(CURRENT_TIMESTAMP(), "Asia/Tokyo"), INTERVAL 1 MONTH)
    AND DATE(creation_time_jst) < DATE(CURRENT_TIMESTAMP(), "Asia/Tokyo")
)
SELECT
  FORMAT_TIMESTAMP("%Y-%m", creation_time_jst) AS query_month,
  user_email,
  query,
  SUM(total_slot_ms) AS total_slot_ms_sum
FROM
  filtered_jobs
GROUP BY
  query_month, user_email, query
ORDER BY
  total_slot_ms_sum DESC
LIMIT  50

Yuichi

ローカルで%%bigquery使いたい

ローカルで%%bigqueryを使用するには、以下の手順を実行します：

まず、必要なライブラリをインストールします：

pip install google-cloud-bigquery pandas pydata-google-auth

Jupyter Notebookを開き、以下のコードを実行して認証を行います：

import pydata_google_auth

credentials = pydata_google_auth.get_user_credentials(
    ['https://www.googleapis.com/auth/bigquery'],
)

このコードを実行すると、GCPへのログイン画面が表示されます。BigQueryの権限を持つユーザーを選択してログインし、表示された認証コードをコピーしてセルの実行結果に入力します

次に、取得した認証情報をBigQueryのマジックコマンドに設定します：

python
from google.cloud.bigquery import magics
magics.context.credentials = credentials

これで%%bigqueryマジックコマンドを使用できるようになります。

例えば：

%%bigquery
SELECT * FROM `your_project.your_dataset.your_table` LIMIT 10

このようにして、ローカル環境のJupyter NotebookでBigQueryを直接操作できるようになります