J'ai du mal à fusionner les données dans la table Snowflake à partir d'une étape nommée interne. J'ai fait référence à la base de connaissances article.

My code is pasted below and the error is also pasted below.

Code:

MERGE INTO ORDERS TGT 
USING 
    (
        SELECT $1::NUMBER                             o_orderkey,
               $2::NUMBER                             o_custkey,
               $3::STRING                             o_orderstatus,
               $4::FLOAT                              o_totalprice,
               TO_DATE($5::VARCHAR, 'YYYY-MM-DD')     O_ORDERDATE,
               $6::STRING                             o_orderpriority,
               $7::STRING                             o_clerk,
               $8::STRING                             o_shippriority,
               $9::STRING                             o_comment
        from '@STG_IVP_REF_BULK_DATA_MIGRATION/ORDERS/Snowflake_DB_Data_12-Feb-2020_02-59-50-PM.csv.gz'
        (file_format = (field_delimiter = '|'))
    )SRC
ON SRC.O_ORDERKEY = TGT.O_ORDERKEY
WHEN MATCHED 
THEN
    UPDATE SET TGT.LAST_MODIFIED_DATE = CURRENT_TIMESTAMP()::TIMESTAMP_NTZ
WHEN NOT MATCHED
THEN
    INSERT (o_orderkey, o_custkey, o_orderstatus, o_totalprice, O_ORDERDATE, o_orderpriority, o_clerk, o_shippriority, o_comment)
    VALUES (o_orderkey, o_custkey, o_orderstatus, o_totalprice, O_ORDERDATE, o_orderpriority, o_clerk, o_shippriority, o_comment);

Sortie d'erreur:

SQL Error [2018] [22023]: SQL compilation error:
Invalid argument [2=>[unresolved function: "="](FILE_FORMAT, [unresolved function: "="](FIELD_DELIMITER, '|'))] for table function. Table function argument is required to be a constant.
0
Sagi 13 févr. 2020 à 17:22

1 réponse

Meilleure réponse

Pouvez-vous créer un objet file_format pour définir le délimiteur comme « | », et l'utiliser sur votre requête ? Par exemple:

CREATE FILE FORMAT myformat TYPE = 'CSV' FIELD_DELIMITER = '|';


MERGE INTO ORDERS TGT 
USING 
    (
        SELECT $1::NUMBER                             o_orderkey,
               $2::NUMBER                             o_custkey,
               $3::STRING                             o_orderstatus,
               $4::FLOAT                              o_totalprice,
               TO_DATE($5::VARCHAR, 'YYYY-MM-DD')     O_ORDERDATE,
               $6::STRING                             o_orderpriority,
               $7::STRING                             o_clerk,
               $8::STRING                             o_shippriority,
               $9::STRING                             o_comment
        from '@STG_IVP_REF_BULK_DATA_MIGRATION/ORDERS/Snowflake_DB_Data_12-Feb-2020_02-59-50-PM.csv.gz'
        (file_format => myformat)
    )SRC
ON SRC.O_ORDERKEY = TGT.O_ORDERKEY
WHEN MATCHED 
THEN
    UPDATE SET TGT.LAST_MODIFIED_DATE = CURRENT_TIMESTAMP()::TIMESTAMP_NTZ
WHEN NOT MATCHED
THEN
    INSERT (o_orderkey, o_custkey, o_orderstatus, o_totalprice, O_ORDERDATE, o_orderpriority, o_clerk, o_shippriority, o_comment)
    VALUES (o_orderkey, o_custkey, o_orderstatus, o_totalprice, O_ORDERDATE, o_orderpriority, o_clerk, o_shippriority, o_comment);

Lorsque vous interrogez les données de la préparation, vous devez utiliser des objets FILE_FORMAT :

https://docs.snowflake.net/manuals/user-guide/querying-stage.html#query-syntax-and-parameters

1
Gokhan Atil 13 févr. 2020 à 14:33