presto | official home of the Presto distributed SQL query engine

by prestodb Java Version: Current License: Apache-2.0

Download this library from

kandi X-RAY | presto Summary

presto is a Java library typically used in Big Data, Spark, JavaFX, Hadoop applications. presto has no bugs, it has build file available, it has a Permissive License and it has high support. However presto has 1 vulnerabilities. You can download it from GitHub, Maven.

After building Presto for the first time, you can load the project into your IDE and run the server. We recommend using IntelliJ IDEA. Because Presto is a standard Maven project, you can import it into your IDE using the root pom.xml file. 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

presto has a highly active ecosystem.
It has 13394 star(s) with 4584 fork(s). There are 870 watchers for this library.
It had no major release in the last 12 months.
There are 794 open issues and 4146 have been closed. On average issues are closed in 943 days. There are 234 open pull requests and 0 closed requests.
It has a positive sentiment in the developer community.
The latest version of presto is current.

presto Support

Best in #Java

Average in #Java

presto Support

Best in #Java

Average in #Java

quality kandi Quality

presto has 0 bugs and 0 code smells.

presto Quality

Best in #Java

Average in #Java

presto Quality

Best in #Java

Average in #Java

security Security

presto has 1 vulnerability issues reported (0 critical, 1 high, 0 medium, 0 low).
presto code analysis shows 0 unresolved vulnerabilities.
There are 0 security hotspots that need review.

presto Security

Best in #Java

Average in #Java

presto Security

Best in #Java

Average in #Java

license License

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

presto License

Best in #Java

Average in #Java

presto License

Best in #Java

Average in #Java

build Reuse

presto 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 are not available. Examples and code snippets are available.
presto saves you 2077108 person hours of effort in developing the same functionality from scratch.
It has 964605 lines of code, 71957 functions and 7790 files.
It has medium code complexity. Code complexity directly impacts maintainability of the code.

presto Reuse

Best in #Java

Average in #Java

presto Reuse

Best in #Java

Average in #Java

Top functions reviewed by kandi - BETA

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

Builds the sql functions .
Create a new task executor
Coerces the given type to the given result type .
Pre - build aggregation .
Processes a join .
Create a batch ORC page source .
Create an orc file source .
Print summary information about the query .
Casts a primitive type to a primitive block .
Creates a Hive page source .

presto Key Features

Open the File menu and select Project Structure

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

In the Project section, ensure the Project language level is set to 8.0 as Presto makes use of several Java 8 language features

Main Class: com.facebook.presto.server.PrestoServer

VM Options: -ea -XX:+UseG1GC -XX:G1HeapRegionSize=32M -XX:+UseGCOverheadLimit -XX:+ExplicitGCInvokesConcurrent -Xmx2G -Dconfig=etc/config.properties -Dlog.levels-file=etc/log.properties

Working directory: $MODULE_WORKING_DIR$ or $MODULE_DIR$(Depends your version of IntelliJ)

Use classpath of module: presto-main

presto Examples and Code Snippets

See all related Code Snippets

Building Presto

Copy

Download

./mvnw clean install

Overview

Copy

Download

-Dhive.metastore.uri=thrift://localhost:9083

Using SOCKS for Hive or HDFS

Copy

Download

ssh -v -N -D 1080 server

Running the CLI

Copy

Download

presto-cli/target/presto-cli-*-executable.jar

Building the Web UI

Copy

Download

yarn --cwd presto-main/src/main/resources/webapp/src install

SQL - Is it possible to print all records or only one record taking into account count(*) in a table?

Copy

Download

 WITH cte AS (
    SELECT ColA, ColB, ColC, 0 AS pos FROM Example
    UNION ALL
    SELECT 'Result', 'NA, 'NA', 1
)

SELECT ColA, ColB, ColC
FROM cte
WHERE pos = 0 OR NOT EXISTS (SELECT 1 FROM cte WHERE pos = 0);

-----------------------

SELECT ColA,ColB,ColC
FROM Example
union
SELECT 
   'Result' as ColA,
   'NA' as ColB,
   'NA' as ColC
WHERE NOT EXISTS (SELECT ColA,ColB,ColC
                  FROM Example)

How to group into batches after assigning rank

Copy

Download

with sample_data as(        
select 'video_a' as video_id, 'frame_1' as frame_id , 'walk' as verb union all 
select 'video_a', 'frame_2' , 'run'   union all
select 'video_a', 'frame_3' , 'sit'   union all
select 'video_a', 'frame_4' , 'walk'  union all
select 'video_a', 'frame_5' , 'walk'  union all
select 'video_a', 'frame_6' , 'walk'  union all
select 'video_b', 'frame_7' , 'stand' union all
select 'video_b', 'frame_8' , 'stand' union all
select 'video_b', 'frame_9' , 'run'   union all
select 'video_b', 'frame_10', 'run'   union all
select 'video_b', 'frame_11', 'sit'   union all
select 'video_b', 'frame_12', 'run'   union all
select 'video_b', 'frame_13', 'run'
)

select s.*, 
       dense_rank() over(order by video_id, rnk_frame / 6) batch_of_five
from
(
select video_id, frame_id, verb, 
       CAST(regexp_extract(frame_id,'_(\d*)$',1) AS INT) frame_number,
       dense_rank() over(partition by video_id order by CAST(regexp_extract(frame_id,'_(\d*)$',1) AS INT)) rnk_frame
  from sample_data
)s
order by video_id, frame_number;

video_id    frame_id    verb    frame_number    rnk_frame   batch_of_five
video_a     frame_1     walk    1                1           1
video_a     frame_2     run     2                2           1
video_a     frame_3     sit     3                3           1
video_a     frame_4     walk    4                4           1
video_a     frame_5     walk    5                5           1
video_a     frame_6     walk    6                6           2
video_b     frame_7     stand   7                1           3
video_b     frame_8     stand   8                2           3
video_b     frame_9     run     9                3           3
video_b     frame_10    run     10               4           3
video_b     frame_11    sit     11               5           3
video_b     frame_12    run     12               6           4
video_b     frame_13    run     13               7           4

-----------------------

with sample_data as(        
select 'video_a' as video_id, 'frame_1' as frame_id , 'walk' as verb union all 
select 'video_a', 'frame_2' , 'run'   union all
select 'video_a', 'frame_3' , 'sit'   union all
select 'video_a', 'frame_4' , 'walk'  union all
select 'video_a', 'frame_5' , 'walk'  union all
select 'video_a', 'frame_6' , 'walk'  union all
select 'video_b', 'frame_7' , 'stand' union all
select 'video_b', 'frame_8' , 'stand' union all
select 'video_b', 'frame_9' , 'run'   union all
select 'video_b', 'frame_10', 'run'   union all
select 'video_b', 'frame_11', 'sit'   union all
select 'video_b', 'frame_12', 'run'   union all
select 'video_b', 'frame_13', 'run'
)

select s.*, 
       dense_rank() over(order by video_id, rnk_frame / 6) batch_of_five
from
(
select video_id, frame_id, verb, 
       CAST(regexp_extract(frame_id,'_(\d*)$',1) AS INT) frame_number,
       dense_rank() over(partition by video_id order by CAST(regexp_extract(frame_id,'_(\d*)$',1) AS INT)) rnk_frame
  from sample_data
)s
order by video_id, frame_number;

video_id    frame_id    verb    frame_number    rnk_frame   batch_of_five
video_a     frame_1     walk    1                1           1
video_a     frame_2     run     2                2           1
video_a     frame_3     sit     3                3           1
video_a     frame_4     walk    4                4           1
video_a     frame_5     walk    5                5           1
video_a     frame_6     walk    6                6           2
video_b     frame_7     stand   7                1           3
video_b     frame_8     stand   8                2           3
video_b     frame_9     run     9                3           3
video_b     frame_10    run     10               4           3
video_b     frame_11    sit     11               5           3
video_b     frame_12    run     12               6           4
video_b     frame_13    run     13               7           4

Presto equivalent to StringTokenizerDelim (Vertica) / Explode(Python)

Copy

Download

-- sample data
WITH dataset (id, product,   market ) AS (
    values (1, 'Cabinet', 'AU,GB,FR'),
    (2, 'Chair', 'US,GB'),
    (3, 'Desk', 'BE,GB,FR,US')
) 

--query
SELECT id, product, m as market
FROM dataset
CROSS JOIN UNNEST (split(market, ',')) as t(m)

Calculating averages by quarters

Copy

Download

-- sample data
WITH dataset (date, value) AS (
    VALUES (date '2021-01-01' ,     10),
(date '2021-01-30' ,     20), 
(date '2021-02-10' ,     10),
(date '2021-04-01' ,     20),
(date '2021-04-02' ,     10),
(date '2021-07-10',      20)
) 

--query
SELECT avg(value)
FROM dataset
WHERE  month(date) / 3 = 1
GROUP BY month(date) / 3

-----------------------

with mytable as (
SELECT * FROM (
    VALUES
        (date '2021-01-01', 10),
        (date '2021-01-30', 20), 
        (date '2021-02-10', 10),
        (date '2021-04-01', 20),
        (date '2021-04-02', 10),
        (date '2021-07-10', 20)
) AS t (date, value)
)

select quarter(date) as qt, avg(value) as avg
  from mytable
  where quarter(date)=2
 group by quarter(date)

qt avg
2   15.0

-----------------------

with mytable as (
SELECT * FROM (
    VALUES
        (date '2021-01-01', 10),
        (date '2021-01-30', 20), 
        (date '2021-02-10', 10),
        (date '2021-04-01', 20),
        (date '2021-04-02', 10),
        (date '2021-07-10', 20)
) AS t (date, value)
)

select quarter(date) as qt, avg(value) as avg
  from mytable
  where quarter(date)=2
 group by quarter(date)

qt avg
2   15.0

AWS Athena (Presto) - multiple WITH statements

Copy

Download

WITH "revenue" AS (,
     SELECT "cik", "accession", year, quarter.
            "form type" as "form_type",
            CAST("value" AS bigint) as "revenue",
            CAST("value" - lag("value") over (partition by "cik") AS bigint) as "increment",
            ROUND("value" / lag("value") over (partition by "cik"),2) as "ratio"
     FROM "gaap" 
     WHERE "form type" IN ('10-K') AND
    "rep" = 'revenue' AND
    ORDER BY "cik", "year", "quarter", "fs" desc
),
"cik_with_continuous_growth" AS (
 SELECT "cik"
 FROM "revenue"
 WHERE "ratio" >= 1.5 AND
       "year" >= 2016
 GROUP BY "cik"
 HAVING COUNT("ratio") >= 3
 ORDER BY "cik"
)
SELECT * FROM "cik_with_continuous_growth";

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
    )

htaccess - remove .php extension from url

Copy

Download

RewriteEngine ON
RewriteBase /

RewriteCond %{ENV:REDIRECT_STATUS} ^$
##using THE_REQUEST variable for condition check.
RewriteCond %{THE_REQUEST} \s/([^.]*)\.php/?\s [NC]
##Performing external redirect here.
RewriteRule ^  %1? [R=301,L]

##Performing rewrite for non-existing pages.
RewriteCond %{REQUEST_FILENAME} !-f
RewriteCond %{REQUEST_FILENAME} !-d
RewriteCond %{DOCUMENT_ROOT}/$1.php -f
RewriteRule ^(.*)/?$ /$1.php [QSA,L]

-----------------------

RewriteEngine on
RewriteCond %{REQUEST_FILENAME} !-d
RewriteCond %{REQUEST_FILENAME}\.php -f
RewriteRule ^(.*)$ $1.php [NC,L]

Create the responsive next / previous button for my project

Copy

Download

<button onclick="location.href='https://google.com';">Previous</button>
<button onclick="location.href='https://facebook.com';">Next</button>

function changePage(prevOrNext) {
  var pages = document.getElementsByTagName('main')[0].children;
  for (let pageIndex = 0; pageIndex < pages.length; pageIndex++) {
    if (pages[pageIndex].style.display == "block") {
      if (prevOrNext == 1 && pageIndex < pages.length - 1) {
        pages[pageIndex].style.display = "none"
        pages[pageIndex + 1].style.display = "block"
        document.body.scrollTop = document.documentElement.scrollTop = 0;
      } else if (prevOrNext == 0 && pageIndex > 0) {
        pages[pageIndex].style.display = "none"
        pages[pageIndex - 1].style.display = "block"
        document.body.scrollTop = document.documentElement.scrollTop = 0;
      }
    }
  }
}

body {
  font-size: 18px;
  background: url("https://www.toptal.com/designers/subtlepatterns/patterns/groovepaper.png");
  font-family: 'Gelasio', serif;
}

main {
  color: black;
  font-size: 1.1rem;
  display: flex;
  flex-direction: column;
  width: 100%;
  margin-left: auto;
  margin-right: auto;
}

main div {
  width: 100%;
  padding: 6rem 5rem;
  display: none
}

h2 {
  text-align: center;
  font-size: 1.2rem;
  font-weight: normal;
  margin-bottom: 6rem;
}

h1 {
  font-family: 'Ibarra Real Nova', serif;
  text-align: center;
  font-weight: 400;
  font-size: 3rem;
  text-transform: uppercase;
  margin-bottom: 6rem;
  letter-spacing: .1rem;
}

.right-page {
  margin-top: 0;
  padding-top: 0;
}

.right-page p {
  line-height: 1.4;
  text-align: justify;
  text-justify: inter-word;
}

.right-page p:first-letter {
  font-family: 'Ibarra Real Nova', serif;
  font-size: 4.5rem;
  float: left;
  margin-top: .5rem;
  margin-right: 1rem;
  line-height: .7;
}

.left-page {
  text-align: center;
  padding-top: 4rem;
}

.left-page small {
  font-style: italic;
}

.left-page img {
  max-width: 90%;
  display: block;
  margin-left: auto;
  margin-right: auto;
}

@media screen and (min-width: 900px) {
  main {
    flex-direction: row;
    with: 100%;
    max-width: 1800px;
  }
  main div {
    width: 50%;
  }
  .left-page {
    padding-top: 14rem;
  }
  .right-page {
    padding-top: 6rem;
    max-height: 100vh;
    height: 100vh;
  }
}

<!DOCTYPE html>
<html lang="en">

<head>
  <meta charset="UTF-8">
  <title> Old book Stories</title>
  <link href="https://fonts.googleapis.com/css?family=Gelasio:400,400i|Ibarra+Real+Nova&display=swap" rel="stylesheet">
  <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/normalize/5.0.0/normalize.min.css">
  <link rel="stylesheet" href="./style.css">

</head>

<body>
  <!-- partial:index.partial.html -->
  <main>
    <div class="left-page" style="display: block">
      <img src="https://www.oldbookillustrations.com/wp-content/uploads/2017/03/there-lonely.jpg" />
      <small>But go there, lonely,<br>
At eventide,<br>
And hearken, hearken<br>
To the lisping tide.<br>
</small>
    </div>
    <div class="right-page">
      <h2>[ 1 ]</h2>
      <h1>Depender</h1>
      <p>
        En cuanto a todas las cosas que existen en el mundo, unas dependen de nosotros, otras no dependen de nosotros. De nosotros dependen; nuestras opiniones, nuestros movimientos, nuestros deseos, nuestras inclinaciones, nuestras aversiones; en una palabra,
        todas nuestras acciones.<br> Así, ante toda fantasía perturbadora, está presto a decir: <i>“Tu no eres sino una
      imaginación, y en absoluto eres lo que parece”</i>, enseguida examínala con atención y ponla a prueba, para ello sírvete de las reglas que tienes, principalmente con esta primera que es, a saber : de si la cosa que te hace penar es del número de
        aquellas que dependen de nosotros o de aquellas que no están en nuestro poder. Di sin titubear: <i>“Esa en nada me atañe”.</i>
      </p>
    </div>
  </main>
  <!-- partial -->

</body>

<footer style="text-align: center">
  <button id="prev" onclick="changePage(0)">Previous</button>
  <button id="next" onclick="changePage(1)">Next</button>
</footer>

</html>

-----------------------

<button onclick="location.href='https://google.com';">Previous</button>
<button onclick="location.href='https://facebook.com';">Next</button>

function changePage(prevOrNext) {
  var pages = document.getElementsByTagName('main')[0].children;
  for (let pageIndex = 0; pageIndex < pages.length; pageIndex++) {
    if (pages[pageIndex].style.display == "block") {
      if (prevOrNext == 1 && pageIndex < pages.length - 1) {
        pages[pageIndex].style.display = "none"
        pages[pageIndex + 1].style.display = "block"
        document.body.scrollTop = document.documentElement.scrollTop = 0;
      } else if (prevOrNext == 0 && pageIndex > 0) {
        pages[pageIndex].style.display = "none"
        pages[pageIndex - 1].style.display = "block"
        document.body.scrollTop = document.documentElement.scrollTop = 0;
      }
    }
  }
}

body {
  font-size: 18px;
  background: url("https://www.toptal.com/designers/subtlepatterns/patterns/groovepaper.png");
  font-family: 'Gelasio', serif;
}

main {
  color: black;
  font-size: 1.1rem;
  display: flex;
  flex-direction: column;
  width: 100%;
  margin-left: auto;
  margin-right: auto;
}

main div {
  width: 100%;
  padding: 6rem 5rem;
  display: none
}

h2 {
  text-align: center;
  font-size: 1.2rem;
  font-weight: normal;
  margin-bottom: 6rem;
}

h1 {
  font-family: 'Ibarra Real Nova', serif;
  text-align: center;
  font-weight: 400;
  font-size: 3rem;
  text-transform: uppercase;
  margin-bottom: 6rem;
  letter-spacing: .1rem;
}

.right-page {
  margin-top: 0;
  padding-top: 0;
}

.right-page p {
  line-height: 1.4;
  text-align: justify;
  text-justify: inter-word;
}

.right-page p:first-letter {
  font-family: 'Ibarra Real Nova', serif;
  font-size: 4.5rem;
  float: left;
  margin-top: .5rem;
  margin-right: 1rem;
  line-height: .7;
}

.left-page {
  text-align: center;
  padding-top: 4rem;
}

.left-page small {
  font-style: italic;
}

.left-page img {
  max-width: 90%;
  display: block;
  margin-left: auto;
  margin-right: auto;
}

@media screen and (min-width: 900px) {
  main {
    flex-direction: row;
    with: 100%;
    max-width: 1800px;
  }
  main div {
    width: 50%;
  }
  .left-page {
    padding-top: 14rem;
  }
  .right-page {
    padding-top: 6rem;
    max-height: 100vh;
    height: 100vh;
  }
}

<!DOCTYPE html>
<html lang="en">

<head>
  <meta charset="UTF-8">
  <title> Old book Stories</title>
  <link href="https://fonts.googleapis.com/css?family=Gelasio:400,400i|Ibarra+Real+Nova&display=swap" rel="stylesheet">
  <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/normalize/5.0.0/normalize.min.css">
  <link rel="stylesheet" href="./style.css">

</head>

<body>
  <!-- partial:index.partial.html -->
  <main>
    <div class="left-page" style="display: block">
      <img src="https://www.oldbookillustrations.com/wp-content/uploads/2017/03/there-lonely.jpg" />
      <small>But go there, lonely,<br>
At eventide,<br>
And hearken, hearken<br>
To the lisping tide.<br>
</small>
    </div>
    <div class="right-page">
      <h2>[ 1 ]</h2>
      <h1>Depender</h1>
      <p>
        En cuanto a todas las cosas que existen en el mundo, unas dependen de nosotros, otras no dependen de nosotros. De nosotros dependen; nuestras opiniones, nuestros movimientos, nuestros deseos, nuestras inclinaciones, nuestras aversiones; en una palabra,
        todas nuestras acciones.<br> Así, ante toda fantasía perturbadora, está presto a decir: <i>“Tu no eres sino una
      imaginación, y en absoluto eres lo que parece”</i>, enseguida examínala con atención y ponla a prueba, para ello sírvete de las reglas que tienes, principalmente con esta primera que es, a saber : de si la cosa que te hace penar es del número de
        aquellas que dependen de nosotros o de aquellas que no están en nuestro poder. Di sin titubear: <i>“Esa en nada me atañe”.</i>
      </p>
    </div>
  </main>
  <!-- partial -->

</body>

<footer style="text-align: center">
  <button id="prev" onclick="changePage(0)">Previous</button>
  <button id="next" onclick="changePage(1)">Next</button>
</footer>

</html>

-----------------------

<button onclick="location.href='https://google.com';">Previous</button>
<button onclick="location.href='https://facebook.com';">Next</button>

function changePage(prevOrNext) {
  var pages = document.getElementsByTagName('main')[0].children;
  for (let pageIndex = 0; pageIndex < pages.length; pageIndex++) {
    if (pages[pageIndex].style.display == "block") {
      if (prevOrNext == 1 && pageIndex < pages.length - 1) {
        pages[pageIndex].style.display = "none"
        pages[pageIndex + 1].style.display = "block"
        document.body.scrollTop = document.documentElement.scrollTop = 0;
      } else if (prevOrNext == 0 && pageIndex > 0) {
        pages[pageIndex].style.display = "none"
        pages[pageIndex - 1].style.display = "block"
        document.body.scrollTop = document.documentElement.scrollTop = 0;
      }
    }
  }
}

body {
  font-size: 18px;
  background: url("https://www.toptal.com/designers/subtlepatterns/patterns/groovepaper.png");
  font-family: 'Gelasio', serif;
}

main {
  color: black;
  font-size: 1.1rem;
  display: flex;
  flex-direction: column;
  width: 100%;
  margin-left: auto;
  margin-right: auto;
}

main div {
  width: 100%;
  padding: 6rem 5rem;
  display: none
}

h2 {
  text-align: center;
  font-size: 1.2rem;
  font-weight: normal;
  margin-bottom: 6rem;
}

h1 {
  font-family: 'Ibarra Real Nova', serif;
  text-align: center;
  font-weight: 400;
  font-size: 3rem;
  text-transform: uppercase;
  margin-bottom: 6rem;
  letter-spacing: .1rem;
}

.right-page {
  margin-top: 0;
  padding-top: 0;
}

.right-page p {
  line-height: 1.4;
  text-align: justify;
  text-justify: inter-word;
}

.right-page p:first-letter {
  font-family: 'Ibarra Real Nova', serif;
  font-size: 4.5rem;
  float: left;
  margin-top: .5rem;
  margin-right: 1rem;
  line-height: .7;
}

.left-page {
  text-align: center;
  padding-top: 4rem;
}

.left-page small {
  font-style: italic;
}

.left-page img {
  max-width: 90%;
  display: block;
  margin-left: auto;
  margin-right: auto;
}

@media screen and (min-width: 900px) {
  main {
    flex-direction: row;
    with: 100%;
    max-width: 1800px;
  }
  main div {
    width: 50%;
  }
  .left-page {
    padding-top: 14rem;
  }
  .right-page {
    padding-top: 6rem;
    max-height: 100vh;
    height: 100vh;
  }
}

<!DOCTYPE html>
<html lang="en">

<head>
  <meta charset="UTF-8">
  <title> Old book Stories</title>
  <link href="https://fonts.googleapis.com/css?family=Gelasio:400,400i|Ibarra+Real+Nova&display=swap" rel="stylesheet">
  <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/normalize/5.0.0/normalize.min.css">
  <link rel="stylesheet" href="./style.css">

</head>

<body>
  <!-- partial:index.partial.html -->
  <main>
    <div class="left-page" style="display: block">
      <img src="https://www.oldbookillustrations.com/wp-content/uploads/2017/03/there-lonely.jpg" />
      <small>But go there, lonely,<br>
At eventide,<br>
And hearken, hearken<br>
To the lisping tide.<br>
</small>
    </div>
    <div class="right-page">
      <h2>[ 1 ]</h2>
      <h1>Depender</h1>
      <p>
        En cuanto a todas las cosas que existen en el mundo, unas dependen de nosotros, otras no dependen de nosotros. De nosotros dependen; nuestras opiniones, nuestros movimientos, nuestros deseos, nuestras inclinaciones, nuestras aversiones; en una palabra,
        todas nuestras acciones.<br> Así, ante toda fantasía perturbadora, está presto a decir: <i>“Tu no eres sino una
      imaginación, y en absoluto eres lo que parece”</i>, enseguida examínala con atención y ponla a prueba, para ello sírvete de las reglas que tienes, principalmente con esta primera que es, a saber : de si la cosa que te hace penar es del número de
        aquellas que dependen de nosotros o de aquellas que no están en nuestro poder. Di sin titubear: <i>“Esa en nada me atañe”.</i>
      </p>
    </div>
  </main>
  <!-- partial -->

</body>

<footer style="text-align: center">
  <button id="prev" onclick="changePage(0)">Previous</button>
  <button id="next" onclick="changePage(1)">Next</button>
</footer>

</html>

-----------------------

<button onclick="location.href='https://google.com';">Previous</button>
<button onclick="location.href='https://facebook.com';">Next</button>

function changePage(prevOrNext) {
  var pages = document.getElementsByTagName('main')[0].children;
  for (let pageIndex = 0; pageIndex < pages.length; pageIndex++) {
    if (pages[pageIndex].style.display == "block") {
      if (prevOrNext == 1 && pageIndex < pages.length - 1) {
        pages[pageIndex].style.display = "none"
        pages[pageIndex + 1].style.display = "block"
        document.body.scrollTop = document.documentElement.scrollTop = 0;
      } else if (prevOrNext == 0 && pageIndex > 0) {
        pages[pageIndex].style.display = "none"
        pages[pageIndex - 1].style.display = "block"
        document.body.scrollTop = document.documentElement.scrollTop = 0;
      }
    }
  }
}

body {
  font-size: 18px;
  background: url("https://www.toptal.com/designers/subtlepatterns/patterns/groovepaper.png");
  font-family: 'Gelasio', serif;
}

main {
  color: black;
  font-size: 1.1rem;
  display: flex;
  flex-direction: column;
  width: 100%;
  margin-left: auto;
  margin-right: auto;
}

main div {
  width: 100%;
  padding: 6rem 5rem;
  display: none
}

h2 {
  text-align: center;
  font-size: 1.2rem;
  font-weight: normal;
  margin-bottom: 6rem;
}

h1 {
  font-family: 'Ibarra Real Nova', serif;
  text-align: center;
  font-weight: 400;
  font-size: 3rem;
  text-transform: uppercase;
  margin-bottom: 6rem;
  letter-spacing: .1rem;
}

.right-page {
  margin-top: 0;
  padding-top: 0;
}

.right-page p {
  line-height: 1.4;
  text-align: justify;
  text-justify: inter-word;
}

.right-page p:first-letter {
  font-family: 'Ibarra Real Nova', serif;
  font-size: 4.5rem;
  float: left;
  margin-top: .5rem;
  margin-right: 1rem;
  line-height: .7;
}

.left-page {
  text-align: center;
  padding-top: 4rem;
}

.left-page small {
  font-style: italic;
}

.left-page img {
  max-width: 90%;
  display: block;
  margin-left: auto;
  margin-right: auto;
}

@media screen and (min-width: 900px) {
  main {
    flex-direction: row;
    with: 100%;
    max-width: 1800px;
  }
  main div {
    width: 50%;
  }
  .left-page {
    padding-top: 14rem;
  }
  .right-page {
    padding-top: 6rem;
    max-height: 100vh;
    height: 100vh;
  }
}

<!DOCTYPE html>
<html lang="en">

<head>
  <meta charset="UTF-8">
  <title> Old book Stories</title>
  <link href="https://fonts.googleapis.com/css?family=Gelasio:400,400i|Ibarra+Real+Nova&display=swap" rel="stylesheet">
  <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/normalize/5.0.0/normalize.min.css">
  <link rel="stylesheet" href="./style.css">

</head>

<body>
  <!-- partial:index.partial.html -->
  <main>
    <div class="left-page" style="display: block">
      <img src="https://www.oldbookillustrations.com/wp-content/uploads/2017/03/there-lonely.jpg" />
      <small>But go there, lonely,<br>
At eventide,<br>
And hearken, hearken<br>
To the lisping tide.<br>
</small>
    </div>
    <div class="right-page">
      <h2>[ 1 ]</h2>
      <h1>Depender</h1>
      <p>
        En cuanto a todas las cosas que existen en el mundo, unas dependen de nosotros, otras no dependen de nosotros. De nosotros dependen; nuestras opiniones, nuestros movimientos, nuestros deseos, nuestras inclinaciones, nuestras aversiones; en una palabra,
        todas nuestras acciones.<br> Así, ante toda fantasía perturbadora, está presto a decir: <i>“Tu no eres sino una
      imaginación, y en absoluto eres lo que parece”</i>, enseguida examínala con atención y ponla a prueba, para ello sírvete de las reglas que tienes, principalmente con esta primera que es, a saber : de si la cosa que te hace penar es del número de
        aquellas que dependen de nosotros o de aquellas que no están en nuestro poder. Di sin titubear: <i>“Esa en nada me atañe”.</i>
      </p>
    </div>
  </main>
  <!-- partial -->

</body>

<footer style="text-align: center">
  <button id="prev" onclick="changePage(0)">Previous</button>
  <button id="next" onclick="changePage(1)">Next</button>
</footer>

</html>

Hive Table Partition Metadata Issue

Copy

Download

INSERT OVERWRITE TABLE table1 PARTITION(cmny_cd,order_dt)
select 
        column1,
        columnx
        ...
  from table1
  ---you can limit partitions in the WHERE

How to check if an array contains any elements of another array

Copy

Download

with mydata as (
select 1 id, array['chair','desk'] as myarray union all
select 2 id, array['screen','charger','computer']  union all
select 3 id, array['calendar', 'screen']
)

select id, 
       cardinality(array_intersect(myarray, array['chair','desk','charger']))>0 as contains_flag
  from mydata
 order by id

id  contains_flag
1   TRUE
2   TRUE
3   FALSE

-----------------------

with mydata as (
select 1 id, array['chair','desk'] as myarray union all
select 2 id, array['screen','charger','computer']  union all
select 3 id, array['calendar', 'screen']
)

select id, 
       cardinality(array_intersect(myarray, array['chair','desk','charger']))>0 as contains_flag
  from mydata
 order by id

id  contains_flag
1   TRUE
2   TRUE
3   FALSE

See all related Code Snippets

Community Discussions

Trending Discussions on presto

SQL - Is it possible to print all records or only one record taking into account count(*) in a table?
How to group into batches after assigning rank
Presto equivalent to StringTokenizerDelim (Vertica) / Explode(Python)
Calculating averages by quarters
AWS Athena (Presto) - multiple WITH statements
Presto SQL - Transforming array(BingTile) into geometry
htaccess - remove .php extension from url
Create the responsive next / previous button for my project
Hive Table Partition Metadata Issue
How to check if an array contains any elements of another array

Trending Discussions on presto

QUESTION

SQL - Is it possible to print all records or only one record taking into account count(*) in a table?

Asked 2022-Apr-04 at 08:54

I am trying to find a way to print the result taking into account if exists records in a table or not. If yes I should print all the records in a table, otherwise I should print only one record.

Example:

I have the Table Example:

ColA	Colb	ColC

If select count(*) from Example > 0 THEN Return

ColA	Colb	ColC
1	VA1	NULL
2	VB1	NULL
3	NULL	VA2

If select count(*) from Example <= 0 THEN Return

ColA	Colb	ColC
Result	NA	NA

Is it possible to do something like that? I am doing the development using PRESTO.

Thanks you in advance

ANSWER

Answered 2022-Apr-04 at 08:49

We could introduce a dummy/default row via a union, and then retain it only in the event of the Example table being empty:

 WITH cte AS (
    SELECT ColA, ColB, ColC, 0 AS pos FROM Example
    UNION ALL
    SELECT 'Result', 'NA, 'NA', 1
)

SELECT ColA, ColB, ColC
FROM cte
WHERE pos = 0 OR NOT EXISTS (SELECT 1 FROM cte WHERE pos = 0);

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

QUESTION

How to group into batches after assigning rank

Asked 2022-Feb-11 at 18:34

I have a table that I'm first trying to group based on unique column values (using dense_rank) and then further group those items into batches of 5. Below is my table:

video_id	frame_id	verb
video_a	frame_1	walk
video_a	frame_2	run
video_a	frame_3	sit
video_a	frame_4	walk
video_a	frame_5	walk
video_a	frame_6	walk
video_b	frame_7	stand
video_b	frame_8	stand
video_b	frame_9	run
video_b	frame_10	run
video_b	frame_11	sit
video_b	frame_12	run
video_b	frame_13	run

And below is what I'm trying to get:

video_id	frame_id	verb	batch_of_five
video_a	frame_1	walk	1
video_a	frame_2	run	1
video_a	frame_3	sit	1
video_a	frame_4	walk	1
video_a	frame_5	walk	1
video_a	frame_6	walk	2
video_b	frame_7	stand	3
video_b	frame_8	stand	3
video_b	frame_9	run	3
video_b	frame_10	run	3
video_b	frame_11	sit	3
video_b	frame_12	run	4
video_b	frame_13	run	4

Where each video_id has a unique rank and each batch of 10 within each ranked video_id has its own unique rank (and each batch of 10 overall has a unique id regardless of whether they belong to the same video_id or not).

I'm able to group based on the video_id column but am having trouble grouping those items further so that they are both in batches of 10 and unique across all video_ids. I thought about using a group by clause but I'm trying to keep the other columns intact as well (verb column).

Here is my presto query so far:

SELECT
    *
FROM (
    SELECT
        *,
        -- Give each unique video_id a unique rank
        DENSE_RANK() OVER (ORDER BY video_id) AS video_batch
    FROM videos
)

ANSWER

Answered 2022-Feb-08 at 16:08

Calculate frame rank (partition by video_id), divide by 6 (integer division) to get batch number in video_id partition. Then rank again to get absolute batch number:

with sample_data as(        
select 'video_a' as video_id, 'frame_1' as frame_id , 'walk' as verb union all 
select 'video_a', 'frame_2' , 'run'   union all
select 'video_a', 'frame_3' , 'sit'   union all
select 'video_a', 'frame_4' , 'walk'  union all
select 'video_a', 'frame_5' , 'walk'  union all
select 'video_a', 'frame_6' , 'walk'  union all
select 'video_b', 'frame_7' , 'stand' union all
select 'video_b', 'frame_8' , 'stand' union all
select 'video_b', 'frame_9' , 'run'   union all
select 'video_b', 'frame_10', 'run'   union all
select 'video_b', 'frame_11', 'sit'   union all
select 'video_b', 'frame_12', 'run'   union all
select 'video_b', 'frame_13', 'run'
)

select s.*, 
       dense_rank() over(order by video_id, rnk_frame / 6) batch_of_five
from
(
select video_id, frame_id, verb, 
       CAST(regexp_extract(frame_id,'_(\d*)$',1) AS INT) frame_number,
       dense_rank() over(partition by video_id order by CAST(regexp_extract(frame_id,'_(\d*)$',1) AS INT)) rnk_frame
  from sample_data
)s
order by video_id, frame_number;

Result:

video_id    frame_id    verb    frame_number    rnk_frame   batch_of_five
video_a     frame_1     walk    1                1           1
video_a     frame_2     run     2                2           1
video_a     frame_3     sit     3                3           1
video_a     frame_4     walk    4                4           1
video_a     frame_5     walk    5                5           1
video_a     frame_6     walk    6                6           2
video_b     frame_7     stand   7                1           3
video_b     frame_8     stand   8                2           3
video_b     frame_9     run     9                3           3
video_b     frame_10    run     10               4           3
video_b     frame_11    sit     11               5           3
video_b     frame_12    run     12               6           4
video_b     frame_13    run     13               7           4

I extracted frame_number to sort as integer, not as string, to get the same sort order as in your question (some sorting column is absolutely necessary), if you already have rank as you mentioned in question, you can use it instead.

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

id	product	market
1	Cabinet	AU
1	Cabinet	GB
1	Cabinet	FR
2	Chair	US
2	Chair	GB
3	Desk	BE
3	Desk	GB
3	Desk	FR
3	Desk	US

_col0
15.0

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

Create the responsive next / previous button for my project

Asked 2021-Dec-19 at 04:40

could you guys please help me creating a next and previous buttons ? I've been struggling because of my bad javascript . I saw some people use Jquery and almost all Javascript. I'm practicing Javascript so there are a lot of things I don't know. Thank you very much.

Wants: Next / Previous button to go to next page and go back page if users want to read again that page.

Link of my demo: https://jsfiddle. net/hioihia123/zgjswtay/3/

body {
  font-size: 18px;
  background: url("https://www.toptal.com/designers/subtlepatterns/patterns/groovepaper.png");
  font-family: 'Gelasio', serif;
}


main {
  color: black;
  font-size: 1.1rem;
  display: flex;
  flex-direction: column;
  width:100%;
  margin-left : auto;
  margin-right: auto;
}

main div {
  width: 100%;
  padding: 6rem 5rem;
}

h2 {
  text-align: center;
  font-size: 1.2rem;
  font-weight: normal;
  margin-bottom: 6rem;
}
h1 {
  font-family: 'Ibarra Real Nova', serif;
  text-align: center;
  font-weight: 400;
  font-size: 3rem;
  text-transform: uppercase;
  margin-bottom: 6rem;
  letter-spacing: .1rem;
}

.right-page {
  margin-top: 0;
  padding-top: 0;
}
.right-page p {
  line-height: 1.4;
  text-align: justify;
  text-justify: inter-word;
}

.right-page p:first-letter {
    font-family: 'Ibarra Real Nova', serif;
    font-size: 4.5rem;
    float: left;
    margin-top: .5rem;
    margin-right: 1rem;
    line-height: .7;
}

.left-page {
    text-align: center;
    padding-top: 4rem;
}

.left-page small {
  font-style: italic;
}

.left-page img {
  max-width: 90%;
  display: block;
  margin-left: auto;
  margin-right: auto;
}


@media screen and (min-width: 900px) {
  main {
    flex-direction: row;
    with: 100%;
    max-width: 1800px;
  }
  main div {
    width: 50%;
  }
  .left-page {
    padding-top: 14rem;
  }
  .right-page {
    padding-top: 6rem;
    max-height: 100vh;
    height: 100vh;
  }
}

<!DOCTYPE html>
<html lang="en" >
<head>
  <meta charset="UTF-8">
  <title> Old book Stories</title>
  <link href="https://fonts.googleapis.com/css?family=Gelasio:400,400i|Ibarra+Real+Nova&display=swap" rel="stylesheet"><link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/normalize/5.0.0/normalize.min.css">
<link rel="stylesheet" href="./style.css">

</head>
<body>
<!-- partial:index.partial.html -->
<main>
  <div class="left-page">
    <img src="https://www.oldbookillustrations.com/wp-content/uploads/2017/03/there-lonely.jpg"/>
    <small>But go there, lonely,<br>
At eventide,<br>
And hearken, hearken<br>
To the lisping tide.<br>
</small>
  </div>
  <div class="right-page">
    <h2>[ 1 ]</h2>
    <h1>Depender</h1>
    <p>
      En cuanto a todas las cosas que existen en el mundo, unas dependen de nosotros,
otras no dependen de nosotros. De nosotros dependen; nuestras opiniones, nuestros
movimientos, nuestros deseos, nuestras inclinaciones, nuestras aversiones; en una
palabra, todas nuestras acciones.<br>
Así, ante toda fantasía perturbadora, está presto a decir: <i>“Tu no eres sino una
      imaginación, y en absoluto eres lo que parece”</i>, enseguida examínala con atención y
ponla a prueba, para ello sírvete de las reglas que tienes, principalmente con esta
primera que es, a saber : de si la cosa que te hace penar es del número de aquellas
que dependen de nosotros o de aquellas que no están en nuestro poder. Di sin
      titubear: <i>“Esa en nada me atañe”.</i>
    </p>
  </div>
</main>
<!-- partial -->
  
</body>
</html>

ANSWER

Answered 2021-Dec-19 at 04:40

Can you simply add the Previous and Next buttons at the footer or somewhere you'd prefer, and link to appropriate pages? Won't that be simple enough in your case?

<button onclick="location.href='https://google.com';">Previous</button>
<button onclick="location.href='https://facebook.com';">Next</button>

UPDATE

Since you have all pages in a div inside main, its pretty easy to traverse through the pages, get the div that is visible, and hide it, and show the next or previous page depending upon the button clicked :)

Here's a sample:

function changePage(prevOrNext) {
  var pages = document.getElementsByTagName('main')[0].children;
  for (let pageIndex = 0; pageIndex < pages.length; pageIndex++) {
    if (pages[pageIndex].style.display == "block") {
      if (prevOrNext == 1 && pageIndex < pages.length - 1) {
        pages[pageIndex].style.display = "none"
        pages[pageIndex + 1].style.display = "block"
        document.body.scrollTop = document.documentElement.scrollTop = 0;
      } else if (prevOrNext == 0 && pageIndex > 0) {
        pages[pageIndex].style.display = "none"
        pages[pageIndex - 1].style.display = "block"
        document.body.scrollTop = document.documentElement.scrollTop = 0;
      }
    }
  }
}

body {
  font-size: 18px;
  background: url("https://www.toptal.com/designers/subtlepatterns/patterns/groovepaper.png");
  font-family: 'Gelasio', serif;
}

main {
  color: black;
  font-size: 1.1rem;
  display: flex;
  flex-direction: column;
  width: 100%;
  margin-left: auto;
  margin-right: auto;
}

main div {
  width: 100%;
  padding: 6rem 5rem;
  display: none
}

h2 {
  text-align: center;
  font-size: 1.2rem;
  font-weight: normal;
  margin-bottom: 6rem;
}

h1 {
  font-family: 'Ibarra Real Nova', serif;
  text-align: center;
  font-weight: 400;
  font-size: 3rem;
  text-transform: uppercase;
  margin-bottom: 6rem;
  letter-spacing: .1rem;
}

.right-page {
  margin-top: 0;
  padding-top: 0;
}

.right-page p {
  line-height: 1.4;
  text-align: justify;
  text-justify: inter-word;
}

.right-page p:first-letter {
  font-family: 'Ibarra Real Nova', serif;
  font-size: 4.5rem;
  float: left;
  margin-top: .5rem;
  margin-right: 1rem;
  line-height: .7;
}

.left-page {
  text-align: center;
  padding-top: 4rem;
}

.left-page small {
  font-style: italic;
}

.left-page img {
  max-width: 90%;
  display: block;
  margin-left: auto;
  margin-right: auto;
}

@media screen and (min-width: 900px) {
  main {
    flex-direction: row;
    with: 100%;
    max-width: 1800px;
  }
  main div {
    width: 50%;
  }
  .left-page {
    padding-top: 14rem;
  }
  .right-page {
    padding-top: 6rem;
    max-height: 100vh;
    height: 100vh;
  }
}

<!DOCTYPE html>
<html lang="en">

<head>
  <meta charset="UTF-8">
  <title> Old book Stories</title>
  <link href="https://fonts.googleapis.com/css?family=Gelasio:400,400i|Ibarra+Real+Nova&display=swap" rel="stylesheet">
  <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/normalize/5.0.0/normalize.min.css">
  <link rel="stylesheet" href="./style.css">

</head>

<body>
  <!-- partial:index.partial.html -->
  <main>
    <div class="left-page" style="display: block">
      <img src="https://www.oldbookillustrations.com/wp-content/uploads/2017/03/there-lonely.jpg" />
      <small>But go there, lonely,<br>
At eventide,<br>
And hearken, hearken<br>
To the lisping tide.<br>
</small>
    </div>
    <div class="right-page">
      <h2>[ 1 ]</h2>
      <h1>Depender</h1>
      <p>
        En cuanto a todas las cosas que existen en el mundo, unas dependen de nosotros, otras no dependen de nosotros. De nosotros dependen; nuestras opiniones, nuestros movimientos, nuestros deseos, nuestras inclinaciones, nuestras aversiones; en una palabra,
        todas nuestras acciones.<br> Así, ante toda fantasía perturbadora, está presto a decir: <i>“Tu no eres sino una
      imaginación, y en absoluto eres lo que parece”</i>, enseguida examínala con atención y ponla a prueba, para ello sírvete de las reglas que tienes, principalmente con esta primera que es, a saber : de si la cosa que te hace penar es del número de
        aquellas que dependen de nosotros o de aquellas que no están en nuestro poder. Di sin titubear: <i>“Esa en nada me atañe”.</i>
      </p>
    </div>
  </main>
  <!-- partial -->

</body>

<footer style="text-align: center">
  <button id="prev" onclick="changePage(0)">Previous</button>
  <button id="next" onclick="changePage(1)">Next</button>
</footer>

</html>

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

QUESTION

Hive Table Partition Metadata Issue

Asked 2021-Dec-15 at 08:53

we have a Hive Internal partitioned table which is existing from long time. recently we discovered that we need to change a column's name and datatype. also, we need to remove 3 duplicate records from the table. so we performed this step.

alter table tablename CHANGE line_nbr line_id INT; (it was string before)

INSERT OVERWRITE TABLE table1 PARTITION(cmny_cd,order_dt)
select 
               column1,
               columnx
               from table1
               where id NOT IN ('1','2','3')
               and order_dt in (CAST('2020-02-18' AS DATE),CAST('2020-02-19' AS DATE),CAST('2020-10-30' AS DATE));"

Tried to run the above steps to change the column name and type and also remove dupes. during this, we encountered an error saying there are few bad partitions in the table. but when checked, the dupes were not removed, but the column name and type were changed.

Now, we removed the bad partitions from the table, and then tried to run only the insert overwrite step to remove the dupes. It was successful, and removed the dupes. But if i try to query the table now, for some of the dates, it gives me an error

-- presto error: Error opening Hive split gs:xxxx/cmny_cd=xxx/order_dt=2021-09-20/000000_0 (offset=0, length=24370774): Malformed ORC file. Cannot read SQL type 'integer' from ORC stream '._col6' of type STRING with attributes {} [gs:xxxx/cmny_cd=xxx/order_dt=2021-09-20/000000_0]

when i check the show create table, the name and dataype are changed, but when i try the below

describe formatted tablename partition (cmny_cd='xxx',order_dt='2021-09-20');

few partitions, it shows changed name and type, but few, it shows the old name and string datatype. Im guessing this could have happened, because of the failure when we first attempted to change the dataype and remove dupes.

Im confused on what should i do in order to repair the table and query the table without any issues. Can someone please help on this? It would be greatly appreciated.

we ran msck repair table tablename already on the table, but it did not solve the issue

ANSWER

Answered 2021-Dec-15 at 08:53

Overwrite all partitions using Hive, like you did when removed duplicates. Some partitions remain not overwritten and causing error in Presto because the type inside ORC file and in Hive metadata is different. So, just try to overwrite partitions which you did not previously overwritten or overwrite all of them:

INSERT OVERWRITE TABLE table1 PARTITION(cmny_cd,order_dt)
select 
        column1,
        columnx
        ...
  from table1
  ---you can limit partitions in the WHERE

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

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

Vulnerabilities

CVE-2020-15087 HIGH

In Presto before version 337, authenticated users can bypass authorization checks by directly accessing internal APIs. This impacts Presto server installations with secure internal communication configured. This does not affect installations that have not configured secure internal communication, as these installations are inherently insecure. This only affects Presto server installations. This does NOT affect clients such as the CLI or JDBC driver. This vulnerability has been fixed in version 337. Additionally, this issue can be mitigated by blocking network access to internal APIs on the coordinator and workers.

Install presto

You can download it from GitHub, Maven.
You can use presto like any standard Java library. Please include the the jar files in your classpath. You can also use any IDE and you can run and debug the presto component as you would do with any other Java program. Best practice is to use a build tool that supports dependency management such as Maven or Gradle. For Maven installation, please refer maven.apache.org. For Gradle installation, please refer gradle.org .