# Bibliotecas SerDe JSON No Athena, é possível usar bibliotecas SerDe para desserializar dados JSON. A desserialização converte os dados JSON para que possam ser serializados (gravados) em um formato diferente, como Parquet ou ORC. + [Hive JSON SerDe](hive-json-serde.md) + [OpenX JSON SerDe](openx-json-serde.md) + [Amazon Ion Hive SerDe](ion-serde.md) **nota** As bibliotecas do Hive e do OpenX esperam que os dados em JSON estejam em uma única linha (não formatados), com registros separados por um caractere de nova linha. Como o Amazon Ion é um superconjunto de JSON, você pode usar o Amazon Ion Hive SerDe para consultar conjuntos de dados JSON em formatos diferentes do Amazon Ion. Ao contrário das bibliotecas de SerDe JSON OpenX, o Amazon Ion SerDe não espera que cada linha de dados esteja em uma única linha. Esse recurso é útil quando você deseja consultar conjuntos de dados JSON que passaram por reformatação automática ou usar caracteres de nova linha para dividir os campos em uma linha. ## Nomes de biblioteca Use uma das seguintes opções: [org.apache.hive.hcatalog.data.JsonSerDe](https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL#LanguageManualDDL-JSON) [org.openx.data.jsonserde.JsonSerDe](https://github.com/rcongiu/Hive-JSON-Serde) [com.amazon.ionhiveserde.IonHiveSerDe](https://github.com/amzn/ion-hive-serde) # Hive JSON SerDe Normalmente, o Hive JSON SerDe é usado para processar dados JSON como eventos. Esses eventos são representados como strings em uma só linha codificadas em JSON separadas por uma nova linha. O Hive 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\$1CURSOR\$1ERROR: Row is not a valid JSON Object (HIVE\$1CURSOR\$1ERROR: a linha não é um objeto JSON válido) ou HIVE\$1CURSOR\$1ERROR: JsonParseException: Unexpected end-of-input: expected close marker for OBJECT (HIVE\$1CURSOR\$1ERROR: 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](https://github.com/rcongiu/Hive-JSON-Serde#json-data-files) na documentação do OpenX SerDe no GitHub. A instrução DDL de exemplo a seguir usa o Hive JSON SerDe para criar uma tabela com base em dados de publicidade online de exemplo. Na cláusula `LOCATION`, substitua *myregion* em `s3://amzn-s3-demo-bucket.elasticmapreduce/samples/hive-ads/tables/impressions` pela região onde o Athena é executado (por exemplo, `s3://us-west-2.elasticmapreduce/samples/hive-ads/tables/impressions`). ``` CREATE EXTERNAL TABLE impressions ( requestbegintime string, adid string, impressionid string, referrer string, useragent string, usercookie string, ip string, number string, processid string, browsercookie string, requestendtime string, timers struct < modellookup:string, requesttime:string >, threadid string, hostname string, sessionid string ) PARTITIONED BY (dt string) ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe' LOCATION 's3://amzn-s3-demo-bucket.elasticmapreduce/samples/hive-ads/tables/impressions'; ``` ## Especificação de formatos de carimbo de data/hora com o Hive JSON SerDe Para analisar valores de carimbo de data/hora da string, você pode adicionar o subcampo `WITH SERDEPROPERTIES` à cláusula `ROW FORMAT SERDE` e usá-lo para especificar o parâmetro `timestamp.formats`. No parâmetro, especifique uma lista separada por vírgula de um ou mais padrões de carimbo de data/hora, como no seguinte exemplo: ``` ... ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe' WITH SERDEPROPERTIES ("timestamp.formats"="yyyy-MM-dd'T'HH:mm:ss.SSS'Z',yyyy-MM-dd'T'HH:mm:ss") ... ``` Para obter mais informações, consulte [Carimbos de data/hora](https://cwiki.apache.org/confluence/display/hive/languagemanual+types#LanguageManualTypes-TimestampstimestampTimestamps) na documentação do Apache Hive. ## Carregamento de tabela para consulta Após criar a tabela, execute [MSCK REPAIR TABLE](msck-repair-table.md) para carregá-la e torná-la consultável no Athena: ``` MSCK REPAIR TABLE impressions ``` ## Consulta a logs do CloudTrail É possível usar o Hive JSON SerDe para consultar os logs do CloudTrail. Para obter mais informações e instruções `CREATE TABLE` de exemplo, consulte [Consultar logs do AWS CloudTrail](cloudtrail-logs.md). # 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 + Ao usar SerDe em OpenX JSON, a quantidade e os valores dos resultados podem ser não determinísticos. Os resultados podem conter mais linhas do que o esperado, menos linhas do que o esperado ou valores nulos inesperados quando não existe nenhum nos dados subjacentes. Para contornar esse problema, use [Hive JSON SerDe](hive-json-serde.md) ou reescreva os dados em outro tipo de formato de arquivo. + 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\$1CURSOR\$1ERROR: Row is not a valid JSON Object (HIVE\$1CURSOR\$1ERROR: a linha não é um objeto JSON válido) ou HIVE\$1CURSOR\$1ERROR: JsonParseException: Unexpected end-of-input: expected close marker for OBJECT (HIVE\$1CURSOR\$1ERROR: 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](https://github.com/rcongiu/Hive-JSON-Serde#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. **use.null.para.invalid.data** Opcional. O padrão é `FALSE`. Quando definido como `TRUE`, o SerDe usa `NULL` para os valores da coluna em que houve falha na desserialização para o tipo de coluna definido no esquema da tabela. Definir `use.null.for.invalid.data` como `TRUE` pode gerar resultados incorretos ou inesperados, pois os valores `NULL` substituem os dados inválidos nas colunas que não correspondem ao esquema. Recomendamos que você corrija os dados nos arquivos ou no esquema da tabela em vez de habilitar essa propriedade. Quando essa propriedade está habilitada, não ocorrem falhas nas consultas devido a dados inválidos, o que pode impedir que você detecte problemas de qualidade de 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\$1CURSOR\$1ERROR: 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](reserved-words.md). 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) 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://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](https://github.com/rcongiu/Hive-JSON-Serde) 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. **** | \$1 | Nome | Endereço | Cidade | Item\$1ID | Order\$1date | | --- | --- | --- | --- | --- | --- | | 1 | Carlos | 123 Main St. | Anytown | 6789 | 11/11/2022 | | 2 | Carlos | 123 Main St. | Anytown | 4352 | 12/12/2022 | ## Recursos adicionais Para obter mais informações sobre como trabalhar com JSON e JSON aninhado no Athena, consulte os seguintes recursos: + [Create tables in Amazon Athena from nested JSON and mappings using JSONSerDe](https://aws.amazon.com/blogs/big-data/create-tables-in-amazon-athena-from-nested-json-and-mappings-using-jsonserde/) (Criar tabelas no Amazon Athena de mapeamentos e JSON aninhado usando JSONSerDe) (blog sobre big data da AWS) + [Recebo mensagens de erro ao tentar ler dados JSON no Amazon Athena](https://aws.amazon.com/premiumsupport/knowledge-center/error-json-athena/) (artigo do Centro de conhecimento da AWS) + [hive-json-schema](https://github.com/quux00/hive-json-schema) (GitHub): ferramenta escrita em Java que gera instruções `CREATE TABLE` de documentos JSON de exemplo. As instruções `CREATE TABLE` geradas usam o OpenX JSON Serde.