El SerDe JSON de OpenX

Al igual que el SerDe JSON de Hive, puede utilizar el JSON de OpenX para procesar datos JSON. Los datos también se representan como cadenas de una sola línea de texto con codificación JSON separadas por una línea nueva. Al igual que SerDe JSON de Hive, SerDe JSON de OpenX no permite claves duplicadas en map ni nombres de claves struct.

nota

El SerDe espera que cada documento JSON esté en una sola línea de texto sin caracteres de terminación de línea que separen los campos del registro. Si el texto JSON está en formato de impresión, puede recibir un mensaje de error como HIVE_CURSOR_ERROR: la fila no es un objeto JSON válido o HIVE_CURSOR_ERROR: JsonParseException: fin de entrada inesperado: marcador de cierre esperado para OBJECT cuando intenta consultar la tabla después de crearla. Para obtener más información, consulte los Archivos de datos JSON en la documentación de OpenX SerDE en GitHub.

Propiedades opcionales

A diferencia del SerDe JSON de Hive, el SerDe JSON de OpenX también tiene las siguientes propiedades SerDe opcionales que pueden resultar útiles para solucionar incoherencias en los datos.

ignore.malformed.json

Opcional. Cuando se establece en TRUE, le permite omitir la sintaxis JSON con formato incorrecto. El valor predeterminado es FALSE.

dots.in.keys

Opcional. El valor predeterminado es FALSE. Cuando se establece en TRUE, permite que el SerDe sustituya los puntos en nombres de claves con guiones bajos. Por ejemplo, si el conjunto de datos de JSON contiene una clave con el nombre "a.b", puede utilizar esta propiedad para definir el nombre de la columna para que sea "a_b" en Athena. De forma predeterminada (sin este SerDe), Athena no permite puntos en los nombres de columnas.

case.insensitive

Opcional. El valor predeterminado es TRUE. Cuando se establece en TRUE, el SerDe convierte todas las columnas en mayúscula a minúscula.

Para utilizar nombres de clave que distinguen mayúsculas de minúsculas en los datos, utilice WITH SERDEPROPERTIES ("case.insensitive"= FALSE;). A continuación, para cada clave que no esté ya en minúsculas, proporcione un mapeo desde el nombre de la columna al nombre de la propiedad utilizando la siguiente sintaxis:

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

Si tiene dos claves como URL y Url que son iguales cuando se escriben en minúsculas, puede producirse un error como el siguiente:

HIVE_CURSOR_ERROR: Row is not a valid JSON Object - JSONException: Duplicate key "url"

Para resolver esto, establezca la propiedad case.insensitive en FALSE y asigne las claves a nombres diferentes, como en el ejemplo siguiente:

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

asignación

Opcional. Asigna nombres de columna a las claves JSON que no son idénticas a los nombres de las columnas. El parámetro mapping es útil cuando los datos JSON contienen claves que son palabras clave. Por ejemplo, si tiene una clave JSON denominada timestamp, utilice la siguiente sintaxis para mapear la clave a una columna denominada ts:

ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
WITH SERDEPROPERTIES ("mapping.ts" = "timestamp")

Asignación de nombres de campos anidados con dos puntos a nombres compatibles con Hive

Si tiene un nombre de campo con dos puntos dentro de una estructura struct, puede utilizar la propiedad mapping para asignar ese campo a un nombre compatible con Hive. Por ejemplo, si las definiciones de tipos de columna contienen my:struct:field:string, puede asignar la definición a my_struct_field:string incluyendo la siguiente entrada en WITH SERDEPROPERTIES:

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

En el siguiente ejemplo se muestra la instrucción CREATE TABLE correspondiente.

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")

Ejemplo: datos de publicidad

La instrucción DDL de ejemplo que se muestra a continuación, utiliza SerDe JSON de OpenX para crear una tabla en función de los mismos datos de publicidad en línea de ejemplo utilizados en el ejemplo para SerDe JSON de Hive. En la cláusula LOCATION, reemplace myregion por el identificador de región donde se ejecuta Athena.

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';

Ejemplo: deserialización de JSON anidado

Puede usar SerDes JSON para analizar datos codificados en JSON más complejos. Esto requiere el uso de instrucciones CREATE TABLE que utilizan elementos struct y array para representar estructuras anidadas.

En el ejemplo siguiente, se crea una tabla de Athena a partir de datos JSON que tienen estructuras anidadas. El ejemplo tiene la siguiente estructura:

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

Recuerde que OpenX SerDe espera que cada registro JSON esté en una sola línea de texto. Cuando se almacenan en Amazon S3, todos los datos del ejemplo anterior deben estar en una sola línea, así:

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

La siguiente instrucción CREATE TABLE utiliza Openx-JsonSerDe con los tipos de datos de recopilación struct y array para establecer grupos de objetos para los datos de ejemplo.

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 la tabla, utilice una instrucción SELECT como la siguiente.

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 acceder a los campos de datos dentro de las estructuras tipo struct, la consulta de ejemplo utiliza la notación con puntos (por ejemplo, user.name). Para acceder a los datos dentro de una matriz de estructuras tipo struct (como con el campo orders), puede utilizar la función UNNEST. La función aplana la matriz en una tabla temporal (en este caso llamada o). Esto permite utilizar la notación de puntos como se hace con las estructuras tipo structs para acceder a los elementos no anidados de la matriz (por ejemplo, o.itemid). El nombre temp_table, utilizado en el ejemplo con fines ilustrativos, normalmente se abrevia como t.

La siguiente tabla muestra los resultados de la consulta.

#	Nombre	Dirección	Ciudad	Item_ID	Order_date
1	Carlos	123 Main St.	Anytown	6789	11/11/2022
2	Carlos	123 Main St.	Anytown	4352	12/12/2022