使用S3存储桶从SAP Data Services加载Snowflake表时出现问题

Question

我正试图使用s3 bucket将表从数据服务加载到Snowflake(需要批量加载信息(。

我无法将输出文件格式化为s3 bucket。我在换行(不换行(和日期(额外的精度(方面有问题，如果任何文本都有逗号，我可能会在逗号方面有问题(实际上分隔符是逗号(。

我已经看到了在具有嵌套模式的s3 bucket中将文件写为json的可能性。但如果我这样做，我不知道如何调用Snowflake的副本。

这个项目是一个迁移。我正在更改雪花的旧数据库。SAP DS中的工作岗位已经创造出来，其理念只是改变命运，而不是信息流。

如果有人能帮我，那就太棒了。感谢

Answer 1

您可以使用一个具有VARIANT类型单列的表来加载json文件。

这里有一个例子：

/* Create a JSON file format that strips the outer array. */
create or replace file format json_format
type = 'JSON'
strip_outer_array = true;
/* Create an internal stage that references the JSON file format. */
create or replace stage mystage
file_format = json_format;
/* Stage the JSON file. */
put file:///tmp/sales.json @mystage auto_compress=true;
/* Create a target table for the JSON data. */
create or replace table house_sales (src variant);
/* Copy the JSON data into the target table. */
copy into house_sales
from @mystage/sales.json.gz;
select * from house_sales;
+---------------------------+
| SRC                       |
|---------------------------|
| {                         |
|   "location": {           |
|     "city": "Lexington",  |
|     "zip": "40503"        |
|   },                      |
|   "price": "75836",       |
|   "sale_date": "4-25-16", |
|   "sq__ft": "1000",       |
|   "type": "Residential"   |
| }                         |
| {                         |
|   "location": {           |
|     "city": "Belmont",    |
|     "zip": "02478"        |
|   },                      |
|   "price": "92567",       |
|   "sale_date": "6-18-16", |
|   "sq__ft": "1103",       |
|   "type": "Residential"   |
| }                         |
| {                         |
|   "location": {           |
|     "city": "Winchester", |
|     "zip": "01890"        |
|   },                      |
|   "price": "89921",       |
|   "sale_date": "1-31-16", |
|   "sq__ft": "1122",       |
|   "type": "Condo"         |
| }                         |
+---------------------------+

有关更多信息，请查看此处

您还可以直接查询暂存的JSON文件，请参阅以下示例：

create or replace file format my_json_format type = 'json';
select * from @~/example_2.json.gz 
(
file_format => my_json_format
);

我得到：

{
"quiz": {
"maths": {
"q1": {
"answer": "12",
"options": [
"10",
"11",
"12",
"13"
],
"question": "5 + 7 = ?"
},
"q2": {
"answer": "4",
"options": [
"1",
"2",
"3",
"4"
],
"question": "12 - 8 = ?"
}
},
"sport": {
"q1": {
"answer": "Huston Rocket",
"options": [
"New York Bulls",
"Los Angeles Kings",
"Golden State Warriros",
"Huston Rocket"
],
"question": "Which one is correct team name in NBA?"
}
}
}
}

我也可以做：

select parse_json($1):quiz.maths from @~/example_2.json.gz 
(
file_format => my_json_format
);

我得到：

{
"q1": {
"answer": "12",
"options": [
"10",
"11",
"12",
"13"
],
"question": "5 + 7 = ?"
},
"q2": {
"answer": "4",
"options": [
"1",
"2",
"3",
"4"
],
"question": "12 - 8 = ?"
}
}