博客 / 工程

使用 ClickHouse 和 Amazon Bedrock 构建 Google Analytics 的 RAG 流水线

Dale McDiarmid

2023 年 11 月 21 日 - 34 分钟阅读

在最近的一篇博客文章中，我们探讨了用户如何通过使用 ClickHouse 和 Superset 来增强其网站分析能力，从而以最低的成本提供快速且灵活的方式来查询来自 Google Analytics 的原始数据。

Superset 易于学习的曲线促使我探索 LLM 如何为技术性较低的用户提供更简单的方法来探索 Google Analytics 数据。在技术博客中广泛使用 RAG、ML 和 LLM 等首字母缩略词的情况下，我抓住了这个机会，深入研究计算机科学领域，坦率地说，我在这个领域的经验有限。这篇文章既记录了我在使用 LLM 和 RAG 来简化应用程序界面方面的旅程和实验。

我获得了相当慷慨的 AWS 账户权限，可以访问 Amazon Bedrock，于是我着手构建一个与我的原始 Google Analytics 数据进行自然语言交互的界面。这里的目标很简单：允许用户以自然语言提问，并生成适当的 SQL，最终使用底层数据回答他们的问题。如果成功，这将构成一个简单界面的基础，用户可以通过该界面提问并渲染出合理的图表。

从我们之前的博客中，回想一下我们提出了以下模式来在 ClickHouse 中保存 Google Analytics 数据

CREATE TABLE default.ga_daily
(
	`event_date` Date,
	`event_timestamp` DateTime64(3),
	`event_name` String,
	`event_params` Map(String, String),
	`ga_session_number` MATERIALIZED CAST(event_params['ga_session_number'], 'Int64'),
	`ga_session_id` MATERIALIZED CAST(event_params['ga_session_id'], 'String'),
	`page_location` MATERIALIZED CAST(event_params['page_location'], 'String'),
	`page_title` MATERIALIZED CAST(event_params['page_title'], 'String'),
	`page_referrer`  MATERIALIZED CAST(event_params['page_referrer'], 'String'),
	`event_previous_timestamp` DateTime64(3),
	`event_bundle_sequence_id` Nullable(Int64),
	`event_server_timestamp_offset` Nullable(Int64),
	`user_id` Nullable(String),
	`user_pseudo_id` Nullable(String),
	`privacy_info` Tuple(analytics_storage Nullable(String), ads_storage Nullable(String), uses_transient_token Nullable(String)),
	`user_first_touch_timestamp` DateTime64(3),
	`device` Tuple(category Nullable(String), mobile_brand_name Nullable(String), mobile_model_name Nullable(String), mobile_marketing_name Nullable(String), mobile_os_hardware_model Nullable(String), operating_system Nullable(String), operating_system_version Nullable(String), vendor_id Nullable(String), advertising_id Nullable(String), language Nullable(String), is_limited_ad_tracking Nullable(String), time_zone_offset_seconds Nullable(Int64), browser Nullable(String), browser_version Nullable(String), web_info Tuple(browser Nullable(String), browser_version Nullable(String), hostname Nullable(String))),
	`geo` Tuple(city Nullable(String), country Nullable(String), continent Nullable(String), region Nullable(String), sub_continent Nullable(String), metro Nullable(String)),
	`app_info` Tuple(id Nullable(String), version Nullable(String), install_store Nullable(String), firebase_app_id Nullable(String), install_source Nullable(String)),
	`traffic_source` Tuple(name Nullable(String), medium Nullable(String), source Nullable(String)),
	`stream_id` Nullable(String),
	`platform` Nullable(String),
	`event_dimensions` Tuple(hostname Nullable(String)),
	`collected_traffic_source` Tuple(manual_campaign_id Nullable(String), manual_campaign_name Nullable(String), manual_source Nullable(String), manual_medium Nullable(String), manual_term Nullable(String), manual_content Nullable(String), gclid Nullable(String), dclid Nullable(String), srsltid Nullable(String)),
	`is_active_user` Nullable(Bool)
)
ENGINE = MergeTree
ORDER BY (event_timestamp, event_name, ga_session_id)

此表保存每个 GA4 事件的行，从中可以使用正确的 SQL 问题推导出诸如“显示过去 30 天博客文章的回访用户”之类的问题。

虽然我们预计针对我们 GA 数据的大多数问题都将像上面的示例一样结构化，但我们也希望能够根据概念回答更细致的问题。例如，假设我们想要“基于物化视图访问页面的新用户随时间推移的总数”。这个问题的大部分是结构化的，需要精确的列匹配和 GROUP BY 日期。“物化视图”的概念稍微细致一些。考虑到一个延伸目标，如果我们能找到一种将概念表示为更具体事物的方法，用户可能会对其进行改进，这似乎是可以实现的。

在这一点上，假设大多数读者都使用过或听说过大型语言模型 (LLM) 和生成式 AI，甚至可能使用过 ChatGPT 等服务，这些服务将它们作为服务公开，这可能是合理的。这些服务已被证明对各种应用程序都很有用，包括需要编写代码片段的技术用户。当提供模式和问题时，SQL 生成效果非常好。在 ClickHouse，我们实际上已经利用了这些功能，在我们的 ClickHouse Cloud SQL 控制台中公开了使用自然语言查询的功能。

虽然 ChatGPT 通常足以入门，但当我们想要大规模构建某些东西或将这些功能集成到生产应用程序中时，探索云提供商中的同等服务总是很有意义的。对于 AWS，Bedrock 服务通过将基础机器学习模型（包括 LLM）作为完全托管的服务公开来提供这些功能。这些模型由 Anthropic 和 Cohere 等公司贡献，并通过简单的 API 提供。这代表了我入门应用程序的最简单方法。

RAG 这个术语目前似乎也很流行，一些公司押注于其需求的普遍性，以至于出现了一整类数据库来为此服务。检索增强生成 (RAG) 是一种旨在将预训练语言模型的强大功能与信息检索系统的优势相结合的技术。这里的目标通常很简单：通过向模型提供额外信息来提高生成文本的质量和相关性。这属于提示工程领域，我们修改我们的问题或提示以包含更多有用的信息，以帮助 LLM 制定更准确的答案。

通过向 LLM 提供额外的支持信息，RAG 旨在克服用户在依赖模型生成文本时经常遇到的一些问题，其中大多数问题是 LLM 的内部知识基于来自某个时间点的固定训练语料库的结果。在无法访问特定领域或最新的信息的情况下，当问题超出此基础知识时，模型可能会陷入困境，导致没有答案或“幻觉”，即响应并非基于现实且在事实上不正确。

为了为我们的 Google Analytics 用例向模型提供额外的支持信息，我们需要一种查找与当前问题在上下文中相关的内容的方法。在之前的博客文章中，我们探讨了如何使用向量嵌入来完成此操作。为此，我们可以使用另一个模型为我们将用作上下文的文档（例如，内部文档）生成嵌入，并将这些嵌入存储在数据库中。然后，我们可以使用相同的模型将用户的自然语言问题转换为嵌入，并检索最相关的内容（例如，前 10 个文档），以在向我们的 LLM 模型/服务发出请求时用作上下文。

虽然专用向量数据库专门为此功能而设计，但像 ClickHouse 这样的数据库也原生支持向量功能。这意味着 ClickHouse 可以同时充当向量数据库和分析数据库，这通过消除 RAG 流水线中对多个定制数据存储的需求，简化了整体架构。

这种检索和生成两步过程确实是对 RAG 的简化概述（尤其是忽略了检索文档相关性的最大挑战），但足以满足我们的需求。

正如我们将在下面发现的那样，对于我们的用例，RAG 也很重要，因为我们不仅需要提供表模式，还需要提供可能的问题示例，例如如何识别回访用户，或者 LLM 应如何处理不太结构化的问题，例如“关于物化视图的文章”。

我们应用程序的基础是文本到 SQL。这是一个活跃的研究领域，并且由于 Spider：耶鲁语义解析和文本到 SQL 挑战赛，相对容易看到哪些方法在提供最佳准确性方面处于领先地位。

“Spider 挑战赛的目标是开发跨域数据库的自然语言界面。它包含 10,181 个问题和 5,693 个独特的复杂 SQL 查询，涉及 200 个数据库，这些数据库包含 138 个不同的领域。为了在该挑战赛中取得好成绩，系统必须很好地推广到新的 SQL 查询和新的数据库模式。”

这个特定的挑战比我们的 Google Analytics 问题到文本问题更通用 - 我们的问题是特定于领域的，而 Spider 旨在衡量跨域零样本性能的性能。尽管如此，领先的论文似乎值得探索以获取灵感。令人欣慰的是，当前的领导者，包括 DAIL-SQL，都依赖于使用提示工程和基于 RAG 的方法原则。

DAIL-SQL 的主要创新之处在于，在可能使用的提示问题集（“候选集”）中屏蔽关键字（例如列和值），并为该问题生成嵌入。然后使用专门的预训练转换器模型生成（和屏蔽）“骨架”查询。提示的示例问题反过来通过将问题及其骨架 SQL 与候选问题及其 SQL 进行比较来识别。有关更详细的概述，请参阅此处。这非常简单有效，并为获得不错的结果提供了一些有用的指导。

虽然我不需要上述过程的跨域功能的灵活性，但它确实为获得良好的结果提供了一些提示。为类似问题提供 SQL 示例对于获得良好的性能似乎至关重要 - 尤其是在我们的领域中，像“回访用户”这样的概念不容易从模式中推断出来。但是，考虑到我的问题的特定领域性质，问题的屏蔽和 SQL 查询的比较似乎是不必要的。

最重要的是，很明显问题应该分解为多个步骤。

鉴于上述研究，我们的 RAG 流不可避免地需要我们能够为文本生成嵌入（例如，查找相似的问题）。为此，我们利用一个简单的 Python UDF embed.py，可以在查询或插入时调用它。这利用了 Amazon 的 titan-embed-text-v1 模型，该模型可通过 Bedrock 获得。embed.py 的主要代码如下所示，完整的支持文件请参见此处

#!/usr/bin/python3
import json
import sys
from bedrock import get_bedrock_client
from tenacity import (
   retry,
   stop_after_attempt,
   wait_random_exponential,
)
import logging
logging.basicConfig(filename='embed.log', level=logging.INFO, format='%(asctime)s - %(levelname)s - %(message)s')

bedrock_runtime = get_bedrock_client(region="us-east-1", silent=True)

accept = "application/json"
contentType = "application/json"
modelId = "amazon.titan-embed-text-v1"
char_limit = 10000  # 1500 tokens effectively


@retry(wait=wait_random_exponential(min=1, max=60), stop=stop_after_attempt(20))
def embeddings_with_backoff(**kwargs):
   return bedrock_runtime.invoke_model(**kwargs)


for size in sys.stdin:
   # collect batch to process
   for row in range(0, int(size)):
       try:
           text = sys.stdin.readline()
           text = text[:char_limit]
           body = json.dumps({"inputText": text})
           response = embeddings_with_backoff(
               body=body, modelId=modelId, accept=accept, contentType=contentType
           )
           response_body = json.loads(response.get("body").read())
           embedding = response_body.get("embedding")
           print(json.dumps(embedding))
       except Exception as e:
           logging.error(e)
           print(json.dumps([]))
   sys.stdout.flush()

相关的 ClickHouse 配置位于 /etc/clickhouse-server/ 下

<functions>
   <function>
       <name>embed</name>
        <type>executable_pool</type>
        <pool_size>3</pool_size>
        <send_chunk_header>true</send_chunk_header>
        <format>TabSeparated</format>
        <return_type>Array(Float32)</return_type>
        <argument>
          <type>String</type>
        </argument>
        <command>embed.py</command>
        <command_read_timeout>10000000</command_read_timeout>
        <command_write_timeout>10000000</command_write_timeout>
        <max_command_execution_time>1000000</max_command_execution_time>
   </function>
</functions>

关于以上内容的一些要点

我们使用 tenacity 库来确保我们重试失败的调用并进行退避。这对于我们达到 Bedrock 的配额限制时至关重要，无论是在令牌吞吐量还是每分钟 API 调用次数方面。请注意，Bedrock 允许配置请求以获得更高的吞吐量工作负载。
我们使用可执行池来确保可以一次发出多个请求。这有效地启动了 N 个（配置中为 3 个）Python 进程，允许请求并行化。
参数 send_chunk_header 确保一次将多个行值发送到 UDF。这包括指示行数的初始行。因此，我们首先从 stdin 读取它，然后再迭代行。这对于性能至关重要。
我们的 UDF 接收文本并响应 Float32 数组。

要调用此 UDF，我们只需运行

SELECT embed('some example text')
FORMAT Vertical

Row 1:
──────
embed('some example text'): [0.5078125,0.09472656,..,0.18652344]

1 row in set. Elapsed: 0.444 sec.

此函数也可以在插入时调用。以下 INSERT INTO SELECT 从表 site_pages_raw 读取行，并在 content 字段上调用 embed 函数。

INSERT INTO pages SELECT url, title, content, embed(content) as embedding FROM site_pages_raw SETTINGS  merge_tree_min_rows_for_concurrent_read = 1, merge_tree_min_bytes_for_concurrent_read=0, min_insert_block_size_rows=10, min_insert_block_size_bytes=0

请注意以下关于上述设置的信息

设置 merge_tree_min_rows_for_concurrent_read = 1 和 merge_tree_min_bytes_for_concurrent_read = 0 确保表 temp 并行读取。当源表中的行数很少时，通常需要这些设置。这确保了我们的 UDF 不会被单个线程调用。行数和字节数大于默认值 163840 和 251658240 的较大表不需要这些设置。但是，用户通常会为较小的表生成嵌入，因此这些设置很有用。有关这些设置的更多详细信息，请参阅此视频。
设置 min_insert_block_size_rows=10 和 min_insert_block_size_bytes=0 确保我们使用更少的行调用 UDF。鉴于调用 Bedrock 时吞吐量通常为每秒 10-20 行，上述值确保行以合理的速率出现在目标表中。更高的吞吐量，例如从调用本地模型获得，将需要将这些设置增加到 128 到 4096（需要测试）。有关这些设置的更多详细信息，请参阅此处。如果对嵌入执行大量读取，则设置 max_block_size 相关。

最终流程如下所示。RAG 流的组件在很大程度上是独立组装的，并经过测试以确保每个组件都提供准确的结果。

总而言之，我们的流程由两个主要阶段组成，以协助提示生成：概念处理和指标提取。

概念提取尝试识别问题是否包含概念过滤器，并执行步骤 (1) 到 (5)

我们首先使用 anthropic.claude-v2 模型和一个简单的提示来识别问题是否包含概念。如果没有，请转到指标提取和步骤 (6)。
使用 amazon.titan-embed-text-v1 模型将概念转换为嵌入。
使用步骤 (2) 中的嵌入在 ClickHouse 中搜索前 3 个最相关的页面。
提示 anthropic.claude-v2 模型从步骤 (3) 返回的页面中提取关键词和短语
修改原始问题，将概念与上述词语关联起来。

指标提取旨在识别问题需要计算的主要指标，以确保将相关示例添加到提示中。这需要执行步骤 (6) 到 (9)

通过提示 anthropic.claude-v2 模型提取 GA 指标。
使用 amazon.titan-embed-text-v1 模型将指标转换为嵌入。
使用步骤 (7) 中的嵌入查询每个指标的前匹配问题。将这些添加到示例集中。
使用简单的正则表达式来确定是否以站点的某个区域为目标，并添加示例。

最后，我们生成提示

为 anthropic.claude-v2 模型生成最终 SQL 提示，以生成 SQL。针对站点页面和 ga_daily 表执行此操作。

如上所述，我们也希望具有处理概念性问题的灵活性，例如“关于 X 的页面”。为此，我们需要上下文信息的文本内容，对于我们的用例，这些内容是我们网站上的页面。虽然编写通用爬虫可能具有挑战性，但对于我们利用站点地图的有限站点来说，一个基于 Scrapy 的简单爬虫[1][2]和页面结构应该绰绰有余。该代码应可重用，作为那些希望在其自己的站点上实施类似服务的人的基础。

要将页面与概念进行比较，我们需要一种方法来

从问题中提取概念。
表示概念，以便可以识别相关页面。

对于 (1)，我们尝试了多种技术，但发现 Bedrock 中提供的 anthropic.claude-v2 模型 非常有效。通过包含示例的适当的提示，这证明在提取目标概念方面是准确的。

对于 (2)，我们首先识别与步骤 (1) 中提取的概念相关的页面。为此，我们根据每个站点页面的文本内容以及概念本身创建并存储嵌入。给定一个源表 source_pages，其中包含 url、raw_title 和 raw_content 列，如下所示，我们可以通过在插入时调用 embed 函数将行插入到目标表 site_pages。请注意，我们还使用 extractTextFromHTML 函数从页面中提取内容，并将嵌入生成限制为 300 个令牌（使用 tokens 函数清理文本）。

CREATE TABLE source_pages
(
	`url` String,
	`raw_title` String,
	`raw_content` String
)
ENGINE = MergeTree
ORDER BY url

CREATE TABLE site_pages
(
	`url` String,
	`raw_title` String,
	`raw_content` String,
	`title` String,
	`content` String,
	`embedding` Array(Float32)
)
ENGINE = MergeTree
ORDER BY url

INSERT INTO site_pages
SELECT url, raw_title, raw_content, extractTextFromHTML(raw_title) AS title, extractTextFromHTML(raw_content) AS content, embed(arrayStringConcat(arraySlice(tokens(concat(title, content)), 1, 300), ' ')) AS embedding
FROM source_pages
SETTINGS merge_tree_min_rows_for_concurrent_read = 1, merge_tree_min_bytes_for_concurrent_read = 0, min_insert_block_size_rows = 10, min_insert_block_size_bytes = 0

0 rows in set. Elapsed: 390.835 sec. Processed 2.95 thousand rows, 113.37 MB (7.55 rows/s., 290.08 KB/s.)
Peak memory usage: 266.70 MiB.

我们可以使用以下查询搜索此表，该查询使用之前的 embed 函数使用 titan 模型为我们的概念创建嵌入。我们发现 cosineDistance 在此模型中提供了最佳结果

SELECT url, title, content FROM site_pages ORDER BY cosineDistance(embedding, embed('<concept>')) ASC LIMIT 3

使用 3 个最相关页面的聚合文本，我们反过来提取主要短语和关键词（再次使用 anthropic.claude-v2 模型）。这些短语和关键词将作为示例问题和答案添加到提示中。这是我们特定领域的上下文，将有助于 LLM 更准确地执行我们的用例。例如，概念“字典”可能会导致以下内容添加到提示中

/* Answer the following: To filter by pages containing words: */
 SELECT page_location FROM ga_daily WHERE page_location IN (SELECT url FROM site_pages WHERE content ILIKE 'dictionary engine' OR content ILIKE 'dictionary functions' OR content ILIKE 'dictionary' OR content ILIKE 'dictionary configuration' OR content ILIKE 'cache' OR content ILIKE 'dictionary updates' OR content ILIKE 'dictionaries')

此外，我们重写原始问题以表明该概念涉及按词语进行过滤。

What are the number of new users for blogs about dictionaries over time? 

becomes…

What are the number of new users for blogs about dictionaries over time? For the topic of dictionaries, filter by dictionary configuration,dictionaries,dictionary functions,cache,dictionary engine,dictionary,dictionary updates

这里的基本思想是，我们将这些短语作为最能代表目标概念的短语呈现给用户，可能还会附带匹配的页面。他们可以反过来选择根据结果添加或删除短语和词语。虽然这不一定是表示概念的唯一方法，但它的优点是将诸如概念之类的松散想法变成了具体的东西。这种方法也比另一种明显的替代方案更简单：只需向主查询添加过滤器，该过滤器根据与概念嵌入的距离函数来过滤页面。后一种方法要求我们要么定义一个最低分数（需要确定），要么仅使用前 K 个。这两种方法对最终用户来说似乎都不现实且无法解释。

根据研究，将问题添加到提示中似乎对于获得良好的结果至关重要。鉴于许多 Google Analytics 指标（例如“回访用户”或“总会话数”）无法轻易从模式中推断出来，这一点似乎更加重要。例如，正如我们之前的博客中所示，我们估计“回访用户”指标的查询如下

SELECT event_date, uniqExact(user_pseudo_id) AS returning_users
FROM ga_daily
WHERE (event_name = 'session_start') AND is_active_user AND (ga_session_number > 1 OR user_first_touch_timestamp < event_date)
GROUP BY event_date
ORDER BY event_date ASC

仅从模式中推断出此特定查询对于 LLM 来说将是具有挑战性的。因此，我们的目标是在需要时在提示中提供此上下文。例如

/*Answer the following: find returning users:*/
SELECT uniqExact(user_pseudo_id) AS returning_users FROM ga_daily WHERE (event_name = 'session_start') AND is_active_user AND (ga_session_number > 1 OR user_first_touch_timestamp < event_date)

根据一些初步实验，还变得明显的是，在提示中将所有可能的指标作为问题及其 SQL 示例提供会降低生成文本的预测质量。较长的提示会导致关键细节被忽略，并且速度也较慢，消耗更多令牌。因此，我们选择了一个简单的补充表，其中包含问题、其 SQL 响应以及问题本身的嵌入

CREATE TABLE default.questions
(
  `question` String,
  `query` String,
  `embedding` Array(Float32)
)
ENGINE = MergeTree
ORDER BY question

我们可以使用与以前相同的 UDF 嵌入函数来填充它。

初始实验使用以下查询来识别前 3 个最相关的示例问题

SELECT question, query FROM questions ORDER BY cosineDistance(embedding, embed("Show me returning users over the last 30 days for blog posts")) ASC LIMIT 3

虽然很简单，但这交付的结果并不令人满意，结果并不总是包括所需的 GA 指标。我们需要一种方法来提取

问题中包含的特定 Google Analytics 指标，例如“回访用户”。
问题是否以站点的某个区域为目标，例如博客或文档。这在我们的用例中可能很常见，并且会转换为 url 上的特定过滤器。这种“领域知识”也无法从模式中推断出来。

对于此任务，我们重用 Claude 模型来提取查询中的关键指标使用带有示例的提示。这将返回关键指标，这些关键指标反过来嵌入并用于识别适当的问题。

例如，对于问题“doc 页面 10 月份每天的回访用户数是多少？”，我们提取指标“回访用户”，从而得到以下查询

SELECT question, query FROM questions ORDER BY cosineDistance(embedding, embed("returning users")) ASC LIMIT 1

匹配的问题及其随附的 SQL 反过来被添加到我们的最终提示中。最后，如果问题按博客或文档进行过滤（通过简单的正则表达式匹配建立），我们会添加一个如何按此站点区域进行过滤的示例。

考虑到早期的研究强调了提示结构的重要性，我们还尝试遵循 claude 模型的 anthropic 文档中规定的指南。这包括一个用于模型提示工程的优秀演示文稿，从中以下内容似乎与我的任务相关

遵循所需的人工/助手结构。
利用 XML 标签在问题中提供提示结构，并允许分隔响应，例如在 <example> 标签中提供示例，在 <schema> 标签中提供模式。
提供作为模式规则的附加信息。
提示模式的排序。这包括确保对输出格式和问题的请求位于提示的底部。

考虑到这一点，我们的提示看起来像这样

Human: You have to generate ClickHouse SQL using natural language query/request <request></request>. Your goal -- create accurate ClickHouse SQL statements and help the user extract data from ClickHouse database. You will be provided with rules <rules></rules>, database schema <schema></schema> and relevant SQL statement examples </examples></examples>.

This is the table schema for ga_daily.

<schema>
CREATE TABLE ga_daily
(
	`event_date` Date,
	`event_timestamp` DateTime64(3),
	`event_name` Nullable(String),
	`event_params` Map(String, String),
	`ga_session_number` MATERIALIZED CAST(event_params['ga_session_number'], 'Int64'),
	`ga_session_id` MATERIALIZED CAST(event_params['ga_session_id'], 'String'),
	`page_location` MATERIALIZED CAST(event_params['page_location'], 'String'),
	`page_title` MATERIALIZED CAST(event_params['page_title'], 'String'),
	`page_referrer`  MATERIALIZED CAST(event_params['page_referrer'], 'String'),
	`event_previous_timestamp` DateTime64(3),
	`event_bundle_sequence_id` Nullable(Int64),
	`event_server_timestamp_offset` Nullable(Int64),
	`user_id` Nullable(String),
	`user_pseudo_id` Nullable(String),
	`privacy_info` Tuple(analytics_storage Nullable(String), ads_storage Nullable(String), uses_transient_token Nullable(String)),
	`user_first_touch_timestamp` DateTime64(3),
	`device` Tuple(category Nullable(String), mobile_brand_name Nullable(String), mobile_model_name Nullable(String), mobile_marketing_name Nullable(String), mobile_os_hardware_model Nullable(String), operating_system Nullable(String), operating_system_version Nullable(String), vendor_id Nullable(String), advertising_id Nullable(String), language Nullable(String), is_limited_ad_tracking Nullable(String), time_zone_offset_seconds Nullable(Int64), browser Nullable(String), browser_version Nullable(String), web_info Tuple(browser Nullable(String), browser_version Nullable(String), hostname Nullable(String))),
	`geo` Tuple(city Nullable(String), country Nullable(String), continent Nullable(String), region Nullable(String), sub_continent Nullable(String), metro Nullable(String)),
	`app_info` Tuple(id Nullable(String), version Nullable(String), install_store Nullable(String), firebase_app_id Nullable(String), install_source Nullable(String)),
	`traffic_source` Tuple(name Nullable(String), medium Nullable(String), source Nullable(String)),
	`stream_id` Nullable(String),
	`platform` Nullable(String),
	`event_dimensions` Tuple(hostname Nullable(String)),
	`collected_traffic_source` Tuple(manual_campaign_id Nullable(String), manual_campaign_name Nullable(String), manual_source Nullable(String), manual_medium Nullable(String), manual_term Nullable(String), manual_content Nullable(String), gclid Nullable(String), dclid Nullable(String), srsltid Nullable(String)),
	`is_active_user` Nullable(Bool)
)
ENGINE = MergeTree
ORDER BY event_timestamp
</schema>

This is the table schema for site_pages.

<schema>
CREATE TABLE site_pages
(
	`url` String,
	`title` String,
	`content` String
)
ENGINE = MergeTree
ORDER BY url
</schema>

<rules>
You can use the tables "ga_daily" and "site_pages".  

The table ga_daily contains website analytics data with a row for user events. The following columns are important:
	- event_name - A string column. Filter by 'first_visit' if identifying new users, 'session_start' for returning users and 'page_view' for page views.
	- event_date - A Date column on which the event occured
	- event_timestamp - A DateTime64(3) with the event time to milli-second accuracy
	- ga_session_id - A string identifying a user session.
	- ga_session_number - The session number for the user
	- user_pseudo_id - A string uniquely identifying a user
	- is_active_user - A boolean indicating if the user was active. True if active or engaged.
	- user_first_touch_timestamp - The first time a user visited the site.
	- page_location - the full url of the page.
	- page_title - The page title.
	- page_referer - The referer for the page. A full url.
	- traffic_source.name provides the source of the traffic.
</rules>

<examples>
/*Answer the following: find new users:*/
SELECT count() AS new_users FROM ga_daily WHERE event_name = 'first_visit'

/*Answer the following: filter by blogs:*/
SELECT page_location FROM ga_daily WHERE page_location LIKE '%/blog/%'

/* Answer the following: To filter by pages containing words: */
 SELECT page_location FROM ga_daily WHERE page_location IN (SELECT url FROM site_pages WHERE content ILIKE 'cache' OR content ILIKE 'dictionary' OR content ILIKE 'dictionaries' OR content ILIKE 'dictionary configuration' OR content ILIKE 'dictionary updates' OR content ILIKE 'dictionary functions' OR content ILIKE 'dictionary engine')

</examples>

<request> Considering all above generate a ClickHouse SQL statement for the following query:
<query>What are the number of new users for blogs about dictionaries over time?. For the topic of dictionaries, filter by cache,dictionary,dictionaries,dictionary configuration,dictionary updates,dictionary functions,dictionary engine</query></request>

Put result statement in <sql></sql> tags:

根据 Anthropic 的建议，我们将模型温度设置为 0，以便在重复试验中提供更一致的结果。top_k 的值 100 似乎提供了相当一致的结果，并避免了注入属于长尾的随机术语。

所以问题是，它的表现如何？为了评估这一点，我们创建了一个问题测试。下面，我们展示了生成的问题以及这是否产生了预期的答案。

以上不是广泛的测试集。生产系统将需要更完整的问题集，以及一个测试框架来评估查询的响应与权威响应（测试响应似乎更合适，因为问题生成可能会有所不同）。

问题	生成的响应	正确
2023 年 10 月 doc 页面每天的回访用户数是多少？	`sql SELECT event_date, uniqExact(user_pseudo_id) AS returning_users FROM ga_daily WHERE event_name = 'session_start' AND is_active_user AND (ga_session_number > 1 OR user_first_touch_timestamp < event_date) AND event_date BETWEEN '2023-10-01' AND '2023-10-31' AND page_location LIKE '%/docs/%' GROUP BY event_date ORDER BY event_date`	是
关于字典的博客的新用户随时间推移的数量是多少？	`sql SELECT toStartOfMonth(event_date) AS month, count() AS new_users FROM ga_daily WHERE event_name = 'first_visit' AND page_location IN ( SELECT url FROM site_pages WHERE content ILIKE '%dictionary%' OR content ILIKE '%dictionary functions%' OR content ILIKE '%dictionaries%' OR content ILIKE '%dictionary configuration%' OR content ILIKE '%dictionary engine%' OR content ILIKE '%cache%' OR content ILIKE '%dictionary updates%') GROUP BY month ORDER BY month`	是
自 2023 年 1 月起，URL 包含“/docs/en”的页面的每月总会话数是多少？	`sql SELECT toStartOfMonth(event_date) AS month, uniqExact(ga_session_id, '_', user_pseudo_id) AS total_sessions FROM ga_daily WHERE page_location LIKE '%/docs/en%' AND event_date >= '2023-01-01' GROUP BY month ORDER BY month`	是
随时间推移的总页面浏览量是多少？	`sql SELECT toStartOfDay(event_date) AS day, count() AS total_page_views FROM ga_daily WHERE event_name = 'page_view' GROUP BY day ORDER BY day`	是
有多少活跃用户访问过关于编解码器和压缩技术的博客？	`sql SELECT count(DISTINCT user_pseudo_id) AS active_users FROM ga_daily WHERE ((event_name = 'session_start') AND is_active_user) OR (event_name = 'first_visit') AND page_location IN ( SELECT url FROM site_pages WHERE content ILIKE ANY ('%codecs%', '%zstd%', '%lz4%', '%techniques%', '%compression%') )`	是
随时间推移的总用户数是多少？	`sql SELECT toDate(event_timestamp) AS date, uniq(user_pseudo_id) AS total_users FROM ga_daily WHERE event_name = 'session_start' GROUP BY date ORDER BY date`	是
关于物化视图的页面随时间推移的总用户数是多少？	`sql SELECT toStartOfMonth(event_date) AS month, uniq(user_pseudo_id) AS total_users FROM ga_daily WHERE event_name = 'session_start' AND page_location IN ( SELECT url FROM site_pages WHERE content ILIKE '%monthly aggregated%' OR content ILIKE '%materialized views%' OR content ILIKE '%system tables%' OR content ILIKE '%materialized%' OR content ILIKE '%materialized view%' OR content ILIKE '%queries%' OR content ILIKE '%yearly aggregated%' ) GROUP BY month ORDER BY month`	是
随时间推移的流量来源是什么？	`sql SELECT toStartOfHour(event_timestamp) AS hour, traffic_source.name AS source, count(*) AS visits FROM ga_daily WHERE event_name = 'session_start' GROUP BY hour, source ORDER BY hour`	是，但问题很模糊，例如时间单位。
关于 Snowflake 的页面的网站总会话数是多少？	`sql SELECT count(DISTINCT ga_session_id, '_', user_pseudo_id) AS total_sessions FROM ga_daily WHERE page_location IN ( SELECT url FROM site_pages WHERE content ILIKE '%snowflake%' OR content ILIKE '%cloud data warehouse%' OR content ILIKE '%real-time analytics%' OR content ILIKE '%materialized views%' OR content ILIKE '%micro-partitions%' )`	是
随着时间的推移，每篇博文的平均浏览量是多少？	`sql SELECT toStartOfMonth(event_date) AS month, page_title, count() AS views FROM ga_daily WHERE page_location LIKE '%/blog/%' AND event_name = 'page_view' GROUP BY month, page_title ORDER BY month, views DESC`	是的。page_title 可能不是唯一的，但用户可以优化。
访问过标题为“Supercharging your large ClickHouse data loads - Tuning a large data load for speed?”的博客的新用户有多少？	`sql SELECT count() AS new_users FROM ga_daily WHERE event_name = 'first_visit' AND page_location IN ( SELECT url FROM site_pages WHERE content ILIKE '%clickhouse%' OR content ILIKE '%loads%' OR content ILIKE '%large%' OR content ILIKE '%speed%' OR content ILIKE '%data loads%' OR content ILIKE '%supercharging%' OR content ILIKE '%tuning%' OR content ILIKE '%data%' AND title = 'Supercharging your large ClickHouse data loads - Tuning a large data load for speed?' )`	是的。由于对概念的错误识别，不必要地对内容进行了过滤。
2023 年 10 月新用户与回访用户的比例是多少？	`sql WITH ( SELECT count() AS new_usersFROM ga_daily WHERE (event_name = 'first_visit') AND (toYYYYMM(event_date) = 202310) ) AS new_users, ( SELECT uniqExact(user_pseudo_id) AS returning_users FROM ga_daily WHERE (event_name = 'session_start') AND is_active_user AND ((ga_session_number > 1) OR (user_first_touch_timestamp < event_date)) AND (toYYYYMM(event_date) = 202310) ) AS returning_users SELECT if(new_users = 0, 0, returning_users / new_users) AS new_to_returning_ratio`	是
每天每个回访用户对文档页面的平均浏览量是多少？	`sql SELECT user_pseudo_id, event_date, count() AS page_views FROM ga_daily WHERE event_name = 'page_view' AND page_location LIKE '%/docs/%' AND is_active_user = 1 GROUP BY user_pseudo_id, event_date ORDER BY user_pseudo_id, event_date`	否。这计算的是每天的平均用户浏览量。未筛选回访用户。
从 2003 年 9 月起，每天发布了多少篇博文？	`sql SELECT toDate(event_timestamp) AS event_date, count(*) AS blog_posts_published FROM ga_daily WHERE page_location LIKE '%/blog/%' GROUP BY event_date HAVING event_date >= '2003-09-01' AND event_date <= '2003-09-30'`	否。没有发布的概念。

从这个结果样本中得出的一些观察结果

当问题可以通过 GROUP BY 解决，并且目标是单个 Google Analytics 指标时，提供的上下文似乎效果良好。
基于将概念定义为一组词语，通过概念进行过滤似乎效果良好，因为它被实现为子查询。任何界面都希望要求用户优化这些词语，因为它们充其量只是对概念的估计。
ClickHouse 文档显然是 LLM 模型训练的一部分，因为使用了像 toStartOfMonth 这样的函数，尽管提示中没有出现。但是，额外的基于文档的上下文可能很有用。
当时间段模糊时，例如“随着时间的推移，流量来源是什么？”，该模型提供了不错的尝试，使用了时间间隔。这可以通过提示修改来解决。
即使没有提供示例问题，也能提供正确的答案，例如，用于识别流量来源。在这种情况下，从架构和附加上下文中推断出适当的列。
该模型在后两个查询中遇到了困难。“每天每个回访用户对文档页面的平均浏览量是多少？”结合了两个 GA 指标，需要一个子查询来识别回访用户，并按此集合过滤每个用户的平均浏览量。但是，生成的查询是计算（使用不正确的查询）每天用户的平均浏览量。这个查询在概念上具有挑战性，可能受益于微调。
我们的后一个问题由于上下文不足而失败。我们没有示例问题展示如何计算博客的发布日期。

从本次实施中获得的最大经验教训是将 RAG 流程分解为多个步骤。在我们上面的流程中，我们分别使用基础模型来识别概念和指标，以确保在提示中提供适当的上下文。

上面的提示也经过了一些迭代以确保其正确性。提示结构和遵守 Anthropic 文档的指南被证明至关重要——尤其是提示排序和 XML 标签的使用。在不遵守这些标准的情况下，我们发现信息会被忽略——特别是示例。提示工程和改进的过程是高度迭代的，实际上，需要一个包含各种示例测试问题的测试工具。没有这个，这个过程可能会非常令人沮丧。

总的来说，上述性能是有希望的，但很明显，当无法为特定问题提供示例查询时，这种方法就会遇到困难。具体来说，我们缺乏有助于构建更复杂的连接查询或子过滤的示例问题。这些更难作为问题进行通用捕获，因为我们的大多数示例只是展示如何计算指标。接下来的步骤可能是捕获各种示例问题。然后，可以将这些问题最接近的概念匹配项作为示例添加到现有示例中，现有示例基于显式指标。

问题明确且为用户提供关于如何构建问题的指南显然非常重要。我们的 RAG 管道目前非常慢，有多个步骤可能可以使用更轻量级的模型和/或更少的令牌。模型改进也可能在许多步骤中提高准确性。模型改进也可能值得在每个步骤中探索，以提高性能和准确性。

当前的准确性不足以让应用程序自动渲染图表。因此，我们已决定探索一种方法，其中生成的 SQL 除了图表外还会显示给用户，以及添加到提示中的上下文。这将符合我们用户可能需要优化用于表示概念的词语的需求。虽然这可能无法解决我们最初的问题，但我们怀疑不熟悉数据集的用户可能仍然会发现这种方法的价值。

开始使用 ClickHouse Cloud，今天就可获得 300 美元赠金。在 30 天试用期结束时，继续使用按需付费计划，或联系我们，详细了解我们的批量折扣。访问我们的定价页面了解详情。

分享这篇文章