Использование коннектора PXF HDFS для чтения и записи JSON-данных из HDFS в Greengage DB

$ mkdir $PXF_BASE/servers/hadoop
$ cd $PXF_BASE/servers/hadoop

$ scp hdfsuser@namenode:/etc/hadoop/conf/core-site.xml .
$ scp hdfsuser@namenode:/etc/hadoop/conf/hdfs-site.xml .
$ scp hdfsuser@namenode:/etc/hadoop/conf/mapred-site.xml .
$ scp hdfsuser@namenode:/etc/hadoop/conf/yarn-site.xml .

$ 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"]}

$ hdfs dfs -mkdir -p /tmp/pxf_examples
$ hdfs dfs -put /tmp/customers.jsonl /tmp/pxf_examples/

CREATE EXTERNAL TABLE customers_r_singleline_1 (
        "customer.id" INT,
        "customer.name" TEXT,
        "ordered_items" TEXT
    )
    
    LOCATION('pxf://tmp/pxf_examples/customers.jsonl?PROFILE=hdfs:json&SERVER=hadoop')
    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://tmp/pxf_examples/customers.jsonl?PROFILE=hdfs:json&SERVER=hadoop')
    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://tmp/pxf_examples/customers.jsonl?PROFILE=hdfs:json&SERVER=hadoop')
    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"
      ]
    }
  ]
}

$ hdfs dfs -mkdir -p /tmp/pxf_examples
$ hdfs dfs -put /tmp/customers.json /tmp/pxf_examples/

CREATE EXTERNAL TABLE customers_r_multiline (
    id INT,
    name TEXT,
    ordered_items TEXT[]
    )
    
    LOCATION ('pxf://tmp/pxf_examples/customers.json?PROFILE=hdfs:json&SERVER=hadoop&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://tmp/pxf_examples/customers?PROFILE=hdfs:json&SERVER=hadoop')
    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']);

$ hdfs dfs -cat /tmp/pxf_examples/customers/*

{"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://tmp/pxf_examples/customers?PROFILE=hdfs:json&SERVER=hadoop&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']);

$ hdfs dfs -cat /tmp/pxf_examples/customers/*

{"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"]}
]}