问题[snowflake-cloud-data-platform](coding)

这是该问题的一个演示。

当我使用下面的 SQL 来获取特定数据时，我什么也没得到。

select * from tmp where ord ='904000009‬‬‬' or trim(ord) ='904000009‬‬‬';

但是当我使用 select * 和搜索功能时，我可以看到数据。这是一个文本列，我尝试使用 trim 删除空格，但仍然不起作用。

select * from tmp;

我尝试了如下所示的许多变体，但都没有效果。

选择 ord，* 从 tmp 中排除 ord，其中 ord ='904000009' 或 trim(ord) ='904000009' ；从 tmp 中选择 *，其中'00'||ord ='00904000009' ；

根据要求，这里是仅显示好行而不显示坏行的 SQL。

with t as (select  '4000009616‬‬‬' c, 'good' indicator union all select  '4000009616' as c , 'bad' indicator)
select * from t
where c='4000009616‬‬‬';

我正在尝试使用 LISTAGG 将一列创建为两列乘积的列表。我可以执行查询，但使用 DISTINCT 时它仍然不会删除重复值。代码示例如下。

CASE WHEN PLA.SALES_CTRY_LIST IS NULL AND PLA.DP_CTRY_LIST IS NOT NULL 
             THEN PLA.DP_CTRY_LIST 
             WHEN PLA.DP_CTRY_LIST IS NULL AND PLA.SALES_CTRY_LIST IS NOT NULL 
             THEN PLA.SALES_CTRY_LIST 
             WHEN PLA.SALES_CTRY_LIST IS NOT NULL AND PLA.DP_CTRY_LIST IS NOT NULL 
             THEN LISTAGG(DISTINCT PLA.SALES_CTRY_LIST ||', '||PLA.DP_CTRY_LIST)
        END AS "TESTING"

以下代码忽略了 csv 文件中的标题，并将模式推断为 c1、c2、c3。标题列仅作为另一行数据生成。以下是相关文件的示例数据

Series_reference    Period  Data_value  Suppressed  STATUS  UNITS   Magnitude   Subject Group   Series_title_1
BDCQ.SEA1AA 2011.06 80078       F   Number  0   Business Data Collection - BDC  Industry by employment variable Filled jobs

create or replace file format mybusinesscsv
type = csv
RECORD_DELIMITER = '\n'
skip_header = 0;

SELECT *
  FROM TABLE(
    INFER_SCHEMA(
      LOCATION=>'@quarterdatastg/employment_1.csv.gz',
      file_format=>'mybusinesscsv'

      )
    );

我有类似的数据

with data(custid, descriptors) as (
    select 1, ['Corporate', 'fun times', 'but not really']
    union all 
    select 2, ['lame times', 'Corporate', 'boring']
    union all 
    select 3, ['boring', 'Corporate', 'fun times', 'but not really']
)
    
select 
* 
from data

包含超过 30k 行且所有数组中唯一描述符的数量未知。我想计算有多少个distinct customerid值具有包含给定字符串的描述符数组。对于任何特定字符串，我可以使用

select 
count(distinct custid)
from data
where array_contains('Corporate'::variant, descriptors)

但我想获取所有 30k+ 行中具有每个数组值的值的数量customerid，而不是一次获取一个。

最终，我希望有一张这样的桌子

对于每个数组中每个可能的唯一字符串，但我不确定如何以编程方式获取所有数组成员，然后count(distinct custid)...where array_contains()对每个成员执行。我一直在阅读有关 s 和游标以及 FOR 循环的文档RESULTSET，但我对 Snowflake 还比较陌生，发现那组文档并不是完全有帮助。我知道我可以用来array_distinct(array_agg())组合所有数组并仅获取唯一值，但在此之后，我不知所措。我怀疑有一种简单的方法，但无论那是什么，我都错过了。

感谢您的帮助！

当我在 Snowflake 中像这样计算 1 / 100,000,000 时：

SELECT (1 / 100000000)::NUMBER(38,12);

结果显示为0：

0.000000000000

即使精度足够高。

但是，当我删除两个零并除以 1 / 1,000,000 时，它就会按预期工作：

SELECT (1 / 1000000)::NUMBER(38,12);

然后我得到了正确的结果0.000001：

0.000001000000

这里发生了什么事？

我在雪花数据库中有两个表。一个是手动生成的（表 1），另一个是使用 DBT 模型生成的（表 2）。这两个表中的记录数应该相同。我正在尝试编写一个 DBT 测试来为我进行比较。

但是，我的测试一直返回编译错误：

“测试 record_count_prontoform_prod_lptr_grower_prontoform_prod_lptr_grower__LPTR_GROWER_RAW_SUBMISSIONS__RAW_SUBMISSIONS (models\marts\schema_prontoform_prod_lptr_plant.yml) 中的编译错误，宏‘dbt_macro__test_record_count’没有关键字参数‘model’”

我在 DBT 项目中写了以下测试：

SQL 文件名：test_record_count.sql

-- count_match_test.sql
{% test record_count(source_name, source_table, model_name) %}

WITH
  table1_count AS (
    SELECT COUNT(*) AS count FROM {{ source(source_name, source_table) }}
  ),
  table2_count AS (
    SELECT COUNT(*) AS count FROM {{ ref(model_name) }}
  )

SELECT
  CASE WHEN t1.count = t2.count THEN 'pass'
       ELSE RAISE_ERROR('Record count mismatch between ' || source_name || ' and ' || model) END AS test_result
FROM table1_count t1
JOIN table2_count t2 ON t1.count = t2.count

{% endtest %}

Schema.yml

  - name: prontoform_prod_lptr_grower
    description: 'Mapping ids to question labels'
    data_tests:
      - record_count:
          source_name: "LPTR_GROWER_RAW_SUBMISSIONS"
          source_table: "RAW_SUBMISSIONS"
          model_name: "prontoform_prod_lptr_grower"

    columns:
      - name: submission_id
        description: 'submission_id of form'
        data_tests:
          - not_null
          - unique
      - name: question_id
        description: 'Unique question ID used for mapping'
        data_tests:
          - not_null          
      - name: question_label
        description: 'The label of the question'
        data_tests:
          - not_null

测试运行：

(dbt_venv) PS C:\prontoforms_dbt\dbt-snowflake> dbt test --select prontoform_prod_lptr_grower 23:37:52 使用 dbt=1.8.6 运行 23:37:52 注册适配器：snowflake=1.8.3 23:37:53 遇到错误：测试 record_count_prontoform_prod_lptr_grower_prontoform_prod_lptr_grower__LPTR_GROWER_RAW_SUBMISSIONS__RAW_SUBMISSIONS (models\marts\schema_prontoform_prod_lptr_plant.yml) 中的编译错误 宏'dbt_macro__test_record_count' 不需要关键字参数'model' (dbt_venv) PS C:\prontoforms_dbt\dbt-snowflake>

我努力设置正确的权限来执行任务。

当我尝试运行任务图（使用 UI：数据 > 数据库 > {db} > {schema} > 任务 > {my_task}）时，我无法单击执行按钮，它显示“只有任务所有者或具有操作权限的用户才能执行任务。 ”

我现在的角色已经拥有整个账户的执行任务权限：

GRANT EXECUTE TASK ON ACCOUNT TO ROLE {myrole};

显然这还不够，所以我尝试了这个：

GRANT OPERATE ON TASK {my_task} TO ROLE {myrole};

这有效，但它不适用于所有任务/未来任务，如下所示：

GRANT OPERATE ON ALL TASKS TO ROLE {myrole}; -- Unsupported feature 'ALL'.

我有数百个任务。有没有办法为所有任务设置 OPERATE 权限（或更改所有权） - 并记住为所有未来任务设置该权限？

我正在尝试创建一个函数，它将根据查找表中的分数和版本号返回标签

所以我有一张查阅表

create function get_label(score int, version int, vector string)
returns varchar
language sql as
$$
select label
from lookup
where version = coalesce(version, to_number(substr(vector,2,1)))
and score between min and max;
$$

我有各种表，我想为它们设置一个新的派生标签列。当我运行更新语句时，其中一些表有一个版本，而另一些表有一个包含版本号的向量。

alter table my_table
set derived_label = get_label(score,version,null);

alter table my_table2
set derived_label = get_label(score,null,vector);

我收到以下错误

Unsupported subquery type cannot be evaluated

我们有一个通用的事务表，每天通过标准管道加载历史数据，然后使用流、任务、程序将数据（批处理）处理到这个最终表上。现在，公司改变了加载数据的方式，改为使用 kafka connect，它会创建一个原始表，读取发布到主题中的 avro 消息。

由 kafka 连接器创建的这个原始表自动具有变体列，特别是以下形式的列之一：

[
   {
      "transactionDetailId": 112233445566,
      "brandCode": "31137209",
      "transactionOrigin": "Origin 1",
      "installmentCreditTransactionId": 1,
      ...
   },
   {
      "transactionDetailId": 112233445566,
      "brandCode": "31137209",
      "transactionOrigin": "Origin 1",
      "installmentCreditTransactionId": 2,
   },
   ...
 ]

这个由 kafka connect 创建的原始表仅包含自 2024-10-01（yyyy-mm-dd）以来的新数据，并且历史数据已经加载到这个已经提到的最终表中（已处理的数据）。

我面临的挑战是将此历史事务表数据转换为此变体列。对于其他表，我已经能够使用 snowflake 的函数 OBJECT_CONSTRUCT 将这些事务数据转换为变体列，但对于此表，尤其是此列，事情是这样的

{
          "transactionDetailId": 112233445566,
          "brandCode": "31137209",
          "transactionOrigin": "Origin 1",
          "installmentCreditTransactionId": 1,
          ...
       }

是最终表格中的一行，并且

{
          "transactionDetailId": 112233445566,
          "brandCode": "31137209",
          "transactionOrigin": "Origin 1",
          "installmentCreditTransactionId": 2,
       },

是另一行。

拆分此变量列的属性是“installmentCreditTransactionId”，因此在这种情况下，这两个对象转换为两行。如果我转到事务表，我会看到以下情况：

transactionDetailId   brandCode   transactionOrigin   installmentCreditTransactionId
998877665544          55555555    Origin 2            1
998877665544          55555555    Origin 2            2
998877665544          55555555    Origin 2            3

那应该转换成：

   [
       {
          "transactionDetailId": 998877665544,
          "brandCode": "55555555",
          "transactionOrigin": "Origin 2",
          "installmentCreditTransactionId": 1,
          ...
       },
       {
          "transactionDetailId": 998877665544,
          "brandCode": "55555555",
          "transactionOrigin": "Origin 2",
          "installmentCreditTransactionId": 2,
       },
       {
          "transactionDetailId": 998877665544,
          "brandCode": "55555555",
          "transactionOrigin": "Origin 2",
          "installmentCreditTransactionId": 3,
       },
       ...
    ]

我不明白 OBJECT_CONSTRUCT 在这里有何帮助，因为每条消息的 installmentCreditTransactionId id 数量是随机的。

对于如何解决这个问题，您有什么想法吗？使用 OBJECT_AGGR？

编辑 1：这是我目前所拥有的：

SELECT 
    OBJECT_AGG(tab1.id_1::VARCHAR,
        OBJECT_CONSTRUCT(
            'col1', null,
            'col2', tab1.col2::VARCHAR,
            'id_1', tab1.id_1::VARCHAR, -- one of the id's
            'id_2', tab1.id_2::VARCHAR, -- one of the id's
            'col3', COALESCE(tab1.col3::VARCHAR, '0'),
            'id_3', tab1.id_3::VARCHAR, -- one of the id's
            'col4', tab1.col4::VARCHAR,
            'id_4', tab1.id_4::VARCHAR, -- one of the id's
            'col5', CURRENT_TIMESTAMP()::VARCHAR,
            'col6', null) OVER (PARTITION BY tab1.id_1::VARCHAR, tab1.id_2::VARCHAR, tab1.id_3::VARCHAR, tab1.id_4::VARCHAR) AS result
FROM 
    db.schema.table1 tab1 
LEFT JOIN
    db.schema.table2 tab2
ON 
    ...
LEFT JOIN
    db.schema.table3 tab3
ON
    ...;

但没有返回我需要的，而是返回：

第一行：

{
  "1": {
    "col1": "28031866",
    "col2": "524",
    "id_1": "1",
    "id_2": "46554737145",
    "col3": 0,
    "id_3": "39866",
    "col_4": "100",
    "id_4": "969424002",
    "col_5": "2025-03-13",
    "col_6": "2024-10-11"
  }
}

第二行：

{
  "2": {
    "col1": "28031866",
    "col2": "522",
    "id_1": "2",
    "id_2": "46554737145",
    "col3": 0,
    "id_3": "39866",
    "col_4": "99",
    "id_4": "969424002",
    "col_5": "2024-11-13",
    "col_6": "2024-10-11"
  }
}

第三行：

{
  "3": {
    "col1": "28031866",
    "col2": "522",
    "id_1": "3",
    "id_2": "46554737145",
    "col3": 0,
    "id_3": "39866",
    "col_4": "99",
    "id_4": "969424002",
    "col_5": "2025-01-15",
    "col_6": "2024-10-11"
  }
}

我需要的是这样的：

[
   {
      "col1": "28031866",
      "col2": "522",
      "id_1": "1",
      "id_2": "46554737145",
      "col3": 0,
      "id_3": "39866",
      "col_4": "99",
      "id_4": "969424002",
      "col_5": "2025-01-15",
      "col_6": "2024-10-11"
   },
   {
      "col1": "28031866",
      "col2": "522",
      "id_1": "2",
      "id_2": "46554737145",
      "col3": 0,
      "id_3": "39866",
      "col_4": "99",
      "id_4": "969424002",
      "col_5": "2025-01-15",
      "col_6": "2024-10-11"
   },
   {
      "col1": "28031866",
      "col2": "522",
      "id_1": "3",
      "id_2": "46554737145",
      "col3": 0,
      "id_3": "39866",
      "col_4": "99",
      "id_4": "969424002",
      "col_5": "2025-01-15",
      "col_6": "2024-10-11"
   }
 ]

在一行中。看看只有 id_1 应该将所有内容聚合在一行中。但我不明白如何实现这一点

Snowflake 允许任务返回值，如此屏幕截图所示（来自文档）：

我该如何设置这些返回值？

我曾尝试过这样的：

create or replace task BI_ELT.LOGS.TEST1_TSK
schedule='USING CRON 0 4 * * * Europe/Berlin'
as 
BEGIN 
SELECT 1;
RETURN 'One selected';
END;

但任务的“运行历史”仍然没有显示返回值。

然而，在“查询详情”中我可以看到结果：

如何在任务图表中获取返回值（如第一个屏幕截图所示）？

感谢所有提示！