Presto Check If Null and Return Default (Nvl Analog)

Presto check if NULL and return default (NVL analog)

The ISO SQL function for that is COALESCE

coalesce(my_field,0)

https://prestodb.io/docs/current/functions/conditional.html

P.S. COALESCE can be used with multiple arguments. It will return the first (from the left) non-NULL argument, or NULL if not found.

e.g.

coalesce (my_field_1,my_field_2,my_field_3,my_field_4,my_field_5)

How to fill null values with 0 in AWS Athena

We can use coalesce, here is Presto documentation on conditions which is what Athena is built on.

select coalesce(myColumn, 0) from my_table_name

Does Presto support NOT IN constructs?

Do not use NOT IN. If any returned values are NULL then it returns no rows. Note: This is how SQL works, not a peculiarity of any particular database.

Instead, use NOT EXISTS:

SELECT DISTINCT t.person_id
FROM my_table t
WHERE NOT EXISTS (SELECT  
                  FROM my_table t2
                  WHERE t2.status = 'hungry' AND
                        t2.person_id = t.person_id
                 );

Actually, I might suggest aggregation for this instead -- you are already doing aggregation essentially with the SELECT DISTINCT:

select person_id
from my_table t
group by person_id
having sum(case when status = 'hungry' then 1 else 0 end) = 0;

Why did not found return type in Custom UDAF of presto

Solved!!

The Custom plugin did not work because it was not a plugin creation, but a way of deploying on EMR was wrong.

Since I did not deploy to all nodes in the procedure I performed, I confirmed the operation by registering the following shell as a bootstrap action.

#/bin/bash
aws s3 cp s3://hogehoge/presto-udaf-1.0-SNAPSHOT.zip /tmp/
sudo mkdir -p /usr/lib/presto/plugin/
sudo unzip -d /usr/lib/presto/plugin/ /tmp/presto-udaf-1.0-SNAPSHOT.zip   

• 10.1. SPI Overview — Presto 0.198 Documentation

Plugins must be installed on all nodes in the Presto cluster
(coordinator and workers).

• Create Bootstrap Actions to Install Additional Software - Amazon EMR

You can use a bootstrap action to copy objects from Amazon S3 to each
node in a cluster before your applications are installed. The AWS CLI
is installed on each node of a cluster, so your bootstrap action can
call AWS CLI commands.

Presto MD5 Hash for multiple Columns in DB Table

I got a solution thanks to Piotr!

SELECT lower(to_hex(md5(to_utf8(concat(trim(user), trim(email)))))) AS UserMailHK FROM table limit 10 

Presto convert timestamp to epoch

You can either use to_unixtime:

presto> select to_unixtime(timestamp '2019-03-07 10:17:03');
     _col0
---------------
 1.551953823E9
(1 row)

or use date_diff to subtract the timestamp from the epoch timestamp:

presto> select date_diff('second', timestamp '1970-01-01', timestamp '2019-03-07 10:17:03');
   _col0
------------
 1551953823
(1 row)

Note that to_unixtime returns a double representing the number of seconds plus milliseconds as a fractional part. If you want just the seconds, you can cast the result to BIGINT:

presto> select cast(to_unixtime(timestamp '2019-03-07 10:17:03') as bigint);
   _col0
------------
 1551953823
(1 row)

HIVE_CANNOT_OPEN_SPLIT : Column column_name type null not supported

This issue is resolved.

For creating an Athena table every field should map exactly to the schema i.e., the order of each field should be the same as that of the schema.

---

Related Topics