提问人：naomitrina 提问时间：8/8/2023 更新时间：8/9/2023 访问量：169
infer_schema缺少列标题的文件的功能
infer_schema function on files with missing column header
问：
目前，我有 5 个暂存的文件，它们都缺少一个列标题：
| 2021 | 2022 | 2023 | |
|---|---|---|---|
| A组 | 54.9 | 55.3 | 55.7 |
| B组 | 91.3 | 82.7 | 89.6 |
我无法更改这些文件的格式。我在 Snowflake 中使用了 infer_schema 函数，因为随着更多年份列的添加，文件将是动态的。
这是我想尝试的：
Create or replace table staging_table
using template (
select array_agg(object_construct(*))
from table(
infer_schema(
location => 'stage'
file_format => 'file_format'
)
)
);
copy into staging_table
from @stage
file_format = 'file_format'
match_by_column_name = case_insensitive;
我正在使用临时表，因为我还想取消透视数据。我最初在file_format中使用，但由于缺少标题而不起作用。我试过了，但这使得跟踪列和重命名它们变得更加困难（日期列比示例中的日期列多）。有没有人对如何更好地处理这个案子有任何想法？parse_header = trueparse_header = false
另外，我知道这可能是一个很长的延伸，但我也想在表中获取文件名。我知道我可以像这样获取文件名：
SELECT *
FROM TABLE (
INFER_SCHEMA(
LOCATION => MY_STAGE,
FILE_FORMAT => 'MY_FILE_FORMAT'
)
);
但是，如何实际将数据与数据一起插入/复制到临时表中呢？
换句话说，我想要 Snowflake 中的数据，如下所示：
| 群 | 年 | 得分 | File_Name |
|---|---|---|---|
| A组 | 2021 | 54.9 | 罚款 |
| A组 | 2022 | 55.7 | 罚款 |
| A组 | 2023 | 55.7 | 罚款 |
| A组 | 2021 | 33.1 | 文件2 |
| A组 | 2022 | 33.1 | 文件2 |
| A组 | 2023 | 34.2 | 文件2 |
如何在 Snowflake 中从第一种文件格式转换为第二种表格格式？
任何帮助将不胜感激!!!我是否必须将其分解为 5 个不同的临时表，然后对实际表进行一个大插入？
答：
0赞
Greg Pavlik
8/9/2023
#1
要使其正常工作，方法是：
- 逐行读取阶段中的每个文件（使用未指定列分隔符的文件格式）。
- 使用 UDTF（用户定义表函数），该函数知道每个文件的第一行是年份列表。我提供的 UDTF 将去除此行上空列的前导逗号（如果存在）。
- 通过 UDTF 运行每个文件的行。UDTF 将读取每个文件第一行的年份，并将分数转换为这些年份。
首先，创建一个全行读取的文件格式，而不是单个字段：
create or replace file format READ_LINE type = csv
field_delimiter = 'NONE' compression = 'AUTO';
然后，您可以通过执行 select 来确保这可以读取整行：
select metadata$file_row_number, metadata$filename, $1 as LINE
from @MY_FILES (file_format => READ_LINES);
接下来，创建 UDTF 来处理数据到达文件的方式：
create or replace function PIVOT_SCORES(LINE_NUMBER float, FILENAME string, LINE string)
returns table (GROUP_NAME string, YEAR string, SCORE string)
language javascript strict immutable
as
$$
{
initialize: function (argumentInfo, context) {
this.years = [];
},
processRow: function (row, rowWriter, context) {
var lineYear;
var lineItems;
if(row.LINE_NUMBER == 1) {
if(row.LINE.trim().substr(0,1) == ",") {
this.years = row.LINE.trim().substr(1).split(",");
} else {
this.years = row.LINE.trim().split(",");
}
} else {
lineItems = row.LINE.trim().split(",");
for (let i=1; i < lineItems.length; i++) {
rowWriter.writeRow({GROUP_NAME:lineItems[0],YEAR:this.years[i-1],SCORE:lineItems[i]});
}
}
},
finalize: function (rowWriter, context) { /* */ },
}
$$
;
最后，选择文件中的行并通过 UDTF 运行它们，如下所示：
with FILE_LINES as
(
select metadata$file_row_number LINE_NUMBER
,metadata$filename FILE_NAME
,$1 LINE
from @MY_FILES (file_format => READ_LINES) F
)
select GROUP_NAME
,YEAR
,SCORE
,FILE_NAME
from FILE_LINES F
,table(pivot_scores(LINE_NUMBER::float, FILE_NAME, F.LINE) over (partition by FILE_NAME order by LINE_NUMBER)) TF
;
我创建了两个文件来测试这一点。File1 是基于示例表的文件，File2 是新文件，多了一年（2020 年）用于测试。结果如下：
| GROUP_NAME | 年 | 得分 | FILE_NAME |
|---|---|---|---|
| A组 | 2020 | 54.9 | 文件2.txt.gz |
| A组 | 2021 | 55.3 | 文件2.txt.gz |
| A组 | 2022 | 55.7 | 文件2.txt.gz |
| A组 | 2023 | 11.1 | 文件2.txt.gz |
| B组 | 2020 | 91.3 | 文件2.txt.gz |
| B组 | 2021 | 82.7 | 文件2.txt.gz |
| B组 | 2022 | 89.6 | 文件2.txt.gz |
| B组 | 2023 | 11.1 | 文件2.txt.gz |
| C组 | 2020 | 12.3 | 文件2.txt.gz |
| C组 | 2021 | 12.3 | 文件2.txt.gz |
| C组 | 2022 | 12.4 | 文件2.txt.gz |
| C组 | 2023 | 11.1 | 文件2.txt.gz |
| A组 | 2021 | 54.9 | 文件1.txt.gz |
| A组 | 2022 | 55.3 | 文件1.txt.gz |
| A组 | 2023 | 55.7 | 文件1.txt.gz |
| B组 | 2021 | 91.3 | 文件1.txt.gz |
| B组 | 2022 | 82.7 | 文件1.txt.gz |
| B组 | 2023 | 89.6 | 文件1.txt.gz |
评论