I’m writing a query (using Athena – AWS) and I need to substitute all values from a group if there’s at least one occurrence of another value. To exemplify: My original dataframe What I need: case when v1, v2 or v3 assume value 1 group by ID, then the whole column that contains 1 group by ID should be 1.
Tag: amazon-athena
SQL many-to-one join – how to get blank fields instead of duplicated values
I have 3 tables: When I join this tables by ticket_id and query, i get result like this: But I want to blank duplicated values and get result like this: Is it possible to get result set like this? I’m working in AWS Athena(Presto SQL), but I would really appreciate your ideas/suggestions on this regardless of the dbms. Thanks! Answer
Query validation for Amazon Athena using AWS SDK
I am using the AWS SDK to execute Amazon Athena queries using Java and what I would like to do is to have some way of ensuring only SELECT queries are executed. This is to make sure queries like DROP or INSERT are not executed. So is there any way I can perform this check using the AWS SDK without
How to join two tables while only selecting the highest day of each month from one table
I have two tables. One with metadata, one with billing data. I need to join those effiently in order to assign metadata to costs. Table 1 (metadata) looks like this: Table 2 (billing data) looks like this: For each combination of year, month, id in Table 2, there is an corresponding ID in Table 1. For each year, month, id
Casting Decimal to Currency in AWS Athena
I have a field value(decimal(23, 2)) and I am querying over it like this: sum(value) and as output i get 1200000.32 I am trying to cast it to currency to get $1.200.000,32 like this: How can I get the desired currency type? Answer There is no money type in presto. And Amazon’s version of presto does not support format and
How to create partitioned table from other tables in Amazon Athena?
I am looking to create a table from an existing table in Amazon Athena. The existing table is partitioned on partition_0, partition_1, and partition_2 (all strings) and I would like this partition to carry over. Here is my code: Trying to run this gives me an error at the FROM line, saying “mismatched input ‘by’. expecting: ‘(‘, ‘,’,”…. Status code:
generate date range between min and max dates Athena presto SQL sequence error
I’m attempting to generate a series of dates in Presto SQL (Athena) using unnest and sequence something similair to generate_series in postgres. my table looks like I’m aiming for an output as follows I’ve attempted to use the following query to achieve this – however I get an error when trying to unnest my date sequence which yields the following
How to parsing Json in AWS Athena?
I want to parse the JSON column in Athena but I have a problem in one column. The column includes an escape character. I don’t know if this problem. I want to reach to Message. You can see below sample data and Message under Return. Thanks This is the JSON. Answer If you take only the object which contains return
Proper Case in Athena SQL
I’m new to Athena and trying to convert a field ‘Country’ to Proper Case. For example: united states -> United States Is there a way to do this in Athena? Answer Turning a comment into an answer: Athena engine version 2 gives in its RegEx section as example for regexp_replace(string, pattern, function)
How to combine multiple records in one in SQL
I have a SQL table that looks something like this: OP ID First name Last name Phone number I 123 John Smith 888-555 U 123 777-555 I have to combine this rows through select query into something like this: ID First name Last name Phone number 123 John Smith 777-555 I have trouble writing query because my only idea is