Skip to main content

Copy from S3 works but not create external table as copy?

  • April 1, 2018
  • 3 replies
  • 10 views
Bryan_H
Forum|alt.badge.img+2

Hi, I installed 9.0.1-6 on CentOS on AWS, and ran into the following trying to access data in an S3 bucket:

dbadmin=> COPY bryanh.testcsv WITH SOURCE S3(url='S3://verticavimeodata/vimeo_s3_test.csv') DELIMITER ',';

Rows Loaded

       5

(1 row)

dbadmin=> CREATE EXTERNAL TABLE bryanh.tests3csv (testID int, testDate datetime, testInt int, testChar varchar(16)) AS COPY FROM 'S3://verticavimeodata/vimeo_s3_test.csv' DELIMITER ',';
CREATE TABLE
dbadmin=> select * from bryanh.tes
bryanh.testcsv bryanh.tests3csv
dbadmin=> select * from bryanh.tests3csv;
ERROR 7160: Cannot expand glob pattern due to error: Access Denied

I assume that since the COPY command worked, my AWS settings are correct, so why does CREATE EXTERNAL TABLE fail? What might I have missed setting up Vertica or S3?

    3 replies

    Bryan_H
    Forum|alt.badge.img+2
    • Bryan_H
    • Author
    • Participating Frequently
    • April 2, 2018

    If it's any help, vertica.log shows a little more info:
    2018-04-01 22:58:04.317 Init Session:7f54a3d8f700-a0000000000b5b @v_vimeoee_node0001: 22023/7160: Cannot expand glob pattern due to error: Access Denied
    LOCATION: expandGlobLocal, /scratch_a/release/svrtar26669/vbuild/vertica/Optimizer/Path/BulkLoad.cpp:2386

    Jim_Knicely
    Forum|alt.badge.img+2
    • Jim_Knicely
    • Participating Frequently
    • April 2, 2018

    Hi,

    Are you using Vertica on AWS? If so, the preferred fix for this is to create an IAM role to allow EC2 instances to access your S3 resources.

    See: https://my.vertica.com/docs/9.0.x/HTML/index.htm#Authoring/Eon/LoadingDataFromS3.htm

    Otherwise, you need to set users access key to allow Vertica access your S3 resources.

    Example:

    vsql=> SELECT SET_CONFIG_PARAMETER(
        'AWSAuth',
        '<aws_access_key_id>:
         <aws_secret_access_key>');
    Bryan_H
    Forum|alt.badge.img+2
    • Bryan_H
    • Author
    • Participating Frequently
    • April 2, 2018

    Yes, that's what it was. I am still figuring this out and didn't have my IAM quite right. We may just use AWSAuth since this is POC. Thanks!

    Still scrolling?
    Ask a question
    Terms & ConditionsAccessibility statement