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

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

Quality

Security

License

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

security Security

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

build Reuse

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

trino Examples and Code Snippets

See all related Code Snippets

Building Trino

Copy

Download

./mvnw clean install -DskipTests

Running the CLI

Copy

Download

client/trino-cli/target/trino-cli-*-executable.jar

Don't want to double count in Filtered Aggregation

Copy

Download

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

Download

$ sudo rpm -i --nodeps trino-server-rpm-368.rpm

Presto SQL - Transforming array(BingTile) into geometry

Copy

Download

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

Download


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

Download

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

Download

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

Download

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

Download

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

Download

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

Download

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);
        }
    }
}

See all related Code Snippets

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

QUESTION

Presto SQL - Transforming array(BingTile) into geometry

Asked 2022-Jan-12 at 10:50

I'm trying to calculate the City area size by using Geospatial Functions like the bing_tiles_around(), geometry_union(), and st_area() with the below sample data.

City ID | Store ID | latitude | longitude | radius (km)
---------------------------------------------------------
12345   | store_01 | 36.1234  | 31.1234   | 3.11
12345   | store_02 | 36.5678  | 31.5678   | 2.52

I believe I should use the above functions to calculate the area size. As long as I understand based on this reference, the bing_tiles_around() returns an array(BingTile) and I need to transform it into geometry types to use both the geometry_union() and st_area() but I'm not sure how to transform 'array(BingTile)' into 'geometry' type.

with store_info as (
select city_id, store_id, latitude, longitude, radius,
bing_tiles_around(latitude, longitude, 10, radius) as bingTiles_around
from store_detail
)

select city_id, store_id, geometry_union(bingTiles_around)
from store_info

The above query is the current one that I'm working on and it showed me the below error when I executed it. (I know it's not correct usage but just want to share the below error message to resolve this problem)

QueryValidation: user_error: Attempt 1: presto: query failed (200 OK): 
"USER_ERROR: com.facebook.presto.sql.analyzer.SemanticException: line 17:70: 
Unexpected parameters (array(BingTile)) for function geometry_union. 
Expected: geometry_union(array(Geometry)) "

The ultimate goal is to create a sample result dataset as below.

City ID | Area_size 
--------------------
12345   | 40.12345
12346   | 50.56789

Can anyone please help me to create this table using the above information? Any help would be appreciated.

Thanks!

ANSWER

Answered 2022-Jan-12 at 10:50

bing_tiles_around returns array of BingTile while geometry_union expects array of Geometry so you need to transform one to another:

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

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

QUESTION

Superset with Trino Impersonation and LDAP

Asked 2021-Dec-01 at 07:26

I have a Trino cluster configured to use LDAP and I want to use Superset to connect to it.

The Trino cluster uses HTTPS with a self signed certificate

I managed to configure Superset to use LDAP, that's not the problem. I also managed to query Trino by having the following configuration:

sqlalchemy URI: trino://myuser:mypassword@trino_server:8443, security extra config: {"connect_args": {"verify": false}}

Now here's the problem: Under the security tab there's a checkbox that says "Impersonate logged in user (Presto, Trino, Hive and GSheets)" . I checked the box, and still the queries I execute run with the user "myuser" which is configured in the sqlalchemy URI, instead of the logged in user.

I'm using Superset version 1.3.2

Does anybody know how to solve this?

ANSWER

Answered 2021-Nov-08 at 19:34

There are two components to get user impersonation working with Trino and Superset:

A version of Superset that supports user impersonation with Trino.

This was added officially in 1.3.0, and since you're on 1.3.2 that shouldn't be a problem.

A Trino client that supports user impersonation.

AFAIK the only Python client that currently works with Superset to connect to Trino is sqlalchemy-trino. I couldn't find any specific changes made for user impersonation until 0.4.0, but I have gotten this working with the older 0.3.0 version.

There may be some other possibilities that could prevent user impersonation from working, but less likely:

Make sure that all containers have a working version of sqlalchemy-trino installed. This depends on how you add Python requirements, but I believe I've seen cases where Superset containers don't have the same dependencies, i.e. the superset_app container has the correct module, but not the superset_worker container.
Make sure that the HTTP headers in the requests going to Trino are not being modified. User impersonation works by authenticating with basic authentication but impersonating the user added in a HTTP header called 'X-Trino-User'. If the HTTP header is removed or changed, then the user impersonation won't work as expected.

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

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 .