时间戳

首页 > 解决方案 > 找不到表，因为 get_ddl 将表名转换为大写

python - 找不到表，因为 get_ddl 将表名转换为大写

问题描述

我正在使用 snowflake_python_onnector 编写一个 python 脚本。目的是将现有对象（例如，表、管道、阶段）的 ddls 从雪花中提取到本地计算机上的 .sql 文件中。

当我运行脚本时，我只有表名不是大写的错误：

query: [SELECT GET_DDL('TABLE', 'PNC_GWCC_12651_RW.**cc_BudgetLine**') script]
2020-08-27 10:25:37 INFO     query execution done
2020-08-27 10:25:37 ERROR    002003 (02000): 01968619-0009-26fe-0000-1c2d074c77e6: SQL compilation error:
Table 'INTE_SDP_LZ_PERS.PNC_GWCC_12651_RW.**CC_BUDGETLINE**' does not exist or not authorized.

我把代码放在下面。我该如何解决这个问题？

问候奥尔加

OBJECT_LIST_QUERY = """
SELECT seq, catalog_name, schema_name, object_type, object_name, ARGUMENT_SIGNATURE, script
FROM (
    select '01' seq, DATABASE_NAME catalog_name, '*' schema_name, 'DATABASE' object_type, DATABASE_NAME object_name, '' ARGUMENT_SIGNATURE
           , 'CREATE OR REPLACE' || CASE WHEN D.IS_TRANSIENT = 'YES' THEN ' TRANSIENT' ELSE '' END || ' ' ||
           'DATABASE ' || D.DATABASE_NAME || ' ' ||
           '\nDATA_RETENTION_TIME_IN_DAYS = ' || TO_VARCHAR(RETENTION_TIME) ||
           CASE WHEN D.COMMENT IS NULL THEN '' ELSE '\nCOMMENT = ''' || D.COMMENT || '''' END || ';' script
      from INFORMATION_SCHEMA.DATABASES D
      WHERE DATABASE_NAME = '{}' 
      UNION ALL
    select '02' seq, catalog_name, schema_name schema_name, 'SCHEMA' object_type, schema_name object_name, '' ARGUMENT_SIGNATURE
           , 'CREATE OR REPLACE' || CASE WHEN S.IS_TRANSIENT = 'YES' THEN ' TRANSIENT' ELSE '' END || ' ' ||
           'SCHEMA ' || S.SCHEMA_NAME || ' ' ||
           CASE WHEN S.IS_MANAGED_ACCESS = 'YES' THEN '\nWITH MANAGED ACCESS' ELSE '' END || ' ' ||
           '\nDATA_RETENTION_TIME_IN_DAYS = ' || TO_VARCHAR(RETENTION_TIME) ||
           CASE WHEN S.COMMENT IS NULL THEN '' ELSE '\nCOMMENT = ''' || S.COMMENT || '''' END || ';' script
      from INFORMATION_SCHEMA.SCHEMATA S
      UNION ALL
    select '03' seq, table_catalog catalog_name, TABLE_SCHEMA schema_name, 'TABLE' object_type, TABLE_NAME object_name, '' ARGUMENT_SIGNATURE, '' script
      from INFORMATION_SCHEMA.TABLES
      WHERE TABLE_TYPE != 'VIEW'
      UNION ALL
    select '04' seq, table_catalog catalog_name, TABLE_SCHEMA schema_name, 'VIEW' object_type, TABLE_NAME object_name, '' ARGUMENT_SIGNATURE, '' script
      from INFORMATION_SCHEMA.VIEWS 
      WHERE TABLE_SCHEMA != 'INFORMATION_SCHEMA'
      UNION ALL
    select '06' seq, SEQUENCE_catalog catalog_name, SEQUENCE_SCHEMA schema_name, 'SEQUENCE' object_type, SEQUENCE_NAME object_name, '' ARGUMENT_SIGNATURE, '' script
      from INFORMATION_SCHEMA.SEQUENCES
      UNION ALL
    select '07' seq, FILE_FORMAT_catalog catalog_name, FILE_FORMAT_SCHEMA schema_name, 'FILE_FORMAT' object_type, FILE_FORMAT_NAME object_name, '' ARGUMENT_SIGNATURE, '' script
      from INFORMATION_SCHEMA.FILE_FORMATS
      UNION ALL
    select '08' seq, PIPE_catalog catalog_name, PIPE_SCHEMA schema_name, 'PIPE' object_type, PIPE_NAME object_name, '' ARGUMENT_SIGNATURE, '' script
      from INFORMATION_SCHEMA.PIPES 
      UNION ALL
    select '09' seq, FUNCTION_catalog catalog_name, FUNCTION_SCHEMA schema_name, 'FUNCTION' object_type, FUNCTION_NAME object_name, ARGUMENT_SIGNATURE, '' script
      from INFORMATION_SCHEMA.FUNCTIONS 
      UNION ALL
    select '10' seq, PROCEDURE_catalog catalog_name, PROCEDURE_SCHEMA schema_name, 'PROCEDURE' object_type, PROCEDURE_NAME object_name, ARGUMENT_SIGNATURE, '' script
      from "INFORMATION_SCHEMA"."PROCEDURES"
  ) T
ORDER BY seq, catalog_name, schema_name, object_name"""

GET_DDL_QUERY = "SELECT GET_DDL('{}', '{}') script"

标签： pythonsql

解决方案

默认情况下，Snowflake 应用以下规则来存储标识符（在创建/定义时）和解析它们（在查询和其他 SQL 语句中）：

  • 当标识符未加引号时，它以大写形式存储和解析。
  • 当标识符被双引号括起来时，它会按照输入的方式存储和解析，包括大小写。

这就是为什么你需要在你的桌子周围加上双引号

SELECT GET_DDL('TABLE', 'PNC_GWCC_12651_RW."cc_BudgetLine"' )

推荐阅读