I'm extracting data from a json column in presto and getting the output in a array like this [AL,null,NEW].The problem is i need to remove the null since the array has to be mapped another array.I ...

I recently ran into the following error "AthenaQueryError: Athena query failed: "NOT_SUPPORTED: Unsupported Hive type", and for this I followed this stack overflow link: converting t...

I'm trying to obtain a random sample of N rows from Athena. But since the table from which I want to draw this sample is huge the naive SELECT id FROM mytable ORDER BY RANDOM() LIMIT 100 takes f...

It seems like there is no native function for that purpose in Presto SQL. Do you know any way to efficiently aggregate a group and return its median?

I've been unable to get any query to work against my AWS Glue Partitioned table. The error I'm getting is HIVE_METASTORE_ERROR: com.facebook.presto.spi.PrestoException: Error: type expected at ...

I have a table Name pets -------------- Andy {dog:2, cat:1, bird:4} John {tiger:3, elephant:1, fish:2} Mary {dog:2, pig:2} I want the find the pet type with maximum count for each person. In the e...

On AWS Athena, I am trying to reuse computed data using a WITH clause, e.g. WITH temp_table AS (...) SELECT ... FROM temp_table t0, temp_table t1, temp_table t2 WHERE ... If the query is fast, t...

I am working on query to get cumulative distinct count of uids on daily basis. Example : Say there are 2 uids (100,200) appeared on date 2016-11-01 and they also appeared on next day with new uid...

Is this possible in SQL (preferably Presto): I want to reshape this table: id, array 1, ['something'] 1, ['something else'] 2, ['something'] To this table: id, array 1, ['something', 'somethin...

I have a column with JSON arrays like below: {data=[{"name":"col1","min":0,"max":32,"avg":29}, {"name":"col2","min":1...

I'm on presto and have a date formatted as varchar that looks like - 7/14/2015 8:22:39 AM I've looked the presto docs and tried various things(cast, date_format, using split_part to parse and t...

I use Presto and I do not know the function to get MONTH's name from a date column. I can get MONTH number using MONTH(<date>) function but I'm not able to get MONTH name. I've seen ways wh...

SELECT sales_invoice_date, MONTH( DATE_TRUNC('month', CASE WHEN TRIM(sales_invoice_date) = '' THEN DATE('1999-12-31') ELSE DATE_PARSE(sales_invoice_date, '%m/%d/%Y') END) ) AS DT FROM testd...

I have a number want to format it as percentage in Presto. I want to format 0.18932 as 18.93%. I tried: format('%s%%', decimal_number*100) format('%s%%', round((a.decimal_number*100), 2)) Here is ...

I want to get result in JSON format, below is the structure of object obj UUID string MobileCountryCode string UserState []struct { Mobile int64 IsNewUser bool } when i do simple select of ...

I have an array in Presto and I'd like to count how many times each element occurs in it. For example, I have [a, a, a, b, b] and I'd like to get something like {a: 3, b: 2}

My partitions look like these event_year=2019/event_week=37/event_date=2019-09-10 event_year=2019/event_week=42/event_date=2019-10-13 event_year=2019/event_week=8/event_date=2019-02-20 event_year=2...

I've got an Athena table where some fields have a fairly complex nested format. The backing records in S3 are JSON. Along these lines (but we have several more levels of nesting): CREATE EXTERNAL ...

In Presto SQL, unlike regular SQL, intervals must be created with inverted commas: INTERVAL '1' DAY rather than INTERVAL 1 DAY I am trying to generate a set of dates as described here: https:...

typically to create a table in Presto (from existing db tables), I do: create table abc as ( select... ) But to make my code simple, I've broken out subqueries like this: with sub1 as ( select....

I have some issue while formatting a timestamp with Amazon Athena service. select date_format(current_timestamp, 'y') Returns just 'y' (the string). The only way I found to format dates in Amazo...

A field is defined to be array(varchar) in modeanlyatic. I want to search for records with field contains a certain text pattern, says 'ABCD'. If I run this SQL: select * from data_table where ...

I have timestamps stored in time since epoch (ms) and I would like to query and display results using a date formatted like 'yyyy-mm-dd'.

I have an Athena table partitioned by year, month, day with the following definition CREATE EXTERNAL TABLE `my_table`( `price` double) PARTITIONED BY ( `year` int, `month` int, `day` int) ...

For example, one column in my table is an array, I want to check if that column contains an element that contains substring "denied" (so elements like "denied at 12:00 pm", "denied by admin" will a...