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
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 esFALSE
. - dots.in.keys
-
Opcional. El valor predeterminado es
FALSE
. Cuando se establece enTRUE
, 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 enTRUE
, 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
yUrl
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
enFALSE
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 denominadatimestamp
, utilice la siguiente sintaxis para mapear la clave a una columna denominadats
: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 propiedadmapping
para asignar ese campo a un nombre compatible con Hive. Por ejemplo, si las definiciones de tipos de columna contienenmy:struct:field:string
, puede asignar la definición amy_struct_field:string
incluyendo la siguiente entrada enWITH 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-JsonSerDestruct
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 |