Как сгенерировать много таблиц из SQL?

У меня есть следующие таблицы:

Названия таблиц: штаты США — 50 таблиц следующего формата.

  1. столбцы: штат, почтовый индекс, адрес
  2. почтовый индекс не уникален

Я хочу сделать следующее с одним SQL-запросом:

  1. создать список уникальных почтовых индексов ~ 40 000
  2. создавать таблицы с именами почтовых индексов ~ 40 000
  3. в каждой таблице - только записи с соответствующим почтовым индексом
  4. столбцы: штат, почтовый индекс, адрес

Как создать такой SQL-запрос, чтобы он был наиболее эффективным и дешевым способом создания всех этих таблиц?


sql google-bigquery
person thstart    schedule 08.06.2018    source источник
comment
How to create such a SQL query ... - Реальность такова, что невозможно сделать это одним простым и дешевым запросом. некоторые сценарии должны быть задействованы для оркестровки всего вышеперечисленного. Между тем, этот дизайн кажется мне ужасным с разных точек зрения. Я чувствую, что это тупик, если честно. Вам следует пересмотреть свой дизайн. В основном, чего именно вы пытаетесь достичь. Зачем вам 40К таблиц? и т.п.   -  person Mikhail Berlyant    schedule 09.06.2018
comment
этот дизайн - это то, что мне нужно, и меня это устраивает.   -  person thstart    schedule 15.06.2018

Ответы (1)


arrow_upward
2
arrow_downward

С текущими функциями вы можете сделать пару «читов»:

  • Определите линейное преобразование почтового индекса в даты. Например, DATE_ADD('1980-01-01', INTERVAL zipcode DAYS)
  • Создайте секционированную таблицу — используйте эту синтетическую дату в качестве раздела.
  • Напишите запрос, который вставляет все ваши данные в эту таблицу SELECT *, synthetic_date(zipcode) FROM ``tables*``.
  • Теперь у вас есть разделенная таблица, в которой каждый раздел содержит данные только для каждого почтового индекса! Стоимость линейная, всего одно сканирование всех ваших данных.
  • И если вы хотите, теперь вы можете скопировать каждый раздел в новую таблицу - бесплатно (но помните о ежедневных квотах). См. https://cloud.google.com/bigquery/docs/managing-partitioned-tables#copying_a_single_partition.
person Felipe Hoffa    schedule 08.06.2018
comment
примечание: Максимальное количество разделов на секционированную таблицу — 4000 - person Mikhail Berlyant; 08.06.2018
comment
Итак, возможные решения: 1) скопировать каждый раздел в новую таблицу - с 40 000 таблиц мне приходится иметь дело с дневными лимитами. 2) У меня не может быть 40 000 разделов. Каково решение? О дневных ограничениях: cloud.google.com/bigquery/quotas#export_jobs не вижу, с чем связаны дневные квоты 1). - person thstart; 08.06.2018
comment
Я вижу возможное решение следующим образом: 1) сгенерировать по 50 таблиц для каждого состояния. 2) создать разделы внутри каждого для почтовых индексов. 40 000/50 = 800 разделов на состояние могут справиться с ограничением в 4000 разделов на таблицу. Единственная проблема - дневные квоты - где эта инфа актуальна к делу? - person thstart; 08.06.2018
comment
Я вижу это ограничение: :Максимальное количество модификаций партиций в день для одной таблицы — 5000 Вы ограничены 5000 модификаций партиций в день для секционированной таблицы. Если я сделаю 800 разделов в каждой из этих 50 таблиц, я полагаю, все будет в порядке с этим ограничением? - person thstart; 08.06.2018