I have an SQL query like this (written for PostgreSQL): SELECT * FROM users WHERE users.company_id = ANY(ARRAY[945387, 969109, 1460013, 1460044]) AND users.profession_id = ANY(ARRAY[2738, 6388]) …
Tag: amazon-athena
SYNTAX_ERROR: ‘“LastName”’ must be an aggregate expression or appear in GROUP BY clause
I have a two tables, main_table & staging_table, main_table contains original data whereas staging_table contains the few of the updated records that I have to add into with main_table data, and …
How to break a row into multiple rows based on a column value in Athena (Presto)?
I have a Athena table that has a column containing array of values. I want to create multiple rows from one row such that the column of array can be changed to contain only 1 value. E.g. : to look like : How can I write my query to achieve this? Answer I think unnest() does what you want:
Presto how to find start date given week
I want to find start date from given ISO week (which can range from 1-53, Monday as starting day) and year using Presto SQL query. i.e. year – 2020 and week – 2 should return 06/01/2020 Is there any inbuilt function for this ? Table structure: Answer There’s no direct way for constructing a …
PrestoDB/AWS Athena- Retrieve a large SELECT by chunks
I have to select more than 1.9 billion rows. I am trying to query a table hosted in a DB in AWS ATHENA console. The table is reading parquet files from the a S3 bucket. When I run this query: My query seems to time-Out as there are 1.9 billion rows that are returned when I run a COUNT on
Amazon Athena returning “mismatched input ‘partitioned’ expecting {, ‘with’}” error when creating partitions
I’d like to use this query to create a partitioned table in Amazon Athena: Unfortunately I don’t get the error message which tells me the following: line 3:2: mismatched input ‘partitioned’ expecting {, ‘with’} Answer The quotes around ‘PARQUET’ seemed to be cau…
how to return a unique set of characters within a field (SQL)?
Basically i am trying to return any fields within the tag column that contain up_ up1_ up2_ up3, up4_. the problem i’m facing is i only want to show these tags and not the others. The field …
Search for exact string value in JSON
I have a column stored in JSON that looks like column name: s2s_payload Values: I want to query exact values in the array rather than returning all values for a certain data type. I was using JSON_EXTRACT to get distinct counts. If I want to filter where “”eventtype””:””sea…
Format pivot data with multiple conditions
My current query is SELECT COUNT (DISTINCT(“json_extract_scalar”(“data”, ‘$.user_id’))) AS users, event, date(timestamp) FROM tableName WHERE category=’category’ GROUP BY event, date(timestamp) ORDER …
How to fix ‘must be an aggregate expression or appear in GROUP BY clause’ with ‘as’ clause
I am trying to get the number of request by hour of my CloudFront distribution using athena query. I created cloudfront_logs table guided by this Link in my sample_db Below is the query I made to …