Questions tagged with Amazon Athena

My files are csv files with 3 fields using tab separation. The builtin classifier CSV creates a schema of 3 strings for the 3 attributes a:string b:string c:string however my last attribute **c** is a json string. I would like to know if it's possible using a costum classifier to create an extra attribute **endpoint** that results from som pattern matching grok or regex. Let's say , if the json string **c** looks like below . ``` {"log":{"timestampReceived":"2023-03-10 01:43:24,563 +0000","component":"ABC","subp":"0","eventStream":"DEF","endpoint":"sales"}} ``` I would like to grab the endpoint:sales into a new attribute of my table and use it as a partition. it would end up like something below a:string b:string c:string **endpoint**:string (partitioned)lg...

I created a data source with correct credentials to be setup for lambda function connector, for example: postgres://jdbc:postgresql://xxx.us-west-2.rds.amazonaws.com:5432/xxx?user=xxx&password=xxx. VPC, Subnets, Security groups are the same my RDS. But on the Amazon Athena > Query editor, when I select the created data source and click the Database dropdown, then I got the below issue: ****Failed to invoke lambda function due to com.amazonaws.services.lambda.invoke.LambdaFunctionException: org.postgresql.util.PSQLException: The connection attempt failed.**** Do you have any suggestions to fix this? Thanks.lg...

Hi, i'm trying to upgrade from athena version 2 to 3 and the following query fails now with the error bellow. I run the select alone and it works fine. Did anything change in the new version that i missed ? lg...

Using Glue we can crawl snowflake table properly in catalog but Athena failed to query table data: HIVE_UNSUPPORTED_FORMAT: Unable to create input format Googling results suggested it's because of table created by crawler has empty "input format", "Output format"... yes they are empty for this table crawled from Snowflake. So the question is 1 why didn't crawler set them? (crawler can classify the table is snowflake correctly) 2 what the values should be if manual edit is needed? Is Snowflake table able to be queried by Athena? Any idea? Thanks,lg...

Hi, I'm creating a dashboard for operators to download the athena query results. The ID column values contain hyphens `-` and For example, if table contains the following data | id | name | | --- | --- | | `-xyz` | `First example` | | `a-b-c` | `Second example` | The generated csv contains a extra single quote in the id column at the first row ```csv "id","name" "'-xyz","First example" "a-b-c","Second example" ``` Is there any way to avoid it?lg...

I want to save parquet file to s3 by partition mode default is not dynamic and the old data will be erased . How can I config athena spark session setting to dynamic mode?lg...

I have been using the below statement to retrieve the sum of all the values from that column as shown below SUM(CAST(column1 AS DOUBLE)) as cnt but now it is erroring out with the below error INVALID_CAST_ARGUMENT: Cannot cast '' to DOUBLE This query ran against the "imdsout" database, unless qualified by the query. Please post the error message on our forum or contact customer support with Query Id: 004997cb-e355-445c-b502-28ecd8f8aaaflg...

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.lg...

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 ```lg...

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)lg...

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 ?lg...

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 datalg...