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

Useful Video Courses


Video

Apache Spark Online Training

46 Lectures 3.5 hours

Arnab Chakraborty

Video

Apache Spark with Scala - Hands On with Big Data

23 Lectures 1.5 hours

Mukund Kumar Mishra

Video

Learn Apache Cordova using Visual Studio 2015 & Command line

16 Lectures 1 hours

Nilay Mehta

Video

Delta Lake with Apache Spark using Scala

52 Lectures 1.5 hours

Bigdata Engineer

Video

Apache Zeppelin - Big Data Visualization Tool

14 Lectures 1 hours

Bigdata Engineer

Video

Olympic Games Analytics Project in Apache Spark for Beginner

23 Lectures 1 hours

Bigdata Engineer

Advertisements