BigQuery の ml.generate_text() で temperature を指定する方法

BigQuery の ml.generate_text() で temperature を指定する方法のメモ。

temperature は以下にあるように AI の回答のランダム性の度合いに影響します。

https://cloud.google.com/vertex-ai/generative-ai/docs/model-reference/text?hl=ja

温度は、トークン選択のランダム性の度合いを制御します。温度が低いほど、確定的で自由度や創造性を抑えたレスポンスが求められるプロンプトに適しています。一方、温度が高いと、より多様で創造的な結果を導くことができます。温度が 0 の場合、確率が最も高いトークンが常に選択されます。この場合、特定のプロンプトに対するレスポンスはほとんど確定的ですが、わずかに変動する可能性は残ります。

BigQuery で生成AIでテキストを生成する際に、以下のようにして temperature を指定することができます。

select
  *
from
  ml.generate_text(
    model `test_dataset.generative_model`
    , (select 'BigQuery ML の temperature はどんな効果がありますか' as prompt)
    , struct (
        0.2 as temperature
        , true as flatten_json_output
      )
  )
;

実行結果

[{
  "ml_generate_text_llm_result": "## BigQuery の今後の展望\n\nBigQuery は、Google Cloud Platform のサーバーレスデータウェアハウスです。ペタバイト規模のデータを高速かつ効率的に処理できるため、多くの企業で利用されています。\n\nBigQuery の今後の展望としては、以下の点が挙げられます。\n\n* **さらなる高速化とスケーラビリティの向上**: Google は、BigQuery のパフォーマンスを継続的に向上させています。今後も、より高速なクエリ処理や、より大規模なデータセットの処理を可能にする機能が追加される予定です。\n* **機械学習機能の強化**: BigQuery は、すでに機械学習機能",
  "ml_generate_text_rai_result": "[{\"category\":1,\"probability\":1,\"probability_score\":0.018978037,\"severity\":1,\"severity_score\":0.040692952},{\"category\":2,\"probability\":1,\"probability_score\":0.011508148,\"severity\":1,\"severity_score\":0.059537489},{\"category\":3,\"probability\":1,\"probability_score\":0.13296424,\"severity\":1,\"severity_score\":0.097906686},{\"category\":4,\"probability\":1,\"probability_score\":0.030271869,\"severity\":1,\"severity_score\":0.02811406}]",
  "ml_generate_text_status": "",
  "prompt": "BigQuery の今後の展望を教えてください"
}]

"0.2 as temperature" を "1.0 as temperature" に変更した場合の実行結果

1.0 の方がより創造的な回答をしていると思われます。

[{
  "ml_generate_text_llm_result": "## BigQuery の今後の展望\n\nBigQueryは常に進化を続けており、今後も様々な新機能が追加される予定です。以下は、BigQueryの今後の展望に関する主なポイントです。\n\n### リアルタイム分析の強化\n\n* **ストリーム分析機能の向上:** BigQueryはリアルタイム分析を可能にする BigQuery ストリーム機能を備えていますが、今後もその機能が強化される予定です。これには、より高速なデータ処理や、より豊富な分析機能の追加などが含まれます。\n* **機械学習の活用:** リアルタイム分析と機械学習を組み合わせることで、より高度な分析が可能",
  "ml_generate_text_rai_result": "[{\"category\":1,\"probability\":1,\"probability_score\":0.018905448,\"severity\":1,\"severity_score\":0.040464837},{\"category\":2,\"probability\":1,\"probability_score\":0.014559518,\"severity\":1,\"severity_score\":0.063250519},{\"category\":3,\"probability\":1,\"probability_score\":0.089454599,\"severity\":1,\"severity_score\":0.076960839},{\"category\":4,\"probability\":1,\"probability_score\":0.042483207,\"severity\":1,\"severity_score\":0.02811406}]",
  "ml_generate_text_status": "",
  "prompt": "BigQuery の今後の展望を教えてください"
}]

BigQuery で生成AIによるテキスト生成

BigQuery で ML.GENERATE_TEXT() を使用してテキストを生成する方法のメモ。

大まかな手順は以下の通り。

• 言語モデル用の外部接続を追加
• 外部接続用のサービスアカウントにロールを設定
• 言語モデルの作成
• 生成AIでテキストを生成

1. 言語モデル用の外部接続を追加

• GCP コンソールで「BigQuery」をクリック
• BigQuery の「エクスプローラ」のメニューで「＋追加」をクリック
• 「外部データソースへの接続」をクリック
• 以下を設定
  • 接続タイプ: Vertex AI リモートモデル ...
  • 接続ID: 任意のID（ここでは ai_remote_model）
  • ロケーションタイプ: リージョン
  • リージョン: 任意のリージョン（ここではus-central1）
• 「接続を作成」ボタンをクリック

2. 外部接続用のサービスアカウントにロールを設定

• BigQuery の当該のプロジェクトに表示される「外部接続」欄に上記で作成した接続ID（us-central1.ai_remote_model）をクリックして接続情報を表示し、サービスアカウントIDをコピー。
• GCP コンソールで「IAMと管理」をクリック
• IAM メニューの「アクセス権を付与」をクリック
• 新しいプリンシパルに上記でコピーしたサービスアカウントIDをペースト
• 「ロールを選択」で「Vertex AI ユーザー」を選択
• 「保存」ボタンをクリック

3. 言語モデルの作成

• BigQuery の当該のプロジェクトのメニューをクリックし、「データセットを作成」をクリック
• 以下を設定
  • データセットID: 任意（ここでは test_dataset）
  • ロケーションタイプ: リージョン
  • リージョン: 任意（ここでは us-central1）
• 「データセットを作成」をクリック
• 以下のクエリを実行して言語モデルを作成
  

  create or replace model
    {データセット}.{モデル名} # ここでは test_dataset.generative_model
  remote with connection
    {外部接続ID} # ここでは `us-central1.ai_remote_model`
  options (
    endpoint = 'gemini-pro'
  );

4. 生成AIでテキストを生成

以下の SQL でテキストからベクトルを生成することができる。

gemini-1.5-pro は "prompt" カラムを処理対象とするため、select ... as prompt としている。

select
  *
from
  ml.generate_text(
    model test_dataset.generative_model,
    (select 'あなたは誰ですか' as prompt),
    struct(true as flatten_json_output)
  )
;

実行結果

[{
  "ml_generate_text_llm_result": "私はGoogleによって作られた、大規模言語モデルのジェミニです。私は、日本語、英語、フランス語、スペイン語、ドイツ語、中国語、韓国語、ポルトガル語、ロシア語、イタリア語、アラビア語、ヒンディー語、トルコ語、ベトナム語、インドネシア語、マレー語、タイ語、フィリピン語、オランダ語、ポーランド語、ウクライナ語、ルーマニア語、チェコ語、ハンガリー語、スウェーデン語、デンマーク語、ノルウェー語、フィンランド語、ギリシャ語、ブル",
  "ml_generate_text_rai_result": "[{\"category\":1,\"probability\":1,\"probability_score\":0.051558189,\"severity\":1,\"severity_score\":0.1046602},{\"category\":2,\"probability\":1,\"probability_score\":0.014559518,\"severity\":1,\"severity_score\":0.026355354},{\"category\":3,\"probability\":1,\"probability_score\":0.14829372,\"severity\":1,\"severity_score\":0.15215819},{\"category\":4,\"probability\":1,\"probability_score\":0.080646612,\"severity\":1,\"severity_score\":0.040769264}]",
  "ml_generate_text_status": "",
  "prompt": "あなたは誰ですか"
}]

以下のようにテーブルに格納されたテキストからテキストを生成することもできる。

テーブル定義

create table test_dataset.text_tbl (
  id     string,
  body   string
);

insert into test_dataset.text_tbl (id, body) values ('01', '晴れています。');
insert into test_dataset.text_tbl (id, body) values ('02', '曇りです。');
insert into test_dataset.text_tbl (id, body) values ('03', '雨が降っています。');

SQL

select
  t.id as id
  , t.body as title
  , (
    select
      ml_generate_text_llm_result
    from
      ml.generate_text(
        model test_dataset.generative_model,
        (select t.body as prompt),
        struct(true as flatten_json_output)
      )
    ) as s
from
  test_dataset.text_tbl as t
order by
  t.id asc
;

実行結果

[{
  "id": "01",
  "title": "晴れています。",
  "s": "今日はとても良い天気ですね！ 気持ちの良い青空が広がっています。 \n\nお出かけ日和なので、ぜひ外に出て楽しんでください。 \n\n何か予定はありますか？"
}, {
  "id": "02",
  "title": "曇りです。",
  "s": "はい、曇りですね。空はどんよりとしていて、雨が降りそうな雰囲気です。傘を持ってお出かけすることをおすすめします。"
}, {
  "id": "03",
  "title": "雨が降っています。",
  "s": "はい、雨が降っていますね。傘をお持ちですか？"
}]

BigQuery でテキストのベクトル（エンベディング）を生成

BigQuery で ML.GENERATE_TEXT_EMBEDDING() を利用してテキストのベクトル（エンベディング）を生成する方法のメモ。

大まかな手順は以下の通り。

• 言語モデル用の外部接続を追加
• 外部接続用のサービスアカウントにロールを設定
• 言語モデルの作成
• 生成AIによるテキストからの生成

1. 言語モデル用の外部接続を追加

• GCP コンソールで「BigQuery」をクリック
• BigQuery の「エクスプローラ」のメニューで「＋追加」をクリック
• 「外部データソースへの接続」をクリック
• 以下を設定
  • 接続タイプ: Vertex AI リモートモデル ...
  • 接続ID: 任意のID（ここでは ai_remote_model）
  • ロケーションタイプ: リージョン
  • リージョン: 任意のリージョン（ここではus-central1）
• 「接続を作成」ボタンをクリック

2. 外部接続用のサービスアカウントにロールを設定

• BigQuery の当該のプロジェクトに表示される「外部接続」欄に上記で作成した接続ID（us-central1.ai_remote_model）をクリックして接続情報を表示し、サービスアカウントIDをコピー。
• GCP コンソールで「IAMと管理」をクリック
• IAM メニューの「アクセス権を付与」をクリック
• 新しいプリンシパルに上記でコピーしたサービスアカウントIDをペースト
• 「ロールを選択」で「Vertex AI ユーザー」を選択
• 「保存」ボタンをクリック

3. 言語モデルの作成

• BigQuery の当該のプロジェクトのメニューをクリックし、「データセットを作成」をクリック
• 以下を設定
  • データセットID: 任意（ここでは test_dataset）
  • ロケーションタイプ: リージョン
  • リージョン: 任意（ここでは us-central1）
• 「データセットを作成」をクリック
• 以下のクエリを実行して言語モデルを作成
  

  create or replace model
    {データセット}.{モデル名} # ここでは test_dataset.embedding_model
  remote with connection
    {外部接続ID} # ここでは `us-central1.ai_remote_model`
  options (
    endpoint = 'textembedding-gecko-multilingual@001'
  );

4. テキストからのベクトル生成

以下の SQL でテキストからベクトルを生成することができる。

select
  *
from
  ml.generate_text_embedding(
    model test_dataset.embedding_model,
    (select '私は人間です' as content),
    struct(true as flatten_json_output)
  )
;

実行結果

[{
  "text_embedding": ["0.041282802820205688", "-0.049310304224491119", ...],
  "statistics": "{\"token_count\":3,\"truncated\":false}",
  "ml_embed_text_status": "",
  "content": "私は人間です"
}]

以下のようにテーブルに格納されたテキストのベクトルを生成することもできる。

テーブル定義

create table test_dataset.text_tbl (
  id     string,
  body   string
);

insert into test_dataset.text_tbl (id, body) values ('01', '晴れています。');
insert into test_dataset.text_tbl (id, body) values ('02', '曇りです。');
insert into test_dataset.text_tbl (id, body) values ('03', '雨が降っています。');

SQL

select
  t.id as id
  , t.body as title
  , (
    select
      text_embedding
    from
      ml.generate_text_embedding(
        model test_dataset.embedding_model,
        (select t.body as content),
        struct(true as flatten_json_output)
      )
    ) as s
from
  test_dataset.text_tbl as t
;

実行結果

[{
  "id": "01",
  "body": "晴れています。",
  "s": ["-0.0071903029456734657", "-0.062979131937026978", ...]
}, {
  "id": "02",
  "body": "曇りです。",
  "s": ["-0.020720960572361946", "-0.058550607413053513", ...]
}, {
  "id": "03",
  "body": "雨が降っています。",
  "s": ["-0.013185698539018631", "-0.027199963107705116", ...]
}]

BigQuery で主成分分析

BigQuery で主成分分析を行う方法のメモ。 ここでは、5次元のデータを2次元に次元圧縮します。

■テーブル作成・データ登録

drop table if exists dataset.test_data;

create table dataset.test_data (
  id      string,
  values  array
);

insert into dataset.test_data values ('id_1', [1.0, 0.0, 1.0, 3.0, 0.0]);
insert into dataset.test_data values ('id_2', [1.0, 2.0, 1.0, 1.0, 1.0]);
insert into dataset.test_data values ('id_3', [0.0, 2.0, 0.0, 1.0, 1.0]);
insert into dataset.test_data values ('id_4', [1.0, 0.0, 3.0, 2.0, 3.0]);
insert into dataset.test_data values ('id_5', [0.0, 0.0, 0.0, 2.0, 0.0]);
insert into dataset.test_data values ('id_6', [0.0, 0.0, 2.0, 2.0, 4.0]);
insert into dataset.test_data values ('id_7', [1.0, 2.0, 1.0, 1.0, 0.0]);
insert into dataset.test_data values ('id_8', [0.0, 2.0, 3.0, 2.0, 2.0]);
insert into dataset.test_data values ('id_9', [1.0, 2.0, 0.0, 2.0, 0.0]);
insert into dataset.test_data values ('id_10', [1.0, 0.0, 3.0, 1.0, 0.0]);

■モデル作成

create or replace model dataset.test_model
options (
  model_type = 'pca'
  , num_principal_components = 2
  , scale_features = false
  , pca_solver = 'full'
)
as (
  select
    values[0] as f0
    , values[1] as f1
    , values[2] as f2
    , values[3] as f3
    , values[4] as f4
    , values[5] as f5
  from
    dataset.test_data
);

■適用

select
  *
from
  ml.predict(model dataset.test_model,
  (
    select
      id
      , values[0] as f0
      , values[1] as f1
      , values[2] as f2
      , values[3] as f3
      , values[4] as f4
      , values[5] as f5
      , values[6] as f6
    from
      dataset.test_data
  ),
  struct(true as keep_original_columns)
);

[{
  "principal_component_1": "-0.69271374229229665",
  "principal_component_2": "1.6535767369786081",
  "id": "id_1",
  "f0": "1.0",
  "f1": "0.0",
  "f2": "1.0",
  "f3": "3.0",
  "f4": "0.0"
}, {
  "principal_component_1": "0.31854554694562942",
  "principal_component_2": "1.5448567976194651",
  "id": "id_10",
  "f0": "1.0",
  "f1": "0.0",
  "f2": "3.0",
  "f3": "1.0",
  "f4": "0.0"
}, {
  "principal_component_1": "-0.67889365782004729",
  "principal_component_2": "-0.94631539186280522",
  "id": "id_2",
  "f0": "1.0",
  "f1": "2.0",
  "f2": "1.0",
  "f3": "1.0",
  "f4": "1.0"
}, {
  "principal_component_1": "-1.2041817682899503",
  "principal_component_2": "-1.3374039692570403",
  "id": "id_3",
  "f0": "0.0",
  "f1": "2.0",
  "f2": "0.0",
  "f3": "1.0",
  "f4": "1.0"
}, {
  "principal_component_1": "2.6487614707519027",
  "principal_component_2": "0.36207867152786921",
  "id": "id_4",
  "f0": "1.0",
  "f1": "0.0",
  "f2": "3.0",
  "f3": "2.0",
  "f4": "3.0"
}, {
  "principal_component_1": "-1.3025753320545592",
  "principal_component_2": "0.98632696171180989",
  "id": "id_5",
  "f0": "0.0",
  "f1": "0.0",
  "f2": "0.0",
  "f3": "2.0",
  "f4": "0.0"
}, {
  "principal_component_1": "2.8720208417866377",
  "principal_component_2": "-0.5153230138544187",
  "id": "id_6",
  "f0": "0.0",
  "f1": "0.0",
  "f2": "2.0",
  "f3": "2.0",
  "f4": "4.0"
}, {
  "principal_component_1": "-1.4274411393246851",
  "principal_component_2": "-0.46000228387475217",
  "id": "id_7",
  "f0": "1.0",
  "f1": "2.0",
  "f2": "1.0",
  "f3": "1.0",
  "f4": "0.0"
}, {
  "principal_component_1": "1.3995485642410159",
  "principal_component_2": "-0.88215219479355511",
  "id": "id_8",
  "f0": "0.0",
  "f1": "2.0",
  "f2": "3.0",
  "f3": "2.0",
  "f4": "2.0"
}, {
  "principal_component_1": "-1.9330707839436485",
  "principal_component_2": "-0.40564231419518071",
  "id": "id_9",
  "f0": "1.0",
  "f1": "2.0",
  "f2": "0.0",
  "f3": "2.0",
  "f4": "0.0"
}]

■固有値など

select
  *
from
  ml.principal_component_info(model dataset.test_model)
;

[{
  "principal_component_id": "0",
  "eigenvalue": "3.020722723318388",
  "explained_variance_ratio": "0.545913745178022",
  "cumulative_explained_variance_ratio": "0.545913745178022"
}, {
  "principal_component_id": "1",
  "eigenvalue": "1.147655631054062",
  "explained_variance_ratio": "0.20740764416639679",
  "cumulative_explained_variance_ratio": "0.75332138934441883"
}]

BigQuery で重複するレコードを削除

BigQuery で重複するレコードを削除する方法のメモ。
BigQuery で primary key の設定を行わないと、意図せずレコードが重複する場合があります。
そのため、各レコードに uuid を付与し、primary key 相当の id と uuid を使って重複するレコードを削除します。

■テーブル作成・データ登録
drop table if exists dataset.test_dup;

create table dataset.test_dup (
  id  string
);
insert into dataset.test_dup values ('123');
insert into dataset.test_dup values ('456');
insert into dataset.test_dup values ('456');
insert into dataset.test_dup values ('789');
insert into dataset.test_dup values ('789');
insert into dataset.test_dup values ('789');

■カラムを追加
alter table dataset.test_dup add column uuid string;
update dataset.test_dup set uuid = generate_uuid();

alter table dataset.test_dup add column min_uuid string;
update
dataset.test_dup as td
set
min_uuid = mr.min_uuid
from
(select
id
, min(uuid) as min_uuid
from
dataset.test_dup
group by
id
) as mu
where
td.id = mu.id
;

■テーブル
select
*
from
dataset.test_dup
order by
id asc
;

■レコード
id uuid min_uuid
123 133... 133...
456 1a8... 1a8...
456 f11... 1a8...
789 902... 902...
789 d68... 902...
789 cdb... 902...

■重複レコード削除
delete from
  dataset.test_dup
where
  uuid > min_uuid
;

■重複レコード削除後
id uuid min_uuid
123 133... 133...
456 1a8... 1a8...
789 902... 902...

BigQuery で "{属性名}: {属性値}, ..." の属性値を取得するユーザ関数

BigQuery で "{属性名}: {属性値}, ..." の形式の文字列から指定の属性名の属性値を取得する関数のメモ。
以下のような文字列から指定の属性名の属性値を取得するユーザ関数を作成します。

price: positive, function: negative, quality: neutral

関数定義は以下の通り。

create or replace function
  dataset.get_attribute_value(text string, name string)
returns
  string
as (
    regexp_extract(
      text
      , concat('(?:^|,[ ]*)', name, ':[ ]*([^, ]+)')
    )
);

以下で属性値を取得する。

select
  dataset.get_attribute_value('price: neutral, quality: positive', 'price') as attr1
  , dataset.get_attribute_value('price: neutral, quality: positive', 'function') as attr2;

実行結果は以下の通り。

[{
  "attr1": "neutral",
  "attr2": null
}]

BigQuery の ML.GENERATE_TEXT() で生成AIを利用

BigQuery の ML.GENERATE_TEXT() で生成AIを利用する方法のメモ。

まず以下のテーブルを作成する。

create table dataset.test (
  id    integer,
  text  string,
);

insert into dataset.test (id, text) values (1, '今日はいい気分です。');
insert into dataset.test (id, text) values (2, '今日は普通の気分です。');
insert into dataset.test (id, text) values (3, '今日は気分が悪い。');

上記のテーブルの text フィールドに対して、生成AIで回答を生成する。

select
  *
from
  ml.generate_text(
    model `dataset.ai_model`
    , (select id, text AS prompt from dataset.test)
    , struct(true as flatten_json_output)
  )
order by
  id asc
;

実行結果は以下の通り。

[{
  "ml_generate_text_llm_result": "それは素晴らしいですね！...",
  "ml_generate_text_rai_result": null,
  "ml_generate_text_status": "",
  "id": 1,
  "prompt": "今日はいい気分です。"
}, {
  "ml_generate_text_llm_result": "今日は普通の気分とのことですね。...",
  "ml_generate_text_rai_result": null,
  "ml_generate_text_status": "",
  "id": 2,
  "prompt": "今日は普通の気分です。"
}, {
  "ml_generate_text_llm_result": "気分が悪いとのこと、お察しします。...",
  "ml_generate_text_rai_result": null,
  "ml_generate_text_status": "",
  "id": 3,
  "prompt": "今日は気分が悪い。"
}]

BigQuery でベクトルの長さを計算する関数を作成

BigQuery でベクトルの長さを計算する関数を作成します。
■関数定義

create or replace function
  dataset.vector_length(v array<float64>)
returns
  float64 as (
  sqrt(
    (select
      sum(e * e)
    from
      unnest(v) as e
    )
  )
);

■実行例

select dataset.vector_length([1.0, 2.0, 3.0, 4.0]);
実行結果
[{
  "f0_": "5.4772255750516612"
}]

BigQuery でベクトル間のコサインを計算する関数を作成

BigQuery でベクトル間のコサインを計算する関数を作成します。
■関数定義

create or replace function
  dataset.vector_cosine(v1 array<float64>, v2 array<float64>)
returns
  float64
as (
  (select
    sum(e1 * e2)
  from
    unnest(v1) as e1 with offset idx1
  inner join
    unnest(v2) as e2 with offset idx2
  on
    idx1 = idx2
  ) / (dataset.vector_length(v1) * dataset.vector_length(v2))
);

dataset.vector_length() は「BigQuery でベクトルの長さを計算する関数を作成」で定義しています。
■実行例

select dataset.vector_cosine([1.0, 2.0, 3.0, 4.0], [4.0, 3.0, 2.0, 1.0]);

実行結果

[{
  "f0_": "0.66666666666666663"
}]

BigQuery でベクトルを正規化する関数を作成

BigQuery でベクトルを正規化する関数を作成します。
■関数定義

create or replace function dataset.vector_normalize(v array<float64>)
returns array<float64> as (
  dataset.vector_scalar_multiply(
    v,
    1.0 / dataset.vector_length(v)
  )
);

dataset.vector_scalar_multiply() はBigQuery でベクトルを定数倍する関数を作成に記載しています。
■実行例

select dataset.vector_normalize([1.0, 2.0, 3.0, 4.0]);

実行結果

[{
  "f0_": ["0.18257418583505536", "0.36514837167011072", "0.54772255750516607", "0.73029674334022143"]
}]

BigQuery でベクトルを定数倍する関数を作成

BigQuery でベクトルを定数倍する関数を作成します。
■関数定義

create or replace function dataset.vector_scalar_multiply(v array<float64>, a float64)
returns array<float64> as (
  array(
    select
      e * a
    from
      unnest(v) as e
  )
);

■実行例

select ikeda_ai_test.vector_scalar_multiply([1.0, 2.0, 3.0, 4.0], 2.0);

実行結果

[{
  "f0_": ["2.0", "4.0", "6.0", "8.0"]
}]

BigQuery でレコードの有効期限を設定

BigQuery でレコードの有効期限を設定する方法のメモ。
■テーブル定義・データ登録

create table dataset.test_partition1 (
  jst_date date
  , log_text string
)
partition by jst_date
options (
  partition_expiration_days = 3
  , require_partition_filter = true
);

insert into dataset.test_partition1 (jst_date, log_text) values ('2023-11-20', 'log_20231120');
insert into dataset.test_partition1 (jst_date, log_text) values ('2023-11-21', 'log_20231121');
insert into dataset.test_partition1 (jst_date, log_text) values ('2023-11-22', 'log_20231122');
insert into dataset.test_partition1 (jst_date, log_text) values ('2023-11-23', 'log_20231123');
insert into dataset.test_partition1 (jst_date, log_text) values ('2023-11-24', 'log_20231124');

■データ確認

select current_date();

[{
  "f0_": "2023-11-24"
}]

select
  *
from
  dataset.test_partition1
where
  jst_date >= '2023-11-01'
order by
  jst_date asc
;

[{"jst_date": "2023-11-21", "log_text": "log_20231121"},
 {"jst_date": "2023-11-22", "log_text": "log_20231122"},
 {"jst_date": "2023-11-23", "log_text": "log_20231123"},
 {"jst_date": "2023-11-24", "log_text": "log_20231124"}]

レコードの有効期限を変更

alter table dataset.test_partition1 set options (
  partition_expiration_days = 2
  , require_partition_filter = true)
;

select
  *
from
  dataset.test_partition1
where
  jst_date >= '2023-11-01'
order by
  jst_date asc
;

[{"jst_date": "2023-11-22", "log_text": "log_20231122"},
 {"jst_date": "2023-11-23", "log_text": "log_20231123"},
 {"jst_date": "2023-11-24", "log_text": "log_20231124"}]

BigQuery のユーザ定義関数でベクトルの重み付き和を計算

BigQuery のユーザ定義関数でベクトルの重み付き和を計算する方法のメモ。
■ユーザ定義関数

create or replace function dataset.vector_wsum_int(wvs array<struct<w integer, v array<integer>>>)
returns array<integer> as (
  array(
    select
      sum(e * w)
    from
      unnest(wvs) as wv
      , unnest(wv.v) as e with offset idx
    group by
      idx
  )
);

■クエリ

select
  dataset.vector_wsum_int([
    struct(2 as w, [1, 2, 3, 4] as v),
    struct(3 as w, [2, 3, 4, 5] as v),
    struct(4 as w, [3, 4, 5, 6] as v)
  ])
;

■実行結果

[{
  "f0_": ["20", "29", "38", "47"]
}]

BigQuery のユーザ定義関数でベクトルの各要素に重み付け

BigQuery のユーザ定義関数でベクトルの各要素に重み付けをする方法のメモ。

■ユーザ定義関数

create function dataset.vector_weight_int(v array<integer>, m integer)
returns array<integer> as (
  array(
    select
      e * m
    from
      unnest(v) as e
  )
);

■テスト

select
  dataset.vector_weight_int([1, 2, 3, 4, 5], 3)
;

■実行結果

[{
  "f0_": ["3", "6", "9", "12", "15"]
}]

BigQuery で重み付きでベクトルを加算

BigQuery で重み付きでベクトルを加算する方法のメモ。

item に重み付きで属性が付与されていて、属性がベクトルで表現されている場合に、
item のベクトルを重み付きで属性のベクトルを加算して計算する。

■item の重み付き属性

create table dataset.item_weight_attrs (
  id           string,
  attr_infos   array<struct<attr as string, weight as integer>>
);

insert into
  dataset.item_weight_attrs
  (id, attr_infos)
values
  ('id1', [struct('attr1' as attr, 1 as weight), struct('attr2' as attr, 2 as weight)])
  , ('id2', [struct('attr2' as attr, 2 as weight), struct('attr3' as attr, 1 as weight)])
  , ('id3', [struct('attr1' as attr, 3 as weight), struct('attr2' as attr, 2 as weight), struct('attr3' as attr, 1 as weight)]);

■属性のベクトル

create table dataset.attr_vectors (
  attr         string,
  vector       array<integer>
);

insert into
  dataset.attr_vectors
  (attr, vector)
values
  ("attr1", ["0", "1", "2", "3", "4"])
  , ("attr2", ["1", "2", "3", "4", "5"])
  , ("attr3", ["2", "3", "4", "5", "6"]);

■item のベクトル生成

with wvs as (
  select
    iwa.id as id
    , array_agg(
        struct(
          attr_info.weight as weight
          , attr_info.vector as vector
        )
      ) as weight_vector
  from
    dataset.item_weight_attrs as iwa
    , unnest(iwa.attr_infos) as attr_info
    , dataset.attr_vector as av
  where
    av.attr = attr_info.attr
  group by
    iwa.id
)

select
  wvs.id
  , array(
      select
        sum(e * wv.weight)
      from
        wvs as wvs2
        , unnest(wvs2.weight_vector) as wv
        , unnest(wv.vector) as e with offset idx
      where
        wvs2.id = wvs.id
      group by
        idx
    ) as sum_vector
from
  wvs
;

■item のベクトル

[{
  "id": "id1",
  "sum_vector": ["2", "5", "8", "11", "14"]
}, {
  "id": "id2",
  "sum_vector": ["4", "7", "10", "13", "16"]
}, {
  "id": "id3",
  "sum_vector": ["4", "10", "16", "22", "28"]
}]