OpenX JSON SerDe

Assim como o Hive JSON SerDe, você pode usar o OpenX JSON para processar dados JSON. Os dados também são representados como strings em uma só linha codificadas em JSON separadas por uma nova linha. Assim como ocorre com o Hive JSON SerDe, o OpenX JSON SerDe não permite chaves duplicadas nos nomes de chaves map ou struct.

Considerações e limitações

Propriedades opcionais

Ao contrário do Hive JSON SerDe, o OpenX JSON SerDe também tem as propriedades SerDe opcionais a seguir que podem ser úteis para resolver inconsistências nos dados.

Exemplo: dados de publicidade

A instrução DDL de exemplo a seguir usa o OpenX JSON SerDe para criar uma tabela com base nos mesmos dados de publicidade online de exemplo usados no exemplo para o Hive JSON SerDe. Na cláusula LOCATION, substitua myregion pelo identificador da região onde o Athena é executado.

CREATE EXTERNAL TABLE impressions (
    requestbegintime string,
    adid string,
    impressionId string,
    referrer string,
    useragent string,
    usercookie string,
    ip string,
    number string,
    processid string,
    browsercokie string,
    requestendtime string,
    timers struct<
       modellookup:string, 
       requesttime:string>,
    threadid string, 
    hostname string,
    sessionid string
)   PARTITIONED BY (dt string)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
LOCATION 's3://amzn-s3-demo-bucket.elasticmapreduce/samples/hive-ads/tables/impressions';

Exemplo: desserializar JSON aninhado

Você pode usar os JSON SerDes para analisar dados codificados por JSON mais complexos. Isso requer o uso de instruções CREATE TABLE que usem elementos struct e array para representar estruturas aninhadas.

O exemplo a seguir cria uma tabela do Athena com base nos dados JSON com estruturas aninhadas. O exemplo tem a seguinte estrutura:

{
"DocId": "AWS",
"User": {
        "Id": 1234,
        "Username": "carlos_salazar", 
        "Name": "Carlos",
"ShippingAddress": {
"Address1": "123 Main St.",
"Address2": null,
"City": "Anytown",
"State": "CA"
   },
"Orders": [
   {
     "ItemId": 6789,
     "OrderDate": "11/11/2022" 
   },
   {
     "ItemId": 4352,
     "OrderDate": "12/12/2022"
   }
  ]
 }
}

Lembre-se de que o OpenX SerDe espera que cada registro JSON esteja em uma única linha de texto. Quando armazenados no Amazon S3, todos os dados no exemplo anterior devem estar em uma única linha, assim:

{"DocId":"AWS","User":{"Id":1234,"Username":"carlos_salazar","Name":"Carlos","ShippingAddress" ...

A instrução CREATE TABLE a seguir usa o Openx-JsonSerDe com os tipos de dados de coleção array e struct para estabelecer grupos de objetos para os dados de exemplo.

CREATE external TABLE complex_json (
   docid string,
   `user` struct<
               id:INT,
               username:string,
               name:string,
               shippingaddress:struct<
                                      address1:string,
                                      address2:string,
                                      city:string,
                                      state:string
                                      >,
               orders:array<
                            struct<
                                 itemid:INT,
                                  orderdate:string
                                  >
                              >
               >
   )
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
LOCATION 's3://amzn-s3-demo-bucket/myjsondata/';

Para consultar os dados, use uma declaração SELECT como a seguinte:

SELECT 
 user.name as Name, 
 user.shippingaddress.address1 as Address, 
 user.shippingaddress.city as City, 
 o.itemid as Item_ID, o.orderdate as Order_date
FROM complex_json, UNNEST(user.orders) as temp_table (o)

Para acessar os campos de dados dentro das estruturas, a consulta de amostra usa a notação de pontos (por exemplo, user.name). Para acessar dados dentro de uma array de estruturas (como no campo orders), você pode usar a função UNNEST. A função UNNEST nivela a array em uma tabela temporária (neste caso chamada o). Isso permite que você use a notação de pontos da mesma forma que faz com estruturas para acessar os elementos não aninhados da array (por exemplo, o.itemid). O nome temp_table, usado no exemplo para fins ilustrativos, geralmente é abreviado como t.

A tabela a seguir exibe os resultados da consulta.