Как я могу вставить общие данные во временную таблицу из разрозненных схем?

Я не уверен, как решить эту проблему:

Мы импортируем информацию о заказах от различных онлайн-поставщиков ( Amazon, Newegg и т. Д.). Каждый поставщик имеет свою особую терминологию и структуру для своих заказов, которые мы отразили в базе данных. Наши данные импортируются в базу данных без проблем, однако проблема, с которой я столкнулся, заключается в том, чтобы написать метод, который будет извлекать необходимые поля из базы данных, независимо от схемы.

Например, предположим, что у нас есть следующие структуры:

Структура Newegg:

"OrderNumber" integer NOT NULL, -- The Order Number
"InvoiceNumber" integer, -- The invoice number
"OrderDate" timestamp without time zone, -- Create date.

Структура Амазонки:

"amazonOrderId" character varying(25) NOT NULL, -- Amazon's unique, displayable identifier for an order.
"merchant-order-id" integer DEFAULT 0, -- A unique identifier optionally supplied for the order by the Merchant.
"purchase-date" timestamp with time zone, -- The date the order was placed.

Как я могу выбрать эти элементы и поместить их во временную таблицу для запроса?

Временная таблица может выглядеть так:

"OrderNumber" character varying(25) NOT NULL,
"TransactionId" integer,
"PurchaseDate" timestamp with time zone

Я понимаю, что некоторые базы данных представляют номер заказа с целым числом, а другие - с изменяющимся символом; чтобы справиться с этим я планирую привести типы данных к значениям String.

Кто-нибудь есть предложение для меня, чтобы прочитать об этом, поможет мне понять это?

Мне не нужен точный ответ, просто толчок в правильном направлении.

Данные будут использоваться Java, поэтому, если какие-то конкретные классы Java помогут, смело предлагайте их.

Источник

2 ответа

Решение

Во-первых, вы можете создать VIEW чтобы обеспечить эту функциональность:

CREATE VIEW orders AS
SELECT '1'::int            AS source -- or any other tag to identify source
      ,"OrderNumber"::text AS order_nr
      ,"InvoiceNumber"     AS tansaction_id -- no cast .. is int already
      ,"OrderDate" AT TIME ZONE 'UTC' AS purchase_date -- !! see explanation
FROM   tbl_newegg

UNION  ALL  -- not UNION!
SELECT 2
       "amazonOrderId"
      ,"merchant-order-id"
      ,"purchase-date"
FROM   tbl_amazon;

Вы можете запросить это представление как любую другую таблицу:

SELECT * FROM orders WHERE order_nr = 123 AND source = 2;

  • source необходимо, если order_nr не уникален Как еще вы можете гарантировать уникальные номера заказов по разным источникам?

  • timestamp without time zone является неоднозначным в глобальном контексте. Это хорошо только в связи с его часовым поясом. Если вы смешаете timestamp а также timestamptz нужно разместить timestamp в определенном часовом поясе с AT TIME ZONE построить, чтобы сделать эту работу. Для более подробного объяснения прочитайте этот связанный ответ.

    Я использую UTC в качестве часового пояса, вы можете указать другой. Простой актерский состав "OrderDate"::timestamptz будет предполагать ваш текущий часовой пояс. AT TIME ZONE применяется к timestamp результаты в timestamptz, Вот почему я не добавил еще один актерский состав.

  • Хотя вы можете, я советую никогда не использовать идентификаторы верблюжьих падежей в PostgreSQL. Предотвращает многие виды путаницы. Обратите внимание на идентификаторы в нижнем регистре (без ненужных двойных кавычек), которые я предоставил.

  • Не использовать varchar(25) как тип для order_nr, Просто используйте text без модификатора произвольной длины, если он должен быть строкой. Если все номера заказов состоят исключительно из цифр, integer или же bigint будет быстрее.

Спектакль

Один из способов сделать это быстро - материализовать представление. То есть запишите результат во (временную) таблицу:

CREATE TEMP TABLE tmp_orders AS
SELECT * FROM orders;

ANALYZE tmp_orders; -- temp tables are not auto-analyzed!

ALTER TABLE tmp_orders
ADD constraint orders_pk PRIMARY KEY (order_nr, source);

Вам нужен индекс. В моем примере ограничение первичного ключа обеспечивает индекс автоматически.

Если ваши таблицы большие, убедитесь, что у вас достаточно временных буферов для обработки этого в оперативной памяти, прежде чем создавать временную таблицу. Иначе это на самом деле замедлит вас.

SET temp_buffers = 1000MB;

Должен быть первым вызовом временных объектов в вашей сессии. Не устанавливайте это высоко глобально, только для вашей сессии. В любом случае временная таблица автоматически удаляется в конце сеанса.

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

SELECT pg_size_pretty(pg_total_relation_size('tmp_orders'));

Больше о размерах объектов в этом связанном вопросе на dba.SE.

Все накладные расходы оплачиваются только в том случае, если вам нужно обработать несколько запросов в течение одного сеанса. Для других случаев использования существуют другие решения. Если вы знаете исходную таблицу на момент запроса, было бы гораздо быстрее вместо этого направить ваш запрос в исходную таблицу. Если нет, я бы поставил под сомнение уникальность вашего order_nr еще раз. Если он действительно гарантированно уникален, вы можете удалить столбец source Я представил.

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

Я также хотел бы рассмотреть функцию plpgsql, которая запрашивает одну таблицу за другой, пока не будет найдена запись. Может быть дешевле на пару запросов, учитывая накладные расходы. Индексы для каждой таблицы нужны конечно.

Кроме того, если вы придерживаетесь text или же varchar для тебя order_nr, рассматривать COLLATE "C" для этого.

Источник

Похоже, вам нужно создать абстрактный класс, который определит основы взаимодействия с данными, а затем выведет класс для каждой схемы базы данных, к которой вам нужно получить доступ. Это позволит основному коду работать с одним типом объекта, и каждая реализация может затем указать запросы в форме, специфичной для этой схемы базы данных.

Что-то вроде:

public class Order
{
    private String orderNumber;
    private BigDecimal orderTotal;
    ... etc ...
}

public abstract class AbstractOrderInformation
{
  public abstract ArrayList<Order> getOrders();
  ...
}

с классом Newegg:

public class NeweggOrderInformation extends AbstractOrderInformation
{
   public ArrayList<Order> getOrders() {
      ... do the work of getting the newegg order
   }
 ...
}

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

Источник
Другие вопросы по тегам