Использование коннекторов PXF object store для чтения и записи JSON-данных между Greengage DB и S3

$ mkdir $PXF_BASE/servers/s3
$ cd $PXF_BASE/servers/s3
$ cp $PXF_HOME/templates/minio-site.xml .

<?xml version="1.0" encoding="UTF-8"?>
<configuration>
    <property>
        <name>fs.s3a.endpoint</name>
        <value>storage.example.com</value>
    </property>
    <property>
        <name>fs.s3a.access.key</name>
        <value>${ACCESS_KEY}</value>
    </property>
    <property>
        <name>fs.s3a.secret.key</name>
        <value>${SECRET_KEY}</value>
    </property>
    <property>
        <name>fs.s3a.fast.upload</name>
        <value>true</value>
    </property>
    <property>
        <name>fs.s3a.path.style.access</name>
        <value>true</value>
    </property>
</configuration>

$ pxf cluster sync

{"customer": {"id": 1,"name": "John Doe"},"ordered_items":["laptop", "monitor"]}
{"customer": {"id": 2,"name": "Jane Smith"},"ordered_items":["keyboard", "mouse", "pad"]}
{"customer": {"id": 3,"name": "Bob Brown"},"ordered_items":["headphones"]}
{"customer": {"id": 4,"name": "Alice Green"},"ordered_items":["webcam", "microphone"]}

CREATE EXTERNAL TABLE customers_r_singleline_1 (
        "customer.id" INT,
        "customer.name" TEXT,
        "ordered_items" TEXT
    )
    LOCATION('pxf://customers/customers.jsonl?PROFILE=s3:json&SERVER=s3')
    FORMAT 'CUSTOM' (FORMATTER='pxfwritable_import');

SELECT * FROM customers_r_singleline_1;

SELECT "customer.id",
       "customer.name",
       ARRAY(SELECT json_array_elements_text(ordered_items::json))::text[] AS items
FROM customers_r_singleline_1;

CREATE EXTERNAL TABLE customers_r_singleline_2 (
        "customer.id" INT,
        "customer.name" TEXT,
        "ordered_items" TEXT[]
    )
    LOCATION('pxf://customers/customers.jsonl?PROFILE=s3:json&SERVER=s3')
    FORMAT 'CUSTOM' (FORMATTER='pxfwritable_import');

SELECT "customer.id",
       "customer.name",
       "ordered_items"[1]
FROM customers_r_singleline_2;

CREATE EXTERNAL TABLE customers_r_singleline_3 (
        "customer.id" INT,
        "customer.name" TEXT,
        "ordered_items[1]" TEXT
    )
    LOCATION('pxf://customers/customers.jsonl?PROFILE=s3:json&SERVER=s3')
    FORMAT 'CUSTOM' (FORMATTER='pxfwritable_import');

SELECT * FROM customers_r_singleline_3;

{
  "customers": [
    {
      "id": 101,
      "name": "Alice Smith",
      "ordered_items": [
        "laptop",
        "monitor"
      ]
    },
    {
      "id": 102,
      "name": "Bob Johnson",
      "ordered_items": [
        "keyboard",
        "mouse",
        "pad"
      ]
    },
    {
      "id": 103,
      "name": "Charlie Brown",
      "ordered_items": [
        "headphones"
      ]
    }
  ]
}

CREATE EXTERNAL TABLE customers_r_multiline (
    id INT,
    name TEXT,
    ordered_items TEXT[]
    )
    LOCATION ('pxf://customers/customers.json?PROFILE=s3:json&SERVER=s3&IDENTIFIER=id')
    FORMAT 'CUSTOM' (FORMATTER='pxfwritable_import');

SELECT * FROM customers_r_multiline;

CREATE WRITABLE EXTERNAL TABLE customers_w_singleline (
        id INT,
        name TEXT, 
        ordered_items TEXT[]    
    )
    LOCATION ('pxf://customers?PROFILE=s3:json&SERVER=s3')
    FORMAT 'CUSTOM' (FORMATTER='pxfwritable_export');

INSERT INTO customers_w_singleline 
VALUES (1, 'John Doe', ARRAY['laptop', 'monitor']),
       (2, 'Jane Smith', ARRAY['keyboard', 'mouse', 'pad']),
       (3, 'Bob Brown', ARRAY['headphones']),
       (4, 'Alice Green', ARRAY['webcam', 'microphone']);

{"id":3,"name":"Bob Brown","ordered_items":["headphones"]}
{"id":4,"name":"Alice Green","ordered_items":["webcam","microphone"]}
{"id":1,"name":"John Doe","ordered_items":["laptop","monitor"]}
{"id":2,"name":"Jane Smith","ordered_items":["keyboard","mouse","pad"]}

CREATE WRITABLE EXTERNAL TABLE customers_w_multiline (
        id INT,
        name TEXT, 
        ordered_items TEXT[]    
    )
    LOCATION ('pxf://customers?PROFILE=s3:json&SERVER=s3&ROOT=customers')
    FORMAT 'CUSTOM' (FORMATTER='pxfwritable_export');

INSERT INTO customers_w_multiline 
VALUES (1, 'John Doe', ARRAY['laptop', 'monitor']),
       (2, 'Jane Smith', ARRAY['keyboard', 'mouse', 'pad']),
       (3, 'Bob Brown', ARRAY['headphones']),
       (4, 'Alice Green', ARRAY['webcam', 'microphone']);

{"customers":[
{"id":3,"name":"Bob Brown","ordered_items":["headphones"]}
]}
{"customers":[
{"id":1,"name":"John Doe","ordered_items":["laptop","monitor"]}
]}
{"customers":[
{"id":2,"name":"Jane Smith","ordered_items":["keyboard","mouse","pad"]}
,{"id":4,"name":"Alice Green","ordered_items":["webcam","microphone"]}
]}