Questions tagged with Amazon Athena
Content language: English
Sort by most recent
I tried to set up an cross-account Athena access. I could see the database in Lake formation, Glue and Athena under target account. At the beginning I don't see any tables in the target Athena console. After I did something in Lake formation console (target account) I could see a table in target Athena console and query it successfully. But I could not see other tables from the same database even I tried many ways. I always got below error even I the gave the KMS access everywhere (both KMS and IAM role) or turn off the kms encryption in Glue. I don't know what is the actual reason.
Below is an example of the error message:
The ciphertext refers to a customer master key that does not exist, does not exist in this region, or you are not allowed to access. (Service: AWSKMS; Status Code: 400; Error Code: AccessDeniedException; Request ID: cb9a754f-fc1c-414d-b526-c43fa96d3c13; Proxy: null) (Service: AWSGlue; Status Code: 400; Error Code: GlueEncryptionException; Request ID: 0c785fdf-e3f7-45b2-9857-e6deddecd6f9; Proxy: null)
This query ran against the "xxx_lakehouse" database, unless qualified by the query. Please post the error message on our forum or contact customer support with Query Id: b2c74c7e-21ed-4375-8712-cd1579eab9a7.
I have already added the permissions pointed out in https://repost.aws/knowledge-center/cross-account-access-denied-error-s3?
Does anyone know how to fix the error and see the cross-account tables in Athena? Thank you very much.
I have created an Iceberg table on Athena with table property **vacuum_min_snapshots_to_keep**. I am running command `VACUUM hamza_iceberg_table;` , upon running this command I am getting below mentioned error:
```
[ErrorCode: INTERNAL_ERROR_QUERY_ENGINE] Amazon Athena experienced an internal error while executing this query. Please contact AWS support for further assistance. You will not be charged for this query. We apologize for the inconvenience.
This query ran against the "db-name" database, unless qualified by the query. Please post the error message on our forum or contact customer support with Query Id: a12te0e1a84-4028-87d3-a6e2
```
I am logged into AWS console as administrator and trying to use Athena to read files on s3 that I don't allow public access to, but it doesn't work. budget policy is as follows, work group is The budget policy is as follows and the work group is the primary Athena SQL one. database
I have confirmed that the database is using the one generated by default and that the Data lake permissions also give All permissions to the IAM user used to log in to the console and I can open and download s3 budget file.
The DDL query for the create table including the s3 LOCATION succeeds, but when I try to hit the select statement
"Permission denied on s3 path: (s3 url)
This query ran against the "default" database, unless qualified by the query. Please post the error message on our forum or contact customer support with Query Id: 444f5547-4c37-4e05-a4a7-d1cd67cb865d"
I think this is probably because the IAM role used in the Athena query that I type in AWS console is different from the IAM user used for login, but I don't know where to refer to the Athena IAM User. (The work group in spark has IAM, but the primary in Athena SQL didn't have IAM.)
```
{
"Version": "2012-10-17",
"Statement": [
{
"Sid": "sample",
"Effect": "Allow",
"Principal": {
"AWS": [
"IAM user login to console"
]
},
"Action": "s3:*",
"Resource": [
"s3 arn",
"s3 arn/*"
]
}
]
}
```
about answer
1.About Output folder, budget policy has already been set.
2.And Glue Data Catalog Policy is configured as this.
```
{
"Version" : "2012-10-17",
"Statement" : [ {
"Effect" : "Allow",
"Principal" : {
"AWS" : [ "my Iam user arn" ]
},
"Action" : "glue:*",
"Resource" : "arn:aws:glue:ap-northeast-1:my id number:*"
} ]
}
```
3.I confirmed s3 is encrypted by Amazon S3 managed keys (SSE-S3).
I mistaked encrypted by my KSM key. but user and administrater key policy is attach to my iam account
but same error happened on AWS management console Athena
error messages s3 url is one I wanted to read from s3. not output folder
Permission denied on s3 path: (s3 url)
Athena queries data directly from Amazon S3. There are no additional storage charges for querying your data with Athena. You are charged standard S3 rates for storage, requests, and data transfer. By default, query results are stored in an S3 bucket of your choice and are also billed at standard S3 rates.
Please clarify my understand is it right . This mean if the data bucket in s3 is lower than 5GB , then Athena should also be free right ?
Hi all,
I'm trying to pivot a table in SQL but I don't know how to. I've got three colomns: day, openinghour and closinghour. Sometimes one day exicsts two times. For example:
day openinghour closinghour
monday 09.00 12.00
monday 13.00 17.00
Now I want to pivot the coloms in a way that the colomn name stands for the day and the row gives the specific information about the openinghours.
For example:
monday tuesday wednesday
09.00 - 12.00 09.00 - 12.00 12.00 - 17.00
13.00 - 16.00
I've added a picture of the data
I have some data in a csv in s3 in the format:
| id | confirmed_at | cancelled_at | suspended_at |
| --- | --- | --- | --- |
| a | 1678414716269 | 1678414716269 | |
| b | 1678316522493 | | |
| c | 1678231915787 | 1678231898220 | 1678231915787 |
I've defined my table with the structure
| column name | data type |
| --- | --- |
| id | string |
| confirmed_at | timestamp |
| cancelled_at | timestamp |
| suspended_at | timestamp |
but when I am using Athena to query my data, it fails on rows where cancelled_at or suspended_at are blank with the error
`Error parsing field value '' for field 1: For input string: ""`
i am using `OpenCsvSerde` but have tried `LazySimpleSerDe` with `'serialization.null.format'=''` but get the same error
is it possible to have athena support having a `timestamp` column that could be null / blank?
Hi All,
I am getting permission error while running the alter table - load partitions from a partitioned cur report table- query on Amazon Athena.
[ErrorCategory:USER_ERROR, ErrorCode:PERMISSION_ERROR], Detail:Amazon Athena experienced a permission error. Please provide proper permission and submitting the query again.
Can anyone suggest a solution for this. Thanks.
```
select * from abdc a where a.employee_id in (select employee_id from abc)
```
The table **abdc** is partition projected on employee_id.
** I got this error while executing this query **
` Amazon Athena experienced an internal error while executing this query. Please try submitting the query again and if the issue reoccurs, contact AWS support for further assistance. We apologize for the inconvenience.
`
The same query got executed when I executed --
```
select * from abdc a where a.employee_id in ('123456789')
```
I have a field with this structure:
Field Name: rulegrouplist
DDL: array<struct<rulegroupid:string,terminatingrule:struct<ruleid:string,action:string,rulematchdetails:string>,nonterminatingmatchingrules:array<string>,excludedrules:string>>
When I query with:
WITH dataset AS (
SELECT *
FROM "Table_Name"
CROSS JOIN UNNEST(rulegrouplist) AS t(t)
)
SELECT t.excludedrules
FROM "table_name"
WHERE date = '2023/03/10'
AND t.excludedrules IS NOT NULL
Limit 1
I get this result:
excludedrules
**[{"rulematchdetails":"null","exclusiontype":"EXCLUDED_AS_COUNT","ruleid":"HostingProviderIPList"}]**
I need to run a query where I can inpect one of the element in this field, in this case, excludedrules, which in the DDL is a field type string.
I wonder if there's an easier way other than apply string functions, as I did here:
WITH dataset AS (
SELECT *
FROM "Table_Name"
CROSS JOIN UNNEST(rulegrouplist) AS t(t)
)
SELECT trim(translate(translate(split_part(t.excludedrules,'ruleid',2),'":', ''),'}]','')) as rule
FROM dataset
WHERE t.excludedrules IS NOT NULL
AND date = '2023/03/10'
Limit 1
I get this result:
HostingProviderIPList
Hi,
We are experiencing the below error from Athena. We don’t understand what is this. Kindly help us to resolve it.
Data source error: {"error":{"code":"DM_GWPipeline_Gateway_MashupDataAccessError","pbi.error":{"code":"DM_GWPipeline_Gateway_MashupDataAccessError","parameters":{},"details":[{"code":"DM_ErrorDetailNameCode_UnderlyingErrorCode","detail":{"type":1,"value":"-2147467259"}},{"code":"DM_ErrorDetailNameCode_UnderlyingErrorMessage","detail":{"type":1,"value":"ODBC: ERROR [HY000] [Simba][Athena] (1040) An error has been thrown from the AWS Athena client. Error Message: HIVE_CURSOR_ERROR: Unexpected end of input stream [Execution ID: f12fbd2d-2a3d-4733-8fbf-d08cba034b2d]"}},{"code":"DM_ErrorDetailNameCode_UnderlyingHResult","detail":{"type":1,"value":"-2147467259"}},{"code":"Microsoft.Data.Mashup.ValueError.DataSourceKind","detail":{"type":1,"value":"Odbc"}},{"code":"Microsoft.Data.Mashup.ValueError.DataSourcePath","detail":{"type":1,"value":"dsn=Simba Athena"}},{"code":"Microsoft.Data.Mashup.ValueError.OdbcErrors","detail":{"type":1,"value":"#table({\"SQLState\", \"NativeError\", \"Message\"}, {})"}},{"code":"Microsoft.Data.Mashup.ValueError.Reason","detail":{"type":1,"value":"DataSource.Error"}}],"exceptionCulprit":1}}}
Cluster URI: WABI-AUSTRALIA-SOUTHEAST-redirect.analysis.windows.net
Activity ID: 384566a5-57db-49e4-bf46-76ef6ba5eb57
Request ID: 34416ea9-e4e4-114b-8343-f3231319a688
Time: 2023-03-10 08:17:11Z
After using a crawler to create a table it shows the following error which I don't know how to fix.
Hello,
We have been testing the existing queries that run successfully on Athena version 2, on Athena version 3. The existing query timeouts in version 3. Can you please help resolving this issue.
Please let me know if there are any questions/comments.
Thanks and regards,
Ani.