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 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...

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 trying to achieve performant record writes from pandas (or ideally Polars if possible) in a Python environment to our Apache Iceberg deployment (with hive metastore) directly, or via Trino quer...

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 ...

The query below does not work. CREATE TABLE test_table (date varchar, id varchar, PRIMARY KEY (date,id)) I can't seem to find any docs on primary keys in Trino.

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...

I have JSON data like this in one of my columns {"foo": 1, "bar": 2} {"foo": 1} and I would like to run a query that returns the keys as an array foo,bar foo

Say I have some data in a table, t. id, arr --, --- 1, [1,2,3] 2, [4,5,6] SQL SELECT AVG(n) FROM UNNEST( SELECT arr FROM t AS n) AS avg_arr This returns the error, 'Mismatched input 'SELECT'. E...

I want to write a recursive query using CTE in Presto to find Employee Hierarchy. Do Presto support recursive query? When I write simple recursion as with cte as(select 1 n union all select cte...

I am new to Presto, and can't quite figure out how to check if a key is present in a map. When I run a SELECT query, this error message is returned: Key not present in map: element SELECT value_m...

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 job_name | run_date A | '2021-0...

why does AWS Athena needs 'spill-bucket' when it dumps results in target S3 location WITH ( format = 'Parquet', parquet_compression = 'SNAPPY', external_location = '**s3://target_bucket_name/my_d...

I have a json table which was created by CREATE TABLE `normaldata_source`( `column1` int, `column2` string, `column3` struct<column4:string>) A sample data is: { "column1": 9...

I'm trying to convert Array(double) to varchar in Presto. A sample value: [99.0,98.0,99.0,95.0,99.0,88.0,90.0,79.0,90.0,56.0,90.0,90.0,92.0,90.0,93.0,99.0] I tried the cast function below: cast(col...

I tried to clear 'null' in my query, but there is still error when run it, keep telling me that 'cannot cast '' to bigint, is there any way to fix it? myquery select m.app, m.hour,m.user_id, m.av...

With the following table (assuming it has many other rows and columns), how could I query it while removing duplicates? order_id customer_name amount bill_type 1 Chris 10 sale 1 Chris 1 t...

I have a long string and I would like to semi-uniquely represent it as a bigint. Ideally I'd just take the hash, but presto hash functions seem to want to return "varbinary", and I can't ...

This question is primarily about older versions of PrestoSQL, which have been resolved in the (now renamed) Trino project as of versions 346. However, Amazon's Athena project is based off of Presto...

Suppose I want to INSERT INTO a static hive partition, can I do that with Presto? The PARTITION keyword is only for hive. INSERT INTO TABLE Employee PARTITION (department='HR') Caused by: co...

Can some experts give some succinct answers to the differences between Presto and Impala from these perspectives? Fundamental architecture design SQL compliance Real-world latency Any SPOF or f...

In my case, Presto connects to a MySQL database which has been configured to be case-insensitive. But any search through Presto seems to be case-sensitive. Questions: 1) Is there a way to configu...

In Presto SQL, SELECT 1 / 3 returns 0, because / performs integer division. But SELECT 1.0 / 3 returns 0.3... How can I get 0.3333333333? (i.e., more specifically, 32- or 64-bits precision instead...