Ah ok, let me try again with text shown below instead (btw I am working on Windows).
I previously had drill working fine using direct s3 bucket access, anyway see revised version below Kind regards, Sacha Barber Technical Lead Classification: Public From: James Turton <dz...@apache.org> Sent: 17 November 2022 13:35 To: dev@drill.apache.org; Barber, Sacha <sacha.bar...@tpicap.com> Cc: Singleton, Owen <osingle...@liquidnet.com> Subject: [External email] Re: access point query Warning. This email is from outside of the organisation. Please do not click links or open attachments unless you recognize the source of this email and know the content is safe. ________________________________ Hi Sacha This mailing list does not support images in emails so we can't see a lot of what you've sent. Where the images are screenshots of text can you include the text instead? If there are still cases where you want to send an image rather than text, please use a paste bin service and include hyperlinks in your email. Thanks James On 2022/11/17 15:14, Barber, Sacha wrote: I have successfully set up an s3 access point which works for me using 1. AWS CLI 2. AW C# SDK I would like to use Apache Drill and I have seen it supports access points here : https://hadoop.apache.org/docs/stable/hadoop-aws/tools/hadoop-aws/index.html#accesspoints<https://hadoop.apache.org/docs/stable/hadoop-aws/tools/hadoop-aws/index.html#accesspoints> So I currently have this in the Drill s3 storage setup, which as far as I can tell is correct { "type": "file", "connection": "s3a://binli-qlik-test-bucket/", "config": { "fs.s3a.secret.key": "REDACTED", "fs.s3a.access.key": " REDACTED ", "fs.s3a.binli-qlik-test-bucket.accesspoint.arn": "arn:aws:s3:eu-west-1:997132959312:accesspoint/binli-qlik-test-ap", "fs.s3a.accesspoint.required": "true" }, "workspaces": { "tmp": { "location": "C:/Users/sbarber-a/drill/", "writable": true, "defaultInputFormat": null, "allowAccessOutsideWorkspace": true }, "root": { "location": "C:/Users/sbarber-a/drill/", "writable": false, "defaultInputFormat": null, "allowAccessOutsideWorkspace": true } }, "formats": { "parquet": { "type": "parquet" }, "avro": { "type": "avro", "extensions": [ "avro" ] }, "json": { "type": "json", "extensions": [ "json" ] }, "csvh": { "type": "text", "extensions": [ "csvh" ], "extractHeader": true }, "sequencefile": { "type": "sequencefile", "extensions": [ "seq" ] }, "psv": { "type": "text", "extensions": [ "tbl" ], "fieldDelimiter": "|" }, "tsv": { "type": "text", "extensions": [ "tsv" ], "fieldDelimiter": "\t" }, "csv": { "type": "text", "extensions": [ "csv" ] }, "shp": { "type": "shp", "extensions": [ "shp" ] }, "hdf5": { "type": "hdf5", "extensions": [ "h5" ], "defaultPath": null }, "spss": { "type": "spss", "extensions": [ "sav" ] }, "xml": { "type": "xml", "extensions": [ "xml" ], "dataLevel": 1 }, "excel": { "type": "excel", "extensions": [ "xlsx" ], "lastRow": 1048576 }, "pcap": { "type": "pcap", "extensions": [ "pcap", "pcapng" ] }, "ltsv": { "type": "ltsv", "extensions": [ "ltsv" ] }, "image": { "type": "image", "extensions": [ "jpg", "jpeg", "jpe", "tif", "tiff", "dng", "psd", "png", "bmp", "gif", "ico", "pcx", "wav", "wave", "avi", "webp", "mov", "mp4", "m4a", "m4p", "m4b", "m4r", "m4v", "3gp", "3g2", "eps", "epsf", "epsi", "ai", "arw", "crw", "cr2", "nef", "orf", "raf", "rw2", "rwl", "srw", "x3f" ], "fileSystemMetadata": true, "descriptive": true }, "syslog": { "type": "syslog", "extensions": [ "syslog" ], "maxErrors": 10 }, "httpd": { "type": "httpd", "extensions": [ "httpd" ], "logFormat": "common\ncombined" } }, "enabled": true } Where the following is true 1. "Connection" points ot the actual s3 location 2. fs.s3a.binli-qlik-test-bucket.accesspoint.arn : is my specific access point ARN for that bucket, which seems to be what the docs show here https://hadoop.apache.org/docs/stable/hadoop-aws/tools/hadoop-aws/index.html this section Configuring S3 AccessPoints usage with S3A In my core-site.xml I have the following lines commented out <!-- <property> <name>fs.s3a.access.key</name> <value>AKIA6QKOBPZIAWLWD5OZ</value> </property> <property> <name>fs.s3a.secret.key</name> <value>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</value> </property> <property> <name>fs.s3a.endpoint</name> <value>s3.eu-west-1.amazonaws.com</value> </property> --> So when I run this query in the web UI select * from s3.`subfolder` limit 10 I get this error: As I say this works in 2 other demos outside of Apache Drill. What am I doing wrong with Acess Poinits there is not many web pages explaning this. I am a bit lost The access point I have setup points to a bucket which has a subfolder called "open" which has a further sub folder called "subfolder" so I have tried these select * from s3.`subfolder` limit 10 select * from s3.`open/`.`subfolder` limit 10 select * from s3.`open`.`subfolder` limit 10 All of which seem to result in this error org.apache.drill.common.exceptions.UserRemoteException: RESOURCE ERROR: Failed to load schema for "s3"! java.nio.file.AccessDeniedException: s3a://binli-qlik-test-bucket/C:/Users/sbarber-a/drill: getFileStatus on s3a://binli-qlik-test-bucket/C:/Users/sbarber-a/drill: com.amazonaws.services.s3.model.AmazonS3Exception: Forbidden (Service: Amazon S3; Status Code: 403; Error Code: 403 Forbidden; Request ID: P9J1QKQ55PX19G45; S3 Extended Request ID: e5B9m8FYUpZ1Mp7B3p1DtZ2tlLr98ziYKYUgYJlfLVw7x74rwPGiPDrWRFtG3fUJ580p/GyT7ZY=), S3 Extended Request ID: e5B9m8FYUpZ1Mp7B3p1DtZ2tlLr98ziYKYUgYJlfLVw7x74rwPGiPDrWRFtG3fUJ580p/GyT7ZY=:403 Forbidden [Error Id: 7c4fdfbe-134f-4e4c-b5b1-2ff2131b662a ] Kind regards, Sacha Barber Technical Lead TP ICAP Classification: Public Honesty | Integrity | Respect | Excellence ________________________________ The information contained in this email is confidential and may also contain privileged information. Sender does not waive confidentiality or legal privilege. If you are not the intended recipient please notify the sender immediately; you should not retain this message or disclose its content to anyone. Internet communications are not secure or error free and the sender does not accept any liability for the content of the email. Although emails are routinely screened for viruses, the sender does not accept responsibility for any damage caused. Replies to this email may be monitored. TP ICAP Group plc | Registered in Jersey | Company number 130617. Please visit www.tpicap.com<http://www.tpicap.com> for further information on TP ICAP group. ________________________________ This communication is sent by TP ICAP plc on behalf of the ICAP Energy Limited Tullett Prebon (Europe) Limited and Spectron Energy Services Limited (together the “Tankard Parties”). All information contained in or attached to it (including, but not limited to market prices/levels and market commentary) (the "Information") is for information purposes only, is confidential, may be legally privileged and is the intellectual property of the Tankard Parties. The Information is subject to any terms of business as published or communicated to clients from time to time and is directed to Eligible Counterparties and Professional Customers only and is not intended for Retail Clients (as each term is defined by the rules of the Financial Conduct Authority). The Information is not, and should not be construed as, an offer, bid, recommendation or solicitation in relation to any financial instrument or investment or to participate in any particular trading strategy. The Information is not to be relied upon and is not warranted, including, but not limited, as to completeness, timeliness or accuracy and is subject to change without notice. All representations and warranties are expressly disclaimed. Access to the Information by anyone other than the intended recipient is unauthorised and any disclosure, copying or redistribution is prohibited. For further regulatory information please see www.icap.com. If you receive this message in error, please immediately delete all copies of it and notify the sender. We have taken precautions to minimise the risk of transmitting software viruses, but we advise you to carry out your own virus checks on any attachment to this message. We cannot accept liability for any loss or damage caused by software viruses. Tullett Prebon (Europe) Limited and ICAP Energy Limited are part of TP ICAP group. TP ICAP Group plc | Registered in Jersey | Company number 130617. For further information please visit www.tpicap.com.
