Использование коннектора PXF HDFS для чтения и записи данных в формате Avro между Greengage DB и HDFS

$ 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

{
  "type": "record",
  "name": "Customer",
  "fields": [
    {"name": "id", "type": "int"},
    {"name": "name", "type": "string"},
    {"name": "email", "type": "string"},
    {"name": "preferredChannels", "type": {"type": "array", "items": "string"}},
    {
      "name": "address",
      "type": {
        "type": "record",
        "name": "Address",
        "fields": [
          {"name": "street", "type": "string"},
          {"name": "city", "type": "string"},
          {"name": "state", "type": "string"},
          {"name": "zipCode", "type": "string"}
        ]
      }
    },
    {
      "name": "preferences",
      "type": {
        "type": "map",
        "values": "string"
      }
    }
  ]
}

{
  "id": 123,
  "name": "Alice Smith",
  "email": "alice.smith@example.com",
  "preferredChannels": ["email", "sms"],
  "address": {
    "street": "123 Main St",
    "city": "Anytown",
    "state": "CA",
    "zipCode": "91234"
  },
  "preferences": {
    "notificationsEnabled": "true",
    "language": "en-US",
    "theme": "dark"
  }
}
{
  "id": 456,
  "name": "Bob Johnson",
  "email": "bob.johnson@example.com",
  "preferredChannels": ["push", "in-app"],
  "address": {
    "street": "456 Oak Ave",
    "city": "Springfield",
    "state": "IL",
    "zipCode": "62704"
  },
  "preferences": {
    "notificationsEnabled": "false",
    "language": "fr-CA",
    "theme": "light"
  }
}

$ curl -O https://repo1.maven.org/maven2/org/apache/avro/avro-tools/1.12.1/avro-tools-1.12.1.jar
$ java -jar ./avro-tools-1.12.1.jar fromjson --schema-file /tmp/avro_schema.avsc /tmp/customers.txt > /tmp/customers.avro

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

CREATE EXTERNAL TABLE customers_r (
        id INT,
        name TEXT,
        email TEXT,
        preferredChannels TEXT[],
        address TEXT[],
        preferences TEXT[]
    )
    LOCATION ('pxf://tmp/pxf_examples/customers.avro?&PROFILE=hdfs:avro&SERVER=hadoop&COLLECTION_DELIM=,&MAPKEY_DELIM=:&RECORDKEY_DELIM=:')
    FORMAT 'CUSTOM' (FORMATTER='pxfwritable_import');

SELECT * FROM customers_r;

SELECT id, preferences[1], preferredChannels[1] FROM customers_r;

CREATE WRITABLE EXTERNAL TABLE customers_w(
        id INT,
        name TEXT, 
        preferredChannels TEXT[]    
    )
    LOCATION ('pxf://tmp/pxf_examples/customers_w?PROFILE=hdfs:avro&SERVER=hadoop')
    FORMAT 'CUSTOM' (FORMATTER='pxfwritable_export');

INSERT INTO customers_w 
VALUES (1, 'Alice Smith', ARRAY['email', 'sms']),
       (1, 'Bob Johnson', ARRAY['push', 'in-app']);

$ hdfs dfs -get -f /tmp/pxf_examples/customers_w /tmp

$ java -jar ./avro-tools-1.12.1.jar concat /tmp/customers_w/ /tmp/customers_w.avro

$ java -jar ./avro-tools-1.12.1.jar tojson /tmp/customers_w.avro

{"id":{"int":1},"name":{"string":"Alice Smith"},"preferredchannels":{"array":[{"string":"email"},{"string":"sms"}]}}
{"id":{"int":1},"name":{"string":"Bob Johnson"},"preferredchannels":{"array":[{"string":"push"},{"string":"in-app"}]}}