如何从不同的架构将公共数据插入到临时表中?
我不确定如何解决此问题:
我们从各种在线供应商(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类有帮助,请随时提出建议。
-
首先,您可以创建一个
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
在全球范围内,A 是模棱两可的。它仅与时区有关。如果您将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);
您 需要 一个索引。在我的示例中,主键约束自动提供索引。
如果表很大,则 在 创建临时表 之前, 请确保您有足够的 临时缓冲区 在RAM中进行处理。否则,它实际上会使您减速。 __
SET temp_buffers = 1000MB;
必须是会话中对临时对象的第一次调用。请勿仅针对您的会话将其设置为全局较高。无论如何,临时表会在会话结束时自动删除。
要估算您需要多少RAM,请一次创建表格并进行以下测量:
SELECT pg_size_pretty(pg_total_relation_size('tmp_orders'));
在dba.SE上的相关问题下,有关对象大小的更多信息。
仅当您必须在一个会话中处理多个查询时,才支付所有开销。对于其他用例,还有其他解决方案。如果您在查询时知道源表,则将查询直接定向到源表会更快。如果您不这样做,我会再次质疑您的独特性
order_nr
。如果确实可以保证唯一,则可以删除source
我介绍的列。对于仅一个或几个查询,使用视图而不是物化视图可能会更快。
我还要考虑一个 plpgsql函数 ,该 函数
一个在另一个表之后查询直到找到记录。考虑到开销,对于一些查询可能会便宜一些。当然,每个表都需要索引。另外,如果您坚持
text
或坚持自己的意愿varchar
,请order_nr
考虑COLLATE "C"
一下。 -