syntax error with COPY INTO snowflake command-CodePudding

I am trying to populate a table in Snowflake from a file in an s3 bucket. I have verified that the Storage Integration is working and so according to the docs, I don't need an External Stage.

It also seems that in order to populate a table using a Parquet file format, I need to specify the columns in the copy command, per posts in Snowflake community forum.

My command is giving a syntax error no matter what changes I make. If I remove the '(' after the s3 bucket url, I get the error

    "unexpected SELECT"

but if I leave it in, I get the error

    "unexpected '(' "

My code:

    COPY INTO MYTABLE
        FROM s3://bucket/folder2/File.snappy.parquet (
       SELECT 
       O_ID: NUMBER(19,0),        

       O_TYPE:VARCHAR(48),        

       C_ID:NUMBER(19,0),

       ON_ID:FLOAT,

       SE_ID:NUMBER(19,0),

       C_MATCH:BOOLEAN 

    )

        STORAGE_INTEGRATION = MY_INTEGRATION
        FILE_FORMAT=(
           TYPE = 'PARQUET' 
          SNAPPY_COMPRESSION = TRUE
       )
      ;

Can someone tell me how to fix the syntax here?

CodePudding user response：

I believe your command should be like

    COPY INTO MYTABLE ( 
       O_ID: NUMBER(19,0),        
       O_TYPE:VARCHAR(48),        
       C_ID:NUMBER(19,0),
       ON_ID:FLOAT,
       SE_ID:NUMBER(19,0),
       C_MATCH:BOOLEAN 
       )
       FROM (
       SELECT
       $1,
       $2,
       $3,
       $4,
       $5,
       $6 
       FROM s3://bucket/folder2/File.snappy.parquet 
       )
        STORAGE_INTEGRATION = MY_INTEGRATION
        FILE_FORMAT=(
           TYPE = 'PARQUET' 
          SNAPPY_COMPRESSION = TRUE
       )
      ;
```

Please try and let me know if it works.

CodePudding user response：

Table needs to be created before COPY INTO:

CREATE OR REPLACE TABLE MYTABLE (
    O_ID NUMBER(19,0),        
    O_TYPE VARCHAR(48),        
    C_ID NUMBER(19,0),
    ON_ID FLOAT,
    SE_ID NUMBER(19,0),
    C_MATCH BOOLEAN 
);


COPY INTO MYTABLE (
    O_ID,        
    O_TYPE,     
    C_ID,
    ON_ID,
    SE_ID
)
FROM s3://bucket/folder2/File.snappy.parquet

STORAGE_INTEGRATION = MY_INTEGRATION
FILE_FORMAT = (
    TYPE = 'PARQUET' 
    SNAPPY_COMPRESSION = TRUE
)
;