Questions tagged with Amazon Athena
Content language: English
Sort by most recent
I have a string type for date and in that column, it has the word 'None'
My query for casting the date is below - *getting only the Month and Year on it*,
date_format(cast(c.enddate as date), '%M') as "Month",
date_format(cast(c.enddate as date), '%Y') as "Year"
ERROR prompted
INVALID_CAST_ARGUMENT: Value cannot be cast to date: None-
Can somebody help me with this problem, so that I can still get the Month and Year only?
Thank you in advance!
Hi, I'd appreciate AWS Athena support for TIMESTAMP data type with microsecond precision for all row formats and table engines. Currently, the support is very inconsistent. See the SQL script below.
```
drop table if exists test_csv;
create external table if not exists test_csv (
id int,
created_time timestamp
)
row format serde 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
with serdeproperties('separatorChar'=',', 'quoteChar'='"', 'escapeChar'='\\')
location 's3://my-bucket/tmp/timestamp_csv_test/';
-- result: OK
drop table if exists test_parquet;
create external table if not exists test_parquet (
id int,
created_time timestamp
)
row format serde 'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe'
stored as inputformat 'org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat'
outputformat 'org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat'
location 's3://my-bucket/tmp/timestamp_parquet_test/'
tblproperties ('parquet.compress' = 'snappy');
-- result: OK
drop table if exists test_iceberg;
create table if not exists test_iceberg (
id int,
created_time timestamp
)
location 's3://my-bucket/tmp/timestamp_iceberg_test/'
tblproperties ( 'table_type' ='iceberg');
-- result: OK
insert into test_csv values (1, timestamp '2023-03-22 11:00:00.123456');
/*
result: ERROR [HY000][100071] [Simba][AthenaJDBC](100071) An error has been thrown from the AWS Athena client. GENERIC_INTERNAL_ERROR: class org.apache.hadoop.hive.serde2.objectinspector.primitive.WritableIntObjectInspector cannot be cast to class org.apache.hadoop.hive.serde2.objectinspector.primitive.StringObjectInspector (org.apache.hadoop.hive.serde2.objectinspector.primitive.WritableIntObjectInspector and org.apache.hadoop.hive.serde2.objectinspector.primitive.StringObjectInspector are in unnamed module of loader io.trino.server.PluginClassLoader @1df1bd44). If a data manifest file was generated at 's3://my-bucket/athena_results/ad44adee-2a80-4f41-906a-17aa5dc27730-manifest.csv', you may need to manually clean the data from locations specified in the manifest. Athena will not delete data in your account. [Execution ID: ***]
*/
insert into test_parquet values (1, timestamp '2023-03-22 11:00:00.123456');
-- result: OK
select * from test_parquet;
-- result: OK DATA: 1,2023-03-22 11:00:00.123000 BUT THE TIMESTAMP VALUE IS TRUNCATED TO MILLISECONDS!
insert into test_iceberg values (1, timestamp '2023-03-22 11:00:00.123456');
-- result: OK
select * from test_csv;
select * from test_iceberg;
-- result: OK DATA: 1,2023-03-22 11:00:00.123456 THIS IS FINE
```
Before I upgrade Athena to version 3, I'd like to know if I can roll it back to version 2 afterwards.
If I can roll it back, then I can test in my main dev environment. If I cannot roll Athena back to version 2, then I need a new environment to test the upgrade.
(I didn't find this question after searching on athena rollback, athena upgrade, or athena version)
Thanks!
Started getting this error today when querying data from Athena in a table created from parquet files in our S3 bucket:
I'm thinking a bad file somewhere but unable to narrow it down. Any steps or tips to resolve would be much appreciated. Thanks!
Getting the below error while querying on Athena -
HIVE_INVALID_PARTITION_VALUE: Invalid partition value 'Unsaved-2023-03 22:00:00' for TIMESTAMP partition key: ingest_timestamp=Unsaved-2023-03 22%3A00%3A00
This query ran against the "due_eventdb" database unless qualified by the query. Please post the error message on our forum or contact customer support with Query Id: b923ebaa-8ea4-42f4-bbb4-b5f6b1d6041a
I have deleted the following directory "Unsaved-2023-03 22%3A00%3A00" from the S3 bucket still getting the same error.
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)
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.
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 ?
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,
asked 8 days ago
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?
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?
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-28ecd8f8aaaf