I have an Athena table with a string column which looks like: +-------------------+ | employee_size | +-------------------+ | GREATER THAN 2000 | +-------------------+ | 500 - 999 | +-------------------+ | 28.00 | +-------------------+ | unknown | +-------------------+ | 563 |…
My database has a column that contains a JSON array like this: WITH dataset AS ( SELECT * FROM (VALUES ('1', JSON'[{ "name" : "foo" }, { "name" : "bar" }]'), ('2', JSON'[{ "name" : "fizz" }, { "name" :…
I am using Athena to query an S3 bucket and using Athena partition projection as well. Here is my bucket structure: bucket-name/node=[A-Z0-9]{4}/date={yyyy}-{MM}-{dd}/{uuid}.parquet e.g: bucket-name/node=NODE/date=2023-10-10/123.parquet Glue table schema: [ { "Name": "data", "Type": "string" }, { "Name": "effective_time_start", "Type": "bigint" },…
I have a parquet datalake on S3 that I wish to query from. To optimize performance, I aim to compact my files periodically using below script: import boto3 import datetime import math from awsglue.utils import getResolvedOptions import sys args =…
I have a use case as follows: An IoT device is uploading many small files into an AWS S3 input bucket. Upon upload, every single file is processed by an AWS Lambda function to decode the data and write a…
I need to configure Athena to connect to a couple of data sources - MySQL RDS, Postgres RDS, and a custom federation Lambda. Ideally, I'd like to configure these via Cloudformation so I can commit these configurations. I cannot seem…
I'm trying to find a explode_percent column by dividing a column to sum of the bucket. Below is my query - SELECT amount, sum(amount) over ( partition by col1, col2, col3, col4 order by col2, col3, col4 ) as explode_amount,…
I have my table like this: WITH my_table (user_id, date, event_name, value) AS ( values (1,'2021-10-01', 'level', 5), (1,'2021-10-03', 'purchase', null), (1,'2021-10-05', 'purchase', null), (2,'2021-10-02', 'level', 10), (2,'2021-10-03', 'purchase', null) ) SELECT * FROM my_table user_id date event_name level 1…
I know that athena store every query results in the bucket and query data will just accumulate over time. I want to know whether retaining previous query results in S3 would make an impact to performance of my queries. For…
Would anyone have any advice on how to hardcode a specific date into my SELECT clause? I'm trying to force a date into my query so I can reference it later, but unsure how to go about this. Thanks in…