Amazon Kinesis Analytics - SQL Reference

186 Pages·2017·1.7 MB·English

Amazon Kinesis Data Analytics
SQL Reference
Amazon Kinesis Data Analytics SQL Reference
Amazon Kinesis Data Analytics: SQL Reference
Copyright © 2022 Amazon Web Services, Inc. and/or its affiliates. All rights reserved.
Amazon's trademarks and trade dress may not be used in connection with any product or service that is not
Amazon's, in any manner that is likely to cause confusion among customers, or in any manner that disparages or
discredits Amazon. All other trademarks not owned by Amazon are the property of their respective owners, who may
or may not be affiliated with, connected to, or sponsored by Amazon.
Amazon Kinesis Data Analytics SQL Reference
Table of Contents
SQL Reference................................................................................................................................... 1
Streaming SQL Language Elements ...................................................................................................... 2
Identifiers.................................................................................................................................. 2
Data Types................................................................................................................................ 3
Numeric Types and Precision ............................................................................................... 6
Streaming SQL Operators ........................................................................................................... 7
IN Operator....................................................................................................................... 8
EXISTS Operator................................................................................................................ 8
Scalar Operators................................................................................................................ 8
Arithmetic Operators.......................................................................................................... 9
String Operators.............................................................................................................. 10
Logical Operators............................................................................................................. 15
Expressions and Literals............................................................................................................ 19
Monotonic Expressions and Operators ......................................................................................... 21
Monotonic columns.......................................................................................................... 22
Monotonic expressions...................................................................................................... 22
Rules for deducing monotonicity ........................................................................................ 23
Condition Clause...................................................................................................................... 24
Temporal Predicates................................................................................................................. 24
Syntax............................................................................................................................. 26
Example.......................................................................................................................... 26
Sample Use Case.............................................................................................................. 27
Reserved Words and Keywords ................................................................................................... 27
Standard SQL Operators ................................................................................................................... 32
CREATE statements.................................................................................................................. 32
CREATE STREAM .............................................................................................................. 32
CREATE FUNCTION........................................................................................................... 33
CREATE PUMP .................................................................................................................. 34
INSERT.................................................................................................................................... 35
Syntax............................................................................................................................. 35
Pump Stream Insert ......................................................................................................... 35
Query..................................................................................................................................... 36
Syntax............................................................................................................................. 36
select.............................................................................................................................. 36
Streaming set operators .................................................................................................... 37
VALUES operator.............................................................................................................. 37
SELECT statement.................................................................................................................... 38
Syntax............................................................................................................................. 38
The STREAM keyword and the principle of streaming SQL ..................................................... 38
SELECT ALL and SELECT DISTINCT ..................................................................................... 39
SELECT clause.................................................................................................................. 40
FROM clause.................................................................................................................... 42
JOIN clause..................................................................................................................... 45
HAVING clause................................................................................................................. 59
GROUP BY clause ............................................................................................................. 60
WHERE clause.................................................................................................................. 61
WINDOW Clause (Sliding Windows) .................................................................................... 61
ORDER BY clause ............................................................................................................. 69
ROWTIME........................................................................................................................ 71
Functions........................................................................................................................................ 73
Aggregate Functions................................................................................................................. 73
Streaming Aggregation and Rowtime Bounds ...................................................................... 74
Aggregate Function List .................................................................................................... 74
Examples of Aggregate Queries on Streams (Streaming Aggregation) ...................................... 75
iii
Amazon Kinesis Data Analytics SQL Reference
Windowed Aggregation on Streams .................................................................................... 78
AVG................................................................................................................................ 82
COUNT............................................................................................................................ 85
COUNT_DISTINCT_ITEMS_TUMBLING Function..................................................................... 88
EXP_AVG......................................................................................................................... 91
FIRST_VALUE................................................................................................................... 91
LAST_VALUE.................................................................................................................... 92
MAX............................................................................................................................... 92
MIN................................................................................................................................ 95
SUM............................................................................................................................... 98
TOP_K_ITEMS_TUMBLING Function................................................................................... 101
Analytic Functions.................................................................................................................. 103
Related Topics................................................................................................................ 104
Boolean Functions.................................................................................................................. 104
ANY.............................................................................................................................. 104
EVERY........................................................................................................................... 105
Conversion Functions.............................................................................................................. 105
CAST............................................................................................................................. 105
Date and Time Functions ......................................................................................................... 120
Time Zones.................................................................................................................... 120
Datetime Conversion Functions ........................................................................................ 121
Date, Timestamp, and Interval Operators .......................................................................... 134
Date and Time Patterns .................................................................................................. 139
CURRENT_DATE.............................................................................................................. 142
CURRENT_ROW_TIMESTAMP ............................................................................................ 143
CURRENT_TIME.............................................................................................................. 143
CURRENT_TIMESTAMP .................................................................................................... 143
EXTRACT....................................................................................................................... 144
LOCALTIME.................................................................................................................... 145
LOCALTIMESTAMP.......................................................................................................... 145
TSDIFF.......................................................................................................................... 146
Null Functions........................................................................................................................ 146
COALESCE..................................................................................................................... 146
NULLIF.......................................................................................................................... 147
Numeric Functions.................................................................................................................. 147
ABS............................................................................................................................... 147
CEIL / CEILING............................................................................................................... 148
EXP............................................................................................................................... 149
FLOOR.......................................................................................................................... 149
LN................................................................................................................................ 150
LOG10.......................................................................................................................... 151
MOD............................................................................................................................. 151
POWER.......................................................................................................................... 151
STEP............................................................................................................................. 152
Log Parsing Functions ............................................................................................................. 155
FAST_REGEX_LOG_PARSER.............................................................................................. 155
FIXED_COLUMN_LOG_PARSE............................................................................................ 159
REGEX_LOG_PARSE......................................................................................................... 160
SYS_LOG_PARSE............................................................................................................. 162
VARIABLE_COLUMN_LOG_PARSE...................................................................................... 162
W3C_LOG_PARSE............................................................................................................ 163
Sorting Functions................................................................................................................... 171
Group Rank................................................................................................................... 171
Statistical Variance and Deviation Functions ............................................................................... 175
HOTSPOTS.................................................................................................................... 175
RANDOM_CUT_FOREST................................................................................................... 179
RANDOM_CUT_FOREST_WITH_EXPLANATION.................................................................... 183
iv
Amazon Kinesis Data Analytics SQL Reference
STDDEV_POP................................................................................................................. 192
STDDEV_SAMP............................................................................................................... 194
VAR_POP....................................................................................................................... 197
VAR_SAMP ..................................................................................................................... 200
Streaming SQL Functions ........................................................................................................ 203
LAG.............................................................................................................................. 203
Monotonic Function........................................................................................................ 205
NTH_VALUE................................................................................................................... 206
String and Search Functions .................................................................................................... 206
CHAR_LENGTH / CHARACTER_LENGTH .............................................................................. 207
INITCAP......................................................................................................................... 207
LOWER.......................................................................................................................... 208
OVERLAY....................................................................................................................... 208
POSITION...................................................................................................................... 209
REGEX_REPLACE............................................................................................................. 210
SUBSTRING.................................................................................................................... 211
TRIM............................................................................................................................. 213
UPPER........................................................................................................................... 214
Kinesis Data Analytics Developer Guide ............................................................................................. 215
Document History.......................................................................................................................... 216
v
Amazon Kinesis Data Analytics SQL Reference
Amazon Kinesis Data Analytics SQL
Reference
The Amazon Kinesis Data Analytics SQL Reference describes the SQL language elements that are
supported by Amazon Kinesis Data Analytics. The language is based on the SQL:2008 standard with
some extensions to enable operations on streaming data.
For new projects, we recommend that you use Kinesis Data Analytics Studio over Kinesis Data Analytics
for SQL Applications. Kinesis Data Analytics Studio combines ease of use with advanced analytical
capabilities, enabling you to build sophisticated stream processing applications in minutes.
For information about developing Kinesis Data Analytics applications, see the Kinesis Data Analytics
Developer Guide.
This guide covers the following:
• Streaming SQL Language Elements (p. 2) – Data Types (p. 3), Streaming SQL
Operators (p. 7), Functions (p. 73).
• Standard SQL Operators (p. 32) – CREATE statements (p. 32), SELECT statement (p. 38).
• Operators for transforming and filtering incoming data – WHERE clause (p. 61), JOIN
clause (p. 45), GROUP BY clause (p. 60), WINDOW Clause (Sliding Windows) (p. 61).
• Logical Operators (p. 15) – AS, AND, OR, etc.
1
Amazon Kinesis Data Analytics SQL Reference
Identifiers
Streaming SQL Language Elements
The following topics discuss the language elements in Amazon Kinesis Data Analytics that underlie its
syntax and operations:
Topics
• Identifiers (p. 2)
• Data Types (p. 3)
• Streaming SQL Operators (p. 7)
• Expressions and Literals (p. 19)
• Monotonic Expressions and Operators (p. 21)
• Condition Clause (p. 24)
• Temporal Predicates (p. 24)
• Reserved Words and Keywords (p. 27)
Identifiers
All identifiers may be up to 128 characters. Identifiers may be quoted (with case-sensitivity) by enclosing
them in double-quote marks ("), or unquoted (with implicit uppercasing before both storage and lookup).
Unquoted identifiers must start with a letter or underscore, and be followed by letters, digits or
underscores; letters are all converted to upper case.
Quoted identifiers can contain other punctuation too (in fact, any Unicode character except control
characters: codes 0x0000 through 0x001F). You can include a double-quote in an identifier by escaping it
with another double-quote.
In the following example, a stream is created with an unquoted identifier, which is converted to upper
case before the stream definition is stored in the catalog. It can be referenced using its upper-case name,
or by an unquoted identifier which is implicitly converted to upper case.
–- Create a stream. Stream name specified without quotes,
–- which defaults to uppercase.
CREATE OR REPLACE STREAM ExampleStream (col1 VARCHAR(4));
– example 1: OK, stream name interpreted as uppercase.
CREATE OR REPLACE PUMP "STREAM_PUMP" AS INSERT INTO ExampleStream
SELECT * FROM SOURCE_SQL_STREAM_001;
– example 2: OK, stream name interpreted as uppercase.
CREATE OR REPLACE PUMP "STREAM_PUMP" AS INSERT INTO examplestream
SELECT * FROM customerdata;
– example 3: Ok.
CREATE OR REPLACE PUMP "STREAM_PUMP" AS INSERT INTO EXAMPLESTREAM
SELECT * FROM customerdata;
– example 2: Not found. Quoted names are case-sensitive.
CREATE OR REPLACE PUMP "STREAM_PUMP" AS INSERT INTO "examplestream"
SELECT * FROM customerdata;
When objects are created in Amazon Kinesis Data Analytics, their names are implicitly quoted, so it is
easy to create identifiers that contain lowercase characters, spaces, dashes, or other punctuation. If you
reference those objects in SQL statements, you will need to quote their names.
2
Amazon Kinesis Data Analytics SQL Reference
Data Types
Reserved Words and Keywords
Certain identifiers, called keywords, have special meaning if they occur in a particular place in a
streaming SQL statement. A subset of these key words are called reserved words and may not be
used as the name of an object, unless they are quoted. For more information, see Reserved Words and
Keywords (p. 27).
Data Types
The following table summarizes the data types supported by Amazon Kinesis Data Analytics.
SQL Data Type JSON Data Type Description Notes
BIGINT number 64-bit signed integer
BINARY BASE64-encoded string Binary (non character) Substring works on
data BINARY. Concatenation
does not work on
BINARY.
BOOLEAN boolean TRUE, FALSE, or NULL Evaluates to TRUE,
FALSE, and UNKNOWN.
CHAR (n) string A character string n must be greater than
of fixed length n. 0 and less than 65535.
Also specifiable as
CHARACTER
DATE string A date is a calendar day Precision is day. Range
(year/month/day). runs from the largest
value, approximately
+229 (in years) to the
smallest value, -229.
DECIMAL number A fixed point, with up to Can be specified with
19 significant digits. DECIMAL, DEC, or
DEC NUMERIC.
NUMERIC
DOUBLE number A 64-bit floating point 64-bit approx value;
number -1.79E+308 to 1.79E
DOUBLE PRECISION +308. Follows the ISO
DOUBLE PRECISION
data type, 53 bits are
used for the number's
mantissa in scientific
notation, representing
15 digits of precision
and 8 bytes of storage.
INTEGER number 32-bit signed integer.
Range is -2147483648
INT to 2147483647
[ 2**(31) to 2**(31)- 1]
3
Amazon Kinesis Data Analytics SQL Reference
Data Types
SQL Data Type JSON Data Type Description Notes
INTERVAL <timeunit> string Day-time intervals Allowed in an
[TO <timeunit>] supported, year-month expression in date
intervals not supported arithmetic, but cannot
be used as a datatype
for a column in a table
or stream.
<timeUnit> string The units of a INTERVAL Supported units are
value YEAR, MONTH, DAY,
HOUR, MINUTE, and
SECOND
SMALLINT number 16-bit signed integer Range is -32768 to
32767
[2**(15) to 2**(15)-1]
REAL number A 32-bit floating point Following the ISO
number REAL data type, 24
bits are used for the
number's mantissa
in scientific notation,
representing 7 digits of
precision and 4 bytes of
storage. The minimum
value is -3.40E+38;
the maximum value is
3.40E+38.
TIME string A TIME is a Its precision is
time in a day milliseconds; its range
(hour:minute:second). is 00:00:00.000 to
23:59:59.999. Since
the system clock
runs in UTC, the
timezone used for
values stored in a TIME
or TIMESTAMP column
is not considered.
for values stored in a
TIME or TIMESTAMP
column.
4
Amazon Kinesis Data Analytics SQL Reference
Data Types
SQL Data Type JSON Data Type Description Notes
TIMESTAMP string A TIMESTAMP is a A TIMESTAMP value
combined DATE and always has a precision
TIME. of 1 millisecond. It has
no particular timezone.
Since the system
clock runs in UTC, the
timezone used for
values stored in a TIME
or TIMESTAMP column
is not considered.
Its range runs from
the largest value,
approximately +229 (in
years) to the smallest
value, -229. Each
timestamp is stored as
a signed 64-bit integer,
with 0 representing
the Unix epoch (Jan
1, 1970 00:00am).
This means that the
largest TIMESTAMP
value represents
approximately 300
million years after
1970, and the smallest
value represents
approximately 300
million years before
1970. Following
the SQL standard, a
TIMESTAMP value has
an undefined timezone.
TINYINT number 8-bit signed integer Range is -128 to 127,
VARBINARY (n) BASE64-encoded string Also specifiable as n must be greater than
BINARY VARYING 0 and less than 65535.
VARCHAR (n) string Also specifiable as n must be greater than
CHARACTER VARYING 0 and less than 65535.
Notes
Regarding characters:
• Amazon Kinesis Data Analytics supports only Java single-byte CHARACTER SETs.
• Implicit type conversion is not supported. That is, characters are mutually assignable if and only if
they are taken from the same character repertoire and are values of the data types CHARACTER or
CHARACTER VARYING.
Regarding numbers:
5