Важно: Данный раздел актуален для Платформы данных в Публичном облаке и On-Premise.
Функция поддерживает шифрование данных следующими режимами:
Синтаксис:
encrypt('mode', 'plaintext', 'key' [, iv, aad])Аргументы:
mode — режим шифрования. String.plaintext — текст, который будет зашифрован. String.key — ключ шифрования. String.iv — инициализирующий вектор. Обязателен для -gcm режимов, для остальных режимов необязателен. String.aad — дополнительные аутентифицированные данные. Не шифруются, но влияют на расшифровку. Параметр работает только с -gcm режимами. Для остальных вызовет исключение. String.Возвращаемое значение:
Примеры:
Создадим такую таблицу:
Запрос:
CREATE TABLE encryption_test
(
`comment` String,
`secret` String
)
ENGINE = Memory;Вставим некоторые данные (замечание: не храните ключи или инициализирующие векторы в базе данных, так как это компрометирует всю концепцию шифрования), также хранение "подсказок" небезопасно и используется только для наглядности:
Запрос:
INSERT INTO encryption_test VALUES('aes-256-ofb no IV', encrypt('aes-256-ofb', 'Secret', '12345678910121314151617181920212')),\
('aes-256-ofb no IV, different key', encrypt('aes-256-ofb', 'Secret', 'keykeykeykeykeykeykeykeykeykeyke')),\
('aes-256-ofb with IV', encrypt('aes-256-ofb', 'Secret', '12345678910121314151617181920212', 'iviviviviviviviv')),\
('aes-256-cbc no IV', encrypt('aes-256-cbc', 'Secret', '12345678910121314151617181920212'));Запрос:
SELECT comment, hex(secret) FROM encryption_test;Результат:
┌─comment────────────────────────────┬─hex(secret)────────────────────────┐
│ aes-256-ofb no IV │ B4972BDC4459 │
│ aes-256-ofb no IV, different key │ 2FF57C092DC9 │
│ aes-256-ofb with IV │ 5E6CB398F653 │
│ aes-256-cbc no IV │ 1BC0629A92450D9E73A00E7D02CF4142 │
└─────────────────────────────────────┴─────────────────────────────────────┘Пример в режиме -gcm:
Запрос:
INSERT INTO encryption_test VALUES('aes-256-gcm', encrypt('aes-256-gcm', 'Secret', '12345678910121314151617181920212', 'iviviviviviviviv')), \
('aes-256-gcm with AAD', encrypt('aes-256-gcm', 'Secret', '12345678910121314151617181920212', 'iviviviviviviviv', 'aad'));
SELECT comment, hex(secret) FROM encryption_test WHERE comment LIKE '%gcm%';Результат:
┌─comment───────────────┬─hex(secret)─────────────────────────────────────┐
│ aes-256-gcm │ A8A3CCBC6426CFEEB60E4EAE03D3E94204C1B09E0254 │
│ aes-256-gcm with AAD │ A8A3CCBC6426D9A1017A0A932322F1852260A4AD6837 │
└────────────────────────┴──────────────────────────────────────────────────┘Совместима с шифрованием myqsl, результат может быть расшифрован функцией AES_DECRYPT.
При одинаковых входящих значениях зашифрованный текст будет совпадать с результатом, возвращаемым функцией encrypt. Однако если key или iv длиннее, чем должны быть, aes_encrypt_mysql будет работать аналогично функции aes_encrypt в MySQL: свернет ключ и проигнорирует лишнюю часть iv.
Функция поддерживает шифрование данных следующими режимами:
Синтаксис:
aes_encrypt_mysql('mode', 'plaintext', 'key' [, iv])Аргументы:
mode — режим шифрования. String.plaintext — текст, который будет зашифрован. String.key — ключ шифрования. Если ключ длиннее, чем требует режим шифрования, производится специфичная для MySQL свертка ключа. String.iv — инициализирующий вектор. Необязателен, учитываются только первые 16 байтов. String.Возвращаемое значение:
Примеры:
При одинаковых входящих значениях результаты шифрования у функций encrypt и aes_encrypt_mysql совпадают.
Запрос:
SELECT encrypt('aes-256-ofb', 'Secret', '12345678910121314151617181920212', 'iviviviviviviviv') =
aes_encrypt_mysql('aes-256-ofb', 'Secret', '12345678910121314151617181920212', 'iviviviviviviviv') AS ciphertexts_equal;Результат:
┌─ciphertexts_equal─┐
│ 1 │
└─────────────────────┘Функция encrypt генерирует исключение, если key или iv длиннее чем нужно:
Запрос:
SELECT encrypt('aes-256-ofb', 'Secret', '123456789101213141516171819202122', 'iviviviviviviviv123');Результат:
Received exception from server (version 21.1.2):
Code: 36. DB::Exception: Received from localhost:9000. DB::Exception:
Invalid key size: 33 expected 32: While processing encrypt('aes-256-ofb', 'Secret', '123456789101213141516171819202122', 'iviviviviviviviv123').Однако функция aes_encrypt_mysql в аналогичном случае возвращает результат, который может быть обработан MySQL:
Запрос:
SELECT hex(aes_encrypt_mysql('aes-256-ofb', 'Secret', '123456789101213141516171819202122', 'iviviviviviviviv123')) AS ciphertext;Результат:
┌─ciphertext───┐
│ 24E9E4966469 │
└───────────────┘Если передать iv еще длиннее, результат останется таким же:
Запрос:
SELECT hex(aes_encrypt_mysql('aes-256-ofb', 'Secret', '123456789101213141516171819202122', 'iviviviviviviviv123456')) AS ciphertextРезультат:
┌─ciphertext───┐
│ 24E9E4966469 │
└───────────────┘Это совпадает с результатом, возвращаемым MySQL при таких же входящих значениях:
mysql> SET block_encryption_mode='aes-256-ofb';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT aes_encrypt('Secret', '123456789101213141516171819202122', 'iviviviviviviviv123456') as ciphertext;
+------------------------+
| ciphertext |
+------------------------+
| 0x24E9E4966469 |
+------------------------+
1 row in set (0.00 sec)Функция расшифровывает зашифрованный текст и может работать в следующих режимах:
Синтаксис:
decrypt('mode', 'ciphertext', 'key' [, iv, aad])Аргументы:
mode — режим шифрования. String.ciphertext — зашифрованный текст, который будет расшифрован. String.key — ключ шифрования. String.iv — инициализирующий вектор. Обязателен для -gcm режимов, для остальных режимов опциональный. String.aad — дополнительные аутентифицированные данные. Текст не будет расшифрован, если это значение неверно. Работает только с -gcm режимами. Для остальных вызовет исключение. String.Возвращаемое значение:
Примеры:
Рассмотрим таблицу из примера для функции encrypt.
Запрос:
SELECT comment, hex(secret) FROM encryption_test;Результат:
┌─comment───────────────┬─hex(secret)─────────────────────────────────────┐
│ aes-256-gcm │ A8A3CCBC6426CFEEB60E4EAE03D3E94204C1B09E0254 │
│ aes-256-gcm with AAD │ A8A3CCBC6426D9A1017A0A932322F1852260A4AD6837 │
└────────────────────────┴──────────────────────────────────────────────────┘
┌─comment────────────────────────────┬─hex(secret)────────────────────────┐
│ aes-256-ofb no IV │ B4972BDC4459 │
│ aes-256-ofb no IV, different key │ 2FF57C092DC9 │
│ aes-256-ofb with IV │ 5E6CB398F653 │
│ aes-256-cbc no IV │ 1BC0629A92450D9E73A00E7D02CF4142 │
└─────────────────────────────────────┴─────────────────────────────────────┘Теперь попытаемся расшифровать эти данные:
Запрос:
SELECT comment, decrypt('aes-256-ofb', secret, '12345678910121314151617181920212') as plaintext FROM encryption_test;Результат:
┌─comment───────────────┬─plaintext────────────────────────────────────────────┐
│ aes-256-gcm │ OQ�E �t�7T�\���\� │
│ aes-256-gcm with AAD │ OQ�E �\��si����;�o�� │
└────────────────────────┴──────────────────────────────────────────────────────┘
┌─comment────────────────────────────┬─plaintext─────────────────────────────────────────────────┐
│ aes-256-ofb no IV │ Secret │
│ aes-256-ofb no IV, different key │ �4� � │
│ aes-256-ofb with IV │ ���6�~ │
│aes-256-cbc no IV │ �2*4�h3c�4w��@ │
└─────────────────────────────────────┴───────────────────────────────────────────────────────────┘Обратите внимание, что только часть данных была расшифрована верно. Оставшаяся часть расшифрована некорректно, так как при шифровании использовались другие значения mode, key, или iv.
Совместима с шифрованием myqsl и может расшифровать данные, зашифрованные функцией AES_ENCRYPT.
При одинаковых входящих значениях расшифрованный текст будет совпадать с результатом, возвращаемым функцией decrypt. Однако если key или iv длиннее, чем должны быть, aes_decrypt_mysql будет работать аналогично функции aes_decrypt в MySQL: свернет ключ и проигнорирует лишнюю часть iv.
Функция поддерживает расшифровку данных в следующих режимах:
Синтаксис:
aes_decrypt_mysql('mode', 'ciphertext', 'key' [, iv])Аргументы:
mode — режим шифрования. String.ciphertext — зашифрованный текст, который будет расшифрован. String.key — ключ шифрования. String.iv — инициализирующий вектор. Необязателен. String.Возвращаемое значение:
Примеры:
Расшифруем данные, которые до этого были зашифрованы в MySQL:
mysql> SET block_encryption_mode='aes-256-ofb';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT aes_encrypt('Secret', '123456789101213141516171819202122', 'iviviviviviviviv123456') as ciphertext;
+------------------------+
| ciphertext |
+------------------------+
| 0x24E9E4966469 |
+------------------------+
1 row in set (0.00 sec)Запрос:
SELECT aes_decrypt_mysql('aes-256-ofb', unhex('24E9E4966469'), '123456789101213141516171819202122', 'iviviviviviviviv123456') AS plaintext;Результат:
┌─plaintext─┐
│ Secret │
└────────────┘Функция, позволяющая сгруппировать несколько столбцов. Для столбцов, имеющих типы T1, T2, … возвращает кортеж типа Tuple(T1, T2, …), содержащий эти столбцы. Выполнение функции ничего не стоит. Кортежи обычно используются как промежуточное значение в качестве аргумента операторов IN, или для создания списка формальных параметров лямбда-функций. Кортежи не могут быть записаны в таблицу.
С помощью функции реализуется оператор (x, y, …).
Синтаксис:
tuple(x, y, …)Функция, позволяющая достать столбец из кортежа. N - индекс столбца начиная с 1. N должно быть константой. N должно быть целым строго положительным числом не большим размера кортежа. Выполнение функции ничего не стоит.
С помощью функции реализуется оператор x.N.
Синтаксис:
tupleElement(tuple, n)Выполняет синтаксическую подстановку элементов кортежа в место вызова.
Синтаксис:
untuple(x)Чтобы пропустить некоторые столбцы в результате запроса, вы можете использовать выражение EXCEPT.
Аргументы:
x — функция tuple, столбец или кортеж элементов. Tuple.Возвращаемое значение:
Примеры:
Входная таблица:
┌─key─┬─v1─┬─v2─┬─v3─┬─v4──┬─v5─┬─v6─────────┐
│ 1 │ 10 │ 20 │ 40 │ 30 │ 15 │ (33,'ab') │
│ 2 │ 25 │ 65 │ 70 │ 40 │ 6 │ (44,'cd') │
│ 3 │ 57 │ 30 │ 20 │ 10 │ 5 │ (55,'ef') │
│ 4 │ 55 │ 12 │ 7 │ 80 │ 90 │ (66,'gh') │
│ 5 │ 30 │ 50 │ 70 │ 25 │ 55 │ (77,'kl') │
└─────┴─────┴────┴────┴─────┴────┴────────────┘Пример использования столбца типа Tuple в качестве параметра функции untuple:
Запрос:
SELECT untuple(v6) FROM kv;Результат:
┌─_ut_1─┬─_ut_2─┐
│ 33 │ ab │
│ 44 │ cd │
│ 55 │ ef │
│ 66 │ gh │
│ 77 │ kl │
└────────┴───────┘Пример использования выражения EXCEPT:
Запрос:
SELECT untuple((* EXCEPT (v2, v3),)) FROM kv;Результат:
┌─key─┬─v1─┬─v4─┬─v5──┬─v6────────┐
│ 1 │ 10 │ 30 │ 15 │ (33,'ab') │
│ 2 │ 25 │ 40 │ 6 │ (44,'cd') │
│ 3 │ 57 │ 10 │ 5 │ (55,'ef') │
│ 4 │ 55 │ 80 │ 90 │ (66,'gh') │
│ 5 │ 30 │ 25 │ 55 │ (77,'kl') │
└─────┴─────┴────┴────┴────────────┘Смотрите также:
Возвращает расстояние Хэмминга между двумя кортежами одинакового размера.
Синтаксис:
tupleHammingDistance(tuple1, tuple2)Аргументы:
Кортежи должны иметь одинаковый размер и тип элементов.
Возвращаемое значение:
Тип: UInt8.
Примеры:
Запрос:
SELECT tupleHammingDistance((1, 2, 3), (3, 2, 1)) AS HammingDistance;Результат:
┌─HammingDistance─┐
│ 2 │
└──────────────────┘Может быть использовано с функциями MinHash для проверки строк на совпадение:
SELECT tupleHammingDistance(wordShingleMinHash(string), wordShingleMinHashCaseInsensitive(string)) as HammingDistance
FROM (SELECT 'RT.WideStore a column-oriented database management system for online analytical processing of queries.' AS string);Результат:
┌─HammingDistance─┐
│ 2 │
└──────────────────┘Приводит именованный кортеж к списку пар (имя, значение). Для Tuple(a T, b T, ..., c T) возвращает Array(Tuple(String, T), ...), где Strings — это названия именованных полей, а T — это соответствующие значения. Все значения в кортеже должны быть одинакового типа.
Синтаксис:
tupleToNameValuePairs(tuple)Аргументы:
tuple — именованный кортеж. Tuple с любым типом значений.Возвращаемое значение:
Тип: Array(Tuple(String, ...)).
Пример:
Запрос:
CREATE TABLE tupletest (`col` Tuple(user_ID UInt64, session_ID UInt64) ENGINE = Memory;
INSERT INTO tupletest VALUES (tuple( 100, 2502)), (tuple(1,100));
SELECT tupleToNameValuePairs(col) FROM tupletest;Результат:
┌─tupleToNameValuePairs(col)─────────────┐
│ [('user_ID',100),('session_ID',2502)] │
│ [('user_ID',1),('session_ID',100)] │
└──────────────────────────────────────────┘С помощью этой функции можно выводить столбцы в виде строк:
CREATE TABLE tupletest (`col` Tuple(CPU Float64, Memory Float64, Disk Float64)) ENGINE = Memory;
INSERT INTO tupletest VALUES(tuple(3.3, 5.5, 6.6));
SELECT arrayJoin(tupleToNameValuePairs(col))FROM tupletest;Результат:
┌─arrayJoin(tupleToNameValuePairs(col))─┐
│ ('CPU',3.3) │
│ ('Memory',5.5) │
│ ('Disk',6.6) │
└──────────────────────────────────────────┘Если в функцию передается обычный кортеж, RT.WideStore использует индексы значений в качестве имен:
SELECT tupleToNameValuePairs(tuple(3, 2, 1));Результат:
┌─tupleToNameValuePairs(tuple(3, 2, 1))─┐
│ [('1',3),('2',2),('3',1)] │
└──────────────────────────────────────────┘Вычисляет сумму соответствующих значений двух кортежей одинакового размера.
Синтаксис:
tuplePlus(tuple1, tuple2)Синоним: vectorSum.
Аргументы:
Возвращаемое значение:
Тип: Tuple.
Пример:
Запрос:
SELECT tuplePlus((1, 2), (2, 3));Результат:
┌─tuplePlus((1, 2), (2, 3))─┐
│ (3,5) │
└─────────────────────────────┘Вычисляет разность соответствующих значений двух кортежей одинакового размера.
Синтаксис:
tupleMinus(tuple1, tuple2)Синоним: vectorDifference.
Аргументы:
Возвращаемое значение:
Тип: Tuple.
Пример:
Запрос:
SELECT tupleMinus((1, 2), (2, 3));Результат:
┌─tupleMinus((1, 2), (2, 3))─┐
│ (-1,-1) │
└──────────────────────────────┘Вычисляет произведение соответствующих значений двух кортежей одинакового размера.
Синтаксис:
tupleMultiply(tuple1, tuple2)Аргументы:
Возвращаемое значение:
Тип: Tuple.
Пример:
Запрос:
SELECT tupleMultiply((1, 2), (2, 3));Результат:
┌─tupleMultiply((1, 2), (2, 3))─┐
│ (2,6) │
└──────────────────────────────────┘Вычисляет частное соответствующих значений двух кортежей одинакового размера. Обратите внимание, что при делении на ноль возвращается значение inf.
Синтаксис:
tupleDivide(tuple1, tuple2)Аргументы:
Возвращаемое значение:
Тип: Tuple.
Пример:
Запрос:
SELECT tupleDivide((1, 2), (2, 3));Результат:
┌─tupleDivide((1, 2), (2, 3))─┐
│ (0.5,0.6666666666666666) │
└───────────────────────────────┘Применяет отрицание ко всем значениям кортежа.
Синтаксис:
tupleNegate(tuple)Аргументы:
tuple — кортеж. Tuple.Возвращаемое значение:
Тип: Tuple.
Пример:
Запрос:
SELECT tupleNegate((1, 2));Результат:
┌─tupleNegate((1, 2))─┐
│ (-1,-2) │
└───────────────────────┘Возвращает кортеж, в котором значения всех элементов умножены на заданное число.
Синтаксис:
tupleMultiplyByNumber(tuple, number)Аргументы:
Возвращаемое значение:
Тип: Tuple.
Пример:
Запрос:
SELECT tupleMultiplyByNumber((1, 2), -2.1);Результат:
┌─tupleMultiplyByNumber((1, 2), -2.1)─┐
│ (-2.1,-4.2) │
└────────────────────────────────────────┘Возвращает кортеж, в котором значения всех элементов поделены на заданное число. Обратите внимание, что при делении на ноль возвращается значение inf.
Синтаксис:
tupleDivideByNumber(tuple, number)Аргументы:
Возвращаемое значение:
Тип: Tuple.
Пример:
Запрос:
SELECT tupleDivideByNumber((1, 2), 0.5);Результат:
┌─tupleDivideByNumber((1, 2), 0.5)─┐
│ (2,4) │
└─────────────────────────────────────┘Вычисляет скалярное произведение двух кортежей одинакового размера.
Синтаксис:
dotProduct(tuple1, tuple2)Синоним: scalarProduct.
Аргументы:
Возвращаемое значение:
Тип: Int/UInt, Float или Decimal.
Пример:
Запрос:
SELECT dotProduct((1, 2), (2, 3));Результат:
┌─dotProduct((1, 2), (2, 3))─┐
│ 8 │
└──────────────────────────────┘Вычисляет сумму абсолютных значений кортежа.
Синтаксис:
L1Norm(tuple)Синоним: normL1.
Аргументы:
tuple — кортеж. Tuple.Возвращаемое значение:
Пример:
Запрос:
SELECT L1Norm((1, 2));Результат:
┌─L1Norm((1, 2))─┐
│ 3 │
└─────────────────┘Вычисляет квадратный корень из суммы квадратов значений кортежа.
Синтаксис:
L2Norm(tuple)Синоним: normL2.
Аргументы:
tuple — кортеж. Tuple.Возвращаемое значение:
Тип: Float.
Пример:
Запрос:
SELECT L2Norm((1, 2));Результат:
┌───L2Norm((1, 2))─┐
│ 2.23606797749979 │
└───────────────────┘Вычисляет максимум из абсолютных значений кортежа.
Синтаксис:
LinfNorm(tuple)Синоним: normLinf.
Аргументы:
tuple — кортеж. Tuple.Возвращаемое значение:
Тип: Float.
Пример:
Запрос:
SELECT LinfNorm((1, -2));Результат:
┌─LinfNorm((1, -2))─┐
│ 2 │
└────────────────────┘Возвращает корень степени p из суммы абсолютных значений кортежа, возведенных в степень p.
Синтаксис:
LpNorm(tuple, p)Синоним: normLp.
Аргументы:
tuple — кортеж. Tuple.p — степень. Возможные значение: любое число из промежутка [1;inf). UInt или Float.Возвращаемое значение:
Тип: Float.
Пример:
Запрос:
SELECT LpNorm((1, -2),2);Результат:
┌─LpNorm((1, -2), 2)─┐
│ 2.23606797749979 │
└──────────────────────┘Вычисляет расстояние между двумя точками (значения кортежей — координаты точек) в пространстве L1 (расстояние городских кварталов).
Синтаксис:
L1Distance(tuple1, tuple2)Синоним: distanceL1.
Аргументы:
Возвращаемое значение:
Тип: Float.
Пример:
Запрос:
SELECT L1Distance((1, 2), (2, 3));Результат:
┌─L1Distance((1, 2), (2, 3))─┐
│ 2 │
└──────────────────────────────┘Вычисляет расстояние между двумя точками (значения кортежей — координаты точек) в пространстве L2 (Евклидово расстояние).
Синтаксис:
L2Distance(tuple1, tuple2)Синоним: distanceL2.
Аргументы:
tuple1 — первый кортеж. Tuple.
tuple2 — второй кортеж. Tuple.Возвращаемое значение:
Тип: Float.
Пример:
Запрос:
SELECT L2Distance((1, 2), (2, 3));Результат:
┌─L2Distance((1, 2), (2, 3))─┐
│ 1.4142135623730951 │
└──────────────────────────────┘Вычисляет расстояние между двумя точками (значения кортежей — координаты точек) в пространстве L_{inf}.
Синтаксис:
LinfDistance(tuple1, tuple2)Синоним: distanceLinf.
Аргументы:
Возвращаемые значения:
Тип: Float.
Пример:
Запрос:
SELECT LinfDistance((1, 2), (2, 3));Результат:
┌─LinfDistance((1, 2), (2, 3))─┐
│ 1 │
└─────────────────────────────────┘Вычисляет расстояние между двумя точками (значения кортежей — координаты точек) в пространстве Lp.
Синтаксис:
LpDistance(tuple1, tuple2, p)Синоним: distanceLp.
Аргументы:
tuple1 — первый кортеж. Tuple.tuple2 — второй кортеж. Tuple.p — степень. Возможные значение: любое число из промежутка [1;inf). UInt или Float.Возвращаемое значение:
Тип: Float.
Пример:
Запрос:
SELECT LpDistance((1, 2), (2, 3), 3);Результат:
┌─LpDistance((1, 2), (2, 3), 3)─┐
│ 1.2599210498948732 │
└──────────────────────────────────┘Вычисляет единичный вектор для исходного вектора (значения кортежа — координаты вектора) в пространстве L1 (расстояние городских кварталов).
Синтаксис:
L1Normalize(tuple)Синоним: normalizeL1.
Аргументы:
tuple — Tuple.Возвращаемое значение:
Тип: кортеж Tuple значений Float.
Пример:
Запрос:
SELECT L1Normalize((1, 2));Результат:
┌─L1Normalize((1, 2))───────────────────────┐
│ (0.3333333333333333,0.6666666666666666) │
└─────────────────────────────────────────────┘Вычисляет единичный вектор для исходного вектора (значения кортежа — координаты вектора) в пространстве L2 (Евклидово пространство).
Синтаксис:
L2Normalize(tuple)Синоним: normalizeL1.
Аргументы:
tuple — кортеж. Tuple.Возвращаемое значение:
Тип: кортеж Tuple значений Float.
Пример:
Запрос:
SELECT L2Normalize((3, 4));Результат:
┌─L2Normalize((3, 4))─┐
│ (0.6,0.8) │
└───────────────────────┘Вычисляет единичный вектор для исходного вектора (значения кортежа — координаты вектора) в пространстве L_{inf}.
Синтаксис:
LinfNormalize(tuple)Синоним: normalizeLinf .
Аргументы:
tuple — кортеж. Tuple.Возвращаемое значение:
Тип: кортеж Tuple значений Float.
Пример:
Запрос:
SELECT LinfNormalize((3, 4));Результат:
┌─LinfNormalize((3, 4))─┐
│ (0.75,1) │
└─────────────────────────┘Вычисляет единичный вектор для исходного вектора (значения кортежа — координаты вектора) в пространстве Lp.
Синтаксис:
LpNormalize(tuple, p)Синоним: normalizeLp .
Аргументы:
tuple — кортеж. Tuple.p — степень. Возможные значение: любое число из промежутка [1;inf). UInt или Float.Возвращаемое значение:
Тип: кортеж Tuple значений Float.
Пример:
Запрос:
SELECT LpNormalize((3, 4),5);Результат:
┌─LpNormalize((3, 4), 5)────────────────────┐
│ (0.7187302630182624,0.9583070173576831) │
└─────────────────────────────────────────────┘Вычисляет косинусную разницу двух векторов (значения кортежей — координаты векторов). Чем меньше возвращаемое значение, тем больше сходство между векторами.
Синтаксис:
cosineDistance(tuple1, tuple2)Аргументы:
Возвращаемые значения:
Тип: Float.
Пример:
Запрос:
SELECT cosineDistance((1, 2), (2, 3));Результат:
┌─cosineDistance((1, 2), (2, 3))─┐
│ 0.007722123286332261 │
└───────────────────────────────────┘Табличные функции — это метод создания таблиц.
Табличные функции можно использовать в:
Секции FROM запроса SELECT.
Это способ создания временной таблицы, которая доступна только в текущем запросе.Запросе CREATE TABLE AS \<table_function()>.
Это один из методов создания таблицы.Запросе INSERT INTO TABLE FUNCTION.
ПРЕДУПРЕЖДЕНИЕ: Если настройка allow_ddl выключена, то использовать табличные функции невозможно.
Функция
Описание
file Создаёт таблицу с движком File. merge Создаёт таблицу с движком Merge. numbers Создаёт таблицу с единственным столбцом, заполненным целыми числами. remote Предоставляет доступ к удалённым серверам, не создавая таблицу с движком Distributed. url Создаёт таблицу с движком Url. mysql Создаёт таблицу с движком MySQL. jdbc Создаёт таблицу с дижком JDBC. odbc Создаёт таблицу с движком ODBC. hdfs Создаёт таблицу с движком HDFS. s3 Создаёт таблицу с движком S3.
Создаёт таблицу из файла. Данная табличная функция похожа на табличные функции url и hdfs.
Функция file может использоваться в запросах SELECT и INSERT при работе с движком таблиц File.
Синтаксис:
file(path [,format] [,structure])Параметры:
path — относительный путь до файла от user_files_path. Путь к файлу поддерживает следующие шаблоны в режиме доступа только для чтения *, ?, {abc,def} и {N..M}, где N, M — числа, 'abc', 'def' — строки.format — формат файла.structure — структура таблицы. Формат: 'colunmn1_name column1_ype, column2_name column2_type, ...'.Возвращаемое значение:
Таблица с указанной структурой, предназначенная для чтения или записи данных в указанном файле.
Примеры:
Настройка user_files_path и содержимое файла test.csv:
$ grep user_files_path /etc/widestore-server/config.xml
<user_files_path>/var/lib/widestore/user_files/</user_files_path>
$ cat /var/lib/widestore/user_files/test.csv
1,2,3
3,2,1
78,43,45Получение данных из таблицы в файле test.csv и выборка первых двух строк из неё:
SELECT * FROM file('test.csv', 'CSV', 'column1 UInt32, column2 UInt32, column3 UInt32') LIMIT 2;
┌─column1─┬─column2─┬─column3─┐
│ 1 │ 2 │ 3 │
│ 3 │ 2 │ 1 │
└──────────┴──────────┴─────────┘Получение первых 10 строк таблицы, содержащей 3 столбца типа UInt32, из CSV-файла:
SELECT * FROM file('test.csv', 'CSV', 'column1 UInt32, column2 UInt32, column3 UInt32') LIMIT 10;Вставка данных из файла в таблицу:
INSERT INTO FUNCTION file('test.csv', 'CSV', 'column1 UInt32, column2 UInt32, column3 UInt32') VALUES (1, 2, 3), (3, 2, 1);
SELECT * FROM file('test.csv', 'CSV', 'column1 UInt32, column2 UInt32, column3 UInt32');
┌─column1─┬─column2─┬─column3─┐
│ 1 │ 2 │ 3 │
│ 3 │ 2 │ 1 │
└──────────┴──────────┴─────────┘При описании пути к файлу могут использоваться шаблоны поиска. Обрабатываются только те файлы, у которых путь и название соответствуют шаблону полностью (а не только префикс или суффикс).
* — заменяет любое количество любых символов кроме /, включая отсутствие символов.? — заменяет ровно один любой символ.{some_string,another_string,yet_another_one} — заменяет любую из строк 'some_string', 'another_string', 'yet_another_one'.{N..M} — заменяет любое число в интервале от N до M включительно (может содержать ведущие нули).Конструкция с {} аналогична табличной функции remote.
Пример:
Предположим, у нас есть несколько файлов со следующими относительными путями:
Запросим количество строк в этих файлах:
SELECT count(*) FROM file('{some,another}_dir/some_file_{1..3}', 'TSV', 'name String, value UInt32');Запросим количество строк во всех файлах этих двух директорий:
SELECT count(*) FROM file('{some,another}_dir/*', 'TSV', 'name String, value UInt32');
"ПРЕДУПРЕЖДЕНИЕ": Если ваш список файлов содержит интервал с ведущими нулями, используйте конструкцию с фигурными скобками для каждой цифры по отдельности или используйте `?`.
Пример:
Запрос данных из файлов с именами file000, file001, … , file999:
SELECT count(*) FROM file('big_dir/file{0..9}{0..9}{0..9}', 'CSV', 'name String, value UInt32');_path — путь к файлу._file — имя файла.Смотрите также:
Создаёт временную таблицу типа Merge. Структура таблицы берётся из первой попавшейся таблицы, подходящей под регулярное выражение.
Синтаксис:
merge('db_name', 'tables_regexp')Аргументы:
db_name — Возможные варианты:
currentDatabase(),REGEXP(expression), где expression — регулярное выражение для отбора БД.tables_regexp — регулярное выражение для имен таблиц в указанной БД или нескольких БД.
См. также:
numbers(N) - возвращает таблицу с единственным столбцом number (UInt64), содержащим натуральные числа от 0 до N-1. numbers(N, M) - возвращает таблицу с единственным столбцом number (UInt64), содержащим натуральные числа от N to (N + M - 1).
Так же как и таблица system.numbers может использоваться для тестов и генерации последовательных значений. Функция numbers(N, M) работает более эффективно, чем выборка из system.numbers.
Следующие запросы эквивалентны:
SELECT * FROM numbers(10);
SELECT * FROM numbers(0,10);
SELECT * FROM system.numbers LIMIT 10;Примеры:
-- генерация последовательности всех дат от 2010-01-01 до 2010-12-31select toDate('2010-01-01') + number as d FROM numbers(365);Позволяет обратиться к удалённым серверам без создания таблицы типа Distributed. Функция remoteSecure работает аналогично remote, но использует защищенное соединение.
Обе функции могут использоваться в запросах SELECT и INSERT.
Синтаксис:
remote('addresses_expr', db, table[, 'user'[, 'password']])
remote('addresses_expr', db.table[, 'user'[, 'password']])
remoteSecure('addresses_expr', db, table[, 'user'[, 'password']])
remoteSecure('addresses_expr', db.table[, 'user'[, 'password']])Параметры:
addresses_expr — выражение, генерирующее адреса удалённых серверов. Это может быть просто один адрес сервера. Адрес сервера — это host:port или только host.
Вместо параметра host может быть указано имя сервера или его адрес в формате IPv4 или IPv6. IPv6 адрес указывается в квадратных скобках.
port — TCP-порт удалённого сервера. Если порт не указан, используется tcp_port из конфигурационного файла сервера, к которому обратились через функцию remote (по умолчанию - 9000), и tcp_port_secure, к которому обратились через функцию remoteSecure (по умолчанию — 9440).
С IPv6-адресом обязательно нужно указывать порт.
Тип: String.
db — имя базы данных. Тип: String.
table — имя таблицы. Тип: String.
user — имя пользователя. Если пользователь не указан, то по умолчанию default. Тип: String.
password — пароль. Если пароль не указан, то используется пустой пароль. Тип: String.
sharding_key — ключ шардирования для поддержки распределения данных между узлами. Например: insert into remote('127.0.0.1:9000,127.0.0.2', db, table, 'default', rand()). Тип: UInt32.
Возвращаемое значение:
Набор данных с удаленных серверов.
Использование:
Использование табличной функции remote менее оптимально, чем создание таблицы типа Distributed, так как в этом случае соединения с серверами устанавливаются заново при каждом запросе. Если указываются имена серверов, то приходится также выполнять поиск сервера по имени. Кроме того, не ведётся сквозной подсчёт ошибок при работе с разными репликами. При обработке большого количества запросов всегда создавайте таблицу типа Distributed, использовать табличную функцию remote в таких случаях не рекомендуется.
Табличная функция remote может быть полезна в следующих случаях:
Адреса:
example01-01-1example01-01-1
:9000
localhost
127.0.0.1
[::]:9000
[2a02:6b8:0:1111::11]:9000Адреса можно указать через запятую. В этом случае RT.WideStore обработает запрос как распределённый, т.е. отправит его по всем указанным адресам как на шарды с разными данными. Пример:
example01-01-1,example01-02-1Примеры:
Выборка данных с удаленного сервера:
SELECT * FROM remote('127.0.0.1', db.remote_engine_table) LIMIT 3;Вставка данных с удаленного сервера в таблицу:
CREATE TABLE remote_table (name String, value UInt32) ENGINE=Memory;
INSERT INTO FUNCTION remote('127.0.0.1', currentDatabase(), 'remote_table') VALUES ('test', 42);
SELECT * FROM remote_table;Шаблоны в фигурных скобках { } используются, чтобы сгенерировать список шардов или указать альтернативный адрес на случай отказа. В одном URL можно использовать несколько шаблонов. Поддерживаются следующие типы шаблонов.
example0{1,2}-1 генерирует адреса example01-1 и example02-1.example0{1..2}-1 генерирует example01-1 и example02-1.example{01..03}-1 генерируются example01-1, example02-1 и example03-1.|. Шаблон задает адреса реплик. Например, example01-{1|2} генерирует репликиexample01-1 и example01-2.Запрос будет отправлен на первую живую реплику. При этом для remote реплики перебираются в порядке, заданном настройкой load_balancing. Количество генерируемых адресов ограничено настройкой table_function_remote_max_addresses.
Функция url берет данные по указанному адресу URL и создает из них таблицу указанной структуры со столбцами указанного формата.
Функция url может быть использована в запросах SELECT и INSERT с таблицами на движке URL.
Синтаксис:
url(URL [,format] [,structure])Параметры:
URL — HTTP или HTTPS-адрес сервера, который может принимать запросы GET или POST (для запросов SELECT или INSERT соответственно). Тип: String.format — формат данных. Тип: String.structure — структура таблицы в формате 'UserID UInt64, Name String'. Определяет имена и типы столбцов. Тип: String.Возвращаемое значение:
Таблица с указанными форматом и структурой, а также с данными, полученными из указанного адреса URL.
Примеры:
Получение с HTTP-сервера первых 3 строк таблицы с данными в формате CSV, содержащей столбцы типа String и UInt32.
SELECT * FROM url('http://127.0.0.1:12345/', CSV, 'column1 String, column2 UInt32') LIMIT 3;Вставка данных в таблицу:
CREATE TABLE test_table (column1 String, column2 UInt32) ENGINE=Memory;
INSERT INTO FUNCTION url('http://127.0.0.1:8123/?query=INSERT+INTO+test_table+FORMAT+CSV', 'CSV', 'column1 String, column2 UInt32') VALUES ('http interface', 42);
SELECT * FROM test_table;Шаблоны в фигурных скобках { } используются, чтобы сгенерировать список шардов или указать альтернативные адреса на случай отказа. Поддерживаемые типы шаблонов и примеры смотрите в описании функции remote. Символ | внутри шаблонов используется, чтобы задать адреса, если предыдущие оказались недоступны. Эти адреса перебираются в том же порядке, в котором они указаны в шаблоне. Количество адресов, которые могут быть сгенерированы, ограничено настройкой glob_expansion_max_elements.
Позволяет выполнять запросы SELECT и INSERT над данными, хранящимися на удалённом MySQL сервере.
Синтаксис:
mysql('host:port', 'database', 'table', 'user', 'password'[, replace_query, 'on_duplicate_clause'])Аргументы:
host:port — адрес сервера MySQL.
database — имя базы данных на удалённом сервере.
table — имя таблицы на удалённом сервере.
user — пользователь MySQL.
password — пароль пользователя.
replace_query — флаг, отвечающий за преобразование запросов INSERT INTO в REPLACE INTO. Возможные значения:
0 - выполняется запрос INSERT INTO.1 - выполняется запрос REPLACE INTO.on_duplicate_clause — выражение ON DUPLICATE KEY on_duplicate_clause, добавляемое в запрос INSERT. Может быть передано только с помощью replace_query = 0 (если вы одновременно передадите replace_query = 1 и on_duplicate_clause, будет сгенерировано исключение).
Пример: `INSERT INTO t (c1,c2) VALUES ('a', 2) ON DUPLICATE KEY UPDATE c2 = c2 + 1`, где `on_duplicate_clause` это `UPDATE c2 = c2 + 1`.
Выражения, которые могут использоваться в качестве `on_duplicate_clause` в секции `ON DUPLICATE KEY`, можно посмотреть в документации по [MySQL](http://www.mysql.ru/docs/).Простые условия WHERE такие как =, !=, >, >=, <, = выполняются на стороне сервера MySQL.
Остальные условия и ограничение выборки LIMIT будут выполнены в RT.WideStore только после выполнения запроса к MySQL.
Поддерживает несколько реплик, которые должны быть перечислены через |. Например:
SELECT name FROM mysql(`mysql{1|2|3}:3306`, 'mysql_database', 'mysql_table', 'user', 'password');или
SELECT name FROM mysql(`mysql1:3306|mysql2:3306|mysql3:3306`, 'mysql_database', 'mysql_table', 'user', 'password');Возвращаемое значение:
Объект таблицы с теми же столбцами, что и в исходной таблице MySQL.
Примечание: Чтобы отличить табличную функцию `mysql (...)` в запросе `INSERT` от имени таблицы со списком столбцов, используйте ключевые слова `FUNCTION` или `TABLE FUNCTION`. См. примеры ниже.
Примеры:
Таблица в MySQL:
mysql> CREATE TABLE `test`.`test` (
-> `int_id` INT NOT NULL AUTO_INCREMENT,
-> `float` FLOAT NOT NULL,
-> PRIMARY KEY (`int_id`));
mysql> INSERT INTO test (`int_id`, `float`) VALUES (1,2);
mysql> SELECT * FROM test;
+--------+-------+
| int_id | float |
+--------+-------+
| 1 | 2 |
+--------+-------+Получение данных в RT.WideStore:
SELECT * FROM mysql('localhost:3306', 'test', 'test', 'bayonet', '123');
┌─int_id─┬─float─┐
│ 1 │ 2 │
└─────────┴───────┘Замена и вставка:
INSERT INTO FUNCTION mysql('localhost:3306', 'test', 'test', 'bayonet', '123', 1) (int_id, float) VALUES (1, 3);
INSERT INTO TABLE FUNCTION mysql('localhost:3306', 'test', 'test', 'bayonet', '123', 0, 'UPDATE int_id = int_id + 1') (int_id, float) VALUES (1, 4);
SELECT * FROM mysql('localhost:3306', 'test', 'test', 'bayonet', '123');
┌─int_id─┬─float─┐
│ 1 │ 3 │
│ 2 │ 4 │
└─────────┴───────┘Смотрите также:
Позволяет выполнять запросы SELECT и INSERT над таблицами удаленной БД PostgreSQL.
Синтаксис
postgresql('host:port', 'database', 'table', 'user', 'password'[, `schema`])Аргументы:
host:port — адрес сервера PostgreSQL.database — имя базы данных на удалённом сервере.table — имя таблицы на удалённом сервере.user — пользователь PostgreSQL.password — пароль пользователя.schema — имя схемы, если не используется схема по умолчанию. Необязательный аргумент.Возвращаемое значение:
Таблица с теми же столбцами, что и в исходной таблице PostgreSQL.
ПРИМЕЧАНИЕ: В запросах `INSERT` для того чтобы отличить табличную функцию `postgresql(...)` от таблицы со списком имен столбцов вы должны указывать ключевые слова `FUNCTION` или `TABLE FUNCTION`. См. примеры ниже.
Запросы SELECT на стороне PostgreSQL выполняются как COPY (SELECT ...) TO STDOUT внутри транзакции PostgreSQL только на чтение с коммитом после каждого запроса SELECT.
Простые условия для WHERE такие как =, !=, >, >=, <, <= и IN исполняются на стороне PostgreSQL сервера.
Все операции объединения, агрегации, сортировки, условия IN [ array ] и ограничения LIMIT выполняются на стороне RT.WideStore только после того как запрос к PostgreSQL закончился.
Запросы INSERT на стороне PostgreSQL выполняются как COPY "table_name" (field1, field2, ... fieldN) FROM STDIN внутри PostgreSQL транзакции с автоматическим коммитом после каждого запроса INSERT.
PostgreSQL массивы конвертируются в массивы RT.WideStore.
ПРИМЕЧАНИЕ: Будьте внимательны, в PostgreSQL массивы, созданные как `type_name[]`, являются многомерными и могут содержать в себе разное количество измерений в разных строках одной таблицы. Внутри RT.WideStore допустимы только многомерные массивы с одинаковым кол-вом измерений во всех строках таблицы.
Поддерживает несколько реплик, которые должны быть перечислены через |. Например:
SELECT name FROM postgresql(`postgres{1|2|3}:5432`, 'postgres_database', 'postgres_table', 'user', 'password');или
SELECT name FROM postgresql(`postgres1:5431|postgres2:5432`, 'postgres_database', 'postgres_table', 'user', 'password');При использовании словаря PostgreSQL поддерживается приоритет реплик. Чем больше номер реплики, тем ниже ее приоритет. Наивысший приоритет у реплики с номером 0.
Примеры:
Таблица в PostgreSQL:
postgres=# CREATE TABLE "public"."test" (
"int_id" SERIAL,
"int_nullable" INT NULL DEFAULT NULL,
"float" FLOAT NOT NULL,"str" VARCHAR(100) NOT NULL DEFAULT '',
"float_nullable" FLOAT NULL DEFAULT NULL,PRIMARY KEY (int_id));
CREATE TABLE
postgres=# INSERT INTO test (int_id, str, "float") VALUES (1,'test',2);
INSERT 0 1
postgresql> SELECT * FROM test;
int_id | int_nullable | float | str | float_nullable
--------+--------------+-------+------+----------------
1 | | 2 | test |
(1 row)Получение данных в RT.WideStore:
SELECT * FROM postgresql('localhost:5432', 'test', 'test', 'postgresql_user', 'password') WHERE str IN ('test');
┌─int_id─┬─int_nullable─┬─float─┬─str──┬─float_nullable─┐
│ 1 │ ᴺᵁᴸᴸ │ 2 │ test │ ᴺᵁᴸᴸ │
└─────────┴───────────────┴───────┴───────┴─────────────────┘Вставка данных:
INSERT INTO TABLE FUNCTION postgresql('localhost:5432', 'test', 'test', 'postgrsql_user', 'password') (int_id, float) VALUES (2, 3);
SELECT * FROM postgresql('localhost:5432', 'test', 'test', 'postgresql_user', 'password');
┌─int_id─┬─int_nullable─┬─float─┬─str──┬─float_nullable─┐
│ 1 │ ᴺᵁᴸᴸ │ 2 │ test │ ᴺᵁᴸᴸ │
│ 2 │ ᴺᵁᴸᴸ │ 3 │ │ ᴺᵁᴸᴸ │
└─────────┴───────────────┴───────┴───────┴─────────────────┘Using Non-default Schema:
postgres=# CREATE SCHEMA "nice.schema";
postgres=# CREATE TABLE "nice.schema"."nice.table" (a integer);
postgres=# INSERT INTO "nice.schema"."nice.table" SELECT i FROM generate_series(0, 99) as t(i)
CREATE TABLE pg_table_schema_with_dots (a UInt32)
ENGINE PostgreSQL('localhost:5432', 'WideStore', 'nice.table', 'postgrsql_user', 'password', 'nice.schema');См. также:
jdbc(datasource, schema, table) - возвращает таблицу, соединение с которой происходит через JDBC-драйвер.
Для работы этой табличной функции требуется отдельно запускать приложение jdbc-bridge. Данная функция поддерживает Nullable типы (на основании DDL таблицы к которой происходит запрос).
Пример:
SELECT * FROM jdbc('jdbc:mysql://localhost:3306/?user=root&password=root', 'schema', 'table')
SELECT * FROM jdbc('mysql://localhost:3306/?user=root&password=root', 'select * from schema.table')
SELECT * FROM jdbc('mysql-dev?p1=233', 'num Int32', 'select toInt32OrZero(''{{p1}}'') as num')
SELECT *FROM jdbc('mysql-dev?p1=233', 'num Int32', 'select toInt32OrZero(''{{p1}}'') as num')
SELECT a.datasource AS server1, b.datasource AS server2, b.name AS db
FROM jdbc('mysql-dev?datasource_column', 'show databases') a
INNER JOIN jdbc('self?datasource_column', 'show databases') b ON a.Database = b.nameВозвращает таблицу, подключенную через ODBC.
odbc(connection_settings, external_database, external_table)Параметры:
connection_settings — название секции с настройками соединения в файле odbc.ini.external_database — имя базы данных во внешней СУБД.external_table — имя таблицы в external_database.Чтобы использование ODBC было безопасным, RT.WideStore использует отдельную программу widestore-odbc-bridge. Если драйвер ODBC подгружать непосредственно из widestore-server, то проблемы с драйвером могут привести к аварийной остановке сервера RT.WideStore. RT.WideStore автоматически запускает widestore-odbc-bridge по мере необходимости. Программа устанавливается из того же пакета, что и widestore-server.
Поля из внешней таблицы со значениями NULL получают значение по умолчанию для базового типа данных. Например, если поле в удалённой таблице MySQL имеет тип INT NULL оно сконвертируется в 0 (значение по умолчанию для типа данных RT.WideStoreInt32).
Получение данных из локальной установки MySQL через ODBC
Этот пример проверялся в Ubuntu Linux 18.04 для MySQL server 5.7.
Убедитесь, что unixODBC и MySQL Connector установлены.
По умолчанию (если установлен из пакетов) RT.WideStore запускается от имени пользователя widestore. Таким образом, вам нужно создать и настроить этого пользователя на сервере MySQL.
$ sudo mysql
mysql> CREATE USER 'widestore'@'localhost' IDENTIFIED BY 'widestore';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'widestore'@'widestore' WITH GRANT OPTION;Теперь настроим соединение в /etc/odbc.ini.
$ cat /etc/odbc.ini
[mysqlconn]
DRIVER = /usr/local/lib/libmyodbc5w.so
SERVER = 127.0.0.1
PORT = 3306
DATABASE = test
USERNAME = widestore
PASSWORD = widestoreВы можете проверить соединение с помощью утилиты isql из установки unixODBC.
$ isql -v mysqlconn
+---------------------------------------+
| Connected! |
| |
...Таблица в MySQL:
mysql> CREATE TABLE `test`.`test` (
-> `int_id` INT NOT NULL AUTO_INCREMENT,
-> `int_nullable` INT NULL DEFAULT NULL,
-> `float` FLOAT NOT NULL,
-> `float_nullable` FLOAT NULL DEFAULT NULL,
-> PRIMARY KEY (`int_id`));
Query OK, 0 rows affected (0,09 sec)
mysql> insert into test (`int_id`, `float`) VALUES (1,2);
Query OK, 1 row affected (0,00 sec)
mysql> select * from test;
+--------+--------------+-------+----------------+
| int_id | int_nullable | float | float_nullable |
+--------+--------------+-------+----------------+
| 1 | NULL | 2 | NULL |
+--------+--------------+-------+----------------+
1 row in set (0,00 sec)Получение данных из таблицы MySQL в RT.WideStore:
SELECT * FROM odbc('DSN=mysqlconn', 'test', 'test')
┌─int_id─┬─int_nullable─┬─float─┬─float_nullable─┐
│ 1 │ 0 │ 2 │ 0 │
└─────────┴───────────────┴────────┴─────────────────┘Смотрите также:
Создаёт таблицу из файла в HDFS. Данная табличная функция похожа на табличные функции url и file.
hdfs(URI, format, structure)Входные параметры:
URI — URI файла в HDFS. Путь к файлу поддерживает следующие шаблоны в режиме доступа только для чтения *, ?, {abc,def} и {N..M}, где N, M — числа, `'abc', 'def' — строки.format — формат файла.structure — структура таблицы. Формат 'column1_name column1_type, column2_name column2_type, ...'.Возвращаемое значение:
Таблица с указанной структурой, предназначенная для чтения или записи данных в указанном файле.
Пример:
Таблица из hdfs://hdfs1:9000/test и выборка первых двух строк из неё:
SELECT *
FROM hdfs('hdfs://hdfs1:9000/test', 'TSV', 'column1 UInt32, column2 UInt32, column3 UInt32')
LIMIT 2
┌─column1─┬─column2─┬─column3─┐
│ 1 │ 2 │ 3 │
│ 3 │ 2 │ 1 │
└──────────┴──────────┴─────────┘Шаблоны в пути:
* — Заменяет любое количество любых символов кроме /, включая отсутствие символов.? — Заменяет ровно один любой символ.{some_string,another_string,yet_another_one} — Заменяет любую из строк 'some_string', 'another_string', 'yet_another_one'.{N..M} — Заменяет любое число в интервале от N до M включительно (может содержать ведущие нули).Конструкция с {} аналогична табличной функции remote.
ПРЕДУПРЕЖДЕНИЕ: Если ваш список файлов содержит интервал с ведущими нулями, используйте конструкцию с фигурными скобками для каждой цифры по отдельности или используйте `?`.
Шаблоны могут содержаться в разных частях пути. Обрабатываться будут ровно те файлы, которые и удовлетворяют всему шаблону пути, и существуют в файловой системе.
_path — Путь к файлу._file — Имя файла.Смотрите также:
Предоставляет табличный интерфейс для выбора/вставки файлов в Amazon S3. Эта табличная функция похожа на hdfs, но обеспечивает специфические для S3 возможности.
Синтаксис:
s3(path [,aws_access_key_id, aws_secret_access_key] [,format] [,structure] [,compression])Аргументы:
path — URL-адрес бакета с указанием пути к файлу. Поддерживает следующие подстановочные знаки в режиме "только чтение": *, ?, {abc,def} и {N..M} где N, M — числа, 'abc', 'def' — строки.format — формат файла.structure — cтруктура таблицы. Формат 'column1_name column1_type, column2_name column2_type, ...'.compression — автоматически обнаруживает сжатие по расширению файла. Возможные значения: none, gzip/gz, brotli/br, xz/LZMA, zstd/zst. Необязательный параметр.Возвращаемые значения:
Таблица с указанной структурой для чтения или записи данных в указанный файл.
Примеры:
Создание таблицы из файла S3 https://storage.yandexcloud.net/my-test-bucket-768/data.csv и выбор первых трех столбцов из нее:
Запрос:
SELECT *
FROM s3('https://storage.yandexcloud.net/my-test-bucket-768/data.csv', 'CSV', 'column1 UInt32, column2 UInt32, column3 UInt32')
LIMIT 2;Результат:
┌─column1─┬─column2─┬─column3─┐
│ 1 │ 2 │ 3 │
│ 3 │ 2 │ 1 │
└──────────┴──────────┴─────────┘То же самое, но файл со сжатием gzip:
Запрос:
SELECT *
FROM s3('https://storage.yandexcloud.net/my-test-bucket-768/data.csv.gz', 'CSV', 'column1 UInt32, column2 UInt32, column3 UInt32', 'gzip')
LIMIT 2;Результат:
┌─column1─┬─column2─┬─column3─┐
│ 1 │ 2 │ 3 │
│ 3 │ 2 │ 1 │
└──────────┴──────────┴─────────┘Предположим, у нас есть несколько файлов со следующими URI на S3:
Подсчитаем количество строк в файлах, заканчивающихся цифрами от 1 до 3:
SELECT count(*)
FROM s3('https://storage.yandexcloud.net/my-test-bucket-768/{some,another}_prefix/some_file_{1..3}.csv', 'CSV', 'name String, value UInt32');
┌─count()─┐
│ 18 │
└──────────┘Подсчитаем общее количество строк во всех файлах этих двух каталогов:
SELECT count(*)
FROM s3('https://storage.yandexcloud.net/my-test-bucket-768/{some,another}_prefix/*', 'CSV', 'name String, value UInt32');
┌─count()─┐
│ 24 │
└──────────┘
ПРЕДУПРЕЖДЕНИЕ: Если список файлов содержит диапазоны чисел с ведущими нулями, используйте конструкцию с фигурными скобками для каждой цифры отдельно или используйте `?`.
Подсчитаем общее количество строк в файлах с именами file-000.csv, file-001.csv, … , file-999.csv:
SELECT count(*)
FROM s3('https://storage.yandexcloud.net/my-test-bucket-768/big_prefix/file-{000..999}.csv', 'CSV', 'name String, value UInt32');
┌─count()─┐
│ 12 │
└──────────┘Запишем данные в файл test-data.csv.gz:
INSERT INTO FUNCTION s3('https://storage.yandexcloud.net/my-test-bucket-768/test-data.csv.gz', 'CSV', 'name String, value UInt32', 'gzip')
VALUES ('test-data', 1), ('test-data-2', 2);Запишем данные из существующей таблицы в файл test-data.csv.gz:
INSERT INTO FUNCTION s3('https://storage.yandexcloud.net/my-test-bucket-768/test-data.csv.gz', 'CSV', 'name String, value UInt32', 'gzip')
SELECT name, value FROM existing_table;Если при добавлении данных в таблицу S3 указать выражение PARTITION BY, то для каждого значения ключа партиционирования создается отдельный файл. Это повышает эффективность операций чтения.
Примеры:
1. При использовании ID партиции в имени ключа создаются отдельные файлы:
INSERT INTO TABLE FUNCTION
s3('http://bucket.amazonaws.com/my_bucket/file_{_partition_id}.csv', 'CSV', 'a UInt32, b UInt32, c UInt32')
PARTITION BY a VALUES ('x', 2, 3), ('x', 4, 5), ('y', 11, 12), ('y', 13, 14), ('z', 21, 22), ('z', 23, 24);В результате данные будут записаны в три файла: file_x.csv, file_y.csv и file_z.csv.
2. При использовании ID партиции в названии бакета создаются файлы в разных бакетах:
INSERT INTO TABLE FUNCTION
s3('http://bucket.amazonaws.com/my_bucket_{_partition_id}/file.csv', 'CSV', 'a UInt32, b UInt32, c UInt32')
PARTITION BY a VALUES (1, 2, 3), (1, 4, 5), (10, 11, 12), (10, 13, 14), (20, 21, 22), (20, 23, 24);В результате будут созданы три файла в разных бакетах: my_bucket_1/file.csv, my_bucket_10/file.csv и my_bucket_20/file.csv.
Смотрите также:
input(structure) - табличная функция, позволяющая эффективно преобразовывать и вставлять отправленные на сервер данные, имеющие структуру structure, в таблицу другой структуры.
structure - структура отправляемых на сервер данных в формате 'column1_name column1_type, column2_name column2_type, ...'. Например: 'id UInt32, name String'.
Данная функция может быть использована только в запросе INSERT SELECT и только один раз, но в остальном ведет себя как обычная табличная функция (можно указать в подзапросе и т.д.).
Данные можно отправлять любым стандартным способом как для обычного INSERT запроса и в любом доступном формате, который указывается в конце запроса (в отличие от обычного INSERT SELECT).
Главная особенность данной функции в том, что сервер при получении данных от клиента одновременно преобразует их в соответствии со списком выражений в SELECT части и вставляет в целевую таблицу. Временная таблица со всеми переданными данными не создается.
Примеры:
test следующая структура (a String, b String), а в файле data.csv данные имеют другую структуру (col1 String, col2 Date, col3 Int32). Запрос для вставки данных из файла data.csv в таблицу test с одновременным преобразованием и использованием функций выглядит так:$ cat data.csv | widestore-client --query="INSERT INTO test SELECT lower(col1), col3 * col3 FROM input('col1 String, col2 Date, col3 Int32') FORMAT CSV";
data.csv лежат данные той же структуры test_structure, что и у таблицы test, то следующие два запроса эквивалентны:$ cat data.csv | widestore-client --query="INSERT INTO test FORMAT CSV"
$ cat data.csv | widestore-client --query="INSERT INTO test SELECT * FROM input('test_structure') FORMAT CSV"Генерирует случайные данные с заданной схемой. Позволяет заполнять тестовые таблицы данными. Поддерживает все типы данных, которые могут храниться в таблице, за исключением LowCardinality и AggregateFunction.
generateRandom('name TypeName[, name TypeName]...', [, 'random_seed'[, 'max_string_length'[, 'max_array_length']]])Аргументы:
name — название соответствующего столбца.TypeName — тип соответствующего столбца.max_array_length — максимальная длина массива для всех сгенерированных массивов. По умолчанию 10.max_string_length — максимальная длина строки для всех генерируемых строк. По умолчанию 10.random_seed — укажите состояние генератора случайных чисел вручную, чтобы получить стабильные результаты. Если значение равно NULL - генератор инициализируется случайным состоянием.Возвращаемое значение:
Объект таблицы с запрошенной схемой.
Пример:
SELECT * FROM generateRandom('a Array(Int8), d Decimal32(4), c Tuple(DateTime64(3), UUID)', 1, 10, 2) LIMIT 3;
┌─a────────┬──────────────d─┬─c────────────────────────────────────────────────────────────────────────┐
│ [77] │ -124167.6723 │ ('2061-04-17 21:59:44.573','3f72f405-ec3e-13c8-44ca-66ef335f7835') │
│ [32,110] │ -141397.7312 │ ('1979-02-09 03:43:48.526','982486d1-5a5d-a308-e525-7bd8b80ffa73') │
│ [68] │ -67417.0770 │ ('2080-03-12 14:17:31.269','110425e5-413f-10a6-05ba-fa6b3e929f15') │
└──────────┴────────────────┴──────────────────────────────────────────────────────────────────────────┘Позволяет обратиться ко всем шардам существующего кластера, который сконфигурирован в секции remote_servers без создания таблицы типа Distributed. В запросе используется одна реплика каждого шарда.
Функция clusterAllReplicas работает также как cluster, но каждая реплика в кластере используется как отдельный шард/отдельное соединение.
Примечание: Все доступные кластеры перечислены в таблице [system.clusters](/docs/ru/operations/system-tables/clusters).
Синтаксис:
cluster('cluster_name', db.table[, sharding_key])
cluster('cluster_name', db, table[, sharding_key])
clusterAllReplicas('cluster_name', db.table[, sharding_key])
clusterAllReplicas('cluster_name', db, table[, sharding_key])Аргументы:
cluster_name – имя кластера, который обозначает подмножество адресов и параметров подключения к удаленным и локальным серверам, входящим в кластер.db.table или db, table - имя базы данных и таблицы.sharding_key - ключ шардирования. Необязательный аргумент. Указывается, если данные добавляются более чем в один шард кластера.Возвращаемое значение:
Набор данных из кластеров.
Использование макросов:
cluster_name может содержать макрос — подстановку в фигурных скобках. Эта подстановка заменяется на соответствующее значение из секции macros конфигурационного файла .
Пример:
SELECT * FROM cluster('{cluster}', default.example_table);Использование и рекомендации:
Использование табличных функций cluster и clusterAllReplicas менее оптимально, чем создание таблицы типа Distributed, поскольку в этом случае при каждом новом запросе устанавливается новое соединение с сервером. При обработке большого количества запросов всегда создавайте Distributed таблицу заранее и не используйте табличные функции cluster и clusterAllReplicas.
Табличные функции cluster and clusterAllReplicas могут быть полезны в следующих случаях:
Настройки соединения user, password, host, post, compression, secure берутся из секции <remote_servers> файлов конфигурации. См. подробности в разделе Distributed
См. также:
Создает временную таблицу указанной структуры с движком Null. В соответствии со свойствами движка, данные в таблице игнорируются, а сама таблица удаляется сразу после выполнения запроса. Функция используется для удобства написания тестов и демонстрационных примеров.
Синтаксис:
null('structure')Параметр:
structure — список колонок и их типов. String.Возвращаемое значение:
Временная таблица указанной структуры с движком Null.
Пример:
Один запрос с функцией null:
INSERT INTO function null('x UInt64') SELECT * FROM numbers_mt(1000000000);заменяет три запроса:
CREATE TABLE t (x UInt64) ENGINE = Null;
INSERT INTO t SELECT * FROM numbers_mt(1000000000);
DROP TABLE IF EXISTS t;См. также:
Отображает данные словаря как таблицу RT.WideStore. Работает аналогично движку Dictionary.
Синтаксис:
dictionary('dict')Аргументы:
dict — имя словаря. String.Возвращаемое значение:
Таблица RT.WideStore.
Пример:
Входная таблица dictionary_source_table:
┌─id─┬─value─┐
│ 0 │ 0 │
│ 1 │ 1 │
└────┴────────┘Создаем словарь:
CREATE DICTIONARY new_dictionary(id UInt64, value UInt64 DEFAULT 0) PRIMARY KEY id
SOURCE(WIDESTORE(HOST 'localhost' PORT tcpPort() USER 'default' TABLE 'dictionary_source_table')) LAYOUT(DIRECT());Запрос:
SELECT * FROM dictionary('new_dictionary');Результат:
┌─id─┬─value─┐
│ 0 │ 0 │
│ 1 │ 1 │
└────┴────────┘Смотрите также:
Позволяет обрабатывать файлы из Amazon S3 параллельно из многих узлов в указанном кластере. На узле-инициаторе функция создает соединение со всеми узлами в кластере, заменяет символы '*' в пути к файлу S3 и динамически отправляет каждый файл. На рабочем узле функция запрашивает у инициатора следующую задачу и обрабатывает ее. Это повторяется до тех пор, пока все задачи не будут завершены.
Синтаксис:
s3Cluster(cluster_name, source, [,access_key_id, secret_access_key] [,format] [,structure])Аргументы:
cluster_name — имя кластера, используемое для создания набора адресов и параметров подключения к удаленным и локальным серверам.source — URL файла или нескольких файлов. Поддерживает следующие символы подстановки: *, ?, {'abc','def'} и {N..M}, где N, M — числа, abc, def — строки. Подробнее смотрите в разделе Символы подстановки.access_key_id и secret_access_key — ключи, указывающие на учетные данные для использования с точкой приема запроса. Необязательные параметры.format — формат файла.structure — структура таблицы. Формат 'column1_name column1_type, column2_name column2_type, ...'.Возвращаемое значение:
Таблица с указанной структурой для чтения или записи данных в указанный файл.
Примеры:
Вывод данных из всех файлов кластера cluster_simple:
SELECT * FROM s3Cluster('cluster_simple', 'http://minio1:9001/root/data/{widestore,database}/*',
'minio', 'minio123', 'CSV', 'name String, value UInt32, polygon Array(Array(Tuple(Float64, Float64)))')
ORDER BY (name, value, polygon);Подсчет общего количества строк во всех файлах кластера cluster_simple:
SELECT count(*)
FROM s3Cluster('cluster_simple', 'http://minio1:9001/root/data/{widestore,database}/*',
'minio', 'minio123', 'CSV', 'name String, value UInt32, polygon Array(Array(Tuple(Float64, Float64)))');
ВНИМАНИЕ: Если список файлов содержит диапазоны чисел с ведущими нулями, используйте конструкцию с фигурными скобками для каждой цифры отдельно или используйте `?`.
Смотрите также:
Позволяет выполнять запросы к данным, хранящимся в базе данных SQLite.
Синтаксис:
sqlite('db_path', 'table_name')Аргументы:
db_path — путь к файлу с базой данных SQLite. String.table_name — имя таблицы в базе данных SQLite. String.Возвращаемое значение:
SQLite.Пример:
Запрос:
SELECT * FROM sqlite('sqlite.db', 'table1') ORDER BY col2;Результат:
┌─col1──┬─col2─┐
│ line1 │ 1 │
│ line2 │ 2 │
│ line3 │ 3 │
└───────┴───────┘См. также:
Извлекает структуру таблицы из данных и анализирует ее в соответствии с указанным форматом ввода.
Синтаксис:
format(format_name, data)Параметры
format_name— Формат данных.data— Строковый литерал или константное выражение, которое возвращает строку, содержащую данные в указанном формате.Возвращаемое значение:
Таблица с данными, проанализированными из dataаргумента в соответствии с указанным форматом и извлеченной схемой.
Примеры:
Запрос:
SELECT * FROM format(JSONEachRow,
$$
{"a": "Hello", "b": 111}
{"a": "World", "b": 123}
{"a": "Hello", "b": 112}
{"a": "World", "b": 124}
$$)Результат:
┌───b─┬─a─────┐
│ 111 │ Hello │
│ 123 │ World │
│ 112 │ Hello │
│ 124 │ World │
└─────┴────────┘Запрос:
DESC format(JSONEachRow,
$$
{"a": "Hello", "b": 111}
{"a": "World", "b": 123}
{"a": "Hello", "b": 112}
{"a": "World", "b": 124}
$$)Результат:
┌─name─┬─type───────────────┬─default_type─┬─default_expression──┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ b │ Nullable(Float64) │ │ │ │ │ │
│ a │ Nullable(String) │ │ │ │ │ │
└──────┴─────────────────────┴────────────────┴─────────────────────┴──────────┴────────────────────┴─────────────────┘Смотрите также:
Преобразовывает подзапрос в таблицу. Функция реализовывает представления (смотрите CREATE VIEW). Результирующая таблица не хранит данные, а только сохраняет указанный запрос SELECT. При чтении из таблицы, RT.WideStore выполняет запрос и удаляет все ненужные столбцы из результата.
Синтаксис:
view(subquery)Аргументы:
subquery — запрос SELECT.Возвращаемое значение:
Пример:
Входная таблица:
┌─id─┬─name─────┬─days─┐
│ 1 │ January │ 31 │
│ 2 │ February │ 29 │
│ 3 │ March │ 31 │
│ 4 │ April │ 30 │
└────┴───────────┴───────┘Запрос:
SELECT * FROM view(SELECT name FROM months);Результат:
┌─name─────┐
│ January │
│ February │
│ March │
│ April │
└───────────┘Вы можете использовать функцию view как параметр табличных функций remote и cluster:
SELECT * FROM remote(`127.0.0.1`, view(SELECT a, b, c FROM table_name));
SELECT * FROM cluster(`cluster_name`, view(SELECT a, b, c FROM table_name));Смотрите также:
В Яндекс.Метрике пользователями передаётся JSON в качестве параметров визитов. Для работы с таким JSON-ом, реализованы некоторые функции. (Хотя в большинстве случаев, JSON-ы дополнительно обрабатываются заранее, и полученные значения кладутся в отдельные столбцы в уже обработанном виде.) Все эти функции исходят из сильных допущений о том, каким может быть JSON, и при этом стараются почти ничего не делать.
Делаются следующие допущения:
visitParamHas('{"abc":"def"}', 'abc') = 1, но visitParamHas('{"\\u0061\\u0062\\u0063":"def"}', 'abc') = 0Проверяет наличие поля с именем name.
Синоним: simpleJSONHas.
Пытается выделить число типа UInt64 из значения поля с именем name. Если поле строковое, пытается выделить число из начала строки. Если такого поля нет, или если оно есть, но содержит не число, то возвращает 0.
Синоним: simpleJSONExtractUInt.
Аналогично для Int64.
Синоним: simpleJSONExtractInt.
Аналогично для Float64.
Синоним: simpleJSONExtractFloat.
Пытается выделить значение true/false. Результат — UInt8.
Синоним: simpleJSONExtractBool.
Возвращает значение поля, включая разделители.
Синоним: simpleJSONExtractRaw.
Примеры:
visitParamExtractRaw('{"abc":"\\n\\u0000"}', 'abc') = '"\\n\\u0000"';
visitParamExtractRaw('{"abc":{"def":[1,2,3]}}', 'abc') = '{"def":[1,2,3]}';Разбирает строку в двойных кавычках. У значения убирается экранирование. Если убрать экранированные символы не удалось, то возвращается пустая строка.
Синоним: simpleJSONExtractString.
Примеры:
visitParamExtractString('{"abc":"\\n\\u0000"}', 'abc') = '\n\0';
visitParamExtractString('{"abc":"\\u263a"}', 'abc') = '☺';
visitParamExtractString('{"abc":"\\u263"}', 'abc') = '';
visitParamExtractString('{"abc":"hello}', 'abc') = '';На данный момент не поддерживаются записанные в формате \uXXXX\uYYYY кодовые точки не из basic multilingual plane (они переводятся не в UTF-8, а в CESU-8).
Следующие функции используют simdjson, который разработан под более сложные требования для разбора JSON. Упомянутое выше допущение 2 по-прежнему применимо.
Проверяет, является ли переданная строка валидным json значением.
Примеры:
SELECT isValidJSON('{"a": "hello", "b": [-100, 200.0, 300]}') = 1
SELECT isValidJSON('not a json') = 0Если значение существует в документе JSON, то возвращается 1.
Если значение не существует, то возвращается 0.
Примеры:
SELECT JSONHas('{"a": "hello", "b": [-100, 200.0, 300]}', 'b') = 1
SELECT JSONHas('{"a": "hello", "b": [-100, 200.0, 300]}', 'b', 4) = 0indices_or_keys — это список из нуля или более аргументов каждый из них может быть либо строкой либо целым числом.
Адресация элементов по индексу начинается с 1, следовательно элемент 0 не существует.
Вы можете использовать целые числа, чтобы адресовать как массивы JSON, так и JSON-объекты.
Примеры:
SELECT JSONExtractKey('{"a": "hello", "b": [-100, 200.0, 300]}', 1) = 'a'
SELECT JSONExtractKey('{"a": "hello", "b": [-100, 200.0, 300]}', 2) = 'b'
SELECT JSONExtractKey('{"a": "hello", "b": [-100, 200.0, 300]}', -1) = 'b'
SELECT JSONExtractKey('{"a": "hello", "b": [-100, 200.0, 300]}', -2) = 'a'
SELECT JSONExtractString('{"a": "hello", "b": [-100, 200.0, 300]}', 1) = 'hello'Возвращает длину массива JSON или объекта JSON.
Если значение не существует или имеет неверный тип, то возвращается 0.
Примеры:
SELECT JSONLength('{"a": "hello", "b": [-100, 200.0, 300]}', 'b') = 3
SELECT JSONLength('{"a": "hello", "b": [-100, 200.0, 300]}') = 2Возвращает тип значения JSON.
Если значение не существует, то возвращается Null.
Примеры:
SELECT JSONType('{"a": "hello", "b": [-100, 200.0, 300]}') = 'Object'
SELECT JSONType('{"a": "hello", "b": [-100, 200.0, 300]}', 'a') = 'String'
SELECT JSONType('{"a": "hello", "b": [-100, 200.0, 300]}', 'b') = 'Array'Парсит JSON и извлекает значение. Эти функции аналогичны функциям visitParam.
Если значение не существует или имеет неверный тип, то возвращается 0.
Примеры:
SELECT JSONExtractInt('{"a": "hello", "b": [-100, 200.0, 300]}', 'b', 1) = -100
SELECT JSONExtractFloat('{"a": "hello", "b": [-100, 200.0, 300]}', 'b', 2) = 200.0
SELECT JSONExtractUInt('{"a": "hello", "b": [-100, 200.0, 300]}', 'b', -1) = 300Парсит JSON и извлекает строку. Эта функция аналогична функции visitParamExtractString.
Если значение не существует или имеет неверный тип, то возвращается пустая строка.
У значения убирается экранирование. Если убрать экранированные символы не удалось, то возвращается пустая строка.
Примеры:
SELECT JSONExtractString('{"a": "hello", "b": [-100, 200.0, 300]}', 'a') = 'hello'
SELECT JSONExtractString('{"abc":"\\n\\u0000"}', 'abc') = '\n\0'
SELECT JSONExtractString('{"abc":"\\u263a"}', 'abc') = '☺'
SELECT JSONExtractString('{"abc":"\\u263"}', 'abc') = ''
SELECT JSONExtractString('{"abc":"hello}', 'abc') = ''Парсит JSON и извлекает значение с заданным типом данных.
Это обобщение предыдущих функций JSONExtract<type>. Это означает JSONExtract(..., 'String') выдает такой же результат, как JSONExtractString(), JSONExtract(..., 'Float64') выдает такой же результат, как JSONExtractFloat().
Примеры:
SELECT JSONExtract('{"a": "hello", "b": [-100, 200.0, 300]}', 'Tuple(String, Array(Float64))') = ('hello',[-100,200,300])
SELECT JSONExtract('{"a": "hello", "b": [-100, 200.0, 300]}', 'Tuple(b Array(Float64), a String)') = ([-100,200,300],'hello')
SELECT JSONExtract('{"a": "hello", "b": [-100, 200.0, 300]}', 'b', 'Array(Nullable(Int8))') = [-100, NULL, NULL]
SELECT JSONExtract('{"a": "hello", "b": [-100, 200.0, 300]}', 'b', 4, 'Nullable(Int64)') = NULL
SELECT JSONExtract('{"passed": true}', 'passed', 'UInt8') = 1
SELECT JSONExtract('{"day": "Thursday"}', 'day', 'Enum8(\'Sunday\' = 0, \'Monday\' = 1, \'Tuesday\' = 2, \'Wednesday\' = 3, \'Thursday\' = 4, \'Friday\' = 5, \'Saturday\' = 6)') = 'Thursday'
SELECT JSONExtract('{"day": 5}', 'day', 'Enum8(\'Sunday\' = 0, \'Monday\' = 1, \'Tuesday\' = 2, \'Wednesday\' = 3, \'Thursday\' = 4, \'Friday\' = 5, \'Saturday\' = 6)') = 'Friday'Разбор пар ключ-значение из JSON, где значение имеет тип данных RT.WideStore.
Пример:
SELECT JSONExtractKeysAndValues('{"x": {"a": 5, "b": 7, "c": 11}}', 'x', 'Int8') = [('a',5),('b',7),('c',11)];Парсит строку JSON и извлекает ключи.
Синтаксис
JSONExtractKeys(json[, a, b, c...])Аргументы
json — строка, содержащая валидный JSON.a, b, c... — индексы или ключи, разделенные запятыми, которые указывают путь к внутреннему полю во вложенном объекте JSON. Каждый аргумент может быть либо строкой для получения поля по ключу, либо целым числом для получения N-го поля (индексирование начинается с 1, отрицательные числа используются для отсчета с конца). Если параметр не задан, весь JSON разбирается как объект верхнего уровня. Необязательный параметр.Возвращаемые значения
Массив с ключами JSON.
Пример
Запрос:
SELECT JSONExtractKeys('{"a": "hello", "b": [-100, 200.0, 300]}');Результат:
text
┌─JSONExtractKeys('{"a": "hello", "b": [-100, 200.0, 300]}')─┐
│ ['a','b'] │
└─────────────────────────────────────────────────────────────────┘Возвращает часть JSON в виде строки, содержащей неразобранную подстроку.
Если значение не существует, то возвращается пустая строка.
Пример:
SELECT JSONExtractRaw('{"a": "hello", "b": [-100, 200.0, 300]}', 'b') = '[-100, 200.0, 300]';Возвращает массив из элементов JSON массива, каждый из которых представлен в виде строки с неразобранными подстроками из JSON.
Если значение не существует или не является массивом, то возвращается пустой массив.
Пример:
SELECT JSONExtractArrayRaw('{"a": "hello", "b": [-100, 200.0, "hello"]}', 'b') = ['-100', '200.0', '"hello"']';Извлекает необработанные данные из объекта JSON.
Синтаксис
JSONExtractKeysAndValuesRaw(json[, p, a, t, h])Аргументы
json — строка, содержащая валидный JSON.p, a, t, h — индексы или ключи, разделенные запятыми, которые указывают путь к внутреннему полю во вложенном объекте JSON. Каждый аргумент может быть либо строкой для получения поля по ключу, либо целым числом для получения N-го поля (индексирование начинается с 1, отрицательные числа используются для отсчета с конца). Если параметр не задан, весь JSON парсится как объект верхнего уровня. Необязательный параметр.Возвращаемые значения
Массив с кортежами ('key', 'value'). Члены кортежа — строки.
Пустой массив, если заданный объект не существует или входные данные не валидный JSON.
Тип: Array(Tuple(String, String).
Примеры
Запрос:
SELECT JSONExtractKeysAndValuesRaw('{"a": [-100, 200.0], "b":{"c": {"d": "hello", "f": "world"}}}');Результат:
┌─JSONExtractKeysAndValuesRaw('{"a": [-100, 200.0], "b":{"c": {"d": "hello", "f": "world"}}}')─┐
│ [('a','[-100,200]'),('b','{"c":{"d":"hello","f":"world"}}')] │
└──────────────────────────────────────────────────────────────────────────────────────────────────────┘Запрос:
SELECT JSONExtractKeysAndValuesRaw('{"a": [-100, 200.0], "b":{"c": {"d": "hello", "f": "world"}}}', 'b');Результат:
┌─JSONExtractKeysAndValuesRaw('{"a": [-100, 200.0], "b":{"c": {"d": "hello", "f": "world"}}}', 'b')─┐
│ [('c','{"d":"hello","f":"world"}')] │
└────────────────────────────────────────────────────────────────────────────────────────────────────────────┘Запрос:
SELECT JSONExtractKeysAndValuesRaw('{"a": [-100, 200.0], "b":{"c": {"d": "hello", "f": "world"}}}', -1, 'c');Результат:
┌─JSONExtractKeysAndValuesRaw('{"a": [-100, 200.0], "b":{"c": {"d": "hello", "f": "world"}}}', -1, 'c')─┐
│ [('d','"hello"'),('f','"world"')] │
└────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘Если значение существует в документе JSON, то возвращается 1.
Если значение не существует, то возвращается 0.
Пример:
SELECT JSON_EXISTS('{"hello":1}', '$.hello');
SELECT JSON_EXISTS('{"hello":{"world":1}}', '$.hello.world');
SELECT JSON_EXISTS('{"hello":["world"]}', '$.hello[*]');
SELECT JSON_EXISTS('{"hello":["world"]}', '$.hello[0]');
ПРИМЕЧАНИЕ:
до версии 21.11 порядок аргументов функции был обратный, т.е. JSON_EXISTS(path, json)
Парсит JSON и извлекает значение как JSON массив или JSON объект.
Если значение не существует, то возвращается пустая строка.
Пример:
SELECT JSON_QUERY('{"hello":"world"}', '$.hello');
SELECT JSON_QUERY('{"array":[[0, 1, 2, 3, 4, 5], [0, -1, -2, -3, -4, -5]]}', '$.array[*][0 to 2, 4]');
SELECT JSON_QUERY('{"hello":2}', '$.hello');
SELECT toTypeName(JSON_QUERY('{"hello":2}', '$.hello'));Результат:
["world"]
[0, 1, 4, 0, -1, -4]
[2]
String
ПРИМЕЧАНИЕ:
до версии 21.11 порядок аргументов функции был обратный, т.е. JSON_QUERY(path, json)
Парсит JSON и извлекает значение как JSON скаляр.
Если значение не существует, то возвращается пустая строка.
Пример:
SELECT JSON_VALUE('{"hello":"world"}', '$.hello');
SELECT JSON_VALUE('{"array":[[0, 1, 2, 3, 4, 5], [0, -1, -2, -3, -4, -5]]}', '$.array[*][0 to 2, 4]');
SELECT JSON_VALUE('{"hello":2}', '$.hello');
SELECT toTypeName(JSON_VALUE('{"hello":2}', '$.hello'));Результат:
world
0
2
String
ПРИМЕЧАНИЕ:
до версии 21.11 порядок аргументов функции был обратный, т.е. JSON_VALUE(path, json)
Сериализует значение в JSON представление. Поддерживаются различные типы данных и вложенные структуры. По умолчанию 64-битные целые числа и более (например, UInt64 или Int128) заключаются в кавычки. Настройка output_format_json_quote_64bit_integers управляет этим поведением. Специальные значения NaN и inf заменяются на null. Чтобы они отображались, включите настройку output_format_json_quote_denormals. Когда сериализуется значение Enum, то функция выводит его имя.
Синтаксис
toJSONString(value)Аргументы
value — значение, которое необходимо сериализовать. Может быть любого типа.Возвращаемое значение
Тип: String.
Пример
Первый пример показывает сериализацию Map. Во втором примере есть специальные значения, обернутые в Tuple.
Запрос:
SELECT toJSONString(map('key1', 1, 'key2', 2));
SELECT toJSONString(tuple(1.25, NULL, NaN, +inf, -inf, [])) SETTINGS output_format_json_quote_denormals = 1;Результат:
{"key1":1,"key2":2}
[1.25,null,"nan","inf","-inf",[]]Смотрите также