Skip to content

Commit da29166

Browse files
alexey-milovidovalexey-milovidov
authored
Merge branch 'master' into fix-unexpected-ast-set
2 parents eb8629d + 9f45513 commit da29166

File tree

65 files changed

+1725
-556
lines changed

Some content is hidden

Large Commits have some content hidden by default. Use the searchbox below for content that may be hidden.

65 files changed

+1725
-556
lines changed

‎docs/en/operations/server-configuration-parameters/settings.md‎

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1,11 +1,11 @@
11
---
22
slug: /en/operations/server-configuration-parameters/settings
33
sidebar_position: 57
4-
sidebar_label: Server Settings
4+
sidebar_label: Global Server Settings
55
description: This section contains descriptions of server settings that cannot be changed at the session or query level.
66
---
77

8-
# Server Settings
8+
# Global Server Settings
99

1010
This section contains descriptions of server settings that cannot be changed at the session or query level.
1111

‎docs/en/operations/settings/index.md‎

Lines changed: 8 additions & 82 deletions
Original file line numberDiff line numberDiff line change
@@ -7,90 +7,16 @@ pagination_next: en/operations/settings/settings
77

88
# Settings Overview
99

10-
There are multiple ways to define ClickHouse settings. Settings are configured in layers, and each subsequent layer redefines the previous values of a setting.
10+
There are two main groups of ClickHouse settings:
1111

12-
The order of priority for defining a setting is:
12+
- Global server settings
13+
- Query-level settings
1314

14-
1. Settings in the `users.xml` server configuration file
15+
The main distinction between global server settings and query-level settings is that
16+
global server settings must be set in configuration files while query-level settings
17+
can be set in configuration files or with SQL queries.
1518

16-
- Set in the element `<profiles>`.
19+
Read about [global server settings](/docs/en/operations/server-configuration-parameters/settings.md) to learn more about configuring your ClickHouse server at the global server level.
1720

18-
2. Session settings
21+
Read about [query-level settings](/docs/en/operations/settings/settings-query-level.md) to learn more about configuring your ClickHouse server at the query-level.
1922

20-
- Send `SET setting=value` from the ClickHouse console client in interactive mode.
21-
Similarly, you can use ClickHouse sessions in the HTTP protocol. To do this, you need to specify the `session_id` HTTP parameter.
22-
23-
3. Query settings
24-
25-
- When starting the ClickHouse console client in non-interactive mode, set the startup parameter `--setting=value`.
26-
- When using the HTTP API, pass CGI parameters (`URL?setting_1=value&setting_2=value...`).
27-
- Define settings in the [SETTINGS](../../sql-reference/statements/select/index.md#settings-in-select-query) clause of the SELECT query. The setting value is applied only to that query and is reset to the default or previous value after the query is executed.
28-
29-
View the [Settings](./settings.md) page for a description of the ClickHouse settings.
30-
31-
## Converting a Setting to its Default Value
32-
33-
If you change a setting and would like to revert it back to its default value, set the value to `DEFAULT`. The syntax looks like:
34-
35-
```sql
36-
SET setting_name = DEFAULT
37-
```
38-
39-
For example, the default value of `max_insert_block_size` is 1048449. Suppose you change its value to 100000:
40-
41-
```sql
42-
SET max_insert_block_size=100000;
43-
44-
SELECT value FROM system.settings where name='max_insert_block_size';
45-
```
46-
47-
The response is:
48-
49-
```response
50-
┌─value──┐
51-
│ 100000 │
52-
└────────┘
53-
```
54-
55-
The following command sets its value back to 1048449:
56-
57-
```sql
58-
SET max_insert_block_size=DEFAULT;
59-
60-
SELECT value FROM system.settings where name='max_insert_block_size';
61-
```
62-
63-
The setting is now back to its default:
64-
65-
```response
66-
┌─value───┐
67-
│ 1048449 │
68-
└─────────┘
69-
```
70-
71-
72-
## Custom Settings {#custom_settings}
73-
74-
In addition to the common [settings](../../operations/settings/settings.md), users can define custom settings.
75-
76-
A custom setting name must begin with one of predefined prefixes. The list of these prefixes must be declared in the [custom_settings_prefixes](../../operations/server-configuration-parameters/settings.md#custom_settings_prefixes) parameter in the server configuration file.
77-
78-
```xml
79-
<custom_settings_prefixes>custom_</custom_settings_prefixes>
80-
```
81-
82-
To define a custom setting use `SET` command:
83-
84-
```sql
85-
SET custom_a = 123;
86-
```
87-
88-
To get the current value of a custom setting use `getSetting()` function:
89-
90-
```sql
91-
SELECT getSetting('custom_a');
92-
```
93-
94-
**See Also**
95-
96-
- [Server Configuration Settings](../../operations/server-configuration-parameters/settings.md)

‎docs/en/operations/settings/settings-query-level.md‎

Lines changed: 217 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,217 @@
1+
---
2+
sidebar_label: Query-level Settings
3+
title: Query-level Settings
4+
slug: /en/operations/settings/query-level
5+
---
6+
7+
There are multiple ways to set ClickHouse query-level settings. Settings are configured in layers, and each subsequent layer redefines the previous values of a setting.
8+
9+
The order of priority for defining a setting is:
10+
11+
1. Applying a setting to a user directly, or within a settings profile
12+
13+
- SQL (recommended)
14+
- adding one or more XML or YAML files to `/etc/clickhouse-server/users.d`
15+
16+
2. Session settings
17+
18+
- Send `SET setting=value` from the ClickHouse Cloud SQL console or
19+
`clickhouse client` in interactive mode. Similarly, you can use ClickHouse
20+
sessions in the HTTP protocol. To do this, you need to specify the
21+
`session_id` HTTP parameter.
22+
23+
3. Query settings
24+
25+
- When starting `clickhouse client` in non-interactive mode, set the startup
26+
parameter `--setting=value`.
27+
- When using the HTTP API, pass CGI parameters (`URL?setting_1=value&setting_2=value...`).
28+
- Define settings in the
29+
[SETTINGS](../../sql-reference/statements/select/index.md#settings-in-select-query)
30+
clause of the SELECT query. The setting value is applied only to that query
31+
and is reset to the default or previous value after the query is executed.
32+
33+
## Examples
34+
35+
These examples all set the value of the `async_insert` setting to `1`, and
36+
show how to examine the settings in a running system.
37+
38+
### Using SQL to apply a setting to a user directly
39+
40+
This creates the user `ingester` with the setting `async_inset = 1`:
41+
42+
```sql
43+
CREATE USER ingester
44+
IDENTIFIED WITH sha256_hash BY '7e099f39b84ea79559b3e85ea046804e63725fd1f46b37f281276aae20f86dc3'
45+
# highlight-next-line
46+
SETTINGS async_insert = 1
47+
```
48+
49+
#### Examine the settings profile and assignment
50+
51+
```sql
52+
SHOW ACCESS
53+
```
54+
55+
```response
56+
┌─ACCESS─────────────────────────────────────────────────────────────────────────────┐
57+
│ ... │
58+
# highlight-next-line
59+
│ CREATE USER ingester IDENTIFIED WITH sha256_password SETTINGS async_insert = true │
60+
│ ... │
61+
└────────────────────────────────────────────────────────────────────────────────────┘
62+
```
63+
### Using SQL to create a settings profile and assign to a user
64+
65+
This creates the profile `log_ingest` with the setting `async_inset = 1`:
66+
67+
```sql
68+
CREATE
69+
SETTINGS PROFILE log_ingest SETTINGS async_insert = 1
70+
```
71+
72+
This creates the user `ingester` and assigns the user the settings profile `log_ingest`:
73+
74+
```sql
75+
CREATE USER ingester
76+
IDENTIFIED WITH sha256_hash BY '7e099f39b84ea79559b3e85ea046804e63725fd1f46b37f281276aae20f86dc3'
77+
# highlight-next-line
78+
SETTINGS PROFILE log_ingest
79+
```
80+
81+
82+
### Using XML to create a settings profile and user
83+
84+
```xml title=/etc/clickhouse-server/users.d/users.xml
85+
<clickhouse>
86+
# highlight-start
87+
<profiles>
88+
<log_ingest>
89+
<async_insert>1</async_insert>
90+
</log_ingest>
91+
</profiles>
92+
# highlight-end
93+
94+
<users>
95+
<ingester>
96+
<password_sha256_hex>7e099f39b84ea79559b3e85ea046804e63725fd1f46b37f281276aae20f86dc3</password_sha256_hex>
97+
# highlight-start
98+
<profile>log_ingest</profile>
99+
# highlight-end
100+
</ingester>
101+
<default replace="true">
102+
<password_sha256_hex>7e099f39b84ea79559b3e85ea046804e63725fd1f46b37f281276aae20f86dc3</password_sha256_hex>
103+
<access_management>1</access_management>
104+
<named_collection_control>1</named_collection_control>
105+
</default>
106+
</users>
107+
</clickhouse>
108+
```
109+
110+
#### Examine the settings profile and assignment
111+
112+
```sql
113+
SHOW ACCESS
114+
```
115+
116+
```response
117+
┌─ACCESS─────────────────────────────────────────────────────────────────────────────┐
118+
│ CREATE USER default IDENTIFIED WITH sha256_password │
119+
# highlight-next-line
120+
│ CREATE USER ingester IDENTIFIED WITH sha256_password SETTINGS PROFILE log_ingest │
121+
│ CREATE SETTINGS PROFILE default │
122+
# highlight-next-line
123+
│ CREATE SETTINGS PROFILE log_ingest SETTINGS async_insert = true │
124+
│ CREATE SETTINGS PROFILE readonly SETTINGS readonly = 1 │
125+
│ ... │
126+
└────────────────────────────────────────────────────────────────────────────────────┘
127+
```
128+
129+
### Assign a setting to a session
130+
131+
```sql
132+
SET async_insert =1;
133+
SELECT value FROM system.settings where name='async_insert';
134+
```
135+
136+
```response
137+
┌─value──┐
138+
│ 1 │
139+
└────────┘
140+
```
141+
142+
### Assign a setting during a query
143+
144+
```sql
145+
INSERT INTO YourTable
146+
# highlight-next-line
147+
SETTINGS async_insert=1
148+
VALUES (...)
149+
```
150+
151+
152+
## Converting a Setting to its Default Value
153+
154+
If you change a setting and would like to revert it back to its default value, set the value to `DEFAULT`. The syntax looks like:
155+
156+
```sql
157+
SET setting_name = DEFAULT
158+
```
159+
160+
For example, the default value of `async_insert` is `0`. Suppose you change its value to `1`:
161+
162+
```sql
163+
SET async_insert = 1;
164+
165+
SELECT value FROM system.settings where name='async_insert';
166+
```
167+
168+
The response is:
169+
170+
```response
171+
┌─value──┐
172+
│ 1 │
173+
└────────┘
174+
```
175+
176+
The following command sets its value back to 0:
177+
178+
```sql
179+
SET async_insert = DEFAULT;
180+
181+
SELECT value FROM system.settings where name='async_insert';
182+
```
183+
184+
The setting is now back to its default:
185+
186+
```response
187+
┌─value───┐
188+
│ 0 │
189+
└─────────┘
190+
```
191+
192+
## Custom Settings {#custom_settings}
193+
194+
In addition to the common [settings](../../operations/settings/settings.md), users can define custom settings.
195+
196+
A custom setting name must begin with one of predefined prefixes. The list of these prefixes must be declared in the [custom_settings_prefixes](../../operations/server-configuration-parameters/settings.md#custom_settings_prefixes) parameter in the server configuration file.
197+
198+
```xml
199+
<custom_settings_prefixes>custom_</custom_settings_prefixes>
200+
```
201+
202+
To define a custom setting use `SET` command:
203+
204+
```sql
205+
SET custom_a = 123;
206+
```
207+
208+
To get the current value of a custom setting use `getSetting()` function:
209+
210+
```sql
211+
SELECT getSetting('custom_a');
212+
```
213+
214+
**See Also**
215+
216+
- View the [Settings](./settings.md) page for a description of the ClickHouse settings.
217+
- [Global server settings](../../operations/server-configuration-parameters/settings.md)

‎docs/en/operations/system-tables/parts.md‎

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -39,6 +39,8 @@ Columns:
3939

4040
- `data_uncompressed_bytes` ([UInt64](../../sql-reference/data-types/int-uint.md)) – Total size of uncompressed data in the data part. All the auxiliary files (for example, files with marks) are not included.
4141

42+
- `primary_key_size` ([UInt64](../../sql-reference/data-types/int-uint.md)) – The amount of memory (in bytes) used by primary key values in the primary.idx/cidx file on disk.
43+
4244
- `marks_bytes` ([UInt64](../../sql-reference/data-types/int-uint.md)) – The size of the file with marks.
4345

4446
- `secondary_indices_compressed_bytes` ([UInt64](../../sql-reference/data-types/int-uint.md)) – Total size of compressed data for secondary indices in the data part. All the auxiliary files (for example, files with marks) are not included.

‎docs/en/sql-reference/functions/array-functions.md‎

Lines changed: 22 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -102,6 +102,8 @@ The function also works for strings.
102102

103103
Can be optimized by enabling the [optimize_functions_to_subcolumns](../../operations/settings/settings.md#optimize-functions-to-subcolumns) setting. With `optimize_functions_to_subcolumns = 1` the function reads only [size0](../../sql-reference/data-types/array.md#array-size) subcolumn instead of reading and processing the whole array column. The query `SELECT length(arr) FROM table` transforms to `SELECT arr.size0 FROM TABLE`.
104104

105+
Alias: `OCTET_LENGTH`
106+
105107
## emptyArrayUInt8, emptyArrayUInt16, emptyArrayUInt32, emptyArrayUInt64
106108

107109
## emptyArrayInt8, emptyArrayInt16, emptyArrayInt32, emptyArrayInt64
@@ -142,6 +144,7 @@ range([start, ] end [, step])
142144

143145
- All arguments `start`, `end`, `step` must be below data types: `UInt8`, `UInt16`, `UInt32`, `UInt64`,`Int8`, `Int16`, `Int32`, `Int64`, as well as elements of the returned array, which's type is a super type of all arguments.
144146
- An exception is thrown if query results in arrays with a total length of more than number of elements specified by the [function_range_max_elements_in_block](../../operations/settings/settings.md#settings-function_range_max_elements_in_block) setting.
147+
- Returns Null if any argument has Nullable(Nothing) type. An exception is thrown if any argument has Null value (Nullable(T) type).
145148

146149
**Examples**
147150

@@ -878,7 +881,7 @@ A special function. See the section [“ArrayJoin function”](../../sql-referen
878881

879882
## arrayDifference
880883

881-
Calculates an array of differences between adjacent array elements. The first element of the result array will be 0, the second `a[1] - a[0]`, the third `a[2] - a[1]`, etc. The type of elements in the result array is determined by the type inference rules for subtraction (e.g. `UInt8` - `UInt8` = `Int16`).
884+
Calculates an array of differences between adjacent array elements. The first element of the result array will be 0, the second `a[1] - a[0]`, the third `a[2] - a[1]`, etc. The type of elements in the result array is determined by the type inference rules for subtraction (e.g. `UInt8` - `UInt8` = `Int16`).
882885

883886
**Syntax**
884887

@@ -996,6 +999,24 @@ SELECT
996999
└──────────────┴───────────┘
9971000
```
9981001

1002+
## arrayJaccardIndex
1003+
1004+
Returns the [Jaccard index](https://en.wikipedia.org/wiki/Jaccard_index) of two arrays.
1005+
1006+
**Example**
1007+
1008+
Query:
1009+
``` sql
1010+
SELECT arrayJaccardIndex([1, 2], [2, 3]) AS res
1011+
```
1012+
1013+
Result:
1014+
``` text
1015+
┌─res────────────────┐
1016+
│ 0.3333333333333333 │
1017+
└────────────────────┘
1018+
```
1019+
9991020
## arrayReduce
10001021

10011022
Applies an aggregate function to array elements and returns its result. The name of the aggregation function is passed as a string in single quotes `'max'`, `'sum'`. When using parametric aggregate functions, the parameter is indicated after the function name in parentheses `'uniqUpTo(6)'`.

0 commit comments

Comments
 (0)