Carga de datos semiestructurados en Amazon Redshift

Utilice el tipo de datos SUPER para continuar y consultar datos jerárquicos y genéricos en Amazon Redshift. Amazon Redshift presenta la función json_parse para analizar datos en formato JSON y convertirlos en la representación SUPER. Amazon Redshift también admite la carga de columnas SUPER mediante el uso del comando COPY. Los formatos de archivos admitidos son JSON, Avro, texto, formato de valores separados por comas (CSV), Parquet y ORC.

Para obtener información acerca de las tablas utilizadas en los ejemplos siguientes, consulte Conjunto de datos de muestra SUPER.

Para obtener más información acerca de la función json_parse, consulte Función JSON_PARSE.

La codificación predeterminada para el tipo de datos SUPER es ZSTD.

Análisis de documentos JSON en columnas SUPER

Puede insertar o actualizar datos JSON en una columna SUPER mediante la función json_parse. La función analiza los datos en formato JSON y los convierte en el tipo de datos SUPER, que puede utilizar en las instrucciones INSERT o UPDATE.

En el siguiente ejemplo, se insertan datos JSON en una columna SUPER. Si la función json_parse falta en la consulta, Amazon Redshift trata el valor como una cadena simple en lugar de como una cadena con formato JSON que debe analizarse.

Si actualiza una columna de datos SUPER, Amazon Redshift requiere que el documento completo se transfiera a valores de columna. Amazon Redshift no admite las actualizaciones parciales.

INSERT INTO region_nations VALUES(0,
   'lar deposits. blithely final packages cajole. regular waters are final requests. regular accounts are according to',
   'AFRICA',
   JSON_PARSE('{"r_nations":[
      {"n_comment":" haggle. carefully final deposits detect slyly agai",
         "n_nationkey":0,
         "n_name":"ALGERIA"
      },
      {"n_comment":"ven packages wake quickly. regu",
         "n_nationkey":5,
         "n_name":"ETHIOPIA"
      },
      {"n_comment":" pending excuses haggle furiously deposits. pending, express pinto beans wake fluffily past t",
         "n_nationkey":14,
         "n_name":"KENYA"
      },
      {"n_comment":"rns. blithely bold courts among the closely regular packages use furiously bold platelets?",
         "n_nationkey":15,
         "n_name":"MOROCCO"
      },
      {"n_comment":"s. ironic, unusual asymptotes wake blithely r",
         "n_nationkey":16,
         "n_name":"MOZAMBIQUE"
      }
   ]
}'));

Uso de COPY para cargar columnas SUPER en Amazon Redshift

En las secciones siguientes, puede obtener información acerca de diferentes formas de utilizar el comando COPY para cargar datos JSON en Amazon Redshift.

Copia de datos de JSON y Avro

Mediante la compatibilidad con datos semiestructurados de Amazon Redshift, puede cargar un documento JSON sin fragmentar los atributos de sus estructuras JSON en varias columnas.

Amazon Redshift proporciona dos métodos para capturar documentos JSON mediante el uso de COPY, incluso con una estructura JSON que sea total o parcialmente desconocida:

Puede utilizar estas opciones con los formatos JSON o Avro.

El tamaño máximo de un objeto JSON antes de la fragmentación es de 4 MB.

Copia de un documento JSON en una única columna de datos SUPER

Para copiar un documento JSON en una única columna de datos SUPER, cree una tabla con una sola columna de datos SUPER.

CREATE TABLE region_nations_noshred (rdata SUPER);

Copie los datos de Amazon S3 en la única columna de datos SUPER. Para capturar los datos de origen JSON en una sola columna de datos SUPER, especifique la opción noshred en la cláusula FORMAT JSON.

COPY region_nations_noshred FROM 's3://redshift-downloads/semistructured/tpch-nested/data/json/region_nation'
REGION 'us-east-1' IAM_ROLE 'arn:aws:iam::xxxxxxxxxxxx:role/Redshift-S3'
FORMAT JSON 'noshred';

Después de que COPY haya capturado correctamente el JSON, su tabla tendrá una columna de datos SUPER rdata que contendrá los datos de todo el objeto JSON. Los datos capturados mantienen todas las propiedades de la jerarquía JSON. Sin embargo, las hojas se convierten en tipos escalares de Amazon Redshift para el procesamiento eficiente de la consulta.

Utilice la siguiente consulta para recuperar la cadena JSON original.

SELECT rdata FROM region_nations_noshred;

Cuando Amazon Redshift genera una columna de datos SUPER, se puede acceder a ella usando JDBC como cadena a través de la serialización JSON. Para obtener más información, consulte Serialización de datos JSON anidados y complejos.

Copia de un documento JSON en varias columnas de datos SUPER

Puede fragmentar un documento JSON en varias columnas que pueden ser columnas de datos SUPER o tipos escalares de Amazon Redshift. Amazon Redshift propaga diferentes partes del objeto JSON a diferentes columnas.

CREATE TABLE region_nations
(
 r_regionkey smallint
 ,r_name varchar
 ,r_comment varchar
 ,r_nations super
 );

Para copiar los datos del ejemplo anterior en la tabla, especifique la opción AUTO en la cláusula FORMAT JSON para dividir el valor JSON en varias columnas. COPY hace coincidir los atributos JSON de nivel superior con los nombres de las columnas y permite que los valores anidados se capturen como valores SUPER, por ejemplo, matrices y objetos JSON.

COPY region_nations FROM 's3://redshift-downloads/semistructured/tpch-nested/data/json/region_nation'
REGION 'us-east-1' IAM_ROLE 'arn:aws:iam::xxxxxxxxxxxx:role/Redshift-S3'
FORMAT JSON 'auto';

Cuando los nombres de atributos JSON están en mayúsculas y minúsculas combinadas, especifique la opción auto ignorecase en la cláusula FORMAT JSON. Para obtener más información acerca del comando COPY, consulte Carga desde datos JSON con la opción 'auto ignorecase'.

En algunos casos, hay una incompatibilidad entre los nombres de las columnas y los atributos JSON o el atributo que se va a cargar está anidado a más que un nivel de profundidad. Si es así, use un archivo jsonpaths para asignar manualmente los atributos JSON a las columnas de Amazon Redshift.

CREATE TABLE nations
(
 regionkey smallint
 ,name varchar
 ,comment super
 ,nations super
 ); 

Imagine que desea cargar datos en una tabla en la que los nombres de las columnas no coinciden con los atributos JSON. En el siguiente ejemplo, la tabla nations es de este tipo. Puede crear un archivo jsonpaths que asigne las rutas de los atributos a las columnas de la tabla por su posición en la matriz jsonpaths.

{"jsonpaths": [
       "$.r_regionkey",
       "$.r_name",
       "$.r_comment",
       "$.r_nations
    ]
}

La ubicación del archivo jsonpaths se utiliza como argumento para FORMAT JSON.

COPY nations FROM 's3://redshift-downloads/semistructured/tpch-nested/data/json/region_nation'
REGION 'us-east-1' IAM_ROLE 'arn:aws:iam::xxxxxxxxxxxx:role/Redshift-S3'
FORMAT JSON 's3://redshift-downloads/semistructured/tpch-nested/data/jsonpaths/nations_jsonpaths.json';

Utilice la siguiente consulta para acceder a la tabla que muestra los datos distribuidos en varias columnas. Las columnas de datos SUPER se imprimen mediante el formato JSON.

SELECT r_regionkey,r_name,r_comment,r_nations[0].n_nationkey FROM region_nations ORDER BY 1,2,3 LIMIT 1;

Los archivos jsonpaths asignan los campos del documento JSON a columnas de la tabla. Puede extraer columnas adicionales, como claves de distribución y ordenación, mientras sigue cargando el documento completo en una columna SUPER. La siguiente consulta carga el documento completo en la columna NATIONS. La columna name es la clave de ordenación y la columna regionkey es la clave de distribución.

CREATE TABLE nations_sorted (
    regionkey smallint,
    name varchar,
    nations super
) DISTKEY(regionkey) SORTKEY(name);

La raíz jsonpath “$” se asigna a la raíz del documento de la siguiente manera:

{"jsonpaths": [
       "$.r_regionkey",
       "$.r_name",
       "$"
    ]
}

La ubicación del archivo jsonpaths se utiliza como argumento para FORMAT JSON.

COPY nations_sorted FROM 's3://redshift-downloads/semistructured/tpch-nested/data/json/region_nation'
REGION 'us-east-1' IAM_ROLE 'arn:aws:iam::xxxxxxxxxxxx:role/Redshift-S3'
FORMAT JSON 's3://redshift-downloads/semistructured/tpch-nested/data/jsonpaths/nations_sorted_jsonpaths.json';

Copia de datos desde texto y CSV

Amazon Redshift representa las columnas SUPER en formatos de texto y CSV como objetos JSON serializados. Se requiere un formato JSON válido para que las columnas SUPER se carguen con el tipo de información correcto. Elimine las comillas de los objetos, las matrices, los números, los valores booleanos y los valores nulos. Ajuste los valores de cadena con comillas dobles. Las columnas SUPER utilizan normas de escape estándar para los formatos de texto y CSV. Para CSV, a los delimitadores se les aplica un carácter de escape según el formato CSV estándar. Para el formato de texto, cuando el delimitador elegido también puede aparecer en un campo SUPER, utilice la opción ESCAPE durante las operaciones COPY y UNLOAD.

COPY region_nations FROM 's3://redshift-downloads/semistructured/tpch-nested/data/csv/region_nation'
REGION 'us-east-1' IAM_ROLE 'arn:aws:iam::xxxxxxxxxxxx:role/Redshift-S3'
FORMAT CSV;

COPY region_nations FROM 's3://redshift-downloads/semistructured/tpch-nested/data/text/region_nation'
REGION 'us-east-1' IAM_ROLE 'arn:aws:iam::xxxxxxxxxxxx:role/Redshift-S3'
DELIMITER ','
ESCAPE;

Copia de datos de Parquet y ORC en formato de columnas

Si los datos semiestructurados o anidados ya están disponibles en formato Apache Parquet o Apache ORC, puede utilizar el comando COPY para capturar datos en Amazon Redshift.

La estructura de la tabla de Amazon Redshift debe coincidir con el número de columnas y con los tipos de datos de las columnas de los archivos Parquet u ORC. Si especifica SERIALIZETOJSON en el comando COPY, puede cargar cualquier tipo de columna en el archivo que se alinee con una columna SUPER de la tabla como SUPER. Esto incluye tipos de estructura y matriz.

COPY region_nations FROM 's3://redshift-downloads/semistructured/tpch-nested/data/parquet/region_nation'
REGION 'us-east-1' IAM_ROLE 'arn:aws:iam::xxxxxxxxxxxx:role/Redshift-S3'
FORMAT PARQUET SERIALIZETOJSON;

En el siguiente ejemplo, se utiliza un formato ORC.

COPY region_nations FROM 's3://redshift-downloads/semistructured/tpch-nested/data/orc/region_nation'
IAM_ROLE 'arn:aws:iam::xxxxxxxxxxxx:role/Redshift-S3'
FORMAT ORC SERIALIZETOJSON;

Cuando los atributos de los tipos de datos de fecha u hora están en ORC, Amazon Redshift los convierte en varchar luego de codificarlos en SUPER.