将xml架构导入postgres以自动创建表，然后使用xml文件填充它？ [英] Importing an xml schema into postgres to automatically create a table and then populate it with an xml file?
问题描述
我有一个巨大的xml文件，除非将其导入数据库，否则无法打开。我正在使用Postgres。我有一个与此数据文件一起使用的架构。列太多，所以我想自动执行从此架构创建表的过程，然后从我的计算机上的本地驱动器导入数据文件以填充此表。我该怎么做呢？
我在SO上看到了很多答案，但却无法正确理解这一点。此外，我没有超级用户权限，因此必须解决这个问题。
I have a huge xml file that I cannot open unless I import it into a database. I am using Postgres for this. I have a schema that goes with this data file. There are too many columns so i'd like to automate the process of creating a table from this schema and then importing the data file from the local drive on my computer to populate this table. How do i do this? I saw a lot of answers on SO but haven't been able to understand this correctly. Also I do not have superuser rights so will have to work around that.
以下是架构文件的样子：
Here's what the schema file looks like:
> <?xml version="1.0" encoding="UTF-8"?> <xs:schema
> xmlns:xs="http://www.w3.org/2001/XMLSchema"
> elementFormDefault="qualified"
> targetNamespace="http://www.drugbank.ca" xmlns="http://www.drugbank.ca">
> <xs:element name="drugbank" type="drugbank-type">
> <xs:annotation>
> <xs:documentation>This is the root element for the DrugBank database schema. DrugBank is a database on drug and
> drug-targets.</xs:documentation>
> </xs:annotation>
> </xs:element>
> <xs:complexType name="drugbank-type">
> <xs:annotation>
> <xs:documentation>This is the root element type for the DrugBank database schema.</xs:documentation>
> </xs:annotation>
> <xs:sequence>
> <xs:element name="drug" type="drug-type" maxOccurs="unbounded"/>
> </xs:sequence>
> <xs:attribute name="version" type="xs:string" use="required">
> <xs:annotation>
> <xs:documentation>The DrugBank version for the exported XML file.</xs:documentation>
> </xs:annotation>
> </xs:attribute>
> <xs:attribute name="exported-on" type="xs:date" use="required">
> <xs:annotation>
> <xs:documentation>The date the XML file was exported.</xs:documentation>
> </xs:annotation>
> </xs:attribute>
> </xs:complexType>
> <xs:complexType name="drug-type">
> <xs:sequence>
> <xs:element maxOccurs="unbounded" minOccurs="1" name="drugbank-id"
> type="drugbank-drug-salt-id-type"> </xs:element>
> <xs:element name="name" type="xs:string"/>
> <xs:element name="description" type="xs:string"/>
> <xs:element name="cas-number" type="xs:string"/>
> <xs:element name="unii" type="xs:string"/>
> <xs:element name="average-mass" type="xs:float" minOccurs="0"/>
> <xs:element name="monoisotopic-mass" type="xs:float" minOccurs="0"/>
> <xs:element name="state" type="state-type" minOccurs="0"/>
> <xs:element name="groups" type="group-list-type"/>
> <xs:element name="general-references" type="reference-list-type"/>
> <xs:element name="synthesis-reference" type="xs:string"/>
> <xs:element name="indication" type="xs:string"/>
> <xs:element name="pharmacodynamics" type="xs:string"/>
> <xs:element name="mechanism-of-action" type="xs:string"/>
> <xs:element name="toxicity" type="xs:string"/>
> <xs:element name="metabolism" type="xs:string"/>
> <xs:element name="absorption" type="xs:string"/>
> <xs:element name="half-life" type="xs:string"/>
> <xs:element name="protein-binding" type="xs:string"/>
> <xs:element name="route-of-elimination" type="xs:string"/>
> <xs:element name="volume-of-distribution" type="xs:string"/>
> <xs:element name="clearance" type="xs:string"/>
> <xs:element name="classification" type="classification-type" minOccurs="0"/>
> <xs:element name="salts" type="salt-list-type"/>
> <xs:element name="synonyms" type="synonym-list-type"/>
> <xs:element name="products" type="product-list-type"/>
> <xs:element name="international-brands" type="international-brand-list-type"/>
> <xs:element name="mixtures" type="mixture-list-type"/>
> <xs:element name="packagers" type="packager-list-type"/>
> <xs:element name="manufacturers" type="manufacturer-list-type"/>
> <xs:element name="prices" type="price-list-type"/>
> <xs:element name="categories" type="category-list-type"/>
> <xs:element name="affected-organisms" type="affected-organism-list-type"/>
> <xs:element name="dosages" type="dosage-list-type"/>
> <xs:element name="atc-codes" type="atc-code-list-type"/>
> <xs:element name="ahfs-codes" type="ahfs-code-list-type"/>
> <xs:element name="pdb-entries" type="pdb-entry-list-type"/>
> <xs:element name="fda-label" type="xs:anyURI" minOccurs="0"/>
> <xs:element name="msds" type="xs:anyURI" minOccurs="0"/>
> <xs:element name="patents" type="patent-list-type"/>
> <xs:element name="food-interactions" type="food-interaction-list-type"/>
> <xs:element name="drug-interactions" type="drug-interaction-list-type"/>
> <xs:element minOccurs="0" name="sequences" type="sequence-list-type"/>
> <xs:element minOccurs="0" name="calculated-properties" type="calculated-property-list-type"/>
> <xs:element name="experimental-properties" type="experimental-property-list-type"/>
> <xs:element name="external-identifiers" type="external-identifier-list-type"/>
> <xs:element name="external-links" type="external-link-list-type"/>
> <xs:element name="pathways" type="pathway-list-type"/>
> <xs:element name="reactions" type="reaction-list-type"/>
> <xs:element name="snp-effects" type="snp-effect-list-type"/>
> <xs:element name="snp-adverse-drug-reactions" type="snp-adverse-drug-reaction-list-type"/>
> <xs:element name="targets" type="target-list-type"/>
> <xs:element name="enzymes" type="enzyme-list-type"/>
> <xs:element name="carriers" type="carrier-list-type"/>
> <xs:element name="transporters" type="transporter-list-type"/>
> </xs:sequence>
这只是其中的一部分。这是一个巨大的文件。非常感谢任何类型的帮助/指导。
This is only a part of it. It's a huge file. Any kind of help/guidance is much appreciated.
推荐答案
可能有一千种方法将XML文件导入PostgreSQL，但这里我发现它很容易实现，并且已经使用大型xml文档进行了测试（120GB +）
There are probably a thousand ways to import XML files into PostgreSQL, but here is an alternative I find quite easy to implement and is already tested with large xml documents (120GB+)
根据XML文件的大小，请考虑拆分它。一个很棒的工具是 xml_split 。此命令将 file.xml 拆分为最小为100MB的较小文件：
Depending on the size of your XML file, consider splitting it. A terrific tool to do so is xml_split. This command splits file.xml in smaller files with a maximum of 100MB:
xml_split -n 5 -l 1 -s 100MB file.xml
文件拆分后在合理的大小，您可以开始导入它们，而不会有内存不足的风险。
Once you have your files split in a reasonable size, you can start importing them without having the risk of running out of memory.
让我们考虑以下XML文件结构......
Let's consider the following XML file structure ...
<?xml version="1.0"?>
<t>
<foo>
<id j="a">1</id>
<val>bar1</val>
</foo>
<foo>
<id j="b">8</id>
<val>bar1</val>
</foo>
<foo>
<id j="c">5</id>
<val>bar1</val>
</foo>
<foo>
<id j="b">2</id>
</foo>
</t>
...以及以下目标表，我们将在其中插入XML记录。
... and the following target table, where we will insert the XML records.
CREATE TABLE t (id TEXT, entry XML);
下面的代码将XML文件导入临时 unlogged 表和 不再 他们进入表格 t 使用 CTE （又名WITH子句）由节点< foo> 组成。命令 perl -pe的/ \ n / \\ n / g'用 \\ n 这样你就不会得到过早的数据结束例外：
The code bellow imports XML files into a temporary unlogged table and unnest them into the table t using a CTE (aka WITH clause) by the node <foo>. The command perl -pe 's/\n/\\n/g' replaces newline characters with \\n so that you do not get a Premature end of data exception:
#!/bin/bash
psql testdb -c "CREATE UNLOGGED TABLE tmp (entry xml);"
for f in /path/to/your/files/;do
cat $f | perl -pe 's/\n/\\n/g' |psql testdb -c "COPY tmp FROM STDIN;"
psql testdb -c "
WITH j AS (
SELECT UNNEST(XPATH('//t/foo',entry)) AS entry FROM tmp
)
INSERT INTO t
SELECT XPATH('//foo/id/text()',j.entry),j.entry FROM j;
TRUNCATE TABLE tmp;"
done
psql testdb -c "DROP TABLE tmp;"
这是您的数据：
testdb=# SELECT * FROM t;
id | entry
-----+--------------------------
{1} | <foo> +
| <id j="a">1</id>+
| <val>bar1</val> +
| </foo>
{8} | <foo> +
| <id j="b">8</id>+
| <val>bar1</val> +
| </foo>
{5} | <foo> +
| <id j="c">5</id>+
| <val>bar1</val> +
| </foo>
{2} | <foo> +
| <id j="b">2</id>+
| </foo>
(4 Zeilen)
这篇关于将xml架构导入postgres以自动创建表，然后使用xml文件填充它？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
