CODEWITHSUNDEEP
sqlsnowflake-cloud-data-platform
topplethepat
topplethepat

Reputation: 531

syntax error with COPY INTO snowflake command

I am trying to populate a table already created 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?

Upvotes: 1

Views: 1978

Answers (2)

demircioglu
demircioglu

Reputation: 3455

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.

Upvotes: 2

Eric Lin
Eric Lin

Reputation: 1510

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
)
;

Upvotes: 0

Related Questions