Stack Overflow
Stack Overflow

Questions tagged [amazon-athena]

Amazon Athena is a service for running SQL queries against data stored on Amazon S3. Amazon Athena is part of Amazon Web Services (AWS).

Amazon Athena is a service for running SQL queries against data stored in files on Amazon S3. Amazon Athena is part of Amazon Web Services (AWS).

Athena is powered by the Presto query engine and uses Apache Hive Metastore for database and table definitions. It supports both dynamic and static partitions for tables. Athena supports data stored in delimited text files, JSON, ORC, Avro, and Parquet.

Athena is a serverless tool - there is no infrastructure to manage, and cost is calculated by the quantity of data scanned during each query.

See the Athena Documentation for more.

3440 questions
68
votes
13 answers

Amazon Athena: no viable alternative at input

While creating a table in Athena; it gives me following exception: no viable alternative at input
Deepak Singhal
  • 10,568
  • 11
  • 59
  • 98
66
votes
7 answers

What is difference between AWS S3 Select and AWS Athena?

I am trying to understand what is difference between AWS Athena service and the newly released S3 select (still in preview). How are use cases different for both of those? It seems both help in selecting partial data from S3.
user3444718
  • 1,485
  • 6
  • 22
  • 32
58
votes
2 answers

How to get input file name as column in AWS Athena external tables

I have external tables created in AWS Athena to query S3 data, however, the location path has 1000+ files. So I need the corresponding filename of the record to be displayed as a column in the table. select file_name , col1 from table where…
Rajeev
  • 1,031
  • 2
  • 13
  • 25
54
votes
4 answers

Athena greater than condition in date column

I have the following query that I am trying to run on Athena. SELECT observation_date, COUNT(*) AS count FROM db.table_name WHERE observation_date > '2017-12-31' GROUP BY observation_date However it is producing this error: SYNTAX_ERROR: line 3:24:…
Eugene Brown
  • 4,032
  • 6
  • 33
  • 47
46
votes
3 answers

How to make MSCK REPAIR TABLE execute automatically in AWS Athena

I have a Spark batch job which is executed hourly. Each run generates and stores new data in S3 with the directory naming pattern DATA/YEAR=?/MONTH=?/DATE=?/datafile. After uploading the data to S3, I want to investigate it using Athena. Also, I…
YangZhao
  • 471
  • 1
  • 4
  • 11
41
votes
4 answers

How to create AWS Glue table where partitions have different columns? ('HIVE_PARTITION_SCHEMA_MISMATCH')

As per this AWS Forum Thread, does anyone know how to use AWS Glue to create an AWS Athena table whose partitions contain different schemas (in this case different subsets of columns from the table schema)? At the moment, when I run the crawler over…
rjmurt
  • 1,135
  • 2
  • 9
  • 25
41
votes
1 answer

Presto check if NULL and return default (NVL analog)

Is there any analog of NVL in Presto DB? I need to check if a field is NULL and return a default value. I solve this somehow like this: SELECT CASE WHEN my_field is null THEN 0 ELSE my_field END FROM my_table But I'm curious if there…
Sasha Shpota
  • 9,436
  • 14
  • 75
  • 148
33
votes
7 answers

Can I delete data (rows in tables) from Athena?

Is it possible to delete data stored in S3 through an Athena query? I have some rows I have to delete from a couple of tables (they point to separate buckets in S3). I couldn't find a way to do it in the Athena User Guide:…
Guillermo Mirandes
  • 435
  • 1
  • 5
  • 8
32
votes
5 answers

converting to timestamp with time zone failed on Athena

I'm trying to create to following view: CREATE OR REPLACE VIEW view_events AS ( SELECT "rank"() OVER (PARTITION BY "tb1"."innerid" ORDER BY "tb1"."date" ASC) "r" , "tb2"."opcode" , "tb1"."innerid" , "tb1"."date" ,…
Gal Itzhak
  • 449
  • 1
  • 7
  • 14
32
votes
5 answers

Aws Athena - Create external table skipping first row

I'm trying to create an external table on csv files with Aws Athena with the code below but the line TBLPROPERTIES ("skip.header.line.count"="1") doesn't work: it doesn't skip the first line (header) of the csv file. CREATE EXTERNAL TABLE mytable ( …
Erica
  • 1,608
  • 2
  • 21
  • 32
31
votes
5 answers

Athena vs Redshift Spectrum

I am kind of evaluating Athena & Redshift Spectrum. Both serve the same purpose, Spectrum needs a Redshift cluster in place whereas Athena is pure serverless. Athena uses Presto and Spectrum uses its Redshift's engine Are there any specific…
Mukund
  • 916
  • 2
  • 11
  • 18
30
votes
8 answers

How to Create Dataframe from AWS Athena using Boto3 get_query_results method

I'm using AWS Athena to query raw data from S3. Since Athena writes the query output into S3 output bucket I used to do: df = pd.read_csv(OutputLocation) But this seems like an expensive way. Recently I noticed the get_query_results method of boto3…
Niv Cohen
  • 1,078
  • 2
  • 11
  • 21
28
votes
1 answer

AWS Athena SQL Query Error with timestamp

I have a table in AWS Athena having column named 'servertime' with the data type of timestamp. I run a query like this select * from table_name where servertime between '2018-04-01 00:00:00' and '2018-04-05 23:59:59'; It gives me this error: Your…
Sumit Kumar Sagar
  • 381
  • 1
  • 3
  • 6
27
votes
7 answers

Create AWS Athena view programmatically

Can you create views in Amazon Athena? outlines how to create a view using the User Interface. I'd like to create an AWS Athena View programatically, ideally using Terraform (which calls CloudFormation). I followed the steps outlined here:…
tjheslin1
  • 1,378
  • 6
  • 19
  • 36
27
votes
2 answers

Amazon Athena - Converting Timestamp to Date?

Looking at the Date/Time Athena documentation, I don't see a function to do this, which surprises me. The closest I see is date_trunc('week', timestamp) but that results in something like 2017-07-09 00:00:00.000 while I would like the format to be…
Louis
  • 1,123
  • 5
  • 15
  • 24
1
2 3
…
99 100