запуск sql для других таблиц, если таковой не существует (игнорирование отсутствующих таблиц не приводит к сбою)

ProgramBox

запуск sql для других таблиц, если таковой не существует (игнорирование отсутствующих таблиц не приводит к сбою)

Post author:admin
Запись опубликована:13 марта, 2023
Post category:Вопросы по программированию

#sql #google-bigquery

Вопрос:

У меня много таблиц в bigquery. Мне нужно создать таблицу, в которой указаны имена людей в каждой таблице старше 20 лет. Я создал что-то подобное, но это не удается, если одна из таблиц не существует. (Я запускаю его для разных проектов, и их таблицы немного отличаются, например, в одном из проектов нет таблицыa)

 WITH
a As (
    SELECT name
    FROM 'tableA'
    WHERE age >20
),
b As (
    SELECT name
    FROM 'tableB'
    WHERE age >20
)

SELECT name FROM a
UNION ALL
SELECT name FROM b

Как я могу предотвратить сбой и сказать, существует ли таблица, а затем найти людей старше 20 лет, в противном случае проигнорировать ее и запустить для других таблиц?
(Это задача воздушного потока, которая завершается с ошибкой)

1. Таблицы должны существовать, чтобы ссылаться на них. Я думаю, вы застряли в динамическом построении запроса, если не знаете, существуют ли таблицы.

2. @GordonLinoff не могли бы вы подробнее рассказать о динамическом построении запроса?

3. Он имеет в виду, что вам нужно создать какую-то хранимую процедуру, чтобы сначала проверить, существует ли таблица, а затем соответствующим образом построить запрос. Это динамически написанный запрос, о котором он говорит.

4. Не могли бы вы подробнее рассказать о своем процессе? Я вижу, что вы пытаетесь запросить две таблицы, вы также упомянули, что она используется в Airflow и, наконец, что вы используете ту же задачу для другого проекта. Я правильно понял? Мне нужно больше деталей, чтобы правильно ответить на этот вопрос.

5. @AlexandreMoraes Привет, Алекс, это верно. У меня есть задача airflow, которая выполняет приведенный выше запрос с 20 таблицами. Мне нужно запустить код для 3 разных проектов в bigquery, и их таблицы немного отличаются. Например, у меня есть 3 проекта на bigquery под названием school1, school2, school3. каждый проект имеет набор данных, называемый классами. В нем есть таблицы с именами TableA, TableB, … но в school2 нет TableA или в school3 нет TableB. Итак, мой запрос завершается с ошибкой, хотя он работает для набора данных со всей необходимой таблицей (например, school1).

Ответ №1:

Насколько я понимаю, у вас есть одна среда composer, и вы хотите, чтобы она использовала BigQueryOperator() для запроса данных в 3 разных проектах.

Я предполагаю, что вы уже создали свою среду Composer в своем проекте. Затем вы можете выполнить следующие действия:

1) Создайте 3 разных соединения между вашей средой Composer и каждым проектом, к которому вы будете запрашивать. Например, как описано здесь .

2) Создайте конкретный запрос для каждого проекта, в котором вы фильтруете age>20 и добавляете все таблицы вместе. Таким образом, вы будете правильно обращаться к таблицам для каждого проекта.

Создайте один DAG 3) файл с 3 BigQueryOperators, каждый из которых ссылается на определенное соединение и использует соответствующий запрос, созданный в 2. Создание DAG описано здесь, и DAG будет выглядеть следующим образом:

 task_custom = bigquery_operator.BigQueryOperator(
    task_id='task_custom_connection_school1',
    bql_1='SELECT * WHERE age>20', use_legacy_sql=False,
    # Set a connection ID to use a connection that you have created in step 1.
    bigquery_conn_id='my_gcp_connection')

В качестве альтернативы вы можете создать несколько файлов DAG, по одному для каждого соединения. Кроме того, обратите внимание, что имя соединения указано с bigquery_conn_id помощью .

Следуя приведенным выше шагам, каждый из ваших запросов будет адаптирован специально для каждого проекта. Таким образом, они будут выполнены правильно.

Ответ №2:

Решением только для BigQuery было бы использование таблиц с подстановочными знаками.

Здесь есть несколько вариантов:

Если запрашиваются все таблицы в наборе данных:

 SELECT name 
FROM `project.dataset.*`
WHERE age > 20

Если известны все имена таблиц во всех наборах данных:

 SELECT name 
FROM `project.dataset.*`
WHERE age > 20
  AND _TABLE_SUFFIX IN ('tableA', 'tableB', ..., 'tableN')

Если все таблицы во всех наборах данных соответствуют определенному шаблону:

 SELECT name 
FROM `project.dataset.*`
WHERE age > 20
  AND _TABLE_SUFFIX LIKE 'table%'

LIKE Оператор (в сочетании с логическими операторами) в _TABLE_SUFFIX поле предоставляет большую свободу для сопоставления множества шаблонов для имен таблиц без необходимости явно перечислять все имена таблиц, поскольку это необходимо для IN оператора. Если ни одна таблица не соответствует _TABLE_SUFFIX указанной (т. Е. Она не указана в массиве IN оператора), запрос вернет 0 результатов вместо сбоя.

Более подробная информация о запросе таблиц с подстановочными знаками приведена в документации BigQuery .

Обратите внимание, что несоответствующие схемы могут вызвать некоторые проблемы, поэтому вы можете включить проверку того, что сопоставленные таблицы имеют правильную схему, с запросом к INFORMATION_SCHEMA.COLUMNS таблице:

 WITH correct_schema_tables as (SELECT table_name FROM (
  SELECT * FROM project.dataset.INFORMATION_SCHEMA.COLUMNS
    WHERE
      column_name = 'name'
      AND data_type = 'STRING')
  JOIN (
  SELECT * FROM project.dataset.INFORMATION_SCHEMA.COLUMNS
    WHERE
      column_name = 'age'
      AND data_type = 'INT64')
  USING (table_name)
)


SELECT name 
FROM `project.dataset.*`
WHERE age > 20
  AND _TABLE_SUFFIX IN (SELECT table_name FROM correct_schema_tables)
  AND _TABLE_SUFFIX LIKE 'table%'