Apache Presto - Basic SQL Operations


Advertisements

In this chapter, we will discuss how to create and execute queries on Presto. Let us go through Presto supported basic data types.

Basic Data Types

The following table describes the basic data types of Presto.

S.No Data type & Description
1.

VARCHAR

Variable length character data

2.

BIGINT

A 64-bit signed integer

3.

DOUBLE

A 64-bit floating point double precision value

4.

DECIMAL

A fixed precision decimal number. For example DECIMAL(10,3) - 10 is precision, i.e. total number of digits and 3 is scale value represented as fractional point. Scale is optional and default value is 0

5.

BOOLEAN

Boolean values true and false

6.

VARBINARY

Variable length binary data

7.

JSON

JSON data

8.

DATE

Date data type represented as year-month-day

9.

TIME, TIMESTAMP, TIMESTAMP with TIME ZONE

TIME - Time of the day (hour-min-sec-millisecond)

TIMESTAMP - Date and time of the day

TIMESTAMP with TIME ZONE - Date and time of the day with time zone from the value

10.

INTERVAL

Stretch or extend date and time data types

11.

ARRAY

Array of the given component type. For example, ARRAY[5,7]

12.

MAP

Map between the given component types. For example, MAP(ARRAY[‘one’,’two’],ARRAY[5,7])

13.

ROW

Row structure made up of named fields

Presto − Operators

Presto operators are listed in the following table.

S.No Operator & Description
1. Arithmetic operator

Presto supports arithmetic operators such as +, -, *, /, %

2. Relational operator

<,>,<=,>=,=,<>

3. Logical operator

AND, OR, NOT

4. Range operator

Range operator is used to test the value in a specific range. Presto supports BETWEEN, IS NULL, IS NOT NULL, GREATEST and LEAST

5. Decimal operator

Binary arithmetic decimal operator performs binary arithmetic operation for decimal type Unary decimal operator − The - operator performs negation

6. String operator

The ‘||’ operator performs string concatenation

7. Date and time operator

Performs arithmetic addition and subtraction operations on date and time data types

8. Array operator

Subscript operator[] - access an element of an array

Concatenation operator || - concatenate an array with an array or an element of the same type

9. Map operator

Map subscript operator [] - retrieve the value corresponding to a given key from a map

Advertisements