0

I am trying to copy results to Amazon s3 from snowflakes using s3n:// and s3a:// url but getting an SQL compilation error

The sql query is in the format

COPY INTO '&s3_path/&curr_dt/pvc'
FROM ( 
    SELECT OBJECT_CONSTRUCT('id',id,'keyword',keyword)
    FROM brands_delta)
CREDENTIALS = (AWS_KEY_ID='&aws_key_id' AWS_SECRET_KEY='&aws_secret_key')
FILE_FORMAT = (TYPE=JSON)
SINGLE = false
OVERWRITE = true
MAX_FILE_SIZE = 1073741824;

The error in the log files is as follows : 001011 (42601): SQL compilation error: invalid URL prefix found in: 's3a://abc/prod-runs/input/2021-01-19/pvc'

Felipe Hoffa
  • 39,978
  • 8
  • 105
  • 241
Akshay Ugale
  • 67
  • 5

1 Answers1

1

The URI protocol determines the code/software used by the client to access the resource given in the URI.

In this case, Snowflake is the client software and it, obviously, doesn't use the s3a/s3n protocols. I'm not sure why you are trying to use them?

NickW
  • 2,531
  • 2
  • 2
  • 12
  • I am trying it because when we were using s3 ,it gave us the below error : An error occurred while calling o25.partitions. : java.io.IOException: No FileSystem for scheme: s3 This error seems to be because of configuration of hadoop. To resolve this error I used s3n:// and s3a:// but now facing sql compilation error at snowflakes end. This is the solution for that error https://stackoverflow.com/questions/46740670/no-filesystem-for-scheme-s3-with-pyspark – Akshay Ugale Jan 20 '21 at 02:29
  • Are you saying that you got that error (java.io.IOException: No FileSystem for scheme: s3) when running the Snowflake COPY INTO command? If not, what error do you get when you use s3 (rather than s3n ir s3a) in your COPY INTO command? – NickW Jan 20 '21 at 07:48
  • Yes I got the error An error occurred while calling o25.partitions. : java.io.IOException: No FileSystem for scheme: s3 when reading data from s3 on emr while running a pyspark script.So I changed s3 to s3n client and then I got SQL compilation error while transfering data from snowflakes to S3 via copy into commands – Akshay Ugale Jan 20 '21 at 08:25
  • I'm not sure you understood my questions. You seem to be saying that you didn't get the java error when running the Snowflake COPY INTO command? Do you still get the SQL compilation error if you use s3 (rather than s3n or s3a) in the COPY INTO command? – NickW Jan 20 '21 at 09:09
  • No I don't get the error when I use s3 with COPY INTO – Akshay Ugale Jan 20 '21 at 15:04
  • So the problem is fixed? – NickW Jan 20 '21 at 15:09
  • Yes this error is fixed but we are migrating to s3a:// client for fetching for the data for model from Amazon S3 and facing issues in it's configuration. – Akshay Ugale Jan 20 '21 at 15:14