# Unable to CTAS using S3

**URL:** <https://community.dremio.com/t/unable-to-ctas-using-s3/2267>\
**Category:** Uncategorized\
**Created:** [November 16, 2018, 10:38pm UTC](https://community.dremio.com/t/unable-to-ctas-using-s3/2267 "2018-11-16T22:38:30Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![Aaron\_Santos](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/aaron_santos/32/810_2.png) [@Aaron\_Santos](https://community.dremio.com/u/Aaron_Santos)\
**Post date:** [November 16, 2018, 10:38pm UTC](https://community.dremio.com/t/unable-to-ctas-using-s3/2267/1 "2018-11-16T22:38:30Z")

</div>

When running a CTAS statement using S3 as the destination Dremio responds with the error `Failure to check if table already exists at path...`

I’ve configured the S3 connector to authenticate using an IAM role (Access Keys have the same issue) and enabled `exports into the source (CTAS and DROP)`. The S3 bucket is configured with this policy

```
{
  "Version": "2012-10-17",
  "Statement": [
      {
          "Effect": "Allow",
          "Principal": {
              "AWS": "arn:aws:iam::XXXXXXXXX:role/DremioRole"
          },
          "Action": [
              "s3:ListBucket",
              "s3:GetBucketLocation"
          ],
          "Resource": "arn:aws:s3:::XXXXXXXXX"
      },
      {
          "Effect": "Allow",
          "Principal": {
              "AWS": "arn:aws:iam::XXXXXXXXX:role/DremioRole"
          },
          "Action": "s3:GetObject",
          "Resource": "arn:aws:s3:::XXXXXXXXX/*"
      }
  ]
}

```

The EC2 instance to which Dremio is deployed is also using an IAM role with AmazonS3FullAccess.

The error in the job profile includes a stack trace

```
     VALIDATION ERROR: Failure to check if table already exists at path XXXXXX.

```

(java.io.IOException) Unable to retrieve bucket named XXXXXX.  
com.dremio.plugins.util.ContainerFileSystem.getFileSystemForPath():305  
com.dremio.plugins.util.ContainerFileSystem.getFileStatus():479  
org.apache.hadoop.fs.FileSystem.exists():1437  
com.dremio.exec.store.dfs.FileSystemWrapper.exists():953  
com.dremio.exec.store.dfs.FileSystemPlugin.createNewTable():1055  
com.dremio.exec.catalog.CatalogImpl.createNewTable():323  
com.dremio.exec.catalog.DelegatingCatalog.createNewTable():134  
com.dremio.exec.planner.sql.handlers.query.CreateTableHandler.convertToDrel():146  
com.dremio.exec.planner.sql.handlers.query.CreateTableHandler.getPlan():106  
com.dremio.exec.planner.sql.handlers.commands.HandlerToExec.plan():69  
com.dremio.exec.work.foreman.AttemptManager.run():292  
java.util.concurrent.ThreadPoolExecutor.runWorker():1149  
java.util.concurrent.ThreadPoolExecutor$Worker.run():624  
java.lang.Thread.run():748  
Caused By (java.io.IOException) Unable to find bucket named XXXXXX.  
com.dremio.plugins.util.ContainerFileSystem.getFileSystemForPath():295  
com.dremio.plugins.util.ContainerFileSystem.getFileStatus():479  
org.apache.hadoop.fs.FileSystem.exists():1437  
com.dremio.exec.store.dfs.FileSystemWrapper.exists():953  
com.dremio.exec.store.dfs.FileSystemPlugin.createNewTable():1055  
com.dremio.exec.catalog.CatalogImpl.createNewTable():323  
com.dremio.exec.catalog.DelegatingCatalog.createNewTable():134  
com.dremio.exec.planner.sql.handlers.query.CreateTableHandler.convertToDrel():146  
com.dremio.exec.planner.sql.handlers.query.CreateTableHandler.getPlan():106  
com.dremio.exec.planner.sql.handlers.commands.HandlerToExec.plan():69  
com.dremio.exec.work.foreman.AttemptManager.run():292  
java.util.concurrent.ThreadPoolExecutor.runWorker():1149  
java.util.concurrent.ThreadPoolExecutor$Worker.run():624  
java.lang.Thread.run():748

Are there any other options I can tweak or diagnostics I can run to help diagnose the issue?

---

<div class="post-metadata">

**Author:** ![firefly707](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/firefly707/32/646_2.png) [@firefly707](https://community.dremio.com/u/firefly707)\
**Post date:** [November 17, 2018, 2:00am UTC](https://community.dremio.com/t/unable-to-ctas-using-s3/2267/2 "2018-11-17T02:00:17Z")

</div>

I was just testing on 3.0.1 and the following worked. I’m wondering if it’s an issue with bucket names that need to be enclosed with " ".

CREATE TABLE jeffs3.“dremio-upload”.review\_012  
AS SELECT \*  
FROM mongo.yelp.review