метаSQL

Весь день работая с такими базами данных, как Vertica и Redshift… Я часто сталкиваюсь с такими задачами, как «обрезать все таблицы в схеме». Когда в схеме сотни таблиц, может потребоваться много написания SQL. Когда я только начинал, я много работал над созданием операторов SQL с использованием электронных таблиц Excel, но теперь я в основном использую метаSQL.

Если вы посмотрите метаSQL, вы, вероятно, найдете какой-нибудь коммерческий продукт, но когда я говорю метаSQL, я имею в виду метапрограммирование с помощью SQL, а не коммерческий продукт. Метапрограммирование — это практика использования кода для написания кода. Существует много уровней метапрограммирования, и, возможно, даже ведутся споры о том, действительно ли под это определение подпадает тот материал по SQL, о котором я собираюсь писать в блоге. Я считаю это метапрограммированием, потому что это «написание SQL с помощью SQL». Большинство реализаций SQL не предоставляют средства, которые традиционно используются в метапрограммировании (например, отражение)… но некоторые процедурные реализации позволяют запустить процедуру для создания другой процедуры. Хотя это может быть классно… здесь я сосредоточился на искусстве использования метаSQL, чтобы выполнять свою работу быстрее.

Итак… используя приведенный выше пример… предположим, мне нужно создать набор операторов для усечения 100 таблиц в схеме с именем «клиент»:

select 
  'truncate table '||schema_name||'.'||table_name||';'
from
  tables
where
  schema_name = 'customer';

Приведенный выше оператор выведет что-то вроде этого в вашем клиенте SQL:

truncate table customer.customers;
truncate table customer.some_table;
truncate table customer.another_table;
truncate table customer.date;
truncate table customer.some goofy table;
...

Оттуда вы можете просто скопировать операторы усечения в свой редактор и запустить их. Легкий!

Чтобы использовать этот метод, вам нужно быть знакомым с таблицами каталога для вашей СУБД. Описанный выше подход работает с Vertica. Большинство СУБД имеют объекты information_schema.tables и information_schema.columns, на которые вы можете ссылаться.

Конечно… у приведенного выше примера есть слабое место. Большинство реализаций SQL позволяют создавать имена схем и таблиц (и столбцов), содержащие пробелы… или, возможно, даже имена объектов с использованием ключевого слова, такого как «дата». По этой причине всегда рекомендуется заключать имена объектов в двойные кавычки:

select
  'truncate table "'||schema_name||'"."'||table_name||'";'
from
  tables
where
  schema_name = 'customer';

…что даст…

truncate table "customer"."customers";
truncate table "customer"."some_table";
truncate table "customer"."another_table";
truncate table "customer"."date";
truncate table "customer"."some goofy table";
...

Опытные эксперты по SQL могут закатить глаза от такой простоты использования SQL… но я видел, как новички и даже люди, которые должны знать лучше, тратят много времени на редактирование копирования/вставки повторяющихся SQL или используют для этих целей Excel. задания.

Чтобы привести еще один пример… предположим, вам нужно сделать отдельный подсчет для каждого столбца в таблице из 100 столбцов…

select
  ',count(distinct "'||column_name||'") as "'||column_name||'"'
from
  columns
where
  table_name='the_table';

…что даст вам…

,count(distinct "column1") as "column1"
,count(distinct "column2") as "column2"
,count(distinct "column3") as "column3"
,count(distinct "column4") as "column4"
...

Конечно… приведенное выше не является правильным оператором SQL, но его очень легко скопировать/вставить в тело вашего SELECT (и удалить первую запятую!)

Я надеюсь, что вы нашли этот подход полезным! Пожалуйста, поделитесь любыми приемами и советами в стиле метаSQL в комментариях!