SQL reference

Partition by columns

When you use the PARTITIONED BY (column-list) clause without specifying INTO x BUCKETS/OBJECTS, you can store the query result by using Hive-style partitioning, which is to create partitions that contain only rows that have certain values for one or more columns. Choose this physical layout if the stored object is further analyzed by using SQL queries that specify predicates on the partition columns.

For example, a result object that contains worldwide order data has a column country to represent the country that the order is initiated from. Partitioning the result object by the column PARTITIONED BY (country), would create a result object with a partition for each country present in the query result.

When the result object is stored this way on Cloud Object Storage, each SQL query that contains a predicate, such as country = 'USA' or country in ('MALTA', 'ITALY', 'VATICAN CITY'), benefits from this physical layout. The reason is that during SQL query execution partitions must be read only if they contain data for the countries of interest. This layout tremendously cuts down the I/O traffic of the SQL query.

See the following extra remarks on Hive-style partitioning.

• Hive-style partitions have an eye-catching naming scheme because the column names that are used for partitioning are part of the partition object prefix, for example, /order/COUNTRY=USA/part-m-00000.snappy.parquet.
• Hive-style partitions do not contain any values for partition columns since their values are stored in the object prefix of the partition. Thus, if you copy a HIVE-style partition and rename the object prefix by removing the partition column values, you lose data.
• Hive-style partitions have a tendency for data skewing. For example, the partition that represents order data from Malta is likely much smaller than the partition that represents order data from the US. You can partition the query result into separate objects if you want to have equally sized partitions.

Partition by columns into objects

By partitioning a query result into objects, you can specify the exact number of equally sized result partitions. With this partitioning, you can experimentally fine-tune the number of objects to meet certain criteria for partition size. To specify the number of partitions, use the PARTITIONED INTO x BUCKETS/OBJECTS clause.

For example, knowing the size of the query result, it is possible to calculate the number of objects to end up with partitions that have a certain size. For example, 128 MB, which is the Hadoop default file size, or any other size that meets application requirements.

The INTO x BUCKETS/OBJECTS clause can be combined with the BY (column-list) clause to create some partitions that support data affinity regarding specified partition columns.

Continue with the preceding example that specifies PARTITION BY (customerid) INTO 10 OBJECTS stores the query result into 10 objects, which ensures that all data for a customer is stored in the same partition. Although it is ensured that all data for a certain customer is stored in the same partition, it is not ensured that the data is physically sorted according to the specified column.

Partition by number of rows

By partitioning by number of rows you can specify the number of rows that go into a single partition. To specify the number of rows stored in each partition, use the EVERY x ROWS clause. In case the row length of the result object is not varying heavily, with the partition every rows clause you can also create almost equally sized result partitions.

The use of the PARTITIONED EVERY x ROWS clause on a sorted query result ensures that partitions are created to have some rows that are sorted according to the query's SORT BY clause. This physical layout can be useful to create partitions that are processed by an application in a pagination manner, for example, browsing order data sorted by order date and customer ID.

The use of PARTITIONED EVERY x ROWS clause causes data to be written single-threaded to Cloud Object Storage, which means that no parallel I/O is performed to write query results to Cloud Object Storage.

groupByClause

groupingSet

More complex group by clauses use so called grouping sets to provide more insights into the set of rows grouped by a grouping expression.

Grouping sets are best explained by using examples. The following set examples use values clauses to define result sets for group by operations. For more information about the values clause, see valuesClause.

In the following example SQL query, sales data is grouped per year and quarter.

-- grouping sales data per year and quarter
SELECT
    sales.col1 AS year,
    sales.col2 AS quarter,
    SUM(sales.col3) AS amount
FROM VALUES
    (2017, 1 ,100),
    (2017, 1 ,50),
    (2017, 2 ,200),
    (2017, 2 ,300),
    (2018, 1 ,300),
    (2018, 1 ,100),
    (2018, 2 ,400) AS sales
GROUP BY col1, col2
ORDER BY year, quarter

The result of the example query is shown in the following table.

A ROLLUP grouping is an extension to the group by clause that produces a result set containing subtotal rows in addition to the regular grouped rows. A GROUP BY COL1, COL2 WITH ROLLUP generates the following grouping sets: (COL1, COL2), (COL1), (). The N grouping expressions of the ROLLUP convert to N+1 grouping sets.

Referring to the preceding example, adding a WITH ROLLUP modifier to the group by clause computes rollup sales data on quarter by year basis, a yearly basis, and a grand total as shown by the following example.

-- rollup sales data on quarter by year basis, a yearly basis, and a grand total
SELECT
    sales.col1 AS year,
    sales.col2 AS quarter,
    SUM(sales.col3) AS amount
FROM VALUES
    (2017, 1 ,100),
    (2017, 1 ,50),
    (2017, 2 ,200),
    (2017, 2 ,300),
    (2018, 1 ,300),
    (2018, 1 ,100),
    (2018, 2 ,400) AS sales
GROUP BY col1, col2
WITH ROLLUP
ORDER BY year, quarter

The result of the example query is shown in the following table.

A CUBE grouping is an extension to the group by clause that produces a result set that contains all the rows of a ROLLUP aggregation and in addition, grouping sets that do not represent a subtotal or grand total. A GROUPY BY COL1, COL2 WITH CUBE generates the following grouping sets: (COL1, COL2), (COL1), (COL2), (). The N elements of a CUBE convert to 2**N (2 to the power N) grouping sets.

Referring to the preceding example, adding a WITH CUBE modifier to the group by clause computes rollup sales data on a quarter by year basis, a yearly basis, a quarterly year-independent basis, and a grand total as shown by the following example.

-- rollup sales data on a quarter by year basis, a yearly basis,
-- a quarterly year-independent basis and a grand total
SELECT
    sales.col1 AS year,
    sales.col2 AS quarter,
    SUM(sales.col3) AS amount
FROM VALUES
    (2017, 1 ,100),
    (2017, 1 ,50),
    (2017, 2 ,200),
    (2017, 2 ,300),
    (2018, 1 ,300),
    (2018, 1 ,100),
    (2018, 2 ,400) AS sales
GROUP BY col1, col2
WITH CUBE
ORDER BY year, quarter

The result of the example query is shown in the following table.

With a GROUPING SETS grouping, an extension to the group by clause, you can explicitly specify the grouping sets of interest. In other words, the ROLLUP and the CUBE groupings are shortcuts for common grouping-set use cases.

Referring to the preceding example, by adding a GROUPING SETS modifier to the group by clause you can compute rollup sales data on a quarter-by-year basis and a yearly basis only as shown in the following example.

-- rollup sales data on a quarter by year basis and a yearly basis only
SELECT
    sales.col1 AS year,
    sales.col2 AS quarter,
    SUM(sales.col3) AS amount
FROM VALUES
    (2017, 1 ,100),
    (2017, 1 ,50),
    (2017, 2 ,200),
    (2017, 2 ,300),
    (2018, 1 ,300),
    (2018, 1 ,100),
    (2018, 2 ,400) AS sales
GROUP BY col1, col2
GROUPING SETS ( (COL1), (COL1, COL2) )
ORDER BY year, quarter

The result of the example query is shown in the following table.

Inner join

An INNER join combines each row of the left table with each row of the right table, keeping only the rows in which the join condition is true.

-- inner join query
SELECT
    left_table.col1 AS l_col1,
    left_table.col2 AS l_col2,
    right_table.col1 AS r_col1,
    right_table.col2 AS r_col2
FROM
    VALUES (0, 10), (1, 11), (2, 12), (3,13), (4, 14), (5, 14) AS left_table
    INNER JOIN
    VALUES (0, 10), (2, 12), (4, 14), (6, 16) AS right_table
    ON left_table.col1 = right_table.col1

The result of the example query is shown in the following table.

Outer join

An OUTER join includes the rows that are produced by the inner join, plus the missing rows, depending on the type of outer join.

A LEFT OUTER or LEFT join includes the rows from the left table that were missing from the inner join.

-- left outer join query
SELECT
    left_table.col1 AS l_col1,
    left_table.col2 AS l_col2,
    right_table.col1 AS r_col1,
    right_table.col2 AS r_col2
FROM VALUES (0, 10), (1, 11), (2, 12), (3,13), (4, 14), (5, 14) AS left_table
LEFT OUTER JOIN
VALUES (0, 10), (2, 12), (4, 14), (6, 16) AS right_table
ON left_table.col1 = right_table.col1

The result of the example query is shown in the following table.

A RIGHT OUTER or RIGHT join includes the rows from the right table that were missing from the inner join.

-- right outer join query
SELECT
    left_table.col1 AS l_col1,
    left_table.col2 AS l_col2,
    right_table.col1 AS r_col1,
    right_table.col2 AS r_col2
FROM
    VALUES (0, 10), (1, 11), (2, 12), (3,13), (4, 14), (5, 14) AS left_table
    RIGHT OUTER JOIN
    VALUES (0, 10), (2, 12), (4, 14), (6, 16) AS right_table
    ON left_table.col1 = right_table.col1

The result of the example query is shown in the following table.

A FULL OUTER or FULL join includes the rows from both tables that were missing from the inner join.

-- full outer join query
SELECT
    left_table.col1 AS l_col1,
    left_table.col2 AS l_col2,
    right_table.col1 AS r_col1,
    right_table.col2 AS r_col2
FROM
    VALUES (0, 10), (1, 11), (2, 12), (3, 13), (4, 14), (5, 14) AS left_table
    FULL OUTER JOIN
    VALUES (0, 10), (2, 12), (4, 14), (6, 16) AS right_table
    ON left_table.col1 = right_table.col1

The result of the example query is shown in the following table.

Cross join

A CROSS join creates a Cartesian product of the tables that are involved in the join operation, if a CROSS join that specifies a join condition behaves like an inner join.

-- cross join that specifies a join condition

SELECT
    left_table.col1 AS l_col1,
    left_table.col2 AS l_col2,
    right_table.col1 AS r_col1,
    right_table.col2 AS r_col2
FROM
    VALUES (0, 10), (1, 11), (2, 12), (3,13), (4, 14), (5, 14) AS left_table
    CROSS JOIN VALUES (0, 10), (2, 12), (4, 14), (6, 16) AS right_table
    ON left_table.col1 = right_table.col1

The result of the example query is shown in the following table.

-- cross join that specifies no join condition
SELECT
    left_table.col1 AS l_col1,
    left_table.col2 AS l_col2,
    right_table.col1 AS r_col1,
    right_table.col2 AS r_col2
FROM
    VALUES (0, 10), (1, 11), (2, 12), (3,13), (4, 14), (5, 14) AS left_table
    CROSS JOIN VALUES (0, 10), (2, 12), (4, 14), (6, 16) AS right_table

The result of the example query is shown in the following table.

Anti join

A LEFT ANTI or ANTI join returns only rows from the left table that do not have a matching row in the right table. Columns from the right table cannot be included in the column list of the select statement.

-- left anti join query
SELECT
    left_table.col1 AS l_col1,
    left_table.col2 AS l_col2
FROM
    VALUES (0, 10), (1, 11), (2, 12), (3,13), (4, 14), (5, 14) AS left_table
    ANTI JOIN
    VALUES (0, 10), (2, 12), (4, 14), (6, 16) AS right_table
    ON left_table.col1 = right_table.col1

The result of the example query is shown in the following table.

Left semi join

A LEFT SEMI join acts like an inner join but does not include the columns of the right table.

-- left semi join query
SELECT
    left_table.col1 AS l_col1,
    left_table.col2 AS l_col2
FROM
    VALUES (0, 10), (1, 11), (2, 12), (3,13), (4, 14), (5, 14) AS left_table
    LEFT SEMI JOIN
    VALUES (0, 10), (2, 12), (4, 14), (6, 16) AS right_table
    ON left_table.col1 = right_table.col1

The result of the example query is shown in the following table.

Ranking function example

This example uses a table that contains information about employee social media activity (posts):

• Column 1: employee ID
• Column 2: department ID
• Column 3: number of social media posts

The example shows how to retrieve the ID of the two most active employees in each department.

-- derive posts ranking by using a named window specification
SELECT *
FROM (
    SELECT
        posts.col1 AS emp_id,
        posts.col2 AS dept_id,
        posts.col3 AS posts,
        DENSE_RANK() OVER post_ranking AS rank
    FROM VALUES
        (1, 1 ,100),
        (2, 1 ,50),
        (8, 1 ,250),
        (3, 2 ,200),
        (4, 2 ,300),
        (9, 2 ,1000),
        (5, 3 ,300),
        (6, 3 ,100),
        (7, 3 ,400) AS posts
    WINDOW post_ranking AS (
        PARTITION BY posts.col2
        ORDER BY posts.col3 DESC
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
)
WHERE rank <= 2

The result of the example query is shown in the following table.

-- derive posts ranking by using an inline window specification
SELECT * FROM (
    SELECT
        posts.col1 AS emp_id,
        posts.col2 AS dept_id,
        posts.col3 AS posts,
        DENSE_RANK() OVER (
                        PARTITION BY posts.col2
                        ORDER BY posts.col3 DESC
                        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
                        ) AS rank
        FROM VALUES
            (1, 1 ,100),
            (2, 1 ,50),
            (8, 1 ,250),
            (3, 2 ,200),
            (4, 2 ,300),
            (9, 2 ,1000),
            (5, 3 ,300),
            (6, 3 ,100),
            (7, 3 ,400) AS posts
) WHERE rank <= 2

The result of the example query is shown in the following table.

Analytic function example

This example uses a table that contains transaction information. The layout is as follows:

• Column 1: transaction ID
• Column 2: account number
• Column 3: transaction amount

The example shows how to create a cumulative distribution of transaction amounts by using the analytic function CUME_DIST(). The CUME_DIST() function returns the percentage of rows that have a value less than or equal to the current row’s value.

-- cumulative distribution of transaction amounts
SELECT
    txn_amount,
    MAX(balance_dist)
FROM (
        SELECT
            txn.col2 AS account,
            txn.col3 AS txn_amount,
            CUME_DIST() OVER current_balance AS balance_dist
        FROM VALUES
            (1, 42, 1000),
            (2, 4711, 2000),
            (3, 42, -200),
            (4, 42, -200),
            (5, 4711, 1000),
            (6, 4711, -300),
            (7, 4711, -300),
            (8, 42, 1000),
            (9, 4711, -400) AS txn
        WINDOW current_balance AS (ORDER BY txn.col3)
        ORDER BY txn.col3
)
GROUP BY txn_amount

The result of the example query is shown in the following table.

Aggregation Function Example

This example uses a table that contains transaction information. The layout is as follows:

• Column 1: transaction ID
• Column 2: account number
• Column 3: transaction amount

The example shows how to retrieve the total balance of each account at the time of each transaction.

--- total balance of each account at the time of each transaction
SELECT
    txn.col1 AS txn_id,
    txn.col2 AS account,
    txn.col3 AS txn_amount,
    SUM(txn.col3) OVER current_balance AS balance
FROM VALUES
    (1, 42, 100),
    (2, 4711, 300),
    (3, 42, 50),
    (4, 42, -250),
    (5, 4711, 1000),
    (6, 4711, -300),
    (7, 4711, 100),
    (8, 42, 200),
    (9, 4711, -400) AS txn
WINDOW current_balance AS (
        PARTITION BY txn.col2
        ORDER BY txn.col1
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) ORDER BY account, txn_id

The result of the example query is shown in the following table.

expression

More topics - Boolean expression

For more information, see booleanExpression.

Related references - expression

An expression is referenced by the following clauses:

• caseExpression
• castExpression
• frameBound
• fullselect
• functionOrAggregate
• groupByClause
• groupingSet
• lateralView
• predicate
• primaryExpression
• resultColumn
• sample
• sortItem
• tableValuedFunction
• valuesClause
• whenClause
• windowSpec

booleanExpression

A Boolean expression is one of the following:

• A negation of a Boolean expression by using Boolean operator NOT.
• A conjunction of Boolean expressions by using Boolean operator AND.
• A disjunction of Boolean expressions by using Boolean operator OR.
• An EXIST predicate.
• A value expression optionally followed by a predicate.

Refer to booleanOperator for details about Boolean operators NOT, AND, and OR.

The EXISTS predicate tests for the existence of certain rows. The query can specify any number of columns, and the following applies:

• The result is true only if the number of rows that is specified by the fullselect is not zero.
• The result is false only if the number of rows that is specified is zero.
• The result cannot be unknown.

Related references - Boolean expression

A Boolean expression is referenced by the following clauses:

• booleanExpression
• expression
• relation
• simpleselect

valueExpression

A value expression is one of the following:

• A primary expression.
• The result of applying an unary operator to a value expression.
• The result of performing an arithmetic operation with two value expressions.
• The result of performing a comparison operation between two value expressions.

More topics - value expression

For more information about the clauses that are used by a value expression, see the following topics:

• arithmeticOperator
• comparisonOperator
• primaryExpression
• unaryOperator

Related references - value expression

A value expression is referenced by the following clauses:

• booleanExpression
• functionOrAggregate
• predicate

primaryExpression

constant

interval

With an interval clause, you can define time duration constants that can be used in expressions to add or subtract time ranges from a timestamp value.

timeUnitSpec

The following time units are valid:

• Singular form: SECOND, MINUTE, DAY, MONTH, YEAR
• Plural form: SECONDS, MINUTES, DAYS, MONTHS, YEARS

Both singular and plural forms can be used interchangeably.

The following example demonstrates how to add and subtract several time units from the current timestamp.

-- add and subtract several time units from the current timestamp
SELECT
    CURRENT_TIMESTAMP
    - INTERVAL 2 YEARS
    + INTERVAL 1 MONTH
    - INTERVAL 3 DAYS
    + INTERVAL 10 HOURS
    + interval 30 MINUTES
    - INTERVAL 20 SECOND AS past_timestamp
FROM VALUES ("dummy")

The result of the example query is shown in the following table.

Since interval clauses can get long, especially if days, hours, minutes, and seconds are involved, it is possible to use an abbreviated syntax by specifying a format STRING and by using the TO keyword.

The format STRING can be used to specify the following time intervals:

• YEAR TO MONTH interval by using a format string that complies with signYEAR-MONTH with the following:
  • sign: optional + or - sign
  • YEAR: number of years
  • MONTH: number of months
• DAY TO SECOND interval by using a format string that complies with signDAY HOUR:MINUTE:SECOND with the following:
  • sign: optional + or - sign
  • DAY: number of days
  • HOUR: number of hours
  • MINUTE: number of minutes
  • SECOND: number of seconds

When you specify a time interval by using the keyword TO, only the singular form of a time unit identifier is supported.

The following table shows equivalent interval clauses.

The following example demonstrates equivalent interval expressions when you deal with YEAR and MONTH time units.

-- equivalent interval expressions with YEAR and MONTH time units
WITH ts AS (
    SELECT CURRENT_TIMESTAMP AS now FROM VALUES ('dummy')
    )
SELECT
    now - INTERVAL 1 YEAR - INTERVAL 2 MONTH AS LONG_VERSION,
    now - INTERVAL '1-2' YEAR TO MONTH  AS SHORT_VERSION
FROM ts

The result of the example query is shown in the following table.

The following example demonstrates equivalent interval expressions, when you deal with DAY, HOUR, MINUTE, and SECOND time units.

-- equivalent interval expressions when you deal with DAY, HOUR, MINUTE, and SECOND time units
WITH ts AS (
    SELECT CURRENT_TIMESTAMP AS now FROM VALUES ('dummy')
    )
SELECT
    now - INTERVAL 1 DAY - INTERVAL 2 HOURS - INTERVAL 3 MINUTES - INTERVAL 4 SECONDS AS LONG_VERSION,
    now - INTERVAL '1 2:3:4.100' DAY TO SECOND AS SHORT_VERSION
FROM ts

The result of the example query is shown in the following table.

columnReference

qualifiedName

A qualified name is a sequence of identifiers that are separated by .. For example, a column name can be qualified by the name of the relation the column is defined in.

Qualified names are available in the following context:

• functionOrAggregate
• lateralView
• sample

Related references - primary expression

For more information about the clauses that are used by a primary expression, see the following topics:

• caseExpression
• castExpression
• constant
• columnReference
• expression
• functionOrAggregate
• identifier
• number
• query
• STRING
• valueExpression
• timeSeriesExpression

predicate

The BETWEEN ... AND predicate compares a value with a range of values. If NOT is specified, the result is reversed.

The IN predicate compares a value or values with a collection of values. The range of values is either defined by a query or a list of expressions that are enclosed in parentheses. The query must identify a number of columns that are the same as the number of expressions that are specified to the left of the IN keyword. In addition, the number of elements in the list of expressions must be the same as the number of expressions that are specified to the left of the IN keyword. If NOT is specified, the result is reversed.

The LIKE predicate searches for strings that have a certain pattern. The pattern is specified by a string in which certain characters have a special meaning.

• The underscore character _ represents any single character.
• The percent sign % represents a string of zero or more characters.
• Any other character represents itself. Thus trailing blanks in a pattern are part of the pattern. If NOT is specified, the result is reversed.

The RLIKE predicate searches for a regular expression pattern in a string. If the pattern expression is found, the result is true. If the pattern expression is not found, the result is false. If the value of any of the arguments is null, the result of the RLIKE predicate is unknown. If NOT is specified, the result is reversed.

The regular expression pattern must be a Java™ regular expression as defined by Java class java.util.regex.Pattern. Meta characters that start with a \ must be escaped for the regular expression to work, for example, use \\d instead of \d in a pattern string to represent a digit. For more information, such as supported meta characters and predefined character classes, see the latest Java documentation.

The IS NULL predicate tests for null values. The result of a NULL predicate cannot be unknown. If the value of the expression is null, the result is true. If the value is not null, the result is false. If NOT is specified, the result is reversed.

The IS DISTINCT FROM predicate compares two expressions and evaluates to TRUE if their values are not identical. The result of a DISTINCT predicate cannot be null. If NOT is specified, the result is reversed. The result of a DISTINCT predicate depends on whether either or both of its input expressions are null:

The following DISTINCT predicates are logically equivalent to the corresponding search conditions:

Examples - predicate

-- select all rows with distinct values in column A and B
SELECT * FROM (
    SELECT
        col1 AS a,
        col2  AS b
    FROM VALUES
            (1 , 2),
            (2, 2),
            (null, 2),
            (1, null),
            (2, null),
            (null, null)
) WHERE a IS DISTINCT FROM b

-- all rows that have no distinct values in column A and B
SELECT * FROM (
    SELECT
        col1 AS a,
        col2 AS b
    FROM VALUES
            (1, 2),
            (2, 2),
            (null, 2),
            (1, null),
            (2, null),
            (null, null)
) WHERE a IS NOT DISTINCT FROM b

-- all employees with a salary between 4000 and 8000
SELECT
    emp.col1 AS emp_id,
    emp.col2 AS salary
FROM VALUES
    (0, 1000),
    (2, 2000),
    (3, 3000),
    (4, 4000),
    (5, 5000),
    (6, 6000),
    (7, null),
    (8, 8000),
    (9,9000) AS emp
WHERE emp.col2 BETWEEN 4000 AND 8000

-- all employees with a salary not between 4000 and 8000
SELECT
    emp.col1 AS emp_id,
    emp.col2 AS salary
FROM VALUES
    (0, 1000),
    (2, 2000),
    (3, 3000),
    (4, 4000),
    (5, 5000),
    (6, 6000),
    (7, null),
    (8, 8000),
    (9,9000) AS emp
WHERE emp.col2 NOT BETWEEN 4000 AND 8000

-- all employees working in department D01 or D02
SELECT
    emp.col1 AS emp_id,
    emp.col2 AS emp_dept
FROM VALUES
    (0, 'D01'),
    (2, 'C01'),
    (3, 'C02'),
    (4, 'D01'),
    (5, 'D02'),
    (6, 'C01'),
    (7, 'D01'),
    (8, 'C03'),
    (9,'D01') AS emp
WHERE emp.col2 IN ('D01','D02')

-- all employees that are managing a department
SELECT
    emp.col1 AS emp_id,
    emp.col2 AS emp_dept
FROM VALUES
    (0, 'D01'),
    (2, 'C01'),
    (3, 'C02'),
    (4, 'D01'),
    (5, 'D02'),
    (6, 'C01'),
    (7, 'D01'),
    (8, 'C03'),
    (9,'D01') AS emp
WHERE (emp.col1,emp.col2) IN (
    SELECT
        mgr.col1,
        mgr.col2
    FROM VALUES
        (2, 'C01'),
        (4, 'D01'),
        (5, 'D02') AS mgr
    )

-- all employees that work in a department that starts with letter C
SELECT
    emp.col1 AS emp_id,
    emp.col2 AS emp_dept
FROM VALUES
        (0, 'D01'),
        (2, 'C01'),
        (3, 'C02'),
        (4, 'D01'),
        (5, 'D02'),
        (6, 'C01'),
        (7, 'D01'),
        (8, 'C03'),
        (9, 'D01') AS emp
WHERE emp.col2 LIKE 'C%'

-- all department names that do not start with letter C
SELECT
    DISTINCT emp.col2 AS emp_dept
FROM VALUES
    (0, 'D01'),
    (2, 'C01'),
    (3, 'C02'),
    (4, 'D01'),
    (5, 'D02'),
    (6, 'C01'),
    (7, 'D01'),
    (8, 'C03'),
    (9,'D01') AS emp
WHERE emp.col2 NOT LIKE 'C%'

-- all rows that contain in col2 a value ending with 'bc'
SELECT *
FROM VALUES
    (0, 'Abc'),
    (1, 'xyz abc'),
    (2, 'abcabcabc'),
    (3, 'abc xyzxyz abc'),
    (4, '123 456 789') AS data
WHERE data.col2 RLIKE 'bc$'

-- all rows that contain in col2 a sequence of 3 'abc' string occurrences
SELECT *
FROM VALUES
    (0, 'Abc'),
    (1, 'xyz abc'),
    (2, 'abcabcabc'),
    (3, 'abc xyzxyz abc'),
    (4, '123 456 789') AS data
WHERE data.col2 RLIKE '(abc){3}'

-- all rows that contain in col2 a sequence of integer values (three digits) separated by blank or tab
SELECT *
FROM VALUES
        (0, 'Abc'),
        (1, 'xyz abc'),
        (2, 'abcabcabc'),
        (3, 'abc xyzxyz abc'),
        (4, '123\t456 789') AS data
WHERE data.col2 RLIKE '\\d{3}[ \\t]\\d{3}[ \\t]\\d{3}'

--- all employees with missing salary information
SELECT
    emp.col1 AS emp_id,
    emp.col2 AS salary
FROM VALUES
    (0, 1000),
    (2, 2000),
    (3, 3000),
    (4, 4000),
    (5, 5000),
    (6, 6000),
    (7, NULL),
    (8, 8000),
    (9,9000) AS emp
WHERE emp.col2 IS NULL

castExpression

In case an expression cannot be cast to the data type specified in the cast expression, the expression result is null.

More topics - cast expression

For more information about the clauses that are used by a cast expression, see the following topics:

• dataType
• expression
• identifier

Related references - cast expression

A cast expression is referenced by the following clause:

• primaryExpression

caseExpression

The upper path in the syntax diagram represents a searched when clause, which means that the WHEN keyword follows directly after the CASE keyword. The lower path is a simple when clause, which means that an expression follows the CASE keyword.

In general, the value of the case expression is the value of the result expression, following the first (leftmost) case that evaluates to true. If no case evaluates to true and the ELSE keyword is present, the result is the value of the ELSE case result expression. If no case evaluates to true and the ELSE keyword is not present, the result is NULL. When a case evaluates to unknown (because of NULLs), the case is not true, and hence is treated the same way as a case that evaluates to false.

When you use the simple when clause, the value of the expression before the first WHEN keyword is tested for equality with the value of the expression that follows the WHEN keyword. Therefore, the data type of the expression before the first WHEN keyword must be comparable to the data types of each expression that follows the WHEN keywords.

A result expression is an expression that follows the THEN or ELSE keywords.

whenClause

Examples - whenClause

-- simple case expression with no ELSE clause
SELECT
    dep.col1 AS dep_id,
    CASE dep.col2
        WHEN 'A' THEN 'Administration'
        WHEN 'B' THEN 'Human Resource'
        WHEN 'C' THEN 'Development'
    END AS dep_name
FROM VALUES (0, 'A'), (1, 'B'), (2, 'C'), (3, 'D'), (4, 'E') AS dep

The result of the example query is shown in the following table.

-- simple case expression with ELSE clause
SELECT
    dep.col1 AS dep_id,
    CASE dep.col2
        WHEN 'A' THEN 'Administration'
        WHEN 'B' THEN 'Human Resource'
        WHEN 'C' THEN 'Development'
        ELSE 'UNKOWN'
    END AS dep_name
FROM VALUES (0, 'A'), (1, 'B'), (2, 'C'), (3, 'D'), (4, 'E') AS dep

The result of the example query is shown in the following table.

The two scalar functions that are specialized to handle a subset of the functionality that is provided by CASE are NULLIF() and COALESCE().

For more information, see SQL functions.

For more information about the clauses that are used by a case expression, see the following topic:

• expression

Related references - case expression

A case expression is referenced by the following clause:

• primaryExpression

timeSeriesExpression

The syntax shows time series functions that require expressions, such as TS_MAP(), TS_FILTER(), TS_SEGMENT_BY_ANCHOR(), TS_SEGMENT_BY_MARKER(), TS_SEGMENT_BY_DUAL_MARKER(), TS_FIND(), and TS_COUNT_ANCHOR().

For more information about each function, see Data processing functions.

Example - time series

WITH timeseries_input AS (SELECT location, TIME_SERIES_WITH_TRS(TS_TIMESTAMP(timestamp), humidity, TS_TRS_DEFAULT()) AS ts
                          FROM cos://us-geo/sql/temperature_humidity.csv STORED AS CSV
                          GROUP BY location),
    only_40_or_above_ts AS (
	    SELECT location,
	   		TS_FILTER(ts, TS_EXP_GT(TS_EXP_ID(), 40.0)) AS above_40_ts
	    FROM timeseries_input
	)
SELECT location, TS_EXPLODE(above_40_ts) AS (timestamp, humidity) FROM only_40_or_above_ts

A time series expression is referenced by the following clause:

• primaryExpression

booleanTimeSeriesExpression

The Boolean time series expression syntax shows the available Boolean expresssions, such as TS_EXP_GT(), which is also used in the previous example.

For more information about each function, see Artifact creation functions.

valueTimeSeriesExpression

Time series values for expressions can either be a string or a double datatype.

doubleTimeSeriesExpression

The functions shown in the double time series expressions, such as TS_EXP_ABS() and TS_EXP_LENGTH(), are able to consume again double time series expressions, number, or an identity time series expression.

For more information about each function, see Artifact creation functions.

stringTimeSeriesExpression

The string function TS_EXP_ID_TO_STRING() converts an ID to a string and the TS_EXP_CONCAT() function concatenates the result of two string expressions.

For more information about each function, see Artifact creation functions.

stringConditionalExpression

The three conditional expression functions for string values are TS_EXP_IF_THEN_ELSE(), TS_EXP_IF_THEN(), and TS_EXP_MATCH_CASE().

For more information about each function, see Artifact creation functions.

identityTimeSeriesExpression

The identity expression denotes current observation values in time series.

createTable

columnDefinition

Create a table definition in the catalog based on the objects in the specified Object Storage location. The LOCATION option is mandatory. If a table or view with the same name exists in the same Data Engine instance, you receive an error, unless the IF NOT EXISTS clause is specified.

The column and partition definitions are optional. If they are not provided, the table schema and partitioning is detected from the structure of the data at the indicated location. If you explicitly provide these definitions, ensure that they match the objects that are stored in Object Storage. See data types for details on the supported column types.

-- create a definition for the table customer
CREATE TABLE customers (
    customerID string,
    companyName string,
    contactName string,
    contactTitle string,
    address string,
    city string,
    region string,
    postalCode string,
    country string,
    phone string,
    fax string
)
USING CSV
LOCATION  cos://us-geo/sql/customers.csv

Before you can use a newly created partitioned table, you must call ALTER TABLE tablename RECOVER PARTITIONS. Otherwise, querying the table returns an empty result.

-- create a definition for the table customers_partitioned
CREATE TABLE customers_partitioned (
    customerID string,
    companyName string,
    contactName string,
    contactTitle string,
    address string,
    city string,
    region string,
    postalCode string,
    country string,
    phone string,
    fax string
)
USING CSV
PARTITIONED BY (COUNTRY)
LOCATION cos://us-geo/sql/customers_partitioned.csv

-- attach table partitions by scanning the location of the table
ALTER TABLE customers_partitioned RECOVER PARTITIONS

An alternative way to create a table definition is to use the automatic schema detection where you do not need to specify any columns.

-- create a definition for the table shippers with automatic schema detection
CREATE TABLE shippers
USING parquet
LOCATION cos://us-geo/sql/shippers.parquet

tableProperty

The tableProperty option consists of one or more key and value pairs.

-- Example of creating a table definition in the catalog for a CSV data without header line:
CREATE TABLE LAX_CARGO
(Extract_Date timestamp, Report_Date timestamp, Arrival_Departure string,
Domestic_International string, Cargo_Type string, Air_Cargo_Tons int)
USING csv
LOCATION cos://us-geo/sql/LAX_Cargo.csv
options(HEADER=false)

When you create a JSON table, the query engine expects that the data contains only syntactically valid records and that all fields and sub-fields match the schema that you have declared for the table. Any access to invalid data causes the query to fail. You can opt for more relaxed processing by specifying mode='PERMISSIVE' as part of the table options. In permissive mode, invalid JSON data does not fail the query, instead one of the following happens.

• Fields or sub-fields that do not match the declared schema type are are dropped from the JSON records, so that their value becomes NULL.
• JSON records that contain a syntax error, such as a misplaced comma or quote, are converted to an empty record, so that all their fields become NULL.

Note that query input from a COS URI with STORED AS JSON format (where the schema is always inferred implicitly) is always processed in permissive mode. So, for COS URIs, invalid records are converted to NULLs. Only when you define a catalog table for more formalized access to the same data, the default handling changes to the stricter failfast mode, in order to avoid silent skipping of invalid data that might cause "data loss" problems. For a catalog table, you need to explicitly specify permissive mode to opt-in to relaxed processing that skips invalid data.

dropTable

Drop a table definition from the catalog. If the table does not exist, you receive an error, unless the IF EXISTS option is specified.

This command does not delete any data in Object Storage. It removes only the table definition from the catalog.

-- drop a definition for the table customer
DROP TABLE customers

createView

identifierComment

Create a view definition in the catalog, based on existing table and view definitions. If a table or view with the same name exists in the same Data Engine instance, you receive an error, unless the IF NOT EXISTS clause is specified.

The query definition is mandatory. It automatically specifies the SQL query that is used, whenever you use the view in a FROM clause of a query. You can hide some complexity of your data model by creating views on top of your tables. It is also possible to define views on top of other views.

-- create a view on top of table customer
CREATE VIEW CUSTOMER_STATISTICS AS
    SELECT country, region, count(*) customers
        FROM CUSTOMERS
        WHERE region is NOT NULL
        GROUP BY country, region

dropView

Drop a view definition from the catalog. If the view does not exist, you receive an error, unless the IF EXISTS option is specified.

Note: This command does not delete any data in Object Storage. It removes only the view definition from the catalog.

-- drop a view definition for the view customer_statistics
DROP VIEW customer_statistics

alterTablePartitions

Use alter table to modify the definition of the partitions or to automatically discover the available partitions.

Use the RECOVER PARTITIONS option to automatically replace the table partition metadata with the structure that is detected from Object Storage data by using the location prefix that is specified for the table.

-- replace the table partitions by scanning the location of the table
ALTER TABLE customers_partitioned RECOVER PARTITIONS

partitionSpecification

To add or remove partitions individually, use the ADD PARTITION or DROP PARTITION options.

With the ADD PARTITION option, you can specify an explicit location for the new partition. This way, you can construct a table from object locations that do not share a common Object Storage prefix, or are even located in separate buckets. If the partition location is not specified, it is inferred from the location of the table and the value(s) of the partitioning column(s). ADD PARTITION does not validate the specified or inferred location.

-- alter the table partitions by adding a partition
ALTER TABLE customers_partitioned ADD IF NOT EXISTS PARTITION ( COUNTRY = 'Spain') LOCATION cos://us-geo/sql/customers_partitioned.csv/COUNTRY=Spain
-- alter the table partitions by dropping a partition
ALTER TABLE customers_partitioned DROP IF EXISTS PARTITION ( COUNTRY = 'Nowhere')

The SET LOCATION option can be used to change the location of an existing partition.

-- modify the location of an existing partition
ALTER TABLE customers_partitioned PARTITION (country = 'Spain') SET LOCATION cos://eu-de/sql/customers_partitioned.csv/COUNTRY=Spain

Use the EXISTS option to avoid getting errors during ADD or DROP.

alterTableColumns

Use alter table to add new columns to the schema of a catalog table.

Adding columns to the schema has no effect on the objects in Object Storage for the table. If some or all of these objects do not contain a column, values for the corresponding rows are treated as NULL. You can add new objects to the table location (for nonpartitioned tables) or new partitions (for partitioned tables) that contain the new columns to evolve the table schema.

-- create a partitioned sample table in PARQUET format
CREATE TABLE customers_addcol USING PARQUET LOCATION cos://us-geo/sql/customers_partitioned.parquet
-- add a new column that does not yet occur in existing partitions. new partitions can add data for that column
ALTER TABLE customers_addcol ADD COLUMNS (priority INTEGER)

Do not use the ADD COLUMNS option with CSV tables. The CSV data format identifies columns by order (not by name), so any schema change leads to a schema mismatch with existing data.

Alternatively, you can perform schema changes by dropping and re-creating catalog tables. It does not affect the stored data in Object Storage. This allows you to reexecute the automatic schema detection when the underlying data is extended with new objects containing more columns. You can also use this method to remove columns from the schema that you do not want to appear in the catalog.

alterTableSetTableProperties

Use alter table set table properties to set new properties to the Hive table. The property is set in key value format. Currently, only the comment property is supported.

-- set property comment to the table CUSTOMERS_PARTITIONED
ALTER TABLE CUSTOMERS_PARTITIONED SET TBLPROPERTIES ('comment' = 'A table comment')

describeTable

Return the schema (column names and data types) of a table or view definition. If the table or view does not exist, you receive an error.

-- returns detailed information about the customer table
DESCRIBE TABLE customers_partitioned

showTables

Returns the list of the defined tables and views in the catalog. The LIKE option allows to filter for an indicated pattern. Use * as wildcard character.

-- returns all defined tables in the catalog for this instance
SHOW TABLES

showPartitions

List the defined partitions of a table when a table was created as partitioned. You can filter the returned partitions by using the partitionSpec option.

-- returns all partitions for the table customers_partitioned
SHOW PARTITIONS customers_partitioned

createIndex

Create an index on the objects in the specified Object Storage location or on the specified table. Define the required index type for each column that you want to calculate the summary metadata for. Create the index on columns that are used for predicates in the SQL statements.

metaindexIndextype

• MINMAX: Stores minimum or maximum values for a column for all types, except for complex types.
• VALUELIST: Stores the list of unique values for the column for all types if the distinct values in that column are low.
• BLOOMFILTER: Uses bloom filter technique for byte, string, long, integer, or short types if the distinct values in that column are high.
• GEOSPATIAL: Stores a geospatial bounding box for geometry types.

-- create an index on the columns temp, lat, lng, vid and city of the metergen sample table
CREATE METAINDEX
MINMAX FOR temp,
MINMAX FOR lat,
MINMAX FOR lng,
BLOOMFILTER FOR vid,
VALUELIST FOR city
ON cos://us-geo/sql/metergen STORED AS parquet

-- create an index on the columns customerID and city of the sample table CUSTOMERS_PARTITIONED
CREATE METAINDEX
VALUELIST for city,
BLOOMFILTER for customerID
ON TABLE CUSTOMERS_PARTITIONED

-- create a geospatial index on the column location of the hospitals sample table
CREATE METAINDEX
GEOSPATIAL FOR location
ON cos://us-geo/sql/hospitals_geometry.parquet STORED AS parquet

Before you start to use data skipping index management commands, ensure that you set the base location in Object Storage, where you want the metadata to be stored. Use the following command:

-- set the default location for all indexes
ALTER METAINDEX SET LOCATION cos://us-south/<mybucket>/<mypath>

dropIndex

Drop an existing index based on the objects in the specified Object Storage location or on the specified table. Use the following command when the index is no longer needed:

-- drop the index based on the metergen sample data set
DROP METAINDEX ON cos://us-geo/sql/metergen STORED AS parquet

refreshIndex

Refresh an existing index based on the objects in the specified Object Storage location or on the specified table. Use the following command if the data changed and you need to update the index:

-- refresh the index based on metergen sample data set
REFRESH METAINDEX ON cos://us-geo/sql/metergen STORED AS parquet

describeIndex

Describe an existing index based on the objects in the specified Object Storage location or on the specified table. Use the following command to receive information of the index, such as index status, types that are used, location where it is stored, or number of objects processed.

-- describe the index based on the metergen sample data set
DESCRIBE METAINDEX ON cos://us-geo/sql/metergen STORED AS parquet

showIndexes

List all stored indexes in the base location. Tables with a different index location are not displayed in the list.

-- list all Metaindexes in the base location
SHOW METAINDEXES

alterIndex

You must alter the Object Storage location for all indexes only once to define the base location. If you change it later, Data Engine cannot find the index metadata anymore. Existing index metadata on previous location is not dropped. Therefore, you can always switch back to the old location when needed.

-- set the default location for all indexes
ALTER METAINDEX SET LOCATION cos://us-south/<mybucket>/<mypath>/

alterTableSetLocation

With this command, you can define a location for this specified Hive table. If you change it later, Data Engine does not find the index metadata anymore. Existing index metadata on previous location is not dropped, therefore you can always switch back to the old location when needed.

-- set the index location for the table CUSTOMERS_PARTITIONED
ALTER TABLE CUSTOMERS_PARTITIONED SET METAINDEX LOCATION cos://us-south/<mybucket>/<mypath>

alterTableDropLocation

With this command, you can drop a location for the specified table. Use this command if the index metadata is to be fetched from the base location. The metadata for the index that is stored in Object Storage is not dropped and must be cleaned up manually.

-- set the index location for the table CUSTOMERS_PARTITIONED
ALTER TABLE CUSTOMERS_PARTITIONED DROP METAINDEX LOCATION

metaindexAsset

The indexAsset is either based on a table or Cloud Object Storage location.

The metaindexAssetLocation is a subset of the externalTableSpec.

The metaindexAssetHiveTable refers to a Hive table.

Unquoted identifier

An unquoted identifier is at least one character long. The following valid characters can be used:

• Digits 0-9
• Letters a-z, A-Z
• Underscore _

Back quoted identifier

It is an identifier that is embraced by grave accent ` characters. A back quoted identifier can contain any character. That includes the grave accent character that must be escaped like this ``.

The following example shows how to add a column name that contains a special character:

SELECT col1 as `Lösung` FROM VALUES 1, 2 ,3

Integer number

An integer number is represented by a sequence of at least one digit, thus 0 to 9. The integer number can have a suffix denoting the type of integer number. The three types of integer numbers are:

• Y: tiny integer number
• S: small integer number
• L: large integer number

For more information about data types, see dataType.

Decimal number

The following is a decimal number:

• A sequence of at least one digit followed by a positive or negative exponent, for example, 10E2 represents integer 1000 and 1E-1 represents 0.1.
• A decimal number, for example, 3.14.
• A decimal number followed by a positive or negative exponent, for example, 3.14E+3 represents 3140.00 or 3.14E-3 represents 0.00314.

The decimal number can have a suffix denoting the type of decimal number. The two types of decimal numbers are:

• D: double decimal number
• BD: large decimal number

For more information about data types, see dataType.