kandi background
Explore Kits

trino | Official repository of Trino, the distributed SQL query engine

 by   trinodb Java Version: Current License: Apache-2.0

 by   trinodb Java Version: Current License: Apache-2.0

Download this library from

kandi X-RAY | trino Summary

trino is a Java library typically used in Big Data, Spark, Hadoop applications. trino has no bugs, it has no vulnerabilities, it has build file available, it has a Permissive License and it has medium support. You can download it from GitHub, Maven.
After building Trino for the first time, you can load the project into your IDE and run the server. We recommend using IntelliJ IDEA. Because Trino is a standard Maven project, you easily can import it into your IDE. In IntelliJ, choose Open Project from the Quick Start box or choose Open from the File menu and select the root pom.xml file.
Support
Support
Quality
Quality
Security
Security
License
License
Reuse
Reuse

kandi-support Support

  • trino has a medium active ecosystem.
  • It has 5271 star(s) with 1702 fork(s). There are 146 watchers for this library.
  • It had no major release in the last 12 months.
  • There are 1580 open issues and 2046 have been closed. On average issues are closed in 95 days. There are 495 open pull requests and 0 closed requests.
  • It has a neutral sentiment in the developer community.
  • The latest version of trino is current.
trino Support
Best in #Java
Average in #Java
trino Support
Best in #Java
Average in #Java

quality kandi Quality

  • trino has 0 bugs and 0 code smells.
trino Quality
Best in #Java
Average in #Java
trino Quality
Best in #Java
Average in #Java

securitySecurity

  • trino has no vulnerabilities reported, and its dependent libraries have no vulnerabilities reported.
  • trino code analysis shows 0 unresolved vulnerabilities.
  • There are 0 security hotspots that need review.
trino Security
Best in #Java
Average in #Java
trino Security
Best in #Java
Average in #Java

license License

  • trino is licensed under the Apache-2.0 License. This license is Permissive.
  • Permissive licenses have the least restrictions, and you can use them in most projects.
trino License
Best in #Java
Average in #Java
trino License
Best in #Java
Average in #Java

buildReuse

  • trino releases are not available. You will need to build from source code and install.
  • Deployable package is available in Maven.
  • Build file is available. You can build the component from source.
  • Installation instructions, examples and code snippets are available.
  • trino saves you 2214113 person hours of effort in developing the same functionality from scratch.
  • It has 1092742 lines of code, 78511 functions and 8409 files.
  • It has medium code complexity. Code complexity directly impacts maintainability of the code.
trino Reuse
Best in #Java
Average in #Java
trino Reuse
Best in #Java
Average in #Java
Top functions reviewed by kandi - BETA

kandi has reviewed trino and discovered the below as its top functions. This is intended to give you an instant insight into trino implemented functionality, and help decide if they suit your requirements.

  • Creates a partition writer .
  • Load partition .
  • Creates an orc page source .
  • Adapts a parameter to a method handle .
  • Convert an object to an expression .
  • Sets up the webi module .
  • Get the pipeline stats .
  • Gets the stats for the task .
  • Creates plan node stats from the given task stats .
  • Creates a join plan for the given criteria .

trino Key Features

Open the File menu and select Project Structure

In the SDKs section, ensure that JDK 11 is selected (create one if none exist)

In the Project section, ensure the Project language level is set to 11

Building Trino

copy iconCopydownload iconDownload
./mvnw clean install -DskipTests

Running the CLI

copy iconCopydownload iconDownload
client/trino-cli/target/trino-cli-*-executable.jar

Don't want to double count in Filtered Aggregation

copy iconCopydownload iconDownload
-- sample data
WITH dataset (last_purchase_timestamp) AS (
    VALUES (timestamp '2022-03-02 1:20:00'),
        (timestamp '2022-03-01 1:30:00'),
        (timestamp '2022-02-28 1:24:03'),
        (timestamp '2022-02-02 21:22:26')
)

-- query
select count_if(last_purchase_timestamp >= DATE '2022-03-05' - INTERVAL '30' day) total_active_p30,
    count_if(last_purchase_timestamp >= DATE '2022-03-05' - INTERVAL '60' day and last_purchase_timestamp < DATE '2022-03-05' - INTERVAL '30' day) total_active_p60,
    count_if(last_purchase_timestamp >= DATE '2022-03-05' - INTERVAL '90' day and last_purchase_timestamp < DATE '2022-03-05' - INTERVAL '60' day) total_active_p90 
from dataset
-----------------------
count(*) filter (where last_purchase_timestamp >= ('2022-03-05'::date - INTERVAL '30' day)) 
as total_active_p30,

count(*) filter (where last_purchase_timestamp >= ('2022-03-05'::date - INTERVAL '60' day)
                            and last_purchase_timestamp < ('2022-03-05'::date - INTERVAL '30' day)) 
as total_active_p60,

count(*) filter (where last_purchase_timestamp >= ('2022-03-05'::date - INTERVAL '90' day)
                        and last_purchase_timestamp < ('2022-03-05'::date - INTERVAL '60' day)) 
as total_active_p90

RPM installation Trino throws python dependency

copy iconCopydownload iconDownload
$ sudo rpm -i --nodeps trino-server-rpm-368.rpm

Presto SQL - Transforming array(BingTile) into geometry

copy iconCopydownload iconDownload
-- sample data
WITH dataset (City_ID , Store_ID , latitude , longitude , radius ) AS (
    VALUES (12345   , 'store_01' , 36.1234  , 31.1234   , 3.11),
(12345   , 'store_02' , 36.5678  , 31.5678   , 2.52)
) 

--query
select city_id,
    store_id,
    geometry_union(
        transform(bingTiles_around, t->bing_tile_polygon(t))
    )
from(
        select city_id,
            store_id,
            latitude,
            longitude,
            radius,
            bing_tiles_around(latitude, longitude, 10, radius) as bingTiles_around
        from dataset
    )

Does Trino (formerly Presto) INSERT work with CTEs?

copy iconCopydownload iconDownload

INSERT INTO my_destination_table

with my_CTE as 
(SELECT a,b,c
FROM my_source_table
WHERE <some conditions to apply>)

SELECT a, b, c
FROM my_CTE;

Trino implement a function like regexp_split_to_table()?

copy iconCopydownload iconDownload
select s.str as original_str, u.str as exploded_value
from
(select 'one,two,,,three' as str)s
cross join unnest(regexp_split(s.str,',+')) as u(str)
original_str     exploded_value 
one,two,,,three  one
one,two,,,three  two
one,two,,,three  three
-----------------------
select s.str as original_str, u.str as exploded_value
from
(select 'one,two,,,three' as str)s
cross join unnest(regexp_split(s.str,',+')) as u(str)
original_str     exploded_value 
one,two,,,three  one
one,two,,,three  two
one,two,,,three  three

window function for moving average

copy iconCopydownload iconDownload
df['date'] = pd.to_datetime(df['date'])    

df['my_average'] = (df.groupby('customer_id')
                      .apply(lambda d: d.rolling('30D', on='date')['price'].mean())
                      .reset_index(level=0, drop=True)
                      .astype(int)
                   )
  customer_id       date  price  my_average
0      cust_1 2020-10-10    100         100
2      cust_1 2020-10-15    200         150
3      cust_1 2020-10-16    240         180
5      cust_1 2020-12-25    140         140
1      cust_2 2020-10-10     15          15
4      cust_2 2020-12-20     25          25
6      cust_2 2021-01-01      5          15
-----------------------
df['date'] = pd.to_datetime(df['date'])    

df['my_average'] = (df.groupby('customer_id')
                      .apply(lambda d: d.rolling('30D', on='date')['price'].mean())
                      .reset_index(level=0, drop=True)
                      .astype(int)
                   )
  customer_id       date  price  my_average
0      cust_1 2020-10-10    100         100
2      cust_1 2020-10-15    200         150
3      cust_1 2020-10-16    240         180
5      cust_1 2020-12-25    140         140
1      cust_2 2020-10-10     15          15
4      cust_2 2020-12-20     25          25
6      cust_2 2021-01-01      5          15

Combine Consecutive Rows for given index values in Pandas DataFrame

copy iconCopydownload iconDownload
df['Serial No.'] = df['Serial No.'].bfill().ffill()
df['Total'] = df['Total'].astype(str).replace('nan', np.nan)

df_out = df.groupby('Serial No.', as_index=False).agg(lambda x: ''.join(x.dropna()))
df_out['Total'] = df_out['Total'].replace('', np.nan, regex=True).astype(float)
print(df_out)

   Serial No.                      Name        Type  Total
0         1.0                    Easter    Multiple   19.0
1         2.0                   Costeri  Roundabout   16.0
2         3.0                     Zhiop         Tee   16.0
3         4.0                     Nesss       Cross   10.0
4         5.0              Uoar Lhahara         Tee   10.0
5         6.0          Trino Nishra(KX)         Tee    9.0
6         7.0                Old-FX Box       Cross    8.0
7         8.0                 Gardeners  Roundabout    8.0
8         9.0                Max Detter  Roundabout    7.0
9        10.0  Others (Asynco,D+ E,etc)       Cross    7.0
-----------------------
df['Serial No.'] = df['Serial No.'].bfill().ffill()
df['Total'] = df['Total'].astype(str).replace('nan', np.nan)

df_out = df.groupby('Serial No.', as_index=False).agg(lambda x: ''.join(x.dropna()))
df_out['Total'] = df_out['Total'].replace('', np.nan, regex=True).astype(float)
print(df_out)

   Serial No.                      Name        Type  Total
0         1.0                    Easter    Multiple   19.0
1         2.0                   Costeri  Roundabout   16.0
2         3.0                     Zhiop         Tee   16.0
3         4.0                     Nesss       Cross   10.0
4         5.0              Uoar Lhahara         Tee   10.0
5         6.0          Trino Nishra(KX)         Tee    9.0
6         7.0                Old-FX Box       Cross    8.0
7         8.0                 Gardeners  Roundabout    8.0
8         9.0                Max Detter  Roundabout    7.0
9        10.0  Others (Asynco,D+ E,etc)       Cross    7.0

AWS Athena (Trino SQL) Convert birthdate string (mm/dd/yy) to date -- need twentieth century

copy iconCopydownload iconDownload
select case when 
         parse_datetime(birthdate, 'MM/dd/yy') > current_timestamp then
                    parse_datetime(birthdate, 'MM/dd/yy') - interval '100' year 
         else parse_datetime(birthdate, 'MM/dd/yy') 
       end as birthdate

Export non-varchar data to CSV table using Trino (formerly PrestoDB)

copy iconCopydownload iconDownload
CREATE TABLE region_csv
WITH (format='CSV')
AS SELECT CAST(regionkey AS varchar), CAST(name AS varchar), CAST(comment AS varchar)
FROM region_orc
-----------------------
CREATE TABLE hive.test.region (
  regionkey bigint,
  name varchar(25),
  comment varchar(152)
)
WITH (
  format = 'TEXTFILE',
  textfile_field_separator = ','
);
INSERT INTO hive.test.region VALUES (
  1,
  'A "quote", with comma',
  'The comment contains a newline
in it');
1,"A ""quote"", with comma","The comment contains a newline
in it"
1,A "quote", with comma,The comment contains a newline
in it
-----------------------
CREATE TABLE hive.test.region (
  regionkey bigint,
  name varchar(25),
  comment varchar(152)
)
WITH (
  format = 'TEXTFILE',
  textfile_field_separator = ','
);
INSERT INTO hive.test.region VALUES (
  1,
  'A "quote", with comma',
  'The comment contains a newline
in it');
1,"A ""quote"", with comma","The comment contains a newline
in it"
1,A "quote", with comma,The comment contains a newline
in it
-----------------------
CREATE TABLE hive.test.region (
  regionkey bigint,
  name varchar(25),
  comment varchar(152)
)
WITH (
  format = 'TEXTFILE',
  textfile_field_separator = ','
);
INSERT INTO hive.test.region VALUES (
  1,
  'A "quote", with comma',
  'The comment contains a newline
in it');
1,"A ""quote"", with comma","The comment contains a newline
in it"
1,A "quote", with comma,The comment contains a newline
in it
-----------------------
CREATE TABLE hive.test.region (
  regionkey bigint,
  name varchar(25),
  comment varchar(152)
)
WITH (
  format = 'TEXTFILE',
  textfile_field_separator = ','
);
INSERT INTO hive.test.region VALUES (
  1,
  'A "quote", with comma',
  'The comment contains a newline
in it');
1,"A ""quote"", with comma","The comment contains a newline
in it"
1,A "quote", with comma,The comment contains a newline
in it

MongoTimeoutException: Error While Using MongoDB with Trino

copy iconCopydownload iconDownload
import com.mongodb.MongoClient;
import com.mongodb.MongoClientOptions;
import com.mongodb.MongoCredential;
import com.mongodb.ServerAddress;
import java.util.Collections;
import org.bson.Document;
import java.util.List;

public class MongoSession
{
    public static void main(String[] args)
    {
        ServerAddress seed = new ServerAddress("127.0.0.1:27017");
        MongoCredential credential = MongoCredential.createCredential("user", "database", "password".toCharArray());
        MongoClient client = new MongoClient(seed, Collections.singletonList(credential), MongoClientOptions.builder().build());
        client.getDatabase("database").runCommand(new Document("ping", 1));
        for (String name : client.getDatabase("database").listCollectionNames()) {
            System.out.println(name);
        }
    }
}

Community Discussions

Trending Discussions on trino
  • Don't want to double count in Filtered Aggregation
  • RPM installation Trino throws python dependency
  • Presto SQL - Transforming array(BingTile) into geometry
  • Does Trino (formerly Presto) INSERT work with CTEs?
  • Superset with Trino Impersonation and LDAP
  • What causes table corruption error when reading hive bucket table in trino?
  • Validate list elements if they take X time to run in a loop
  • Unable to access WebUI when Trino is secured behind a load balancer and LDAP authentication is enabled
  • Spark Trino Connection
  • Athena (Trino SQL) parsing JSON document using fields (dot notation)
Trending Discussions on trino

QUESTION

Don't want to double count in Filtered Aggregation

Asked 2022-Mar-29 at 19:38

Sample Data:

shopper_id last_purchase_timestamp active_p30 active_p60 active_over_p90
1 2022-03-02 1:20:00 TRUE TRUE TRUE
2 2022-03-01 1:30:00 TRUE TRUE TRUE
3 2022-02-28 1:24:03 TRUE TRUE TRUE
4 2022-02-02 21:22:26 FALSE TRUE TRUE

I want to count if the shopper was active (as in made their last purchase) in the last 30 days (starting march 5th), last 60 days, etc.

My goal is to find how many shoppers bought their last item in the last 30 days, how many shoppers bought their last item in the last 60 days etc. However I do not want to double count.

What I've attempted:

AS total_active_p30,

count(*) FILTER (where last_purchase_timestamp >= DATE '2022-03-05' - INTERVAL '60' day) 
AS total_active_p60,

count(*) FILTER (where last_purchase_timestamp >= DATE '2022-03-05' - INTERVAL '90' day) AS 
total_active_p90 

Results:

total_active_p30 total_active_p60 total_active_p90
3 4 4

However this is causing it to double count. How can I prevent it from double counting? The total number of counts should be 4.

My ideal output would be:

total_active_p30 total_active_p60 total_active_p90
3 1 0

Thanks in advance everyone! I'm using Trino!

ANSWER

Answered 2022-Mar-29 at 19:02

Add both upper and lower bounds to the filter so they do not intersect. Something along this lines:

-- sample data
WITH dataset (last_purchase_timestamp) AS (
    VALUES (timestamp '2022-03-02 1:20:00'),
        (timestamp '2022-03-01 1:30:00'),
        (timestamp '2022-02-28 1:24:03'),
        (timestamp '2022-02-02 21:22:26')
)

-- query
select count_if(last_purchase_timestamp >= DATE '2022-03-05' - INTERVAL '30' day) total_active_p30,
    count_if(last_purchase_timestamp >= DATE '2022-03-05' - INTERVAL '60' day and last_purchase_timestamp < DATE '2022-03-05' - INTERVAL '30' day) total_active_p60,
    count_if(last_purchase_timestamp >= DATE '2022-03-05' - INTERVAL '90' day and last_purchase_timestamp < DATE '2022-03-05' - INTERVAL '60' day) total_active_p90 
from dataset

Output:

total_active_p30 total_active_p60 total_active_p90
3 1 0

Source https://stackoverflow.com/questions/71667157

Community Discussions, Code Snippets contain sources that include Stack Exchange Network

Vulnerabilities

No vulnerabilities reported

Install trino

Mac OS X or Linux
Java 11.0.11+, 64-bit
Docker

Support

For any new features, suggestions and bugs create an issue on GitHub. If you have any questions check and ask questions on community page Stack Overflow .

DOWNLOAD this Library from

Find, review, and download reusable Libraries, Code Snippets, Cloud APIs from
over 430 million Knowledge Items
Find more libraries
Reuse Solution Kits and Libraries Curated by Popular Use Cases

Save this library and start creating your kit

Explore Related Topics

Share this Page

share link
Find, review, and download reusable Libraries, Code Snippets, Cloud APIs from
over 430 million Knowledge Items
Find more libraries
Reuse Solution Kits and Libraries Curated by Popular Use Cases

Save this library and start creating your kit

  • © 2022 Open Weaver Inc.