This is an automated email from the ASF dual-hosted git repository.
brile pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/druid.git
The following commit(s) were added to refs/heads/master by this push:
new 1aa6808b9a5 docs: add tutorial with examples of sql null handling
(#16185)
1aa6808b9a5 is described below
commit 1aa6808b9a56c4be5ee43e1da41395bdf85d7da1
Author: Charles Smith <techdocsm...@gmail.com>
AuthorDate: Mon Apr 1 11:03:42 2024 -0700
docs: add tutorial with examples of sql null handling (#16185)
Co-authored-by: 317brian <53799971+317br...@users.noreply.github.com>
---
docs/querying/sql-data-types.md | 50 ++++++---
docs/tutorials/tutorial-sql-null.md | 216 ++++++++++++++++++++++++++++++++++++
website/sidebars.json | 1 +
3 files changed, 251 insertions(+), 16 deletions(-)
diff --git a/docs/querying/sql-data-types.md b/docs/querying/sql-data-types.md
index ccc3dcf86ea..092e6823250 100644
--- a/docs/querying/sql-data-types.md
+++ b/docs/querying/sql-data-types.md
@@ -140,23 +140,41 @@ as regular JSON arrays instead of in stringified form.
## NULL values
+By default, Druid treats NULL values similarly to the ANSI SQL standard.
+In the default mode:
+- numeric NULL is permitted.
+- NULL values and empty strings are not equal.
+
+This manner of null handling applies to both storage and queries.
The
[`druid.generic.useDefaultValueForNull`](../configuration/index.md#sql-compatible-null-handling)
-runtime property controls Druid's NULL handling mode. For the most SQL
compliant behavior, set this to `false` (the default).
-
-When `druid.generic.useDefaultValueForNull = false` (the default), NULLs are
treated more closely to the SQL standard. In this mode,
-numeric NULL is permitted, and NULLs and empty strings are no longer treated
as interchangeable. This property
-affects both storage and querying, and must be set on all Druid service types
to be available at both ingestion time
-and query time. There is some overhead associated with the ability to handle
NULLs; see
-the [segment internals](../design/segments.md#handling-null-values)
documentation for more details.
-
-When `druid.generic.useDefaultValueForNull = true` (deprecated legacy mode),
Druid treats NULLs and empty strings
-interchangeably, rather than according to the SQL standard. In this mode Druid
SQL only has partial support for NULLs.
-For example, the expressions `col IS NULL` and `col = ''` are equivalent, and
both evaluate to true if `col` contains
-an empty string. Similarly, the expression `COALESCE(col1, col2)` returns
`col2` if `col1` is an empty string. While
-the `COUNT(*)` aggregator counts all rows, the `COUNT(expr)` aggregator counts
the number of rows where `expr` is
-neither null nor the empty string. Numeric columns in this mode are not
nullable; any null or missing values are
-treated as zeroes. This was the default prior to Druid 28.0.0, but will be
removed in a future release so that Druid
-always behaves in an SQL compatible manner.
+runtime property controls Druid's NULL handling mode. For the most SQL
compliant behavior, maintain the default value of `false`.
+
+There is some performance impact for null handling. see [segment
internals](../design/segments.md#handling-null-values) for more information.
+For examples of null handling, see the [null handling
tutorial](../tutorials/tutorial-sql-null.md).
+
+### Legacy null handling mode
+
+:::info
+To ensure Druid always behaves in an ANSI SQL compatible manner, this mode
will be removed in a future release.
+:::
+
+You can set `druid.generic.useDefaultValueForNull = true` to revert to Druid's
deprecated legacy null handling mode, the default for Druid 27.0.0 and prior
releases. This mode is not recommended.
+
+When running in the deprecated legacy mode, Druid treats NULL values and empty
strings interchangeably.
+In this mode:
+- Druid does not distinguish between empty strings and nulls.
+- Druid SQL only has partial support for NULLs.
+- Numeric columns are not nullable; null or missing values are treated as 0.
+
+For example, the following expressions are equivalent:
+
+- col IS NULL
+- col = ''
+
+Both evaluate to true if col contains an empty string.
+Similarly, the expression COALESCE(`col1`, `col2`) returns `col2` if `col1` is
an empty string.
+
+The COUNT(*) aggregator counts all rows but the COUNT(expr) aggregator counts
the number of rows where expr is neither null nor the empty string.
## Boolean logic
diff --git a/docs/tutorials/tutorial-sql-null.md
b/docs/tutorials/tutorial-sql-null.md
new file mode 100644
index 00000000000..555992368b0
--- /dev/null
+++ b/docs/tutorials/tutorial-sql-null.md
@@ -0,0 +1,216 @@
+---
+id: tutorial-sql-null
+title: Null handling tutorial
+sidebar_label: Handling null values
+description: Introduction to null handling in Druid
+---
+
+<!--
+ ~ Licensed to the Apache Software Foundation (ASF) under one
+ ~ or more contributor license agreements. See the NOTICE file
+ ~ distributed with this work for additional information
+ ~ regarding copyright ownership. The ASF licenses this file
+ ~ to you under the Apache License, Version 2.0 (the
+ ~ "License"); you may not use this file except in compliance
+ ~ with the License. You may obtain a copy of the License at
+ ~
+ ~ http://www.apache.org/licenses/LICENSE-2.0
+ ~
+ ~ Unless required by applicable law or agreed to in writing,
+ ~ software distributed under the License is distributed on an
+ ~ "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ ~ KIND, either express or implied. See the License for the
+ ~ specific language governing permissions and limitations
+ ~ under the License.
+ -->
+
+This tutorial introduces the basic concepts of null handling for string and
numeric columns in Apache Druid.
+The tutorial focuses on filters using the logical NOT operation on columns
with NULL values.
+
+## Prerequisites
+
+Before starting this tutorial, download and run Apache Druid on your local
machine as described in
+the [Local quickstart](index.md).
+
+The tutorial assumes you are familiar with using the [Query
view](./tutorial-sql-query-view.md) to ingest and query data.
+
+The tutorial also assumes you have not changed any of the default settings for
null handling.
+
+## Load data with null values
+
+The sample data for the tutorial contains null values for string and numeric
columns as follows:
+
+```json
+{"date": "1/1/2024 1:02:00","title": "example_1","string_value":
"some_value","numeric_value": 1}
+{"date": "1/1/2024 1:03:00","title": "example_2","string_value":
"another_value","numeric_value": 2}
+{"date": "1/1/2024 1:04:00","title": "example_3","string_value": "",
"numeric_value": null}
+{"date": "1/1/2024 1:05:00","title": "example_4","string_value": null,
"numeric_value": null}
+```
+
+Run the following query in the Druid Console to load the data:
+
+```sql
+REPLACE INTO "null_example" OVERWRITE ALL
+WITH "ext" AS (
+ SELECT *
+ FROM TABLE(
+ EXTERN(
+ '{"type":"inline","data":"{\"date\": \"1/1/2024 1:02:00\",\"title\":
\"example_1\",\"string_value\": \"some_value\",\"numeric_value\":
1}\n{\"date\": \"1/1/2024 1:03:00\",\"title\": \"example_2\",\"string_value\":
\"another_value\",\"numeric_value\": 2}\n{\"date\": \"1/1/2024
1:04:00\",\"title\": \"example_3\",\"string_value\": \"\", \"numeric_value\":
null}\n{\"date\": \"1/1/2024 1:05:00\",\"title\":
\"example_4\",\"string_value\": null, \"numeric_value\": null}"}',
+ '{"type":"json"}'
+ )
+ ) EXTEND ("date" VARCHAR, "title" VARCHAR, "string_value" VARCHAR,
"numeric_value" BIGINT)
+)
+SELECT
+ TIME_PARSE("date", 'd/M/yyyy H:mm:ss') AS "__time",
+ "title",
+ "string_value",
+ "numeric_value"
+FROM "ext"
+PARTITIONED BY DAY
+```
+
+After Druid finishes loading the data, run the following query to see the
table:
+
+```sql
+SELECT * FROM "null_example"
+```
+
+Druid returns the following:
+
+|`__time`|`title`|`string_value`|`numeric_value`|
+|---|---|---|---|
+|`2024-01-01T01:02:00.000Z`|`example_1`|`some_value`|1|
+|`2024-01-01T01:03:00.000Z`|`example_2`|`another_value`|2|
+|`2024-01-01T01:04:00.000Z`|`example_3`|`empty`|`null`|
+|`2024-01-01T01:05:00.000Z`|`example_4`|`null`|`null`|
+
+Note the difference in the empty string value for example 3 and the null
string value for example 4.
+
+## String query example
+
+The queries in this section illustrate null handling with strings.
+The following query filters rows where the string value is not equal to
`some_value`:
+
+```sql
+SELECT COUNT(*)
+FROM "null_example"
+WHERE "string_value" != 'some_value'
+```
+
+Druid returns 2 for `another_value` and the empty string `""`. The null value
is not counted.
+
+Note that the null value is included in `COUNT(*)` but not as a count of the
values in the column as follows:
+
+```sql
+SELECT "string_value",
+ COUNT(*) AS count_all_rows,
+ COUNT("string_value") AS count_values
+FROM "inline_data"
+GROUP BY 1
+```
+
+Druid returns the following:
+
+|`string_value`|`count_all_rows`|`count_values`|
+|---|---|---|
+|`null`|1|0|
+|`empty`|1|1|
+|`another_value`|1|1|
+|`some_value`|1|1|
+
+Also note that GROUP BY expressions yields distinct entries for `null` and the
empty string.
+
+### Filter for empty strings in addition to null
+
+If your queries rely on treating empty strings and null values the same, you
can use an OR operator in the filter. For example to select all rows with null
values or empty strings:
+
+```sql
+SELECT *
+FROM "null_example"
+WHERE "string_value" IS NULL OR "string_value" = ''
+```
+
+Druid returns the following:
+
+|`__time`|`title`|`string_value`|`numeric_value`|
+|---|---|---|---|---|---|
+|`2024-01-01T01:04:00.000Z`|`example_3`|`empty`|`null`|
+|`2024-01-01T01:05:00.000Z`|`example_4`|`null`|`null`|
+
+For another example, if you do not want to count empty strings, use a FILTER
to exclude them. For example:
+
+```sql
+SELECT COUNT("string_value") FILTER(WHERE "string_value" <> '')
+FROM "null_example"
+```
+
+Druid returns 2. Both the empty string and null values are excluded.
+
+## Numeric query examples
+
+Druid does does not count null values in numeric comparisons.
+
+```sql
+SELECT COUNT(*)
+FROM "null_example"
+WHERE "numeric_value" < 2
+```
+
+Druid returns 1. The `null` values for examples 3 and 4 are excluded.
+
+Additionally, be aware that null values do not behave as 0. For examples:
+
+```sql
+SELECT numeric_value + 1
+FROM "null_example"
+WHERE "__time" > '2024-01-01 01:04:00.000Z'
+```
+
+Druid returns `null` and not 1. One option is to use the COALESCE function for
null handling. For example:
+
+```sql
+SELECT COALESCE(numeric_value, 0) + 1
+FROM "null_example"
+WHERE "__time" > '2024-01-01 01:04:00.000Z'
+```
+
+In this case, Druid returns 1.
+
+## Ingestion time filtering
+
+The same null handling rules apply at ingestion time.
+The following query replaces the example data with data filtered with a WHERE
clause:
+
+```sql
+REPLACE INTO "null_example" OVERWRITE ALL
+WITH "ext" AS (
+ SELECT *
+ FROM TABLE(
+ EXTERN(
+ '{"type":"inline","data":"{\"date\": \"1/1/2024 1:02:00\",\"title\":
\"example_1\",\"string_value\": \"some_value\",\"numeric_value\":
1}\n{\"date\": \"1/1/2024 1:03:00\",\"title\": \"example_2\",\"string_value\":
\"another_value\",\"numeric_value\": 2}\n{\"date\": \"1/1/2024
1:04:00\",\"title\": \"example_3\",\"string_value\": \"\", \"numeric_value\":
null}\n{\"date\": \"1/1/2024 1:05:00\",\"title\":
\"example_4\",\"string_value\": null, \"numeric_value\": null}"}',
+ '{"type":"json"}'
+ )
+ ) EXTEND ("date" VARCHAR, "title" VARCHAR, "string_value" VARCHAR,
"numeric_value" BIGINT)
+)
+SELECT
+ TIME_PARSE("date", 'd/M/yyyy H:mm:ss') AS "__time",
+ "title",
+ "string_value",
+ "numeric_value"
+FROM "ext"
+WHERE "string_value" != 'some_value'
+PARTITIONED BY DAY
+```
+
+The resulting data set only includes two rows. Druid has filtered out example
1 (`some_value`) and example 4 (`null`):
+
+|`__time`|`title`|`string_value`|`numeric_value`|
+|---|---|---|---|
+|`2024-01-01T01:03:00.000Z`|`example_2`|`another_value`|2|
+|`2024-01-01T01:04:00.000Z`|`example_3`|`empty`|`null`|
+
+## Learn more
+
+See the following for more information:
+- [Null values](../querying/sql-data-types.md#null-values)
+- "Generating and working with NULL values" notebook at [Learn
druid](https://github.com/implydata/learn-druid/)
diff --git a/website/sidebars.json b/website/sidebars.json
index 13054218a1d..f5407587a15 100644
--- a/website/sidebars.json
+++ b/website/sidebars.json
@@ -24,6 +24,7 @@
"tutorials/docker",
"tutorials/tutorial-kerberos-hadoop",
"tutorials/tutorial-sql-query-view",
+ "tutorials/tutorial-sql-null",
"tutorials/tutorial-unnest-arrays",
"tutorials/tutorial-query-deep-storage",
"tutorials/tutorial-jdbc"
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscr...@druid.apache.org
For additional commands, e-mail: commits-h...@druid.apache.org
