INFORMATION_SCHEMA и Oracle
Информационная схема ( INFORMATION_SCHEMA ) является стандартным представлением метаданных в языке SQL. Исторически каждый производитель реляционных СУБД предоставлял системные таблицы, которые содержали мета-информацию — имена таблиц, столбцов, ограничений, типы данных столбцов и т.д. Структура и состав системных таблиц могут меняться в разных версиях продукта, однако поддержка информационной схемы дает возможность менять структуру системных таблиц без изменения способа доступа к метаданным. Другим преимуществом применения INFORMATION_SCHEMA является то, что запросы к метаданным не зависят от используемой СУБД.
Из ведущих производителей, пожалуй, только Oracle не поддерживает INFORMATION_SCHEMA. Справедливости ради следует сказать, что Oracle предоставляет возможность использовать системные представления вместо непосредственного обращения к системным таблицам, что также позволяет безопасно изменять структуру системных таблиц.
Приведем ряд типичных запросов в стандартном варианте и для Oracle.
1. Список таблиц базы данных (текущей)
Консоль
Выполнить
Как в запросе выбрать данные только из партиции с указанием ее имени?
Мне известно название партиции, которое находится в таблице all_tab_partition .
Название партиции: DF_387 . Как мне выбрать данные именно из этой партиции? Запрос вида:
select * from tm420.tbl_1 partition(DF_387) выдает ошибку:
ORA-14109: имена объектов с указанием секций могут использоваться только для таблиц и редактируемых представлений
В документации не могу найти примеры обращения к секциям таблицы.
Отслеживать
51.6k 201 201 золотой знак 63 63 серебряных знака 245 245 бронзовых знаков
задан 25 мая 2018 в 12:45
Анатолий Эрнст Анатолий Эрнст
927 1 1 золотой знак 8 8 серебряных знаков 27 27 бронзовых знаков
Покажите скрипт создания таблицы
25 мая 2018 в 13:07
Нет возможности сейчас проверить. Попробуйте обернуть имя партиции в двойные или одинарные кавычки. Но по хорошему просто условие писать так, чтобы оракл понял, что надо в конкретную партицию попасть
25 мая 2018 в 13:10
1 ответ 1
Сортировка: Сброс на вариант по умолчанию
Синтаксис partition(DF_387) вполне правильнный (partition_extension_clause).
Но так обращаться к партиции можно, как и сказано в сообщении об ошибке, только если основной объект таблица или редактируемое представление. Указанный в вопросе объект tm420.tbl_1 не является таблицей.
create table tab (id number, name varchar2(10)) partition by range (id) ( partition p1 values less than (1000) ); create or replace view tab_1 as select * from tab; Так будет работать:
select * from tab partition (p1); select * from tab_1 partition (p1); ORA-14109: partition-extended object names may only be used with tables and editioning views
В этом случае, надо заменить на редактируемое представление, которое будет работать:
drop view tab_1; create or replace editioning view tab_1 as select * from tab; Оконные функции SQL простым языком с примерами
Сразу хочется отметить, что данная статья написана исключительно для людей, начинающих свой путь в изучении SQL и оконных функций. Здесь могут быть не разобраны сложные применения функций и могут не использоваться сложные формулировки определений — все написано максимально простым языком для базового понимания.
P.S. Если автор что-то не разобрал и не написал, значит он посчитал это не обязательным в рамках этой статьи)))
Для примеров будем использовать небольшую таблицу, которая показывает оценки учеников по разным предметам. В БД табличка выглядит следующим образом
--создание таблицы create table student_grades ( name varchar, subject varchar, grade int); -- наполнение таблицы данными insert into student_grades ( values ('Петя', 'русский', 4), ('Петя', 'физика', 5), ('Петя', 'история', 4), ('Маша', 'математика', 4), ('Маша', 'русский', 3), ('Маша', 'физика', 5), ('Маша', 'история', 3)); --запрос всех данных из таблицы select * from student_grades;
SQL часто используется для вычислений в данных различных метрик или агрегаций значений по измерениям. Помимо функций агрегации для этого широко используются оконные функции.
Оконная функция в SQL — функция, которая работает с выделенным набором строк (окном, партицией) и выполняет вычисление для этого набора строк в отдельном столбце.
Партиции (окна из набора строк) — это набор строк, указанный для оконной функции по одному из столбцов или группе столбцов таблицы. Партиции для каждой оконной функции в запросе могут быть разделены по различным колонкам таблицы.
В чем заключается главное отличие оконных функций от функций агрегации с группировкой?
При использовании агрегирующих функций предложение GROUP BY сокращает количество строк в запросе с помощью их группировки.
При использовании оконных функций количество строк в запросе не уменьшается по сравнении с исходной таблицей.
Порядок расчета оконных функций в SQL запросе
Сначала выполняется команда выборки таблиц, их объединения и возможные подзапросы под командой FROM.
Далее выполняются условия фильтрации WHERE, группировки GROUP BY и возможная фильтрация c HAVING
Только потом применяется команда выборки столбцов SELECT и расчет оконных функций под выборкой.
После этого идет условие сортировки ORDER BY, где тоже можно указать столбец расчета оконной функции для сортировки.
Здесь важно уточнить, что партиции или окна оконных функций создаются после разделения таблицы на группы с помощью команды GROUP BY, если эта команда используется в запросе.
Синтаксис оконных функций
Синтаксис оконных функций вне зависимости от их класса будет так или иначе состоять из идентичных команд.
Оконные функции можно прописывать как под командой SELECT, так и в отдельном ключевом слове WINDOW, где окну дается алиас (псевдоним), к которому можно обращаться в SELECT выборке.
Классы Оконных функций
Множество оконных функций можно разделять на 3 класса:
- Агрегирующие (Aggregate)
- Ранжирующие (Ranking)
- Функции смещения (Value)
Агрегирующие:
Можно применять любую из агрегирующих функций — SUM, AVG, COUNT, MIN, MAX
select name, subject, grade, sum(grade) over (partition by name) as sum_grade, avg(grade) over (partition by name) as avg_grade, count(grade) over (partition by name) as count_grade, min(grade) over (partition by name) as min_grade, max(grade) over (partition by name) as max_grade from student_grades;
Ранжирующие:
В ранжирующих функция под ключевым словом OVER обязательным идет указание условия ORDER BY, по которому будет происходить сортировка ранжирования.
ROW_NUMBER() — функция вычисляет последовательность ранг (порядковый номер) строк внутри партиции, НЕЗАВИСИМО от того, есть ли в строках повторяющиеся значения или нет.
RANK() — функция вычисляет ранг каждой строки внутри партиции. Если есть повторяющиеся значения, функция возвращает одинаковый ранг для таких строчек, пропуская при этом следующий числовой ранг.
DENSE_RANK() — то же самое что и RANK, только в случае одинаковых значений DENSE_RANK не пропускает следующий числовой ранг, а идет последовательно.
select name, subject, grade, row_number() over (partition by name order by grade desc), rank() over (partition by name order by grade desc), dense_rank() over (partition by name order by grade desc) from student_grades;
Про NULL в случае ранжирования:
Для SQL пустые NULL значения будут определяться одинаковым рангом
Функции смещения:
Это функции, которые позволяют перемещаясь по выделенной партиции таблицы обращаться к предыдущему значению строки или крайним значениям строк в партиции.
LAG() — функция, возвращающая предыдущее значение столбца по порядку сортировки.
LEAD() — функция, возвращающая следующее значение столбца по порядку сортировки.
На простом примере видно, как можно в одной строке получить текущую оценку, предыдущую и следующую оценки Пети в четвертях.
--создание таблицы create table grades_quartal ( name varchar, quartal varchar, subject varchar, grade int); --наполнение таблицы данными insert into grades_quartal ( values ('Петя', '1 четверть', 'физика', 4), ('Петя', '2 четверть', 'физика', 3), ('Петя', '3 четверть', 'физика', 4), ('Петя', '4 четверть', 'физика', 5) ); --запрос всех данных из таблицы select * from grades_quartal;
select name, quartal, subject, grade, lag(grade) over (order by quartal) as previous_grade, lead(grade) over (order by quartal) as next_grade from grades_quartal;
FIRST_VALUE()/LAST_VALUE() — функции возвращающие первое или последнее значение столбца в указанной партиции. В качестве аргумента указывает столбец, значение которого нужно вернуть. В оконной функции под словом OVER обязательное указание ORDER BY условия.
В следующей версии статьи разберем отдельно такое понятие как фрейм окна функции или window frame и рассмотрим на простых примерах как он используется.
Манипуляции с партициями и кусками
Для работы с партициями доступны следующие операции:
- DETACH PARTITION — перенести партицию в директорию detached ;
- DROP PARTITION — удалить партицию;
- ATTACH PARTITION | PART — добавить партицию/кусок в таблицу из директории detached ;
- ATTACH PARTITION FROM — скопировать партицию из другой таблицы;
- REPLACE PARTITION — скопировать партицию из другой таблицы с заменой;
- MOVE PARTITION TO TABLE — переместить партицию в другую таблицу;
- CLEAR COLUMN IN PARTITION — удалить все значения в столбце для заданной партиции;
- CLEAR INDEX IN PARTITION — очистить построенные вторичные индексы для заданной партиции;
- FREEZE PARTITION — создать резервную копию партиции;
- UNFREEZE PARTITION — удалить резервную копию партиции;
- FETCH PARTITION | PART — скачать партицию/кусок с другого сервера;
- MOVE PARTITION | PART — переместить партицию/кускок на другой диск или том.
- UPDATE IN PARTITION — обновить данные внутри партиции по условию.
- DELETE IN PARTITION — удалить данные внутри партиции по условию.
DETACH PARTITION | PART
ALTER TABLE table_name [ON CLUSTER cluster] DETACH PARTITION|PART partition_expr Перемещает заданную партицию в директорию detached . Сервер не будет знать об этой партиции до тех пор, пока вы не выполните запрос ATTACH.
ALTER TABLE mt DETACH PARTITION '2020-11-21'; ALTER TABLE mt DETACH PART 'all_2_2_0'; Подробнее о том, как корректно задать имя партиции, см. в разделе Как задавать имя партиции в запросах ALTER.
После того как запрос будет выполнен, вы сможете производить любые операции с данными в директории detached . Например, можно удалить их из файловой системы.
Запрос реплицируется — данные будут перенесены в директорию detached и забыты на всех репликах. Обратите внимание, запрос может быть отправлен только на реплику-лидер. Чтобы узнать, является ли реплика лидером, выполните запрос SELECT к системной таблице system.replicas. Либо можно выполнить запрос DETACH на всех репликах — тогда на всех репликах, кроме реплик-лидеров (поскольку допускается несколько лидеров), запрос вернет ошибку.
DROP PARTITION | PART
ALTER TABLE table_name [ON CLUSTER cluster] DROP PARTITION|PART partition_expr Удаляет партицию. Партиция помечается как неактивная и будет полностью удалена примерно через 10 минут.
Подробнее о том, как корректно задать имя партиции, см. в разделе Как задавать имя партиции в запросах ALTER.
Запрос реплицируется — данные будут удалены на всех репликах.
ALTER TABLE mt DROP PARTITION '2020-11-21'; ALTER TABLE mt DROP PART 'all_4_4_0'; DROP DETACHED PARTITION | PART
ALTER TABLE table_name [ON CLUSTER cluster] DROP DETACHED PARTITION|PART partition_expr Удаляет из detached кусок или все куски, принадлежащие партиции. Подробнее о том, как корректно задать имя партиции, см. в разделе Как задавать имя партиции в запросах ALTER.
ATTACH PARTITION | PART
ALTER TABLE table_name [ON CLUSTER cluster] ATTACH PARTITION|PART partition_expr Добавляет данные в таблицу из директории detached . Можно добавить данные как для целой партиции, так и для отдельного куска. Примеры:
ALTER TABLE visits ATTACH PARTITION 201901; ALTER TABLE visits ATTACH PART 201901_2_2_0; Как корректно задать имя партиции или куска, см. в разделе Как задавать имя партиции в запросах ALTER.
Этот запрос реплицируется. Реплика-иницатор проверяет, есть ли данные в директории detached . Если данные есть, то запрос проверяет их целостность. В случае успеха данные добавляются в таблицу.
Если реплика, не являющаяся инициатором запроса, получив команду присоединения, находит кусок с правильными контрольными суммами в своей собственной папке detached , она присоединяет данные, не скачивая их с других реплик. Если нет куска с правильными контрольными суммами, данные загружаются из любой реплики, имеющей этот кусок.
Вы можете поместить данные в директорию detached на одной реплике и с помощью запроса ALTER . ATTACH добавить их в таблицу на всех репликах.
ATTACH PARTITION FROM
ALTER TABLE table2 [ON CLUSTER cluster] ATTACH PARTITION partition_expr FROM table1 Копирует партицию из таблицы table1 в таблицу table2 .
Обратите внимание, что:
- Данные не удаляются ни из table1 , ни из table2 .
- table1 может быть временной таблицей.
Следует иметь в виду:
- Таблицы должны иметь одинаковую структуру.
- Для таблиц должен быть задан одинаковый ключ партиционирования, одинаковый ключ сортировки и одинаковый первичный ключ.
- Для таблиц должна быть задана одинаковая политика хранения (диск, на котором хранится партиция, должен быть доступен для обеих таблиц).
Подробнее о том, как корректно задать имя партиции, см. в разделе Как задавать имя партиции в запросах ALTER.
REPLACE PARTITION
ALTER TABLE table2 [ON CLUSTER cluster] REPLACE PARTITION partition_expr FROM table1 Копирует партицию из таблицы table1 в таблицу table2 с заменой существующих данных в table2 .
Обратите внимание, что:
- Данные из table1 не удаляются.
- table1 может быть временной таблицей.
Следует иметь в виду:
- Таблицы должны иметь одинаковую структуру.
- Для таблиц должен быть задан одинаковый ключ партиционирования, одинаковый ключ сортировки и одинаковый первичный ключ.
- Для таблиц должна быть задана одинаковая политика хранения (диск, на котором хранится партиция, должен быть доступен для обеих таблиц).
Подробнее о том, как корректно задать имя партиции, см. в разделе Как задавать имя партиции в запросах ALTER.
MOVE PARTITION TO TABLE
ALTER TABLE table_source [ON CLUSTER cluster] MOVE PARTITION partition_expr TO TABLE table_dest Перемещает партицию из таблицы table_source в таблицу table_dest (добавляет к существующим данным в table_dest ) с удалением данных из таблицы table_source .
Следует иметь в виду:
- Таблицы должны иметь одинаковую структуру.
- Для таблиц должен быть задан одинаковый ключ партиционирования, одинаковый ключ сортировки и одинаковый первичный ключ.
- Для таблиц должна быть задана одинаковая политика хранения (диск, на котором хранится партиция, должен быть доступен для обеих таблиц).
- Движки таблиц должны быть одинакового семейства (реплицированные или нереплицированные).
CLEAR COLUMN IN PARTITION
ALTER TABLE table_name [ON CLUSTER cluster] CLEAR COLUMN column_name IN PARTITION partition_expr Сбрасывает все значения в столбце для заданной партиции. Если для столбца определено значение по умолчанию (в секции DEFAULT ), то будет выставлено это значение.
ALTER TABLE visits CLEAR COLUMN hour in PARTITION 201902 CLEAR INDEX IN PARTITION
ALTER TABLE table_name [ON CLUSTER cluster] CLEAR INDEX index_name IN PARTITION partition_expr Работает как CLEAR COLUMN , но сбрасывает индексы вместо данных в столбцах.
FREEZE PARTITION
ALTER TABLE table_name [ON CLUSTER cluster] FREEZE [PARTITION partition_expr] [WITH NAME 'backup_name'] Создаёт резервную копию для заданной партиции. Если выражение PARTITION опущено, резервные копии будут созданы для всех партиций.
Примечание
Создание резервной копии не требует остановки сервера.
Для таблиц старого стиля имя партиций можно задавать в виде префикса (например, 2019 ). В этом случае, резервные копии будут созданы для всех соответствующих партиций. Подробнее о том, как корректно задать имя партиции, см. в разделе Как задавать имя партиции в запросах ALTER.
Запрос формирует для текущего состояния таблицы жесткие ссылки на данные в этой таблице. Ссылки размещаются в директории /var/lib/clickhouse/shadow/N/. , где:
- /var/lib/clickhouse/ — рабочая директория ClickHouse, заданная в конфигурационном файле;
- N — инкрементальный номер резервной копии.
- если задан параметр WITH NAME , то вместо инкрементального номера используется значение параметра ‘backup_name’ .
Примечание
При использовании нескольких дисков для хранения данных таблицы директория shadow/N появляется на каждом из дисков, на которых были куски, попавшие под выражение PARTITION .
Структура директорий внутри резервной копии такая же, как внутри /var/lib/clickhouse/ . Запрос выполнит chmod для всех файлов, запрещая запись в них.
Обратите внимание, запрос ALTER TABLE t FREEZE PARTITION не реплицируется. Он создает резервную копию только на локальном сервере. После создания резервной копии данные из /var/lib/clickhouse/shadow/ можно скопировать на удалённый сервер, а локальную копию удалить.
Резервная копия создается почти мгновенно (однако, сначала запрос дожидается завершения всех запросов, которые выполняются для соответствующей таблицы).
ALTER TABLE t FREEZE PARTITION копирует только данные, но не метаданные таблицы. Чтобы сделать резервную копию метаданных таблицы, скопируйте файл /var/lib/clickhouse/metadata/database/table.sql
Чтобы восстановить данные из резервной копии, выполните следующее:
- Создайте таблицу, если она ещё не существует. Запрос на создание можно взять из .sql файла (замените в нём ATTACH на CREATE ).
- Скопируйте данные из директории data/database/table/ внутри резервной копии в директорию /var/lib/clickhouse/data/database/table/detached/ .
- С помощью запросов ALTER TABLE t ATTACH PARTITION добавьте данные в таблицу.
Восстановление данных из резервной копии не требует остановки сервера.
Подробнее о резервном копировании и восстановлении данных читайте в разделе Резервное копирование данных.
UNFREEZE PARTITION
ALTER TABLE table_name [ON CLUSTER cluster] UNFREEZE [PARTITION 'part_expr'] WITH NAME 'backup_name' Удаляет с диска «замороженные» партиции с указанным именем. Если секция PARTITION опущена, запрос удаляет резервную копию всех партиций сразу.
FETCH PARTITION | PART
ALTER TABLE table_name [ON CLUSTER cluster] FETCH PARTITION|PART partition_expr FROM 'path-in-zookeeper' Загружает партицию с другого сервера. Этот запрос работает только для реплицированных таблиц.
Запрос выполняет следующее:
- Загружает партицию/кусок с указанного шарда. Путь к шарду задается в секции FROM (‘path-in-zookeeper’). Обратите внимание, нужно задавать путь к шарду в ZooKeeper.
- Помещает загруженные данные в директорию detached таблицы table_name . Чтобы прикрепить эти данные к таблице, используйте запрос ATTACH PARTITION | PART.
ALTER TABLE users FETCH PARTITION 201902 FROM '/clickhouse/tables/01-01/visits'; ALTER TABLE users ATTACH PARTITION 201902; - FETCH PART
ALTER TABLE users FETCH PART 201901_2_2_0 FROM '/clickhouse/tables/01-01/visits'; ALTER TABLE users ATTACH PART 201901_2_2_0; Следует иметь в виду:
- Запрос ALTER TABLE t FETCH PARTITION|PART не реплицируется. Он загружает партицию в директорию detached только на локальном сервере.
- Запрос ALTER TABLE t ATTACH реплицируется — он добавляет данные в таблицу сразу на всех репликах. На одной из реплик данные будут добавлены из директории detached , а на других — из соседних реплик.
Перед загрузкой данных система проверяет, существует ли партиция и совпадает ли её структура со структурой таблицы. При этом автоматически выбирается наиболее актуальная реплика среди всех живых реплик.
Несмотря на то что запрос называется ALTER TABLE , он не изменяет структуру таблицы и не изменяет сразу доступные данные в таблице.
MOVE PARTITION | PART
Перемещает партицию или кусок данных на другой том или диск для таблиц с движком MergeTree . Смотрите Хранение данных таблицы на нескольких блочных устройствах.
ALTER TABLE table_name [ON CLUSTER cluster] MOVE PARTITION|PART partition_expr TO DISK|VOLUME 'disk_name' Запрос ALTER TABLE t MOVE :
- Не реплицируется, т.к. на разных репликах могут быть различные конфигурации политик хранения.
- Возвращает ошибку, если указан несконфигурированный том или диск. Ошибка также возвращается в случае невыполнения условий перемещения данных, которые указаны в конфигурации политики хранения.
- Может возвращать ошибку в случае, когда перемещаемые данные уже оказались перемещены в результате фонового процесса, конкурентного запроса ALTER TABLE t MOVE или как часть результата фоновой операции слияния. В данном случае никаких дополнительных действий от пользователя не требуется.
ALTER TABLE hits MOVE PART '20190301_14343_16206_438' TO VOLUME 'slow' ALTER TABLE hits MOVE PARTITION '2019-09-01' TO DISK 'fast_ssd' UPDATE IN PARTITION
Манипулирует данными в указанной партиции, соответствующими заданному выражению фильтрации. Реализовано как мутация mutation.
ALTER TABLE [db.]table [ON CLUSTER cluster] UPDATE column1 = expr1 [, ...] [IN PARTITION partition_id] WHERE filter_expr Пример
ALTER TABLE mt UPDATE x = x + 1 IN PARTITION 2 WHERE p = 2; Смотрите также
DELETE IN PARTITION
Удаляет данные в указанной партиции, соответствующие указанному выражению фильтрации. Реализовано как мутация mutation.
ALTER TABLE [db.]table [ON CLUSTER cluster] DELETE [IN PARTITION partition_id] WHERE filter_expr Пример
ALTER TABLE mt DELETE IN PARTITION 2 WHERE p = 2; Смотрите также
Как задавать имя партиции в запросах ALTER
Чтобы задать нужную партицию в запросах ALTER . PARTITION , можно использовать:
- Имя партиции. Посмотреть имя партиции можно в столбце partition системной таблицы system.parts. Например, ALTER TABLE visits DETACH PARTITION 201901 .
- Кортеж из выражений или констант, совпадающий (в типах) с кортежем партиционирования. В случае ключа партиционирования из одного элемента, выражение следует обернуть в функцию tuple(. ) . Например, ALTER TABLE visits DETACH PARTITION tuple(toYYYYMM(toDate(‘2019-01-25’))) .
- Строковый идентификатор партиции. Идентификатор партиции используется для именования кусков партиции на файловой системе и в ZooKeeper. В запросах ALTER идентификатор партиции нужно указывать в секции PARTITION ID , в одинарных кавычках. Например, ALTER TABLE visits DETACH PARTITION ID ‘201901’ .
- Для запросов ATTACH PART и DROP DETACHED PART: чтобы задать имя куска партиции, используйте строковой литерал со значением из столбца name системной таблицы system.detached_parts. Например, ALTER TABLE visits ATTACH PART ‘201901_1_1_0’ .
Использование кавычек в имени партиций зависит от типа данных столбца, по которому задано партиционирование. Например, для столбца с типом String имя партиции необходимо указывать в кавычках (одинарных). Для типов Date и Int* кавычки указывать не нужно.
Замечание: для таблиц старого стиля партицию можно указывать и как число 201901 , и как строку ‘201901’ . Синтаксис для таблиц нового типа более строг к типам (аналогично парсеру входного формата VALUES).
Правила, сформулированные выше, актуальны также для запросов OPTIMIZE. Чтобы указать единственную партицию непартиционированной таблицы, укажите PARTITION tuple() . Например:
OPTIMIZE TABLE table_not_partitioned PARTITION tuple() FINAL; IN PARTITION указывает на партицию, для которой применяются выражения UPDATE или DELETE в результате запроса ALTER TABLE . Новые куски создаются только в указанной партиции. Таким образом, IN PARTITION помогает снизить нагрузку, когда таблица разбита на множество партиций, а вам нужно обновить данные лишь точечно.