主页 / coding / 问题 / 79419087
Accepted
Mat
Asked: 2025-02-07 03:12:50 +0800 CST

雪花 - 推断架构未检测到标题

  • 772

以下代码忽略了 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'

      )
    );
snowflake-cloud-data-platform

2 个回答

    • 0
  2. Best Answer

    尼克指出的一点是该'parse header'选项也是必需的。

    我们有两种文件格式，一种用于 LOAD，另一种用于 INFER 阶段：

    create or replace file format DB_NAME.SCHEMA_NAME.CSV_FORMAT 
    type = CSV RECORD_DELIMITER ='\n', FIELD_DELIMITER=',',
    SKIP_HEADER=1, PARSE_HEADER=false, ESCAPE_UNENCLOSED_FIELD='\\', 
   FIELD_OPTIONALLY_ENCLOSED_BY='\"';

create or replace file format DB_NAME.SCHEMA_NAME.CSV_FORMAT_HEADER 
   type = CSV RECORD_DELIMITER ='\n', FIELD_DELIMITER=',',
   SKIP_HEADER=0, PARSE_HEADER=true, ESCAPE_UNENCLOSED_FIELD='\\', 
   FIELD_OPTIONALLY_ENCLOSED_BY='\"';

    因此 INFER 如下：

                FROM TABLE(INFER_SCHEMA(
                      LOCATION => ''$$ || S3_FOLDER || $$'',
                      FILE_FORMAT => ''DB_NAME.SCHEMA_NAME.CSV_FORMAT_HEADER'',
                      IGNORE_CASE => TRUE
                ))

    以及在 COPY 中的使用情况，例如：

     COPY INTO DB_NAME.SCHEMA_NAME.TABLE_NAME
      FROM @DB_NAME.SCHEMA_NAME.MEANINGFUL_NAME_STAGE/table_path_tokens
      FILE_FORMAT = DB_NAME.SCHEMA_NAME.CSV_FORMAT;
    • 0

相关问题