用数组和对象从多个值插入雪花表

Question

我试图通过SQL语句将多行数组和结构插入到Snowflake中。要在列中插入值数组，我使用array_constuct函数，要插入结构/字典/对象，我使用object_construct函数。

insert into "MY_DB"."MY_SCHEMA"."MY_TABLE"
("ID", "TS", "TEXT", "DATEONLY", "ARRAY", "OBJ", "BOOL", "DOUBLE", "INT", "DEC_18_9")
values
('id1', '2020-11-26 14:01:27.868', '19', '2020-11-26',
  ARRAY_CONSTRUCT(0, 1, 2), OBJECT_CONSTRUCT('this', 'is', 'my', 'object', 'query',
  OBJECT_CONSTRUCT('field1', 'one', 'field2', ARRAY_CONSTRUCT('field2a', 'two')),
  'field3', ARRAY_CONSTRUCT(3, 4, 5)), FALSE, 178482300.96318725, 9, 12345619.876543190),
('id2', '2020-11-26 14:01:27.868', '19', '2020-11-26',
  ARRAY_CONSTRUCT(0, 1, 2), OBJECT_CONSTRUCT('this', 'is', 'my', 'object', 'query',
  OBJECT_CONSTRUCT('field1', 'one', 'field2', ARRAY_CONSTRUCT('field2a', 'two')),
  'field3', ARRAY_CONSTRUCT(3, 4, 5)), FALSE, 178482300.96318725, 9, 12345619.876543190)
;

这将导致异常：

SQL编译错误：VALUES子句中的表达式[Array_Construct(0,1,2)]无效

使用以下语法插入一行可以工作：

insert into "MY_DB"."MY_SCHEMA"."MY_TABLE"
("ID", "TS", "TEXT", "DATEONLY", "ARRAY", "OBJ", "BOOL", "DOUBLE", "INT", "DEC_18_9")
select 'id1', '2020-11-26 14:01:27.868', '19', '2020-11-26',
  ARRAY_CONSTRUCT(0, 1, 2), OBJECT_CONSTRUCT('this', 'is', 'my', 'object', 'query',
  OBJECT_CONSTRUCT('field1', 'one', 'field2', ARRAY_CONSTRUCT('field2a', 'two')),
  'field3', ARRAY_CONSTRUCT(3, 4, 5)), FALSE, 178482300.96318725, 9, 12345619.876543190
;

create or replace temporary table "MY_DB"."MY_SCHEMA"."MY_TABLE"
("ID" STRING NOT NULL, "TS" TIMESTAMP NOT NULL, "TEXT" STRING,
  "DATEONLY" DATE, "ARRAY" ARRAY, "OBJ" OBJECT, "BOOL" BOOLEAN,
  "DOUBLE" DOUBLE, "INT" BIGINT, "DEC_18_9" NUMBER (18, 9)
);

Answer 1

经过反复试验，我找到了这个解决方案：

sql prettyprint-override">INSERT INTO "MY_DB"."MY_SCHEMA"."MY_TABLE"
SELECT $1, $2, $3, $4, PARSE_JSON($5), PARSE_JSON($6), $7, $8, $9, $10
from values
('id1', '2020-11-26 14:01:27.868', '19', '2020-11-26', '[0, 1, 2]',
  '{"this": "is", "my": "object",
    "query": {"field1": "one", "field2": ["field2a", "two"], "field3": [3, 4, 5]}}',
   FALSE, 178482300.96318725, 9, 12345619.876543190),
('id2', '2020-11-26 14:01:27.868', '19', '2020-11-26', '[0, 1, 2]',
  '{"this": "is", "my": "object",
    "query": {"field1": "one", "field2": ["field2a", "two"], "field3": [3, 4, 5]}}',
   FALSE, 178482300.96318725, 9, 12345619.876543190)
;

仅供参考，从一个简短的测试中，Snowflake将JSON中的NaN、-inf和inf标识为浮点值，而周围没有引号，例如{“my_field”：NaN}。