14. Query Execution - 6
Query Plan in Presto Web UI
Presto Web UI ろ 貎朱Μ
history 蠍磯レ 螻牛覃 ろ 貎
襴 Plan 覲企ゼ Graphical 蟆
覲 給.
Query Plan EXPLAIN 覈轟企ゼ 牛
text 襦 豢 螳ロ.
13
16. 一危 (Data Types)(2/2)
Groups Type Description Examples
Date and Time
DATE 讌 (, , ). : DATE '2001-08-22'
TIME TIME ZONE 螳 (, 覿, 豐, 覦襴 豐). : TIME '01:02:03.456'
TIME WITH TIME ZONE TIME ZONE 螳 (, 覿, 豐, 覦襴 豐).
: TIME '01:02:03.456 America
/Los_Angeles'
TIMESTAMP
TIME ZONE 螳螻 讌螳 語ろ
.
: TIMESTAMP '2001-08-22 03
:04:05.321'
TIMESTAMP WITH TIM
E ZONE
TIME ZONE 讌 螳 語ろ
.
: TIMESTAMP '2001-08-22 03
:04:05.321 America/Los_Angele
s'
INTERVAL YEAR TO MO
NTH
螻 覯. : INTERVAL '3' MONTH
INTERVAL DAY TO SEC
OND
, 螳, 覿, 豐 覦 覦襴 豐 覯. : INTERVAL '2' DAY
Structural
ARRAY 讌 貉危朱 覦一. : ARRAY[1, 2, 3]
MAP 讌 貉危朱 企 MAP.
: MAP(ARRAY['foo', 'bar'], AR
RAY[1, 2])
ROW
覈覈 襦 蟲焔 蟲譟. SQL
螳讌 , 谿語^ 一一襯 ′
.
: CAST(ROW(1, 2.0) AS ROW(
x BIGINT, y DOUBLE))
Network Address IPADDRESS IPv4 IPv6 譯殊襯 IP 譯殊.
: ,IPADDRESS '10.0.0.1'IPAD
DRESS '2001:db8::1'
HyperLogLog
HyperLogLog HyperLogLog れ豺襦 螻一 螳.
P4HyperLogLog
P4HyperLogLog れ豺 ,讌襷 譟磯
.
Quantile Digest QDigest
Quantile れ伎ろ (qdigest) 譯殊伎 誤
旧 一危 覿襯 谿 蟲
譟
Presto 一危
15
17. SQL Statement Syntax (1/3)
/覲蟆
Syntax Description Synopsis Examples
CREATE
襦 ろる,
企 煙
燕.
CREATE SCHEMA [ IF NOT EXISTS ] schema_name
[ WITH ( property_name = expression [, ...] )
CREATE SCHEMA IF NOT EXISTS
orders;
CREATE TABLE [ IF NOT EXISTS ]
table_name (
{ column_name data_type [ COMMENT comment ]
[ WITH ( property_name = expression [, ...] ) ]
| LIKE existing_table_name [ { INCLUDING |
EXCLUDING } PROPERTIES ] }
[, ...]
)
[ COMMENT table_comment ]
[ WITH ( property_name = expression [, ...] ) ]
CREATE TABLE IF NOT EXISTS orders (
orderkey bigint,
orderstatus varchar,
totalprice double COMMENT 'Price in
cents.',
orderdate date
)
COMMENT 'A table to keep track of
orders.'
ALTER
Definition
覲蟆渚.
ALTER TABLE name RENAME TO new_name
ALTER TABLE name ADD COLUMN column_name data_
type [ COMMENT comment ] [ WITH ( property_name
= expression [, ...] ) ]
ALTER TABLE name RENAME TO
new_name
ALTER TABLE name ADD COLUMN
column_name orderkey bigint
ALTER TABLE name DROP COLUMN
column_name
ALTER TABLE name RENAME
COLUMN column_name TO
new_column_name
Presto SQL Syntax
16
18. SQL Statement Syntax (2/3)
譟壱/焔/覲蟆/
Syntax Description Synopsis Examples
INSERT
Table 襦 一磯ゼ
1 焔.
INSERT INTO table_name [ ( column [, ... ] ) ] que
ry
INSERT INTO nation (nationkey, n
ame, regionkey, comment)
VALUES (26, 'POLAND', 3, 'no co
mment');
SELECT
譟郁唄 襷 一危磯ゼ 譟
.
[ WITH with_query [, ...] ]
SELECT [ ALL | DISTINCT ] select_expr [, ...]
[ FROM from_item [, ...] ]
[ WHERE condition ]
[ GROUP BY [ ALL | DISTINCT ] grouping_element
[, ...] ]
[ HAVING condition]
[ { UNION | INTERSECT | EXCEPT } [ ALL | DISTIN
CT ] select ]
[ ORDER BY expression [ ASC | DESC ] [, ...] ]
[ LIMIT [ count | ALL ] ]
SELECT origin_state, NULL, NULL,
sum(package_weight)
FROM shipping
GROUP BY origin_state
DROP
schema/table/view 煙
DROP SCHEMA [ IF EXISTS ] schema_name
DROP TABLE [ IF EXISTS ] table_name
DROP VIEW [ IF EXISTS ] view_name
DROP TABLE sales
DROP TABLE IF EXISTS tb_date
DROP VIEW IF EXISTS vtb_date
DELETE
Table 一危磯ゼ
.(一 貉朱
螳.)
DELETE FROM table_name [ WHERE condition ]
DELETE FROM lineitem
WHERE orderkey IN (SELECT ord
erkey FROM orders WHERE priori
ty = 'LOW');
Presto SQL Syntax
17
19. SQL Statement Syntax (3/3)
蠍壱
Syntax Description Synopsis Examples
COMMIT
豕 transaction
COMMIT.
COMMIT [ WORK ] COMMIT;
EXPLAIN
覈豪語 朱Μ 覿
ろ 螻 蟇
覈豪語 煙 蟆
.
EXPLAIN [ ( option [, ...] ) ] statement
where option can be one of:
FORMAT { TEXT | GRAPH+D8+B7:C7+A
7:C7
- EXPLAIN SELECT regionkey, count(*) F
ROM nation GROUP BY 1;
- EXPLAIN (TYPE DISTRIBUTED) SELEC
T regionkey, count(*) FROM nation GRO
UP BY 1;
Presto SQL Syntax
18
20. - Logical Operators
朱Μ一一襯 螻牛.
- Comparison Functions and
Operators
觜蟲一一襯 螻牛.
(Functions)(1/5)
- Range Operator: BETWEEN
Operators ろ 螳 轟 覯 伎 蟆曙
襯 螻牛.
- IS NULL and IS NOT NULL
螳 NULL(覩語)語 覿 誤襯 螻牛.
- Conditional Expressions
譟郁唄覓語 螻牛.
SELECT 3 BETWEEN 2 AND 6;
SELECT 3 NOT BETWEEN 2 AND 6;
SELECT 3.0 IS NULL; -- false
SELECT a, CASE a WHEN 1 THEN 'one' WHEN 2 THEN 'two' ELSE
'many' END
if( 譟郁唄 , true_value , false_value )
譟郁唄 谿語企 true_value, 蟇一企 false_value 覦.
Presto - Functions
19
21. - Mathematical Operators
襯 螻牛.
- Comparison Functions and
Operatorsabs( x ) [リ骸 ]. x 螳 覦 .
cbrt( x ) 覦, x 觚 襭碁ゼ 覦 .
ceil( x ) [リ骸 ], ceiling()螻 .
ceiling( x ) [リ骸 ], 覦 x螳 螳 螳蟾 襦 覦襴朱
cosine_similarity( x , y ) double, ろ 覯″ x yれ 螻 螳 貊 煙 覦 .
floor( x ) [リ骸 ] / 覦 螳 x螳 螳蟾 襦 企朱.
random( ) double / 0.0 <= x <1.0 覯 豺襯 れ.
round( x , d ) [リ骸 ] / x襦 覦襴 d 危 襴酔襯 覦.
Presto - Functions
(Functions)(2/5)
20
25. - columns襷 讌 覈.
SELECT * FROM TB; (X) SELECT a,b FROM TB; (O)
- GROUP BY 伎 cardinality 螻.
SELECT GROUP BY gender, age; (X) SELECT GROUP BY age, gender ; (O)
- ORDER BY 蟷 LIMIT
SELECT col FROM tbl ORDER BY time ; (X) SELECT col FROM tbl ORDER BY time LIMIT 100 ; (O)
- 蠏殊 讌螻 (approximate aggregate functions)
SELECT distinct(user_id) FROM (X) SELECT approx_distinct user_id FROM; (O)
- り唄 LIKE regexp_like 襦 覲蟆.
WHERE method LIKE %GET% OR method LIKE %POST% (X) WHERE regex_like(method, GET|POST) (O)
- JOIN 企 襾殊 讌
FROM small s, large l where l.id=s.id (X) FROM large l, small s where l.id=s.id (O)
- TOP N 貊襯 ROW_NUMBER RANK 襯 .
牛 焔ロレ(SQL)
Presto - tuning
24