I have an array of unknown length in AWS Athena. I want to get all elements expect for the first one and concatenate into a string. I can do with a known length, but I don’t see how for unknown length. In this example: What I want is myslice_joined. I could use slice because I knew it had four elements,
Tag: amazon-athena
Querying latest snapshot partition with Athena
I have a partitioned table with daily snapshots from from glue. When I use athena to query it queries across all partitions. Is there a way to get Athena to automatically only get the latest snapshot? Or do I have to explicitly state what partition I want to query if I want to avoid querying across all snapshots? Answer If
Amazon Athena set location to single csv file
I would like to set the location value in my Athena SQL create table statement to a single CSV file as I do not want to query every file in the path. I can set and successfully query an s3 directory (object) path and all files in that path, but not a single file. Is setting a single file as
Presto SQL save query results in variable
I have a database that I am querying with athena. I am using subqueries to select a subset of the data like so can I save the query results of in a variable VAR so that we need not query it again and again and also to make query look cleaner? Answer There is no such concept as variable in
AWS Athena: How can we get integer value as string with thousand comma separator in AWS Athena
How can we show integer numbers with thousand comma separator. So, by executing the below statement select * from 1234567890 How can we get the result as 1,234,567,890 Answer You can achieve this by casting number to string and using regex: Output: _col0 1,234,567,890 123,456,789 12,345,678 1,234,567 123,456 12,345 1,234 123
sql – query for all values in table with limit
I have an SQL query which I run in Amazon Athena: where I order by B and take the first row only for the value 1000 for A. However I want to run this query for all values of A in T i.e for each A in T get the first row only and append to the results. How do
SQL – Extracting first 5 consecutive numbers from alphanumeric string
I am using AWS Athena, so functions are a bit limiting. But essentially I want to extract the first 5 consecutive and sequential numbers from a alphanumeric field. From the first example, you can see it ignores the first 1 because there aren’t 4 trailing numbers. I want to find and extract the first 5 numbers that are given together
Concatenate variable in Athena SQL query from Python Lambda function
I have a Python Lambda function that creates a SQL table in Athena. How do I properly concatenate variables in my query? When I set the LOCATION value, I receive the error response below. The function runs successfully if I hard code the LOCATION value. Error response: Lambda function: Thank you. Answer Have you tried to use Python’s format method?
SQL – Guarantee at least n unique users with 2 appearances each in query
I’m working with AWS Personalize and one of the service Quotas is to have “At least 1000 records containing a min of 25 unique users with at least 2 records each”, I know my raw data has those numbers but I’m trying to find a way to guarantee that those numbers will always be met, even if the query is
Count ROW type item Athena / Presto
I have an Athena query like this and the result is I would like to count the number of records per day per devices to have a result like this EDIT My dataset is actually like this Here the expected results would be : Answer You can cast your json to map and count number of keys: Output: device_id date