Stack Overflow
Stack Overflow

Questions tagged [presto]

Presto is an open source distributed SQL query engine for running analytic queries against data sources of all sizes ranging from gigabytes to petabytes. The community version of Presto is now called Trino. Amazon serverless query service called Athena is using Presto under the hood.

What is Presto?

Presto is an open source distributed SQL query engine for running interactive analytic queries against data sources of all sizes ranging from gigabytes to petabytes.

Presto was designed and written from the ground up for interactive analytics and approaches the speed of commercial data warehouses while scaling to the size of organizations like Facebook.

What can it do?

Presto allows querying data where it lives, including Hive, HBase, relational databases or even proprietary data stores. A single Presto query can combine data from multiple sources, allowing for analytics across your entire organization.

Presto is targeted at analysts who expect response times ranging from sub-second to minutes. Presto breaks the false choice between having fast analytics using an expensive commercial solution or using a slow "free" solution that requires excessive hardware.

References

3114 questions
59
votes
9 answers

Presto SQL - Converting a date string to date format

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 then cast) and am not getting this to convert to a date…
Moosa
  • 3,126
  • 5
  • 25
  • 45
58
votes
2 answers

How to get input file name as column in AWS Athena external tables

I have external tables created in AWS Athena to query S3 data, however, the location path has 1000+ files. So I need the corresponding filename of the record to be displayed as a column in the table. select file_name , col1 from table where…
Rajeev
  • 1,031
  • 2
  • 13
  • 25
58
votes
2 answers

Presto/Trino - static date and timestamp in where clause

I'm pretty sure the following query used to work for me on Presto: select segment, sum(count) from modeling_trends where segment='2557172' and date = '2016-06-23' and count_time between '2016-06-23 14:00:00.000' and '2016-06-23 14:59:59.000'; group…
Tal Joffe
  • 5,347
  • 4
  • 25
  • 31
54
votes
4 answers

Athena greater than condition in date column

I have the following query that I am trying to run on Athena. SELECT observation_date, COUNT(*) AS count FROM db.table_name WHERE observation_date > '2017-12-31' GROUP BY observation_date However it is producing this error: SYNTAX_ERROR: line 3:24:…
Eugene Brown
  • 4,032
  • 6
  • 33
  • 47
42
votes
3 answers

Presto equivalent of MySQL group_concat

I'm new to Presto and looking to get the same functionality as the group_concat function in MySQL. Are the following two equivalent? If not, any suggestions for how I can recreate the group_concat functionality in Presto? MySQL: select a, …
Mike Moyer
  • 431
  • 1
  • 4
  • 4
41
votes
1 answer

Presto check if NULL and return default (NVL analog)

Is there any analog of NVL in Presto DB? I need to check if a field is NULL and return a default value. I solve this somehow like this: SELECT CASE WHEN my_field is null THEN 0 ELSE my_field END FROM my_table But I'm curious if there…
Sasha Shpota
  • 9,436
  • 14
  • 75
  • 148
40
votes
3 answers

Why Presto is faster than Spark SQL

Why is Presto faster than Spark SQL? Besides what is the difference between Presto and Spark SQL in computing architectures and memory management?
Long.zhao
  • 1,085
  • 2
  • 11
  • 16
34
votes
3 answers

How do I escape a single quote in Presto?

How can I escape a ' (single quote) in Presto? This is where I am trying to use it select count(*) as count from uploads where title not in ('Driver's License') I've tried the usual escapes: , 'Driver\'s License', "Driver's License", E'Driver\'s…
scottshepard
  • 781
  • 1
  • 6
  • 7
28
votes
1 answer

AWS Athena SQL Query Error with timestamp

I have a table in AWS Athena having column named 'servertime' with the data type of timestamp. I run a query like this select * from table_name where servertime between '2018-04-01 00:00:00' and '2018-04-05 23:59:59'; It gives me this error: Your…
Sumit Kumar Sagar
  • 381
  • 1
  • 3
  • 6
27
votes
2 answers

Amazon Athena - Converting Timestamp to Date?

Looking at the Date/Time Athena documentation, I don't see a function to do this, which surprises me. The closest I see is date_trunc('week', timestamp) but that results in something like 2017-07-09 00:00:00.000 while I would like the format to be…
Louis
  • 1,123
  • 5
  • 15
  • 24
27
votes
3 answers

Generate interval from variable in Presto

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://stackoverflow.com/a/2157776/2388930, but am…
Dimpl
  • 935
  • 1
  • 10
  • 24
26
votes
1 answer

random sample of size N in Athena

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 forever to run, presumably because the ORDER BY…
RoyalTS
  • 9,545
  • 12
  • 60
  • 101
24
votes
4 answers

How to convert string into timestamp in Presto (Athena)?

I want to convert datatype of string (eg : '2018-03-27T00:20:00.855556Z' ) into timestamp (eg : '2018-03-27 00:20:00'). Actually I execute the query in Athena : select * from tb_name where elb_status_code like '5%%' AND date between…
Harshit Agrawal
  • 259
  • 1
  • 2
  • 4
23
votes
1 answer

Athena: Query exhausted resources at scale factor

I am running a query like: SELECT f.*, p.countryName, p.airportName, a.name AS agentName FROM ( SELECT f.outboundlegid, f.inboundlegid, f.querydatetime, cast(f.agent as bigint) as agent, …
Jiew Meng
  • 84,767
  • 185
  • 495
  • 805
23
votes
2 answers

LATERAL VIEW EXPLODE in presto

New to presto, any pointer how can I use LATERAL VIEW EXPLODE in presto for below table. I need to filter on names in my presto query CREATE EXTERNAL TABLE `id`( `id` string, `names` map>, `tags`…
rkj
  • 671
  • 3
  • 14
  • 25
1
2 3
…
99 100