DoubleCloud 即将停止运营。迁移到 ClickHouse，享受限时免费迁移服务。立即联系我们 ->->

博客 / 工程

使用 ClickHouse 和 Amazon Bedrock 构建 Google Analytics 的 RAG 管道

Dale McDiarmid

2023年11月21日

在最近的一篇博文中，我们探讨了用户如何通过使用 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 事件保存一行数据，从中可以推导出诸如“显示过去 30 天内博客文章的回访用户”之类的查询，前提是要使用正确的 SQL 查询。

虽然我们预计大多数针对 GA 数据的查询都将像上面的示例一样结构化，但我们也希望能够根据概念回答更细致的问题。例如，假设我们想要“随着时间的推移，访问基于物化视图的页面的新用户的总数”。此问题的绝大部分是结构化的，需要精确的列匹配和按日期分组。但是，“物化视图”的概念则更加细致一些。为了设定一个更有挑战性的目标，如果我们能够找到一种方法将概念表示为用户可以改进的更具体的事物，这似乎是可行的。

在这一点上，可能可以合理地假设大多数读者都使用过或听说过大型语言模型 (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挑战赛的目标是开发跨域数据库的自然语言接口。它包含10181个问题和5693个独特的复杂SQL查询，这些查询分布在200个数据库中，数据库包含多个表，涵盖138个不同的领域。为了在这个挑战赛中取得好成绩，系统必须能够很好地泛化到新的SQL查询和新的数据库模式。”

这个具体的挑战赛比我们的Google Analytics问题到文本问题更通用——我们的问题是特定领域的，而Spider旨在衡量跨域零样本性能。尽管如此，领先论文中的思路仍然值得我们探索和借鉴。令人欣慰的是，包括DAIL-SQL在内的当前领先方法都依赖于提示工程和基于检索增强生成（RAG）的方法。

DAIL-SQL的主要创新点在于，它对一组可能的有用提示问题（“候选集”）中的关键词（例如，列和值）进行掩码处理，并为问题生成嵌入。然后，使用一个专门的预训练Transformer模型生成（并掩码）一个“骨架”查询。通过比较问题及其骨架SQL与候选问题及其SQL，依次识别提示的示例问题。有关更详细的概述，请参见此处。这种方法出奇地简单有效，并提供了一些关于获得良好结果的有用提示。

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

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

根据上述研究，我们的RAG流程必然需要我们能够为文本生成嵌入（例如，查找类似的问题）。为此，我们使用了一个简单的Python UDF embed.py，它可以在查询或插入时被调用。它利用了亚马逊通过Bedrock提供的titan-embed-text-v1模型。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表执行此SQL。

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

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

从问题中提取概念。
表示这些概念，以便能够识别相关的页面。

对于（1），我们尝试了许多技术，但发现Bedrock中提供的anthropic.claude-v2模型非常有效。使用包含示例的适当的提示，该模型能够准确地提取目标概念。

对于（2），我们首先识别与步骤（1）中提取的概念相关的页面。为此，我们根据每个网站页面的文本内容以及概念本身创建并存储嵌入。给定一个源表source_pages，其中包含url、raw_title和raw_content列，如下所示，我们可以插入行到目标表site_pages，并在插入时调用embed函数。请注意，我们还使用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 embed函数填充此表。

初始实验使用以下查询来识别前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模型，使用带有示例的提示从查询中提取关键指标。这将返回关键指标，然后将其嵌入并用于识别适当的问题。

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

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

匹配的问题及其相应的SQL将添加到我们的最终提示中。最后，如果问题按博客或文档进行筛选（通过简单的正则表达式匹配确定），我们会添加一个关于如何按此网站区域进行筛选的示例。

考虑到早期研究强调提示结构的重要性，我们也尝试遵循Anthropic的Claude模型文档中提出的指南。这包括一个关于模型提示工程的优秀演示文稿，其中以下内容与我的任务相关

遵循人/需要帮助的结构。
利用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月文档页面的每日回访用户数是多少？	`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管道速度很慢，有多个步骤可以使用更轻量级的模型和/或更少的token。改进模型也可能有利于提高多个步骤的准确性。在每个步骤中探索模型改进也值得考虑，以提高性能和准确性。

当前的准确率不足以支持应用程序自动渲染图表。因此，我们决定探索一种方法，除了图表之外，还向用户显示生成的SQL以及添加到提示中的上下文。这将符合我们可能需要用户改进用于表示概念的词汇的需求。虽然这可能无法解决我们的初始问题，但我们怀疑新接触数据集的用户仍然会发现这种方法很有价值。

立即开始使用 ClickHouse Cloud 并获得 300 美元的信用额度。在 30 天试用期结束后，您可以继续使用按需付费计划，或联系我们了解有关我们基于用量的折扣的更多信息。访问我们的定价页面获取详细信息。

分享此文章