Questions tagged with Amazon Athena
Content language: English
Sort by most recent
I am trying to analyze CloudFront standard logs using Amazon Athena. I get the following error after running a query:
GENERIC_INTERNAL_ERROR: S3 service error
This query ran against the "<DatabaseName>" database, unless qualified by the query.
Can anyone explain what this error means and how to resolve it?
Can anyone tell how this has suddenly been thrown up, despite me running queries earlier
I do a crawler to load all my S3 csv files to Glue Data Catalog. Now I want to create a glue job to execute ETL (create and drop temporary tables, select and insert data to tables in Data Catalog) But in the glue job as a python shell I have to split my sql statements to execute one by one. With the following code, I got an error.
client = boto3.client('athena')
client.start_query_execution(QueryString = """drop table if exists database1.temptable1 ;CREATE EXTERNAL TABLE IF NOT EXISTS temptable1(id int ) """, ResultConfiguration = config)
Is there any way to run multiple sql statements in glue job?
Data layer is not my thing and I need some guidance.
I create a glue crawler to extract compressed JSON files and store them in an aws S3 bucket. I recently learned that I can use Athena to directly connect to the glue database. When I do select * from *table-name* It starts to load but then errors with long string of stuff
HIVE_METASTORE_ERROR: Error: : expected at the position 407 of 'struct<http:struct<status_code:int,url_details:struct<path:string,queryString:struct<prefix:string,versioning:string,logging:string,encoding-type:string,nodes:string,policy:string,acl:string,policyStatus:string,replication:string,notification:string,tagging:string,website:string,encryption:string,size:string,limit:string,hash:string,accelerate:string,publicAccessBlock:string,code:string,protocol:string,G%EF%BF%BD%EF%BF%BD%EF%BF%BD\,%EF%BF%BD%EF%BF%BD%EF%BF%BD`~%EF%BF%BD%00%EF%BF%BD%EF%BF%BD{%EF%BF%BD%D5%96%EF%BF%BDw%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%3C:string,cors:string,object- etc etc etc.
I can load one small table but the others fail.
I can't find a proper way of setting the correct data type for a timestamp attribute on my Athena table **parquet** in order to query for time intervals.
im creating the table via a crawler on parquet files resultant from a glue interactive job (PySpark)
**below my Athena table and the timestamp formats im trying **
1. ts (datatype **timestamp**) : 2023-02-07 23:59:59.460000
2. ts_iso_stamp (datatype **timestamp**) : 2023-02-07 23:59:59.460000
3. ts_iso_str (datatype **timestamp**) : "2023-02-07T23:59:59.460000+00:00"
**im applying this mapping in my glue job to create the various formats**
```
ApplyMapping.apply(frame = dyf, mappings = [ ("date", "date", "date", "date")
,("ts", "string", "ts", "timestamp")
,("ts_iso", "string", "ts_iso_stamp", "timestamp")
,("ts_iso", "string", "ts_iso_str", "string") ]
```
**ts_iso looks like this when you run .show() on the Dynamic Dataframe.** a valid iso format as far as I understand.
```
"ts_iso": "2023-02-07T23:59:59.460000+00:00"
```
query examples failing same error
```
select * from searchdb.queries_lu where appid = 'aHs7sPcX66ytv2FqmRnv'
AND ts >= TIMESTAMP '2023-02-07 23:59:59.405000'
AND ts <= TIMESTAMP '2023-02-08 00:00:00.637000';
select * from searchdb.queries_lu where appid = 'aHs7sPcX66ytv2FqmRnv'
AND ts_iso_stamp >= TIMESTAMP '2023-02-07 23:59:59.405000'
AND ts_iso_stamp <= TIMESTAMP '2023-02-08 00:00:00.637000';
select * from searchdb.queries_lu where appid = 'aHs7sPcX66ytv2FqmRnv'
AND ts_iso_str >= TIMESTAMP '2023-02-07T23:59:59.405000+00:00'
AND ts_iso_str <= TIMESTAMP '2023-02-08T00:00:00.637000+00:00';
```
Athena DDL
create table searchdb.queries_ac
(
ts timestamp(23, 3) ,
ts_iso_stamp timestamp(23,3),
ts_iso_str string(255),
endpoint string(255),
appid string(255),
requestid string(255),
"in" string(255),
q string(255),
qq string(255),
results_id string(255),
results_count string(255),
requestline string(255),
"date" string(255)
);
My requirement is like : I want to query the iceberg table present in another AWS account.
Let's say I am a user of account A and want to query account B's iceberg tables present in that account's glue. I followed the steps from AWS docs [glue cross account](https://docs.aws.amazon.com/athena/latest/ug/security-iam-cross-account-glue-catalog-access.html) and [s3 cross account](https://docs.aws.amazon.com/AmazonS3/latest/userguide/example-walkthroughs-managing-access-example2.html)
to attach permission to accountB's glue and s3 bucket where the data is stored and the policy contains required permission with principal `account A:root` means any user from account A should be able to query. Then I also attached the glue and s3 polices to the account A's user.
Then I go to Athena and create the data source as glue with catalog ID as account B's account ID and then I am able to see all the glue databases and tables of account B.
But when I query the table such as `select * from table` it gives the error as `HIVE_METASTORE_ERROR: Table storage descriptor is missing SerDe info`.
But I am able to query the table properly in account B. But yeah it's serDeInfo is empty.
Hi,
Can someone explain to me why my query works as long as I use an inner join but as soon as I change it to an Left Join I got the error: GENERIC_INTERNAL_ERROR: Unexpected identifier in logical plan: loc.
It's the table on row 38. As long as I use Inner it works.
Hi,
Trying to use a recursive cte with Athena and I get this error :
NOT_SUPPORTED: Recursion depth limit exceeded (10). Use 'max_recursion_depth' session property to modify the limit.
How would I set this session property from the console ?
Thanks!
Getting following error while executing Node and edge sql : ERROR: NOT_SUPPORTED: ROW comparison not supported for fields with null elements
This query ran against the "wow_athena" database, unless qualified by the query
Any Idea ?
I have written a lambda function to convert json file in raw s3 bucket into parquet file and gets uploaded directly it to the cleansed s3 bucket. I cannot delete json files since i want to convert it parquet . So while testing the lambda function, parquet format is shown in the bucket and destination table is also in parquet format . but don't know why athena is showing off this error. please help me with this one.
Not able to display paruqet file in athena , which json is converted into parquet using lambda function
I am having this error when querying the information table in Athena. I wonder if anyone else received this error.
```SQL
select distinct substr("table_name", 12, 8) as "dt" from "information_schema"."tables"
where "table_name" LIKE 'catalog_id_%' AND "table_schema" = 'catalog-id'
order by 1 desc;
```
```
GENERIC_INTERNAL_ERROR: java.lang.RuntimeException: java.lang.InterruptedException: sleep interrupted
This query ran against the "catalog-ph" database, unless qualified by the query. Please post the error message on our forum or contact customer support with Query Id: 1d599830-3a43-4326-b662-d26573ac49d8
```
Best Regards,