Блог

Главная - Вопросы по программированию - В BigQuery определите, когда столбцы не совпадают при объединении ВСЕХ

В BigQuery определите, когда столбцы не совпадают при объединении ВСЕХ

#sql #google-bigquery #union-all

#sql #google-bigquery #объединение всех

Вопрос:

 with
  table1 as (
    select 'joe' as name, 17 as age, 25 as speed
  ),
  table2 as (
    select 'nick' as name, 21 as speed, 23 as strength
  )

select * from table1
union all
select * from table2

В Google BigQuery это union all не выдает ошибку, потому что обе таблицы имеют одинаковое количество столбцов (по 3 в каждой). Однако я получаю неверный вывод данных, потому что столбцы не совпадают. Вместо того, чтобы выводить новую таблицу с 4 столбцами name , age , speed , strength union all с правильными значениями nulls для пропущенных значений (что, вероятно, было бы предпочтительнее), в, , сохраняет 3 столбца из верхней строки.

Есть ли хороший способ определить, что столбцы не совпадают, вместо того, чтобы запрос автоматически возвращал неверные данные? Есть ли какой-либо способ вернуть ошибку, возможно, в отличие от успешной таблицы? Я не уверен, как проверить в SQL, что столбцы в 2 таблицах совпадают.

Редактировать: в этом примере ясно видно, что столбцы не совпадают, однако в наших данных у нас более 100 столбцов, и мы хотим избежать ситуации, когда мы допустим ошибку при объединении ВСЕХ

Комментарии:

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

2. Да, я знаю, что порядок столбцов должен быть одинаковым при использовании union all . Мой вопрос заключается в том, существует ли способ вычислить, что имена столбцов порядки фактически одинаковы.

3. Суть в том, что не используйте * в качестве проекции при выполнении UNION ALL , а явно перечисляйте столбцы.

4. Я понимаю наилучшую практику. Я просто спрашиваю, возможно ли поймать / обработать ошибку в том случае, если имена столбцов не совпадают

5. вы хотите сделать это во «время выполнения» или «во время разработки»? другими словами — это нормально, просто сначала сравнить схему и, если все кошерно, чем в отдельном запросе, запустить ваш union all stuff? — о, хорошо — это именно то, что ты только что ответил

Ответ №1:

Ниже приведен стандартный SQL BigQuery и функция использования сценариев BQ

 DECLARE statement STRING;
SET statement = (
  WITH  table1_columns AS (
    SELECT column FROM (SELECT * FROM `project.dataset.table1` LIMIT 1) t,
    UNNEST(REGEXP_EXTRACT_ALL(TRIM(TO_JSON_STRING(t), '{}'), r'"([^"]*)":')) column
  ), table2_columns AS (
    SELECT column FROM (SELECT * FROM `project.dataset.table2` LIMIT 1) t,
    UNNEST(REGEXP_EXTRACT_ALL(TRIM(TO_JSON_STRING(t), '{}'), r'"([^"]*)":')) column
  ), all_columns AS (
    SELECT column FROM table1_columns UNION DISTINCT SELECT column FROM table2_columns
  )
  SELECT (
      SELECT 'SELECT ' || STRING_AGG(IF(t.column IS NULL, 'NULL as ', '') || a.column, ', ') || ' FROM `project.dataset.table1` UNION ALL '
      FROM all_columns a LEFT JOIN table1_columns t USING(column)
    ) || (
      SELECT 'SELECT ' || STRING_AGG(IF(t.column IS NULL, 'NULL as ', '') || a.column, ', ') || ' FROM `project.dataset.table2`'
      FROM all_columns a LEFT JOIN table2_columns t USING(column)
    ) 
);
EXECUTE IMMEDIATE statement;

при применении к образцу данных из вашего вопроса — вывод

 Row name    age     speed   strength     
1   joe     17      25      null     
2   nick    null    21      23

Ответ №2:

После сохранения table1 и table2 в виде 2 таблиц в наборе данных в BigQuery я затем использовал метаданные с помощью INFORMATION_SCHEMA , чтобы проверить соответствие столбцов.

 SELECT *
FROM models.INFORMATION_SCHEMA.COLUMNS
where table_name = 'table1'

SELECT *
FROM models.INFORMATION_SCHEMA.COLUMNS
where table_name = 'table2'

INFORMATION_SCHEMA.COLUMNS возвращает информацию, включая имена столбцов и их расположение. Затем я могу объединить эти 2 таблицы вместе, чтобы проверить, совпадают ли имена…