Трансформация внешних данных

$ gpfdist -c config.yaml

CREATE READABLE EXTERNAL TABLE <external_table_name>
    LOCATION ('gpfdist://<hostname>:<port>/<filename>#transform=<transformation_name>'
               [, ...]
             )

INSERT INTO <table_name> SELECT * FROM <external_table_name>;

<customers>
    <customer id="1">
        <first_name>John</first_name>
        <last_name>Doe</last_name>
        <email>john.doe@example.com</email>
        <address>123 Elm Street</address>
    </customer>
    <customer id="2">
        <first_name>Jane</first_name>
        <last_name>Smith</last_name>
        <email>jane.smith@example.com</email>
        <address>456 Oak Street</address>
    </customer>
    <customer id="3">
        <first_name>Bob</first_name>
        <last_name>Brown</last_name>
        <email>bob.brown@example.com</email>
        <address>789 Pine Street</address>
    </customer>
    <customer id="4">
        <first_name>Rob</first_name>
        <last_name>Stuart</last_name>
        <email>rob.stuart@example.com</email>
        <address>119 Willow Street</address>
    </customer>
</customers>

TABLE customers;

import csv
import xml.etree.ElementTree as ET
import sys

def csv_to_xml(csv_data):

    root = ET.Element('customers')

    reader = csv.reader(csv_data.splitlines(), delimiter='|')
    header = next(reader)

    for row in reader:
        if len(row) != 4:
            print "Skipping invalid row: %s" % row
            continue

        customer_id = row[0]
        name = row[1]
        email = row[2]
        address = row[3]

        customer = ET.SubElement(root, 'customer', {'id': customer_id})

        ET.SubElement(customer, 'first_name').text = name.split(' ')[0]
        ET.SubElement(customer, 'last_name').text = name.split(' ')[-1]
        ET.SubElement(customer, 'email').text = email
        ET.SubElement(customer, 'address').text = address

    return ET.tostring(root, encoding='utf-8')

if __name__ == "__main__":
    csv_data = sys.stdin.read()
    try:
        xml_output = csv_to_xml(csv_data)
        print xml_output
    except ValueError as e:
        print "Error: %s" % e
        sys.exit(1)

---
VERSION: 1.0.0.1
TRANSFORMATIONS:
    transform_customers:
        TYPE: output
        COMMAND: /usr/bin/env bash -c "(/usr/bin/python /tmp/transform_customers_out.py | cat - >> $0)" %filename%

$ gpfdist -c config_out.yaml -d /tmp -p 8888 &

CREATE WRITABLE EXTERNAL TABLE customers_w (LIKE customers)
    LOCATION ('gpfdist://mdw:8888/customers_out.xml#transform=transform_customers')
    FORMAT 'TEXT' (DELIMITER '|');

INSERT INTO customers_w (id, name, email, address)
VALUES (1,'John Doe','john.doe@example.com','123 Elm Street'),
       (2,'Jane Smith','jane.smith@example.com','456 Oak Street'),
       (3,'Bob Brown','bob.brown@example.com','789 Pine Street'),
       (4,'Rob Stuart','rob.stuart@example.com','119 Willow Street');

$ cat /tmp/customers_out.xml

<customers>
    <customer id="1">
        <first_name>John</first_name>
        <last_name>Doe</last_name>
        <email>john.doe@example.com</email>
        <address>123 Elm Street</address>
    </customer>
    <customer id="2">
        <first_name>Jane</first_name>
        <last_name>Smith</last_name>
        <email>jane.smith@example.com</email>
        <address>456 Oak Street</address>
    </customer>
    <customer id="3">
        <first_name>Bob</first_name>
        <last_name>Brown</last_name>
        <email>bob.brown@example.com</email>
        <address>789 Pine Street</address>
    </customer>
    <customer id="4">
        <first_name>Rob</first_name>
        <last_name>Stuart</last_name>
        <email>rob.stuart@example.com</email>
        <address>119 Willow Street</address>
    </customer>
</customers>