Looker Studio、BigQuery、Vertex AI を利用した生成 AIチャットボット搭載ダッシュボード

CREATE TABLE blog_demo.table_demo AS
WITH RankedSales AS (
  SELECT
    date,
    category_name,
    ROUND(SUM(volume_sold_liters)) as total_volume_sold,
    ROW_NUMBER() OVER (PARTITION BY date ORDER BY SUM(volume_sold_liters) DESC) as rank
  FROM
    `bigquery-public-data.iowa_liquor_sales.sales`
  WHERE
    date >= "2024-05-26" AND date <= "2024-05-31"
  GROUP BY
    date, category_name
)
SELECT
  date,
  category_name,
  total_volume_sold
FROM
  RankedSales
WHERE
  rank <= 10
ORDER BY
  date, rank;

CREATE MODEL IF NOT EXISTS `blog_demo.model_demo`
    REMOTE WITH CONNECTION `us.connection_demo`
    OPTIONS(
        ENDPOINT = 'gemini-pro'
    )

WITH
table_data AS(
  SELECT
    STRING_AGG(CONCAT('date: ', CAST(date AS STRING), ', category_name: ', category_name, ', 
total_volume_sold: ', CAST(total_volume_sold AS STRING)), '\n') AS table_info
  FROM
  `blog_demo.table_demo`
)

SELECT
  STRING(unnest_ml_generate_text_result_candidates.content.parts[0].text) AS content_text
FROM
  ML.GENERATE_TEXT(
    MODEL `blog_demo.model_demo`,
    (
      SELECT
      CASE
      WHEN @prompt IS NULL OR @prompt = '' THEN ''
      ELSE CONCAT('以下のアイオワ州における日ごとのお酒の種類別の販売量（ℓ）のデータに関する質問
です。\n',@prompt,'\n',table_info)
      END AS prompt
      FROM table_data
    ),
    STRUCT(
      800 AS max_output_tokens
    )
  )
LEFT JOIN
  UNNEST(JSON_EXTRACT_ARRAY(ml_generate_text_result.candidates)) AS 
unnest_ml_generate_text_result_candidates