BigQuery で改行を含む文字列を使用する方法と、文字列に値を埋め込む方法のメモ。
文字列を ''' でくくると改行を含む文字列を使用することができます。
select ''' abc def ghi ''';
実行結果
abc def ghi
format() を使用して文字列に値を埋め込むことができます。
select format('''
string: "%s"
integer: "%d"
float: "%f"
''', 'abc', 123, 123.456);実行結果
string: "abc" integer: "123" float: "123.456000"
BigQuery での文字列を正規化する方法のメモ。
ここでは以下の文字列の正規化を行います。
normalize(string, "NFKC") で正規化を行います。
実行例
select normalize("ＡＢＣ０１２！＃＄ｶﾅ", NFKC);
ABC012!#$カナlower(string) で英大文字を小文字に変換します。
実行例
select lower("ＡＢＣABC");
ａｂｃabc正規表現で制御文字を半角空白に置換します。
select regexp_replace('a\n\r\tb', r'[\x00-\x1f\x7f]', ' ');
a bto_code_points(string) で各文字をコードポイントに変換し、 code_points_to_bytes(code_points) でさらにバイトに変換し、 to_hex(bytes) で16進数に変換してみます。
select
to_hex(code_points_to_bytes(to_code_points(
regexp_replace('a\n\r\tb', r'[\x00-\x19\x7f]', ' ')
)));
612020206261:'a'、20:' '、62:'b' のため、改行やタブを半角空白に変換できていることがわかります。
正規表現で連続する空白文字を1文字に変換します。
select regexp_replace('a b c d e', r'[ ]{2,}', ' ');
a b c d e先頭、末尾の不要な空白文字列を正規表現で削除します。
select regexp_replace(' a b c ', r'(?:^[ ]+|[ ]+$)', '');
a b ccreate or replace function
dataset.normalize_string(str string)
as (
regexp_replace(
regexp_replace(
regexp_replace(
lower(
normalize(str, NFKC)
)
, r'[\x00-\x1f\x7f]', ' '
)
, r'[ ]{2,}', ' '
)
, r'(?:^[ ]+|[ ]+$)', ''
)
);
select dataset.normalize_string(' ＡＢＣ ｶﾅ ＃！ ');
abc カナ #!
BigQuery で日本時間での日付、日時を取得する方法のメモ。
日本時間での日付、日時は current_date()、current_datetime() の引数に 'Asia/Tokyo' を指定することで取得できます。
select
current_date() as utc_date
, current_date('Asia/Tokyo') as jpn_date
, current_datetime() as utc_datetime
, current_datetime('Asia/Tokyo') as jpn_datetime
;[{
"utc_date": "2024-09-29",
"jpn_date": "2024-09-29",
"utc_datetime": "2024-09-29T02:26:20.153788",
"jpn_datetime": "2024-09-29T11:26:20.153788"
}]
Vertex AI のマルチモーダルエンベディングの実行例のメモ。
概要
外部接続の IAM に対して以下の権限を設定。
$ gsutil cp *.jpg gs://{バケット}/{パス}/以下を実行。
CREATE OR REPLACE EXTERNAL TABLE
`{プロジェク}.{データセット}.{テーブル}`
WITH CONNECTION
`{リージョン}.{接続ID}`
OPTIONS (
object_metadata = 'SIMPLE',
uris = ['gs://{バケット}/{パス}/*.jpg']
);select
regexp_extract(uri, '/([0-9]+).jpg$') as id
, ml_generate_embedding_result as image_vecctor
from
ml.generate_embedding(
model {データセット}.{テーブル},
table {データセット}.{オブジェクトテーブル},
struct(
true as flatten_json_output,
512 as output_dimensionality
)
)
;
GA4 の BigQuery のログを URL 毎に集計する方法のメモ。
select
event_date as date
, p.value.string_value as url
, count(*) as pv
from
`{ga_dataset}.events_*`
, unnest(event_params) as p
where
_table_suffix = '{YYYYmmdd}'
and event_name = 'page_view'
and p.key = 'page_location'
group by
date
, url
;select
event_date as date
, (select p.value.string_value from unnest(event_params) p where p.key = 'page_location') as url
, count(*) as pv
from
`{ga_dataset}.events_*`
where
_table_suffix = '{YYYYmmdd}'
and event_name = 'page_view'
group by
date
, url
;関数定義
event_params はデータ構造が複雑なため、引数は any type としています。
create or replace function
{dataset}.get_event_params_value (
event_params any type
, key string
)
returns struct<>
as (
(select
p.value
from
unnest(event_params) as p
where
p.key = key
)
);ログ集計 SQL
select
event_date as date
, {dataset}.get_event_params_value(event_params, 'page_location').string_value as url
, count(*) as pv
from
`{ga_dataset}.events_*`
where
_table_suffix = '{YYYYmmdd}'
and event_name = 'page_view'
and p.key = 'page_location'
group by
date
, url
;
BigQuery で実行中のジョブをキャンセルする方法のメモ。
以下でジョブ一覧を出力することができる。
bq ls --jobs
実行結果例
jobId Job Type State Start Time Duration ------------------------------ ---------- --------- ----------------- ---------------- bquxjob_3ba5c31b_190e9c1dd8a query RUNNING 25 Jul 20:58:34 bquxjob_63ac3918_190e9bf7161 query SUCCESS 25 Jul 20:55:55 0:02:00.769000 ...
以下でジョブに関する情報を表示することができる。
show --job "{project_id}:{location}.{job_id}" 実行結果例
{project_id}:bquxjob_3ba5c31b_190e9c1dd8a
Job Type State Start Time Duration User Email Bytes Processed Bytes Billed Billing Tier Labels
---------- --------- ----------------- ---------- ------------------------ ----------------- -------------- -------------- --------
query RUNNING 25 Jul 20:58:34 {user_email}
以下でジョブをキャンセルすることができる。
bq cancel "{project_id}:{location}.{job_id}"実行結果例
Waiting on bquxjob_3ba5c31b_190e9c1dd8a ... (16s) Current status: DONE
Job {projecct_id}:bquxjob_3ba5c31b_190e9c1dd8a
Job Type State Start Time Duration User Email Bytes Processed Bytes Billed Billing Tier Labels
---------- --------- ----------------- ---------------- ------------------------ ----------------- -------------- -------------- --------
query FAILURE 25 Jul 20:58:34 0:03:16.366000 {user_email} 0 0 1
Error encountered during job execution:
Job execution was cancelled: User requested cancellation
Job has been cancelled successfully.ジョブ一覧では以下のように FAILURE となる。
bq ls --jobs
jobId Job Type State Start Time Duration
------------------------------ ---------- --------- ----------------- ----------------
bquxjob_3ba5c31b_190e9c1dd8a query FAILURE 25 Jul 20:58:34 0:03:16.366000
bquxjob_63ac3918_190e9bf7161 query SUCCESS 25 Jul 20:55:55 0:02:00.769000
bquxjob_c547e29_190e9b61dd7 query SUCCESS 25 Jul 20:45:44 0:00:10.844000
BigQuery で Cloud SQL（MySQL）の sleep() を使って sleep する方法のメモ。
BigQuery で「+追加」をクリックし、「外部データソースへの接続」から DB への外部接続を設定。
上記で作成した外部接続の接続情報から、サービスアカウントIDをコピー。
「IAM と管理」メニューで「アクセス件を付与」をクリックし、「新しいプリンシパル」にコピーしたサービスアカウントIDをペースト、ロールに「Cloud SQL クライアント」を指定して「保存」。
Cloud SQL で sleep() を実行することで、BigQuery で sleep することができる。
ただし、Cloud SQL への接続や BigQuery のオーバーヘッドなどで、sleep に指定した秒数よりも実行時間は長くなる。
select
*
from
external_query(
"{外部接続の接続ID}",
"select sleep({秒数});"
)
;外部接続の接続IDは、「接続情報」の「接続ID」に表示される projects/.../locations/.../connections/... の形式か、{ロケーション}.{接続ID} の形式。
JavaScript UDF 等で sleep したかったのですが、実現できなかったため、この方法を試してみました。
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 の今後の展望を教えてください"
}][{
"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 の今後の展望を教えてください"
}]
create or replace model
{データセット}.{モデル名} # ここでは test_dataset.generative_model
remote with connection
{外部接続ID} # ここでは `us-central1.ai_remote_model`
options (
endpoint = 'gemini-pro'
);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', '雨が降っています。');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": "はい、雨が降っていますね。傘をお持ちですか？"
}]
create or replace model
{データセット}.{モデル名} # ここでは test_dataset.embedding_model
remote with connection
{外部接続ID} # ここでは `us-central1.ai_remote_model`
options (
endpoint = 'textembedding-gecko-multilingual@001'
);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', '雨が降っています。');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", ...]
}]
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"
}]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
}]
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, '今日は気分が悪い。');
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": "今日は気分が悪い。"
}]
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" }]
