OpenX JSON SerDe - Amazon Athena
Propriedades opcionaisExemplo: dados de publicidadeExemplo: desserializar JSON aninhado

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.

nota

O SerDe espera que cada documento JSON esteja em uma única linha de texto, sem caracteres de terminação de linha separando os campos no registro. Se o texto JSON estiver formatado para impressão, você poderá receber uma mensagem de erro como HIVE_CURSOR_ERROR: Row is not a valid JSON Object (HIVE_CURSOR_ERROR: a linha não é um objeto JSON válido) ou HIVE_CURSOR_ERROR: JsonParseException: Unexpected end-of-input: expected close marker for OBJECT (HIVE_CURSOR_ERROR: JSONParseException: Fim de entrada inesperado: marcador de fechamento esperado para OBJECT) quando tentar consultar a tabela após criá-la. Para obter mais informações, consulte JSON Data Files na documentação do OpenX SerDe no GitHub.

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.

ignore.malformed.json

Opcional. Quando definido como TRUE, permite ignorar a sintaxe JSON malformada. O padrão é FALSE.

dots.in.keys

Opcional. O padrão é FALSE. Quando definido como TRUE, permite que o SerDe substitua por sublinhados os pontos nos nomes-chave. Por exemplo, se o conjunto de dados JSON tem uma chave chamada "a.b", você pode usar essa propriedade para definir o nome da coluna como "a_b" no Athena. Por padrão (sem esse SerDe), o Athena não permite pontos nos nomes de coluna.

case.insensitive

Opcional. O padrão é TRUE. Quando definido como TRUE, o SerDe converte todas as colunas em maiúsculas para minúsculas.

Para usar nomes de chave que diferenciam maiúsculas e minúsculas em seus dados, use WITH SERDEPROPERTIES ("case.insensitive"= FALSE;). Depois, para cada chave que ainda não esteja totalmente em letras minúsculas, forneça um mapeamento do nome da coluna para o nome da propriedade usando a seguinte sintaxe:

ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
WITH SERDEPROPERTIES ("case.insensitive" = "FALSE", "mapping.userid" = "userId")

Se você tiver duas chaves como URL e Url que são iguais quando estão em minúsculas, um erro como o seguinte pode ocorrer:

HIVE_CURSOR_ERROR: a linha não é um objeto JSON válido - JSONException: chave duplicada “url”

Para resolver isso, defina a propriedade case.insensitive como FALSE e mapeie as chaves para nomes diferentes, como no exemplo a seguir:

ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
WITH SERDEPROPERTIES ("case.insensitive" = "FALSE", "mapping.url1" = "URL", "mapping.url2" = "Url")
mapeamento

Opcional. Mapeia os nomes das colunas para chaves JSON que não são idênticas aos nomes da coluna. O parâmetro mapping é útil quando os dados JSON contêm chaves que são palavras-chave. Por exemplo, se você tiver uma chave JSON chamada timestamp, use a seguinte sintaxe para mapear a chave para uma coluna chamada ts:

ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
WITH SERDEPROPERTIES ("mapping.ts" = "timestamp")
Mapear nomes de campos aninhados com dois pontos para nomes compatíveis com o Hive

Se você tiver um nome de campo com dois pontos dentro de um struct, poderá usar a propriedade mapping para mapear o campo para um nome compatível com o Hive. Por exemplo, se suas definições de tipo de coluna contiverem my:struct:field:string, você poderá mapear a definição para my_struct_field:string incluindo a seguinte entrada em WITH SERDEPROPERTIES:

("mapping.my_struct_field" = "my:struct:field")

O exemplo a seguir mostra a instrução CREATE TABLE correspondente.

CREATE EXTERNAL TABLE colon_nested_field (
item struct<my_struct_field:string>)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
WITH SERDEPROPERTIES ("mapping.my_struct_field" = "my:struct:field")

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://DOC-EXAMPLE-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://DOC-EXAMPLE-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.

# Nome Endereço Cidade Item_ID Order_date
1 Carlos 123 Main St. Anytown 6789 11/11/2022
2 Carlos 123 Main St. Anytown 4352 12/12/2022